<?php

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use App\Models\Product;
use App\Models\ReconImportBatch;
use App\Models\ReconImportRow;
use App\Models\ReconImportRowProduct;
use App\Models\ReconImportTemplate;
use App\Models\ReconProductMapping;
use App\Models\ReconProductMappingComponent;
use App\Models\SaleItem;
use Illuminate\Http\Request;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;

class ReconciliationController extends Controller
{
    // ─── Column Templates ─────────────────────────────────────────

    /**
     * GET /api/recon/templates
     */
    public function templateIndex(Request $request)
    {
        $templates = ReconImportTemplate::query()
            ->when($request->channel, fn($q) => $q->where('channel', $request->channel))
            ->orderBy('channel')
            ->get();

        return response()->json($templates);
    }

    /**
     * POST /api/recon/templates
     */
    public function templateStore(Request $request)
    {
        $data = $request->validate([
            'channel'                 => ['required', 'string', 'max:100'],
            'name'                    => ['nullable', 'string', 'max:255'],
            'header_row'              => ['required', 'integer', 'min:0'],
            'column_map'              => ['required', 'array'],
            'column_map.date'         => ['required', 'integer', 'min:0'],
            'column_map.product'      => ['required', 'integer', 'min:0'],
            'column_map.qty'          => ['required', 'integer', 'min:0'],
            'column_map.amount'       => ['required', 'integer', 'min:0'],
            'column_map.status'       => ['nullable', 'integer', 'min:0'],
            'status_exclude_values'   => ['nullable', 'array'],
            'status_exclude_values.*' => ['string', 'max:100'],
        ]);

        $template = ReconImportTemplate::updateOrCreate(
            ['channel' => $data['channel'], 'name' => $data['name'] ?? 'Default'],
            [
                'header_row'            => $data['header_row'],
                'column_map'            => $data['column_map'],
                'status_exclude_values' => $data['status_exclude_values'] ?? null,
                'created_by'            => $request->user()->id,
            ]
        );

        return response()->json($template, 201);
    }

    /**
     * DELETE /api/recon/templates/{template}
     */
    public function templateDestroy(ReconImportTemplate $template)
    {
        $template->delete();

        return response()->json(null, 204);
    }

    // ─── Product Mappings ─────────────────────────────────────────

    /**
     * GET /api/recon/mappings
     */
    public function mappingIndex(Request $request)
    {
        $mappings = ReconProductMapping::with('components.product:id,sku,name')
            ->when($request->channel, fn($q) => $q->where('channel', $request->channel))
            ->when($request->search, fn($q) => $q->where(fn($sub) => $sub
                ->where('external_key', 'like', "%{$request->search}%")
                ->orWhere('external_label', 'like', "%{$request->search}%")
                ->orWhereHas('components.product', fn($p) => $p
                    ->where('name', 'like', "%{$request->search}%")
                    ->orWhere('sku', 'like', "%{$request->search}%"))))
            ->orderBy('channel')
            ->orderBy('external_key')
            ->get();

        return response()->json($mappings);
    }

    /**
     * POST /api/recon/mappings
     */
    public function mappingStore(Request $request)
    {
        $data = $request->validate([
            'channel'                     => ['required', 'string', 'max:100'],
            'external_key'                => ['required', 'string', 'max:255'],
            'external_label'              => ['nullable', 'string', 'max:255'],
            'notes'                       => ['nullable', 'string', 'max:255'],
            'components'                  => ['required', 'array', 'min:1'],
            'components.*.product_id'     => ['required', 'integer', 'exists:products,id', 'distinct'],
            'components.*.qty_multiplier' => ['required', 'numeric', 'min:0.01', 'max:9999.99'],
        ]);

        $data['external_key'] = ReconProductMapping::normalizeKey($data['external_key']);
        $data['external_label'] = $data['external_label'] ?? $data['external_key'];

        $mapping = DB::transaction(function () use ($data) {
            $mapping = ReconProductMapping::updateOrCreate(
                ['channel' => $data['channel'], 'external_key' => $data['external_key']],
                ['external_label' => $data['external_label'], 'notes' => $data['notes'] ?? null]
            );

            $this->syncComponents($mapping, $data['components']);
            $this->backfillRows($mapping);

            return $mapping;
        });

        return response()->json($mapping->load('components.product:id,sku,name'), 201);
    }

