<?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 supplier_transaction_ledger_view AS
SELECT * FROM (

    -- Opening Balance (you already owed this to supplier)
    SELECT 
 
        DATE(CONVERT_TZ(s.opening_date, '+00:00', '+06:00')) AS `date`,
        'Opening Balance' AS particulars,
        NULL AS invoice_no,
        0 AS debit,
        s.opening_balance AS credit,
        s.id AS supplier_id,
        NULL AS order_no,
        '--' AS reference,
        0 - s.opening_balance AS total,
        1 AS sort_order
    FROM suppliers s

    UNION ALL

    -- Purchase (you owe supplier more → credit)
    SELECT 
        DATE(pm.created_at) AS date,
        'Purchase' AS particulars,
        pm.invoice_no,
        0 AS debit,
        pm.total AS credit,
        pm.supplier_id,
        pm.invoice_no AS order_no,
        NULL AS reference,
        0 - pm.total AS total,
        2 AS sort_order
    FROM purchase_masters pm

    UNION ALL

    -- Payment (you pay supplier → debit)
    SELECT 
        DATE(pp.date) AS date,
        pp.payment_type AS particulars,
        NULL AS invoice_no,
        pp.payment AS debit,
        0 AS credit,
        pp.supplier_id,
        pp.order_no,
        pp.collection_reference AS reference,
        pp.payment AS total,
        3 AS sort_order
    FROM purchase_payments pp

    UNION ALL

    -- Purchase Return (you return product → liability decreases → credit)
    SELECT 
        DATE(pr.return_date) AS date,
        'Purchase Return' AS particulars,
        pr.reference_no,
        pr.total AS debit,
        0 AS credit,
        pr.supplier_id,
        NULL AS order_no,
        pr.reference_no AS reference,
        0 - pr.total AS total,
        4 AS sort_order
    FROM purchase_return_masters pr

) AS all_transactions
WHERE supplier_id IS NOT NULL
ORDER BY supplier_id, date, sort_order;




");
    }

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