<?php

namespace App\Console\Commands;

use App\Models\Branch;
use App\Models\Catalog\MainCategory;
use App\Models\Catalog\Product;
use App\Models\Catalog\SubCategory;
use App\Models\Inventory\Grn;
use App\Models\Lookups\Status;
use App\Models\User;
use App\Services\GrnService;
use Database\Seeders\AccessControlSeeder;
use Database\Seeders\CustomerSeeder;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

/**
 * Rebuild the product catalogue and opening inventory from the corrected
 * stock-take export in `latest-data/` (GNJ.json, JMC.json, OE.json).
 *
 *  - Creates the three main categories (GNJ, JMC, OE), their sub-categories,
 *    and every product (products carry no price — the receipt lots own it)
 *  - Turns each source's stock balance into ONE confirmed opening GRN per
 *    branch, so stock lands through the real GRN → stock-movement flow
 *    (traceable history, correct valuation) instead of a raw balance write
 *  - GNJ products are named "{PART} - {DESCRIPTION}" (business rule — the
 *    bare DESCRIPTION alone isn't a usable product name); JMC/OE product
 *    names are used as-is
 *  - Idempotent (re-runnable; products match by SKU, the opening GRN is
 *    created once per branch)
 *
 * Customers, users, roles and permissions are seeded by `db:seed`
 * ({@see CustomerSeeder}, {@see AccessControlSeeder}).
 * Invoices, returns and payments are intentionally NOT migrated — this is a
 * clean-inventory rebuild.
 *
 * NOTE: the old `legacy-system-data/db-export` CSV dump is NOT used — that
 * export carried the incorrect stock/pricing this rebuild exists to fix.
 * `latest-data/*.json` is the corrected source of truth.
 *
 *   php artisan legacy:migrate            # migrate over existing data (best-effort)
 *   php artisan legacy:migrate --fresh    # truncate catalog + stock first
 *
 * IMPORTANT: run after `migrate:fresh && db:seed` so lookups, roles and the
 * admin user exist.
 */
