<?php

namespace Modules\Accounting\Services;

use App\Models\ActivityLogModel;
use App\Models\BankAccountModel;
use App\Models\BankReconciliationLineModel;
use App\Models\BankReconciliationModel;
use App\Models\JournalEntryLineModel;

class BankReconciliationService
{
    protected BankReconciliationModel $recons;
    protected BankReconciliationLineModel $lines;
    protected BankAccountModel $banks;
    protected JournalEntryLineModel $jeLines;
    protected ActivityLogModel $activity;

    public function __construct()
    {
        $this->recons   = new BankReconciliationModel();
        $this->lines    = new BankReconciliationLineModel();
        $this->banks    = new BankAccountModel();
        $this->jeLines  = new JournalEntryLineModel();
        $this->activity = new ActivityLogModel();
    }

    public function create(array $input, ?int $actingUserId): array
    {
        $bankAccountId = (int) ($input['bank_account_id'] ?? 0);
        $bank = $this->banks->find($bankAccountId);
        if (! $bank) {
            return ['success' => false, 'errors' => ['bank_account_id' => 'Bank account not found.']];
        }
        if (($bank['status'] ?? '') !== 'active') {
            return ['success' => false, 'errors' => ['bank_account_id' => 'Bank account must be active.']];
        }

        $statementDate = $input['statement_date'] ?? date('Y-m-d');
        $bookBalance   = $this->computeBookBalance((int) $bank['account_id'], (float) $bank['opening_balance'], $statementDate);

        $data = [
            'number'                   => $this->recons->nextNumber(),
            'bank_account_id'          => $bankAccountId,
            'statement_date'           => $statementDate,
            'statement_ending_balance' => round((float) ($input['statement_ending_balance'] ?? 0), 2),
            'book_balance'             => $bookBalance,
            'status'                   => 'draft',
            'notes'                    => ! empty($input['notes']) ? trim((string) $input['notes']) : null,
            'created_by'               => $actingUserId,
            'updated_by'               => $actingUserId,
        ];

        if (! $this->recons->insert($data, true)) {
            return ['success' => false, 'errors' => $this->recons->errors()];
        }

        $id = (int) $this->recons->getInsertID();
        $uncleared = $this->loadUnclearedLines((int) $bank['account_id'], $statementDate);
        if ($uncleared !== []) {
            $this->lines->replaceForReconciliation($id, $uncleared);
        }

        $this->activity->log('bank_reconciliations', 'created', $actingUserId, "Created bank reconciliation #{$id} ({$data['number']})", (string) $id);

        return ['success' => true, 'id' => $id];
    }

    public function update(int $id, array $input, ?int $actingUserId): array
    {
        $recon = $this->recons->find($id);
        if (! $recon) {
            return ['success' => false, 'errors' => ['id' => 'Reconciliation not found.']];
        }
        if ($recon['status'] !== 'draft') {
            return ['success' => false, 'errors' => ['status' => 'Only draft reconciliations can be edited.']];
        }

        $data = [
            'statement_date'           => $input['statement_date'] ?? $recon['statement_date'],
            'statement_ending_balance' => round((float) ($input['statement_ending_balance'] ?? $recon['statement_ending_balance']), 2),
            'notes'                    => array_key_exists('notes', $input)
                ? (trim((string) $input['notes']) ?: null)
                : $recon['notes'],
            'updated_by'               => $actingUserId,
        ];

        $bank = $this->banks->find((int) $recon['bank_account_id']);
        if ($bank) {
            $data['book_balance'] = $this->computeBookBalance(
                (int) $bank['account_id'],
                (float) $bank['opening_balance'],
                $data['statement_date']
            );
        }

        if (! $this->recons->update($id, $data)) {
            return ['success' => false, 'errors' => $this->recons->errors()];
        }

        // Toggle cleared flags: lines[id] = 1
        $postedLines = $input['lines'] ?? null;
        if (is_array($postedLines)) {
            $existing = $this->lines->getForReconciliation($id);
            foreach ($existing as $line) {
                $lineId = (int) $line['id'];
                $cleared = ! empty($postedLines[$lineId]) || ! empty($postedLines[(string) $lineId]) ? 1 : 0;
                $this->lines->update($lineId, ['is_cleared' => $cleared]);
            }
        }

        $this->activity->log('bank_reconciliations', 'updated', $actingUserId, "Updated bank reconciliation #{$id}", (string) $id);

        return ['success' => true];
    }

