<?php

use Phalcon\Mvc\Model\Migration;

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

        self::$connection->execute('
            ALTER TABLE IF EXISTS "jaga"."pak_questionnaire"
                ADD COLUMN IF NOT EXISTS panduan_questionnaire TEXT;
            ALTER TABLE IF EXISTS "jaga"."pak_questionnaire"
                ADD COLUMN IF NOT EXISTS nama_dokumen VARCHAR(255);
            ALTER TABLE IF EXISTS "jaga"."pak_questionnaire"
                ADD COLUMN IF NOT EXISTS is_using_doc_type boolean;
        ');

        self::$connection->execute('
            CREATE TABLE IF NOT EXISTS "jaga"."pak_jenis_dokumen_questionnaire" (
                id SERIAL PRIMARY KEY,
                questionnaire_id INTEGER NOT NULL,
                jenis_dokumen VARCHAR(255) NOT NULL,
                is_using_doc_info boolean DEFAULT NULL, 
                created_at timestamp(6) DEFAULT now(),
                created_by INTEGER,
                updated_at timestamp(6),
                updated_by INTEGER,
                deleted_at timestamp(6),
                deleted_by INTEGER
            );
        ');

        self::$connection->execute('
            ALTER TABLE IF EXISTS "jaga"."pak_verifikasi_implementasi_attachment"
                ADD COLUMN IF NOT EXISTS jenis_dokumen_id INTEGER;
            ALTER TABLE IF EXISTS "jaga"."pak_verifikasi_implementasi_attachment"
                ADD COLUMN IF NOT EXISTS tahun INTEGER;
            ALTER TABLE IF EXISTS "jaga"."pak_verifikasi_implementasi_attachment"
                ADD COLUMN IF NOT EXISTS nomor INTEGER;
            ALTER TABLE IF EXISTS "jaga"."pak_verifikasi_implementasi_attachment"
                ADD COLUMN IF NOT EXISTS tentang VARCHAR(255);
            ALTER TABLE IF EXISTS "jaga"."pak_verifikasi_implementasi_attachment"
                ADD COLUMN IF NOT EXISTS tanggal_ttd timestamp;
        ');

        self::$connection->execute('
            ALTER TABLE IF EXISTS "jaga"."pak_implementasi"
                DROP COLUMN IF EXISTS tahun;
            ALTER TABLE IF EXISTS "jaga"."pak_implementasi"
                DROP COLUMN IF EXISTS nomor;
            ALTER TABLE IF EXISTS "jaga"."pak_implementasi"
                DROP COLUMN IF EXISTS tentang;
            ALTER TABLE IF EXISTS "jaga"."pak_implementasi"
                DROP COLUMN IF EXISTS tanggal_ttd;
        ');

        self::$connection->execute('
            ALTER TABLE IF EXISTS "jaga"."pak_jenis_dokumen_questionnaire" 
                DROP CONSTRAINT IF EXISTS questionnaire_id_fk_1, 
                ADD CONSTRAINT questionnaire_id_fk_1 
                FOREIGN KEY (questionnaire_id) 
                REFERENCES pak_questionnaire (id)
                ON UPDATE CASCADE ON DELETE CASCADE;

            ALTER TABLE IF EXISTS "jaga"."pak_verifikasi_implementasi" 
                DROP CONSTRAINT IF EXISTS implementasi_id_fk_1, 
                ADD CONSTRAINT implementasi_id_fk_1 
                FOREIGN KEY (implementasi_id) 
                REFERENCES pak_implementasi (id)
                ON UPDATE CASCADE ON DELETE CASCADE;

            ALTER TABLE IF EXISTS "jaga"."pak_verifikasi_implementasi" 
                DROP CONSTRAINT IF EXISTS questionnaire_id_fk_1, 
                ADD CONSTRAINT questionnaire_id_fk_1 
                FOREIGN KEY (questionnaire_id) 
                REFERENCES pak_questionnaire (id)
                ON UPDATE CASCADE ON DELETE CASCADE;

            ALTER TABLE IF EXISTS "jaga"."pak_verifikasi_implementasi_attachment" 
                DROP CONSTRAINT IF EXISTS verifikasi_id_fk_1, 
                ADD CONSTRAINT verifikasi_id_fk_1 
                FOREIGN KEY (verifikasi_id) 
                REFERENCES pak_verifikasi_implementasi (id)
                ON UPDATE CASCADE ON DELETE CASCADE;

            ALTER TABLE IF EXISTS "jaga"."pak_verifikasi_implementasi_attachment" 
                DROP CONSTRAINT IF EXISTS attachment_id_fk_1, 
                ADD CONSTRAINT attachment_id_fk_1 
                FOREIGN KEY (attachment_id) 
                REFERENCES attachments (id)
                ON UPDATE CASCADE ON DELETE CASCADE;
        ');
    }

    public function down()
    {
        self::$connection->execute('
            ALTER TABLE IF EXISTS "jaga"."pak_questionnaire"
                DROP COLUMN IF EXISTS is_using_doc_type;
            ALTER TABLE IF EXISTS "jaga"."pak_questionnaire"
                DROP COLUMN IF EXISTS panduan_questionnaire;
            ALTER TABLE IF EXISTS "jaga"."pak_questionnaire"
                DROP COLUMN IF EXISTS nama_dokumen;
        ');

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

        self::$connection->execute('
            ALTER TABLE IF EXISTS "jaga"."pak_verifikasi_implementasi_attachment"
                DROP COLUMN IF EXISTS tahun;
            ALTER TABLE IF EXISTS "jaga"."pak_verifikasi_implementasi_attachment"
                DROP COLUMN IF EXISTS nomor;
            ALTER TABLE IF EXISTS "jaga"."pak_verifikasi_implementasi_attachment"
                DROP COLUMN IF EXISTS tentang;
            ALTER TABLE IF EXISTS "jaga"."pak_verifikasi_implementasi_attachment"
                DROP COLUMN IF EXISTS tanggal_ttd;
        ');

        self::$connection->execute('
            ALTER TABLE IF EXISTS "jaga"."pak_jenis_dokumen_questionnaire" 
                DROP CONSTRAINT IF EXISTS questionnaire_id_fk_1;
            ALTER TABLE IF EXISTS "jaga"."pak_verifikasi_implementasi" 
                DROP CONSTRAINT IF EXISTS implementasi_id_fk_1;
            ALTER TABLE IF EXISTS "jaga"."pak_verifikasi_implementasi" 
                DROP CONSTRAINT IF EXISTS questionnaire_id_fk_1;
            ALTER TABLE IF EXISTS "jaga"."pak_verifikasi_implementasi_attachment" 
                DROP CONSTRAINT IF EXISTS verifikasi_id_fk_1;
            ALTER TABLE IF EXISTS "jaga"."pak_verifikasi_implementasi_attachment" 
                DROP CONSTRAINT IF EXISTS attachment_id_fk_1;
        ');
    }
}