<?php

namespace App\Services;

use App\Models\Company;
use App\Models\CreditNote;
use App\Models\Invoice;
use App\Models\InvoiceItem;
use App\Models\Item;
use App\Models\PurchaseBill;
use App\Models\PurchaseBillItem;
use Carbon\Carbon;
use Illuminate\Support\Collection;

class InventoryReportService
{
    public function __construct(
        private readonly SpreadsheetExporter $exporter,
    ) {}

    /**
     * @return array{company: Company, date_from: string, date_to: string, sheets: array<string, string>}
     */
    public function build(int $companyId, string $reportKey, string $dateFrom, string $dateTo, array $options = []): array
    {
        $company = Company::findOrFail($companyId);
        $start = Carbon::parse($dateFrom)->startOfDay();
        $end = Carbon::parse($dateTo)->endOfDay();

        $sheets = match ($reportKey) {
            'sales_by_product' => $this->salesByItemType($companyId, $start, $end, 'product'),
            'sales_by_service' => $this->salesByItemType($companyId, $start, $end, 'service'),
            'sales_inventory' => $this->salesInventory($companyId, $start, $end),
            'sales_by_client' => $this->salesByClient($companyId, $start, $end),
            'sales_by_user' => $this->salesByUser($companyId, $start, $end),
            'in_out_inventory' => $this->inOutInventory($companyId, $start, $end, $options),
            'invoice_tax_breakdown' => $this->invoiceTaxBreakdown($companyId, $start, $end),
            'e_document' => $this->eDocument($companyId, $start, $end),
            default => throw new \InvalidArgumentException("Unknown inventory report: {$reportKey}"),
        };

        $sheets = ['Summary' => $this->summarySheet($company, $reportKey, $dateFrom, $dateTo, $options)] + $sheets;

        return [
            'company' => $company,
            'date_from' => $dateFrom,
            'date_to' => $dateTo,
            'sheets' => $sheets,
        ];
    }

    public function download(array $report, string $filename): \Symfony\Component\HttpFoundation\StreamedResponse
    {
        return $this->exporter->download($report['sheets'], $filename);
    }

    /**
     * @return array<string, string>
     */
    private function salesByItemType(int $companyId, Carbon $start, Carbon $end, string $itemType): array
    {
        $lines = $this->invoiceLinesInRange($companyId, $start, $end);
        $items = Item::where('company_id', $companyId)->get()->keyBy('id');
        $buckets = [];

        foreach ($lines as $line) {
            $item = $line['item_id'] ? $items->get($line['item_id']) : null;
            if ($item && $item->type !== $itemType) {
                continue;
            }
            if (! $item && $itemType === 'service') {
                continue;
            }
            $key = $line['item_id'] ?: 'name:'.$line['item_name'];
            if (! isset($buckets[$key])) {
                $buckets[$key] = [
                    'name' => $line['item_name'],
                    'sku' => $item?->sku ?? '',
                    'hsn' => $line['hsn_sac'] ?? $item?->hsn_sac ?? '',
                    'qty' => 0,
                    'sales' => 0,
                    'cogs' => 0,
                ];
            }
            $qty = (float) $line['quantity'];
            $taxable = $this->lineTaxable($line);
            $buckets[$key]['qty'] += $qty;
            $buckets[$key]['sales'] += $taxable;
            $costRate = (float) ($item?->purchase_rate ?? 0);
            $buckets[$key]['cogs'] += $costRate * $qty;
        }

        $headers = ['Item', 'SKU', 'HSN/SAC', 'Qty Sold', 'Sales Amount', 'Cost of Goods', 'Gross Margin'];
        $rows = [];
        foreach ($buckets as $b) {
            $margin = round($b['sales'] - $b['cogs'], 2);
            $rows[] = [
                $b['name'], $b['sku'], $b['hsn'],
                round($b['qty'], 3), round($b['sales'], 2), round($b['cogs'], 2), $margin,
            ];
        }

        $sheetName = $itemType === 'product' ? 'Sales by Product' : 'Sales by Service';

        return [$sheetName => $this->exporter->sheetFromRows($headers, $rows)];
    }

