<?php
declare(strict_types=1);

namespace App\Modules\Accounting\Services;

use App\Core\App;

/**
 * General Ledger and Trial Balance queries.
 * Works from posted journal entries — never touches draft entries.
 */
final class LedgerService
{
    /**
     * Get general ledger for a specific account within a date range.
     *
     * @return array ['account' => [...], 'opening_balance' => string, 'entries' => [...], 'closing_balance' => string]
     */
    public static function getAccountLedger(
        int     $accountId,
        string  $dateFrom,
        string  $dateTo,
        ?int    $costCenterId = null,
        ?int    $branchId = null
    ): array {
        $db = App::getInstance()->db();

        $account = $db->selectOne("SELECT * FROM chart_of_accounts WHERE id = ?", [$accountId]);
        if (!$account) {
            return ['account' => null, 'opening_balance' => '0.00', 'entries' => [], 'closing_balance' => '0.00'];
        }

        // Opening balance = all posted entries before dateFrom
        $openingWhere = "je.status = 'posted' AND je.entry_date < ? AND je.is_archived = 0";
        $openingParams = [$dateFrom];
        $extraJoin = '';

        if ($costCenterId !== null) {
            $openingWhere .= ' AND jel.cost_center_id = ?';
            $openingParams[] = $costCenterId;
        }
        if ($branchId !== null) {
            $openingWhere .= ' AND jel.branch_id = ?';
            $openingParams[] = $branchId;
        }

        $openingRow = $db->selectOne(
            "SELECT COALESCE(SUM(jel.debit), 0) as total_debit,
                    COALESCE(SUM(jel.credit), 0) as total_credit
             FROM journal_entry_lines jel
             JOIN journal_entries je ON je.id = jel.journal_entry_id
             WHERE jel.account_id = ? AND {$openingWhere}",
            array_merge([$accountId], $openingParams)
        );

        $openingDebit = $openingRow['total_debit'] ?? '0.00';
        $openingCredit = $openingRow['total_credit'] ?? '0.00';

        // Opening balance adjusted for account nature
        if ($account['account_nature'] === 'debit') {
            $openingBalance = bcsub($openingDebit, $openingCredit, 2);
            $openingBalance = bcadd($openingBalance, (string) $account['opening_balance'], 2);
        } else {
            $openingBalance = bcsub($openingCredit, $openingDebit, 2);
            $openingBalance = bcadd($openingBalance, (string) $account['opening_balance'], 2);
        }

        // Ledger entries within the period
        $periodWhere = "je.status = 'posted' AND je.entry_date >= ? AND je.entry_date <= ? AND je.is_archived = 0";
        $periodParams = [$dateFrom, $dateTo];

        if ($costCenterId !== null) {
            $periodWhere .= ' AND jel.cost_center_id = ?';
            $periodParams[] = $costCenterId;
        }
        if ($branchId !== null) {
            $periodWhere .= ' AND jel.branch_id = ?';
            $periodParams[] = $branchId;
        }

        $entries = $db->select(
            "SELECT jel.*, je.entry_number, je.entry_date, je.description_ar as entry_description,
                    je.reference_type, je.reference_number, je.source_module
             FROM journal_entry_lines jel
             JOIN journal_entries je ON je.id = jel.journal_entry_id
             WHERE jel.account_id = ? AND {$periodWhere}
             ORDER BY je.entry_date ASC, je.id ASC, jel.line_number ASC",
            array_merge([$accountId], $periodParams)
        );

        // Calculate running balance
        $running = $openingBalance;
        foreach ($entries as &$entry) {
            if ($account['account_nature'] === 'debit') {
                $running = bcadd($running, bcsub((string) $entry['debit'], (string) $entry['credit'], 2), 2);
            } else {
                $running = bcadd($running, bcsub((string) $entry['credit'], (string) $entry['debit'], 2), 2);
            }
            $entry['running_balance'] = $running;
        }
        unset($entry);

        return [
            'account'         => $account,
            'opening_balance' => $openingBalance,
            'entries'         => $entries,
            'closing_balance' => $running,
        ];
    }

