<?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 view_purchase_details AS
SELECT 
    pm.invoice_no,

      CONVERT(pm.purchase_date, DATE) as purchase_date,
    pm.supplier_id,
    s.name AS supplier_name,
    pm.warehouse_id,
    w.name AS warehouse_name,
    pd.attribute_id,
    pd.batch_no,
    pd.mfg_date,
    pd.exp_date,
    pd.product_id,
    p.name AS product_name,
    SUM(CAST(pd.quantity AS DECIMAL(10,2))) AS total_quantity,
    SUM(CAST(pd.unit_price AS DECIMAL(10,2))) AS total_unit_price,
    SUM(CAST(pd.unit_price_total AS DECIMAL(10,2))) AS total_unit_price_total,
    SUM(CAST(pd.vat AS DECIMAL(10,2))) AS total_vat,
    SUM(CAST(pd.tax AS DECIMAL(10,2))) AS total_tax,
    SUM(CAST(pm.total AS DECIMAL(10,2))) AS total_invoice_price

FROM 
    purchase_masters AS pm
JOIN 
    purchase_details AS pd ON pd.purchase_master_id = pm.id
JOIN 
    suppliers AS s ON s.id = pm.supplier_id
JOIN 
    warehouses AS w ON w.id = pm.warehouse_id
JOIN 
    product_attributes AS pa ON pa.id = pd.attribute_id
JOIN 
    products AS p ON p.id = pd.product_id

GROUP BY 
    pm.invoice_no,
    pm.purchase_date,
    pm.supplier_id,
    s.name,
    pm.warehouse_id,
    w.name,
    pd.attribute_id,
    pd.product_id,
    p.name

ORDER BY 
    pm.purchase_date ASC;



            ');
    }

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