    /**
     * PUT /api/recon/mappings/{mapping}
     */
    public function mappingUpdate(Request $request, ReconProductMapping $mapping)
    {
        $data = $request->validate([
            'external_label'              => ['nullable', 'string', 'max:255'],
            'notes'                       => ['nullable', 'string', 'max:255'],
            'components'                  => ['required', 'array', 'min:1'],
            'components.*.product_id'     => ['required', 'integer', 'exists:products,id', 'distinct'],
            'components.*.qty_multiplier' => ['required', 'numeric', 'min:0.01', 'max:9999.99'],
        ]);

        DB::transaction(function () use ($mapping, $data) {
            $mapping->update([
                'external_label' => $data['external_label'] ?? $mapping->external_label,
                'notes'          => $data['notes'] ?? null,
            ]);
            $this->syncComponents($mapping, $data['components']);
            $this->backfillRows($mapping, repoint: true);
        });

        return response()->json($mapping->load('components.product:id,sku,name'));
    }

    /**
     * DELETE /api/recon/mappings/{mapping}
     */
    public function mappingDestroy(ReconProductMapping $mapping)
    {
        $mapping->delete();

        return response()->json(null, 204);
    }

    /**
     * Replace a mapping's components wholesale. Cardinality is small (rarely
     * more than a handful per mapping), so delete+reinsert is simpler and
     * safer than diffing.
     */
    private function syncComponents(ReconProductMapping $mapping, array $components): void
    {
        $mapping->components()->delete();

        $now = now();
        ReconProductMappingComponent::insert(collect($components)->map(fn ($c) => [
            'mapping_id'     => $mapping->id,
            'product_id'     => $c['product_id'],
            'qty_multiplier' => $c['qty_multiplier'],
            'created_at'     => $now,
            'updated_at'     => $now,
        ])->all());
    }

    /**
     * Resolve import rows for a mapping's channel + external_key.
     * With $repoint, already-resolved rows are re-pointed too (components changed).
     */
    private function backfillRows(ReconProductMapping $mapping, bool $repoint = false): void
    {
        $components = $mapping->components()->with('product:id,sell_price')->orderBy('id')->get();
        if ($components->isEmpty()) {
            return;
        }

        $rowsQuery = ReconImportRow::query()
            ->where('external_key', $mapping->external_key)
            ->whereIn('batch_id', ReconImportBatch::where('channel', $mapping->channel)->select('id'));

        if (!$repoint) {
            $rowsQuery->whereDoesntHave('items');
        }

        $rows = $rowsQuery->get(['id', 'qty', 'amount']);
        if ($rows->isEmpty()) {
            return;
        }

        ReconImportRowProduct::whereIn('row_id', $rows->pluck('id'))->delete();

        $now = now();
        $inserts = [];
        foreach ($rows as $row) {
            foreach ($this->splitRowAcrossComponents($row->qty, $row->amount, $components) as $piece) {
                $inserts[] = [
                    'row_id'     => $row->id,
                    'product_id' => $piece['product_id'],
                    'qty'        => $piece['qty'],
                    'amount'     => $piece['amount'],
                    'created_at' => $now,
                    'updated_at' => $now,
                ];
            }
        }

        foreach (array_chunk($inserts, 500) as $chunk) {
            ReconImportRowProduct::insert($chunk);
        }
    }

