<?php

declare(strict_types=1);

namespace App\Models;

use Core\Database;

final class BiModel
{
    private Database $db;

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

    // ── Shared summary ────────────────────────────────────────────────────────

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

    public function summaryPrev(string $from, string $to): array
    {
        $days   = (int) (new \DateTime($to))->diff(new \DateTime($from))->days + 1;
        $pFrom  = date('Y-m-d', strtotime($from . " -{$days} days"));
        $pTo    = date('Y-m-d', strtotime($from . ' -1 day'));
        return $this->summary($pFrom, $pTo);
    }

    // ── Overview ──────────────────────────────────────────────────────────────

    public function dailyTrend(string $from, string $to): array
    {
        return $this->db->fetchAll(
            "SELECT DATE(placed_at)                                        AS d,
                    COUNT(*)                                               AS orders,
                    COALESCE(SUM(CASE WHEN status='delivered' THEN total_amount END),0) AS revenue
               FROM orders
              WHERE DATE(placed_at) BETWEEN ? AND ?
              GROUP BY DATE(placed_at) ORDER BY d ASC",
            [$from, $to]
        );
    }

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

    public function orderTypeSplit(string $from, string $to): array
    {
        return $this->db->fetchAll(
            "SELECT COALESCE(order_type,'delivery') AS order_type,
                    COUNT(*) AS cnt
               FROM orders
              WHERE DATE(placed_at) BETWEEN ? AND ?
                AND status NOT IN ('cancelled')
              GROUP BY order_type",
            [$from, $to]
        );
    }

    public function newCustomersThisPeriod(string $from, string $to): int
    {
        return (int) $this->db->fetchValue(
            "SELECT COUNT(*) FROM customers WHERE DATE(created_at) BETWEEN ? AND ?",
            [$from, $to]
        );
    }

    // ── Revenue analytics ─────────────────────────────────────────────────────

    public function revenueByHour(string $from, string $to): array
    {
        return $this->db->fetchAll(
            "SELECT HOUR(placed_at) AS hr,
                    COUNT(*)        AS orders,
                    COALESCE(SUM(CASE WHEN status='delivered' THEN total_amount END),0) AS revenue
               FROM orders
              WHERE DATE(placed_at) BETWEEN ? AND ?
              GROUP BY HOUR(placed_at) ORDER BY hr ASC",
            [$from, $to]
        );
    }

    public function revenueByDayOfWeek(string $from, string $to): array
    {
        return $this->db->fetchAll(
            "SELECT DAYOFWEEK(placed_at) AS dow,
                    DAYNAME(placed_at)   AS day_name,
                    COUNT(*)             AS orders,
                    COALESCE(SUM(CASE WHEN status='delivered' THEN total_amount END),0) AS revenue
               FROM orders
              WHERE DATE(placed_at) BETWEEN ? AND ?
              GROUP BY DAYOFWEEK(placed_at), DAYNAME(placed_at)
              ORDER BY dow ASC",
            [$from, $to]
        );
    }

    public function revenueComponents(string $from, string $to): array
    {
        return $this->db->fetchOne(
            "SELECT
                COALESCE(SUM(CASE WHEN status='delivered' THEN subtotal END),0)      AS food_revenue,
                COALESCE(SUM(CASE WHEN status='delivered' THEN delivery_fee END),0)  AS delivery_revenue,
                COALESCE(SUM(CASE WHEN status='delivered' THEN tax_amount END),0)    AS tax_revenue,
                COALESCE(SUM(discount_amount + voucher_discount),0)                  AS total_discount
               FROM orders
              WHERE DATE(placed_at) BETWEEN ? AND ?",
            [$from, $to]
        ) ?? [];
    }

    public function monthlyRevenue(int $months = 6): array
    {
        return $this->db->fetchAll(
            "SELECT DATE_FORMAT(placed_at,'%Y-%m') AS month,
                    COUNT(*)                         AS orders,
                    COALESCE(SUM(CASE WHEN status='delivered' THEN total_amount END),0) AS revenue
               FROM orders
              WHERE placed_at >= DATE_SUB(CURDATE(), INTERVAL ? MONTH)
              GROUP BY DATE_FORMAT(placed_at,'%Y-%m')
              ORDER BY month ASC",
            [$months]
        );
    }

    // ── Category performance ──────────────────────────────────────────────────

