<?php
/**
 * BankStatements — statement import + list + view + line-level query.
 *
 * Import path:
 *   parseCsv(): rows[] with normalised columns
 *     Expected columns (order-tolerant, header-driven):
 *       date       — YYYY-MM-DD or DD/MM/YYYY or D-M-Y
 *       description— free text
 *       reference  — bank txn ref (optional)
 *       amount     — signed (+deposit / -withdrawal)          -- OR --
 *       money_in   + money_out (either fills `amount`)
 *       balance    — running balance (optional)
 *
 *   createFromCsv(): opens a bank_statements row + inserts its lines
 *     inside a single transaction so a partial import never lands.
 *
 * The reconciliation logic itself lives in BankReconciler — this class
 * is intentionally thin so import edge cases don't leak into recon.
 */
class BankStatements
{
    private int $tenantId;

    public function __construct(?int $tenantId = null)
    {
        $this->tenantId = $tenantId ?? (defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1);
    }

    /* ══════════════════════════════════════════════════════════════════
       CRUD
       ══════════════════════════════════════════════════════════════════ */

    public function listPaginated(int $page, int $perPage, array $filters = []): array
    {
        $where  = ['bs.deleted = 0', 'bs.tenant_id = :tenant'];
        $params = [':tenant' => $this->tenantId];

        if (!empty($filters['bank_account_id'])) {
            $where[] = 'bs.bank_account_id = :bank';
            $params[':bank'] = (int)$filters['bank_account_id'];
        }
        if (!empty($filters['status'])) {
            $where[] = 'bs.status = :status';
            $params[':status'] = $filters['status'];
        }
        $whereSql = implode(' AND ', $where);
        $db = new DB();

        $countRs = $db->mySelect("SELECT COUNT(*) AS n FROM bank_statements bs WHERE $whereSql", $params);
        $total = (int)($countRs[0]['n'] ?? 0);

        $page = max(1, $page); $perPage = max(1, min(200, $perPage));
        $offset = ($page - 1) * $perPage;

        $rows = $db->mySelect(
            "SELECT bs.*, coa.account_code, coa.account_name
               FROM bank_statements bs
          LEFT JOIN chart_of_accounts coa ON coa.c0d = bs.bank_account_id
              WHERE $whereSql
           ORDER BY bs.statement_to DESC, bs.c0d DESC
              LIMIT $perPage OFFSET $offset",
            $params
        ) ?: [];

        return ['rows' => $rows, 'total' => $total];
    }

