<?php

declare(strict_types=1);

namespace App\Modules\Reporting\Application\Services;

use App\Shared\Schema\FinanceSchemaSupport;
use PDO;

final class ProfitReportService
{
    public function __construct(private PDO $pdo)
    {
    }

    public function ensureSchema(): void
    {
        FinanceSchemaSupport::ensureSalesInvoicePaymentSchema($this->pdo);
        FinanceSchemaSupport::ensureCashPaymentsSchema($this->pdo);
        FinanceSchemaSupport::ensureExpensesSchema($this->pdo);
    }

    /**
     * @return array{
     *   from:string,
     *   to:string,
     *   q:string,
     *   summary:array<string,float>,
     *   salesRows:array<int,array<string,mixed>>,
     *   returnRows:array<int,array<string,mixed>>,
     *   expenseRows:array<int,array<string,mixed>>,
     *   expenseByCategory:array<string,float>
     * }
     */
    public function report(string $from, string $to, string $q): array
    {
        $from = trim($from) !== '' ? trim($from) : date('Y-m-01');
        $to = trim($to) !== '' ? trim($to) : date('Y-m-d');
        $q = trim($q);

        $salesFilter = $this->buildSalesFilter($from, $to, $q);
        $returnsFilter = $this->buildReturnsFilter($from, $to, $q);
        $expensesFilter = $this->buildExpensesFilter($from, $to, $q);

        $salesRows = $this->fetchSalesRows($salesFilter['where'], $salesFilter['params']);
        $salesCost = $this->fetchSalesCost($salesFilter['where'], $salesFilter['params']);
        $returnRows = $this->fetchReturnRows($returnsFilter['where'], $returnsFilter['params']);
        $returnCost = $this->fetchReturnCost($returnsFilter['where'], $returnsFilter['params']);
        $expenseRows = $this->fetchExpenseRows($expensesFilter['where'], $expensesFilter['params']);

        $grossSales = array_sum(array_map(fn(array $row): float => (float) ($row['invoice_total'] ?? 0), $salesRows));
        $salesReturns = array_sum(array_map(fn(array $row): float => (float) ($row['return_total'] ?? 0), $returnRows));
        $totalExpenses = array_sum(array_map(fn(array $row): float => (float) ($row['amount'] ?? 0), $expenseRows));

        $netSales = $grossSales - $salesReturns;
        $netCogs = $salesCost - $returnCost;
        $grossProfit = $netSales - $netCogs;
        $netProfit = $grossProfit - $totalExpenses;

        $expenseByCategory = [];
        foreach ($expenseRows as $row) {
            $category = (string) ($row['expense_category_name'] ?? 'غير مصنف');
            $expenseByCategory[$category] = ($expenseByCategory[$category] ?? 0.0) + (float) ($row['amount'] ?? 0.0);
        }
        arsort($expenseByCategory);

        return [
            'from' => $from,
            'to' => $to,
            'q' => $q,
            'summary' => [
                'gross_sales' => $grossSales,
                'sales_returns' => $salesReturns,
                'net_sales' => $netSales,
                'sales_cost' => $salesCost,
                'return_cost' => $returnCost,
                'net_cogs' => $netCogs,
                'gross_profit' => $grossProfit,
                'expenses' => $totalExpenses,
                'net_profit' => $netProfit,
            ],
            'salesRows' => $salesRows,
            'returnRows' => $returnRows,
            'expenseRows' => $expenseRows,
            'expenseByCategory' => $expenseByCategory,
        ];
    }

    /** @return array{where:array<int,string>,params:array<int,mixed>} */
    private function buildSalesFilter(string $from, string $to, string $q): array
    {
        $where = ["UPPER(COALESCE(s.status,'POSTED'))='POSTED'", 's.invoice_date >= ?', 's.invoice_date < ?'];
        $params = [$from . ' 00:00:00', date('Y-m-d 00:00:00', strtotime($to . ' +1 day'))];
        if ($q !== '') {
            $like = "%{$q}%";
            $where[] = '(s.invoice_no LIKE ? OR COALESCE(s.notes,"") LIKE ? OR COALESCE(pt.name,"") LIKE ?)';
            array_push($params, $like, $like, $like);
        }
        return ['where' => $where, 'params' => $params];
    }

    /** @return array{where:array<int,string>,params:array<int,mixed>} */
    private function buildReturnsFilter(string $from, string $to, string $q): array
    {
        $where = ["UPPER(COALESCE(sr.status,'POSTED'))='POSTED'", 'sr.return_date >= ?', 'sr.return_date < ?'];
        $params = [$from . ' 00:00:00', date('Y-m-d 00:00:00', strtotime($to . ' +1 day'))];
        if ($q !== '') {
            $like = "%{$q}%";
            $where[] = '(sr.return_no LIKE ? OR COALESCE(sr.notes,"") LIKE ? OR COALESCE(sr.reason,"") LIKE ? OR COALESCE(pt.name,"") LIKE ?)';
            array_push($params, $like, $like, $like, $like);
        }
        return ['where' => $where, 'params' => $params];
    }

