<?php

use Phalcon\Mvc\Model\Migration;

class PAK291Migration_291 extends Migration
{
    public function up()
    {
        $this->down(); 

        self::$connection->execute('
            ALTER TABLE IF EXISTS "jaga"."pak_implementasi"
                ADD COLUMN IF NOT EXISTS status INTEGER DEFAULT 1;
        ');

        self::$connection->execute('
            ALTER TABLE IF EXISTS "jaga"."pak_verifikasi_implementasi"
                ADD COLUMN IF NOT EXISTS verified_at timestamp(6);
        ');

        self::$connection->execute('
            ALTER TABLE IF EXISTS "public"."um_user"
                ADD COLUMN IF NOT EXISTS id_sekolah INTEGER DEFAULT NULL;
        ');

        self::$connection->execute('
            ALTER TABLE IF EXISTS "jaga"."instansi"
                ADD COLUMN IF NOT EXISTS flag_pak boolean DEFAULT false;
        ');

        self::$connection->execute("
            
            INSERT INTO jaga.scopes (name, label, description) VALUES 
                ('pak.manage.questionnaire', 'Administrasi modul PAK', 'Administrasi modul PAK'),
                ('pak.view.questionnaire', 'Administrasi modul PAK', 'Administrasi modul PAK'),

                ('pak.manage.implementasi', 'Mengelola implementasi PAK', 'Mengelola implementasi PAK'),
                ('pak.view.implementasi', 'Melihat implementasi PAK', 'Melihat implementasi PAK'),
                ('pak.verify.implementasi', 'Verifikasi Implementasi PAK', 'Verifikasi Implementasi PAK'), 

                ('pak.manage.user', 'Mengelola user PAK', 'Mengelola user PAK'),
                ('pak.view.user', 'Melihat user PAK', 'Melihat user PAK')
                ON CONFLICT DO NOTHING;

            INSERT INTO jaga.roles_scopes (role_id, scope_id)
                SELECT r.id, s.id FROM jaga.roles r 
                JOIN jaga.scopes s ON r.name = 'admin_pak' and s.name = 'pak.manage.questionnaire' ON CONFLICT DO NOTHING;

            INSERT INTO jaga.roles_scopes (role_id, scope_id)
                SELECT r.id, s.id FROM jaga.roles r 
                JOIN jaga.scopes s ON r.name = 'admin_pak' and s.name = 'pak.view.questionnaire' ON CONFLICT DO NOTHING;

            INSERT INTO jaga.roles_scopes (role_id, scope_id)
                SELECT r.id, s.id FROM jaga.roles r 
                JOIN jaga.scopes s ON r.name = 'observer_pak' and s.name = 'pak.view.questionnaire' ON CONFLICT DO NOTHING;

            INSERT INTO jaga.roles_scopes (role_id, scope_id)
                SELECT r.id, s.id FROM jaga.roles r 
                JOIN jaga.scopes s ON r.name = 'verifikator_pak' and s.name = 'pak.view.questionnaire' ON CONFLICT DO NOTHING;

            INSERT INTO jaga.roles_scopes (role_id, scope_id)
                SELECT r.id, s.id FROM jaga.roles r 
                JOIN jaga.scopes s ON r.name = 'user_dinas_pak' and s.name = 'pak.view.questionnaire' ON CONFLICT DO NOTHING;

            INSERT INTO jaga.roles_scopes (role_id, scope_id)
                SELECT r.id, s.id FROM jaga.roles r 
                JOIN jaga.scopes s ON r.name = 'user_sekolah_pak' and s.name = 'pak.view.questionnaire' ON CONFLICT DO NOTHING;


            INSERT INTO jaga.roles_scopes (role_id, scope_id)
                SELECT r.id, s.id FROM jaga.roles r 
                JOIN jaga.scopes s ON r.name = 'user_dinas_pak' and s.name = 'pak.manage.implementasi' ON CONFLICT DO NOTHING;
            INSERT INTO jaga.roles_scopes (role_id, scope_id)
                SELECT r.id, s.id FROM jaga.roles r 
                JOIN jaga.scopes s ON r.name = 'user_dinas_pak' and s.name = 'pak.view.implementasi' ON CONFLICT DO NOTHING;

            INSERT INTO jaga.roles_scopes (role_id, scope_id)
                SELECT r.id, s.id FROM jaga.roles r 
                JOIN jaga.scopes s ON r.name = 'admin_pak' and s.name = 'pak.view.implementasi' ON CONFLICT DO NOTHING;

            INSERT INTO jaga.roles_scopes (role_id, scope_id)
                SELECT r.id, s.id FROM jaga.roles r 
                JOIN jaga.scopes s ON r.name = 'verifikator_pak' and s.name = 'pak.view.implementasi' ON CONFLICT DO NOTHING;

            INSERT INTO jaga.roles_scopes (role_id, scope_id)
                SELECT r.id, s.id FROM jaga.roles r 
                JOIN jaga.scopes s ON r.name = 'observer_pak' and s.name = 'pak.view.implementasi' ON CONFLICT DO NOTHING;

            INSERT INTO jaga.roles_scopes (role_id, scope_id)
                SELECT r.id, s.id FROM jaga.roles r 
                JOIN jaga.scopes s ON r.name = 'verifikator_pak' and s.name = 'pak.verify.implementasi' ON CONFLICT DO NOTHING;


            INSERT INTO jaga.roles_scopes (role_id, scope_id)
                SELECT r.id, s.id FROM jaga.roles r 
                JOIN jaga.scopes s ON r.name = 'admin_pak' and s.name = 'pak.manage.user' ON CONFLICT DO NOTHING;

            INSERT INTO jaga.roles_scopes (role_id, scope_id)
                SELECT r.id, s.id FROM jaga.roles r 
                JOIN jaga.scopes s ON r.name = 'admin_pak' and s.name = 'pak.view.user' ON CONFLICT DO NOTHING;

            INSERT INTO jaga.roles_scopes (role_id, scope_id)
                SELECT r.id, s.id FROM jaga.roles r 
                JOIN jaga.scopes s ON r.name = 'user_dinas_pak' and s.name = 'pak.view.user' ON CONFLICT DO NOTHING;

            INSERT INTO jaga.roles_scopes (role_id, scope_id)
                SELECT r.id, s.id FROM jaga.roles r 
                JOIN jaga.scopes s ON r.name = 'user_sekolah_pak' and s.name = 'pak.view.user' ON CONFLICT DO NOTHING;

            INSERT INTO jaga.roles_scopes (role_id, scope_id)
                SELECT r.id, s.id FROM jaga.roles r 
                JOIN jaga.scopes s ON r.name = 'verifikator_pak' and s.name = 'pak.view.user' ON CONFLICT DO NOTHING;

            INSERT INTO jaga.roles_scopes (role_id, scope_id)
                SELECT r.id, s.id FROM jaga.roles r 
                JOIN jaga.scopes s ON r.name = 'observer_pak' and s.name = 'pak.view.user' ON CONFLICT DO NOTHING;

        ");
    }

    public function down()
    {
        self::$connection->execute('
            ALTER TABLE IF EXISTS "jaga"."pak_implementasi"
                DROP COLUMN IF EXISTS status;
        ');

        self::$connection->execute('
            ALTER TABLE IF EXISTS "jaga"."pak_verifikasi_implementasi"
                DROP COLUMN IF EXISTS verified_at;
        ');

        self::$connection->execute('
            ALTER TABLE IF EXISTS "public"."um_user"
                DROP COLUMN IF EXISTS id_sekolah;
        ');

        self::$connection->execute('
            ALTER TABLE IF EXISTS "jaga"."instansi"
                DROP COLUMN IF EXISTS flag_pak;
        ');

        self::$connection->execute("
            DELETE FROM jaga.roles_scopes WHERE role_id IN 
            (SELECT id as role_id FROM roles WHERE 
                name = 'admin_pak' OR 
                name = 'verifikator_pak' OR 
                name = 'observer_pak' OR 
                name = 'user_sekolah_pak' OR 
                name = 'user_dinas_pak'
            );
        ");

        self::$connection->execute("
            DELETE FROM jaga.scopes WHERE 
                name = 'pak.admin' OR 
                name = 'pak.verify' OR 
                name = 'pak.observe' OR 
                name = 'pak.user_dinas' OR 
                name = 'pak.user_sekolah';

            DELETE FROM jaga.scopes WHERE 
                name = 'pak.manage.questionnaire' OR
                name = 'pak.view.questionnaire' OR
                name = 'pak.manage.implementasi' OR
                name = 'pak.view.implementasi' OR
                name = 'pak.verify.implementasi' OR
                name = 'pak.manage.user' OR
                name = 'pak.view.user';
        ");
    }
}