    /**
     * @return array<string, string>
     */
    private function salesInventory(int $companyId, Carbon $start, Carbon $end): array
    {
        $headers = ['Date', 'Invoice #', 'Client', 'Item', 'Unit', 'Qty', 'Rate', 'Line Total', 'Account'];
        $rows = [];

        $invoices = $this->invoicesQuery($companyId, $start, $end)->with(['customer', 'items.item'])->get();

        foreach ($invoices as $invoice) {
            foreach ($invoice->items as $item) {
                $itemModel = $item->item;
                if ($itemModel && ! $itemModel->track_inventory) {
                    continue;
                }
                $lineTotal = $this->lineTotal($item);
                $rows[] = [
                    $invoice->invoice_date->format('d-m-Y'),
                    $invoice->invoice_number,
                    $invoice->customer?->name ?? '',
                    $item->item_name,
                    $item->unit ?? 'NOS',
                    (float) $item->quantity,
                    (float) $item->rate,
                    $lineTotal,
                    'Sales',
                ];
            }
        }

        return ['Sales Inventory' => $this->exporter->sheetFromRows($headers, $rows)];
    }

    /**
     * @return array<string, string>
     */
    private function salesByClient(int $companyId, Carbon $start, Carbon $end): array
    {
        $headers = [
            'Client', 'GSTIN', 'Invoice Count', 'Taxable Amount', 'CGST', 'SGST', 'IGST',
            'Total Tax', 'Grand Total', 'Paid', 'Balance', 'Status',
        ];
        $rows = [];
        $invoices = $this->invoicesQuery($companyId, $start, $end)->with('customer')->get();
        $grouped = $invoices->groupBy('customer_id');

        foreach ($grouped as $group) {
            $first = $group->first();
            $customer = $first->customer;
            $rows[] = [
                $customer?->name ?? '—',
                $customer?->gstin ?? '',
                $group->count(),
                round($group->sum(fn ($i) => (float) $i->taxable_amount), 2),
                round($group->sum(fn ($i) => (float) $i->cgst_amount), 2),
                round($group->sum(fn ($i) => (float) $i->sgst_amount), 2),
                round($group->sum(fn ($i) => (float) $i->igst_amount), 2),
                round($group->sum(fn ($i) => (float) $i->total_tax), 2),
                round($group->sum(fn ($i) => (float) $i->grand_total), 2),
                round($group->sum(fn ($i) => (float) $i->paid_amount), 2),
                round($group->sum(fn ($i) => $i->balance), 2),
                $this->aggregateStatus($group),
            ];
        }

        return ['Sales by Client' => $this->exporter->sheetFromRows($headers, $rows)];
    }

    /**
     * @return array<string, string>
     */
    private function salesByUser(int $companyId, Carbon $start, Carbon $end): array
    {
        $headers = [
            'User', 'Invoice Count', 'Taxable Amount', 'CGST', 'SGST', 'IGST',
            'Total Tax', 'Grand Total', 'Balance', 'Status',
        ];
        $rows = [];
        $invoices = $this->invoicesQuery($companyId, $start, $end)->get();
        $grouped = $invoices->groupBy(fn ($i) => $i->sales_person ?: 'Unassigned');

        foreach ($grouped as $user => $group) {
            $rows[] = [
                $user,
                $group->count(),
                round($group->sum(fn ($i) => (float) $i->taxable_amount), 2),
                round($group->sum(fn ($i) => (float) $i->cgst_amount), 2),
                round($group->sum(fn ($i) => (float) $i->sgst_amount), 2),
                round($group->sum(fn ($i) => (float) $i->igst_amount), 2),
                round($group->sum(fn ($i) => (float) $i->total_tax), 2),
                round($group->sum(fn ($i) => (float) $i->grand_total), 2),
                round($group->sum(fn ($i) => $i->balance), 2),
                $this->aggregateStatus($group),
            ];
        }

        return ['Sales by User' => $this->exporter->sheetFromRows($headers, $rows)];
    }

