<?php

use Phalcon\Mvc\Model\Migration;

class AlterTableDiskusiLikeMigration_546 extends Migration
{
    public function up()
    {
        self::$connection->execute("
            DELETE FROM jaga.diskusi_like WHERE id IN (SELECT id FROM (SELECT id, user_id, diskusi_id, count(1)over(partition by user_id, diskusi_id) as total, row_number()over(partition by user_id, diskusi_id) as idx FROM jaga.diskusi_like) a WHERE total > 1 AND idx <> 1 ORDER BY user_id, diskusi_id, idx desc);
            DELETE FROM jaga.diskusi_comment_like WHERE id IN (SELECT id FROM (SELECT id, user_id, comment_id, count(1)over(partition by user_id, comment_id) as total, row_number()over(partition by user_id, comment_id) as idx FROM jaga.diskusi_comment_like) a WHERE total > 1 AND idx <> 1 ORDER BY user_id, comment_id, idx desc);
            ALTER TABLE jaga.diskusi_like 
                ADD CONSTRAINT diskusi_like_uniques_diskusi_id_user_id UNIQUE (user_id, diskusi_id);
            ALTER TABLE jaga.diskusi_comment_like 
                ADD CONSTRAINT diskusi_comment_like_uniques_comment_id_user_id UNIQUE (user_id, comment_id);
        ");
    }

    public function down()
    {
        self::$connection->execute("
            ALTER TABLE jaga.diskusi_like 
                DROP CONSTRAINT diskusi_like_uniques_diskusi_id_user_id;
             ALTER TABLE jaga.diskusi_comment_like 
                DROP CONSTRAINT diskusi_comment_like_uniques_comment_id_user_id;
        ");
    }
}