    public function categoryPerformance(string $from, string $to): array
    {
        return $this->db->fetchAll(
            "SELECT cat.name                   AS category,
                    COUNT(DISTINCT oi.order_id) AS orders,
                    SUM(oi.quantity)            AS qty_sold,
                    SUM(oi.line_total)          AS revenue
               FROM order_items oi
               JOIN orders o              ON o.id = oi.order_id
               LEFT JOIN menu_items mi    ON mi.id = oi.menu_item_id
               LEFT JOIN categories cat  ON cat.id = mi.category_id
              WHERE DATE(o.placed_at) BETWEEN ? AND ?
                AND o.status NOT IN ('cancelled')
              GROUP BY cat.id, cat.name
              ORDER BY revenue DESC",
            [$from, $to]
        );
    }

    public function topItemsPerCategory(string $from, string $to, int $limit = 5): array
    {
        return $this->db->fetchAll(
            "SELECT cat.name AS category, oi.item_name,
                    SUM(oi.quantity) AS qty, SUM(oi.line_total) AS revenue
               FROM order_items oi
               JOIN orders o             ON o.id = oi.order_id
               LEFT JOIN menu_items mi   ON mi.id = oi.menu_item_id
               LEFT JOIN categories cat  ON cat.id = mi.category_id
              WHERE DATE(o.placed_at) BETWEEN ? AND ?
                AND o.status NOT IN ('cancelled')
              GROUP BY cat.id, cat.name, oi.item_name
              ORDER BY cat.name, revenue DESC",
            [$from, $to]
        );
    }

    // ── Peak hours ────────────────────────────────────────────────────────────

    public function peakHeatmap(string $from, string $to): array
    {
        return $this->db->fetchAll(
            "SELECT DAYOFWEEK(placed_at) AS dow,
                    HOUR(placed_at)      AS hr,
                    COUNT(*)             AS orders
               FROM orders
              WHERE DATE(placed_at) BETWEEN ? AND ?
              GROUP BY DAYOFWEEK(placed_at), HOUR(placed_at)",
            [$from, $to]
        );
    }

    public function busiestHour(string $from, string $to): array
    {
        return $this->db->fetchOne(
            "SELECT HOUR(placed_at) AS hr, COUNT(*) AS orders
               FROM orders WHERE DATE(placed_at) BETWEEN ? AND ?
              GROUP BY HOUR(placed_at) ORDER BY orders DESC LIMIT 1",
            [$from, $to]
        ) ?? [];
    }

    public function busiestDay(string $from, string $to): array
    {
        return $this->db->fetchOne(
            "SELECT DAYNAME(placed_at) AS day_name, COUNT(*) AS orders
               FROM orders WHERE DATE(placed_at) BETWEEN ? AND ?
              GROUP BY DAYNAME(placed_at) ORDER BY orders DESC LIMIT 1",
            [$from, $to]
        ) ?? [];
    }

    // ── Cancellation analysis ─────────────────────────────────────────────────

    public function cancellationByHour(string $from, string $to): array
    {
        return $this->db->fetchAll(
            "SELECT HOUR(placed_at) AS hr, COUNT(*) AS cancelled
               FROM orders
              WHERE status = 'cancelled' AND DATE(placed_at) BETWEEN ? AND ?
              GROUP BY HOUR(placed_at) ORDER BY hr ASC",
            [$from, $to]
        );
    }

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

    public function cancellationGuestVsRegistered(string $from, string $to): array
    {
        return $this->db->fetchAll(
            "SELECT
                SUM(CASE WHEN customer_id IS NOT NULL THEN 1 ELSE 0 END) AS registered,
                SUM(CASE WHEN customer_id IS NULL     THEN 1 ELSE 0 END) AS guest
               FROM orders
              WHERE status = 'cancelled' AND DATE(placed_at) BETWEEN ? AND ?",
            [$from, $to]
        );
    }

    // ── Rider performance ─────────────────────────────────────────────────────

