<?php

use Phalcon\Mvc\Model\Migration;

/**
 * Class KeluhanCovid_309
 *
 */
class KeluhanCovidMigration_309 extends Migration
{
    public function up()
    {
        $this->down();

        //NOTE: Update "Bantuan Sosial Covid-19" jadi "Covid-19" di table `produk`
        self::$connection->execute("
        UPDATE jaga.produk SET nama = 'Covid-19' WHERE nama = 'Bantuan Sosial Covid-19';
        ");

        //NOTE: Add column `type` pada table `topik_dikusi_bansos`
        self::$connection->execute("
        ALTER TABLE jaga.topik_diskusi_bansos ADD COLUMN type character varying(10);
        ");

        //NOTE: Update value pada field `type` untuk setiap row existing menjadi "BANSOS"
        self::$connection->execute("
        UPDATE jaga.topik_diskusi_bansos SET type = 'BANSOS';
        ");

        //NOTE: Update value pada field `deskripsi` Untuk `type` "BANSOS", sehingga memiliki Prefix "Bansos - "
        self::$connection->execute("
        UPDATE jaga.topik_diskusi_bansos SET deskripsi = CONCAT('Bansos - ', deskripsi) WHERE type = 'BANSOS';
        ");

        //NOTE: Add row pada table `topik_diskusi_bansos`, untuk topik "COVID"
        self::$connection->execute("
        INSERT INTO jaga.topik_diskusi_bansos (deskripsi, created_at, type)
        VALUES
        ('Covid-19 - Pelayanan dan penanganan COVID-19 (umum)', now(), 'COVID'),
        ('Covid-19 - Vaksinasi', now(), 'COVID'),
        ('Covid-19 - Fasilitas Kesehatan (Puskesmas dan RS Daerah)', now(), 'COVID'),
        ('Covid-19 - JKN dan BPJS', now(), 'COVID'),
        ('Covid-19 - Dugaan penyelewengan pada penanganan COVID-19', now(), 'COVID'),
        ('Covid-19 - Lainnya', now(), 'COVID');
        ");

        //NOTE: Add Scope "covid.dinkes_pemda"
        self::$connection->execute("
        INSERT INTO jaga.scopes (name, label, description, created_at)
        VALUES
        ('covid.dinkes_pemda', 'Dinas Kesehatan Pemda Untuk Keluhan Covid', 'Melihat dan Menindaklanjuti Keluhan Covid', now());
        ");

        //NOTE: Add Role "keluhan_covid_dinkes"
        self::$connection->execute("
        INSERT INTO jaga.roles (name, label, description, created_at)
        VALUES
        ('keluhan_covid_dinkes', 'Instansi TL Keluhan Covid', 'Melihat dan Menindaklanjuti Keluhan Covid', now());
        ");

        //NOTE: Add Roles_Scopes "keluhan_covid_dinkes", "covid.dinkes_pemda"
        self::$connection->execute("
        INSERT INTO jaga.roles_scopes (role_id, scope_id)
                SELECT r.id, s.id
                FROM jaga.roles r
                JOIN jaga.scopes s ON r.name = 'keluhan_covid_dinkes' and s.name = 'covid.dinkes_pemda' ON CONFLICT DO NOTHING;
        ");
    }

    public function down()
    {
        //NOTE: REVERT: Update "Covid-19" jadi "Bantuan Sosial Covid-19" di table `produk`
        self::$connection->execute("
        UPDATE jaga.produk SET nama = 'Bantuan Sosial Covid-19' WHERE nama = 'Covid-19';
        ");

        //NOTE: Delete Topik COVID
        self::$connection->execute("
        DELETE FROM jaga.topik_diskusi_bansos WHERE deskripsi LIKE 'Covid-19 - %';
        ");

        //NOTE: Update value pada field `deskripsi` Untuk `type` "BANSOS", sehingga menghapus  Prefix "Bansos - "
        self::$connection->execute("
        UPDATE jaga.topik_diskusi_bansos SET deskripsi = regexp_replace(deskripsi, 'Bansos - ', '') WHERE deskripsi LIKE 'Bansos - %';
        ");

        //NOTE: Remove column `type` pada table `topik_dikusi_bansos`
        self::$connection->execute("
        ALTER TABLE jaga.topik_diskusi_bansos DROP COLUMN IF EXISTS type;
        ");

        //NOTE: Remove Roles_Scopes "keluhan_covid_dinkes", "covid.dinkes_pemda"
        self::$connection->execute("
        DELETE 
            FROM
            	jaga.roles_scopes 
            WHERE
            	role_id = ( SELECT ID FROM jaga.roles WHERE NAME = 'keluhan_covid_dinkes' ) 
            	AND scope_id = ( SELECT ID FROM jaga.scopes WHERE NAME = 'covid.dinkes_pemda' );
        ");

        //NOTE: Remove Scope "covid.dinkes_pemda"
        self::$connection->execute("
        DELETE FROM jaga.scopes WHERE name = 'covid.dinkes_pemda';
        ");

        //NOTE: Remove Scope "covid.dinkes_pemda"
        self::$connection->execute("
        DELETE FROM jaga.roles WHERE name = 'keluhan_covid_dinkes';
        ");
    }
}