<?php

declare(strict_types=1);

namespace App\Repositories;

use PDO;

final class CashPaymentRepository
{
    public function __construct(private PDO $pdo) {}

    public function search(string $q, string $from, string $to): array
    {
        $params = [$from . ' 00:00:00', date('Y-m-d 00:00:00', strtotime($to . ' +1 day'))]; $where = "WHERE voucher_date >= ? AND voucher_date < ?";
        if ($q !== '') { $where .= " AND (voucher_no LIKE ? OR payee_name LIKE ? OR COALESCE(ref_no,'') LIKE ?)"; $like = "%{$q}%"; array_push($params, $like, $like, $like); }
        $sql = "SELECT p.*, cb.name cashbox_name, ba.name bank_account_name, s.name party_name, ei.name expense_item_name, ec.name expense_category_name
        FROM cash_payments p
        LEFT JOIN cashboxes cb ON cb.id=p.cashbox_id
        LEFT JOIN bank_accounts ba ON ba.id=p.bank_account_id
        LEFT JOIN parties s ON s.id = COALESCE(p.party_id, p.supplier_id)
        LEFT JOIN expense_items ei ON ei.id = p.expense_item_id
        LEFT JOIN expense_categories ec ON ec.id = ei.category_id
        $where ORDER BY p.id DESC";
        $st = $this->pdo->prepare($sql); $st->execute($params); return $st->fetchAll() ?: [];
    }

    public function activeCashboxes(): array { return $this->pdo->query("SELECT id,name FROM cashboxes WHERE is_active=1 ORDER BY id ASC")->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 activeExpenseItems(): array { return $this->pdo->query("SELECT ei.id, ei.name, ec.name AS category_name FROM expense_items ei JOIN expense_categories ec ON ec.id=ei.category_id WHERE ei.is_active=1 AND ec.is_active=1 ORDER BY ec.sort_order ASC, ec.name ASC, ei.sort_order ASC, ei.name ASC")->fetchAll() ?: []; }

    public function findSupplierParty(int $partyId): array|false
    {
        $st = $this->pdo->prepare("SELECT id, name FROM parties WHERE id=? AND is_active=1 AND is_supplier=1 LIMIT 1"); $st->execute([$partyId]); return $st->fetch();
    }
    public function findPartyById(int $partyId): array|false
    {
        $st = $this->pdo->prepare("SELECT id,name FROM parties WHERE id=? LIMIT 1"); $st->execute([$partyId]); return $st->fetch();
    }
    public function findExpenseItem(int $id): array|false
    {
        $st = $this->pdo->prepare("SELECT ei.id, ei.name, ec.name AS category_name FROM expense_items ei JOIN expense_categories ec ON ec.id=ei.category_id WHERE ei.id=? AND ei.is_active=1 AND ec.is_active=1 LIMIT 1"); $st->execute([$id]); return $st->fetch();
    }
    public function create(array $data): int
    {
        $this->pdo->prepare("INSERT INTO cash_payments (voucher_no, voucher_date, cashbox_id, bank_account_id, account_source_type, payment_type, supplier_id, party_id, expense_item_id, amount, pay_method, payee_name, purpose, ref_no, notes, status, user_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)")
            ->execute([$data['voucher_no'],$data['voucher_date'],$data['cashbox_id'],$data['bank_account_id'],$data['account_source_type'],$data['payment_type'],null,$data['party_id'],$data['expense_item_id'],$data['amount'],$data['pay_method'],$data['payee_name'],$data['purpose'],$data['ref_no'],$data['notes'],'POSTED',$data['user_id']]);
        return (int)$this->pdo->lastInsertId();
    }
    public function findViewById(int $id): array|false
    {
        $st = $this->pdo->prepare("SELECT p.*, cb.name cashbox_name, ba.name bank_account_name, s.name party_name, ei.name expense_item_name, ec.name expense_category_name
        FROM cash_payments p
        LEFT JOIN cashboxes cb ON cb.id=p.cashbox_id
        LEFT JOIN bank_accounts ba ON ba.id=p.bank_account_id
        LEFT JOIN parties s ON s.id = COALESCE(p.party_id, p.supplier_id)
        LEFT JOIN expense_items ei ON ei.id = p.expense_item_id
        LEFT JOIN expense_categories ec ON ec.id = ei.category_id
        WHERE p.id=? LIMIT 1");
        $st->execute([$id]); return $st->fetch();
    }
}