    public function complete(int $id, ?int $actingUserId): array
    {
        $recon = $this->recons->find($id);
        if (! $recon) {
            return ['success' => false, 'error' => 'Reconciliation not found.'];
        }
        if ($recon['status'] !== 'draft') {
            return ['success' => false, 'error' => 'Only draft reconciliations can be completed.'];
        }

        $bank = $this->banks->find((int) $recon['bank_account_id']);
        if (! $bank) {
            return ['success' => false, 'error' => 'Bank account not found.'];
        }

        $bookBalance = $this->computeBookBalance(
            (int) $bank['account_id'],
            (float) $bank['opening_balance'],
            $recon['statement_date']
        );
        $this->recons->update($id, ['book_balance' => $bookBalance, 'updated_by' => $actingUserId]);

        $lines = $this->lines->getForReconciliation($id);
        $unclearedSum = 0.0;
        foreach ($lines as $line) {
            if (! (int) $line['is_cleared']) {
                $unclearedSum += (float) $line['amount'];
            }
        }

        // Statement should match book after removing uncleared items still on the books.
        $adjusted = round($bookBalance - $unclearedSum, 2);
        $statement = round((float) $recon['statement_ending_balance'], 2);
        if (abs($adjusted - $statement) > 0.01) {
            return [
                'success' => false,
                'error'   => sprintf(
                    'Balances do not match. Adjusted book %.2f vs statement %.2f (difference %.2f). Clear matching items or correct the statement balance.',
                    $adjusted,
                    $statement,
                    round($adjusted - $statement, 2)
                ),
            ];
        }

        $this->recons->update($id, [
            'status'       => 'completed',
            'completed_at' => date('Y-m-d H:i:s'),
            'updated_by'   => $actingUserId,
        ]);
        $this->activity->log('bank_reconciliations', 'completed', $actingUserId, "Completed bank reconciliation #{$id}", (string) $id);

        return ['success' => true];
    }

    public function delete(int $id, ?int $actingUserId): array
    {
        $recon = $this->recons->find($id);
        if (! $recon) {
            return ['success' => false, 'error' => 'Reconciliation not found.'];
        }
        if ($recon['status'] !== 'draft') {
            return ['success' => false, 'error' => 'Only draft reconciliations can be deleted.'];
        }

        $this->lines->where('reconciliation_id', $id)->delete();
        $this->recons->delete($id);
        $this->activity->log('bank_reconciliations', 'deleted', $actingUserId, "Deleted bank reconciliation #{$id}", (string) $id);

        return ['success' => true];
    }

    /**
     * opening_balance + sum(debit) - sum(credit) for asset-style bank CoA through statement date.
     */
    public function computeBookBalance(int $coaAccountId, float $openingBalance, string $asOfDate): float
    {
        $db = db_connect();
        $row = $db->table('journal_entry_lines jel')
            ->select('COALESCE(SUM(jel.debit), 0) as total_debit, COALESCE(SUM(jel.credit), 0) as total_credit', false)
            ->join('journal_entries je', 'je.id = jel.journal_entry_id')
            ->where('jel.account_id', $coaAccountId)
            ->where('je.status', 'posted')
            ->where('je.entry_date <=', $asOfDate)
            ->get()
            ->getRowArray();

        $debit  = (float) ($row['total_debit'] ?? 0);
        $credit = (float) ($row['total_credit'] ?? 0);

        return round($openingBalance + $debit - $credit, 2);
    }

    /**
     * Posted JE lines for the bank CoA not already on a completed reconciliation.
     *
     * @return list<array{journal_entry_line_id:int,txn_date:?string,description:?string,amount:float,is_cleared:int}>
     */
    protected function loadUnclearedLines(int $coaAccountId, string $asOfDate): array
    {
        $db = db_connect();
        $builder = $db->table('journal_entry_lines jel')
            ->select('jel.id, jel.description, jel.debit, jel.credit, je.entry_date, je.number as journal_number, je.description as journal_description')
            ->join('journal_entries je', 'je.id = jel.journal_entry_id')
            ->where('jel.account_id', $coaAccountId)
            ->where('je.status', 'posted')
            ->where('je.entry_date <=', $asOfDate)
            ->orderBy('je.entry_date', 'ASC')
            ->orderBy('jel.id', 'ASC');

        // Exclude lines already cleared on a completed reconciliation
        $builder->where(
            "jel.id NOT IN (
                SELECT brl.journal_entry_line_id
                FROM bank_reconciliation_lines brl
                INNER JOIN bank_reconciliations br ON br.id = brl.reconciliation_id
                WHERE brl.journal_entry_line_id IS NOT NULL
                  AND br.status = 'completed'
                  AND brl.is_cleared = 1
            )",
            null,
            false
        );

        $rows = $builder->get()->getResultArray();
        $out  = [];
        foreach ($rows as $row) {
            $amount = round((float) $row['debit'] - (float) $row['credit'], 2);
            $desc   = $row['description']
                ?: ($row['journal_description'] ?: ('JE ' . $row['journal_number']));
            $out[] = [
                'journal_entry_line_id' => (int) $row['id'],
                'txn_date'              => $row['entry_date'],
                'description'           => $desc,
                'amount'                => $amount,
                'is_cleared'            => 0,
            ];
        }

        return $out;
    }
}
