<?php

use App\Models\BranchStock;
use App\Models\Catalog\Product;
use App\Models\Central\Tenant;
use App\Models\Inventory\StockBatch;
use App\Models\Lookups\Status;
use App\Models\Scopes\BranchScope;
use App\Services\CurrentContext;
use App\Services\DocumentNumberService;
use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

/**
 * Backfill opening lots for pre-existing stock.
 *
 * Before lot-level pricing, stock lived only as a number in branch_stock —
 * stock_batches existed solely for batch-tracked products, so most stock
 * has no lot at all. From now on every unit must sit in a lot (the lot IS
 * the price), so this migration creates one opening lot per
 * (branch, product) pair that holds more stock than its active lots
 * already cover:
 *
 *  - sale_price     = products.sale_price  (product price at migration time)
 *  - batch_cost     = products.purchase_price
 *  - no expiry / no receipt provenance (grn_item_id, grn_id stay null)
 *  - notes          = 'Opening balance — pre-lot-pricing migration'
 *
 * Runs per tenant (see docs/migration-plan.md §1 — TenantScope now requires
 * this) so DocumentNumberService can mint collision-free batch numbers
 * against each tenant's own sequence counter, and the unique
 * (tenant_id, internal_batch_number) pair from
 * 2026_08_17_000001 holds.
 */
return new class extends Migration
{
    private const NOTES = 'Opening balance — pre-lot-pricing migration';

    private const EPSILON = 0.0005;

    public function up(): void
    {
        $context = app(CurrentContext::class);

        foreach (Tenant::query()->get() as $tenant) {
            $context->runForTenant($tenant, function () {
                $this->backfillTenant();
            });
        }
    }

    private function backfillTenant(): void
    {
        // Once migration 000005 has dropped the product price columns, there
        // is nothing left to backfill from — a re-run is a deliberate no-op
        // (the backfill only ever runs meaningfully while the columns exist).
        if (! Schema::hasColumn('products', 'sale_price')) {
            return;
        }

        $productIds = Product::query()->pluck('id')->all();
        if (! $productIds) {
            return;
        }

        $prices = Product::query()
            ->whereIn('id', $productIds)
            ->get(['id', 'sale_price', 'purchase_price'])
            ->keyBy('id');

        // Stock on the books, per (branch, product).
        $onHand = BranchStock::query()
            ->withoutGlobalScope(BranchScope::class)
            ->where('quantity', '>', 0)
            ->whereIn('product_id', $productIds)
            ->get(['branch_id', 'product_id', 'quantity']);

        // How much each (branch, product) pair's active lots already hold.
        // The pair key is assembled in PHP on purpose: pluck() resolves its
        // key argument as a property name on the result row, so a DB::raw()
        // expression there looks for a column that was never selected, and
        // building the key in SQL instead would tie the migration to a
        // CONCAT the test suite's SQLite connection does not have.
        $covered = StockBatch::query()
            ->withoutGlobalScope(BranchScope::class)
            ->whereIn('product_id', $productIds)
            ->where('available_quantity', '>', 0)
            ->active()
            ->selectRaw('branch_id, product_id, SUM(available_quantity) AS covered')
            ->groupBy('branch_id', 'product_id')
            ->get()
            ->mapWithKeys(fn ($row): array => ["{$row->branch_id}:{$row->product_id}" => $row->covered]);

        $numbers = app(DocumentNumberService::class);
        $activeStatusId = Status::idOf(Status::CTX_STOCK_BATCH, Status::BATCH_ACTIVE);

        DB::transaction(function () use ($onHand, $covered, $prices, $numbers, $activeStatusId) {
            foreach ($onHand as $row) {
                $shortfall = (float) $row->quantity - (float) ($covered["{$row->branch_id}:{$row->product_id}"] ?? 0);
                if ($shortfall <= self::EPSILON) {
                    continue;
                }

                $product = $prices[$row->product_id] ?? null;
                if (! $product) {
                    continue;
                }

                StockBatch::create([
                    'branch_id' => $row->branch_id,
                    'product_id' => $row->product_id,
                    'internal_batch_number' => $this->freshBatchNumber($numbers),
                    'received_quantity' => $shortfall,
                    'available_quantity' => $shortfall,
                    'reserved_quantity' => 0,
                    'damaged_quantity' => 0,
                    'sale_price' => $product->sale_price,
                    'batch_cost' => $product->purchase_price,
                    'status_id' => $activeStatusId,
                    'notes' => self::NOTES,
                ]);
            }
        });
    }

    /**
     * The batch counter isn't seeded from issued numbers ('batch' has no
     * SOURCES entry), so guard the unique (tenant_id, internal_batch_number)
     * pair anyway — a counter reset would otherwise collide mid-run.
     */
    private function freshBatchNumber(DocumentNumberService $numbers): string
    {
        do {
            $number = $numbers->next('batch');
        } while (StockBatch::query()->where('internal_batch_number', $number)->exists());

        return $number;
    }

    public function down(): void
    {
        $context = app(CurrentContext::class);

        foreach (Tenant::query()->get() as $tenant) {
            $context->runForTenant($tenant, function () {
                StockBatch::query()
                    ->where('notes', self::NOTES)
                    ->whereNull('grn_id')
                    ->forceDelete();
            });
        }
    }
};
