<?php

use Phalcon\Mvc\Model\Migration;

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

        self::$connection->execute('
            CREATE TABLE jaga.pancek_konsep_sistem_pencegahan(
                id serial primary key,
                code char varying (30) not null,
                label char varying (255) not null,
                view_order integer not null default 1,
                created_at timestamp(6) DEFAULT now(),
                updated_at timestamp(6),
                deleted_at timestamp(6)
            );

            DROP TABLE IF EXISTS jaga.pancek_indikator;
            CREATE TABLE jaga.pancek_indikator(
                id serial primary key,
                konsep_sistem_pencegahan_id integer not null,
                code char varying (30) not null,
                label text not null,
                view_order integer not null default 1,
                created_at timestamp(6) DEFAULT now(),
                updated_at timestamp(6),
                deleted_at timestamp(6)
            );

            DROP TABLE IF EXISTS jaga.pancek_komponen_indikator;
            CREATE TABLE jaga.pancek_komponen_indikator(
                id serial primary key,
                indikator_id integer not null,
                code char varying (30) not null,
                parent_code char varying (30),
                label text not null,
                is_header boolean default false,
                view_order integer not null default 1,
                created_at timestamp(6) DEFAULT now(),
                updated_at timestamp(6),
                deleted_at timestamp(6)
            );

            DROP TABLE IF EXISTS jaga.pancek_komponen_template;
            CREATE TABLE jaga.pancek_komponen_template(
                id serial primary key,
                komponen_indikator_id integer not null,
                attachment_id integer NOT NULL,
                label text not null,
                created_at timestamp(6) DEFAULT now(),
                updated_at timestamp(6),
                deleted_at timestamp(6)
            );

            DROP TABLE IF EXISTS jaga.pancek_jenis_instansi;
            CREATE TABLE jaga.pancek_jenis_instansi(
                id serial primary key,
                nama char varying (255) not null,
                created_at timestamp(6) DEFAULT now(),
                updated_at timestamp(6),
                deleted_at timestamp(6)
            );

            DROP TABLE IF EXISTS jaga.pancek_sektor_usaha;
            CREATE TABLE jaga.pancek_sektor_usaha(
                id serial primary key,
                nama char varying (255) not null,
                created_at timestamp(6) DEFAULT now(),
                updated_at timestamp(6),
                deleted_at timestamp(6)
            );

            DROP TABLE IF EXISTS jaga.pancek_instansi;
            CREATE TABLE jaga.pancek_instansi(
                id serial primary key,
                nomor char varying(100) unique,
                jenis_instansi_id integer not null,
                sektor_usaha_id integer,
                sektor_usaha_nama char varying(255),
                nama char varying(255) not null,
                provinsi_id integer not null,
                kab_kota_id integer not null,
                alamat char varying(255),
                telepon char varying(15),
                created_at timestamp(6) DEFAULT now(),
                updated_at timestamp(6),
                deleted_at timestamp(6)
            );

            DROP TABLE IF EXISTS jaga.pancek_user;
            CREATE TABLE jaga.pancek_user(
                id serial primary key,
                um_id integer not null,
                instansi_id integer not null,
                jabatan char varying (255) not null,
                created_at timestamp(6) DEFAULT now(),
                updated_at timestamp(6),
                deleted_at timestamp(6)
            );
        ');

        self::$connection->execute("
            INSERT INTO jaga.pancek_konsep_sistem_pencegahan (label, code, view_order) VALUES ('KOMITMEN', 'K', 1);
            INSERT INTO jaga.pancek_konsep_sistem_pencegahan (label, code, view_order) VALUES ('PERENCANAAN', 'P', 2);
            INSERT INTO jaga.pancek_konsep_sistem_pencegahan (label, code, view_order) VALUES ('PELAKSANAAN', 'D', 3);
            INSERT INTO jaga.pancek_konsep_sistem_pencegahan (label, code, view_order) VALUES ('EVALUASI', 'C', 4);
            INSERT INTO jaga.pancek_konsep_sistem_pencegahan (label, code, view_order) VALUES ('PERBAIKAN', 'A', 5);
            INSERT INTO jaga.pancek_konsep_sistem_pencegahan (label, code, view_order) VALUES ('RESPON', 'R', 6);
        ");

        $sql = file_get_contents(__DIR__ . DIRECTORY_SEPARATOR . 'pancek_indikator_insert.sql');
        self::$connection->execute($sql);

        $sql = file_get_contents(__DIR__ . DIRECTORY_SEPARATOR . 'pancek_komponen_indikator_insert.sql');
        self::$connection->execute($sql);

        $sql = file_get_contents(__DIR__ . DIRECTORY_SEPARATOR . 'pancek_master_insert.sql');
        self::$connection->execute($sql);

        self::$connection->execute("
            INSERT INTO jaga.scopes(name, label, description) VALUES ('pancek.admin', 'Manajemen Data Pancek', 'Manajemen Data Pancek');
            INSERT INTO jaga.scopes(name, label, description) VALUES ('pancek.answer', 'Menjawab Survey Pancek', 'Menjawab Survey Pancek');
            INSERT INTO jaga.scopes(name, label, description) VALUES ('pancek.verify', 'Melakukan Verifikasi Survey Pancek', 'Melakukan Verifikasi Survey Pancek');
            INSERT INTO jaga.scopes(name, label, description) VALUES ('pancek.monitoring', 'Melihat data terkait Pancek', 'Melihat data terkait Pancek');
            INSERT INTO jaga.roles(name, label, description) VALUES ('admin_pancek', 'Admin Pancek', 'Admin Pancek');
            INSERT INTO jaga.roles(name, label, description) VALUES ('user_pancek', 'Pengguna Korporasi Pancek', 'User pengguna yang mewakili Korporasi untuk pengisian Survey Pancek');
            INSERT INTO jaga.roles(name, label, description) VALUES ('verifikator_pancek', 'Verifikator Pancek', 'User yang melakukan verifikasi Survey Pancek');
            INSERT INTO jaga.roles(name, label, description) VALUES ('observer_pancek', 'Observer Pancek', 'User yang dapat melihat data terkait Pancek');
            INSERT INTO jaga.roles_scopes(role_id, scope_id) values(
				(select id from jaga.roles where name = 'admin_pancek'), (select id from jaga.scopes where name = 'pancek.admin') 
            );
            INSERT INTO jaga.roles_scopes(role_id, scope_id) values(
				(select id from jaga.roles where name = 'user_pancek'), (select id from jaga.scopes where name = 'pancek.answer') 
            );
            INSERT INTO jaga.roles_scopes(role_id, scope_id) values(
				(select id from jaga.roles where name = 'verifikator_pancek'), (select id from jaga.scopes where name = 'pancek.verify') 
            );
            INSERT INTO jaga.roles_scopes(role_id, scope_id) values(
				(select id from jaga.roles where name = 'observer_pancek'), (select id from jaga.scopes where name = 'pancek.monitoring')
            );
        ");
    }

    public function down()
    {
        self::$connection->execute('
            DROP TABLE IF EXISTS jaga.pancek_konsep_sistem_pencegahan;
            DROP TABLE IF EXISTS jaga.pancek_indikator;
            DROP TABLE IF EXISTS jaga.pancek_komponen_indikator;
            DROP TABLE IF EXISTS jaga.pancek_komponen_template;
            DROP TABLE IF EXISTS jaga.pancek_jenis_instansi;
            DROP TABLE IF EXISTS jaga.pancek_sektor_usaha;
            DROP TABLE IF EXISTS jaga.pancek_sektor_usaha;
            DROP TABLE IF EXISTS jaga.pancek_instansi;
            DROP TABLE IF EXISTS jaga.pancek_user;
        ');

        self::$connection->execute("
            DELETE FROM jaga.roles_scopes WHERE 
            role_id = (select id from jaga.roles where name = 'admin_pancek') 
            AND scope_id = (select id from jaga.scopes where name = 'pancek.admin') 
				;
            DELETE FROM jaga.roles_scopes WHERE 
            role_id = (select id from jaga.roles where name = 'user_pancek') 
            AND scope_id = (select id from jaga.scopes where name = 'pancek.answer') 
				;
            DELETE FROM jaga.roles_scopes WHERE 
            role_id = (select id from jaga.roles where name = 'verifikator_pancek') 
            AND scope_id = (select id from jaga.scopes where name = 'pancek.verify') 
				;
            DELETE FROM jaga.roles_scopes WHERE 
            role_id = (select id from jaga.roles where name = 'observer_pancek') 
            AND scope_id = (select id from jaga.scopes where name = 'pancek.monitoring') 
				;
            DELETE FROM  jaga.roles where name = 'admin_pancek';
            DELETE FROM  jaga.roles where name = 'user_pancek';
            DELETE FROM  jaga.roles where name = 'verifikator_pancek';
            DELETE FROM  jaga.roles where name = 'observer_pancek'; 
            DELETE FROM  jaga.scopes where name = 'pancek.admin';
            DELETE FROM  jaga.scopes where name = 'pancek.answer';
            DELETE FROM  jaga.scopes where name = 'pancek.verify';
            DELETE FROM  jaga.scopes where name = 'pancek.monitoring';
            DELETE FROM jaga.users_roles where role_id = (select id from jaga.roles where name = 'admin_pancek');
            DELETE FROM jaga.users_roles where role_id = (select id from jaga.roles where name = 'user_pancek');
            DELETE FROM jaga.users_roles where role_id = (select id from jaga.roles where name = 'verifikator_pancek');
            DELETE FROM jaga.users_roles where role_id = (select id from jaga.roles where name = 'observer_pancek');
        ");
    }
}