    /**
     * @param  array<string, mixed>  $options
     * @return array<string, string>
     */
    private function inOutInventory(int $companyId, Carbon $start, Carbon $end, array $options): array
    {
        $includeServices = (bool) ($options['include_services'] ?? false);
        $headers = ['Date', 'Type', 'Document #', 'Party', 'Item', 'Unit', 'Qty', 'Rate', 'Amount'];
        $rows = [];

        $purchaseBills = PurchaseBill::with(['vendor', 'items.item'])
            ->forCompany($companyId)
            ->whereNotIn('status', ['draft', 'void'])
            ->whereDate('bill_date', '>=', $start)
            ->whereDate('bill_date', '<=', $end)
            ->orderBy('bill_date')
            ->get();

        foreach ($purchaseBills as $bill) {
            foreach ($bill->items as $line) {
                if (! $this->lineIncluded($line->item, $includeServices)) {
                    continue;
                }
                $rows[] = [
                    $bill->bill_date->format('d-m-Y'),
                    'IN',
                    $bill->bill_number,
                    $bill->vendor?->name ?? '',
                    $line->item_name,
                    $line->unit ?? 'NOS',
                    (float) $line->quantity,
                    (float) $line->rate,
                    $this->lineTotalFromArray($line),
                ];
            }
        }

        $invoices = $this->invoicesQuery($companyId, $start, $end)
            ->with(['customer', 'items.item'])
            ->orderBy('invoice_date')
            ->get();

        foreach ($invoices as $invoice) {
            foreach ($invoice->items as $line) {
                if (! $this->lineIncluded($line->item, $includeServices)) {
                    continue;
                }
                $rows[] = [
                    $invoice->invoice_date->format('d-m-Y'),
                    'OUT',
                    $invoice->invoice_number,
                    $invoice->customer?->name ?? '',
                    $line->item_name,
                    $line->unit ?? 'NOS',
                    (float) $line->quantity,
                    (float) $line->rate,
                    $this->lineTotal($line),
                ];
            }
        }

        usort($rows, fn ($a, $b) => strcmp($a[0].$a[2], $b[0].$b[2]));

        return ['In and Out' => $this->exporter->sheetFromRows($headers, $rows)];
    }

    /**
     * @return array<string, string>
     */
    private function invoiceTaxBreakdown(int $companyId, Carbon $start, Carbon $end): array
    {
        $taxRates = $this->distinctTaxRates($companyId, $start, $end);
        $headers = array_merge(
            ['Date', 'Invoice #', 'Client', 'Type', 'Taxable Amount'],
            array_map(fn ($r) => "Tax @ {$r}%", $taxRates),
            ['CGST', 'SGST', 'IGST', 'Total Tax', 'Grand Total', 'Status']
        );

        $rows = [];
        $invoices = $this->invoicesQuery($companyId, $start, $end)->with('customer')->get();

        foreach ($invoices as $invoice) {
            $rateAmounts = array_fill_keys($taxRates, 0.0);
            foreach ($invoice->items as $item) {
                $rate = number_format((float) $item->tax_rate, 2, '.', '');
                if (isset($rateAmounts[$rate])) {
                    $rateAmounts[$rate] += (float) $item->tax_amount;
                }
            }
            $row = [
                $invoice->invoice_date->format('d-m-Y'),
                $invoice->invoice_number,
                $invoice->customer?->name ?? '',
                $invoice->typeLabel(),
                (float) $invoice->taxable_amount,
            ];
            foreach ($taxRates as $rate) {
                $row[] = round($rateAmounts[$rate] ?? 0, 2);
            }
            $row[] = (float) $invoice->cgst_amount;
            $row[] = (float) $invoice->sgst_amount;
            $row[] = (float) $invoice->igst_amount;
            $row[] = (float) $invoice->total_tax;
            $row[] = (float) $invoice->grand_total;
            $row[] = ucfirst($invoice->display_status);

            $rows[] = $row;
        }

        return ['Invoices' => $this->exporter->sheetFromRows($headers, $rows)];
    }

    /**
     * @return array<string, string>
     */
    private function eDocument(int $companyId, Carbon $start, Carbon $end): array
    {
        $headers = ['Date', 'Document', 'Type', 'Party', 'E-Invoice IRN', 'E-Way Bill', 'Amount', 'Status'];
        $rows = [];

        $invoices = $this->invoicesQuery($companyId, $start, $end)
            ->with('customer')
            ->where(function ($q) {
                $q->whereNotNull('einvoice_irn')->where('einvoice_irn', '!=', '')
                    ->orWhereNotNull('eway_bill_no')->where('eway_bill_no', '!=', '');
            })
            ->get();

        foreach ($invoices as $invoice) {
            $rows[] = [
                $invoice->invoice_date->format('d-m-Y'),
                $invoice->invoice_number,
                'Invoice',
                $invoice->customer?->name ?? '',
                $invoice->einvoice_irn ?? '',
                $invoice->eway_bill_no ?? '',
                (float) $invoice->grand_total,
                ucfirst($invoice->display_status),
            ];
        }

        return ['E Documents' => $this->exporter->sheetFromRows($headers, $rows)];
    }

