<?php

require_once BASE_PATH . '/config/database.php';

/**
 * Read-only aggregates for the Owner reporting area.
 *
 * Revenue and sales always use completed orders with a completed_at range.
 * Order lifecycle statistics use created_at, so status percentages describe
 * orders placed during the selected period.
 */
class Report
{
    private PDO $db;

    public function __construct()
    {
        $this->db = (new Database())->connect();
    }

    public function getCompletedSalesSummary(
        string $fromStart,
        string $toExclusive
    ): array {
        $stmt = $this->db->prepare(
            "SELECT
                COUNT(*) AS completed_order_count,
                COALESCE(SUM(o.total_amount), 0) AS final_revenue,
                COALESCE(AVG(o.total_amount), 0) AS average_order_value,
                COALESCE(SUM(o.subtotal), 0) AS subtotal_amount,
                COALESCE(SUM(o.discount_amount), 0) AS discount_amount,
                COALESCE(SUM(o.shipping_fee), 0) AS shipping_amount,
                COUNT(DISTINCT o.customer_id) AS customer_count
             FROM orders o
             WHERE o.status = 'completed'
               AND o.completed_at >= :completed_from
               AND o.completed_at < :completed_to"
        );
        $stmt->execute([
            'completed_from' => $fromStart,
            'completed_to' => $toExclusive
        ]);

        return $stmt->fetch() ?: $this->emptyCompletedSalesSummary();
    }

    public function getCompletedQuantitySold(
        string $fromStart,
        string $toExclusive
    ): int {
        $stmt = $this->db->prepare(
            "SELECT COALESCE(SUM(oi.quantity), 0)
             FROM orders o
             INNER JOIN order_items oi ON oi.order_id = o.id
             WHERE o.status = 'completed'
               AND o.completed_at >= :completed_from
               AND o.completed_at < :completed_to"
        );
        $stmt->execute([
            'completed_from' => $fromStart,
            'completed_to' => $toExclusive
        ]);

        return (int) $stmt->fetchColumn();
    }

    public function getOrderLifecycleSummary(
        string $fromStart,
        string $toExclusive
    ): array {
        $stmt = $this->db->prepare(
            "SELECT
                COUNT(*) AS total_orders,
                COALESCE(SUM(o.status = 'cancelled'), 0) AS cancelled_order_count,
                COALESCE(SUM(
                    CASE WHEN o.status = 'cancelled'
                    THEN o.total_amount ELSE 0 END
                ), 0) AS cancelled_order_value
             FROM orders o
             WHERE o.created_at >= :created_from
               AND o.created_at < :created_to"
        );
        $stmt->execute([
            'created_from' => $fromStart,
            'created_to' => $toExclusive
        ]);

        return $stmt->fetch() ?: [
            'total_orders' => 0,
            'cancelled_order_count' => 0,
            'cancelled_order_value' => 0
        ];
    }

    public function getRevenueByDay(
        string $fromStart,
        string $toExclusive
    ): array {
        $stmt = $this->db->prepare(
            "SELECT
                DATE(o.completed_at) AS report_date,
                COUNT(*) AS order_count,
                COALESCE(SUM(o.total_amount), 0) AS revenue
             FROM orders o
             WHERE o.status = 'completed'
               AND o.completed_at >= :completed_from
               AND o.completed_at < :completed_to
             GROUP BY DATE(o.completed_at)
             ORDER BY report_date ASC"
        );
        $stmt->execute([
            'completed_from' => $fromStart,
            'completed_to' => $toExclusive
        ]);

        return $stmt->fetchAll();
    }

    public function getRevenueByMonth(
        string $fromStart,
        string $toExclusive
    ): array {
        $stmt = $this->db->prepare(
            "SELECT
                DATE_FORMAT(o.completed_at, '%Y-%m') AS report_month,
                COUNT(*) AS completed_order_count,
                COALESCE(SUM(o.total_amount), 0) AS revenue
             FROM orders o
             WHERE o.status = 'completed'
               AND o.completed_at >= :completed_from
               AND o.completed_at < :completed_to
             GROUP BY DATE_FORMAT(o.completed_at, '%Y-%m')
             ORDER BY report_month ASC"
        );
        $stmt->execute([
            'completed_from' => $fromStart,
            'completed_to' => $toExclusive
        ]);

        return $stmt->fetchAll();
    }

    public function getOrderStatusStats(
        string $fromStart,
        string $toExclusive
    ): array {
        $stmt = $this->db->prepare(
            "SELECT
                o.status,
                COUNT(*) AS order_count
             FROM orders o
             WHERE o.created_at >= :created_from
               AND o.created_at < :created_to
             GROUP BY o.status
             ORDER BY o.status ASC"
        );
        $stmt->execute([
            'created_from' => $fromStart,
            'created_to' => $toExclusive
        ]);

        return $stmt->fetchAll();
    }

