<?php

declare(strict_types=1);

namespace App\Repositories;

use PDO;

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

    public function search(string $q, string $status, string $dateFrom, string $dateTo): array
    {
        $where = [];
        $params = [];
        if ($q !== '') {
            $where[] = "(sr.return_no LIKE ? OR p.name LIKE ? OR si.invoice_no LIKE ?)";
            $like = '%' . $q . '%';
            $params[] = $like; $params[] = $like; $params[] = $like;
        }
        if ($status !== '' && in_array($status, ['DRAFT','POSTED','CANCELED'], true)) {
            $where[] = 'sr.status = ?';
            $params[] = $status;
        }
        if ($dateFrom !== '') { $where[] = 'sr.return_date >= ?'; $params[] = $dateFrom . ' 00:00:00'; }
        if ($dateTo !== '') { $where[] = 'sr.return_date < ?'; $params[] = date('Y-m-d 00:00:00', strtotime($dateTo . ' +1 day')); }
        $whereSql = $where ? ('WHERE ' . implode(' AND ', $where)) : '';

        $st = $this->pdo->prepare("SELECT sr.*, p.name customer_name, w.name warehouse_name, si.invoice_no source_inv_no
                     FROM sales_returns sr
                     JOIN parties p ON p.id = sr.party_id
                     JOIN warehouses w ON w.id = sr.warehouse_id
                     JOIN sales_invoices si ON si.id = sr.sales_invoice_id
                     $whereSql
                     ORDER BY sr.id DESC
                     LIMIT 300");
        $st->execute($params);
        return $st->fetchAll() ?: [];
    }

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

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

    public function fetchReturnItems(int $returnId): array
    {
        $st = $this->pdo->prepare("SELECT sri.*, s.item_name
                     FROM sales_return_items sri
                     LEFT JOIN sales_items s ON s.id = sri.sales_invoice_item_id
                     WHERE sri.sales_return_id=?
                     ORDER BY sri.line_no");
        $st->execute([$returnId]);
        return $st->fetchAll() ?: [];
    }

    public function fetchDraftItems(int $returnId): array
    {
        $st = $this->pdo->prepare('SELECT sri.* FROM sales_return_items sri WHERE sri.sales_return_id=? ORDER BY sri.line_no');
        $st->execute([$returnId]);
        return $st->fetchAll() ?: [];
    }

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

    public function fetchInvoiceLines(int $invoiceId): array
    {
        $st = $this->pdo->prepare('SELECT id, item_id, item_name, qty, price, line_total FROM sales_items WHERE invoice_id=? ORDER BY id');
        $st->execute([$invoiceId]);
        return $st->fetchAll() ?: [];
    }

    public function fetchPostedReturnedQtyMap(int $invoiceId, ?int $excludeReturnId = null): array
    {
        $sql = "SELECT sri.sales_invoice_item_id, COALESCE(SUM(sri.qty),0) qty
                     FROM sales_return_items sri
                     JOIN sales_returns sr ON sr.id = sri.sales_return_id
                     WHERE sr.sales_invoice_id=? AND sr.status='POSTED'";
        $params = [$invoiceId];
        if ($excludeReturnId) {
            $sql .= ' AND sr.id<>?';
            $params[] = $excludeReturnId;
        }
        $sql .= ' GROUP BY sri.sales_invoice_item_id';
        $st = $this->pdo->prepare($sql);
        $st->execute($params);
        $map = [];
        foreach ($st->fetchAll() ?: [] as $r) {
            $map[(int)$r['sales_invoice_item_id']] = (float)$r['qty'];
        }
        return $map;
    }

    public function fetchCashboxes(): array
    {
        if (!table_exists($this->pdo, 'cashboxes')) {
            return [];
        }
        return $this->pdo->query("SELECT id, name FROM cashboxes WHERE COALESCE(is_active,1)=1 ORDER BY name")->fetchAll() ?: [];
    }

    public function generateReturnNo(): string
    {
        $returnNo = 'SR-' . 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 sales_returns WHERE return_no=?');
            $chk->execute([$returnNo]);
            if (!$chk->fetch()) {
                break;
            }
            $returnNo = 'SR-' . date('Y') . '-' . str_pad((string) rand(1, 99999), 5, '0', STR_PAD_LEFT);
        }
        return $returnNo;
    }

    public function createDraft(array $data, int $currentUserId): int
    {
        $this->pdo->prepare("INSERT INTO sales_returns (return_no, return_date, sales_invoice_id, customer_id, party_id, warehouse_id, status, settlement_type, cashbox_id, subtotal, total_amount, reason, notes, created_by)
                       VALUES (?,?,?,?,?,?, 'DRAFT', ?, ?, ?, ?, ?, ?, ?)")
            ->execute([
                $data['return_no'],
                $data['return_date'],
                $data['sales_invoice_id'],
                null,
                $data['party_id'],
                $data['warehouse_id'],
                $data['settlement_type'],
                $data['cashbox_id'],
                $data['subtotal'],
                $data['total_amount'],
                $data['reason'],
                $data['notes'],
                $currentUserId,
            ]);
        return (int)$this->pdo->lastInsertId();
    }

    public function updateDraft(int $returnId, array $data): void
    {
        $this->pdo->prepare("UPDATE sales_returns SET return_date=?, settlement_type=?, cashbox_id=?, subtotal=?, total_amount=?, reason=?, notes=? WHERE id=?")
            ->execute([
                $data['return_date'],
                $data['settlement_type'],
                $data['cashbox_id'],
                $data['subtotal'],
                $data['total_amount'],
                $data['reason'],
                $data['notes'],
                $returnId,
            ]);
    }

    public function replaceItems(int $returnId, array $lines): void
    {
        $this->pdo->prepare('DELETE FROM sales_return_items WHERE sales_return_id=?')->execute([$returnId]);
        $ins = $this->pdo->prepare("INSERT INTO sales_return_items (sales_return_id, sales_invoice_item_id, line_no, item_id, qty, unit_price, line_total)
                            VALUES (?,?,?,?,?,?,?)");
        $lineNo = 1;
        foreach ($lines as $ln) {
            $ins->execute([$returnId, $ln['sales_invoice_item_id'], $lineNo++, $ln['item_id'], $ln['qty'], $ln['unit_price'], $ln['line_total']]);
        }
    }

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

    public function sourceLineQty(int $salesInvoiceItemId): float
    {
        $st = $this->pdo->prepare('SELECT qty FROM sales_items WHERE id=? LIMIT 1');
        $st->execute([$salesInvoiceItemId]);
        return (float)($st->fetchColumn() ?: 0);
    }

    public function fetchPostingLines(int $returnId): array
    {
        $st = $this->pdo->prepare("SELECT sri.*, si.item_name, COALESCE(si.cost,0) item_cost
                           FROM sales_return_items sri
                           LEFT JOIN sales_items si ON si.id = sri.sales_invoice_item_id
                           WHERE sri.sales_return_id=?
                           ORDER BY sri.line_no");
        $st->execute([$returnId]);
        return $st->fetchAll() ?: [];
    }

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

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

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