<?php

use Phalcon\Mvc\Model\Migration;

class CreateMcpAssetTablesMigration_503 extends Migration {
    public function up() {
        $query = "
            CREATE SEQUENCE IF NOT EXISTS jaga.aset_sertifikasi_new_id_seq
                START WITH 1
                INCREMENT BY 1
                NO MINVALUE
                NO MAXVALUE
                CACHE 1;

            CREATE TABLE IF NOT EXISTS jaga.aset_sertifikasi_new (
                id integer NOT NULL default nextval('jaga.aset_sertifikasi_new_id_seq'::regclass),
                instansi_id integer,
                tahun integer,
                periode integer,
                total_luas_aset integer NULL,
                luas_aset_bersertifikat integer NULL,
                luas_target_sertifikat integer NULL,
                jumlah_sertifikat integer NULL,
                jumlah_sertifikat_diproses integer NULL,
                nilai numeric(18,2) NULL,
                anggaran numeric(18,2) NULL,
                keterangan character varying(255),
                created_by integer,
                created_at timestamp(6) without time zone DEFAULT now(),
                updated_by integer,
                updated_at timestamp(6) without time zone NULL,
                deleted_by integer,
                deleted_at timestamp(6) without time zone NULL
            );
            
            CREATE SEQUENCE IF NOT EXISTS jaga.aset_penertiban_new_id_seq
                START WITH 1
                INCREMENT BY 1
                NO MINVALUE
                NO MAXVALUE
                CACHE 1;
            
            CREATE TABLE IF NOT EXISTS jaga.aset_penertiban_new (
                id integer NOT NULL default nextval('jaga.aset_penertiban_new_id_seq'::regclass),
                instansi_id integer,
                tahun integer,
                periode integer,
                jumlah_perumahan integer,
                jumlah_bidang integer,
                luas numeric(10,2),
                uraian character varying(255),
                unit_tanah integer,
                luas_tanah numeric(10,1),
                nilai_tanah numeric(18,2),
                unit_bangunan integer,
                luas_bangunan numeric(10,1),
                nilai_bangunan numeric(18,2),
                unit_kendaraan integer,
                nilai_kendaraan numeric(18,2),
                aset_bermasalah numeric(18,2),
                kronologi character varying(255),
                unit_tanah_bermasalah integer,
                luas_tanah_bermasalah numeric(10,1),
                nilai_tanah_bermasalah numeric(18,2),
                penyelesaian_tanah character varying(255),
                unit_bangunan_bermasalah integer,
                luas_bangunan_bermasalah numeric(10,1),
                nilai_bangunan_bermasalah numeric(18,2),
                penyelesaian_bangunan character varying(255),
                unit_kendaraan_bermasalah integer,
                luas_kendaraan_bermasalah numeric(10,1),
                nilai_kendaraan_bermasalah numeric(18,2),
                penyelesaian_kendaraan character varying(255),
                created_by integer,
                created_at timestamp(6) without time zone DEFAULT now(),
                updated_by integer,
                updated_at timestamp(6) without time zone NULL,
                deleted_by integer,
                deleted_at timestamp(6) without time zone NULL
            );

            CREATE SEQUENCE IF NOT EXISTS jaga.aset_penertiban_sarpras_new_id_seq
                START WITH 1
                INCREMENT BY 1
                NO MINVALUE
                NO MAXVALUE
                CACHE 1;
            
            CREATE TABLE IF NOT EXISTS jaga.aset_penertiban_sarpras_new (
                id integer NOT NULL default nextval('jaga.aset_penertiban_sarpras_new_id_seq'::regclass),
                instansi_id integer,
                tahun integer,
                periode integer,
                jumlah_perumahan integer,
                jumlah_bidang integer,
                luas numeric(10,2),
                nilai numeric(18,2)
            );
        ";
        self::$connection->execute($query);
    }

    public function down() {
        $query = "
            DROP TABLE IF EXISTS jaga.aset_sertifikasi_new;
            DROP TABLE IF EXISTS jaga.aset_penertiban_new;
            DROP TABLE IF EXISTS jaga.aset_penertiban_sarpras_new;
            DROP SEQUENCE IF EXISTS jaga.aset_sertifikasi_new_id_seq;
            DROP SEQUENCE IF EXISTS jaga.aset_penertiban_new_id_seq;
            DROP SEQUENCE IF EXISTS jaga.aset_penertiban_sarpras_new_id_seq;
        ";
        self::$connection->execute($query);
    }
}