<?php

class Report extends Model
{
    protected string $table = 'orders'; // not really used directly, this model is query-only

    public function salesSummary(string $dateFrom, string $dateTo): array
    {
        $stmt = $this->db->prepare(
            "SELECT COUNT(*) as order_count, COALESCE(SUM(total_amount),0) as revenue,
                    COALESCE(AVG(total_amount),0) as avg_order_value
             FROM orders
             WHERE status != 'CANCELLED' AND DATE(created_at) BETWEEN :from AND :to"
        );
        $stmt->execute(['from' => $dateFrom, 'to' => $dateTo]);
        return $stmt->fetch();
    }

    public function dailySales(string $dateFrom, string $dateTo): array
    {
        $stmt = $this->db->prepare(
            "SELECT DATE(created_at) as sales_date, COUNT(*) as order_count, COALESCE(SUM(total_amount),0) as revenue
             FROM orders
             WHERE status != 'CANCELLED' AND DATE(created_at) BETWEEN :from AND :to
             GROUP BY DATE(created_at) ORDER BY sales_date ASC"
        );
        $stmt->execute(['from' => $dateFrom, 'to' => $dateTo]);
        return $stmt->fetchAll();
    }

    public function revenueByChannel(string $dateFrom, string $dateTo): array
    {
        $stmt = $this->db->prepare(
            "SELECT COALESCE(order_source, 'UNSPECIFIED') as channel, COUNT(*) as order_count, COALESCE(SUM(total_amount),0) as revenue
             FROM orders
             WHERE status != 'CANCELLED' AND DATE(created_at) BETWEEN :from AND :to
             GROUP BY order_source ORDER BY revenue DESC"
        );
        $stmt->execute(['from' => $dateFrom, 'to' => $dateTo]);
        return $stmt->fetchAll();
    }

    public function revenueByStatus(string $dateFrom, string $dateTo): array
    {
        $stmt = $this->db->prepare(
            "SELECT status, COUNT(*) as order_count, COALESCE(SUM(total_amount),0) as revenue
             FROM orders
             WHERE DATE(created_at) BETWEEN :from AND :to
             GROUP BY status ORDER BY revenue DESC"
        );
        $stmt->execute(['from' => $dateFrom, 'to' => $dateTo]);
        return $stmt->fetchAll();
    }

    public function topItems(string $dateFrom, string $dateTo, int $limit = 15): array
    {
        $stmt = $this->db->prepare(
            "SELECT oi.item_name_snapshot as item_name, 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
             WHERE o.status != 'CANCELLED' AND DATE(o.created_at) BETWEEN :from AND :to
             GROUP BY oi.item_name_snapshot
             ORDER BY revenue DESC LIMIT " . (int) $limit
        );
        $stmt->execute(['from' => $dateFrom, 'to' => $dateTo]);
        return $stmt->fetchAll();
    }

    public function pnl(string $dateFrom, string $dateTo): array
    {
        $income = $this->db->prepare(
            "SELECT COALESCE(SUM(amount),0) as total FROM payments WHERE status='PAID' AND DATE(paid_at) BETWEEN :from AND :to"
        );
        $income->execute(['from' => $dateFrom, 'to' => $dateTo]);

        $expenses = $this->db->prepare(
            "SELECT COALESCE(SUM(amount),0) as total FROM expenses WHERE expense_date BETWEEN :from AND :to"
        );
        $expenses->execute(['from' => $dateFrom, 'to' => $dateTo]);

        $payroll = $this->db->prepare(
            "SELECT COALESCE(SUM(net_pay),0) as total FROM payroll_items WHERE is_paid = 1 AND DATE(paid_at) BETWEEN :from AND :to"
        );
        $payroll->execute(['from' => $dateFrom, 'to' => $dateTo]);

        $purchases = $this->db->prepare(
            "SELECT COALESCE(SUM(amount),0) as total FROM supplier_payments WHERE DATE(paid_at) BETWEEN :from AND :to"
        );
        $purchases->execute(['from' => $dateFrom, 'to' => $dateTo]);

        $incomeTotal = (float) $income->fetch()['total'];
        $expenseTotal = (float) $expenses->fetch()['total'];
        $payrollTotal = (float) $payroll->fetch()['total'];
        $purchaseTotal = (float) $purchases->fetch()['total'];

        return [
            'income'     => $incomeTotal,
            'expenses'   => $expenseTotal,
            'payroll'    => $payrollTotal,
            'purchases'  => $purchaseTotal,
            'net'        => $incomeTotal - $expenseTotal - $payrollTotal - $purchaseTotal,
        ];
    }

    public function stockValuation(): array
    {
        $stmt = $this->db->query(
            "SELECT material_name, unit, current_stock, unit_cost, (current_stock * unit_cost) as stock_value
             FROM raw_materials WHERE is_active = 1 ORDER BY stock_value DESC"
        );
        return $stmt->fetchAll();
    }

    public function deliveryPerformance(string $dateFrom, string $dateTo): array
    {
        $stmt = $this->db->prepare(
            "SELECT u.full_name as rider_name,
                    COUNT(CASE WHEN d.status = 'DELIVERED' THEN 1 END) as delivered,
                    COUNT(CASE WHEN d.status = 'FAILED' THEN 1 END) as failed
             FROM deliveries d
             JOIN orders o ON o.id = d.order_id
             LEFT JOIN users u ON u.id = d.rider_id
             WHERE DATE(o.created_at) BETWEEN :from AND :to
             GROUP BY d.rider_id, u.full_name
             ORDER BY delivered DESC"
        );
        $stmt->execute(['from' => $dateFrom, 'to' => $dateTo]);
        return $stmt->fetchAll();
    }
}
