<?php

declare(strict_types=1);

namespace App\Repositories;

use PDO;

final class BankTransactionRepository
{
    public function __construct(private PDO $pdo) {}
    public function search(string $q, int $bankId, string $type): array { $where=['1=1']; $params=[]; if($bankId>0){$where[]='bt.bank_account_id = ?'; $params[]=$bankId;} if($q!==''){ $where[]='(ba.name LIKE ? OR bt.ref_no LIKE ? OR bt.notes LIKE ?)'; $like="%{$q}%"; array_push($params,$like,$like,$like);} if($type!==''){ $where[]='bt.source_type = ?'; $params[]=$type; } $sql="SELECT bt.*, ba.name AS bank_name, cb.name AS cashbox_name FROM bank_transactions bt INNER JOIN bank_accounts ba ON ba.id = bt.bank_account_id LEFT JOIN cashboxes cb ON cb.id = bt.source_id AND bt.source_type IN ('cash_to_bank','bank_to_cash') WHERE ".implode(' AND ',$where)." ORDER BY bt.trx_date DESC, bt.id DESC"; $st=$this->pdo->prepare($sql); $st->execute($params); return $st->fetchAll() ?: []; }
    public function activeBankAccounts(): array { return $this->pdo->query("SELECT id,name FROM bank_accounts WHERE is_active=1 ORDER BY name ASC")->fetchAll() ?: []; }
    public function activeCashboxes(): array { return $this->pdo->query("SELECT id,name FROM cashboxes WHERE is_active=1 ORDER BY name ASC")->fetchAll() ?: []; }
    public function create(array $d): int { $this->pdo->prepare("INSERT INTO bank_transactions (bank_account_id, trx_date, trx_type, amount, source_type, source_id, ref_no, notes, user_id) VALUES (?,?,?,?,?,?,?,?,?)")->execute([$d['bank_account_id'],$d['trx_date'],$d['trx_type'],$d['amount'],$d['source_type'],$d['source_id'],$d['ref_no'],$d['notes'],$d['user_id']]); return (int)$this->pdo->lastInsertId(); }
    public function findAccount(int $id): array|false { $st=$this->pdo->prepare("SELECT * FROM bank_accounts WHERE id=? LIMIT 1"); $st->execute([$id]); return $st->fetch(); }
    public function statementRows(int $bankId, string $from, string $to, string $q): array { $params=[$bankId,$from.' 00:00:00',$to.' 23:59:59']; $whereQ=''; if($q!==''){ $whereQ=" AND (bt.ref_no LIKE ? OR bt.notes LIKE ? OR ba.name LIKE ?)"; $like="%{$q}%"; array_push($params,$like,$like,$like); } $sql="SELECT bt.*, ba.name AS bank_name, cb.name AS cashbox_name FROM bank_transactions bt INNER JOIN bank_accounts ba ON ba.id=bt.bank_account_id LEFT JOIN cashboxes cb ON cb.id = bt.source_id AND bt.source_type IN ('cash_to_bank','bank_to_cash') WHERE bt.bank_account_id=? AND bt.trx_date BETWEEN ? AND ? {$whereQ} ORDER BY bt.trx_date ASC, bt.id ASC"; $st=$this->pdo->prepare($sql); $st->execute($params); return $st->fetchAll() ?: []; }
}
