<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateViewBookingsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        
            \DB::statement("CREATE VIEW view_bookings AS SELECT b.id,
            b.ms,
            b.branch_id,
            b.created_at,
            b.is_block,
            b.file_number,
            b.status,
            b.is_hotitem,
            b.is_resale,
            u.name,
            concat_ws('-'::text, plot_br.code,p.floor,p.plot_id) AS booking_details,
            br.code,
            br.name AS branch_name,
            p.size,
            p.category,
            c.sales_person
           FROM bookings b
             JOIN plots p ON p.id = b.plot_id
             JOIN users u ON u.id = b.user_id
             JOIN branches br ON br.id = b.branch_id
             LEFT JOIN branches plot_br ON plot_br.id = p.branch_id
             LEFT JOIN ( SELECT c_1.booking_id,
                    string_agg(cu.name::text, ', '::text) AS sales_person
                   FROM commissions c_1
                     JOIN users cu ON cu.id = c_1.user_id
                  GROUP BY c_1.booking_id) c ON b.id = c.booking_id
          WHERE (b.status::text = ANY (ARRAY['Approve'::character varying::text, 'Unapprove'::character varying::text])) AND b.is_recycle::text = '0'::text;");
        
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        DB::statement("DROP VIEW IF EXISTS view_bookings");
    }
}
