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

final class InvoiceRepository extends Repository {
    public function nextNumber(): string {
        $mx = $this->one("SELECT MAX(id) mx FROM invoices")['mx'] ?? 0;
        return 'INV-' . date('Y') . '-' . str_pad((string)($mx + 1), 5, '0', STR_PAD_LEFT);
    }

    public function recent(): array {
        return $this->all("SELECT i.*, c.full_name customer_name FROM invoices i JOIN customers c ON c.id=i.customer_id ORDER BY i.id DESC LIMIT 15");
    }

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

    public function lines(int $invoiceId): array {
        return $this->all("SELECT ii.*, it.item_code, it.item_name, c.name_ar category_name
            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 create(array $invoice, array $lines, array $payments): int {
        $this->db->beginTransaction();
        try {
            $invoiceId = $this->insert("INSERT INTO invoices(invoice_no,customer_id,invoice_type,delivery_date,return_date,invoice_status,discount_amount,grand_total,paid_total,balance_due,created_by,signature_name,signature_data,notes,created_at,updated_at)
                VALUES(?,?,?,?,?,'confirmed',0,0,0,0,?,?,?,?,NOW(),NOW())", [
                $invoice['invoice_no'], $invoice['customer_id'], $invoice['invoice_type'], $invoice['delivery_date'], $invoice['return_date'],
                $invoice['created_by'], $invoice['signature_name'] ?: null, $invoice['signature_data'] ?: null, $invoice['notes'] ?: null
            ]);
            foreach ($lines as $line) {
                $this->insert("INSERT INTO invoice_items(invoice_id,item_id,line_type,unit_price,days_count,quantity,final_price,return_status,created_at)
                    VALUES(?,?,?,?,?,?,?,? ,NOW())", [
                    $invoiceId, $line['item_id'], $line['line_type'], $line['unit_price'], $line['days_count'], $line['quantity'], $line['final_price'],
                    $line['line_type'] === 'sale' ? 'returned' : 'pending'
                ]);
            }
            foreach ($payments as $payment) {
                if ((float)$payment['amount'] <= 0) continue;
                $this->insert("INSERT INTO payments(invoice_id,payment_method,amount,transfer_reference,notes,created_at) VALUES(?,?,?,?,?,NOW())", [
                    $invoiceId, $payment['payment_method'], $payment['amount'], $payment['transfer_reference'] ?: null, $payment['notes'] ?: null
                ]);
            }
            $this->recalc($invoiceId);
            $this->db->commit();
            return $invoiceId;
        } catch (\Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    public function recalc(int $invoiceId): void {
        $sum = (float)($this->one("SELECT COALESCE(SUM(final_price),0) total FROM invoice_items WHERE invoice_id=?", [$invoiceId])['total'] ?? 0);
        $discount = (float)($this->one("SELECT discount_amount FROM invoices WHERE id=?", [$invoiceId])['discount_amount'] ?? 0);
        $sumAfterDiscount = max(0, $sum - $discount);
        $paid = (float)($this->one("SELECT COALESCE(SUM(amount),0) total FROM payments WHERE invoice_id=?", [$invoiceId])['total'] ?? 0);
        $pending = (int)($this->one("SELECT COALESCE(SUM(CASE WHEN line_type='rental' AND return_status='pending' THEN 1 ELSE 0 END),0) c FROM invoice_items WHERE invoice_id=?", [$invoiceId])['c'] ?? 0);
        $rental = (int)($this->one("SELECT COALESCE(SUM(CASE WHEN line_type='rental' THEN 1 ELSE 0 END),0) c FROM invoice_items WHERE invoice_id=?", [$invoiceId])['c'] ?? 0);
        $balance = max(0, $sumAfterDiscount - $paid);
        $status = 'confirmed';
        if ($rental === 0 && $balance <= 0) $status = 'closed';
        elseif ($rental > 0 && $pending === 0 && $balance <= 0) $status = 'closed';
        elseif ($rental > 0 && $pending === 0 && $balance > 0) $status = 'awaiting_settlement';
        elseif ($rental > 0 && $pending > 0 && $balance <= 0) $status = 'awaiting_return';
        $this->exec("UPDATE invoices SET grand_total=?, paid_total=?, balance_due=?, invoice_status=?, updated_at=NOW() WHERE id=?", [$sumAfterDiscount, $paid, $balance, $status, $invoiceId]);
    }
}
