<?php

namespace Modules\Accounting\Services;

use Config\Database;

class AccountingReportService
{
    public function resolveFilters(array $get): array
    {
        $dateFrom = trim((string) ($get['date_from'] ?? ''));
        $dateTo   = trim((string) ($get['date_to'] ?? ''));

        if ($dateFrom === '') {
            $dateFrom = date('Y-01-01');
        }
        if ($dateTo === '') {
            $dateTo = date('Y-m-d');
        }

        if (strtotime($dateTo) < strtotime($dateFrom)) {
            $dateTo = $dateFrom;
        }

        $accountId = (int) ($get['account_id'] ?? 0);

        return [
            'date_from'  => $dateFrom,
            'date_to'    => $dateTo,
            'account_id' => $accountId > 0 ? $accountId : null,
            'type'       => trim((string) ($get['type'] ?? '')) ?: null,
        ];
    }

    public function trialBalance(array $filters): array
    {
        $db = Database::connect();

        $sql = "
            SELECT
                a.id,
                a.code,
                a.name,
                a.type,
                COALESCE(SUM(CASE
                    WHEN j.status = 'posted'
                     AND j.entry_date >= ?
                     AND j.entry_date <= ?
                    THEN l.debit ELSE 0 END), 0) AS total_debit,
                COALESCE(SUM(CASE
                    WHEN j.status = 'posted'
                     AND j.entry_date >= ?
                     AND j.entry_date <= ?
                    THEN l.credit ELSE 0 END), 0) AS total_credit
            FROM chart_of_accounts a
            LEFT JOIN journal_entry_lines l ON l.account_id = a.id
            LEFT JOIN journal_entries j ON j.id = l.journal_entry_id
            WHERE a.status = 'active'
        ";
        $params = [
            $filters['date_from'], $filters['date_to'],
            $filters['date_from'], $filters['date_to'],
        ];

        if (! empty($filters['type'])) {
            $sql .= ' AND a.type = ?';
            $params[] = $filters['type'];
        }

        $sql .= ' GROUP BY a.id, a.code, a.name, a.type ORDER BY a.code ASC';
        $rows = $db->query($sql, $params)->getResultArray();

        $out = [];
        $sumDebit = 0.0;
        $sumCredit = 0.0;

        foreach ($rows as $row) {
            $debit  = (float) $row['total_debit'];
            $credit = (float) $row['total_credit'];
            $net = round($debit - $credit, 2);

            if ($debit == 0.0 && $credit == 0.0) {
                continue;
            }

            $debitBal  = $net > 0 ? $net : 0.0;
            $creditBal = $net < 0 ? abs($net) : 0.0;

            $sumDebit += $debitBal;
            $sumCredit += $creditBal;

            $out[] = [
                'id'           => (int) $row['id'],
                'code'         => $row['code'],
                'name'         => $row['name'],
                'type'         => $row['type'],
                'debit'        => $debitBal,
                'credit'       => $creditBal,
                'total_debit'  => $debit,
                'total_credit' => $credit,
            ];
        }

        return [
            'rows'         => $out,
            'total_debit'  => round($sumDebit, 2),
            'total_credit' => round($sumCredit, 2),
            'balanced'     => round($sumDebit, 2) === round($sumCredit, 2),
            'kpis'         => [
                $this->kpi('Accounts', (string) count($out), 'With activity', 'fa-sitemap'),
                $this->kpi('Debit Total', number_format($sumDebit, 2), 'Net debit balances', 'fa-arrow-down'),
                $this->kpi('Credit Total', number_format($sumCredit, 2), 'Net credit balances', 'fa-arrow-up'),
            ],
        ];
    }

