<?php

declare(strict_types=1);

namespace App\Repositories;

use PDO;

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

    public function search(string $q, string $status, string $dateFrom, string $dateTo): array
    {
        $where=[]; $params=[];
        if ($q !== '') {
            $where[] = "(pr.ret_no LIKE ? OR s.name LIKE ? OR pi.inv_no LIKE ?)";
            $like = '%' . $q . '%'; array_push($params, $like, $like, $like);
        }
        if ($status !== '' && in_array(strtoupper($status), ['DRAFT','POSTED','CANCELED'], true)) {
            $where[] = 'pr.status = ?'; $params[] = strtoupper($status);
        }
        if ($dateFrom !== '') { $where[] = 'pr.ret_date >= ?'; $params[] = $dateFrom . ' 00:00:00'; }
        if ($dateTo !== '') { $where[] = 'pr.ret_date < ?'; $params[] = date('Y-m-d 00:00:00', strtotime($dateTo . ' +1 day')); }
        $whereSql = $where ? ('WHERE ' . implode(' AND ', $where)) : '';
        $st = $this->pdo->prepare("SELECT pr.*, s.name supplier_name, COALESCE(s.code, CONCAT('P',LPAD(s.id,5,'0'))) supplier_code,
                            w.name warehouse_name, pi.inv_no source_inv_no
                     FROM purchase_returns pr
                     JOIN parties s ON s.id=COALESCE(pr.party_id, pr.supplier_id)
                     JOIN warehouses w ON w.id=pr.warehouse_id
                     JOIN purchase_invoices pi ON pi.id=pr.source_invoice_id
                     $whereSql
                     ORDER BY pr.id DESC
                     LIMIT 300");
        $st->execute($params);
        return $st->fetchAll() ?: [];
    }

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

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

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

    public function fetchSourceInvoice(int $invoiceId): array|false
    {
        $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
                     FROM purchase_invoices pi
                     JOIN parties s ON s.id=COALESCE(pi.party_id, pi.supplier_id)
                     JOIN warehouses w ON w.id=pi.warehouse_id
                     WHERE pi.id=? LIMIT 1");
        $st->execute([$invoiceId]);
        return $st->fetch();
    }

    public function fetchInvoiceLinesForReturn(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 fetchPostedReturnedQtyMap(int $invoiceId, ?int $excludeId = null): array
    {
        $sql = "SELECT item_id, unit_id, COALESCE(SUM(qty),0) qty FROM purchase_return_lines l JOIN purchase_returns r ON r.id=l.return_id WHERE r.source_invoice_id=? AND r.status='POSTED'";
        $params = [$invoiceId];
        if ($excludeId) { $sql .= ' AND r.id<>?'; $params[] = $excludeId; }
        $sql .= ' GROUP BY item_id, unit_id';
        $st = $this->pdo->prepare($sql);
        $st->execute($params);
        $map = [];
        foreach ($st->fetchAll() ?: [] as $r) {
            $map[((int)$r['item_id']) . ':' . ((int)($r['unit_id'] ?? 0))] = (float)$r['qty'];
        }
        return $map;
    }

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

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

    public function createDraft(array $data, int $userId): int
    {
        $this->pdo->prepare("INSERT INTO purchase_returns (ret_no, supplier_id, party_id, warehouse_id, ret_date, status, source_invoice_id, notes, subtotal, total, created_by)
        VALUES (?,?,?,?,?, 'DRAFT', ?, ?, ?, ?, ?)")
        ->execute([$data['ret_no'], null, $data['party_id'], $data['warehouse_id'], $data['ret_date'] . ' 00:00:00', $data['source_invoice_id'], $data['notes'], $data['subtotal'], $data['total'], $userId]);
        return (int)$this->pdo->lastInsertId();
    }

    public function updateDraft(int $id, array $data): void
    {
        $this->pdo->prepare('UPDATE purchase_returns SET ret_date=?, notes=?, subtotal=?, total=? WHERE id=?')
            ->execute([$data['ret_date'] . ' 00:00:00', $data['notes'], $data['subtotal'], $data['total'], $id]);
    }

    public function replaceLines(int $returnId, array $lines): void
    {
        $this->pdo->prepare('DELETE FROM purchase_return_lines WHERE return_id=?')->execute([$returnId]);
        $ins = $this->pdo->prepare('INSERT INTO purchase_return_lines (return_id, line_no, item_id, unit_id, qty, unit_price, line_total) VALUES (?,?,?,?,?,?,?)');
        $lineNo = 1;
        foreach ($lines as $ln) {
            $ins->execute([$returnId, $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_returns WHERE id=? FOR UPDATE');
        $st->execute([$id]);
        return $st->fetch();
    }

    public function deletePostingEffects(int $id): void
    {
        $this->pdo->prepare("DELETE FROM stock_moves WHERE ref_type='PURCHASE_RET' AND ref_id=?")->execute([$id]);
        $this->pdo->prepare("DELETE FROM party_ledger WHERE doc_type='PURCHASE_RET' AND doc_id=?")->execute([$id]);
    }

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

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