    public function byId(int $id): ?array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT bs.*, coa.account_code, coa.account_name
               FROM bank_statements bs
          LEFT JOIN chart_of_accounts coa ON coa.c0d = bs.bank_account_id
              WHERE bs.c0d = :id AND bs.deleted = 0 LIMIT 1",
            [':id' => $id]
        );
        return $rs[0] ?? null;
    }

    public function linesFor(int $statementId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT bsl.*,
                    je.je_no        AS matched_je_no,
                    je.entry_date   AS matched_je_date,
                    jl.debit        AS matched_debit,
                    jl.credit       AS matched_credit,
                    jl.narrative    AS matched_narrative
               FROM bank_statement_lines bsl
          LEFT JOIN journal_lines   jl ON jl.c0d = bsl.matched_journal_line_id
          LEFT JOIN journal_entries je ON je.c0d = jl.journal_entry_id
              WHERE bsl.statement_id = :sid AND bsl.deleted = 0
           ORDER BY bsl.txn_date, bsl.line_no",
            [':sid' => $statementId]
        ) ?: [];
    }

    /**
     * Journal-line side of the recon: every posting hitting the bank account
     * within the statement window that isn't already matched to another
     * statement line. Used by the matching UI and by autoSuggest().
     */
    public function bookLinesFor(int $statementId): array
    {
        $s = $this->byId($statementId);
        if (!$s) return [];
        $db = new DB();
        return $db->mySelect(
            "SELECT jl.c0d, jl.debit, jl.credit, jl.narrative,
                    je.je_no, je.entry_date, je.source_type,
                    -- match state — is this line already claimed by any
                    -- non-deleted statement line?
                    COALESCE((
                        SELECT bsl_used.c0d FROM bank_statement_lines bsl_used
                         WHERE bsl_used.matched_journal_line_id = jl.c0d
                           AND bsl_used.deleted = 0
                         LIMIT 1
                    ), 0) AS matched_stmt_line_id
               FROM journal_lines jl
               JOIN journal_entries je ON je.c0d = jl.journal_entry_id
                                      AND je.status = 'POSTED'
                                      AND je.deleted = 0
              WHERE jl.account_id = :bank
                AND je.entry_date BETWEEN :from AND :to
           ORDER BY je.entry_date, jl.c0d",
            [
                ':bank' => (int)$s['bank_account_id'],
                ':from' => $s['statement_from'],
                ':to'   => $s['statement_to'],
            ]
        ) ?: [];
    }

    /* ══════════════════════════════════════════════════════════════════
       Import
       ══════════════════════════════════════════════════════════════════ */

    /**
     * Parse a CSV file into a normalised row list. Detects the header row,
     * maps common column aliases, and returns each row as:
     *   ['txn_date' => 'YYYY-MM-DD', 'description', 'reference', 'amount' => float, 'balance']
     *
     * Returns ['ok' => bool, 'msg' => string, 'rows' => array].
     */
    public function parseCsv(string $filePath): array
    {
        if (!is_readable($filePath)) {
            return ['ok' => false, 'msg' => 'CSV file is not readable.', 'rows' => []];
        }
        $fh = fopen($filePath, 'r');
        if (!$fh) return ['ok' => false, 'msg' => 'Could not open CSV.', 'rows' => []];

        // Sniff separator: TSV or CSV
        $firstLine = fgets($fh);
        rewind($fh);
        $sep = (substr_count((string)$firstLine, "\t") > substr_count((string)$firstLine, ',')) ? "\t" : ',';

        $rawHeader = fgetcsv($fh, 0, $sep);
        if (!$rawHeader) { fclose($fh); return ['ok' => false, 'msg' => 'Empty CSV.', 'rows' => []]; }

        // Normalise headers
        $headers = array_map(fn($h) => strtolower(trim(str_replace([' ', '_', '-'], '', (string)$h))), $rawHeader);

        // Column resolvers — first match wins.
        $find = function (array $aliases) use ($headers): int {
            foreach ($aliases as $a) {
                $idx = array_search($a, $headers, true);
                if ($idx !== false) return (int)$idx;
            }
            return -1;
        };

        $col = [
            'date'   => $find(['date', 'txndate', 'transactiondate', 'valuedate', 'postingdate']),
            'desc'   => $find(['description', 'narrative', 'details', 'particulars', 'memo']),
            'ref'    => $find(['reference', 'ref', 'txnref', 'transactionref', 'chequeno', 'chequenumber']),
            'amt'    => $find(['amount', 'signedamount']),
            'moneyIn'  => $find(['moneyin', 'credit', 'deposit', 'deposits', 'in']),
            'moneyOut' => $find(['moneyout', 'debit', 'withdrawal', 'withdrawals', 'out']),
            'bal'    => $find(['balance', 'runningbalance', 'closingbalance']),
        ];

        if ($col['date'] < 0) {
            fclose($fh);
            return ['ok' => false, 'msg' => 'CSV is missing a date column. Expected one of: Date, TxnDate, TransactionDate.', 'rows' => []];
        }
        if ($col['amt'] < 0 && ($col['moneyIn'] < 0 || $col['moneyOut'] < 0)) {
            fclose($fh);
            return ['ok' => false, 'msg' => 'CSV needs either an Amount column, or both MoneyIn and MoneyOut columns.', 'rows' => []];
        }

        $rows = [];
        $lineNo = 0;
        while (($r = fgetcsv($fh, 0, $sep)) !== false) {
            if (count($r) < 2) continue;                 // skip blank / trailer
            $rawDate = trim((string)($r[$col['date']] ?? ''));
            if ($rawDate === '') continue;
            $txnDate = $this->parseDate($rawDate);
            if (!$txnDate) continue;

            $desc  = $col['desc'] >= 0 ? trim((string)($r[$col['desc']] ?? '')) : '';
            $ref   = $col['ref']  >= 0 ? trim((string)($r[$col['ref']]  ?? '')) : '';
            $bal   = $col['bal']  >= 0 ? $this->parseAmount((string)($r[$col['bal']] ?? '')) : null;

            if ($col['amt'] >= 0) {
                $amt = $this->parseAmount((string)($r[$col['amt']] ?? ''));
            } else {
                $in  = $this->parseAmount((string)($r[$col['moneyIn']]  ?? ''));
                $out = $this->parseAmount((string)($r[$col['moneyOut']] ?? ''));
                $amt = $in - $out;                       // signed: + in, - out
            }
            if (abs($amt) < 0.005) continue;              // skip zero rows

            $rows[] = [
                'line_no'         => ++$lineNo,
                'txn_date'        => $txnDate,
                'description'     => mb_substr($desc, 0, 255),
                'reference'       => mb_substr($ref, 0, 60),
                'amount'          => round($amt, 2),
                'running_balance' => $bal,
            ];
        }
        fclose($fh);

        if (empty($rows)) {
            return ['ok' => false, 'msg' => 'No importable rows found. Check the CSV format.', 'rows' => []];
        }
        return ['ok' => true, 'msg' => count($rows) . ' rows parsed.', 'rows' => $rows];
    }

    /**
     * Insert a statement header + all lines in one transaction. If the caller
     * pre-parsed via parseCsv() they can pass rows directly.
     */
    public function createFromCsv(array $header, array $rows, int $uid): array
    {
        if (empty($rows)) return ['ok' => false, 'msg' => 'No rows to import.', 'id' => 0];

        $bankId = (int)($header['bank_account_id'] ?? 0);
        if ($bankId <= 0) return ['ok' => false, 'msg' => 'Pick the bank account first.', 'id' => 0];

        $from = trim((string)($header['statement_from'] ?? ''));
        $to   = trim((string)($header['statement_to']   ?? ''));
        if (!$from || !$to) {
            // Derive from the row range if the operator didn't type them
            $dates = array_column($rows, 'txn_date');
            sort($dates);
            $from = $from ?: $dates[0];
            $to   = $to   ?: end($dates);
        }

        // DB wrapper doesn't expose the underlying PDO handle, so the import
        // runs statement-by-statement. Insert the header first — if any line
        // insert fails we soft-delete the header so the operator can retry
        // without an orphan statement lingering in the list.
        $db = new DB();

        $ins = $db->insertUpdate(
            "INSERT INTO bank_statements
                (tenant_id, bank_account_id, statement_from, statement_to,
                 opening_balance, closing_balance, reference, notes,
                 line_count, created_by, created_on)
             VALUES (:t, :b, :f, :to, :ob, :cb, :ref, :notes, :n, :uid, NOW())",
            [
                ':t'     => $this->tenantId,
                ':b'     => $bankId,
                ':f'     => $from,
                ':to'    => $to,
                ':ob'    => (float)($header['opening_balance'] ?? 0),
                ':cb'    => (float)($header['closing_balance'] ?? 0),
                ':ref'   => mb_substr(trim((string)($header['reference'] ?? '')), 0, 60),
                ':notes' => trim((string)($header['notes'] ?? '')),
                ':n'     => count($rows),
                ':uid'   => $uid,
            ]
        );
        $stmtId = (int)$ins['last_id'];
        if ($stmtId <= 0) {
            return ['ok' => false, 'msg' => 'Could not create statement header.', 'id' => 0];
        }

        $inserted = 0;
        foreach ($rows as $r) {
            $res = $db->insertUpdate(
                "INSERT INTO bank_statement_lines
                    (statement_id, line_no, txn_date, description, reference,
                     amount, running_balance, created_on)
                 VALUES (:s, :l, :d, :de, :r, :a, :b, NOW())",
                [
                    ':s'  => $stmtId,
                    ':l'  => (int)$r['line_no'],
                    ':d'  => $r['txn_date'],
                    ':de' => $r['description'],
                    ':r'  => $r['reference'],
                    ':a'  => $r['amount'],
                    ':b'  => $r['running_balance'],
                ]
            );
            if ((int)($res['success'] ?? 0) > 0) $inserted++;
        }

        if ($inserted === 0) {
            // Nothing landed — soft-delete the empty header so it doesn't
            // clutter the list.
            $db->insertUpdate(
                "UPDATE bank_statements SET deleted = 1 WHERE c0d = :id",
                [':id' => $stmtId]
            );
            return ['ok' => false, 'msg' => 'No lines could be persisted.', 'id' => 0];
        }

        return ['ok' => true, 'msg' => 'Imported ' . $inserted . ' lines.', 'id' => $stmtId];
    }

    public function voidStatement(int $id, int $uid): array
    {
        $db = new DB();
        $db->insertUpdate(
            "UPDATE bank_statements SET status = 'VOID', deleted = 1,
                    edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id",
            [':id' => $id, ':uid' => $uid]
        );
        return ['ok' => true, 'msg' => 'Statement voided.'];
    }

    /**
     * Bank / cash COAs — anything flagged is_bank=1 in the chart. Used
     * to populate the bank picker on the import form.
     */
    public function bankAccounts(): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT c0d, account_code, account_name
               FROM chart_of_accounts
              WHERE is_bank = 1 AND is_active = 1
           ORDER BY account_code",
            []
        ) ?: [];
    }

    /* ── helpers ───────────────────────────────────────────────────── */

    private function parseDate(string $s): ?string
    {
        $s = trim($s);
        if ($s === '') return null;
        // Try ISO first
        if (preg_match('/^(\d{4})-(\d{2})-(\d{2})/', $s, $m)) {
            return $m[1] . '-' . $m[2] . '-' . $m[3];
        }
        // dd/mm/yyyy or dd-mm-yyyy (TZ-common)
        if (preg_match('/^(\d{1,2})[\/\-\.](\d{1,2})[\/\-\.](\d{2,4})/', $s, $m)) {
            $day = str_pad($m[1], 2, '0', STR_PAD_LEFT);
            $mon = str_pad($m[2], 2, '0', STR_PAD_LEFT);
            $yr  = strlen($m[3]) === 2 ? ('20' . $m[3]) : $m[3];
            return "$yr-$mon-$day";
        }
        // strtotime fallback for "01 Aug 2026" etc
        $t = strtotime($s);
        return $t ? date('Y-m-d', $t) : null;
    }

    private function parseAmount(string $s): float
    {
        $s = trim($s);
        if ($s === '' || $s === '-' || strtoupper($s) === 'NULL') return 0.0;
        // Parenthesised negatives: (1,234.56)
        $neg = false;
        if ($s[0] === '(' && substr($s, -1) === ')') { $neg = true; $s = substr($s, 1, -1); }
        // Strip thousand separators; unify decimal to '.'
        $s = str_replace([' ', ','], ['', ''], $s);
        // Drop currency prefixes like "TZS", "TSh", "$"
        $s = preg_replace('/[^\d.\-+]/', '', $s) ?? '';
        if ($s === '' || $s === '.' || $s === '-') return 0.0;
        $v = (float)$s;
        return $neg ? -abs($v) : $v;
    }
}