    /**
     * Generate trial balance for a given date range or period.
     *
     * @return array ['accounts' => [...], 'total_debit' => string, 'total_credit' => string, 'is_balanced' => bool]
     */
    public static function getTrialBalance(
        string  $dateFrom,
        string  $dateTo,
        ?int    $fiscalYearId = null,
        ?int    $costCenterId = null,
        ?int    $branchId = null,
        bool    $includeZeroBalances = false
    ): array {
        $db = App::getInstance()->db();

        $extraWhere = '';
        $params = [$dateFrom, $dateTo];

        if ($costCenterId !== null) {
            $extraWhere .= ' AND jel.cost_center_id = ?';
            $params[] = $costCenterId;
        }
        if ($branchId !== null) {
            $extraWhere .= ' AND jel.branch_id = ?';
            $params[] = $branchId;
        }

        // Get all accounts with their period movement
        $accounts = $db->select(
            "SELECT coa.id, coa.account_code, coa.name_ar, coa.name_en,
                    coa.account_type, coa.account_nature, coa.level, coa.is_header,
                    coa.opening_balance,
                    COALESCE(SUM(jel.debit), 0) as total_debit,
                    COALESCE(SUM(jel.credit), 0) as total_credit
             FROM chart_of_accounts coa
             LEFT JOIN journal_entry_lines jel ON jel.account_id = coa.id
                 AND jel.journal_entry_id IN (
                     SELECT id FROM journal_entries
                     WHERE status = 'posted' AND entry_date >= ? AND entry_date <= ? AND is_archived = 0
                 )
                 {$extraWhere}
             WHERE coa.is_archived = 0 AND coa.is_header = 0
             GROUP BY coa.id, coa.account_code, coa.name_ar, coa.name_en,
                      coa.account_type, coa.account_nature, coa.level, coa.is_header,
                      coa.opening_balance
             ORDER BY coa.account_code ASC",
            $params
        );

        $totalDebit = '0.00';
        $totalCredit = '0.00';
        $filtered = [];

        foreach ($accounts as &$acc) {
            $debitMovement = (string) $acc['total_debit'];
            $creditMovement = (string) $acc['total_credit'];
            $opening = (string) $acc['opening_balance'];

            // Calculate net balance
            if ($acc['account_nature'] === 'debit') {
                $balance = bcadd($opening, bcsub($debitMovement, $creditMovement, 2), 2);
            } else {
                $balance = bcadd($opening, bcsub($creditMovement, $debitMovement, 2), 2);
            }

            // Determine trial balance columns
            if (bccomp($balance, '0.00', 2) >= 0) {
                if ($acc['account_nature'] === 'debit') {
                    $acc['tb_debit'] = $balance;
                    $acc['tb_credit'] = '0.00';
                } else {
                    $acc['tb_debit'] = '0.00';
                    $acc['tb_credit'] = $balance;
                }
            } else {
                // Negative balance — show on opposite side
                $absBalance = bcmul($balance, '-1', 2);
                if ($acc['account_nature'] === 'debit') {
                    $acc['tb_debit'] = '0.00';
                    $acc['tb_credit'] = $absBalance;
                } else {
                    $acc['tb_debit'] = $absBalance;
                    $acc['tb_credit'] = '0.00';
                }
            }

            $acc['balance'] = $balance;

            if (!$includeZeroBalances && bccomp($balance, '0.00', 2) === 0
                && bccomp($debitMovement, '0.00', 2) === 0
                && bccomp($creditMovement, '0.00', 2) === 0) {
                continue;
            }

            $totalDebit = bcadd($totalDebit, $acc['tb_debit'], 2);
            $totalCredit = bcadd($totalCredit, $acc['tb_credit'], 2);
            $filtered[] = $acc;
        }
        unset($acc);

        return [
            'accounts'     => $filtered,
            'total_debit'  => $totalDebit,
            'total_credit' => $totalCredit,
            'is_balanced'  => bccomp($totalDebit, $totalCredit, 2) === 0,
        ];
    }

    /**
     * Get account statement (similar to bank statement style) for a member.
     */
    public static function getMemberStatement(int $memberId, string $dateFrom, string $dateTo): array
    {
        $db = App::getInstance()->db();

        return $db->select(
            "SELECT jel.*, je.entry_number, je.entry_date, je.description_ar as entry_description,
                    je.reference_type, je.reference_number,
                    coa.account_code, coa.name_ar as account_name
             FROM journal_entry_lines jel
             JOIN journal_entries je ON je.id = jel.journal_entry_id
             JOIN chart_of_accounts coa ON coa.id = jel.account_id
             WHERE jel.member_id = ?
               AND je.status = 'posted'
               AND je.entry_date >= ? AND je.entry_date <= ?
               AND je.is_archived = 0
             ORDER BY je.entry_date ASC, je.id ASC",
            [$memberId, $dateFrom, $dateTo]
        );
    }

    /**
     * Get all journal entry lines for a specific account in a period.
     */
    public static function getAccountMovement(int $accountId, string $period): array
    {
        $db = App::getInstance()->db();
        $dateFrom = $period . '-01';
        $dateTo = date('Y-m-t', strtotime($dateFrom));

        return $db->select(
            "SELECT jel.*, je.entry_number, je.entry_date, je.description_ar as entry_description,
                    je.reference_type, je.reference_number, je.source_module
             FROM journal_entry_lines jel
             JOIN journal_entries je ON je.id = jel.journal_entry_id
             WHERE jel.account_id = ?
               AND je.status = 'posted'
               AND je.entry_date >= ? AND je.entry_date <= ?
               AND je.is_archived = 0
             ORDER BY je.entry_date ASC, je.id ASC",
            [$accountId, $dateFrom, $dateTo]
        );
    }
}
