<?php

use Phalcon\Mvc\Model\Migration;

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

        self::$connection->execute('
            CREATE TABLE IF NOT EXISTS "jaga"."pak_metode_isian_sekolah" (
                id SERIAL PRIMARY KEY,
                nama VARCHAR(100) NOT NULL,
                attributes json,
                created_at timestamp(6) DEFAULT now(),
                created_by INTEGER,
                updated_at timestamp(6),
                updated_by INTEGER,
                deleted_at timestamp(6),
                deleted_by INTEGER
            );

            CREATE TABLE IF NOT EXISTS "jaga"."pak_isian_sekolah" (
                id SERIAL PRIMARY KEY,
                sekolah_npsn VARCHAR(100) NOT NULL,
                tahun_ajaran CHAR(9),
                deskripsi TEXT,
                status smallint DEFAULT 2,
                created_at timestamp(6) DEFAULT now(),
                created_by INTEGER,
                updated_at timestamp(6),
                updated_by INTEGER,
                deleted_at timestamp(6),
                deleted_by INTEGER,
                verified_at timestamp(6),
                verified_by INTEGER
            );

            CREATE TABLE IF NOT EXISTS "jaga"."pak_isian_sekolah_detail" (
                id SERIAL PRIMARY KEY,
                isian_id VARCHAR(100) NOT NULL,
                metode_id INTEGER NOT NULL,
                mata_pelajaran VARCHAR(255),
                kelas VARCHAR(100),
                nama_kegiatan VARCHAR(100),
                deskripsi TEXT,
                link VARCHAR(255),
                created_at timestamp(6) DEFAULT now(),
                created_by INTEGER,
                updated_at timestamp(6),
                updated_by INTEGER,
                deleted_at timestamp(6),
                deleted_by INTEGER
            );

            CREATE TABLE IF NOT EXISTS "jaga"."pak_isian_sekolah_attachment" (
                id SERIAL PRIMARY KEY,
                isian_detail_id INTEGER NOT NULL,
                attachment_id INTEGER NOT NULL,
                tipe smallint NOT 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("
        //     INSERT INTO jaga.pak_metode_isian_sekolah (nama, attributes) VALUES 
        //         ('Insersi Mata Pelajaran', '{nama:asd}'),
        //         ON CONFLICT DO NOTHING;

        // ");

    }

    public function down()
    {
        self::$connection->execute('
            DROP TABLE IF EXISTS "jaga"."pak_sekolah";
            DROP TABLE IF EXISTS "jaga"."pak_isian_sekolah";
            DROP TABLE IF EXISTS "jaga"."pak_isian_sekolah_detail";
            DROP TABLE IF EXISTS "jaga"."pak_isian_sekolah_attachment";
            DROP TABLE IF EXISTS "jaga"."pak_isian_sekolah_detail_attachment";
        ');
    }
}