<?php 

use Phalcon\Db\Column;
use Phalcon\Db\Index;
use Phalcon\Db\Reference;
use Phalcon\Mvc\Model\Migration;

class CreateViewProdukDiskusiMigration_304 extends Migration
{
    /**
     * Run the migrations
     *
     * @return void
     */
    public function up()
    {
        self::$connection->execute("
            CREATE OR REPLACE VIEW jaga.view_produk_diskusi AS 
            SELECT
                CAST(npsn as VARCHAR) as id,
                nama,
                'SEKOLAH' as tipe
            FROM
                sekolah
            UNION ALL
            SELECT
                CAST(id as VARCHAR) as id,
                nama,
                'PERIZINAN' as tipe
            FROM
                perizinan
            UNION ALL
            SELECT
                CAST(id as VARCHAR) as id,
                nama,
                'INSTANSI' as tipe
            FROM
             instansi
            UNION ALL
            SELECT
                CAST(kode_rs as VARCHAR) as id,
                nama,
                'RUMAHSAKIT' as tipe
            FROM
                rumah_sakit
            UNION ALL
            SELECT
                CAST(kode_desa as VARCHAR) as id,
                nama_desa as nama,
                'DESA' as tipe
            FROM
                omspan_master_desa
            UNION ALL
            SELECT
                CAST(id_pt as VARCHAR) as id,
                nama,
                'KAMPUS' as tipe
            FROM
                perguruan_tinggi
            UNION ALL
            SELECT
                CAST(id as VARCHAR) as id,
                name as nama,
                'WILAYAH' as tipe
            FROM
                master_wilayah
            ;
        ");
    }

    /**
     * Reverse the migrations
     *
     * @return void
     */
    public function down()
    {
        self::$connection->execute("
            DROP VIEW IF EXISTS jaga.view_produk_diskusi;
        ");
    }

}