<?php

use Phalcon\Mvc\Model\Migration;

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

        self::$connection->execute("
            CREATE TABLE jaga.spi_mon_rekomendasi_group (
              id bigserial NOT NULL,
              name character varying(255) NOT NULL,
              display_order integer NOT NULL,
              PRIMARY KEY (id)
            )
            WITH (
                OIDS = FALSE
            );
        ");

        self::$connection->execute("
            INSERT INTO jaga.spi_mon_rekomendasi_group (name, display_order) VALUES ('Risiko Sangat Tinggi', 1);
            INSERT INTO jaga.spi_mon_rekomendasi_group (name, display_order) VALUES ('Risiko Tinggi', 2);
            INSERT INTO jaga.spi_mon_rekomendasi_group (name, display_order) VALUES ('Risiko Sedang', 3);
            INSERT INTO jaga.spi_mon_rekomendasi_group (name, display_order) VALUES ('Risiko Rendah', 4);
        ");

        self::$connection->execute("
            CREATE TABLE jaga.spi_mon_rekomendasi_item (
              id bigserial NOT NULL,
              id_group bigint NOT NULL,
              id_instansi bigint NOT NULL,
              tahun int NULL,
              name text NOT NULL,
              PRIMARY KEY (id)
            )
            WITH (
                OIDS = FALSE
            );

            COMMENT ON COLUMN jaga.spi_mon_rekomendasi_item.id_instansi IS 'Jika 0 artinya semua instansi';
        ");

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

        self::$connection->execute("
            CREATE TABLE jaga.spi_mon_form (
              id bigserial NOT NULL,
              id_instansi bigint NOT NULL,
              type character varying(5) NOT NULL,
              status character varying(10) NOT NULL,
              tahun int NOT NULL,
              user_id bigint NOT NULL,
              created_at timestamp without time zone NOT NULL DEFAULT now(),
              updated_at timestamp without time zone NULL,
              PRIMARY KEY (id)
            )
            WITH (
                OIDS = FALSE
            );

            COMMENT ON COLUMN jaga.spi_mon_form.type IS 'KL/PEMDA';
            COMMENT ON COLUMN jaga.spi_mon_form.status IS 'TARGET/REALISASI';
        ");

        self::$connection->execute("
            CREATE TABLE jaga.spi_mon_form_detail (
              id bigserial NOT NULL,
              id_mon_form bigint NOT NULL,
              id_rekomendasi_group bigint NOT NULL,
              id_rekomendasi_item bigint NOT NULL,
              rekomendasi_group_name character varying(255) NOT NULL,
              rekomendasi_item_name text NOT NULL,
              status character varying(10) NOT NULL,
              rencana_aksi text NULL,
              unit_kerja_pj text NULL,
              id_area_intervensi bigint NULL,
              kriteria_keberhasilan text NULL,
              ukuran_keberhasilan text NULL,
              b03 text NULL,
              capaian_b03 numeric NULL,
              b06 text NULL,
              capaian_b06 numeric NULL,
              b09 text NULL,
              capaian_b09 numeric NULL,
              b12 text NULL,
              capaian_b12 numeric NULL,
              user_id bigint NOT NULL,
              target_updated_at timestamp without time zone NULL,
              realisasi_updated_at timestamp without time zone NULL,
              PRIMARY KEY (id)
            )
            WITH (
                OIDS = FALSE
            );

            COMMENT ON COLUMN jaga.spi_mon_form_detail.status IS 'TARGET/REALISASI';
        ");

        self::$connection->execute("
            CREATE TABLE jaga.spi_mon_form_attachment (
              id bigserial NOT NULL,
              id_attachment bigint NOT NULL,
              id_mon_form_detail bigint NOT NULL,
              created_at timestamp without time zone NOT NULL DEFAULT now(),
              PRIMARY KEY (id)
            )
            WITH (
                OIDS = FALSE
            );
        ");
    }

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

        self::$connection->execute("
            DROP TABLE IF EXISTS jaga.spi_mon_form_detail;
        ");
        self::$connection->execute("
            DROP TABLE IF EXISTS jaga.spi_mon_form;
        ");

        self::$connection->execute("
            DROP TABLE IF EXISTS jaga.spi_mon_rekomendasi_item;
        ");

        self::$connection->execute("
            DROP TABLE IF EXISTS jaga.spi_mon_rekomendasi_group;
        ");
    }
}