class MigrateLegacyData extends Command
{
    protected $signature = 'legacy:migrate
        {--fresh : Wipe catalog + stock tables before importing}
        {--dry : Parse + report but skip the opening GRN}
        {--source= : Path to the latest-data directory (default: base_path(\'latest-data\'))}';

    protected $description = 'Rebuild the product catalogue and opening inventory (as a confirmed GRN) from latest-data/*.json.';

    /** Reference stamped on the single opening-stock GRN (used for idempotency). */
    private const OPENING_REFERENCE = 'OPENING-STOCK';

    /** Explicit sub-category sort codes for every GNJ PART value. */
    private const GNJ_SUBCAT_CODES = [
        'COLD FORGED FLANGE CUP' => 'CFFC',
        'SLIDER BLOCK SET' => 'SBS',
        'CLUTCH HOUSING' => 'CH',
        'WIPER (ARM+BLADE)' => 'WAB',
        'BALL RACER KIT : B/CS-04' => 'BRK-BCS04',
        'BALL RACER KIT : B/CS-06' => 'BRK-BCS06',
        'BALL RACER KIT : B/CS-03' => 'BRK-BCS03',
        'BALL RACER KIT : P/CS-01' => 'BRK-PCS01',
        'TRAILING ARM' => 'TA',
        'CONNECTING ROD KIT' => 'CRK',
        'REAR AXLE' => 'RA',
        'FRONT AXLE' => 'FA',
        'PIVOT PIN' => 'PP',
        'WIRING HARNESS' => 'WH',
        'HORN' => 'HRN',
        'WHEEL RIM' => 'WRM',
        'FRONT HUB' => 'FH',
        'BRAKE DRUM' => 'BD',
        'REAR HUB' => 'RH',
        'WHEEL CYLINDER' => 'WC',
        'WHEEL CYLINDER KIT' => 'WCK',
        'MASTER CYLINDER ASSY' => 'MCA',
        'MASTER CYLINDER KIT' => 'MCK',
        'OIL CONTAINER' => 'OC',
        'BALL BEARING' => 'BB',
        'NEEDLE BEARING (4ST)' => 'NB4',
        'NEEDLE BEARING (2ST)' => 'NB2',
        'CYLINDER BLOCK KIT' => 'CBK',
        'BRAKE SHOE KIT' => 'BSK',
        'MAIN SHAFT' => 'MS',
        'MAIN SPRINDELSHAFT' => 'MSP',
        'DOLLAR RUBBER' => 'DR',
        'DOLLAR JUMBO RUBBER' => 'DJR',
        'AXLE BOOT' => 'AB',
        'ROTOR ASSY' => 'RTA',
        'BENDIX' => 'BDX',
        'COIL PLATE' => 'CP',
        'STARTER MOTOR ASSY' => 'SMA',
        'ARMATURE' => 'ARM',
        'AIR FILTER' => 'AF',
        'OIL FILTER' => 'OF',
        'FL MODEL CABLES' => 'FLC',
        'RE 205CC CABLES' => 'R205',
        'RE 175CC CABLES' => 'R175',
        '2-STROKE CABLES' => '2SC',
        'PIAGGIO MODEL CABLES' => 'PMC',
        'PIAGGIO APE INNER' => 'PAI',
        'BAJAJ 4 STROKE INNER' => 'B4SI',
        'SILENCER' => 'SLC',
        'FLANGE CUP' => 'FC',
    ];

    /** Stats counter */
    private array $counts = [];

    /** Resolved id of the branch every product/GRN is parked under. */
    private ?int $branchId = null;

    public function handle(): int
    {
        $sourceDir = rtrim($this->option('source') ?: base_path('latest-data'), DIRECTORY_SEPARATOR);
        if (! is_dir($sourceDir)) {
            $this->error("Source directory not found: {$sourceDir}");

            return self::FAILURE;
        }

        $this->info('Source: '.$sourceDir);
        $this->info($this->option('fresh') ? 'Mode: --fresh (will wipe catalog + stock tables first)' : 'Mode: incremental');

        if ($this->option('fresh') && ! $this->option('dry')) {
            $this->wipeDestination();
        }

        // Lookups + roles + the admin user must already exist; bail loudly otherwise.
        if (Status::query()->count() === 0) {
            $this->error('Lookup tables are empty. Run `php artisan migrate:fresh && php artisan db:seed` first.');

            return self::FAILURE;
        }

        /** @var array<int, array{qty: float, price: float}> $stockData product_id => opening balance */
        $stockData = [];

        DB::transaction(function () use ($sourceDir, &$stockData) {
            $branch = $this->importBranch();
            [$gnj, $jmc, $oe] = $this->importMainCategories();

            $stockData += $this->seedGnj($sourceDir, $gnj);
            $stockData += $this->seedJmc($sourceDir, $jmc);
            $stockData += $this->seedOe($sourceDir, $oe);

            $this->branchId = $branch->id;
        });

        if (! $this->option('dry')) {
            $this->createOpeningGrn($stockData);
        }

        $this->info('');
        $this->info('Migration complete.');
        foreach ($this->counts as $table => $n) {
            $this->line(sprintf('  %-22s %d', $table, $n));
        }

        return self::SUCCESS;
    }

    // ───────────────────────────── helpers ─────────────────────────────

    private function tally(string $table, int $count): void
    {
        $this->counts[$table] = ($this->counts[$table] ?? 0) + $count;
    }

    private function wipeDestination(): void
    {
        $this->warn('Wiping catalog + stock tables…');
        Schema::disableForeignKeyConstraints();
        foreach ([
            'stock_movements', 'branch_stock',
            // Lots reference products (restrictOnDelete), so they must go first.
            'stock_batches', 'grn_items', 'grns',
            'products', 'sub_categories', 'main_categories',
            // Customers, users, roles and tills are owned by db:seed — never wiped here.
        ] as $t) {
            DB::table($t)->delete();
        }
        Schema::enableForeignKeyConstraints();
    }

    /** @return array<int, array<string, string>> */
    private function readJson(string $sourceDir, string $file): array
    {
        $path = $sourceDir.DIRECTORY_SEPARATOR.$file;
        if (! file_exists($path)) {
            $this->warn('  skip (missing): '.$file);

            return [];
        }

        return json_decode(file_get_contents($path), true) ?? [];
    }

    private function parsePrice(string $value): float
    {
        return (float) str_replace(',', '', $value);
    }

    private function normalizeWhitespace(string $text): string
    {
        return trim(preg_replace('/\s+/', ' ', $text));
    }

    private function abbreviate(string $name): string
    {
        $words = preg_split('/[\s\(\)\[\]:\/\-]+/', strtoupper($name), -1, PREG_SPLIT_NO_EMPTY);
        $code = implode('', array_map(fn ($w) => substr($w, 0, 1), $words));

        return substr($code, 0, 8);
    }

    // ───────────────────────────── branch + categories ─────────────────────────────

    private function importBranch(): Branch
    {
        $branch = Branch::firstOrCreate(
            ['name' => 'Badulla - Main'],
            ['city' => 'Badulla', 'country' => 'Sri Lanka', 'is_active' => true],
        );
        $this->tally('branches_used', 1);

        return $branch;
    }

    /** @return array{MainCategory, MainCategory, MainCategory} */
    private function importMainCategories(): array
    {
        $gnj = MainCategory::firstOrCreate(
            ['sort_code' => 'GNJ'],
            ['name' => 'GNJ', 'description' => 'GNJ Auto Parts', 'is_active' => true],
        );
        $jmc = MainCategory::firstOrCreate(
            ['sort_code' => 'JMC'],
            ['name' => 'JMC', 'description' => 'JMC Auto Parts', 'is_active' => true],
        );
        $oe = MainCategory::firstOrCreate(
            ['sort_code' => 'OE'],
            ['name' => 'OE', 'description' => 'OE Auto Parts', 'is_active' => true],
        );
        $this->tally('main_categories', 3);

        return [$gnj, $jmc, $oe];
    }

    // ───────────────────────────── per-source importers ─────────────────────────────

    /**
     * GNJ: sub-category = PART, product name = "{PART} - {DESCRIPTION}".
     *
     * @return array<int, array{qty: float, price: float}>
     */
    private function seedGnj(string $sourceDir, MainCategory $category): array
    {
        $items = $this->readJson($sourceDir, 'GNJ.json');

        /** @var array<int, int> $counters subcategoryId => next sequence number */
        $counters = [];
        $stock = [];
        $subCount = 0;
        $productCount = 0;

        foreach ($items as $item) {
            $part = $this->normalizeWhitespace($item['PART'] ?? '');
            $description = trim($item['DESCRIPTION'] ?? '');

            if ($part === '' || $description === '') {
                continue;
            }

            $sortCode = self::GNJ_SUBCAT_CODES[$part] ?? $this->abbreviate($part);

            $subCategory = SubCategory::firstOrCreate(
                ['main_category_id' => $category->id, 'sort_code' => $sortCode],
                ['name' => $part, 'is_active' => true],
            );
            if ($subCategory->wasRecentlyCreated) {
                $subCount++;
            }

            $counters[$subCategory->id] = ($counters[$subCategory->id] ?? 0) + 1;
            $sku = sprintf('GNJ-%s-%03d', $sortCode, $counters[$subCategory->id]);

            $price = $this->parsePrice($item['PRICE'] ?? '0');

            $product = Product::updateOrCreate(
                ['sku' => $sku],
                [
                    'name' => "{$part} - {$description}",
                    'vat_rate' => 0,
                    'main_category_id' => $category->id,
                    'sub_category_id' => $subCategory->id,
                    'is_active' => true,
                ],
            );
            $productCount++;

            $qty = $this->parsePrice($item['QTY'] ?? '0');
            if ($qty > 0) {
                $stock[$product->id] = ['qty' => $qty, 'price' => $price];
            }
        }

        $this->info("GNJ: {$productCount} products, {$subCount} new sub-categories.");
        $this->tally('sub_categories', $subCount);
        $this->tally('products', $productCount);

        return $stock;
    }

    /**
     * JMC: everything under a single "General" sub-category, product name = ITEM NAME.
     *
     * @return array<int, array{qty: float, price: float}>
     */
    private function seedJmc(string $sourceDir, MainCategory $category): array
    {
        $subCategory = SubCategory::firstOrCreate(
            ['main_category_id' => $category->id, 'sort_code' => 'GEN'],
            ['name' => 'General', 'is_active' => true],
        );
        if ($subCategory->wasRecentlyCreated) {
            $this->tally('sub_categories', 1);
        }

        $items = $this->readJson($sourceDir, 'JMC.json');
        $seq = 0;
        $stock = [];

        foreach ($items as $item) {
            $name = trim($item['ITEM NAME'] ?? '');
            if ($name === '') {
                continue;
            }

            $seq++;
            $sku = sprintf('JMC-GEN-%03d', $seq);
            $price = $this->parsePrice($item['UNIT PRICE'] ?? '0');

            $product = Product::updateOrCreate(
                ['sku' => $sku],
                [
                    'name' => $name,
                    'vat_rate' => 0,
                    'main_category_id' => $category->id,
                    'sub_category_id' => $subCategory->id,
                    'is_active' => true,
                ],
            );

            $qty = $this->parsePrice($item['QTY'] ?? '0');
            if ($qty > 0) {
                $stock[$product->id] = ['qty' => $qty, 'price' => $price];
            }
        }

        $this->info("JMC: {$seq} products.");
        $this->tally('products', $seq);

        return $stock;
    }

    /**
     * OE: everything under a single "General" sub-category, product name = ITEM, barcode = PART NO.
     *
     * @return array<int, array{qty: float, price: float}>
     */
    private function seedOe(string $sourceDir, MainCategory $category): array
    {
        $subCategory = SubCategory::firstOrCreate(
            ['main_category_id' => $category->id, 'sort_code' => 'GEN'],
            ['name' => 'General', 'is_active' => true],
        );
        if ($subCategory->wasRecentlyCreated) {
            $this->tally('sub_categories', 1);
        }

        $items = $this->readJson($sourceDir, 'OE.json');
        $seq = 0;
        $stock = [];

        foreach ($items as $item) {
            $name = trim($item['ITEM'] ?? '');
            if ($name === '') {
                continue;
            }

            $seq++;
            $sku = sprintf('OE-GEN-%03d', $seq);
            $partNo = trim($item['PART NO'] ?? '');
            $price = $this->parsePrice($item['PRICE'] ?? '0');

            $product = Product::updateOrCreate(
                ['sku' => $sku],
                [
                    'name' => $name,
                    'barcode' => $partNo !== '' ? $partNo : null,
                    'vat_rate' => 0,
                    'main_category_id' => $category->id,
                    'sub_category_id' => $subCategory->id,
                    'is_active' => true,
                ],
            );

            $qty = $this->parsePrice($item['QTY'] ?? '0');
            if ($qty > 0) {
                $stock[$product->id] = ['qty' => $qty, 'price' => $price];
            }
        }

        $this->info("OE: {$seq} products.");
        $this->tally('products', $seq);

        return $stock;
    }

    /**
     * Receive the opening stock as ONE confirmed GRN so it flows through the real
     * stock-in path: GRN → GRN items → confirm → stock movements + branch_stock.
     * Unit cost is the source spreadsheet's own PRICE (the only pricing figure it
     * carries — it already values stock as QTY × PRICE), and the same figure is
     * passed as the line's sale_price, so the opening lots own the price exactly
     * like any received consignment.
     *
     * @param  array<int, array{qty: float, price: float}>  $stockData
     */
    private function createOpeningGrn(array $stockData): void
    {
        if ($this->branchId === null) {
            return;
        }

        $confirmedBy = User::query()->where('email', 'admin@vellix.com')->value('id')
            ?? User::query()->orderBy('id')->value('id');

        if ($confirmedBy === null) {
            $this->warn('  no user found to confirm the opening GRN — run db:seed first. Skipping opening stock.');

            return;
        }

        // Idempotency: only ever one opening GRN per branch, so re-runs never double stock.
        $alreadyReceived = Grn::query()
            ->where('branch_id', $this->branchId)
            ->where('reference', self::OPENING_REFERENCE)
            ->exists();
        if ($alreadyReceived) {
            $this->warn('  opening GRN already exists for this branch — skipping opening stock.');

            return;
        }

        $lines = [];
        foreach ($stockData as $productId => $info) {
            $lines[] = [
                'product_id' => $productId,
                'quantity' => $info['qty'],
                'unit_cost' => $info['price'],
                // The source spreadsheet's own PRICE is the only pricing figure
                // it carries — it values stock as QTY × PRICE. It lands on the
                // lot (the price owner), not the product.
                'sale_price' => $info['price'],
            ];
        }

        if ($lines === []) {
            $this->warn('  no products carry opening stock — nothing to receive.');

            return;
        }

        $grnService = app(GrnService::class);
        $grn = $grnService->create([
            'branch_id' => $this->branchId,
            'reference' => self::OPENING_REFERENCE,
            'note' => 'Opening inventory migrated from the corrected stock-take (latest-data).',
        ], $lines);

        $grnService->confirm($grn, $confirmedBy);

        $this->info("Opening GRN {$grn->grn_number} confirmed with ".count($lines).' line(s).');
        $this->tally('opening_grn', 1);
        $this->tally('opening_grn_items', count($lines));
    }
}
