<?php
use Phalcon\Mvc\Model\Migration;

class CreateSpiRtlMigration_551 extends Migration {
    public function up()
    {
        self::$connection->execute('
            CREATE TABLE IF NOT EXISTS "jaga"."spi_rtl" (
                id SERIAL PRIMARY KEY,
                id_instansi INTEGER NOT NULL,
                tahun VARCHAR(4),
                tipe VARCHAR(5),
                dimensi VARCHAR(255),
                rekomendasi TEXT,
                rencana_aksi TEXT,
                kriteria_keberhasilan TEXT,
                ukuran_keberhasilan TEXT,
                data_dukung TEXT,
                penanggung_jawab TEXT,
                waktu_pelaksanaan VARCHAR(255),
                risiko TEXT,
                capaian VARCHAR(10),
                upload_dok_pendukung TEXT,

                FOREIGN KEY (id_instansi) REFERENCES instansi(id) ON DELETE CASCADE
            );

            CREATE INDEX IF NOT EXISTS idx_spi_rtl_id_instansi ON jaga.spi_rtl (id_instansi);
        ');

        self::$connection->execute('
            CREATE TABLE IF NOT EXISTS "jaga"."spi_rtl_verifikasi" (
                id SERIAL PRIMARY KEY,
                id_rtl INTEGER NOT NULL,
                id_user INTEGER NOT NULL,
                progress INTEGER,
                catatan TEXT,
                status VARCHAR(50),

                FOREIGN KEY (id_rtl) REFERENCES spi_rtl(id) ON DELETE CASCADE,
                FOREIGN KEY (id_user) REFERENCES um_user(um_id) ON DELETE CASCADE
            );

            CREATE INDEX IF NOT EXISTS idx_spi_rtl_verifikasi_id_rtl ON jaga.spi_rtl_verifikasi (id_rtl);
            CREATE INDEX IF NOT EXISTS idx_spi_rtl_verifikasi_id_user ON jaga.spi_rtl_verifikasi (id_user);
        ');
    }

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

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