<?php


use Phalcon\Mvc\Model\Migration;

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

        //CREATE TABLE litbang_kajian_pengajuan
        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.litbang_kajian_pengajuan
            (
                id serial NOT NULL,
                satgas_id integer NOT NULL,
                sektor_id integer NOT NULL,
                jenis_id integer NOT NULL,
                judul character varying(255),
                pt_pertimbangan_pemilihan_topik text,
                pt_daftar_peneliti character varying(255),
                pt_kontak_peneliti character varying(100),
                pt_created_by integer,
                pt_assign_to integer,
                rkk_studi_pendahuluan text,
                rkk_masalah_hipotesa text,
                rkk_pengumpulan_data_sekunder text,
                rkk_studi_literatur text,
                rkk_lingkup_kajian text,
                rkk_created_by integer,
                rkk_assign_to integer,
                krs_deskripsi text,
                krs_checklist json,
                krs_created_by integer,
                krs_assign_to integer,
                kom_instansi_id integer,
                kom_notulensi text,
                kom_created_by integer,
                kom_assign_to integer,
                pk_is_pihak_ketiga boolean DEFAULT FALSE,
                pk_pihak_ketiga text,
                pk_created_by integer,
                pk_assign_to integer,
                kl_perumusan_masalah text,
                kl_metode_penelitian text,
                kl_tujuan_penelitian text,
                kl_kerangka_teori text,
                kl_gambaran_umum text,
                kl_teknis_penulisan text,
                kl_created_by integer,
                kl_assign_to integer,
                rd_created_by integer,
                rd_assign_to integer,
                pap_is_diseminasi boolean DEFAULT FALSE,
                pap_diseminasi text,
                pap_created_by integer,
                pap_assign_to integer,
                status character varying(30),
                tahapan character varying(30),
                approval_note text,
                created_at timestamp(6) DEFAULT now(),
                updated_at timestamp(6) DEFAULT now(),
                deleted_at timestamp(6),
                PRIMARY KEY (id)
            )
            WITH (OIDS = FALSE);
        ");

        // CREATE TABLE litbang_kajian_pengajuan_jenis_dokumen
        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.litbang_kajian_pengajuan_jenis_dokumen
            (
                id serial NOT NULL,
                tahapan character varying(30) NOT NULL,
                nama character varying(30) NOT NULL,
                keterangan character varying(255),
                ref character varying(30),
                view_order integer NOT NULL DEFAULT 1,
                created_at timestamp(6) DEFAULT now(),
                updated_at timestamp(6) DEFAULT now(),
                deleted_at timestamp(6),
                PRIMARY KEY (id)
            )
            WITH (OIDS = FALSE);        
        ");

        // CREATE TABLE litbang_kajian_pengajuan_attachment
        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.litbang_kajian_pengajuan_attachment
            (
                id serial NOT NULL,
                attachment_id integer NOT NULL,
                kajian_pengajuan_id integer NOT NULL,
                jenis_dokumen_id integer NOT NULL,
                tahapan character varying(30),
                created_at timestamp(6) DEFAULT now(),
                created_by integer,
                updated_at timestamp(6) DEFAULT now(),
                updated_by integer,
                deleted_at timestamp(6),
                PRIMARY KEY (id),
                CONSTRAINT attachment_id_fk_1
                    FOREIGN KEY(attachment_id) 
                    REFERENCES attachments(id)
                    ON DELETE CASCADE,
                CONSTRAINT kajian_pengajuan_id_fk_1
                    FOREIGN KEY(kajian_pengajuan_id) 
                    REFERENCES litbang_kajian_pengajuan(id)
                    ON DELETE CASCADE,
                CONSTRAINT jenis_dokumen_id_fk_1
                    FOREIGN KEY(jenis_dokumen_id) 
                    REFERENCES litbang_kajian_pengajuan_jenis_dokumen(id)
                    ON DELETE CASCADE
            )
            WITH (OIDS = FALSE);
        ");

        // CREATE TABLE litbang_kajian_pengajuan_diskusi
        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.litbang_kajian_pengajuan_diskusi
            (
                id serial NOT NULL,
                kajian_pengajuan_id integer NOT NULL,
                attachment_id integer,
                satgas_id integer,
                pesan text,
                created_at timestamp(6) DEFAULT now(),
                created_by integer,
                PRIMARY KEY (id)
            )
            WITH (OIDS = FALSE);
        ");

        // CREATE TABLE litbang_satgas
        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.litbang_satgas
            (
                id serial NOT NULL,
                nama character varying(100) NOT NULL,
                created_at timestamp(6) DEFAULT now(),
                updated_at timestamp(6) DEFAULT now(),
                deleted_at timestamp(6),
                PRIMARY KEY (id)
            )
            WITH (OIDS = FALSE);
        ");

        // CREATE TABLE litbang_user
        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.litbang_user
            (
                id serial NOT NULL,
                um_id integer NOT NULL,
                satgas_id integer,
                created_at timestamp(6) DEFAULT now(),
                updated_at timestamp(6) DEFAULT now(),
                deleted_at timestamp(6),
                PRIMARY KEY (id)
            )
            WITH (OIDS = FALSE);
        ");

        // CREATE TABLE litbang_kajian_repositori
        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.litbang_kajian_repositori
            (
                id serial NOT NULL,
                sektor_id integer NOT NULL,
                pengajuan_id integer,
                tahun integer,
                kode character varying(15),
                judul character varying(255),
                latar_belakang text,
                tujuan_penelitian text,
                metode_pengumpulan_data text,
                lokasi_subyek text,
                abstrak text,
                kata_kunci character varying(255),
                kesimpulan text,
                daftar_peneliti character varying(255),
                kontak_peneliti character varying(100),
                tgl_presentasi timestamp without time zone,
                tgl_publikasi timestamp without time zone,
                mitra_kerjasama text,
                narasumber text,
                status character varying(30),
                src character varying(30),
                created_at timestamp(6) DEFAULT now(),
                updated_at timestamp(6) DEFAULT now(),
                deleted_at timestamp(6),
                PRIMARY KEY (id)
            )
            WITH (OIDS = FALSE);
        ");

        // CREATE TABLE litbang_kajian_repositori_attachment
        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.litbang_kajian_repositori_attachment
            (
                id serial NOT NULL,
                attachment_id integer NOT NULL,
                kajian_repositori_id integer NOT NULL,
                jenis_dokumen_id integer NOT NULL,
                created_at timestamp(6) DEFAULT now(),
                created_by integer,
                updated_at timestamp(6) DEFAULT now(),
                updated_by integer,
                deleted_at timestamp(6),
                PRIMARY KEY (id),
                CONSTRAINT attachment_id_fk_1
                    FOREIGN KEY(attachment_id) 
                    REFERENCES attachments(id)
                    ON DELETE CASCADE,
                CONSTRAINT kajian_repositori_id_fk_1
                    FOREIGN KEY(kajian_repositori_id) 
                    REFERENCES litbang_kajian_repositori(id)
                    ON DELETE CASCADE,
                CONSTRAINT jenis_dokumen_id_fk_1
                    FOREIGN KEY(jenis_dokumen_id) 
                    REFERENCES litbang_kajian_pengajuan_jenis_dokumen(id)
                    ON DELETE CASCADE
            )
            WITH (OIDS = FALSE);
        ");

        
        //INSERT DATA litbang_kajian_pengajuan_jenis_dokumen
        self::$connection->execute("
            INSERT INTO jaga.litbang_kajian_pengajuan_jenis_dokumen (tahapan, nama, keterangan, view_order) VALUES ('PT', 'Pemilihan Topik', 'Pemilihan Topik (notulensi, atau dokumen lain)', 1);
            INSERT INTO jaga.litbang_kajian_pengajuan_jenis_dokumen (tahapan, nama, keterangan, view_order) VALUES ('RKK', 'Rencana Kerja Kajian', 'Rencana Kerja Kajian (notulensi, atau dokumen lain)', 1);
            INSERT INTO jaga.litbang_kajian_pengajuan_jenis_dokumen (tahapan, nama, keterangan, view_order) VALUES ('KRS', 'KAK, RAB dan SPT', 'KAK, RAB dan SPT', 1);
            INSERT INTO jaga.litbang_kajian_pengajuan_jenis_dokumen (tahapan, nama, keterangan, view_order) VALUES ('KOM', 'MoM / Notulensi', 'MoM / Notulensi', 1);
            INSERT INTO jaga.litbang_kajian_pengajuan_jenis_dokumen (tahapan, nama, keterangan, view_order) VALUES ('PK', 'Pengumpulan Data dan Informasi', 'Pengumpulan Data dan Informasi', 1);
            INSERT INTO jaga.litbang_kajian_pengajuan_jenis_dokumen (tahapan, nama, keterangan, view_order) VALUES ('PK', 'Analisis RCA, CRA, PIA', 'Analisis RCA, CRA, PIA', 2);
            INSERT INTO jaga.litbang_kajian_pengajuan_jenis_dokumen (tahapan, nama, keterangan, view_order) VALUES ('PK', 'Review Hasil Analisis', 'Review Hasil Analisis', 3);
            INSERT INTO jaga.litbang_kajian_pengajuan_jenis_dokumen (tahapan, nama, keterangan, view_order) VALUES ('RD', 'Review Draft', 'Review Draft', 1);
            INSERT INTO jaga.litbang_kajian_pengajuan_jenis_dokumen (tahapan, nama, keterangan, view_order) VALUES ('PAP', 'Paparan Direktur', 'Paparan Direktur', 1);
            INSERT INTO jaga.litbang_kajian_pengajuan_jenis_dokumen (tahapan, nama, keterangan, view_order) VALUES ('PAP', 'Paparan Internal ke Deputi', 'Paparan Internal ke Deputi', 2);
            INSERT INTO jaga.litbang_kajian_pengajuan_jenis_dokumen (tahapan, nama, keterangan, view_order) VALUES ('PAP', 'Paparan Internal ke Direktur', 'Paparan Internal ke Direktur', 3);
            INSERT INTO jaga.litbang_kajian_pengajuan_jenis_dokumen (tahapan, nama, keterangan, view_order) VALUES ('PAP', 'Paparan Eksternal', 'Paparan Eksternal', 4);
            INSERT INTO jaga.litbang_kajian_pengajuan_jenis_dokumen (tahapan, nama, keterangan, view_order) VALUES ('PAP', 'Lampiran Diseminasi', 'Lampiran Diseminasi', 5);
            INSERT INTO jaga.litbang_kajian_pengajuan_jenis_dokumen (tahapan, nama, keterangan, ref, view_order) VALUES ('PUB', 'Pemilihan Topik', 'Pemilihan topik (notulensi, atau dokumen lain)', 'PT', 1);
            INSERT INTO jaga.litbang_kajian_pengajuan_jenis_dokumen (tahapan, nama, keterangan, ref, view_order) VALUES ('PUB', 'Rencana kerja kajian', 'Rencana kerja kajian', 'RKK', 2);
            INSERT INTO jaga.litbang_kajian_pengajuan_jenis_dokumen (tahapan, nama, keterangan, ref, view_order) VALUES ('PUB', 'KAK, RAB, SPT', 'KAK, RAB, SPT', 'KRS', 3);
            INSERT INTO jaga.litbang_kajian_pengajuan_jenis_dokumen (tahapan, nama, keterangan, ref, view_order) VALUES ('PUB', 'Pelaksanaan kajian', 'Pelaksanaan kajian (laporan lapangan)', 'PK', 4);
            INSERT INTO jaga.litbang_kajian_pengajuan_jenis_dokumen (tahapan, nama, keterangan, ref, view_order) VALUES ('PUB', 'Review draft', 'Review draft (notulensi)', 'RD', 5);
            INSERT INTO jaga.litbang_kajian_pengajuan_jenis_dokumen (tahapan, nama, keterangan, ref, view_order) VALUES ('PUB', 'Paparan', 'Paparan (slide ppt)', 'PAP', 6);
        ");

        //INSERT DATA roles
        self::$connection->execute("
            INSERT INTO jaga.roles (name, label, description) VALUES ('admin_litbang', 'Administrator Litbang', 'Administrator Litbang');
            INSERT INTO jaga.roles (name, label, description) VALUES ('direktur_litbang', 'Direktur Litbang', 'Direktur Litbang');
            INSERT INTO jaga.roles (name, label, description) VALUES ('kasatgas_litbang', 'Kasatgas Litbang', 'Kasatgas Litbang');
            INSERT INTO jaga.roles (name, label, description) VALUES ('anggota_litbang', 'Anggota Litbang', 'Anggota Litbang');
        ");

        //INSERT DATA scopes
        self::$connection->execute("
            INSERT INTO jaga.scopes (name, label, description) VALUES ('litbang.access', 'Access pengajuan kajian', 'Access pengajuan kajian');
            INSERT INTO jaga.scopes (name, label, description) VALUES ('litbang.submit', 'Submit pengajuan kajian', 'Submit pengajuan kajian');
            INSERT INTO jaga.scopes (name, label, description) VALUES ('litbang.verify', 'Verify pengajuan kajian', 'Verify pengajuan kajian');
            INSERT INTO jaga.scopes (name, label, description) VALUES ('litbang.manage_user', 'Manage user litbang', 'Manage user litbang');
            INSERT INTO jaga.scopes (name, label, description) VALUES ('litbang.manage_satgas', 'Manage satgas litbang', 'Manage satgas litbang');
        ");

        //INSERT DATA roles_scopes
        self::$connection->execute("
            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_litbang' AND s.name IN (
                    'litbang.access',
                    'litbang.admin',
                    'litbang.manage_user',
                    'litbang.manage_satgas'
                ) 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 = 'direktur_litbang' AND s.name IN (
                    'litbang.access',
                    'litbang.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 = 'kasatgas_litbang' AND s.name IN (
                    'litbang.access',
                    'litbang.submit'
                ) 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 = 'anggota_litbang' AND s.name IN (
                    'litbang.access',
                    'litbang.submit'
                ) ON CONFLICT DO NOTHING;
        ");

        //ALTER TABLE litbang_kajian_klpd
        self::$connection->execute("
            ALTER TABLE litbang_kajian_klpd ADD COLUMN src character varying(10) DEFAULT 'KAJIAN';
        ");
    }

    public function down()
    {
        self::$connection->execute('
            DROP TABLE IF EXISTS "jaga"."litbang_kajian_pengajuan_attachment";
            DROP TABLE IF EXISTS "jaga"."litbang_kajian_pengajuan_diskusi";
            DROP TABLE IF EXISTS "jaga"."litbang_kajian_pengajuan";
            DROP TABLE IF EXISTS "jaga"."litbang_satgas";
            DROP TABLE IF EXISTS "jaga"."litbang_user";
            DROP TABLE IF EXISTS "jaga"."litbang_kajian_repositori_attachment";
            DROP TABLE IF EXISTS "jaga"."litbang_kajian_repositori";
            DROP TABLE IF EXISTS "jaga"."litbang_kajian_pengajuan_jenis_dokumen";
        ');

        self::$connection->execute("
            DELETE FROM jaga.roles_scopes WHERE role_id IN (
                SELECT id FROM jaga.roles WHERE name IN ('admin_litbang', 'direktur_litbang', 'kasatgas_litbang', 'anggota_litbang')
            );
        ");

        self::$connection->execute("
            DELETE FROM jaga.roles WHERE name IN ('admin_litbang', 'direktur_litbang', 'kasatgas_litbang', 'anggota_litbang');
            DELETE FROM jaga.scopes WHERE name IN ('litbang.access', 'litbang.submit', 'litbang.verify', 'litbang.manage_user', 'litbang.manage_satgas');
        ");

        self::$connection->execute("
            ALTER TABLE litbang_kajian_klpd DROP COLUMN IF EXISTS src;
        ");
    }
}