<?php

declare(strict_types=1);

namespace App\Models;

use Core\Database;

final class ReportModel
{
    private Database $db;

    public function __construct()
    {
        $this->db = Database::getInstance();
    }

    // ── Summary cards ─────────────────────────────────────────────────────────

    public function summary(string $from, string $to): array
    {
        return $this->db->fetchOne(
            "SELECT
                COUNT(*)                                              AS total_orders,
                COALESCE(SUM(total_amount), 0)                        AS total_revenue,
                COALESCE(SUM(CASE WHEN status='delivered' THEN total_amount END), 0) AS delivered_revenue,
                COUNT(CASE WHEN status='delivered'  THEN 1 END)       AS delivered_orders,
                COUNT(CASE WHEN status='cancelled'  THEN 1 END)       AS cancelled_orders,
                COALESCE(AVG(CASE WHEN status='delivered' THEN total_amount END), 0) AS avg_order_value
               FROM orders
              WHERE DATE(placed_at) BETWEEN ? AND ?",
            [$from, $to]
        ) ?? [];
    }

    // ── Sales by day (for chart) ──────────────────────────────────────────────

    public function dailySales(string $from, string $to): array
    {
        return $this->db->fetchAll(
            "SELECT DATE(placed_at) AS sale_date,
                    COUNT(*)        AS orders,
                    COALESCE(SUM(total_amount), 0) AS revenue
               FROM orders
              WHERE DATE(placed_at) BETWEEN ? AND ?
                AND status NOT IN ('cancelled')
              GROUP BY DATE(placed_at)
              ORDER BY DATE(placed_at) ASC",
            [$from, $to]
        );
    }

    // ── Item-wise sales ───────────────────────────────────────────────────────

    public function itemSales(string $from, string $to, string $search = ''): array
    {
        $params = [$from, $to];
        $extra  = '';
        if ($search !== '') {
            $extra    = ' AND oi.item_name LIKE ?';
            $params[] = '%' . $search . '%';
        }
        return $this->db->fetchAll(
            "SELECT oi.item_name,
                    SUM(oi.quantity)   AS total_qty,
                    SUM(oi.line_total) AS total_revenue,
                    COUNT(DISTINCT oi.order_id) AS order_count
               FROM order_items oi
               JOIN orders o ON o.id = oi.order_id
              WHERE DATE(o.placed_at) BETWEEN ? AND ?
                AND o.status NOT IN ('cancelled')
                {$extra}
              GROUP BY oi.item_name
              ORDER BY total_revenue DESC",
            $params
        );
    }

    // ── Invoice / order-wise report ───────────────────────────────────────────

    public function invoiceReport(string $from, string $to, string $status = '', string $search = ''): array
    {
        $where  = ['DATE(o.placed_at) BETWEEN ? AND ?'];
        $params = [$from, $to];

        if ($status !== '') {
            $where[]  = 'o.status = ?';
            $params[] = $status;
        }
        if ($search !== '') {
            $where[]  = '(o.order_number LIKE ? OR u.name LIKE ?)';
            $params[] = '%' . $search . '%';
            $params[] = '%' . $search . '%';
        }

        return $this->db->fetchAll(
            "SELECT o.id, o.order_number, o.status, o.payment_method, o.payment_status,
                    o.subtotal, o.discount_amount, o.delivery_fee, o.tax_amount, o.total_amount,
                    o.placed_at,
                    u.name  AS customer_name,
                    u.phone AS customer_phone,
                    inv.invoice_number
               FROM orders o
               JOIN customers c  ON c.id = o.customer_id
               JOIN users u      ON u.id = c.user_id
               LEFT JOIN invoices inv ON inv.order_id = o.id
              WHERE " . implode(' AND ', $where) . "
              ORDER BY o.placed_at DESC",
            $params
        );
    }

    // ── Customer history ──────────────────────────────────────────────────────

    public function customerSummary(string $from, string $to, string $search = ''): array
    {
        $params = [$from, $to];
        $extra  = '';
        if ($search !== '') {
            $extra    = ' AND (u.name LIKE ? OR u.phone LIKE ? OR u.email LIKE ?)';
            $params[] = '%' . $search . '%';
            $params[] = '%' . $search . '%';
            $params[] = '%' . $search . '%';
        }
        return $this->db->fetchAll(
            "SELECT u.id AS user_id, u.name, u.phone, u.email,
                    COUNT(o.id)              AS total_orders,
                    COALESCE(SUM(CASE WHEN o.status NOT IN ('cancelled') THEN o.total_amount END), 0) AS total_spent,
                    COALESCE(MAX(o.placed_at), NULL) AS last_order_at,
                    COUNT(CASE WHEN o.status='cancelled' THEN 1 END) AS cancelled_orders
               FROM customers c
               JOIN users u  ON u.id = c.user_id
               LEFT JOIN orders o ON o.customer_id = c.id AND DATE(o.placed_at) BETWEEN ? AND ?
              WHERE 1=1 {$extra}
              GROUP BY u.id, u.name, u.phone, u.email
              ORDER BY total_spent DESC",
            $params
        );
    }

    public function customerOrders(int $userId): array
    {
        return $this->db->fetchAll(
            "SELECT o.*
               FROM orders o
               JOIN customers c ON c.id = o.customer_id
              WHERE c.user_id = ?
              ORDER BY o.placed_at DESC",
            [$userId]
        );
    }

    // ── Top items (for dashboard mini-list) ───────────────────────────────────

    public function topItems(int $limit = 5): array
    {
        return $this->db->fetchAll(
            "SELECT oi.item_name, SUM(oi.quantity) AS total_qty
               FROM order_items oi
               JOIN orders o ON o.id = oi.order_id
              WHERE o.status NOT IN ('cancelled')
                AND o.placed_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
              GROUP BY oi.item_name
              ORDER BY total_qty DESC
              LIMIT ?",
            [$limit]
        );
    }
}