    public function getBestSellingProducts(
        string $fromStart,
        string $toExclusive,
        int $limit = 10
    ): array {
        $limit = max(1, min($limit, 100));

        $stmt = $this->db->prepare(
            "SELECT
                oi.product_id,
                MAX(oi.sku) AS sku,
                MAX(oi.product_name) AS product_name,
                SUM(oi.quantity) AS quantity_sold,
                COUNT(DISTINCT oi.order_id) AS order_count,
                COALESCE(SUM(oi.line_total), 0) AS revenue
             FROM orders o
             INNER JOIN order_items oi ON oi.order_id = o.id
             WHERE o.status = 'completed'
               AND o.completed_at >= :completed_from
               AND o.completed_at < :completed_to
             GROUP BY oi.product_id
             ORDER BY quantity_sold DESC, revenue DESC, product_name ASC
             LIMIT {$limit}"
        );
        $stmt->execute([
            'completed_from' => $fromStart,
            'completed_to' => $toExclusive
        ]);

        return $stmt->fetchAll();
    }

    public function countProductSales(
        string $fromStart,
        string $toExclusive,
        string $search = ''
    ): int {
        [$searchSql, $searchParams] = $this->buildProductSearch($search);

        $stmt = $this->db->prepare(
            "SELECT COUNT(*)
             FROM (
                SELECT oi.product_id
                FROM orders o
                INNER JOIN order_items oi ON oi.order_id = o.id
                WHERE o.status = 'completed'
                  AND o.completed_at >= :completed_from
                  AND o.completed_at < :completed_to
                  {$searchSql}
                GROUP BY oi.product_id
             ) AS product_sales"
        );
        $stmt->execute(array_merge([
            'completed_from' => $fromStart,
            'completed_to' => $toExclusive
        ], $searchParams));

        return (int) $stmt->fetchColumn();
    }

    public function getProductSales(
        string $fromStart,
        string $toExclusive,
        string $search,
        string $sort,
        int $limit,
        int $offset
    ): array {
        [$searchSql, $searchParams] = $this->buildProductSearch($search);
        $limit = max(1, min($limit, 100));
        $offset = max(0, $offset);

        $orderBy = [
            'quantity' => 'quantity_sold DESC, revenue DESC, product_name ASC',
            'revenue' => 'revenue DESC, quantity_sold DESC, product_name ASC',
            'name' => 'product_name ASC, quantity_sold DESC, revenue DESC'
        ][$sort] ?? 'quantity_sold DESC, revenue DESC, product_name ASC';

        $stmt = $this->db->prepare(
            "SELECT
                oi.product_id,
                MAX(oi.sku) AS sku,
                MAX(oi.product_name) AS product_name,
                SUM(oi.quantity) AS quantity_sold,
                COUNT(DISTINCT oi.order_id) AS order_count,
                COALESCE(SUM(oi.line_total), 0) AS revenue
             FROM orders o
             INNER JOIN order_items oi ON oi.order_id = o.id
             WHERE o.status = 'completed'
               AND o.completed_at >= :completed_from
               AND o.completed_at < :completed_to
               {$searchSql}
             GROUP BY oi.product_id
             ORDER BY {$orderBy}
             LIMIT {$limit} OFFSET {$offset}"
        );
        $stmt->execute(array_merge([
            'completed_from' => $fromStart,
            'completed_to' => $toExclusive
        ], $searchParams));

        return $stmt->fetchAll();
    }

    public function getCategoryPerformance(
        string $fromStart,
        string $toExclusive
    ): array {
        $stmt = $this->db->prepare(
            "SELECT
                pc.id AS category_id,
                pc.name AS category_name,
                SUM(oi.quantity) AS quantity_sold,
                COUNT(DISTINCT oi.order_id) AS completed_order_count,
                COALESCE(SUM(oi.line_total), 0) AS revenue
             FROM orders o
             INNER JOIN order_items oi ON oi.order_id = o.id
             INNER JOIN products p ON p.id = oi.product_id
             INNER JOIN product_categories pc ON pc.id = p.category_id
             WHERE o.status = 'completed'
               AND o.completed_at >= :completed_from
               AND o.completed_at < :completed_to
             GROUP BY pc.id, pc.name
             ORDER BY revenue DESC, quantity_sold DESC, category_name ASC"
        );
        $stmt->execute([
            'completed_from' => $fromStart,
            'completed_to' => $toExclusive
        ]);

        return $stmt->fetchAll();
    }

