<?php

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

return new class extends Migration {
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        DB::statement("
     CREATE OR REPLACE VIEW purchase_return_details_view AS
SELECT
    pr.id AS purchase_return_id,
    pr.reference_no,
    pr.return_date,
    pr.supplier_id,
    pr.purchase_invoice_no,
    pr.total AS purchase_return_total,

    prd.id AS purchase_return_detail_id,
    prd.purchase_invoice_no AS purchase_return_detail_invoice_no,
    prd.product_id,
    prd.attribute_id,
    prd.purchase_quantity,
    prd.return_qty,
    prd.purchase_price,
    prd.return_price,
    prd.total AS return_total,

    p.name AS product_name,
    p.description AS product_description,
    s.name AS supplier_name,

    b.name AS brand_name,
    c.name AS color_name,
    sz.name AS size_name,
    m.name AS model_name,
    o.name AS origin_name

FROM
    purchase_return_masters pr
JOIN
    purchase_return_details prd ON pr.id = prd.purchase_return_master_id
JOIN
    product_attributes pa ON prd.attribute_id = pa.id
JOIN
    products p ON pa.product_id = p.id
JOIN
    suppliers s ON pr.supplier_id = s.id

LEFT JOIN brands b ON pa.brand_id = b.id
LEFT JOIN colors c ON pa.color_id = c.id
LEFT JOIN sizes sz ON pa.size_id = sz.id
LEFT JOIN model_tables m ON pa.model_id = m.id
LEFT JOIN origins o ON pa.origin_id = o.id;

            ");
    }

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