    /**
     * @param  array<string, mixed>  $options
     */
    private function summarySheet(Company $company, string $reportKey, string $dateFrom, string $dateTo, array $options): string
    {
        $meta = config("reports.inventory.{$reportKey}", []);
        $lines = [
            [$meta['title'] ?? $reportKey],
            ['Company', $company->name ?? ''],
            ['GSTIN', $company->gstin ?? ''],
            ['Period', "{$dateFrom} to {$dateTo}"],
            ['Generated', now()->format('d-m-Y H:i')],
        ];
        if (! empty($options['include_services'])) {
            $lines[] = ['Include services', 'Yes'];
        }
        $lines[] = [];
        foreach ($meta['description'] ?? [] as $desc) {
            $lines[] = [$desc];
        }

        $xml = '';
        foreach ($lines as $line) {
            $xml .= $this->exporter->row($line, false);
        }

        return $xml;
    }

    /**
     * @return list<array<string, mixed>>
     */
    private function invoiceLinesInRange(int $companyId, Carbon $start, Carbon $end): array
    {
        $lines = [];
        $invoices = $this->invoicesQuery($companyId, $start, $end)->with('items')->get();
        foreach ($invoices as $invoice) {
            foreach ($invoice->items as $item) {
                $lines[] = [
                    'item_id' => $item->item_id,
                    'item_name' => $item->item_name,
                    'hsn_sac' => $item->hsn_sac,
                    'quantity' => $item->quantity,
                    'rate' => $item->rate,
                    'discount_percent' => $item->discount_percent,
                ];
            }
        }

        $creditNotes = CreditNote::forCompany($companyId)
            ->whereNotIn('status', ['draft', 'void'])
            ->whereDate('credit_note_date', '>=', $start)
            ->whereDate('credit_note_date', '<=', $end)
            ->with('items')
            ->get();

        foreach ($creditNotes as $note) {
            foreach ($note->items as $item) {
                $lines[] = [
                    'item_id' => $item->item_id,
                    'item_name' => $item->item_name,
                    'hsn_sac' => $item->hsn_sac,
                    'quantity' => -(float) $item->quantity,
                    'rate' => $item->rate,
                    'discount_percent' => $item->discount_percent,
                ];
            }
        }

        return $lines;
    }

    /**
     * @return list<string>
     */
    private function distinctTaxRates(int $companyId, Carbon $start, Carbon $end): array
    {
        $rates = [];
        $this->invoicesQuery($companyId, $start, $end)->with('items')->get()
            ->each(function (Invoice $invoice) use (&$rates) {
                foreach ($invoice->items as $item) {
                    $rates[] = number_format((float) $item->tax_rate, 2, '.', '');
                }
            });

        $rates = array_unique($rates);
        sort($rates, SORT_NUMERIC);

        return array_values($rates);
    }

    private function invoicesQuery(int $companyId, Carbon $start, Carbon $end)
    {
        return Invoice::forCompany($companyId)
            ->whereNotIn('status', ['draft', 'cancelled'])
            ->whereDate('invoice_date', '>=', $start)
            ->whereDate('invoice_date', '<=', $end);
    }

    /**
     * @param  array<string, mixed>  $line
     */
    private function lineTaxable(array $line): float
    {
        $qty = abs((float) $line['quantity']);
        $rate = (float) $line['rate'];
        $disc = (float) ($line['discount_percent'] ?? 0);

        return max(0, ($qty * $rate) - ($qty * $rate * $disc / 100));
    }

    private function lineTotal(InvoiceItem $item): float
    {
        return round($this->lineTaxable([
            'quantity' => $item->quantity,
            'rate' => $item->rate,
            'discount_percent' => $item->discount_percent,
        ]) + (float) $item->tax_amount, 2);
    }

    private function lineTotalFromArray(PurchaseBillItem $item): float
    {
        $qty = (float) $item->quantity;
        $rate = (float) $item->rate;
        $disc = (float) $item->discount_percent;
        $taxable = max(0, ($qty * $rate) - ($qty * $rate * $disc / 100));

        return round($taxable + (float) $item->tax_amount, 2);
    }

    private function lineIncluded(?Item $item, bool $includeServices): bool
    {
        if (! $item) {
            return true;
        }
        if ($item->type === 'service' && ! $includeServices) {
            return false;
        }

        return $item->track_inventory || $includeServices || $item->type === 'product';
    }

    private function aggregateStatus(Collection $group): string
    {
        if ($group->every(fn ($i) => $i->display_status === 'paid')) {
            return 'Paid';
        }
        if ($group->contains(fn ($i) => in_array($i->display_status, ['unpaid', 'overdue', 'partial'], true))) {
            return 'Outstanding';
        }

        return 'Mixed';
    }
}
