<?php

use Phalcon\Mvc\Model\Migration;

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

        self::$connection->execute("
            CREATE OR REPLACE VIEW web.view_trx_comments
            AS
            SELECT c.comment_id,
            c.comment_type,
                CASE
                    WHEN c.comment_type = 1 THEN 'Berita'::character varying
                    WHEN c.comment_type = 2 THEN 'Kegiatan'::character varying
                    ELSE NULL::character varying
                END AS comment_type_text,
            c.comment_data_id,
                CASE
                    WHEN c.comment_type = 1 THEN ( SELECT trx_news.news_title
                        FROM web.trx_news
                        WHERE c.comment_data_id = trx_news.news_id)
                    WHEN c.comment_type = 2 THEN ( SELECT trx_activities.activity_title
                        FROM web.trx_activities
                        WHERE c.comment_data_id = trx_activities.activity_id)
                    ELSE NULL::character varying
                END AS comment_data_title,
            c.comment_date,
            web.datetimeidn(c.comment_date) AS comment_date_idn,
            c.comment_text,
            c.comment_user_uuid,
            _create.nama AS comment_create_user_fullname,
            _create.foto AS comment_create_user_foto,
            c.comment_create_date,
            web.datetimeidn(c.comment_create_date) AS comment_create_date_idn,
            c.comment_update_user_id,
            _update.user_fullname AS comment_update_user_fullname,
            c.comment_update_date,
            web.datetimeidn(c.comment_update_date) AS comment_update_date_idn,
            c.comment_status,
            c.comment_log_id
            FROM web.trx_comments c
                JOIN jaga.um_user _create ON _create.uuid_user = c.comment_user_uuid
                LEFT JOIN web.view_sys_users _update ON _update.user_id = c.comment_update_user_id
            WHERE c.comment_log_id IS NULL;
        ");
    }

    public function down()
    {
        
    }
}