    /**
     * Split a row's qty/amount across a mapping's components:
     *  - qty_i = row.qty * component.qty_multiplier (rounded)
     *  - amount_i = floor(row.amount * weight_i / total_weight), weight_i = sell_price_i * qty_multiplier_i
     *  - the LAST component absorbs the remainder so amounts always sum
     *    exactly to row.amount, avoiding int-rounding drift.
     *  - if every component's sell_price is 0/null, falls back to an even split.
     *
     * @param  Collection<int, ReconProductMappingComponent>  $components  must have `product` eager loaded
     * @return array<int, array{product_id:int, qty:int, amount:int}>
     */
    private function splitRowAcrossComponents(int $qty, int $amount, Collection $components): array
    {
        $n = $components->count();
        $weights = $components->map(
            fn ($c) => max(0, (int) ($c->product->sell_price ?? 0)) * (float) $c->qty_multiplier
        );
        $totalWeight = $weights->sum();

        $result = [];
        $amountUsed = 0;
        $i = 0;

        foreach ($components as $component) {
            $i++;
            $isLast = $i === $n;
            $componentQty = (int) round($qty * (float) $component->qty_multiplier);

            if ($isLast) {
                $componentAmount = $amount - $amountUsed;
            } elseif ($totalWeight > 0) {
                $weight = max(0, (int) ($component->product->sell_price ?? 0)) * (float) $component->qty_multiplier;
                $componentAmount = (int) floor($amount * $weight / $totalWeight);
            } else {
                $componentAmount = (int) floor($amount / $n);
            }

            $amountUsed += $componentAmount;
            $result[] = ['product_id' => $component->product_id, 'qty' => $componentQty, 'amount' => $componentAmount];
        }

        return $result;
    }

    // ─── Import Batches ───────────────────────────────────────────

    /**
     * GET /api/recon/batches
     */
    public function batchIndex(Request $request)
    {
        $batches = ReconImportBatch::with('importedBy:id,name')
            ->when($request->channel, fn($q) => $q->where('channel', $request->channel))
            ->latest()
            ->get()
            ->map(fn($b) => [
                'id'             => $b->id,
                'channel'        => $b->channel,
                'date_from'      => $b->date_from->toDateString(),
                'date_to'        => $b->date_to->toDateString(),
                'file_name'      => $b->file_name,
                'row_count'      => $b->row_count,
                'excluded_count' => $b->excluded_count,
                'status'         => $b->status,
                'superseded_by'  => $b->superseded_by,
                'imported_by'    => $b->importedBy?->name ?? 'Unknown',
                'created_at'     => $b->created_at->toISOString(),
            ]);

        return response()->json($batches);
    }

