<?php

use Phalcon\Mvc\Model\Migration;

class CreatePresignedAttachmentsMigration_536 extends Migration {
    public function up()
    {
        $this->down();
        self::$connection->execute("
            ALTER TABLE jaga.um_user 
                ADD COLUMN IF NOT EXISTS presigned_url text,
                ADD COLUMN IF NOT EXISTS presigned_exp timestamp without time zone;

            DROP VIEW IF EXISTS jaga.view_trending_berita;
            DROP VIEW IF EXISTS jaga.view_hot_diskusi;

            CREATE VIEW jaga.view_trending_berita AS (
             SELECT d.id,
                    d.title,
                    d.title_en,
                    d.content,
                    d.content_en,
                    d.uuid,
                    d.created_at,
                    d.updated_at,
                    d.release_date,
                    d.user_id,
                    u.is_banned,
                    u.um_user_name AS nama,
                    u.uuid_user,
                    u.jenis_kelamin,
                    p.nama AS nama_produk,
                    d.is_deleted,
                    d.type,
                    u.foto,
                    d.is_anonymous,
                    COALESCE(dc.comment_count, 0::bigint) AS total_comment,
                    COALESCE(dl.like_count, 0::bigint) AS total_like,
                    COALESCE(ds.share_count, 0::bigint) AS total_share,
                    COALESCE(COALESCE(dc.comment_count, 0::bigint) + COALESCE(dl.like_count, 0::bigint) + COALESCE(ds.share_count, 0::bigint), 0::bigint) AS hotness,
                    u.checkmark_verified,
                    u.um_id, u.presigned_url, u.presigned_exp,
                    p.id_index AS id_produk
                 FROM jaga.diskusi d
                     JOIN jaga.um_user u ON d.user_id = u.um_id
                     JOIN jaga.kategori_diskusi kd ON d.kategori_diskusi_id = kd.id
                     JOIN jaga.produk p ON p.id_index = kd.produk_id
                     LEFT JOIN ( SELECT diskusi_comment.diskusi_id,
                                    count(diskusi_comment.diskusi_id) AS comment_count
                                 FROM jaga.diskusi_comment
                                WHERE diskusi_comment.created_at > (now() - '8 days'::interval) AND diskusi_comment.created_at <= (now()::date + '00:00:01'::interval)
                                GROUP BY diskusi_comment.diskusi_id) dc ON d.id = dc.diskusi_id
                     LEFT JOIN ( SELECT diskusi_like.diskusi_id,
                                    count(diskusi_like.diskusi_id) AS like_count
                                 FROM jaga.diskusi_like
                                WHERE diskusi_like.created_at > (now() - '8 days'::interval) AND diskusi_like.created_at <= (now()::date + '00:00:01'::interval)
                                GROUP BY diskusi_like.diskusi_id) dl ON d.id = dl.diskusi_id
                     LEFT JOIN ( SELECT diskusi_share.diskusi_id,
                                    count(diskusi_share.diskusi_id) AS share_count
                                 FROM jaga.diskusi_share
                                WHERE diskusi_share.created_at > (now() - '8 days'::interval) AND diskusi_share.created_at <= (now()::date + '00:00:01'::interval)
                                GROUP BY diskusi_share.diskusi_id) ds ON d.id = ds.diskusi_id
                WHERE d.type = 1 AND d.is_deleted = 0
                ORDER BY (COALESCE(COALESCE(dc.comment_count, 0::bigint) + COALESCE(dl.like_count, 0::bigint) + COALESCE(ds.share_count, 0::bigint), 0::bigint)) DESC
            );
                   
            CREATE VIEW jaga.view_hot_diskusi AS (
                SELECT d.id,
                    d.title,
                    d.title_en,
                    d.content,
                    d.content_en,
                    d.uuid,
                    d.created_at,
                    d.updated_at,
                    d.release_date,
                    d.user_id,
                    u.is_banned,
                    u.um_user_name AS nama,
                    u.uuid_user,
                    u.jenis_kelamin,
                    p.nama AS nama_produk,
                    d.is_deleted,
                    d.type,
                    u.foto,
                    d.is_anonymous,
                    COALESCE(dc.comment_count, 0::bigint) AS total_comment,
                    COALESCE(dl.like_count, 0::bigint) AS total_like,
                    COALESCE(ds.share_count, 0::bigint) AS total_share,
                    COALESCE(COALESCE(dc.comment_count, 0::bigint) + COALESCE(dl.like_count, 0::bigint) + COALESCE(ds.share_count, 0::bigint), 0::bigint) AS hotness,
                    u.checkmark_verified,
                    u.um_id, u.presigned_url, u.presigned_exp,
                    p.id_index AS id_produk
                 FROM jaga.diskusi d
                     JOIN jaga.um_user u ON d.user_id = u.um_id
                     JOIN jaga.kategori_diskusi kd ON d.kategori_diskusi_id = kd.id
                     JOIN jaga.produk p ON p.id_index = kd.produk_id
                     LEFT JOIN ( SELECT diskusi_comment.diskusi_id,
                                    count(diskusi_comment.diskusi_id) AS comment_count
                                 FROM jaga.diskusi_comment
                                WHERE diskusi_comment.created_at > (now() - '1 mon 1 day'::interval)
                                GROUP BY diskusi_comment.diskusi_id) dc ON d.id = dc.diskusi_id
                     LEFT JOIN ( SELECT diskusi_like.diskusi_id,
                                    count(diskusi_like.diskusi_id) AS like_count
                                 FROM jaga.diskusi_like
                                WHERE diskusi_like.created_at > (now() - '1 mon 1 day'::interval)
                                GROUP BY diskusi_like.diskusi_id) dl ON d.id = dl.diskusi_id
                     LEFT JOIN ( SELECT diskusi_share.diskusi_id,
                                    count(diskusi_share.diskusi_id) AS share_count
                                 FROM jaga.diskusi_share
                                WHERE diskusi_share.created_at > (now() - '1 mon 1 day'::interval)
                                GROUP BY diskusi_share.diskusi_id) ds ON d.id = ds.diskusi_id
                WHERE d.type = 0 AND d.is_deleted = 0
                ORDER BY (COALESCE(COALESCE(dc.comment_count, 0::bigint) + COALESCE(dl.like_count, 0::bigint) + COALESCE(ds.share_count, 0::bigint), 0::bigint)) DESC
             );
        ");
    }

    public function down()
    {
        self::$connection->execute("
            DROP VIEW IF EXISTS jaga.view_trending_berita;
            DROP VIEW IF EXISTS jaga.view_hot_diskusi;
            ALTER TABLE jaga.um_user DROP COLUMN IF EXISTS presigned_url, DROP COLUMN IF EXISTS presigned_exp;
        ");

        self::$connection->execute("
            CREATE VIEW jaga.view_trending_berita AS (
             SELECT d.id,
                    d.title,
                    d.title_en,
                    d.content,
                    d.content_en,
                    d.uuid,
                    d.created_at,
                    d.updated_at,
                    d.release_date,
                    d.user_id,
                    u.is_banned,
                    u.um_user_name AS nama,
                    u.uuid_user,
                    u.jenis_kelamin,
                    p.nama AS nama_produk,
                    d.is_deleted,
                    d.type,
                    u.foto,
                    d.is_anonymous,
                    COALESCE(dc.comment_count, 0::bigint) AS total_comment,
                    COALESCE(dl.like_count, 0::bigint) AS total_like,
                    COALESCE(ds.share_count, 0::bigint) AS total_share,
                    COALESCE(COALESCE(dc.comment_count, 0::bigint) + COALESCE(dl.like_count, 0::bigint) + COALESCE(ds.share_count, 0::bigint), 0::bigint) AS hotness,
                    u.checkmark_verified,
                    p.id_index AS id_produk
                 FROM jaga.diskusi d
                     JOIN jaga.um_user u ON d.user_id = u.um_id
                     JOIN jaga.kategori_diskusi kd ON d.kategori_diskusi_id = kd.id
                     JOIN jaga.produk p ON p.id_index = kd.produk_id
                     LEFT JOIN ( SELECT diskusi_comment.diskusi_id,
                                    count(diskusi_comment.diskusi_id) AS comment_count
                                 FROM jaga.diskusi_comment
                                WHERE diskusi_comment.created_at > (now() - '8 days'::interval) AND diskusi_comment.created_at <= (now()::date + '00:00:01'::interval)
                                GROUP BY diskusi_comment.diskusi_id) dc ON d.id = dc.diskusi_id
                     LEFT JOIN ( SELECT diskusi_like.diskusi_id,
                                    count(diskusi_like.diskusi_id) AS like_count
                                 FROM jaga.diskusi_like
                                WHERE diskusi_like.created_at > (now() - '8 days'::interval) AND diskusi_like.created_at <= (now()::date + '00:00:01'::interval)
                                GROUP BY diskusi_like.diskusi_id) dl ON d.id = dl.diskusi_id
                     LEFT JOIN ( SELECT diskusi_share.diskusi_id,
                                    count(diskusi_share.diskusi_id) AS share_count
                                 FROM jaga.diskusi_share
                                WHERE diskusi_share.created_at > (now() - '8 days'::interval) AND diskusi_share.created_at <= (now()::date + '00:00:01'::interval)
                                GROUP BY diskusi_share.diskusi_id) ds ON d.id = ds.diskusi_id
                WHERE d.type = 1 AND d.is_deleted = 0
                ORDER BY (COALESCE(COALESCE(dc.comment_count, 0::bigint) + COALESCE(dl.like_count, 0::bigint) + COALESCE(ds.share_count, 0::bigint), 0::bigint)) DESC
            );
                   
            CREATE VIEW jaga.view_hot_diskusi AS (
                SELECT d.id,
                    d.title,
                    d.title_en,
                    d.content,
                    d.content_en,
                    d.uuid,
                    d.created_at,
                    d.updated_at,
                    d.release_date,
                    d.user_id,
                    u.is_banned,
                    u.um_user_name AS nama,
                    u.uuid_user,
                    u.jenis_kelamin,
                    p.nama AS nama_produk,
                    d.is_deleted,
                    d.type,
                    u.foto,
                    d.is_anonymous,
                    COALESCE(dc.comment_count, 0::bigint) AS total_comment,
                    COALESCE(dl.like_count, 0::bigint) AS total_like,
                    COALESCE(ds.share_count, 0::bigint) AS total_share,
                    COALESCE(COALESCE(dc.comment_count, 0::bigint) + COALESCE(dl.like_count, 0::bigint) + COALESCE(ds.share_count, 0::bigint), 0::bigint) AS hotness,
                    u.checkmark_verified,
                    p.id_index AS id_produk
                 FROM jaga.diskusi d
                     JOIN jaga.um_user u ON d.user_id = u.um_id
                     JOIN jaga.kategori_diskusi kd ON d.kategori_diskusi_id = kd.id
                     JOIN jaga.produk p ON p.id_index = kd.produk_id
                     LEFT JOIN ( SELECT diskusi_comment.diskusi_id,
                                    count(diskusi_comment.diskusi_id) AS comment_count
                                 FROM jaga.diskusi_comment
                                WHERE diskusi_comment.created_at > (now() - '1 mon 1 day'::interval)
                                GROUP BY diskusi_comment.diskusi_id) dc ON d.id = dc.diskusi_id
                     LEFT JOIN ( SELECT diskusi_like.diskusi_id,
                                    count(diskusi_like.diskusi_id) AS like_count
                                 FROM jaga.diskusi_like
                                WHERE diskusi_like.created_at > (now() - '1 mon 1 day'::interval)
                                GROUP BY diskusi_like.diskusi_id) dl ON d.id = dl.diskusi_id
                     LEFT JOIN ( SELECT diskusi_share.diskusi_id,
                                    count(diskusi_share.diskusi_id) AS share_count
                                 FROM jaga.diskusi_share
                                WHERE diskusi_share.created_at > (now() - '1 mon 1 day'::interval)
                                GROUP BY diskusi_share.diskusi_id) ds ON d.id = ds.diskusi_id
                WHERE d.type = 0 AND d.is_deleted = 0
                ORDER BY (COALESCE(COALESCE(dc.comment_count, 0::bigint) + COALESCE(dl.like_count, 0::bigint) + COALESCE(ds.share_count, 0::bigint), 0::bigint)) DESC
             );
        ");
    }
}