<?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
{
    public function up(): void
    {
        // stock_batches — sellableFor / priceRangesFor / frontLotsPayload
        // Hot path: branch_id + product_id filtered, available_quantity > 0, sale_price not null
        Schema::table('stock_batches', function (Blueprint $table) {
            // Cover the POS catalog's price-range grouped query
            $table->index(['branch_id', 'product_id', 'available_quantity'], 'idx_stock_batches_branch_product_avail');
            $table->index(['branch_id', 'product_id', 'sale_price'], 'idx_stock_batches_branch_product_price');
            // FEFO ordering needs expiry_date; compound helps range scans
            $table->index(['branch_id', 'product_id', 'expiry_date'], 'idx_stock_batches_branch_product_expiry');
        });

        // cash_movements — expectedBalance single aggregated query + reconciliation
        Schema::table('cash_movements', function (Blueprint $table) {
            // Existing index is (pos_session_id, type); add source to avoid extra filter pass
            $table->index(['pos_session_id', 'source'], 'idx_cash_movements_session_source');
            $table->index(['pos_session_id', 'type', 'amount'], 'idx_cash_movements_session_type_amount');
        });

        // product_serials — availableSerials / withAvailableSerialCount
        Schema::table('product_serials', function (Blueprint $table) {
            $table->index(['branch_id', 'product_id', 'status_id'], 'idx_serials_branch_product_status');
            $table->index(['product_id', 'stock_batch_id'], 'idx_serials_product_batch');
        });

        // held_sales — listing per branch scoped by held_at
        // Already has (branch_id, held_at) but ensure tenant scoping doesn't miss
        Schema::table('held_sales', function (Blueprint $table) {
            $table->index(['tenant_id', 'branch_id'], 'idx_held_sales_tenant_branch');
        });

        // stock_movements — BranchStock lock path
        Schema::table('stock_movements', function (Blueprint $table) {
            $table->index(['branch_id', 'product_id'], 'idx_stock_movements_branch_product');
        });

        // sales_order_items / sales_order_payments — recalculate sums
        Schema::table('sales_order_items', function (Blueprint $table) {
            $table->index('sales_order_id', 'idx_so_items_order_id');
        });
        Schema::table('sales_order_payments', function (Blueprint $table) {
            $table->index('sales_order_id', 'idx_so_payments_order_id');
        });

        // deals / cart_discounts — active window queries filter is_active + starts_at/ends_at
        Schema::table('deals', function (Blueprint $table) {
            $table->index(['is_active', 'starts_at', 'ends_at'], 'idx_deals_active_window');
        });
        Schema::table('cart_discounts', function (Blueprint $table) {
            $table->index(['is_active', 'starts_at', 'ends_at'], 'idx_cart_discounts_active_window');
        });

        // invoices — pos_session_id channel filter for lastSale/amend
        Schema::table('invoices', function (Blueprint $table) {
            $table->index(['pos_session_id', 'channel'], 'idx_invoices_session_channel');
        });
    }

    public function down(): void
    {
        Schema::table('stock_batches', function (Blueprint $table) {
            $table->dropIndex('idx_stock_batches_branch_product_avail');
            $table->dropIndex('idx_stock_batches_branch_product_price');
            $table->dropIndex('idx_stock_batches_branch_product_expiry');
        });
        Schema::table('cash_movements', function (Blueprint $table) {
            $table->dropIndex('idx_cash_movements_session_source');
            $table->dropIndex('idx_cash_movements_session_type_amount');
        });
        Schema::table('product_serials', function (Blueprint $table) {
            $table->dropIndex('idx_serials_branch_product_status');
            $table->dropIndex('idx_serials_product_batch');
        });
        Schema::table('held_sales', function (Blueprint $table) {
            $table->dropIndex('idx_held_sales_tenant_branch');
        });
        Schema::table('stock_movements', function (Blueprint $table) {
            $table->dropIndex('idx_stock_movements_branch_product');
        });
        Schema::table('sales_order_items', function (Blueprint $table) {
            $table->dropIndex('idx_so_items_order_id');
        });
        Schema::table('sales_order_payments', function (Blueprint $table) {
            $table->dropIndex('idx_so_payments_order_id');
        });
        Schema::table('deals', function (Blueprint $table) {
            $table->dropIndex('idx_deals_active_window');
        });
        Schema::table('cart_discounts', function (Blueprint $table) {
            $table->dropIndex('idx_cart_discounts_active_window');
        });
        Schema::table('invoices', function (Blueprint $table) {
            $table->dropIndex('idx_invoices_session_channel');
        });
    }
};
