<?php

declare(strict_types=1);

namespace App\Repositories;

use PDO;

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

    public function searchPage(string $from, string $to, string $q, string $status, int $warehouseId, int $partyId, int $page = 1, int $perPage = 25): array
    {
        $page = max(1, $page);
        $perPage = max(1, min(100, $perPage));
        $offset = ($page - 1) * $perPage;

        $where = ['si.invoice_date >= ? AND si.invoice_date < ?'];
        $params = [$from . ' 00:00:00', date('Y-m-d 00:00:00', strtotime($to . ' +1 day'))];

        if ($status !== '') {
            $where[] = 'COALESCE(si.status,\'POSTED\') = ?';
            $params[] = strtoupper($status);
        }
        if ($warehouseId > 0) {
            $where[] = 'si.warehouse_id = ?';
            $params[] = $warehouseId;
        }
        if ($partyId > 0) {
            $where[] = 'COALESCE(si.party_id, si.customer_id) = ?';
            $params[] = $partyId;
        }
        if ($q !== '') {
            $where[] = '(si.invoice_no LIKE ? OR c.name LIKE ? OR COALESCE(c.phone,\'\') LIKE ? OR COALESCE(c.code,\'\') LIKE ?)';
            $like = "%{$q}%";
            array_push($params, $like, $like, $like, $like);
        }

        $whereSql = implode(' AND ', $where);

        $countSql = "SELECT COUNT(*)
                     FROM sales_invoices si
                     LEFT JOIN parties c ON c.id = COALESCE(si.party_id, si.customer_id)
                     WHERE {$whereSql}";
        $countSt = $this->pdo->prepare($countSql);
        $countSt->execute($params);
        $totalRows = (int) ($countSt->fetchColumn() ?: 0);

        $sql = "SELECT si.id, si.invoice_no, si.invoice_date, si.warehouse_id,
                       COALESCE(si.grand_total, si.total, 0) grand_total,
                       COALESCE(si.paid_amount, 0) paid_amount,
                       COALESCE(si.due_amount, 0) due_amount,
                       COALESCE(si.status,'POSTED') status,
                       c.name customer_name,
                       COALESCE(c.code, CONCAT('P',LPAD(c.id,5,'0'))) customer_code,
                       w.name warehouse_name
                FROM sales_invoices si
                LEFT JOIN parties c ON c.id = COALESCE(si.party_id, si.customer_id)
                LEFT JOIN warehouses w ON w.id = si.warehouse_id
                WHERE {$whereSql}
                ORDER BY si.invoice_date DESC, si.id DESC
                LIMIT {$perPage} OFFSET {$offset}";
        $st = $this->pdo->prepare($sql);
        $st->execute($params);

        return [
            'rows' => $st->fetchAll() ?: [],
            'total_rows' => $totalRows,
        ];
    }

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



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

    public function deletePostingEffects(int $invoiceId): void
    {
        $this->pdo->prepare("DELETE FROM stock_moves WHERE ref_type='SALES_INV' AND ref_id=?")->execute([$invoiceId]);
        $this->pdo->prepare("DELETE FROM party_ledger WHERE doc_type IN ('SALES_INV','SALES_RCPT') AND doc_id=?")->execute([$invoiceId]);
        if (table_exists($this->pdo, 'cashbox_transactions')) {
            $this->pdo->prepare("DELETE FROM cashbox_transactions WHERE ref_type='sales_invoice' AND ref_id=?")->execute([$invoiceId]);
        }
    }

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

    public function fetchSalesReturnSummary(int $invoiceId): array
    {
        if (!table_exists($this->pdo, 'sales_returns')) {
            return ['count' => 0, 'total' => 0.0];
        }
        try {
            $st = $this->pdo->prepare("SELECT COUNT(*) c, COALESCE(SUM(total_amount),0) s FROM sales_returns WHERE sales_invoice_id=? AND status='POSTED'");
            $st->execute([$invoiceId]);
            $row = $st->fetch() ?: [];
            return ['count' => (int)($row['c'] ?? 0), 'total' => (float)($row['s'] ?? 0)];
        } catch (\Throwable) {
            return ['count' => 0, 'total' => 0.0];
        }
    }
    public function fetchLines(int $invoiceId): array
    {
        $st = $this->pdo->prepare("SELECT * FROM sales_items WHERE invoice_id=? ORDER BY id");
        $st->execute([$invoiceId]);
        return $st->fetchAll() ?: [];
    }

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

    public function fetchCustomerCreditMeta(int $partyId): array|false
    {
        $st = $this->pdo->prepare("SELECT credit_limit, name FROM parties WHERE id=? LIMIT 1");
        $st->execute([$partyId]);
        return $st->fetch();
    }

    public function fetchItemsStockMap(int $warehouseId, array $itemIds): array
    {
        if (!$itemIds) {
            return [];
        }
        $in = implode(',', array_fill(0, count($itemIds), '?'));
        $st = $this->pdo->prepare("SELECT i.id, i.name, i.cost, COALESCE(sb.qty,0) balance
                      FROM items i
                      LEFT JOIN stock_balances sb ON sb.item_id=i.id AND sb.warehouse_id=?
                      WHERE i.id IN ($in) AND i.is_active=1");
        $st->execute(array_merge([$warehouseId], $itemIds));
        $map = [];
        foreach ($st->fetchAll() ?: [] as $r) {
            $map[(int) $r['id']] = $r;
        }
        return $map;
    }

    public function generateInvoiceNo(): string
    {
        $invoiceNo = 'SI-' . date('Y') . '-' . str_pad((string) rand(1, 99999), 5, '0', STR_PAD_LEFT);
        $tries = 0;
        while ($tries < 10) {
            $chk = $this->pdo->prepare("SELECT 1 FROM sales_invoices WHERE invoice_no=? LIMIT 1");
            $chk->execute([$invoiceNo]);
            if (!$chk->fetch()) {
                break;
            }
            $invoiceNo = 'SI-' . date('Y') . '-' . str_pad((string) rand(1, 99999), 5, '0', STR_PAD_LEFT);
            $tries++;
        }
        return $invoiceNo;
    }

    public function create(array $data): int
    {
        $this->pdo->prepare("INSERT INTO sales_invoices
        (invoice_no, invoice_date, warehouse_id, customer_id, party_id, status, total, subtotal, discount_type, discount_value, discount_note, discount_amount, net_after_discount, vat_enabled, vat_rate, vat_amount, grand_total, notes, payment_mode, paid_amount, due_amount, cashbox_id, collection_note, user_id, posted_by, posted_at)
        VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)")
        ->execute([
            $data['invoice_no'],
            $data['invoice_date'],
            $data['warehouse_id'],
            null,
            $data['party_id'],
            $data['status'],
            $data['grand_total'],
            $data['subtotal'],
            $data['discount_type'],
            $data['discount_value'],
            $data['discount_note'],
            $data['discount_amount'],
            $data['net_after_discount'],
            $data['vat_enabled'],
            $data['vat_rate'],
            $data['vat_amount'],
            $data['grand_total'],
            $data['notes'],
            $data['payment_mode'],
            $data['paid_amount'],
            $data['due_amount'],
            $data['cashbox_id'],
            $data['collection_note'],
            $data['user_id'],
            $data['posted_by'],
            $data['posted_at'],
        ]);
        return (int) $this->pdo->lastInsertId();
    }

    public function update(int $id, array $data): void
    {
        $this->pdo->prepare("UPDATE sales_invoices SET
          invoice_date=?, warehouse_id=?, customer_id=?, party_id=?, status=?, total=?, subtotal=?, discount_type=?, discount_value=?, discount_note=?, discount_amount=?, net_after_discount=?, vat_enabled=?, vat_rate=?, vat_amount=?, grand_total=?, notes=?, payment_mode=?, paid_amount=?, due_amount=?, cashbox_id=?, collection_note=?, posted_by=?, posted_at=?
        WHERE id=?")
        ->execute([
            $data['invoice_date'],
            $data['warehouse_id'],
            null,
            $data['party_id'],
            $data['status'],
            $data['grand_total'],
            $data['subtotal'],
            $data['discount_type'],
            $data['discount_value'],
            $data['discount_note'],
            $data['discount_amount'],
            $data['net_after_discount'],
            $data['vat_enabled'],
            $data['vat_rate'],
            $data['vat_amount'],
            $data['grand_total'],
            $data['notes'],
            $data['payment_mode'],
            $data['paid_amount'],
            $data['due_amount'],
            $data['cashbox_id'],
            $data['collection_note'],
            $data['posted_by'],
            $data['posted_at'],
            $id,
        ]);
    }

    public function replaceLines(int $invoiceId, array $lines, array $itemsMap): void
    {
        $this->pdo->prepare("DELETE FROM sales_items WHERE invoice_id=?")->execute([$invoiceId]);
        $ins = $this->pdo->prepare("INSERT INTO sales_items (invoice_id, item_id, item_name, qty, price, cost, line_total) VALUES (?,?,?,?,?,?,?)");
        foreach ($lines as $ln) {
            $iid = (int) $ln['item_id'];
            $it = $itemsMap[$iid] ?? null;
            $name = $it ? (string) $it['name'] : null;
            $cost = $it ? (float) $it['cost'] : 0.0;
            $ins->execute([$invoiceId, $iid, $name, (float) $ln['qty'], (float) $ln['price'], $cost, (float) $ln['line_total']]);
        }
    }



    public function deleteDraft(int $invoiceId): bool
    {
        $this->pdo->prepare("DELETE FROM sales_items WHERE invoice_id=?")->execute([$invoiceId]);
        $st = $this->pdo->prepare("DELETE FROM sales_invoices WHERE id=? AND COALESCE(status,'POSTED')='DRAFT'");
        $st->execute([$invoiceId]);
        return $st->rowCount() > 0;
    }

    public function fetchInvoiceNo(int $id): string
    {
        $st = $this->pdo->prepare("SELECT invoice_no FROM sales_invoices WHERE id=?");
        $st->execute([$id]);
        return (string) ($st->fetchColumn() ?: ('SI#' . $id));
    }
}