    /**
     * POST /api/recon/batches
     * Receives client-parsed rows; stores the batch for reconciliation.
     */
    public function batchStore(Request $request)
    {
        $data = $request->validate([
            'channel'               => ['required', 'string', 'max:100'],
            'file_name'             => ['nullable', 'string', 'max:255'],
            'supersede'             => ['nullable', 'boolean'],
            'template_snapshot'     => ['nullable', 'array'],
            'rows'                  => ['required', 'array', 'min:1', 'max:10000'],
            'rows.*.row_number'     => ['required', 'integer', 'min:1'],
            'rows.*.sale_date'      => ['required', 'date_format:Y-m-d'],
            'rows.*.external_label' => ['required', 'string', 'max:255'],
            'rows.*.qty'            => ['required', 'integer', 'min:0'],
            'rows.*.amount'         => ['required', 'integer', 'min:0'],
            'rows.*.order_status'   => ['nullable', 'string', 'max:100'],
            'rows.*.excluded'       => ['nullable', 'boolean'],
        ]);

        $activeRows = collect($data['rows'])->filter(fn($r) => !($r['excluded'] ?? false));
        if ($activeRows->isEmpty()) {
            return response()->json(['message' => 'Semua baris dikecualikan — tidak ada data untuk direkonsiliasi.'], 422);
        }

        $dateFrom = $activeRows->min('sale_date');
        $dateTo   = $activeRows->max('sale_date');

        return DB::transaction(function () use ($data, $request, $dateFrom, $dateTo) {
            $overlapping = ReconImportBatch::query()
                ->where('channel', $data['channel'])
                ->where('status', 'active')
                ->whereDate('date_from', '<=', $dateTo)
                ->whereDate('date_to', '>=', $dateFrom)
                ->lockForUpdate()
                ->get();

            if ($overlapping->isNotEmpty() && !($data['supersede'] ?? false)) {
                return response()->json([
                    'message'     => 'Periode ini sudah pernah diimpor untuk channel yang sama.',
                    'overlapping' => $overlapping->map(fn($b) => [
                        'id'        => $b->id,
                        'date_from' => $b->date_from->toDateString(),
                        'date_to'   => $b->date_to->toDateString(),
                        'file_name' => $b->file_name,
                        'row_count' => $b->row_count,
                    ]),
                ], 409);
            }

            $excludedCount = count($data['rows']) - collect($data['rows'])->filter(fn($r) => !($r['excluded'] ?? false))->count();

            $batch = ReconImportBatch::create([
                'channel'           => $data['channel'],
                'date_from'         => $dateFrom,
                'date_to'           => $dateTo,
                'file_name'         => $data['file_name'] ?? null,
                'row_count'         => count($data['rows']),
                'excluded_count'    => $excludedCount,
                'template_snapshot' => $data['template_snapshot'] ?? null,
                'imported_by'       => $request->user()->id,
            ]);

            if ($overlapping->isNotEmpty()) {
                ReconImportBatch::whereIn('id', $overlapping->pluck('id'))
                    ->update(['status' => 'superseded', 'superseded_by' => $batch->id]);
            }

            $mappings = ReconProductMapping::with('components.product:id,sell_price')
                ->where('channel', $data['channel'])
                ->get()
                ->keyBy('external_key');

            $now = now();
            $rows = collect($data['rows'])->map(function ($r) use ($batch, $now) {
                $key = ReconProductMapping::normalizeKey($r['external_label']);
                return [
                    'batch_id'       => $batch->id,
                    'row_number'     => $r['row_number'],
                    'sale_date'      => $r['sale_date'],
                    'external_key'   => $key,
                    'external_label' => $r['external_label'],
                    'qty'            => $r['qty'],
                    'amount'         => $r['amount'],
                    'order_status'   => $r['order_status'] ?? null,
                    'excluded'       => $r['excluded'] ?? false,
                    'created_at'     => $now,
                    'updated_at'     => $now,
                ];
            });

            foreach ($rows->chunk(500) as $chunk) {
                ReconImportRow::insert($chunk->values()->toArray());
            }

            // Raw insert() doesn't return generated IDs, so re-select the rows
            // just inserted — safe since batch_id was freshly created inside
            // this same transaction and uniquely scopes to this request.
            $insertedRows = ReconImportRow::where('batch_id', $batch->id)
                ->get(['id', 'external_key', 'qty', 'amount']);

            $rowProductInserts = [];
            foreach ($insertedRows as $insertedRow) {
                $mapping = $mappings->get($insertedRow->external_key);
                if (!$mapping || $mapping->components->isEmpty()) {
                    continue;
                }
                foreach ($this->splitRowAcrossComponents($insertedRow->qty, $insertedRow->amount, $mapping->components) as $piece) {
                    $rowProductInserts[] = [
                        'row_id'     => $insertedRow->id,
                        'product_id' => $piece['product_id'],
                        'qty'        => $piece['qty'],
                        'amount'     => $piece['amount'],
                        'created_at' => $now,
                        'updated_at' => $now,
                    ];
                }
            }

            foreach (array_chunk($rowProductInserts, 500) as $chunk) {
                ReconImportRowProduct::insert($chunk);
            }

            $unmapped = ReconImportRow::query()
                ->where('batch_id', $batch->id)
                ->where('excluded', false)
                ->whereDoesntHave('items')
                ->groupBy('external_key')
                ->selectRaw('external_key, MIN(external_label) as external_label, SUM(qty) as total_qty')
                ->orderByDesc(DB::raw('SUM(qty)'))
                ->get();

            return response()->json([
                'batch_id'  => $batch->id,
                'channel'   => $batch->channel,
                'date_from' => $dateFrom,
                'date_to'   => $dateTo,
                'row_count' => $batch->row_count,
                'excluded_count' => $batch->excluded_count,
                'unmapped'  => $unmapped,
            ], 201);
        });
    }

