<?php

use Phalcon\Mvc\Model\Migration;

class RoleApiConsumerMigration_425 extends Migration {
    public function up()
    {
        self::$connection->execute("
            INSERT INTO jaga.scopes(name, label, description) VALUES ('api.consume', 'Mengakses API JAGA dari Aplikasi Eksternal', 'Mengakses API JAGA dari Aplikasi Eksternal');
            INSERT INTO jaga.scopes(name, label, description) VALUES ('keluhan.desa.manage', 'Mengelola data Keluhan Desa', 'Mengelola data Keluhan Desa');

            INSERT INTO jaga.roles(name, label, description) VALUES ('consumer_keluhan_desa', 'Manager Keluhan Desa dari Aplikasi Eksternal', 'Manager Keluhan Desa dari Aplikasi Eksternal');

            INSERT INTO jaga.roles_scopes(role_id, scope_id) values(
				(select id from jaga.roles where name = 'consumer_keluhan_desa'), (select id from jaga.scopes where name = 'api.consume') 
            );
            INSERT INTO jaga.roles_scopes(role_id, scope_id) values(
				(select id from jaga.roles where name = 'consumer_keluhan_desa'), (select id from jaga.scopes where name = 'keluhan.desa.manage') 
            );
            INSERT INTO jaga.roles_scopes(role_id, scope_id) values(
				(select id from jaga.roles where name = 'consumer_keluhan_desa'), (select id from jaga.scopes where name = 'discussion.create') 
            );
            INSERT INTO jaga.roles_scopes(role_id, scope_id) values(
				(select id from jaga.roles where name = 'consumer_keluhan_desa'), (select id from jaga.scopes where name = 'discussion.react') 
            );

            CREATE TABLE IF NOT EXISTS jaga.keluhan_desa_sipemandu (
                id SERIAL PRIMARY KEY,
                id_diskusi_bansos INTEGER NOT NULL,
                nomor_tiket VARCHAR(50) NOT NULL,
                nama_pelapor VARCHAR(100),
                email_pelapor VARCHAR(100), 
                alamat_pelapor VARCHAR(255),
                id_provinsi INTEGER,
                nama_provinsi VARCHAR(100),
                id_kota_kabupaten INTEGER,
                nama_kota_kabupaten VARCHAR(100),
                id_kecamatan INTEGER,
                nama_kecamatan VARCHAR(100),
                id_desa INTEGER,
                nama_desa VARCHAR(100),
                created_at timestamp(6) WITHOUT TIME ZONE DEFAULT now(),
                updated_at timestamp(6),
                deleted_at timestamp(6),
                CONSTRAINT keluhan_desa_sipemandu_nomor_tiket_unique UNIQUE (nomor_tiket)
            )
            WITH (
                OIDS = FALSE
            );
        ");
    }

    public function down()
    {
        self::$connection->execute("
            DELETE FROM jaga.roles_scopes WHERE 
            role_id = (select id from jaga.roles where name = 'consumer_keluhan_desa');  

            DELETE FROM jaga.scopes WHERE name = 'api.consume';
            DELETE FROM jaga.scopes WHERE name = 'keluhan.desa.manage';

            DELETE FROM jaga.roles WHERE name = 'consumer_keluhan_desa';

            DROP TABLE IF EXISTS jaga.keluhan_desa_sipemandu
        ");
    }
}