<?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 daily_product_sales_summary_view AS
SELECT
    DATE(sm.created_at) AS date,
    sm.transfer_type,
    sm.mobilebank_id,
    sm.bank_id,

    pa.product_id,
    pa.id AS attribute_id,
    pa.sku,
    p.name AS product_name,
    sm.location_id,
    sm.after_discount AS discount_value,

    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(sd.qty) AS total_quantity_sold,
    SUM(sd.total) AS total_sales_value,
    COUNT(sm.id) AS total_sales_count

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

GROUP BY
    DATE(sm.created_at),
    sm.transfer_type,
    sm.mobilebank_id,
    sm.bank_id,
    pa.product_id,
    pa.id,
    pa.sku,
    p.name,
    sm.location_id,
    sm.after_discount,
    b.name,
    c.name,
    sz.name,
    m.name,
    o.name;


            ");
    }

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