<?php

use Phalcon\Mvc\Model\Migration;
class AnggaranStuntingMigration_453 extends Migration
{
    public function up()
    {
        $this->down();

        self::$connection->execute('
            CREATE TABLE IF NOT EXISTS "jaga"."stunting_anggaran"
            (
            "id" bigserial NOT NULL,
            "kodepemda" character varying(6) NOT NULL,
            "namasubkegiatan" text,
            "standarfungsi" character varying(255),
            "standarsubkegiatan" text,
            "tahun" integer NOT NULL,
            "nilai" numeric NOT NULL,
            "created_at" timestamp(6) DEFAULT now(),
            PRIMARY KEY (id)
            )
            WITH (OIDS = FALSE);
        ');

        self::$connection->execute('
            CREATE TABLE IF NOT EXISTS "jaga"."stunting_realisasi"
            (
            "id" bigserial NOT NULL,
            "id_anggaran" bigint, 
            "kodepemda" character varying(6) NOT NULL,
            "namasubkegiatan" text,
            "standarfungsi" character varying(255),
            "standarsubkegiatan" text,
            "tahun" integer NOT NULL,
            "nilai" numeric NOT NULL,
            "created_at" timestamp(6) DEFAULT now(),
            PRIMARY KEY (id)
            )
            WITH (OIDS = FALSE);
        ');

        $sql = file_get_contents(__DIR__ . DIRECTORY_SEPARATOR . 'stunting_anggaran.sql');
        self::$connection->execute($sql);

        $sql = file_get_contents(__DIR__ . DIRECTORY_SEPARATOR . 'stunting_realisasi.sql');
        self::$connection->execute($sql);

        self::$connection->execute('
            UPDATE "jaga"."stunting_realisasi"
            SET id_anggaran=sa.id
            FROM (SELECT id, kodepemda, namasubkegiatan, standarfungsi, standarsubkegiatan, tahun
                  FROM  "jaga"."stunting_anggaran") AS sa
            WHERE 
            "jaga"."stunting_realisasi".kodepemda = sa.kodepemda
            AND regexp_replace("jaga"."stunting_realisasi".namasubkegiatan, E\'\\\\s+\', \'\', \'g\') = regexp_replace(sa.namasubkegiatan, E\'\\\\s+\', \'\', \'g\')
            AND regexp_replace("jaga"."stunting_realisasi".standarfungsi, E\'\\\\s+\', \'\', \'g\') = regexp_replace(sa.standarfungsi, E\'\\\\s+\', \'\', \'g\')
            AND regexp_replace("jaga"."stunting_realisasi".standarsubkegiatan, E\'\\\\s+\', \'\', \'g\') = regexp_replace(sa.standarsubkegiatan, E\'\\\\s+\', \'\', \'g\')
            AND "jaga"."stunting_realisasi".tahun = sa.tahun
          ');

        self::$connection->execute("
          CREATE VIEW jaga.view_stunting AS
            (
            SELECT
                sa.id AS id_anggaran,
                null AS ids_realisasi,
                    sa.kodepemda,
                    sa.namasubkegiatan,
                    sa.standarfungsi,
                    sa.standarsubkegiatan,
                    sa.tahun, 
                    sa.nilai AS nilai_anggaran,
                    null AS nilai_realisasi,
                    CASE WHEN sa.nilai = 0 THEN null ELSE ROUND((0/sa.nilai), 2) END AS realisasi_thd_anggaran
            FROM stunting_anggaran sa
            LEFT JOIN stunting_realisasi sr ON sa.id = sr.id_anggaran
            WHERE sr.id IS NULL
            
            UNION ALL 
            
            SELECT
                sa.id AS id_anggaran,
                    STRING_AGG(sr.id::text, ',') AS ids_realisasi,
                    sa.kodepemda,
                    sa.namasubkegiatan,
                    sa.standarfungsi,
                    sa.standarsubkegiatan,
                    sa.tahun, 
                    sa.nilai AS nilai_anggaran,
                    SUM(sr.nilai) AS nilai_realisasi,
                    CASE WHEN sa.nilai = 0 THEN null ELSE ROUND((SUM(sr.nilai)/sa.nilai)*100, 2) END AS realisasi_thd_anggaran
            FROM stunting_anggaran sa
            INNER JOIN stunting_realisasi sr ON sa.id = sr.id_anggaran
            GROUP BY 
            sa.id,
                    sa.kodepemda,
                    sa.namasubkegiatan,
                    sa.standarfungsi,
                    sa.standarsubkegiatan,
                    sa.tahun, 
                    sa.nilai
            
            UNION ALL
            
            SELECT
                null AS id_anggaran,
                    sr.id::text AS ids_realisasi,
                    sr.kodepemda,
                    sr.namasubkegiatan,
                    sr.standarfungsi,
                    sr.standarsubkegiatan,
                    sr.tahun, 
                    null AS nilai_anggaran,
                    sr.nilai AS nilai_realisasi,
                    null AS realisasi_thd_anggaran
            FROM stunting_realisasi sr
            LEFT JOIN stunting_anggaran sa ON sa.id = sr.id_anggaran
            WHERE sa.id IS NULL    
            );
          "
        );
    }

    public function down()
    {
        self::$connection->execute("
            DROP VIEW IF EXISTS jaga.view_stunting;
            DROP TABLE IF EXISTS jaga.stunting_realisasi;
            DROP TABLE IF EXISTS jaga.stunting_anggaran;
        ");
    }
}