    /**
     * GET /api/recon/batches/{batch}
     */
    public function batchShow(ReconImportBatch $batch)
    {
        $batch->load(['importedBy:id,name', 'rows.items.product:id,sku,name']);

        return response()->json([
            'id'             => $batch->id,
            'channel'        => $batch->channel,
            'date_from'      => $batch->date_from->toDateString(),
            'date_to'        => $batch->date_to->toDateString(),
            'file_name'      => $batch->file_name,
            'row_count'      => $batch->row_count,
            'excluded_count' => $batch->excluded_count,
            'status'         => $batch->status,
            'imported_by'    => $batch->importedBy?->name ?? 'Unknown',
            'created_at'     => $batch->created_at->toISOString(),
            'rows'           => $batch->rows->map(fn($r) => [
                'row_number'     => $r->row_number,
                'sale_date'      => $r->sale_date->toDateString(),
                'external_label' => $r->external_label,
                'qty'            => $r->qty,
                'amount'         => $r->amount,
                'order_status'   => $r->order_status,
                'excluded'       => $r->excluded,
                'items'          => $r->items->map(fn($item) => [
                    'qty'     => $item->qty,
                    'amount'  => $item->amount,
                    'product' => $item->product ? [
                        'id'   => $item->product->id,
                        'sku'  => $item->product->sku,
                        'name' => $item->product->name,
                    ] : null,
                ]),
            ]),
        ]);
    }

    /**
     * DELETE /api/recon/batches/{batch}
     */
    public function batchDestroy(ReconImportBatch $batch)
    {
        $batch->delete();

        return response()->json(null, 204);
    }

    // ─── Reconciliation Report ────────────────────────────────────

