<?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_details_view AS
SELECT
    pm.id AS `purchase_master_id`,
    pm.supplier_id,
    pm.warehouse_id AS `location_id`,
    DATE(CONVERT_TZ(pm.purchase_date, '+00:00', '+06:00')) AS `date`,
    pm.invoice_no,
    pd.attribute_id,
    pd.batch_no,
    pd.mfg_date,
    pd.exp_date,
    s.name AS `supplier_name`,
    p.name AS `product_name`, 
    pa.sku AS `sku`,

    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`,

    SUM(pd.quantity) AS qty,
    SUM(pd.gift_qty) AS gift_qty,
    SUM(pd.unit_price) AS purchase,
    SUM(pd.unit_price_total) AS total,
    SUM(pd.vat) AS vat,
    SUM(pd.tax) AS tax

FROM
    purchase_masters pm
JOIN
    purchase_details pd ON pm.id = pd.purchase_master_id
JOIN 
    product_attributes pa ON pd.attribute_id = pa.id
JOIN 
    products p ON pa.product_id = p.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

LEFT JOIN 
    suppliers s ON pm.supplier_id = s.id
JOIN 
    warehouses w ON pm.warehouse_id = w.id

GROUP BY
    pm.id, pm.supplier_id, pd.batch_no, pm.purchase_date, pm.invoice_no,
    pd.attribute_id, p.name, pm.warehouse_id, s.name,
    pa.sku, b.name, c.name, sz.name, m.name, o.name, pd.mfg_date, pd.exp_date

ORDER BY
    pm.purchase_date ASC;




");
    }

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