    public function riderPerformance(string $from, string $to): array
    {
        return $this->db->fetchAll(
            "SELECT u.name AS rider_name, u.phone,
                    COUNT(o.id)                                          AS total_assigned,
                    COUNT(CASE WHEN o.status='delivered' THEN 1 END)     AS delivered,
                    COUNT(CASE WHEN o.status='cancelled' THEN 1 END)     AS cancelled,
                    COALESCE(SUM(CASE WHEN o.status='delivered' THEN o.total_amount END),0) AS revenue_handled
               FROM users u
               JOIN roles r ON r.id = u.role_id AND r.name = 'rider'
               LEFT JOIN orders o ON o.rider_id = u.id AND DATE(o.placed_at) BETWEEN ? AND ?
              GROUP BY u.id, u.name, u.phone
              ORDER BY delivered DESC",
            [$from, $to]
        );
    }

    // ── Customer retention ────────────────────────────────────────────────────

    public function newVsReturning(string $from, string $to): array
    {
        return $this->db->fetchAll(
            "SELECT DATE(o.placed_at) AS d,
                    COUNT(DISTINCT CASE WHEN o.placed_at = first_order.first THEN o.customer_id END) AS new_customers,
                    COUNT(DISTINCT CASE WHEN o.placed_at > first_order.first  THEN o.customer_id END) AS returning_customers
               FROM orders o
               JOIN (
                   SELECT customer_id, MIN(placed_at) AS first FROM orders GROUP BY customer_id
               ) first_order ON first_order.customer_id = o.customer_id
              WHERE DATE(o.placed_at) BETWEEN ? AND ?
                AND o.customer_id IS NOT NULL
              GROUP BY DATE(o.placed_at) ORDER BY d ASC",
            [$from, $to]
        );
    }

    public function tierBreakdown(): array
    {
        return $this->db->fetchAll(
            "SELECT
                SUM(CASE WHEN delivered_count >= 30 THEN 1 ELSE 0 END) AS platinum,
                SUM(CASE WHEN delivered_count >= 15 AND delivered_count < 30 THEN 1 ELSE 0 END) AS gold,
                SUM(CASE WHEN delivered_count >= 5  AND delivered_count < 15 THEN 1 ELSE 0 END) AS silver,
                SUM(CASE WHEN delivered_count >= 1  AND delivered_count < 5  THEN 1 ELSE 0 END) AS bronze,
                SUM(CASE WHEN delivered_count = 0 THEN 1 ELSE 0 END)   AS new_customers
               FROM (
                   SELECT c.id,
                          COUNT(CASE WHEN o.status='delivered' THEN 1 END) AS delivered_count
                     FROM customers c
                     LEFT JOIN orders o ON o.customer_id = c.id
                    GROUP BY c.id
               ) t"
        );
    }

    public function tierRevenue(): array
    {
        return $this->db->fetchAll(
            "SELECT
                CASE
                    WHEN dc >= 30 THEN 'Platinum'
                    WHEN dc >= 15 THEN 'Gold'
                    WHEN dc >= 5  THEN 'Silver'
                    WHEN dc >= 1  THEN 'Bronze'
                    ELSE 'New'
                END AS tier,
                COUNT(*) AS customers,
                COALESCE(SUM(revenue),0) AS total_revenue
               FROM (
                   SELECT c.id,
                          COUNT(CASE WHEN o.status='delivered' THEN 1 END)             AS dc,
                          COALESCE(SUM(CASE WHEN o.status='delivered' THEN o.total_amount END),0) AS revenue
                     FROM customers c
                     LEFT JOIN orders o ON o.customer_id = c.id
                    GROUP BY c.id
               ) t
              GROUP BY tier ORDER BY total_revenue DESC"
        );
    }

    public function repeatOrderRate(): array
    {
        return $this->db->fetchOne(
            "SELECT
                COUNT(DISTINCT customer_id)                                         AS total_customers,
                COUNT(DISTINCT CASE WHEN order_count > 1 THEN customer_id END)      AS repeat_customers
               FROM (
                   SELECT customer_id, COUNT(*) AS order_count
                     FROM orders WHERE customer_id IS NOT NULL AND status='delivered'
                    GROUP BY customer_id
               ) t"
        ) ?? [];
    }

    public function churnedCustomers(): int
    {
        return (int) $this->db->fetchValue(
            "SELECT COUNT(*) FROM (
                SELECT customer_id FROM orders
                 WHERE customer_id IS NOT NULL
                 GROUP BY customer_id
                HAVING MAX(placed_at) < DATE_SUB(NOW(), INTERVAL 60 DAY)
             ) t"
        );
    }
}
