<?php

use Phalcon\Mvc\Model\Migration;

class JagaDataChallengeMigration_365 extends Migration
{
    public function up()
    {
        $this->down();

        self::$connection->execute("
            DROP VIEW IF EXISTS jaga.view_hot_diskusi;
            CREATE OR REPLACE VIEW jaga.view_hot_diskusi AS
            select
                d.id,
                d.title,
                d.content,
                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) as total_comment,
                COALESCE(dl.like_count, 0) as total_like,
                COALESCE(ds.share_count, 0) as total_share,
                COALESCE((COALESCE(dc.comment_count, 0) + COALESCE(dl.like_count, 0) + COALESCE(ds.share_count, 0)), 0) 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_id,
                    count(diskusi_id) as comment_count
                from jaga.diskusi_comment
                where created_at > (NOW() - INTERVAL '1 MONTH + 1 DAY')
                group by diskusi_id
            ) as dc
            on d.id = dc.diskusi_id
            left join (
                select 
                    diskusi_id,
                    count(diskusi_id) as like_count
                from jaga.diskusi_like
                where created_at > (NOW() - INTERVAL '1 MONTH + 1 DAY')
                group by diskusi_id
            ) as dl
            on d.id = dl.diskusi_id
            left join (
                select
                    diskusi_id,
                    count(diskusi_id) as share_count
                from jaga.diskusi_share
                where created_at > (NOW() - INTERVAL '1 MONTH + 1 DAY')
                group by diskusi_id
            ) as ds
            on d.id = ds.diskusi_id
            where d.type = 0
            and d.is_deleted = 0
            order by hotness desc
            ;
        ");

        self::$connection->execute("
            DROP VIEW IF EXISTS jaga.view_trending_berita;
            CREATE OR REPLACE VIEW jaga.view_trending_berita AS
            SELECT d.id,
            d.title,
            d.content,
            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            
          ;
        ");
    }

    public function down()
    {
        self::$connection->execute("
            DROP VIEW IF EXISTS jaga.view_hot_diskusi;
        ");

        self::$connection->execute("
            DROP VIEW IF EXISTS jaga.view_trending_berita;
        ");
    }
}