<?php
declare(strict_types=1);
namespace App\Repositories;
use App\Core\Repository;

final class ReturnRepository extends Repository {
    public function pendingInvoices(string $q=''): array {
        $params = [];
        $where = "WHERE inv.invoice_status IN ('confirmed','awaiting_return','awaiting_settlement')";
        if ($q !== '') {
            $where .= " AND (inv.invoice_no LIKE ? OR c.full_name LIKE ? OR c.phone LIKE ? )";
            $like = '%' . $q . '%';
            $params = [$like, $like, $like];
        }
        return $this->all("SELECT inv.*, c.full_name customer_name, c.phone customer_phone,
            (SELECT COUNT(*) FROM invoice_items x WHERE x.invoice_id=inv.id AND x.line_type='rental' AND x.return_status='pending') pending_returns
            FROM invoices inv
            JOIN customers c ON c.id=inv.customer_id
            $where
            ORDER BY inv.id DESC", $params);
    }

    public function invoiceWithLines(int $invoiceId): ?array {
        return $this->one("SELECT inv.*, c.full_name customer_name, c.phone customer_phone, u.full_name created_by_name
            FROM invoices inv JOIN customers c ON c.id=inv.customer_id JOIN users u ON u.id=inv.created_by WHERE inv.id=?", [$invoiceId]);
    }

    public function lines(int $invoiceId): array {
        return $this->all("SELECT ii.*, it.item_code, it.item_name, it.item_status, c.name_ar category_name,
            (SELECT image_path FROM item_images im WHERE im.item_id=it.id ORDER BY is_primary DESC, sort_order ASC, id ASC LIMIT 1) image_path
            FROM invoice_items ii
            JOIN items it ON it.id=ii.item_id
            JOIN categories c ON c.id=it.category_id
            WHERE ii.invoice_id=?
            ORDER BY ii.id ASC", [$invoiceId]);
    }

    public function processReturn(int $invoiceId, array $invoiceItemIds, array $conditions, int $userId, array $payment = []): void {
        $this->db->beginTransaction();
        try {
            foreach ($invoiceItemIds as $ii) {
                $invoiceItemId = (int)$ii;
                $line = $this->one("SELECT ii.*, it.id item_id FROM invoice_items ii JOIN items it ON it.id=ii.item_id WHERE ii.id=? AND ii.invoice_id=?", [$invoiceItemId, $invoiceId]);
                if (!$line || $line['line_type'] !== 'rental' || $line['return_status'] !== 'pending') {
                    continue;
                }

                $cond = (string)($conditions[$invoiceItemId] ?? 'good');
                $this->exec("UPDATE invoice_items SET return_status='returned', item_condition_on_return=?, returned_at=NOW() WHERE id=?", [$cond, $invoiceItemId]);

                $itemStatus = match ($cond) {
                    'damaged' => 'maintenance',
                    'missing' => 'missing',
                    'needs_maintenance' => 'maintenance',
                    default => 'available',
                };

                $this->exec("UPDATE items SET item_status=?, updated_at=NOW() WHERE id=?", [$itemStatus, (int)$line['item_id']]);
                $this->insert("INSERT INTO return_logs(invoice_id,invoice_item_id,return_condition,returned_by,created_at) VALUES(?,?,?,?,NOW())", [$invoiceId, $invoiceItemId, $cond, $userId]);
                $this->insert("INSERT INTO item_status_logs(item_id,old_status,new_status,note,created_at) VALUES(?,?,?,?,NOW())", [(int)$line['item_id'], (string)($line['item_status'] ?? 'rented'), $itemStatus, 'Return processed from invoice #' . $invoiceId]);
            }

            $amount = (float)($payment['amount'] ?? 0);
            if ($amount > 0) {
                $method = trim((string)($payment['payment_method'] ?? 'cash')) ?: 'cash';
                $reference = trim((string)($payment['transfer_reference'] ?? ''));
                $notes = trim((string)($payment['notes'] ?? 'Settlement from return screen'));

                $this->insert(
                    "INSERT INTO payments(invoice_id,payment_method,amount,transfer_reference,notes,created_at) VALUES(?,?,?,?,?,NOW())",
                    [$invoiceId, $method, $amount, $reference ?: null, $notes ?: null]
                );
            }

            (new InvoiceRepository())->recalc($invoiceId);
            $this->db->commit();
        } catch (\Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }
}