    public function generalLedger(array $filters): array
    {
        $db = Database::connect();

        $builder = $db->table('journal_entry_lines l')
            ->select('l.*, j.number, j.entry_date, j.reference, j.description as journal_description,
                      a.code as account_code, a.name as account_name, a.type as account_type')
            ->join('journal_entries j', 'j.id = l.journal_entry_id')
            ->join('chart_of_accounts a', 'a.id = l.account_id')
            ->where('j.status', 'posted')
            ->where('j.entry_date >=', $filters['date_from'])
            ->where('j.entry_date <=', $filters['date_to'])
            ->orderBy('a.code', 'ASC')
            ->orderBy('j.entry_date', 'ASC')
            ->orderBy('j.id', 'ASC')
            ->orderBy('l.sort_order', 'ASC');

        if (! empty($filters['account_id'])) {
            $builder->where('l.account_id', $filters['account_id']);
        }

        if (! empty($filters['type'])) {
            $builder->where('a.type', $filters['type']);
        }

        $rows = $builder->get()->getResultArray();

        $opening = [];
        if (! empty($filters['account_id'])) {
            $openingRow = $db->table('journal_entry_lines l')
                ->select('COALESCE(SUM(l.debit), 0) AS total_debit, COALESCE(SUM(l.credit), 0) AS total_credit', false)
                ->join('journal_entries j', 'j.id = l.journal_entry_id')
                ->where('j.status', 'posted')
                ->where('l.account_id', $filters['account_id'])
                ->where('j.entry_date <', $filters['date_from'])
                ->get()
                ->getRowArray();

            $openingBalance = (float) ($openingRow['total_debit'] ?? 0) - (float) ($openingRow['total_credit'] ?? 0);
            $opening = [
                'balance' => round($openingBalance, 2),
                'debit'   => $openingBalance > 0 ? round($openingBalance, 2) : 0.0,
                'credit'  => $openingBalance < 0 ? round(abs($openingBalance), 2) : 0.0,
            ];
        }

        $grouped = [];
        $runningByAccount = [];

        foreach ($rows as $row) {
            $accountId = (int) $row['account_id'];
            if (! isset($runningByAccount[$accountId])) {
                $runningByAccount[$accountId] = (! empty($filters['account_id']) && $filters['account_id'] === $accountId)
                    ? (float) ($opening['balance'] ?? 0)
                    : 0.0;
                if (empty($filters['account_id'])) {
                    $prior = $db->table('journal_entry_lines l')
                        ->select('COALESCE(SUM(l.debit), 0) AS total_debit, COALESCE(SUM(l.credit), 0) AS total_credit', false)
                        ->join('journal_entries j', 'j.id = l.journal_entry_id')
                        ->where('j.status', 'posted')
                        ->where('l.account_id', $accountId)
                        ->where('j.entry_date <', $filters['date_from'])
                        ->get()
                        ->getRowArray();
                    $runningByAccount[$accountId] = (float) ($prior['total_debit'] ?? 0) - (float) ($prior['total_credit'] ?? 0);
                }
            }

            $debit  = (float) $row['debit'];
            $credit = (float) $row['credit'];
            $runningByAccount[$accountId] = round($runningByAccount[$accountId] + $debit - $credit, 2);

            if (! isset($grouped[$accountId])) {
                $grouped[$accountId] = [
                    'account_id'   => $accountId,
                    'account_code' => $row['account_code'],
                    'account_name' => $row['account_name'],
                    'account_type' => $row['account_type'],
                    'lines'        => [],
                ];
            }

            $grouped[$accountId]['lines'][] = [
                'entry_date'  => $row['entry_date'],
                'number'      => $row['number'],
                'reference'   => $row['reference'],
                'description' => $row['description'] ?: $row['journal_description'],
                'debit'       => $debit,
                'credit'      => $credit,
                'balance'     => $runningByAccount[$accountId],
                'journal_id'  => (int) $row['journal_entry_id'],
            ];
        }

        $totalDebit = 0.0;
        $totalCredit = 0.0;
        foreach ($rows as $row) {
            $totalDebit += (float) $row['debit'];
            $totalCredit += (float) $row['credit'];
        }

        return [
            'accounts'     => array_values($grouped),
            'opening'      => $opening,
            'total_debit'  => round($totalDebit, 2),
            'total_credit' => round($totalCredit, 2),
            'kpis'         => [
                $this->kpi('Accounts', (string) count($grouped), 'In period', 'fa-book-open'),
                $this->kpi('Lines', (string) count($rows), 'Posted movements', 'fa-list'),
                $this->kpi('Debits', number_format($totalDebit, 2), 'Period total', 'fa-arrow-down'),
                $this->kpi('Credits', number_format($totalCredit, 2), 'Period total', 'fa-arrow-up'),
            ],
        ];
    }

    /**
     * Revenue and expense activity for the selected period.
     */
    public function profitAndLoss(array $filters): array
    {
        $rows = $this->accountPeriodTotals($filters['date_from'], $filters['date_to'], ['revenue', 'expense']);

        $revenues = [];
        $expenses = [];
        $totalRevenue = 0.0;
        $totalExpense = 0.0;

        foreach ($rows as $row) {
            $debit  = (float) $row['total_debit'];
            $credit = (float) $row['total_credit'];

            if ($debit == 0.0 && $credit == 0.0) {
                continue;
            }

            if ($row['type'] === 'revenue') {
                $amount = round($credit - $debit, 2);
                $totalRevenue += $amount;
                $revenues[] = $this->plRow($row, $amount);
            } else {
                $amount = round($debit - $credit, 2);
                $totalExpense += $amount;
                $expenses[] = $this->plRow($row, $amount);
            }
        }

        $netIncome = round($totalRevenue - $totalExpense, 2);

        return [
            'revenues'      => $revenues,
            'expenses'      => $expenses,
            'total_revenue' => round($totalRevenue, 2),
            'total_expense' => round($totalExpense, 2),
            'net_income'    => $netIncome,
            'kpis'          => [
                $this->kpi('Revenue', number_format($totalRevenue, 2), 'Period total', 'fa-arrow-up', $totalRevenue >= 0 ? 'positive' : 'negative'),
                $this->kpi('Expenses', number_format($totalExpense, 2), 'Period total', 'fa-arrow-down', 'negative'),
                $this->kpi('Net Income', number_format($netIncome, 2), $netIncome >= 0 ? 'Profit' : 'Loss', 'fa-scale-balanced', $netIncome >= 0 ? 'positive' : 'negative'),
            ],
        ];
    }

    /**
     * Point-in-time balances as of date_to, with period net income from date_from..date_to.
     */
    public function balanceSheet(array $filters): array
    {
        $asOf = $filters['date_to'];
        $rows = $this->accountBalancesAsOf($asOf, ['asset', 'liability', 'equity']);

        $assets = [];
        $liabilities = [];
        $equity = [];
        $totalAssets = 0.0;
        $totalLiabilities = 0.0;
        $totalEquity = 0.0;

        foreach ($rows as $row) {
            $debit  = (float) $row['total_debit'];
            $credit = (float) $row['total_credit'];
            $raw    = round($debit - $credit, 2);

            if ($raw == 0.0) {
                continue;
            }

            if ($row['type'] === 'asset') {
                $amount = $raw;
                $totalAssets += $amount;
                $assets[] = $this->bsRow($row, $amount);
            } elseif ($row['type'] === 'liability') {
                $amount = round(-$raw, 2); // credit-normal
                $totalLiabilities += $amount;
                $liabilities[] = $this->bsRow($row, $amount);
            } else {
                $amount = round(-$raw, 2); // credit-normal
                $totalEquity += $amount;
                $equity[] = $this->bsRow($row, $amount);
            }
        }

        $pl = $this->profitAndLoss($filters);
        $netIncome = (float) $pl['net_income'];
        $totalEquityWithEarnings = round($totalEquity + $netIncome, 2);
        $totalLiabilitiesEquity = round($totalLiabilities + $totalEquityWithEarnings, 2);

        return [
            'as_of'                    => $asOf,
            'assets'                   => $assets,
            'liabilities'              => $liabilities,
            'equity'                   => $equity,
            'total_assets'             => round($totalAssets, 2),
            'total_liabilities'        => round($totalLiabilities, 2),
            'total_equity'             => round($totalEquity, 2),
            'net_income'               => $netIncome,
            'total_equity_with_earnings' => $totalEquityWithEarnings,
            'total_liabilities_equity' => $totalLiabilitiesEquity,
            'balanced'                 => round($totalAssets, 2) === $totalLiabilitiesEquity,
            'kpis'                     => [
                $this->kpi('Assets', number_format($totalAssets, 2), 'As of ' . $asOf, 'fa-building'),
                $this->kpi('Liabilities', number_format($totalLiabilities, 2), 'As of ' . $asOf, 'fa-file-invoice-dollar'),
                $this->kpi('Equity + NI', number_format($totalEquityWithEarnings, 2), 'Incl. period earnings', 'fa-landmark'),
                $this->kpi('Net Income', number_format($netIncome, 2), $filters['date_from'] . ' → ' . $filters['date_to'], 'fa-chart-line', $netIncome >= 0 ? 'positive' : 'negative'),
            ],
        ];
    }

    /**
     * Cash flow from movements on bank/cash-linked CoA accounts, classified by counterpart type.
     */
    public function cashFlow(array $filters): array
    {
        $db = Database::connect();
        $cashAccountIds = $this->cashLinkedAccountIds();

        if ($cashAccountIds === []) {
            return [
                'opening_cash' => 0.0,
                'closing_cash' => 0.0,
                'net_change'   => 0.0,
                'operating'    => ['inflows' => 0.0, 'outflows' => 0.0, 'net' => 0.0, 'rows' => []],
                'investing'    => ['inflows' => 0.0, 'outflows' => 0.0, 'net' => 0.0, 'rows' => []],
                'financing'    => ['inflows' => 0.0, 'outflows' => 0.0, 'net' => 0.0, 'rows' => []],
                'transfers'    => ['net' => 0.0, 'rows' => []],
                'unclassified' => ['net' => 0.0, 'rows' => []],
                'kpis'         => [
                    $this->kpi('Cash Accounts', '0', 'Link bank/cash to CoA', 'fa-money-bill', 'negative'),
                    $this->kpi('Net Change', '0.00', 'No cash accounts', 'fa-water', 'negative'),
                ],
                'warning'      => 'No bank or cash accounts with linked ledger accounts were found. Configure Banking first.',
            ];
        }

        $openingCash = $this->cashBalanceBefore($cashAccountIds, $filters['date_from']);
        $closingCash = $this->cashBalanceAsOf($cashAccountIds, $filters['date_to']);
        $netChange   = round($closingCash - $openingCash, 2);

        $sections = [
            'operating'    => ['inflows' => 0.0, 'outflows' => 0.0, 'net' => 0.0, 'rows' => []],
            'investing'    => ['inflows' => 0.0, 'outflows' => 0.0, 'net' => 0.0, 'rows' => []],
            'financing'    => ['inflows' => 0.0, 'outflows' => 0.0, 'net' => 0.0, 'rows' => []],
            'transfers'    => ['net' => 0.0, 'rows' => []],
            'unclassified' => ['net' => 0.0, 'rows' => []],
        ];

        $placeholders = implode(',', array_fill(0, count($cashAccountIds), '?'));
        $sql = "
            SELECT
                j.id AS journal_id,
                j.number,
                j.entry_date,
                j.reference,
                j.description,
                SUM(CASE WHEN l.account_id IN ({$placeholders}) THEN l.debit - l.credit ELSE 0 END) AS cash_net,
                GROUP_CONCAT(DISTINCT CASE WHEN l.account_id NOT IN ({$placeholders}) THEN a.type END) AS counterpart_types,
                GROUP_CONCAT(DISTINCT CASE WHEN l.account_id NOT IN ({$placeholders}) THEN CONCAT(a.code, ' · ', a.name) END SEPARATOR '; ') AS counterpart_labels
            FROM journal_entries j
            INNER JOIN journal_entry_lines l ON l.journal_entry_id = j.id
            INNER JOIN chart_of_accounts a ON a.id = l.account_id
            WHERE j.status = 'posted'
              AND j.entry_date >= ?
              AND j.entry_date <= ?
            GROUP BY j.id, j.number, j.entry_date, j.reference, j.description
            HAVING cash_net <> 0
            ORDER BY j.entry_date ASC, j.id ASC
        ";

        $params = array_merge($cashAccountIds, $cashAccountIds, [$filters['date_from'], $filters['date_to']]);
        $journals = $db->query($sql, $params)->getResultArray();

        foreach ($journals as $row) {
            $cashNet = round((float) $row['cash_net'], 2);
            $types   = array_values(array_filter(array_unique(array_map('trim', explode(',', (string) ($row['counterpart_types'] ?? ''))))));
            $section = $this->classifyCashFlowSection($types);

            $entry = [
                'journal_id'  => (int) $row['journal_id'],
                'number'      => $row['number'],
                'entry_date'  => $row['entry_date'],
                'reference'   => $row['reference'],
                'description' => $row['description'],
                'counterpart' => $row['counterpart_labels'] ?: '—',
                'amount'      => $cashNet,
            ];

            if (in_array($section, ['operating', 'investing', 'financing'], true)) {
                if ($cashNet > 0) {
                    $sections[$section]['inflows'] = round($sections[$section]['inflows'] + $cashNet, 2);
                } else {
                    $sections[$section]['outflows'] = round($sections[$section]['outflows'] + abs($cashNet), 2);
                }
                $sections[$section]['net'] = round($sections[$section]['net'] + $cashNet, 2);
                $sections[$section]['rows'][] = $entry;
            } else {
                $sections[$section]['net'] = round($sections[$section]['net'] + $cashNet, 2);
                $sections[$section]['rows'][] = $entry;
            }
        }

        return [
            'opening_cash' => $openingCash,
            'closing_cash' => $closingCash,
            'net_change'   => $netChange,
            'operating'    => $sections['operating'],
            'investing'    => $sections['investing'],
            'financing'    => $sections['financing'],
            'transfers'    => $sections['transfers'],
            'unclassified' => $sections['unclassified'],
            'kpis'         => [
                $this->kpi('Opening Cash', number_format($openingCash, 2), 'Before ' . $filters['date_from'], 'fa-vault'),
                $this->kpi('Closing Cash', number_format($closingCash, 2), 'As of ' . $filters['date_to'], 'fa-sack-dollar'),
                $this->kpi('Net Change', number_format($netChange, 2), 'Period movement', 'fa-water', $netChange >= 0 ? 'positive' : 'negative'),
                $this->kpi('Operating', number_format($sections['operating']['net'], 2), 'Classified CF', 'fa-briefcase', $sections['operating']['net'] >= 0 ? 'positive' : 'negative'),
            ],
            'warning'      => null,
        ];
    }

    protected function accountPeriodTotals(string $from, string $to, array $types): array
    {
        $db = Database::connect();
        $placeholders = implode(',', array_fill(0, count($types), '?'));

        $sql = "
            SELECT
                a.id,
                a.code,
                a.name,
                a.type,
                COALESCE(SUM(CASE
                    WHEN j.status = 'posted'
                     AND j.entry_date >= ?
                     AND j.entry_date <= ?
                    THEN l.debit ELSE 0 END), 0) AS total_debit,
                COALESCE(SUM(CASE
                    WHEN j.status = 'posted'
                     AND j.entry_date >= ?
                     AND j.entry_date <= ?
                    THEN l.credit ELSE 0 END), 0) AS total_credit
            FROM chart_of_accounts a
            LEFT JOIN journal_entry_lines l ON l.account_id = a.id
            LEFT JOIN journal_entries j ON j.id = l.journal_entry_id
            WHERE a.status = 'active'
              AND a.type IN ({$placeholders})
            GROUP BY a.id, a.code, a.name, a.type
            ORDER BY a.code ASC
        ";

        $params = array_merge([$from, $to, $from, $to], $types);

        return $db->query($sql, $params)->getResultArray();
    }

    protected function accountBalancesAsOf(string $asOf, array $types): array
    {
        $db = Database::connect();
        $placeholders = implode(',', array_fill(0, count($types), '?'));

        $sql = "
            SELECT
                a.id,
                a.code,
                a.name,
                a.type,
                COALESCE(SUM(CASE
                    WHEN j.status = 'posted'
                     AND j.entry_date <= ?
                    THEN l.debit ELSE 0 END), 0) AS total_debit,
                COALESCE(SUM(CASE
                    WHEN j.status = 'posted'
                     AND j.entry_date <= ?
                    THEN l.credit ELSE 0 END), 0) AS total_credit
            FROM chart_of_accounts a
            LEFT JOIN journal_entry_lines l ON l.account_id = a.id
            LEFT JOIN journal_entries j ON j.id = l.journal_entry_id
            WHERE a.status = 'active'
              AND a.type IN ({$placeholders})
            GROUP BY a.id, a.code, a.name, a.type
            ORDER BY a.code ASC
        ";

        $params = array_merge([$asOf, $asOf], $types);

        return $db->query($sql, $params)->getResultArray();
    }

    /**
     * @return list<int>
     */
    protected function cashLinkedAccountIds(): array
    {
        $db = Database::connect();
        $ids = [];

        if ($db->tableExists('bank_accounts')) {
            $rows = $db->table('bank_accounts')->select('account_id')->where('account_id >', 0)->get()->getResultArray();
            foreach ($rows as $row) {
                $ids[] = (int) $row['account_id'];
            }
        }

        if ($db->tableExists('cash_accounts')) {
            $rows = $db->table('cash_accounts')->select('account_id')->where('account_id >', 0)->get()->getResultArray();
            foreach ($rows as $row) {
                $ids[] = (int) $row['account_id'];
            }
        }

        $ids = array_values(array_unique(array_filter($ids)));
        sort($ids);

        return $ids;
    }

    /**
     * @param list<int> $cashAccountIds
     */
    protected function cashBalanceBefore(array $cashAccountIds, string $dateFrom): float
    {
        return $this->sumCashNet($cashAccountIds, null, $dateFrom, true);
    }

    /**
     * @param list<int> $cashAccountIds
     */
    protected function cashBalanceAsOf(array $cashAccountIds, string $asOf): float
    {
        return $this->sumCashNet($cashAccountIds, null, $asOf, false);
    }

    /**
     * @param list<int> $cashAccountIds
     */
    protected function sumCashNet(array $cashAccountIds, ?string $from, string $toOrBefore, bool $beforeExclusive): float
    {
        if ($cashAccountIds === []) {
            return 0.0;
        }

        $db = Database::connect();
        $placeholders = implode(',', array_fill(0, count($cashAccountIds), '?'));
        $params = $cashAccountIds;

        $dateClause = $beforeExclusive ? 'j.entry_date < ?' : 'j.entry_date <= ?';
        $params[] = $toOrBefore;

        $fromClause = '';
        if ($from !== null) {
            $fromClause = ' AND j.entry_date >= ?';
            $params[] = $from;
        }

        $sql = "
            SELECT COALESCE(SUM(l.debit - l.credit), 0) AS net
            FROM journal_entry_lines l
            INNER JOIN journal_entries j ON j.id = l.journal_entry_id
            WHERE j.status = 'posted'
              AND l.account_id IN ({$placeholders})
              AND {$dateClause}
              {$fromClause}
        ";

        $row = $db->query($sql, $params)->getRowArray();

        return round((float) ($row['net'] ?? 0), 2);
    }

    /**
     * @param list<string> $types
     */
    protected function classifyCashFlowSection(array $types): string
    {
        if ($types === []) {
            return 'transfers';
        }

        $hasRevenueExpense = (bool) array_intersect($types, ['revenue', 'expense']);
        $hasAsset          = in_array('asset', $types, true);
        $hasFinancing      = (bool) array_intersect($types, ['liability', 'equity']);

        // Prefer operating when P&L accounts are involved.
        if ($hasRevenueExpense) {
            return 'operating';
        }
        if ($hasAsset && ! $hasFinancing) {
            return 'investing';
        }
        if ($hasFinancing) {
            return 'financing';
        }

        return 'unclassified';
    }

    protected function plRow(array $row, float $amount): array
    {
        return [
            'id'     => (int) $row['id'],
            'code'   => $row['code'],
            'name'   => $row['name'],
            'type'   => $row['type'],
            'amount' => $amount,
        ];
    }

    protected function bsRow(array $row, float $amount): array
    {
        return [
            'id'     => (int) $row['id'],
            'code'   => $row['code'],
            'name'   => $row['name'],
            'type'   => $row['type'],
            'amount' => $amount,
        ];
    }

    protected function kpi(string $label, string $value, string $badge, string $icon, string $badgeType = 'positive'): array
    {
        if (! in_array($badgeType, ['positive', 'negative'], true)) {
            $badgeType = 'positive';
        }

        return [
            'label'     => $label,
            'value'     => $value,
            'badge'     => $badge,
            'badgeType' => $badgeType,
            'icon'      => $icon,
            'dept'      => 'Accounting',
        ];
    }
}
