<?php
namespace App\Http\Controllers;

use App\Models as M;
use App\Services\FinanceService;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class BankImportController extends Controller {
    const CREDIT = ['deposit', 'transfer-in', 'interest'];
    const DEBIT = ['withdrawal', 'transfer-out', 'bank-charges'];

    public function create() {
        $this->need('finance.create');
        return view('bank-import.create', ['banks' => M\BankAccount::where('is_active', true)->orderBy('bank_name')->get()]);
    }

    /** Step 1: parse CSV → stash rows in session → review screen with match suggestions. */
    public function parse(Request $r) {
        $this->need('finance.create');
        $v = $r->validate(['bank_account_id' => 'required|exists:bank_accounts,id',
            'file' => 'required|file|max:5120|mimes:csv,txt|extensions:csv']);
        $rows = self::parseCsv($r->file('file')->getRealPath());
        if (empty($rows)) return back()->with('error', 'No statement rows found — check the CSV format.')->withInput();
        if (count($rows) > 500) return back()->with('error', 'Too many rows (500 max per import). Split the file.')->withInput();
        $bankId = (int) $v['bank_account_id'];
        foreach ($rows as &$row) $row['match'] = self::suggest($bankId, $row);
        session(['bank_import' => ['bank_id' => $bankId, 'rows' => $rows]]);
        return view('bank-import.review', ['bank' => M\BankAccount::findOrFail($bankId), 'rows' => $rows]);
    }

    /** Step 2: apply user decisions (match / new / skip). */
    public function confirm(Request $r) {
        $this->need('finance.create');
        $imp = session('bank_import');
        if (!$imp) return redirect()->route('bank-import.create')->with('error', 'Import session expired — upload again.');
        $decisions = $r->validate(['rows' => 'required|array'])['rows'];
        $matched = $created = $skipped = 0;
        DB::transaction(function () use ($imp, $decisions, &$matched, &$created, &$skipped) {
            foreach ($imp['rows'] as $i => $row) {
                $d = $decisions[$i] ?? [];
                $action = $d['action'] ?? ($row['match'] && !$row['match']['reconciled'] ? 'match' : 'new');
                if ($action === 'skip') { $skipped++; continue; }
                if ($action === 'match' && !empty($d['txn_id'])) {
                    $t = M\BankTransaction::where('id', $d['txn_id'])->where('bank_account_id', $imp['bank_id'])->where('reconciled', false)->first();
                    if ($t) { $t->update(['reconciled' => true]); $matched++; continue; }
                    $skipped++; continue; // already reconciled elsewhere
                }
                $type = $d['txn_type'] ?? ($row['direction'] === 'Cr' ? 'deposit' : 'withdrawal');
                if (!in_array($type, array_merge(self::CREDIT, self::DEBIT))) $type = $row['direction'] === 'Cr' ? 'deposit' : 'withdrawal';
                $t = FinanceService::bankTxn($imp['bank_id'], $row['date'], $type, $row['amount'],
                    substr($row['narration'], 0, 255), 'bank-statement', null, $row['reference']);
                $t->update(['reconciled' => true]);
                $created++;
            }
        });
        session()->forget('bank_import');
        return redirect()->route('bank-transactions.index')
            ->with('success', "Statement import done: {$matched} matched, {$created} created, {$skipped} skipped.");
    }

    /** Auto-match: unreconciled same-account txn, same amount+direction, date within ±3 days. */
    protected static function suggest(int $bankId, array $row): ?array {
        $want = $row['direction'] === 'Cr' ? self::CREDIT : self::DEBIT;
        $cands = M\BankTransaction::where('bank_account_id', $bankId)->where('amount', $row['amount'])
            ->whereIn('txn_type', $want)
            ->whereBetween('txn_date', [Carbon::parse($row['date'])->subDays(3)->format('Y-m-d'), Carbon::parse($row['date'])->addDays(3)->format('Y-m-d')])
            ->orderBy('txn_date')->get();
        if ($cands->isEmpty()) return null;
        $open = $cands->firstWhere('reconciled', false);
        $t = $open ?? $cands->first(); // fall back to reconciled one → flags duplicate
        return ['id' => $t->id, 'txn_no' => $t->txn_no, 'txn_date' => $t->txn_date->format('d M Y'),
            'narration' => $t->narration, 'reconciled' => (bool) $t->reconciled];
    }

    /** Flexible parser: finds date / description / debit / credit (or amount) columns by header. */
    public static function parseCsv(string $path): array {
        $fh = fopen($path, 'r');
        $header = null; $map = []; $rows = [];
        while (($c = fgetcsv($fh, 0, ',', '"', '\\')) !== false) {
            $c = array_map(fn($v) => trim((string) $v), $c);
            if ($header === null) {
                if (count(array_filter($c)) < 2) continue;
                $header = array_map('strtolower', $c);
                foreach ($header as $i => $h) {
                    if (preg_match('/date|value dt|posting/', $h)) $map['date'] ??= $i;
                    elseif (preg_match('/desc|narrat|particular|detail|remark/', $h)) $map['narr'] ??= $i;
                    elseif (preg_match('/debit|dr amount|withdrawal|paid out/', $h)) $map['dr'] ??= $i;
                    elseif (preg_match('/credit|cr amount|deposit|paid in/', $h)) $map['cr'] ??= $i;
                    elseif (preg_match('/^amount$/', $h)) $map['amt'] ??= $i;
                    elseif (preg_match('/ref|cheque|tran.?id/', $h)) $map['ref'] ??= $i;
                }
                if (!isset($map['date']) || (!isset($map['dr']) && !isset($map['cr']) && !isset($map['amt']))) { $header = null; $map = []; continue; }
                continue;
            }
            $date = self::parseDate($c[$map['date']] ?? '');
            $dr = isset($map['dr']) ? self::num($c[$map['dr']] ?? '') : 0;
            $cr = isset($map['cr']) ? self::num($c[$map['cr']] ?? '') : 0;
            if (isset($map['amt']) && $dr == 0 && $cr == 0) { $a = self::num($c[$map['amt']] ?? ''); if ($a < 0) $dr = -$a; else $cr = $a; }
            if (!$date || ($dr == 0 && $cr == 0)) continue;
            $rows[] = ['date' => $date, 'narration' => $c[$map['narr']] ?? 'Bank statement entry',
                'direction' => $cr > 0 ? 'Cr' : 'Dr', 'amount' => round($cr > 0 ? $cr : $dr, 2),
                'reference' => isset($map['ref']) ? substr($c[$map['ref']] ?? '', 0, 80) : null];
        }
        fclose($fh);
        return $rows;
    }

    protected static function parseDate(string $s): ?string {
        $s = trim($s);
        foreach (['d/m/Y', 'd-m-Y', 'Y-m-d', 'd-M-y', 'd-M-Y', 'd/m/y', 'm/d/Y'] as $f) {
            try { return Carbon::createFromFormat($f, $s)->format('Y-m-d'); } catch (\Throwable $e) { }
        }
        return null;
    }

    protected static function num(string $s): float {
        $s = trim(str_replace(['GH₵', 'GHS', ',', ' '], '', $s));
        if (preg_match('/^\((.*)\)$/', $s, $m)) $s = '-'.$m[1];
        return is_numeric($s) ? (float) $s : 0.0;
    }
}
