<?php

use Phalcon\Mvc\Model\Migration;
class Stranaspk2023Migration_416 extends Migration {
    public function up()
    {
        self::$connection->execute("
            CREATE SEQUENCE jaga.str2023_target_id_seq
                INCREMENT 1
                START 1
                MINVALUE 1
                MAXVALUE 9223372036854775807
                CACHE 1;
        
            CREATE TABLE IF NOT EXISTS jaga.str2023_target
            (
            	id integer not null primary key DEFAULT NEXTVAL('jaga.str2023_target_id_seq'::regclass),
		        id_periode INTEGER NOT NULL,
                id_level1 INTEGER,
                id_level2 INTEGER,
                id_level3 INTEGER,
                id_level4 INTEGER,
                nama text not null,
                start_triwulan integer default 1,
                bobot NUMERIC(10,2) DEFAULT NULL,
                is_forced_complete BOOLEAN DEFAULT FALSE,
                created_by integer not null,
                updated_by integer,
                deleted_by integer,
                created_at timestamp without time zone not null default now(),
                updated_at timestamp without time zone,
                deleted_at timestamp without time zone
            );
            
            CREATE SEQUENCE jaga.str2023_target_instansi_id_seq
                INCREMENT 1
                START 1
                MINVALUE 1
                MAXVALUE 9223372036854775807
                CACHE 1;

            CREATE TABLE IF NOT EXISTS jaga.str2023_target_instansi
            (
                id integer not null primary key DEFAULT NEXTVAL('jaga.str2023_target_instansi_id_seq'::regclass),
                id_target integer not null,
                id_instansi integer not null,
                CONSTRAINT fk_str2023_target_instansi_id_target
                	FOREIGN KEY(id_target)
                		REFERENCES jaga.str2023_target(id),
                CONSTRAINT fk_str2023_target_instansi_id_instansi
                        FOREIGN KEY(id_instansi)
                            REFERENCES jaga.instansi(id)
            );

            CREATE SEQUENCE jaga.str2023_target_master_id_seq
                INCREMENT 1
                START 1
                MINVALUE 1
                MAXVALUE 9223372036854775807
                CACHE 1;
            
            CREATE TABLE IF NOT EXISTS jaga.str2023_target_master
            (
            	id integer not null primary key DEFAULT NEXTVAL('jaga.str2023_target_master_id_seq'::regclass),
                id_target_instansi integer NOT NULL,
                id_triwulan integer NOT NULL,
                created_by integer NOT NULL,
                nilai NUMERIC(10,2) DEFAULT NULL,
                keterangan text DEFAULT NULL,
        		keterangan_verifikasi text DEFAULT NULL,
                updated_by integer,
                deleted_by integer,
                created_at timestamp without time zone NOT NULL default now(),
                updated_at timestamp without time zone,
                deleted_at timestamp without time zone,
                CONSTRAINT fk_str2023_target_master_id_target_instansi
                	FOREIGN KEY(id_target_instansi)
                		REFERENCES jaga.str2023_target_instansi(id)
            );
            
            CREATE SEQUENCE jaga.str2023_target_master_attachment_id_seq
                INCREMENT 1
                START 1
                MINVALUE 1
                MAXVALUE 9223372036854775807
                CACHE 1;
            
            CREATE TABLE IF NOT EXISTS jaga.str2023_target_master_attachment (
            	id integer not null primary key DEFAULT NEXTVAL('jaga.str2023_target_master_attachment_id_seq'::regclass),
            	id_master INTEGER NOT NULL,
            	is_verifikator BOOLEAN NOT NULL DEFAULT FALSE,
            	id_attachment INTEGER,
            	CONSTRAINT fk_str2023_id_master
                	FOREIGN KEY(id_master)
                		REFERENCES jaga.str2023_target_master(id)
            );
        ");
    }

    public function down()
    {
        self::$connection->execute("
        	DROP TABLE IF EXISTS jaga.str2023_target_master_attachment;
        	DROP SEQUENCE IF EXISTS jaga.str2023_target_master_attachment_id_seq;
            DROP TABLE IF EXISTS jaga.str2023_target_master;
        	DROP SEQUENCE IF EXISTS jaga.str2023_target_master_id_seq;
            DROP TABLE IF EXISTS jaga.str2023_target_instansi;
            DROP SEQUENCE IF EXISTS jaga.str2023_target_instansi_id_seq;
        	DROP TABLE IF EXISTS jaga.str2023_target;
        	DROP SEQUENCE IF EXISTS jaga.str2023_target_id_seq;
        ");
    }
}