<?php

use Phalcon\Mvc\Model\Migration;

class AlterPerguruanTinggiMigration_455 extends Migration
{
    public function up()
    {
        self::$connection->execute("
            DELETE FROM
                jaga.perguruan_tinggi a USING jaga.perguruan_tinggi b
            WHERE
                a.kode = b.kode
                AND a.id_pt < b.id_pt
                AND a.id_pt NOT IN (SELECT alokasi_boptn_bpptnbh.perguruan_tinggi_id FROM alokasi_boptn_bpptnbh)
                AND a.id_pt NOT IN (SELECT pendapatan_belanja_ptnbh.perguruan_tinggi_id FROM pendapatan_belanja_ptnbh)
            ;
            
            DELETE FROM
                jaga.perguruan_tinggi a USING jaga.perguruan_tinggi b
            WHERE
                a.kode = b.kode
                AND a.id_pt > b.id_pt
                AND a.id_pt NOT IN (SELECT alokasi_boptn_bpptnbh.perguruan_tinggi_id FROM alokasi_boptn_bpptnbh)
                AND a.id_pt NOT IN (SELECT pendapatan_belanja_ptnbh.perguruan_tinggi_id FROM pendapatan_belanja_ptnbh)
            ;
            
            DELETE FROM jaga.perguruan_tinggi WHERE kode IS NULL OR kode = '';
            
            ALTER TABLE jaga.perguruan_tinggi ADD CONSTRAINT unique_perguruan_tinggi_kode UNIQUE (kode);
            
            SELECT setval('jaga.perguruan_tinggi_id_pt_seq', COALESCE((SELECT MAX(id_pt)+1 FROM jaga.perguruan_tinggi), 1), false);
        ");
    }

    public function down()
    {
        self::$connection->execute("
            ALTER TABLE jaga.perguruan_tinggi DROP CONSTRAINT unique_perguruan_tinggi_kode;
        ");
    }
}