Untitled

<?php

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

class AlterVwRoomsSmallFutureLeases extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        DB::statement('DROP VIEW IF EXISTS `vw_rooms`;');
        DB::statement('CREATE VIEW `vw_rooms` AS
          SELECT
              rooms.id,
              rooms.house_id,
              houses.region_id,
              rooms.price,
              IF(vw_future_member_leases.room_id IS NULL,
                 if(vw_member_leases.id AND (vw_member_leases.date_end IS NULL OR vw_member_leases.date_end > now() + INTERVAL 30 DAY), 0, 1),
                 0
              ) AS vacancy,
              if(now() < houses.date_lease_start, houses.date_lease_start,
                 if(vw_future_member_leases.room_id, null, if(vw_member_leases.id, (vw_member_leases.date_end + INTERVAL 1 DAY),
                    houses.date_lease_start))) AS move_in_at,
              (SELECT COUNT(rooms.id)
               FROM rooms
               WHERE
                 rooms.house_id = houses.id
                 AND rooms.deleted_at IS NULL) AS vacancies
            FROM rooms
              LEFT JOIN vw_latest_member_lease_by_rooms ON vw_latest_member_lease_by_rooms.room_id = rooms.id
              LEFT JOIN vw_member_leases ON vw_member_leases.id = vw_latest_member_lease_by_rooms.id
              LEFT JOIN vw_future_member_leases 
                ON vw_future_member_leases.room_id = rooms.id
                    AND COALESCE(vw_future_member_leases.date_end, NOW() + INTERVAL 3 MONTH) > NOW() + INTERVAL 30 DAY
              JOIN vw_houses AS houses ON (houses.id = rooms.house_id)
            WHERE
              rooms.deleted_at IS NULL');
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        DB::statement('DROP VIEW IF EXISTS `vw_rooms`;');
        DB::statement('CREATE VIEW `vw_rooms` AS
          SELECT
              rooms.id,
              rooms.house_id,
              houses.region_id,
              rooms.price,
              IF(vw_future_member_leases.room_id IS NULL,
                 if(vw_member_leases.id AND (vw_member_leases.date_end IS NULL OR vw_member_leases.date_end > now() + INTERVAL 30 DAY), 0, 1),
                 0
              ) AS vacancy,
              if(now() < houses.date_lease_start, houses.date_lease_start,
                 if(vw_future_member_leases.room_id, null, if(vw_member_leases.id, (vw_member_leases.date_end + INTERVAL 1 DAY),
                    houses.date_lease_start))) AS move_in_at,
              (SELECT COUNT(rooms.id)
               FROM rooms
               WHERE
                 rooms.house_id = houses.id
                 AND rooms.deleted_at IS NULL) AS vacancies
            FROM rooms
              LEFT JOIN vw_latest_member_lease_by_rooms ON vw_latest_member_lease_by_rooms.room_id = rooms.id
              LEFT JOIN vw_member_leases ON vw_member_leases.id = vw_latest_member_lease_by_rooms.id
              LEFT JOIN vw_future_member_leases ON vw_future_member_leases.room_id = rooms.id
              JOIN vw_houses AS houses ON (houses.id = rooms.house_id)
            WHERE
              rooms.deleted_at IS NULL');
    }
}