<?php

use Phalcon\Mvc\Model\Migration;

class CreateKemendesBumdesMigration_554 extends Migration
{
    public function up()
    {
        self::$connection->execute("CREATE TABLE IF NOT EXISTS jaga.kemendes_bumdes (
            id bigserial primary key,
            bumdes_id character varying(100) NOT NULL,
            type_bumdes character varying(100) NOT NULL,
            nama_bumdes character varying(255) NOT NULL,
            nama_full_bumdes character varying(255) NOT NULL,
            id_provinsi integer NOT NULL,
            name_provinsi character varying(255) NOT NULL,
            id_kabupaten integer NOT NULL,
            name_kabupaten character varying(255) NOT NULL,
            id_kecamatan integer NOT NULL,
            name_kecamatan character varying(255) NOT NULL,
            id_desa character varying(12) NOT NULL,
            name_desa character varying(255) NOT NULL,
            alamat_lengkap character varying(255),
            email character varying(255),
            no_hp_kepala_desa character varying(30),
            website character varying(100),
            status character varying(100),
            jenis_usaha text,
            created_at timestamp without time zone NOT NULL DEFAULT now(),
            updated_at timestamp without time zone NOT NULL DEFAULT now(),
            deleted_at timestamp without time zone);

            CREATE INDEX IF NOT EXISTS idx_kemendes_bumdes_id_provinsi ON jaga.kemendes_bumdes USING btree (id_provinsi);
            CREATE INDEX IF NOT EXISTS idx_kemendes_bumdes_id_kabupaten ON jaga.kemendes_bumdes USING btree (id_kabupaten);
        ");

        self::$connection->execute("CREATE TABLE IF NOT EXISTS jaga.kemendes_bumdes_count (
            id serial primary key,
            id_provinsi integer NOT NULL,
            name_provinsi character varying(255) NOT NULL,
            id_kabupaten integer NOT NULL,
            name_kabupaten character varying(255) NOT NULL,
            bumdes_mendaftar_nama integer not null,
            bumdes_perbaikan_nama integer not null,
            bumdes_verifikasi_nama integer not null,
            bumdes_mendaftar_badan_hukum integer not null,
            bumdes_perbaikan_badan_hukum integer not null,
            bumdes_verifikasi_badan_hukum integer not null,
            bumdesma_mendaftar_nama integer not null,
            bumdesma_perbaikan_nama integer not null,
            bumdesma_verifikasi_nama integer not null,
            bumdesma_mendaftar_badan_hukum integer not null,
            bumdesma_perbaikan_badan_hukum integer not null,
            bumdesma_verifikasi_badan_hukum integer not null,
            created_at timestamp without time zone NOT NULL DEFAULT now(),
            updated_at timestamp without time zone NOT NULL DEFAULT now(),
            deleted_at timestamp without time zone);

            CREATE INDEX IF NOT EXISTS idx_kemendes_bumdes_count_id_provinsi ON jaga.kemendes_bumdes_count USING btree (id_provinsi);
            CREATE INDEX IF NOT EXISTS idx_kemendes_bumdes_count_id_kabupaten ON jaga.kemendes_bumdes_count USING btree (id_kabupaten);
        ");

        self::$connection->execute("CREATE TABLE IF NOT EXISTS jaga.kemendes_bumdes_pemda (
            id serial primary key,
            id_provinsi integer NOT NULL,
            id_kabupaten integer NOT NULL,
            last_updated_bumdes timestamp without time zone,
            last_updated_bumdes_count timestamp without time zone
            );
        ");
    }

    public function down()
    {
        self::$connection->execute("
            DROP TABLE IF EXISTS jaga.kemendes_bumdes;
            DROP TABLE IF EXISTS jaga.kemendes_bumdes_count;
            DROP TABLE IF EXISTS jaga.kemendes_bumdes_pemda;
        ");
    }
}