<?php

declare(strict_types=1);

namespace App\Repositories;

use PDO;

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

    private function buildSearchQuery(string $q, string $status, string $dateFrom, string $dateTo, bool $forReturn): array
    {
        $status = strtoupper($status);
        if ($forReturn && $status === '') {
            $status = 'POSTED';
        }

        $where = [];
        $params = [];
        if ($q !== '') {
            $where[] = "(pi.inv_no LIKE ? OR s.name LIKE ? OR s.code LIKE ?)";
            $like = '%' . $q . '%';
            array_push($params, $like, $like, $like);
        }
        if ($status !== '' && in_array($status, ['DRAFT','POSTED','CANCELED'], true)) {
            $where[] = 'pi.status = ?';
            $params[] = $status;
        }
        if ($dateFrom !== '') {
            $where[] = 'pi.inv_date >= ?';
            $params[] = $dateFrom . ' 00:00:00';
        }
        if ($dateTo !== '') {
            $where[] = 'pi.inv_date < ?';
            $params[] = date('Y-m-d 00:00:00', strtotime($dateTo . ' +1 day'));
        }
        $whereSql = $where ? ('WHERE ' . implode(' AND ', $where)) : '';

        $hasAlloc = table_exists($this->pdo, 'supplier_payment_allocations');
        $hasPay = table_exists($this->pdo, 'supplier_payments');
        $hasRet = table_exists($this->pdo, 'purchase_returns');

        $joinPaid = $hasAlloc && $hasPay
            ? "LEFT JOIN (SELECT a.purchase_invoice_id, SUM(a.amount) AS paid_total FROM supplier_payment_allocations a JOIN supplier_payments sp ON sp.id=a.payment_id WHERE sp.status='POSTED' GROUP BY a.purchase_invoice_id) pay ON pay.purchase_invoice_id = pi.id"
            : "LEFT JOIN (SELECT NULL AS purchase_invoice_id, 0 AS paid_total) pay ON 1=0";

        $joinRet = $hasRet
            ? "LEFT JOIN (SELECT source_invoice_id, SUM(total) AS ret_total FROM purchase_returns WHERE status='POSTED' GROUP BY source_invoice_id) pr ON pr.source_invoice_id = pi.id"
            : "LEFT JOIN (SELECT NULL AS source_invoice_id, 0 AS ret_total) pr ON 1=0";

        $fromSql = " FROM purchase_invoices pi
                     LEFT JOIN parties s ON s.id = pi.party_id
                     JOIN warehouses w ON w.id = pi.warehouse_id
                     $joinPaid
                     $joinRet
                     $whereSql";

        return [$fromSql, $params];
    }

    public function countSearch(string $q, string $status, string $dateFrom, string $dateTo, bool $forReturn): int
    {
        [$fromSql, $params] = $this->buildSearchQuery($q, $status, $dateFrom, $dateTo, $forReturn);
        $st = $this->pdo->prepare("SELECT COUNT(*)" . $fromSql);
        $st->execute($params);
        return (int) ($st->fetchColumn() ?: 0);
    }

    public function searchPage(string $q, string $status, string $dateFrom, string $dateTo, bool $forReturn, int $offset, int $limit): array
    {
        [$fromSql, $params] = $this->buildSearchQuery($q, $status, $dateFrom, $dateTo, $forReturn);
        $limit = max(1, $limit);
        $offset = max(0, $offset);
        $st = $this->pdo->prepare("SELECT pi.*, s.name supplier_name, COALESCE(s.code, CONCAT('P',LPAD(s.id,5,'0'))) supplier_code,
                            w.name warehouse_name,
                            COALESCE(pay.paid_total,0) AS paid_total,
                            COALESCE(pr.ret_total,0) AS ret_total
                     " . $fromSql . "
                     ORDER BY pi.inv_date DESC, pi.id DESC
                     LIMIT {$limit} OFFSET {$offset}");
        $st->execute($params);
        return $st->fetchAll() ?: [];
    }

    public function search(string $q, string $status, string $dateFrom, string $dateTo, bool $forReturn): array
    {
        return $this->searchPage($q, $status, $dateFrom, $dateTo, $forReturn, 0, 300);
    }

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

    public function findViewById(int $id): array|false
    {
        $st = $this->pdo->prepare("SELECT pi.*, s.name party_name, COALESCE(s.code, CONCAT('P',LPAD(s.id,5,'0'))) party_code,
                            w.name warehouse_name
                     FROM purchase_invoices pi
                     LEFT JOIN parties s ON s.id=pi.party_id
                     JOIN warehouses w ON w.id=pi.warehouse_id
                     WHERE pi.id=? LIMIT 1");
        $st->execute([$id]);
        return $st->fetch();
    }

    public function fetchLines(int $invoiceId): array
    {
        $st = $this->pdo->prepare("SELECT l.*, i.name item_name, u.name unit_name
                     FROM purchase_invoice_lines l
                     JOIN items i ON i.id = l.item_id
                     LEFT JOIN units u ON u.id = l.unit_id
                     WHERE l.invoice_id=?
                     ORDER BY l.line_no");
        $st->execute([$invoiceId]);
        return $st->fetchAll() ?: [];
    }

    public function fetchEditLines(int $invoiceId): array
    {
        $st = $this->pdo->prepare("SELECT l.*, i.name item_name FROM purchase_invoice_lines l LEFT JOIN items i ON i.id=l.item_id WHERE invoice_id=? ORDER BY line_no");
        $st->execute([$invoiceId]);
        return $st->fetchAll() ?: [];
    }

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

    public function fetchRetSummary(int $invoiceId): array
    {
        if (!table_exists($this->pdo, 'purchase_returns')) return ['count' => 0, 'sum' => 0.0];
        $st = $this->pdo->prepare("SELECT COUNT(*) c, COALESCE(SUM(total),0) s FROM purchase_returns WHERE source_invoice_id=? AND status='POSTED'");
        $st->execute([$invoiceId]);
        $r = $st->fetch() ?: [];
        return ['count' => (int)($r['c'] ?? 0), 'sum' => (float)($r['s'] ?? 0)];
    }

    public function fetchPaidSum(int $invoiceId, float $basePaid): float
    {
        $paid = $basePaid;
        if (table_exists($this->pdo, 'supplier_payment_allocations') && table_exists($this->pdo, 'supplier_payments')) {
            $st = $this->pdo->prepare("SELECT COALESCE(SUM(a.amount),0) s
                       FROM supplier_payment_allocations a
                       JOIN supplier_payments sp ON sp.id=a.payment_id
                       WHERE sp.status='POSTED' AND a.purchase_invoice_id=?");
            $st->execute([$invoiceId]);
            $pp = $st->fetch();
            $paid += (float)($pp['s'] ?? 0);
        }
        return $paid;
    }

    public function fetchItemsMap(array $ids): array
    {
        if (!$ids) return [];
        $in = implode(',', array_fill(0, count($ids), '?'));
        $st = $this->pdo->prepare("SELECT id, name, is_active FROM items WHERE id IN ($in)");
        $st->execute($ids);
        $map = [];
        foreach ($st->fetchAll() ?: [] as $r) $map[(int)$r['id']] = $r;
        return $map;
    }

    public function generateInvoiceNo(): string
    {
        $invNo = 'PI-' . date('Y') . '-' . str_pad((string)rand(1, 99999), 5, '0', STR_PAD_LEFT);
        for ($tries = 0; $tries < 8; $tries++) {
            $chk = $this->pdo->prepare('SELECT 1 FROM purchase_invoices WHERE inv_no=?');
            $chk->execute([$invNo]);
            if (!$chk->fetch()) break;
            $invNo = 'PI-' . date('Y') . '-' . str_pad((string)rand(1, 99999), 5, '0', STR_PAD_LEFT);
        }
        return $invNo;
    }

    public function create(array $data, int $userId): int
    {
        $this->pdo->prepare("INSERT INTO purchase_invoices
        (inv_no, supplier_id, party_id, warehouse_id, inv_date, status, notes, payment_mode, paid_amount, due_amount, cashbox_id, payment_note, discount_type, discount_value, discount_note, tax_enabled, tax_rate, tax_invoice_no, wht_enabled, wht_rate, wht_amount, wht_notice_no, extra_charges, charges_note, subtotal, discount_amount, tax_amount, total, created_by, posted_by, posted_at)
        VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)")
        ->execute([
          $data['inv_no'], null, $data['party_id'], $data['warehouse_id'], $data['inv_date'] . ' 00:00:00', $data['status'],
          $data['notes'], $data['payment_mode'], $data['paid_amount'], $data['due_amount'], $data['cashbox_id'], $data['payment_note'],
          $data['discount_type'], $data['discount_value'], $data['discount_note'],
          $data['tax_enabled'], $data['tax_rate'], $data['tax_invoice_no'],
          $data['wht_enabled'], $data['wht_rate'], $data['wht_amount'], $data['wht_notice_no'],
          $data['extra_charges'], $data['charges_note'],
          $data['subtotal'], $data['discount_amount'], $data['tax_amount'], $data['total'],
          $userId, $data['posted_by'], $data['posted_at'],
        ]);
        return (int)$this->pdo->lastInsertId();
    }

    public function update(int $id, array $data): void
    {
        $this->pdo->prepare("UPDATE purchase_invoices
        SET supplier_id=NULL, party_id=?, warehouse_id=?, inv_date=?, status=?, notes=?,
            payment_mode=?, paid_amount=?, due_amount=?, cashbox_id=?, payment_note=?,
            discount_type=?, discount_value=?, discount_note=?,
            tax_enabled=?, tax_rate=?, tax_invoice_no=?,
            wht_enabled=?, wht_rate=?, wht_amount=?, wht_notice_no=?,
            extra_charges=?, charges_note=?,
            subtotal=?, discount_amount=?, tax_amount=?, total=?,
            posted_by=?, posted_at=?
        WHERE id=?")
        ->execute([
          $data['party_id'], $data['warehouse_id'], $data['inv_date'] . ' 00:00:00', $data['status'], $data['notes'],
          $data['payment_mode'], $data['paid_amount'], $data['due_amount'], $data['cashbox_id'], $data['payment_note'],
          $data['discount_type'], $data['discount_value'], $data['discount_note'],
          $data['tax_enabled'], $data['tax_rate'], $data['tax_invoice_no'],
          $data['wht_enabled'], $data['wht_rate'], $data['wht_amount'], $data['wht_notice_no'],
          $data['extra_charges'], $data['charges_note'],
          $data['subtotal'], $data['discount_amount'], $data['tax_amount'], $data['total'],
          $data['posted_by'], $data['posted_at'],
          $id
        ]);
    }

    public function deleteDraftSideEffects(int $id): void
    {
        $this->pdo->prepare('DELETE FROM purchase_invoice_lines WHERE invoice_id=?')->execute([$id]);
        $this->pdo->prepare("DELETE FROM party_ledger WHERE doc_type IN ('PURCHASE_INV','PURCHASE_PAY') AND doc_id=?")->execute([$id]);
        if (table_exists($this->pdo, 'cashbox_transactions')) {
            $this->pdo->prepare("DELETE FROM cashbox_transactions WHERE ref_type='purchase_invoice' AND ref_id=?")->execute([$id]);
        }
        $this->pdo->prepare("DELETE FROM stock_moves WHERE ref_type='PURCHASE_INV' AND ref_id=?")->execute([$id]);
    }

    public function replaceLines(int $invoiceId, array $lines): void
    {
        $ins = $this->pdo->prepare("INSERT INTO purchase_invoice_lines (invoice_id, line_no, item_id, unit_id, qty, unit_price, line_total)
                          VALUES (?,?,?,?,?,?,?)");
        $lineNo = 1;
        foreach ($lines as $ln) {
            $ins->execute([$invoiceId, $lineNo++, $ln['item_id'], $ln['unit_id'], $ln['qty'], $ln['unit_price'], $ln['line_total']]);
        }
    }

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

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

    public function markCanceledDraft(int $id): void
    {
        $this->pdo->prepare("UPDATE purchase_invoices SET status='CANCELED' WHERE id=? AND status='DRAFT'")->execute([$id]);
    }
}
