<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        DB::statement('
            CREATE VIEW warehouse_transfer_view AS
            SELECT 
                wtm.from_warehouse_id,
                wtm.to_warehouse_id,
                CONVERT(wtm.date, DATE) as date,
                wtd.product_id,
                wtd.attribute_id,
                wtd.qty,
                wt.name AS to_warehouse_name,
                wf.name AS from_warehouse_name,
                p.name AS product_name,
                mt.name AS model_name,
                ut.name AS unit_name,
                c.name AS color_name,
                s.name AS size_name,
                b.name AS brand_name,
                o.name AS origin_name
            FROM warehouse_transfer_masters AS wtm
            JOIN warehouse_transfer_details AS wtd ON wtd.warehouse_transfer_master_id = wtm.id
            JOIN warehouses AS wf ON wf.id = wtm.from_warehouse_id
            JOIN warehouses AS wt ON wt.id = wtm.to_warehouse_id
            JOIN product_attributes AS pa ON pa.id = wtd.attribute_id
            JOIN products AS p ON p.id = pa.product_id
            LEFT JOIN model_tables AS mt ON mt.id = pa.model_id
            LEFT JOIN units AS ut ON ut.id = pa.unit_id
            LEFT JOIN colors AS c ON c.id = pa.color_id
            LEFT JOIN sizes AS s ON s.id = pa.size_id
            LEFT JOIN brands AS b ON b.id = pa.brand_id
            LEFT JOIN origins AS o ON o.id = pa.origin_id;

    
    ');
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        DB::statement('DROP VIEW IF EXISTS warehouse_transfer_view');
    }
};
