<?php

use Phalcon\Mvc\Model\Migration;

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

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

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

        self::$connection->execute("
            INSERT INTO jaga.ppj_unit_induk (nama, kode_unit) VALUES ('UIW ACEH', '11');
            INSERT INTO jaga.ppj_unit_induk (nama, kode_unit) VALUES ('UIW SUMATERA UTARA', '12');
            INSERT INTO jaga.ppj_unit_induk (nama, kode_unit) VALUES ('UIW SUMATERA BARAT', '13');
            INSERT INTO jaga.ppj_unit_induk (nama, kode_unit) VALUES ('UIW S2JB', '14');
            INSERT INTO jaga.ppj_unit_induk (nama, kode_unit) VALUES ('UIW BANGKA BELITUNG', '16');
            INSERT INTO jaga.ppj_unit_induk (nama, kode_unit) VALUES ('UID LAMPUNG', '17');
            INSERT INTO jaga.ppj_unit_induk (nama, kode_unit) VALUES ('UIW RIAU DAN KEPRI', '18');
            INSERT INTO jaga.ppj_unit_induk (nama, kode_unit) VALUES ('UIW KALIMANTAN BARAT', '21');
            INSERT INTO jaga.ppj_unit_induk (nama, kode_unit) VALUES ('UIW KALIMANTAN SELATAN DAN TENGAH', '22');
            INSERT INTO jaga.ppj_unit_induk (nama, kode_unit) VALUES ('UIW KALIMANTAN TIMUR', '23');
            INSERT INTO jaga.ppj_unit_induk (nama, kode_unit) VALUES ('UIW SULUTTENGGO', '31');
            INSERT INTO jaga.ppj_unit_induk (nama, kode_unit) VALUES ('UIW SULSELRABAR', '32');
            INSERT INTO jaga.ppj_unit_induk (nama, kode_unit) VALUES ('UIW MALUKU DAN MALUKU UTARA', '41');
            INSERT INTO jaga.ppj_unit_induk (nama, kode_unit) VALUES ('UIW PAPUA DAN PAPUA BARAT', '42');
            INSERT INTO jaga.ppj_unit_induk (nama, kode_unit) VALUES ('UIW NUSA TENGGARA TIMUR', '43');
            INSERT INTO jaga.ppj_unit_induk (nama, kode_unit) VALUES ('UIW NUSA TENGGARA BARAT', '44');
            INSERT INTO jaga.ppj_unit_induk (nama, kode_unit) VALUES ('UID JAWA TIMUR', '51');
            INSERT INTO jaga.ppj_unit_induk (nama, kode_unit) VALUES ('UID JAWA TENGAH DAN DIY', '52');
            INSERT INTO jaga.ppj_unit_induk (nama, kode_unit) VALUES ('UID JAWA BARAT', '53');
            INSERT INTO jaga.ppj_unit_induk (nama, kode_unit) VALUES ('UID JAKARTA RAYA', '54');
            INSERT INTO jaga.ppj_unit_induk (nama, kode_unit) VALUES ('UID BALI', '55');
            INSERT INTO jaga.ppj_unit_induk (nama, kode_unit) VALUES ('UID BANTEN', '56');
        ");

        self::$connection->execute("
            INSERT INTO jaga.roles(name, label, description) VALUES ('monitoring_ppj_pln', 'Monitoring PPJ PLN', 'Pengguna Monitoring PPJ dari PLN');
        ");

        self::$connection->execute("
            INSERT INTO jaga.scopes(name, label, description) VALUES ('ppj.monitoring.unitinduk', 'Monitoring PPJ Unit Induk', 'Monitoring Dashboard PPJ berdasarkan Unit Induk');
        ");

        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 = 'monitoring_ppj_pln' and s.name = 'ppj.monitoring.unitinduk' ON CONFLICT DO NOTHING;
        ");
    }

    public function down()
    {
        self::$connection->execute('
            DROP TABLE IF EXISTS "jaga"."ppj_unit_induk";
            DROP TABLE IF EXISTS "jaga"."ppj_user_unit";
        ');

        self::$connection->execute("
            DELETE 
            FROM
            	jaga.roles_scopes 
            WHERE
            	role_id = ( SELECT ID FROM jaga.roles WHERE NAME = 'monitoring_ppj_pln' ) 
            	AND scope_id = ( SELECT ID FROM jaga.scopes WHERE NAME = 'ppj.monitoring.unitinduk' );
        ");

        self::$connection->execute("
            DELETE FROM jaga.roles WHERE name = 'monitoring_ppj_pln';
        ");

        self::$connection->execute("
            DELETE FROM jaga.scopes WHERE name = 'ppj.monitoring.unitinduk'
        ");
    }
}