<?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 sales_details_summary_view AS
SELECT
    sm.id AS sales_master_id,
    sm.client_id,
    c.name AS client_name,
    DATE(sm.created_at) AS date,
    sm.location_id,
    sm.transfer_type,
    sm.mobilebank_id,
    sm.invoice_no,
    sm.bank_id,

    sd.product_id,
    p.name AS product_name,
    sd.attribute_id,
    pa.sku,

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

    sd.qty AS quantity_sold,
    sd.price AS unit_price,
    sd.batch_no,
    sd.total AS total_sales_value,
    sm.grand_total AS sales_grand_total,
    sd.after_discount AS discount_value,
    sm.after_vat AS vat_value

FROM
    sales_masters sm
JOIN
    sales_details sd ON sm.id = sd.sales_masters_id
JOIN
    product_attributes pa ON sd.attribute_id = pa.id
JOIN
    products p ON pa.product_id = p.id
LEFT JOIN
    clients c ON sm.client_id = c.id

LEFT JOIN brands b ON pa.brand_id = b.id
LEFT JOIN colors c2 ON pa.color_id = c2.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 sales_details_summary_view');
    }
};
