<?php

use Phalcon\Mvc\Model\Migration;

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

        self::$connection->execute('

            CREATE TABLE IF NOT EXISTS "jaga"."pak_questionnaire" (
                id SERIAL PRIMARY KEY,
                nama VARCHAR(255),
                deskripsi TEXT NOT NULL,
                created_at timestamp(6) DEFAULT now(),
                created_by INTEGER,
                updated_at timestamp(6),
                updated_by INTEGER,
                deleted_at timestamp(6),
                deleted_by INTEGER
            );

            CREATE TABLE IF NOT EXISTS "jaga"."pak_implementasi" (
                id SERIAL PRIMARY KEY,
                instansi_id INTEGER NOT NULL,
                tahun INTEGER NOT NULL,
                nomor VARCHAR(100) NOT NULL,
                tentang VARCHAR(100),
                tanggal_ttd timestamp(6), 
                created_at timestamp(6) DEFAULT now(),
                created_by INTEGER,
                updated_at timestamp(6),
                updated_by INTEGER,
                deleted_at timestamp(6),
                deleted_by INTEGER
            );

            CREATE TABLE IF NOT EXISTS "jaga"."pak_verifikasi_implementasi" (
                id SERIAL PRIMARY KEY,
                questionnaire_id INTEGER NOT NULL,
                implementasi_id INTEGER NOT NULL,
                nilai_verifikasi INTEGER,
                verified_by INTEGER,
                created_at timestamp(6) DEFAULT now(),
                created_by INTEGER,
                updated_at timestamp(6),
                updated_by INTEGER,
                deleted_at timestamp(6),
                deleted_by INTEGER
            );

            CREATE TABLE IF NOT EXISTS "jaga"."pak_verifikasi_implementasi_attachment" (
                id SERIAL PRIMARY KEY,
                verifikasi_id INTEGER NOT NULL,
                attachment_id INTEGER NOT NULL,
                created_at timestamp(6) DEFAULT now(),
                created_by INTEGER,
                updated_at timestamp(6),
                updated_by INTEGER,
                deleted_at timestamp(6),
                deleted_by INTEGER
            );

            CREATE TABLE IF NOT EXISTS "jaga"."pak_sekolah" (
                id SERIAL PRIMARY KEY,
                sekolah_id VARCHAR(100) NOT NULL,
                nama_sekolah VARCHAR(100) NOT NULL,
                created_at timestamp(6) DEFAULT now(),
                created_by INTEGER,
                updated_at timestamp(6),
                updated_by INTEGER,
                deleted_at timestamp(6),
                deleted_by INTEGER
            );

            CREATE TABLE IF NOT EXISTS "jaga"."pak_isian_sekolah" (
                id SERIAL PRIMARY KEY,
                sekolah_id VARCHAR(100) NOT NULL,
                nama_sekolah VARCHAR(100) NOT NULL,
                created_at timestamp(6) DEFAULT now(),
                created_by INTEGER,
                updated_at timestamp(6),
                updated_by INTEGER,
                deleted_at timestamp(6),
                deleted_by INTEGER
            );

            CREATE TABLE IF NOT EXISTS "jaga"."pak_isian_sekolah_detail" (
                id SERIAL PRIMARY KEY,
                isian_id INTEGER NOT NULL,
                metode INTEGER NOT NULL,
                mata_pelajaran VARCHAR(100) NOT NULL,
                kelas VARCHAR(100) NOT NULL,
                nama_kegiatan VARCHAR(100) NOT NULL,
                link VARCHAR(255) NOT NULL,
                created_at timestamp(6) DEFAULT now(),
                created_by INTEGER,
                updated_at timestamp(6),
                updated_by INTEGER,
                deleted_at timestamp(6),
                deleted_by INTEGER
            );

            CREATE TABLE IF NOT EXISTS "jaga"."pak_isian_sekolah_detail_attachment" (
                id SERIAL PRIMARY KEY,
                isian_detail_id INTEGER NOT NULL,
                attachment_tipe INTEGER NOT NULL,
                attachment_id INTEGER NOT NULL,
                created_at timestamp(6) DEFAULT now(),
                created_by INTEGER,
                updated_at timestamp(6),
                updated_by INTEGER,
                deleted_at timestamp(6),
                deleted_by INTEGER
            );
        ');

        self::$connection->execute("
            INSERT INTO jaga.roles (name, label, description) VALUES 
                ('user_pak', 'User PAK', 'Pengguna modul Pendidikan Anti Korupsi (PAK)'),
                ('admin_pak', 'Administrator PAK', 'Admin untuk Pendidikan Anti Korupsi (PAK)'),
                ('verifikator_pak', 'Verifikator PAK', 'Verifikator untuk Pendidikan Anti Korupsi (PAK)')
                ON CONFLICT DO NOTHING;

            INSERT INTO jaga.scopes (name, label, description) VALUES 
                ('pak.create_question', 'Menambah data questionnaire PAK', 'Menambah data questionnaire PAK'),
                ('pak.verify', 'Verifikasi Implementasi PAK', 'Verifikasi 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.create_question' 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;
        ");

        self::$connection->execute("
            INSERT INTO jaga.pak_questionnaire (nama, deskripsi) VALUES 
                ('Regulasi terkait Implementasi PAK di Satuan Pendidikan','Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras at arcu erat. Nunc vulputate mi sed ligula accumsan, non venenatis velit cursus.'),
                ('Renja Disdik terkait Implementasi PAK','Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras at arcu erat. Nunc vulputate mi sed ligula accumsan, non venenatis velit cursus.'),
                ('Pelaksanaan PAK di Satuan Pendidikan','Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras at arcu erat. Nunc vulputate mi sed ligula accumsan, non venenatis velit cursus.'),
                ('Monitoring Evaluasi dan Publikasi Implementasi PAK','Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras at arcu erat. Nunc vulputate mi sed ligula accumsan, non venenatis velit cursus.'),
                ('Lorem ipsum dolor sit amet, consectetur adipiscing elit. ','Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras at arcu erat. Nunc vulputate mi sed ligula accumsan, non venenatis velit cursus.');
            ");
    }

    public function down()
    {
        self::$connection->execute('
            DROP TABLE IF EXISTS "jaga"."pak_isian_detail_attachment";
            DROP TABLE IF EXISTS "jaga"."pak_isian_detail";
            DROP TABLE IF EXISTS "jaga"."pak_isian_sekolah";
            DROP TABLE IF EXISTS "jaga"."pak_sekolah";
            DROP TABLE IF EXISTS "jaga"."pak_verifikasi_implementasi_attachment";
            DROP TABLE IF EXISTS "jaga"."pak_verifikasi_implementasi";
            DROP TABLE IF EXISTS "jaga"."pak_implementasi";
            DROP TABLE IF EXISTS "jaga"."pak_questionnaire";
        ');

        self::$connection->execute("
            DELETE FROM jaga.roles WHERE 
                name = 'user_pak' OR 
                name = 'admin_pak' OR 
                name = 'verifikator_pak';
            DELETE FROM jaga.scopes WHERE 
                name = 'pak.create_question' OR 
                name = 'pak.verify';
        ");
    }
}