    public function countTopCustomers(
        string $fromStart,
        string $toExclusive,
        string $search = ''
    ): int {
        [$searchSql, $searchParams] = $this->buildCustomerSearch($search);

        $stmt = $this->db->prepare(
            "SELECT COUNT(DISTINCT o.customer_id)
             FROM orders o
             INNER JOIN users u ON u.id = o.customer_id
             WHERE o.status = 'completed'
               AND o.completed_at >= :completed_from
               AND o.completed_at < :completed_to
               {$searchSql}"
        );
        $stmt->execute(array_merge([
            'completed_from' => $fromStart,
            'completed_to' => $toExclusive
        ], $searchParams));

        return (int) $stmt->fetchColumn();
    }

    public function getTopCustomers(
        string $fromStart,
        string $toExclusive,
        string $search = '',
        int $limit = 20,
        int $offset = 0
    ): array {
        [$searchSql, $searchParams] = $this->buildCustomerSearch($search);
        $limit = max(1, min($limit, 100));
        $offset = max(0, $offset);

        $stmt = $this->db->prepare(
            "SELECT
                u.id AS customer_id,
                u.full_name,
                u.phone,
                u.email,
                COUNT(o.id) AS completed_order_count,
                COALESCE(SUM(o.total_amount), 0) AS total_spent,
                COALESCE(AVG(o.total_amount), 0) AS average_order_value,
                MAX(o.completed_at) AS last_completed_at
             FROM orders o
             INNER JOIN users u ON u.id = o.customer_id
             WHERE o.status = 'completed'
               AND o.completed_at >= :completed_from
               AND o.completed_at < :completed_to
               {$searchSql}
             GROUP BY u.id, u.full_name, u.phone, u.email
             ORDER BY total_spent DESC, completed_order_count DESC, u.full_name ASC
             LIMIT {$limit} OFFSET {$offset}"
        );
        $stmt->execute(array_merge([
            'completed_from' => $fromStart,
            'completed_to' => $toExclusive
        ], $searchParams));

        return $stmt->fetchAll();
    }

    public function getPromotionPerformance(
        string $fromStart,
        string $toExclusive
    ): array {
        $stmt = $this->db->prepare(
            "SELECT
                p.id AS promotion_id,
                p.name AS promotion_name,
                p.code AS promotion_code,
                COUNT(o.id) AS completed_order_count,
                COALESCE(SUM(o.discount_amount), 0) AS total_discount,
                COALESCE(SUM(o.total_amount), 0) AS generated_revenue
             FROM orders o
             INNER JOIN promotions p ON p.id = o.promotion_id
             WHERE o.status = 'completed'
               AND o.completed_at >= :completed_from
               AND o.completed_at < :completed_to
             GROUP BY p.id, p.name, p.code
             ORDER BY generated_revenue DESC, completed_order_count DESC, p.name ASC"
        );
        $stmt->execute([
            'completed_from' => $fromStart,
            'completed_to' => $toExclusive
        ]);

        return $stmt->fetchAll();
    }

    public function getLowStockProducts(int $threshold): array
    {
        $stmt = $this->db->prepare(
            "SELECT
                p.id,
                p.sku,
                p.name,
                p.stock_quantity,
                p.status,
                p.sold_quantity
             FROM products p
             WHERE p.stock_quantity <= :threshold
               AND p.status != 'hidden'
             ORDER BY p.stock_quantity ASC, p.name ASC"
        );
        $stmt->execute([
            'threshold' => max(0, $threshold)
        ]);

        return $stmt->fetchAll();
    }

    public function getOutOfStockProducts(): array
    {
        $stmt = $this->db->query(
            "SELECT
                p.id,
                p.sku,
                p.name,
                p.stock_quantity,
                p.status,
                p.sold_quantity
             FROM products p
             WHERE p.stock_quantity <= 0
                OR p.status = 'out_of_stock'
             ORDER BY p.stock_quantity ASC, p.name ASC"
        );

        return $stmt->fetchAll();
    }

    private function buildProductSearch(string $search): array
    {
        if ($search === '') {
            return ['', []];
        }

        return [
            ' AND (oi.product_name LIKE :product_name_search
                   OR oi.sku LIKE :product_sku_search)',
            [
                'product_name_search' => '%' . $search . '%',
                'product_sku_search' => '%' . $search . '%'
            ]
        ];
    }

    private function buildCustomerSearch(string $search): array
    {
        if ($search === '') {
            return ['', []];
        }

        return [
            ' AND (u.full_name LIKE :customer_name_search
                   OR u.phone LIKE :customer_phone_search
                   OR u.email LIKE :customer_email_search)',
            [
                'customer_name_search' => '%' . $search . '%',
                'customer_phone_search' => '%' . $search . '%',
                'customer_email_search' => '%' . $search . '%'
            ]
        ];
    }

    private function emptyCompletedSalesSummary(): array
    {
        return [
            'completed_order_count' => 0,
            'final_revenue' => 0,
            'average_order_value' => 0,
            'subtotal_amount' => 0,
            'discount_amount' => 0,
            'shipping_amount' => 0,
            'customer_count' => 0
        ];
    }
}