    /**
     * GET /api/recon/report
     * Compares imported e-commerce rows vs manual sales, per product per day.
     */
    public function report(Request $request)
    {
        $request->validate([
            'channel'   => ['required', 'string', 'max:100'],
            'date_from' => ['required', 'date'],
            'date_to'   => ['required', 'date', 'after_or_equal:date_from'],
        ]);

        // Side A: imported rows from active batches, cancelled rows excluded.
        // Left-joined through recon_import_row_products so a row mapped to N
        // components fans out into N per-product groups, while an unmapped
        // row (no items) still yields exactly one group via COALESCE fallback
        // to the row's own raw qty/amount.
        $imported = ReconImportRow::query()
            ->join('recon_import_batches as b', 'b.id', '=', 'recon_import_rows.batch_id')
            ->leftJoin('recon_import_row_products as rip', 'rip.row_id', '=', 'recon_import_rows.id')
            ->where('b.channel', $request->channel)
            ->where('b.status', 'active')
            ->where('recon_import_rows.excluded', false)
            ->whereBetween('recon_import_rows.sale_date', [$request->date_from, $request->date_to])
            ->selectRaw('recon_import_rows.sale_date,
                         rip.product_id as product_id,
                         recon_import_rows.external_key,
                         MIN(recon_import_rows.external_label) as external_label,
                         SUM(COALESCE(rip.qty, recon_import_rows.qty)) as qty,
                         SUM(COALESCE(rip.amount, recon_import_rows.amount)) as amount')
            ->groupBy('recon_import_rows.sale_date', 'rip.product_id', 'recon_import_rows.external_key')
            ->get();

        // Dates actually covered by an active batch: manual-only rows outside
        // coverage must not be flagged as missing_import.
        $coveredRanges = ReconImportBatch::query()
            ->where('channel', $request->channel)
            ->where('status', 'active')
            ->whereDate('date_from', '<=', $request->date_to)
            ->whereDate('date_to', '>=', $request->date_from)
            ->get(['date_from', 'date_to']);

        // Side B: manual sales. The join bypasses Sale's SoftDeletes global
        // scope, so deleted_at must be filtered explicitly.
        $manual = SaleItem::query()
            ->join('sales', 'sales.id', '=', 'sale_items.sale_id')
            ->where('sales.channel', $request->channel)
            ->whereNull('sales.voided_at')
            ->whereNull('sales.deleted_at')
            ->whereBetween(DB::raw('DATE(sales.created_at)'), [$request->date_from, $request->date_to])
            ->selectRaw('DATE(sales.created_at) as sale_date,
                         sale_items.product_id,
                         SUM(sale_items.qty) as qty,
                         SUM(sale_items.subtotal) as amount')
            ->groupBy(DB::raw('DATE(sales.created_at)'), 'sale_items.product_id')
            ->get();

        $rows = [];

        foreach ($imported as $imp) {
            $date = $imp->sale_date instanceof \Carbon\CarbonInterface
                ? $imp->sale_date->toDateString()
                : substr((string) $imp->sale_date, 0, 10);
            $key = $imp->product_id
                ? "{$date}|{$imp->product_id}"
                : "{$date}|key:{$imp->external_key}";
            $rows[$key] = [
                'sale_date'      => $date,
                'product_id'     => $imp->product_id,
                'external_label' => $imp->external_label,
                'import_qty'     => (int) $imp->qty,
                'import_amount'  => (int) $imp->amount,
                'manual_qty'     => 0,
                'manual_amount'  => 0,
            ];
        }

        foreach ($manual as $m) {
            $key = "{$m->sale_date}|{$m->product_id}";
            if (isset($rows[$key])) {
                $rows[$key]['manual_qty']    = (int) $m->qty;
                $rows[$key]['manual_amount'] = (int) $m->amount;
            } else {
                $rows[$key] = [
                    'sale_date'      => $m->sale_date,
                    'product_id'     => $m->product_id,
                    'external_label' => null,
                    'import_qty'     => 0,
                    'import_amount'  => 0,
                    'manual_qty'     => (int) $m->qty,
                    'manual_amount'  => (int) $m->amount,
                ];
            }
        }

        $products = Product::whereIn('id', collect($rows)->pluck('product_id')->filter()->unique())
            ->get(['id', 'sku', 'name'])
            ->keyBy('id');

        $isCovered = fn(string $date) => $coveredRanges->contains(fn($b) =>
            $date >= $b->date_from->toDateString() && $date <= $b->date_to->toDateString());

        $result = collect($rows)->map(function ($row) use ($products, $isCovered) {
            $product = $row['product_id'] ? $products->get($row['product_id']) : null;

            if (!$row['product_id']) {
                $status = 'unmapped';
            } elseif ($row['import_qty'] > 0 && $row['manual_qty'] === 0) {
                $status = 'missing_manual';
            } elseif ($row['manual_qty'] > 0 && $row['import_qty'] === 0) {
                $status = $isCovered($row['sale_date']) ? 'missing_import' : 'not_covered';
            } else {
                $qtyMatch    = $row['import_qty'] === $row['manual_qty'];
                $amountMatch = $row['import_amount'] === $row['manual_amount'];
                $status = match (true) {
                    $qtyMatch && $amountMatch  => 'match',
                    !$qtyMatch && $amountMatch => 'qty_mismatch',
                    $qtyMatch && !$amountMatch => 'amount_mismatch',
                    default                    => 'both_mismatch',
                };
            }

            return [
                'sale_date'      => $row['sale_date'],
                'product_id'     => $row['product_id'],
                'sku'            => $product?->sku,
                'product_name'   => $product?->name,
                'external_label' => $row['external_label'],
                'import_qty'     => $row['import_qty'],
                'manual_qty'     => $row['manual_qty'],
                'qty_diff'       => $row['import_qty'] - $row['manual_qty'],
                'import_amount'  => $row['import_amount'],
                'manual_amount'  => $row['manual_amount'],
                'amount_diff'    => $row['import_amount'] - $row['manual_amount'],
                'status'         => $status,
            ];
        })
        ->filter(fn($r) => $r['status'] !== 'not_covered')
        ->sortBy([['sale_date', 'asc'], ['product_name', 'asc']])
        ->values();

        return response()->json([
            'summary' => [
                'total_rows'          => $result->count(),
                'by_status'           => $result->groupBy('status')->map->count(),
                'total_import_qty'    => $result->sum('import_qty'),
                'total_manual_qty'    => $result->sum('manual_qty'),
                'total_import_amount' => $result->sum('import_amount'),
                'total_manual_amount' => $result->sum('manual_amount'),
            ],
            'data' => $result,
        ]);
    }
}
