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

final class ReportRepository extends Repository {
    public function dashboardMetrics(): array {
        return [
            'active_rentals' => (int)($this->one("SELECT COUNT(*) c FROM invoice_items ii JOIN invoices inv ON inv.id=ii.invoice_id WHERE ii.line_type='rental' AND ii.return_status='pending' AND inv.invoice_status NOT IN ('cancelled','closed')")['c'] ?? 0),
            'today_revenue' => (float)($this->one("SELECT COALESCE(SUM(amount),0) t FROM payments WHERE DATE(created_at)=CURDATE()")['t'] ?? 0),
            'open_invoices' => (int)($this->one("SELECT COUNT(*) c FROM invoices WHERE invoice_status NOT IN ('closed','cancelled')")['c'] ?? 0),
            'queue_waiting' => (int)($this->one("SELECT COUNT(*) c FROM queue_tickets qt JOIN queue_sessions qs ON qs.id=qt.queue_session_id WHERE qs.status='open' AND qt.ticket_status='pending'")['c'] ?? 0),
        ];
    }

    public function topItems(string $from, string $to): array {
        return $this->all("SELECT ii.item_id, it.item_code, it.item_name, c.name_ar category_name, COUNT(ii.id) rental_count, COALESCE(SUM(ii.final_price),0) total_value
            FROM invoice_items ii
            JOIN invoices inv ON inv.id=ii.invoice_id
            JOIN items it ON it.id=ii.item_id
            JOIN categories c ON c.id=it.category_id
            WHERE ii.line_type='rental' AND inv.delivery_date <= ? AND inv.return_date >= ?
            GROUP BY ii.item_id
            ORDER BY rental_count DESC, total_value DESC
            LIMIT 20", [$to, $from]);
    }

    public function futureBookings(): array {
        return $this->all("SELECT inv.invoice_no, inv.delivery_date, inv.return_date, c.full_name customer_name,
            COUNT(ii.id) items_count
            FROM invoices inv
            JOIN customers c ON c.id=inv.customer_id
            LEFT JOIN invoice_items ii ON ii.invoice_id=inv.id
            WHERE inv.delivery_date >= CURDATE() AND inv.invoice_status NOT IN ('cancelled')
            GROUP BY inv.id
            ORDER BY inv.delivery_date ASC
            LIMIT 20");
    }

    public function occupiedItems(): array {
        return $this->all("SELECT ii.item_id, it.item_code, it.item_name, c.name_ar category_name, inv.invoice_no, inv.return_date, cust.full_name customer_name
            FROM invoice_items ii
            JOIN items it ON it.id=ii.item_id
            JOIN categories c ON c.id=it.category_id
            JOIN invoices inv ON inv.id=ii.invoice_id
            JOIN customers cust ON cust.id=inv.customer_id
            WHERE ii.line_type='rental' AND ii.return_status='pending' AND inv.invoice_status NOT IN ('cancelled','closed')
            ORDER BY inv.return_date ASC, it.item_code ASC
            LIMIT 50");
    }

    public function invoicesList(string $from, string $to): array {
        return $this->all("SELECT inv.*, c.full_name customer_name
            FROM invoices inv
            JOIN customers c ON c.id=inv.customer_id
            WHERE inv.delivery_date BETWEEN ? AND ?
            ORDER BY inv.id DESC
            LIMIT 100", [$from, $to]);
    }

    public function queueSummary(string $from, string $to): array {
        return $this->one("SELECT 
                COUNT(*) total_tickets,
                SUM(CASE WHEN ticket_status='done' THEN 1 ELSE 0 END) done_count,
                SUM(CASE WHEN ticket_status='pending' THEN 1 ELSE 0 END) pending_count,
                SUM(CASE WHEN ticket_status='skipped' THEN 1 ELSE 0 END) skipped_count,
                SUM(CASE WHEN ticket_status='serving' THEN 1 ELSE 0 END) serving_count
            FROM queue_tickets
            WHERE DATE(created_at) BETWEEN ? AND ?", [$from, $to]) ?: [];
    }

    public function queueDailyBreakdown(string $from, string $to): array {
        return $this->all("SELECT DATE(created_at) report_date,
                COUNT(*) total_tickets,
                SUM(CASE WHEN ticket_status='done' THEN 1 ELSE 0 END) done_count,
                SUM(CASE WHEN ticket_status='skipped' THEN 1 ELSE 0 END) skipped_count
            FROM queue_tickets
            WHERE DATE(created_at) BETWEEN ? AND ?
            GROUP BY DATE(created_at)
            ORDER BY DATE(created_at) DESC", [$from, $to]);
    }
}
