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

class CreateKorsupgahVerificationEditHistoryMigration_518 extends Migration {
    public function up() {
        self::$connection->execute('
            CREATE TABLE IF NOT EXISTS "jaga"."korsupgah_answer_verification_edit_history" (
                id SERIAL PRIMARY KEY,
                id_answer SERIAL NOT NULL,
                old_keterangan_verifikasi TEXT NOT NULL,
                old_verified_at TIMESTAMP(6),
                new_keterangan_verifikasi TEXT NOT NULL,
                new_verified_at TIMESTAMP(6) DEFAULT now()
            );
        ');

        self::$connection->execute('
            CREATE OR REPLACE FUNCTION insert_into_korsupgah_edit_history() RETURNS trigger AS $$
            BEGIN
                IF OLD.verified_at IS DISTINCT FROM NEW.verified_at THEN
                    INSERT INTO "jaga"."korsupgah_answer_verification_edit_history" (
                        id_answer,
                        old_keterangan_verifikasi,
                        old_verified_at,
                        new_keterangan_verifikasi,
                        new_verified_at
                    ) VALUES (
                        OLD.id,
                        OLD.keterangan_verifikasi, 
                        OLD.verified_at,
                        NEW.keterangan_verifikasi,
                        CURRENT_TIMESTAMP
                    );
                END IF;
                RETURN NEW;
            END;
            $$ LANGUAGE plpgsql;
        ');

        self::$connection->execute('
            CREATE TRIGGER after_update_verification_insert_into_korsupgah_history
            AFTER UPDATE OF keterangan_verifikasi
            ON "jaga"."korsupgah_answer"
            FOR EACH ROW
            WHEN (
                    old.keterangan_verifikasi is not null AND
                    old.verified_at is not null
                 )
            EXECUTE PROCEDURE insert_into_korsupgah_edit_history();
        ');
    }

    public function down() {
        self::$connection->execute('
            DROP TRIGGER IF EXISTS after_update_verification_insert_into_korsupgah_history ON "jaga"."korsupgah_answer";
        ');

        self::$connection->execute('
            DROP FUNCTION IF EXISTS insert_into_korsupgah_edit_history();
        ');

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

/*


*/