<?php

declare(strict_types=1);

namespace App\Repositories;

use PDO;

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

    public function search(string $q, string $status, string $dateFrom, string $dateTo): array
    {
        $where = [];
        $params = [];

        if ($q !== '') {
            $where[] = "(sp.pay_no LIKE ? OR p.name LIKE ? OR sp.ref_no LIKE ?)";
            $like = '%' . $q . '%';
            $params[] = $like;
            $params[] = $like;
            $params[] = $like;
        }

        if ($status !== '' && in_array($status, ['DRAFT', 'POSTED'], true)) {
            $where[] = 'sp.status = ?';
            $params[] = $status;
        }

        if ($dateFrom !== '') {
            $where[] = 'sp.pay_date >= ?';
            $params[] = $dateFrom . ' 00:00:00';
        }
        if ($dateTo !== '') {
            $where[] = 'sp.pay_date < ?';
            $params[] = date('Y-m-d 00:00:00', strtotime($dateTo . ' +1 day'));
        }

        $whereSql = $where ? ('WHERE ' . implode(' AND ', $where)) : '';
        $st = $this->pdo->prepare("SELECT sp.*, p.name party_name
                     FROM supplier_payments sp
                     JOIN parties p ON p.id = sp.supplier_id
                     $whereSql
                     ORDER BY sp.id DESC
                     LIMIT 500");
        $st->execute($params);
        return $st->fetchAll() ?: [];
    }

    public function findById(int $id): array|false
    {
        $st = $this->pdo->prepare('SELECT * FROM supplier_payments WHERE id=? LIMIT 1');
        $st->execute([$id]);
        return $st->fetch();
    }

    public function findViewById(int $id): array|false
    {
        $st = $this->pdo->prepare("SELECT sp.*, p.name party_name, COALESCE(p.code, CONCAT('P',LPAD(p.id,5,'0'))) party_code
                     FROM supplier_payments sp
                     JOIN parties p ON p.id = sp.supplier_id
                     WHERE sp.id=? LIMIT 1");
        $st->execute([$id]);
        return $st->fetch();
    }

    public function fetchParty(int $partyId): array|false
    {
        $st = $this->pdo->prepare("SELECT id, name, COALESCE(code, CONCAT('P',LPAD(id,5,'0'))) code FROM parties WHERE id=? LIMIT 1");
        $st->execute([$partyId]);
        return $st->fetch();
    }

    public function create(array $data, int $userId): int
    {
        $this->pdo->prepare("INSERT INTO supplier_payments
            (pay_no, supplier_id, pay_date, amount, method, ref_no, notes, status, created_by)
            VALUES (NULL,?,?,?,?,?,?,'DRAFT',?)")
            ->execute([$data['party_id'], $data['pay_date'], $data['amount'], $data['method'], $data['ref_no'], $data['notes'], $userId]);
        return (int)$this->pdo->lastInsertId();
    }

    public function update(int $id, array $data): void
    {
        $this->pdo->prepare("UPDATE supplier_payments SET supplier_id=?, pay_date=?, amount=?, method=?, ref_no=?, notes=? WHERE id=?")
            ->execute([$data['party_id'], $data['pay_date'], $data['amount'], $data['method'], $data['ref_no'], $data['notes'], $id]);
    }

    public function updatePayNo(int $id, string $payNo): void
    {
        $this->pdo->prepare('UPDATE supplier_payments SET pay_no=? WHERE id=?')->execute([$payNo, $id]);
    }

    public function deleteAllocations(int $paymentId): void
    {
        $this->pdo->prepare('DELETE FROM supplier_payment_allocations WHERE payment_id=?')->execute([$paymentId]);
    }

    public function insertAllocation(int $paymentId, int $invoiceId, float $amount): void
    {
        $this->pdo->prepare('INSERT INTO supplier_payment_allocations (payment_id, purchase_invoice_id, amount) VALUES (?,?,?)')
            ->execute([$paymentId, $invoiceId, $amount]);
    }

    public function fetchCurrentAllocMap(int $paymentId, array $invoiceIds): array
    {
        if (!$invoiceIds) return [];
        $inClause = implode(',', array_fill(0, count($invoiceIds), '?'));
        $st = $this->pdo->prepare("SELECT purchase_invoice_id AS id, SUM(amount) AS cur_alloc
                              FROM supplier_payment_allocations
                              WHERE payment_id=? AND purchase_invoice_id IN ($inClause)
                              GROUP BY purchase_invoice_id");
        $st->execute(array_merge([$paymentId], $invoiceIds));
        $map = [];
        foreach (($st->fetchAll() ?: []) as $r) {
            $map[(int)$r['id']] = (float)$r['cur_alloc'];
        }
        return $map;
    }

    public function fetchPostedAllocMap(array $invoiceIds): array
    {
        if (!$invoiceIds) return [];
        $inClause = implode(',', array_fill(0, count($invoiceIds), '?'));
        $sql = "SELECT a.purchase_invoice_id AS id, SUM(a.amount) AS posted_alloc
                      FROM supplier_payment_allocations a
                      JOIN supplier_payments sp ON sp.id=a.payment_id
                      WHERE sp.status='POSTED' AND a.purchase_invoice_id IN ($inClause)
                      GROUP BY a.purchase_invoice_id";
        $st = $this->pdo->prepare($sql);
        $st->execute($invoiceIds);
        $map = [];
        foreach (($st->fetchAll() ?: []) as $r) {
            $map[(int)$r['id']] = (float)$r['posted_alloc'];
        }
        return $map;
    }

    public function fetchPostedInvoicesMap(array $invoiceIds, int $partyId): array
    {
        if (!$invoiceIds) return [];
        $inClause = implode(',', array_fill(0, count($invoiceIds), '?'));
        $st = $this->pdo->prepare("SELECT id, total FROM purchase_invoices WHERE id IN ($inClause) AND COALESCE(party_id, supplier_id)=? AND status='POSTED'");
        $st->execute(array_merge($invoiceIds, [$partyId]));
        $map = [];
        foreach (($st->fetchAll() ?: []) as $r) {
            $map[(int)$r['id']] = (float)$r['total'];
        }
        return $map;
    }

    public function fetchReturnMap(array $invoiceIds): array
    {
        if (!$invoiceIds || !table_exists($this->pdo, 'purchase_returns')) return [];
        $inClause = implode(',', array_fill(0, count($invoiceIds), '?'));
        $st = $this->pdo->prepare("SELECT source_invoice_id AS id, COALESCE(SUM(total),0) AS ret_total
            FROM purchase_returns WHERE status='POSTED' AND source_invoice_id IN ($inClause) GROUP BY source_invoice_id");
        $st->execute($invoiceIds);
        $map = [];
        foreach (($st->fetchAll() ?: []) as $r) {
            $map[(int)$r['id']] = (float)$r['ret_total'];
        }
        return $map;
    }

    public function deleteLedger(int $paymentId): void
    {
        $this->pdo->prepare("DELETE FROM party_ledger WHERE doc_type='PAYMENT' AND doc_id=?")->execute([$paymentId]);
    }

    public function markPosted(int $paymentId, int $userId): void
    {
        $this->pdo->prepare("UPDATE supplier_payments SET status='POSTED', posted_by=?, posted_at=NOW() WHERE id=?")
            ->execute([$userId, $paymentId]);
    }

    public function fetchAllocations(int $paymentId): array
    {
        if (!table_exists($this->pdo, 'supplier_payment_allocations')) return [];
        $st = $this->pdo->prepare("SELECT a.purchase_invoice_id, a.amount, pi.inv_no, DATE(pi.inv_date) AS inv_date
                        FROM supplier_payment_allocations a
                        JOIN purchase_invoices pi ON pi.id=a.purchase_invoice_id
                        WHERE a.payment_id=?
                        ORDER BY pi.inv_date DESC, pi.id DESC");
        $st->execute([$paymentId]);
        return $st->fetchAll() ?: [];
    }

    public function fetchSuppliers(): array
    {
        $st = $this->pdo->query("SELECT id, name, COALESCE(code, CONCAT('P',LPAD(id,5,'0'))) code FROM parties WHERE is_supplier=1 ORDER BY name");
        return $st->fetchAll() ?: [];
    }

    public function fetchSupplierById(int $partyId): array|false
    {
        $st = $this->pdo->prepare("SELECT id, name, COALESCE(code, CONCAT('P',LPAD(id,5,'0'))) code, opening_balance
                       FROM parties WHERE id=? AND is_supplier=1 LIMIT 1");
        $st->execute([$partyId]);
        return $st->fetch();
    }

    public function fetchStatementRows(int $partyId, string $from, string $to, bool $hasEventAt): array
    {
        $statementSql = "SELECT pl.doc_date, " . ($hasEventAt ? "pl.event_at, " : "NULL AS event_at, ") . "pl.doc_type, pl.doc_no, pl.doc_id, pl.debit, pl.credit,
                                COALESCE(
                                  NULLIF(pl.notes,''),
                                  NULLIF(si.notes,''),
                                  NULLIF(cr.notes,''),
                                  NULLIF(cp.notes,''),
                                  NULLIF(pi.notes,''),
                                  NULLIF(pr.notes,''),
                                  NULLIF(sr.notes,'')
                                ) AS notes
                         FROM party_ledger pl
                         LEFT JOIN sales_invoices si ON si.id = pl.doc_id AND pl.doc_type='SALES_INV'
                         LEFT JOIN customer_receipts cr ON cr.id = pl.doc_id AND pl.doc_type='RCPT'
                         LEFT JOIN cash_payments cp ON cp.id = pl.doc_id AND pl.doc_type='PAY'
                         LEFT JOIN purchase_invoices pi ON pi.id = pl.doc_id AND pl.doc_type='PURCHASE_INV'
                         LEFT JOIN purchase_returns pr ON pr.id = pl.doc_id AND pl.doc_type='PURCHASE_RET'
                         LEFT JOIN sales_returns sr ON sr.id = pl.doc_id AND pl.doc_type='SALES_RET'
                         WHERE pl.party_id=? AND pl.doc_date BETWEEN ? AND ?
                         ORDER BY pl.doc_date, " . ($hasEventAt ? "pl.event_at, " : "pl.created_at, ") . "pl.id";
        $st = $this->pdo->prepare($statementSql);
        $st->execute([$partyId, $from, $to]);
        return $st->fetchAll() ?: [];
    }

    public function fetchSupplierAgingInvoices(int $partyId, string $asOf): array
    {
        if ($partyId > 0) {
            $st = $this->pdo->prepare("SELECT id, party_id, inv_no, inv_date, total FROM purchase_invoices WHERE party_id=? AND status='POSTED' AND inv_date <= ? ORDER BY inv_date");
            $st->execute([$partyId, $asOf . ' 23:59:59']);
        } else {
            $st = $this->pdo->prepare("SELECT id, party_id, inv_no, inv_date, total FROM purchase_invoices WHERE status='POSTED' AND inv_date <= ? ORDER BY inv_date");
            $st->execute([$asOf . ' 23:59:59']);
        }
        return $st->fetchAll() ?: [];
    }

    public function fetchAgingReturnMap(string $asOf): array
    {
        if (!table_exists($this->pdo, 'purchase_returns')) return [];
        $st = $this->pdo->prepare("SELECT source_invoice_id, COALESCE(SUM(total),0) s FROM purchase_returns WHERE status='POSTED' AND ret_date <= ? GROUP BY source_invoice_id");
        $st->execute([$asOf . ' 23:59:59']);
        $map = [];
        foreach (($st->fetchAll() ?: []) as $r) {
            $map[(int)$r['source_invoice_id']] = (float)$r['s'];
        }
        return $map;
    }

    public function fetchAgingPaymentMap(string $asOf): array
    {
        $st = $this->pdo->prepare("SELECT spa.purchase_invoice_id, COALESCE(SUM(spa.amount),0) s
          FROM supplier_payment_allocations spa
          JOIN supplier_payments sp ON sp.id = spa.payment_id
          WHERE sp.status='POSTED' AND sp.pay_date <= ?
          GROUP BY spa.purchase_invoice_id");
        $st->execute([$asOf]);
        $map = [];
        foreach (($st->fetchAll() ?: []) as $r) {
            $map[(int)$r['purchase_invoice_id']] = (float)$r['s'];
        }
        return $map;
    }
}
