<?php

use Phalcon\Mvc\Model\Migration;

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

        self::$connection->execute('
            ALTER TABLE IF EXISTS "jaga"."pak_questionnaire"
                DROP COLUMN IF EXISTS panduan_questionnaire;
            ALTER TABLE  IF EXISTS "jaga"."pak_questionnaire" 
                ALTER COLUMN deskripsi DROP NOT NULL;
            ALTER TABLE IF EXISTS "jaga"."pak_questionnaire" 
                DROP COLUMN IF EXISTS is_using_doc_type;
            ALTER TABLE IF EXISTS "jaga"."pak_questionnaire" 
                ADD COLUMN IF NOT EXISTS is_using_doc_info boolean DEFAULT false;
        ');

        self::$connection->execute('
            ALTER TABLE IF EXISTS "jaga"."pak_questionnaire"
                ADD COLUMN IF NOT EXISTS is_active boolean DEFAULT true;
        ');

        self::$connection->execute("
            DELETE FROM jaga.scopes WHERE 
                name = 'pak.create_question'
        ");

        self::$connection->execute("
            INSERT INTO jaga.roles (name, label, description) VALUES 
                ('observer_pak', 'Observer PAK', 'Observer modul Pendidikan Anti Korupsi (PAK)'),
                ('user_dinas_pak', 'User Dinas PAK', 'Pengguna kelompok Dinas pada modul Pendidikan Anti Korupsi (PAK)'),
                ('user_sekolah_pak', 'user Sekolah PAK', 'Pengguna kelompok Sekolah pada modul Pendidikan Anti Korupsi (PAK)')
                ON CONFLICT DO NOTHING;

            INSERT INTO jaga.scopes (name, label, description) VALUES 
                ('pak.admin', 'Administrasi modul PAK', 'Administrasi modul PAK'),
                ('pak.verify', 'Verifikasi Implementasi PAK', 'Verifikasi Implementasi PAK'), 
                ('pak.observe', 'Observasi implementasi PAK', 'Observasi implementasi PAK'),
                ('pak.user_dinas', 'Implementasi PAK', 'Implementasi PAK'),
                ('pak.user_sekolah', 'Implementasi PAK', 'Implementasi 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.admin' 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' 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.observe' 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.user_dinas' 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.user_sekolah' ON CONFLICT DO NOTHING;
        ");
    }

    public function down()
    {

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

        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'
            );

            DELETE FROM jaga.roles_scopes WHERE scope_id IN 
            (SELECT id as scope_id FROM scopes WHERE 
                name = 'pak.admin' OR 
                name = 'pak.verify' OR 
                name = 'pak.observe' OR 
                name = 'pak.user_dinas' OR 
                name = 'pak.user_sekolah'
            );
        ");
        self::$connection->execute("
            DELETE FROM jaga.roles WHERE 
                name = 'observer_pak' OR 
                name = 'user_sekolah_pak' OR 
                name = 'user_dinas_pak';
            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';
        ");
    }
}