    /** @return array{where:array<int,string>,params:array<int,mixed>} */
    private function buildExpensesFilter(string $from, string $to, string $q): array
    {
        $where = ["p.payment_type='expense'", "UPPER(COALESCE(p.status,'POSTED'))='POSTED'", 'p.voucher_date >= ?', 'p.voucher_date < ?'];
        $params = [$from . ' 00:00:00', date('Y-m-d 00:00:00', strtotime($to . ' +1 day'))];
        if ($q !== '') {
            $like = "%{$q}%";
            $where[] = '(p.voucher_no LIKE ? OR COALESCE(p.notes,"") LIKE ? OR COALESCE(p.payee_name,"") LIKE ?)';
            array_push($params, $like, $like, $like);
        }
        return ['where' => $where, 'params' => $params];
    }

    /** @param array<int,string> $where @param array<int,mixed> $params
     *  @return array<int,array<string,mixed>> */
    private function fetchSalesRows(array $where, array $params): array
    {
        $sql = "SELECT s.id, s.invoice_no, s.invoice_date, COALESCE(NULLIF(s.grand_total,0), s.total, 0) AS invoice_total, COALESCE(pt.name,'-') AS party_name
                FROM sales_invoices s
                LEFT JOIN parties pt ON pt.id=s.party_id
                WHERE " . implode(' AND ', $where) . "
                ORDER BY s.invoice_date ASC, s.id ASC";
        $st = $this->pdo->prepare($sql);
        $st->execute($params);
        return $st->fetchAll() ?: [];
    }

    /** @param array<int,string> $where @param array<int,mixed> $params */
    private function fetchSalesCost(array $where, array $params): float
    {
        $sql = "SELECT COALESCE(SUM(si.qty * COALESCE(si.cost,0)),0)
                FROM sales_invoices s
                INNER JOIN sales_items si ON si.invoice_id=s.id
                LEFT JOIN parties pt ON pt.id=s.party_id
                WHERE " . implode(' AND ', $where);
        $st = $this->pdo->prepare($sql);
        $st->execute($params);
        return (float) ($st->fetchColumn() ?: 0);
    }

    /** @param array<int,string> $where @param array<int,mixed> $params
     *  @return array<int,array<string,mixed>> */
    private function fetchReturnRows(array $where, array $params): array
    {
        $sql = "SELECT sr.id, sr.return_no, sr.return_date, COALESCE(sr.total_amount,0) AS return_total, COALESCE(pt.name,'-') AS party_name
                FROM sales_returns sr
                LEFT JOIN parties pt ON pt.id=sr.party_id
                WHERE " . implode(' AND ', $where) . "
                ORDER BY sr.return_date ASC, sr.id ASC";
        $st = $this->pdo->prepare($sql);
        $st->execute($params);
        return $st->fetchAll() ?: [];
    }

    /** @param array<int,string> $where @param array<int,mixed> $params */
    private function fetchReturnCost(array $where, array $params): float
    {
        $sql = "SELECT COALESCE(SUM(sri.qty * COALESCE(ss.cost,0)),0)
                FROM sales_returns sr
                INNER JOIN sales_return_items sri ON sri.sales_return_id=sr.id
                INNER JOIN sales_items ss ON ss.id=sri.sales_invoice_item_id
                LEFT JOIN parties pt ON pt.id=sr.party_id
                WHERE " . implode(' AND ', $where);
        $st = $this->pdo->prepare($sql);
        $st->execute($params);
        return (float) ($st->fetchColumn() ?: 0);
    }

    /** @param array<int,string> $where @param array<int,mixed> $params
     *  @return array<int,array<string,mixed>> */
    private function fetchExpenseRows(array $where, array $params): array
    {
        $sql = "SELECT p.id, p.voucher_no, p.voucher_date, p.amount, COALESCE(ei.name,'-') AS expense_item_name, COALESCE(ec.name,'-') AS expense_category_name
                FROM cash_payments p
                LEFT JOIN expense_items ei ON ei.id=p.expense_item_id
                LEFT JOIN expense_categories ec ON ec.id=ei.category_id
                WHERE " . implode(' AND ', $where) . "
                ORDER BY p.voucher_date ASC, p.id ASC";
        $st = $this->pdo->prepare($sql);
        $st->execute($params);
        return $st->fetchAll() ?: [];
    }
}
