<?php

namespace Core\App\Controllers;

use Core\App\Support\ShippingProviderPriceResolver;

class FinanceController
{
    private \PDO $db;

    public function __construct()
    {
        $this->db = \db();
    }

    /* ====================================================
       MAIN STATS PAGE
    ==================================================== */
    public function index()
    {
        $period    = $_GET['period'] ?? '30';
        $startDate = $_GET['start']  ?? null;
        $endDate   = $_GET['end']    ?? null;

        [$dateFrom, $dateTo] = $this->resolveDates($period, $startDate, $endDate);

        $summary     = $this->getSummary($dateFrom, $dateTo);
        $prevSummary = $this->getPreviousPeriodSummary($dateFrom, $dateTo);
        $dailyChart  = $this->getDailyChart($dateFrom, $dateTo);
        $byProduct   = $this->getByProduct($dateFrom, $dateTo);
        $byShipper   = $this->getByShipper($dateFrom, $dateTo);
        $byWilaya    = $this->getByWilaya($dateFrom, $dateTo);
        $topOrders   = $this->getTopOrders($dateFrom, $dateTo);

        require ROOT_PATH . '/admin/templates/header.php';
        require ROOT_PATH . '/admin/templates/finance/index.php';
        require ROOT_PATH . '/admin/templates/footer.php';
    }

    /* ====================================================
       DATE RESOLVER
    ==================================================== */
    private function resolveDates(string $period, ?string $start, ?string $end): array
    {
        if ($start && $end) {
            return [$start . ' 00:00:00', $end . ' 23:59:59'];
        }

        $days = match($period) {
            '7'   => 7,
            '90'  => 90,
            '365' => 365,
            default => 30,
        };

        $from = date('Y-m-d 00:00:00', strtotime("-{$days} days"));
        $to   = date('Y-m-d 23:59:59');
        return [$from, $to];
    }

    /* ====================================================
       CORE SUMMARY
    ==================================================== */
    private function getSummary(string $from, string $to): array
    {
        // الطلبات المؤكدة فقط = مداخيل فعلية
        $stmt = $this->db->prepare("
            SELECT
                COUNT(*)                                        AS confirmed_count,
                SUM(CASE
                    WHEN JSON_EXTRACT(extra,'$.pricing.negotiated_price') IS NOT NULL
                        THEN GREATEST(CAST(total_amount AS DECIMAL(12,2)) - COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(extra,'$.pricing.shipping_price')) AS DECIMAL(12,2)),0), 0)
                    ELSE COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(extra,'$.pricing.products_total')) AS DECIMAL(12,2)), 0)
                END) AS revenue_products,
                SUM(COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(extra,'$.pricing.shipping_price')) AS DECIMAL(12,2)),0))  AS revenue_shipping,
                SUM(COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(extra,'$.pricing.negotiated_price')) AS DECIMAL(12,2)), CAST(total_amount AS DECIMAL(12,2)), COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(extra,'$.pricing.total')) AS DECIMAL(12,2)),0))) AS revenue_total
            FROM orders
            WHERE order_status = 'confirmed'
              AND created_at BETWEEN ? AND ?
        ");
        $stmt->execute([$from, $to]);
        $confirmed = $stmt->fetch(\PDO::FETCH_ASSOC);

        // الطلبات المرفوضة
        $stmt2 = $this->db->prepare("
            SELECT COUNT(*) AS rejected_count
            FROM orders
            WHERE order_status = 'rejected'
              AND created_at BETWEEN ? AND ?
        ");
        $stmt2->execute([$from, $to]);
        $rejected = $stmt2->fetch(\PDO::FETCH_ASSOC);

        // الطلبات المعلقة
        $stmt3 = $this->db->prepare("
            SELECT COUNT(*) AS pending_count
            FROM orders
            WHERE order_status = 'pending'
              AND created_at BETWEEN ? AND ?
        ");
        $stmt3->execute([$from, $to]);
        $pending = $stmt3->fetch(\PDO::FETCH_ASSOC);

        // المرتجعات
        $stmt4 = $this->db->prepare("
            SELECT COUNT(*) AS returns_count,
                   SUM(JSON_EXTRACT(o.extra,'$.pricing.products_total')) AS returns_value
            FROM order_returns r
            JOIN orders o ON o.id = r.order_id
            WHERE r.created_at BETWEEN ? AND ?
        ");
        $stmt4->execute([$from, $to]);
        $returns = $stmt4->fetch(\PDO::FETCH_ASSOC);

        // تكلفة الشحن الفعلية (ما ندفعه لشركة الشحن)
        // نجلبها من shipping_prices حسب ولاية + provider + نوع التوصيل لكل طلب مؤكد
        $shippingCost = $this->calcShippingCost($from, $to);

        $revenueProducts = (float)($confirmed['revenue_products'] ?? 0);
        $revenueShipping = (float)($confirmed['revenue_shipping'] ?? 0);
        $revenueTotal    = (float)($confirmed['revenue_total']    ?? 0);
        $returnsValue    = (float)($returns['returns_value']      ?? 0);

        // الحسابات
        // المداخيل الصافية = ما جُمع من الزبائن على الطلبات المؤكدة
        // النفقات = تكلفة الشحن الفعلية (ما دفعناه لشركة الشحن)
        // صافي الربح = مداخيل المنتجات - تكلفة الشحن الفعلية
        // (سعر الشحن الذي دفعه الزبون - تكلفة الشحن الفعلية = هامش الشحن)

        $shippingMargin = $revenueShipping - $shippingCost;
        $netProfit      = $revenueProducts + $shippingMargin - $returnsValue;

        $confirmedCount = (int)($confirmed['confirmed_count'] ?? 0);
        $totalOrders    = $confirmedCount + (int)($rejected['rejected_count'] ?? 0) + (int)($pending['pending_count'] ?? 0);
        $confirmRate    = $totalOrders > 0 ? round(($confirmedCount / $totalOrders) * 100, 1) : 0;

        return [
            'confirmed_count'  => $confirmedCount,
            'rejected_count'   => (int)($rejected['rejected_count']  ?? 0),
            'pending_count'    => (int)($pending['pending_count']     ?? 0),
            'returns_count'    => (int)($returns['returns_count']     ?? 0),
            'total_orders'     => $totalOrders,
            'confirm_rate'     => $confirmRate,
            'revenue_products' => $revenueProducts,
            'revenue_shipping' => $revenueShipping,
            'revenue_total'    => $revenueTotal,
            'shipping_cost'    => $shippingCost,
            'shipping_margin'  => $shippingMargin,
            'returns_value'    => $returnsValue,
            'net_profit'       => $netProfit,
            'avg_order_value'  => $confirmedCount > 0 ? round($revenueTotal / $confirmedCount, 2) : 0,
        ];
    }

    /* ====================================================
       SHIPPING COST CALCULATOR
       يحسب تكلفة الشحن الفعلية التي ندفعها لشركة الشحن
    ==================================================== */
    private function calcShippingCost(string $from, string $to): float
    {
        // نجلب كل الطلبات المؤكدة مع provider و wilaya
        $stmt = $this->db->prepare("
            SELECT
                JSON_UNQUOTE(JSON_EXTRACT(extra,'$.shipping.provider'))    AS provider,
                JSON_UNQUOTE(JSON_EXTRACT(extra,'$.shipping.destination.state_code')) AS wilaya_code,
                JSON_UNQUOTE(JSON_EXTRACT(extra,'$.shipping.type'))        AS delivery_type,
                COUNT(*) AS cnt
            FROM orders
            WHERE order_status = 'confirmed'
              AND created_at BETWEEN ? AND ?
            GROUP BY provider, wilaya_code, delivery_type
        ");
        $stmt->execute([$from, $to]);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);

        $total = 0.0;
        foreach ($rows as $row) {
            if (!$row['provider'] || !$row['wilaya_code']) continue;
            $price = ShippingProviderPriceResolver::resolve(
                (string)$row['provider'],
                (string)$row['wilaya_code'],
                $row['delivery_type'] === 'desk' ? 'desk' : 'home'
            );
            $total += $price * (int)$row['cnt'];
        }

        return $total;
    }

    /* ====================================================
       PREVIOUS PERIOD (للمقارنة)
    ==================================================== */
    private function getPreviousPeriodSummary(string $from, string $to): array
    {
        $duration = strtotime($to) - strtotime($from);
        $prevTo   = date('Y-m-d H:i:s', strtotime($from) - 1);
        $prevFrom = date('Y-m-d H:i:s', strtotime($from) - $duration - 1);

        $stmt = $this->db->prepare("
            SELECT
                COUNT(*) AS confirmed_count,
                COALESCE(SUM(COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(extra,'$.pricing.negotiated_price')) AS DECIMAL(12,2)), CAST(total_amount AS DECIMAL(12,2)), COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(extra,'$.pricing.total')) AS DECIMAL(12,2)),0))), 0) AS revenue_total
            FROM orders
            WHERE order_status = 'confirmed'
              AND created_at BETWEEN ? AND ?
        ");
        $stmt->execute([$prevFrom, $prevTo]);
        return $stmt->fetch(\PDO::FETCH_ASSOC) ?: ['confirmed_count' => 0, 'revenue_total' => 0];
    }

    /* ====================================================
       DAILY CHART DATA
    ==================================================== */
    private function getDailyChart(string $from, string $to): array
    {
        $stmt = $this->db->prepare("
            SELECT
                DATE(created_at) AS day,
                COUNT(*) AS orders_count,
                COALESCE(SUM(CASE
                    WHEN JSON_EXTRACT(extra,'$.pricing.negotiated_price') IS NOT NULL
                        THEN GREATEST(CAST(total_amount AS DECIMAL(12,2)) - COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(extra,'$.pricing.shipping_price')) AS DECIMAL(12,2)),0), 0)
                    ELSE COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(extra,'$.pricing.products_total')) AS DECIMAL(12,2)),0)
                END), 0) AS revenue,
                COALESCE(SUM(CAST(JSON_UNQUOTE(JSON_EXTRACT(extra,'$.pricing.shipping_price')) AS DECIMAL(12,2))), 0) AS shipping_revenue
            FROM orders
            WHERE order_status = 'confirmed'
              AND created_at BETWEEN ? AND ?
            GROUP BY DATE(created_at)
            ORDER BY day ASC
        ");
        $stmt->execute([$from, $to]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    /* ====================================================
       BY PRODUCT
    ==================================================== */
    private function getByProduct(string $from, string $to): array
    {
        $stmt = $this->db->prepare("
            SELECT
                p.title,
                COUNT(o.id) AS orders_count,
                COALESCE(SUM(CASE
                    WHEN JSON_EXTRACT(o.extra,'$.pricing.negotiated_price') IS NOT NULL
                        THEN GREATEST(CAST(o.total_amount AS DECIMAL(12,2)) - COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(o.extra,'$.pricing.shipping_price')) AS DECIMAL(12,2)),0), 0)
                    ELSE COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(o.extra,'$.pricing.products_total')) AS DECIMAL(12,2)),0)
                END), 0) AS revenue,
                COALESCE(SUM(COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(o.extra,'$.pricing.negotiated_price')) AS DECIMAL(12,2)), CAST(o.total_amount AS DECIMAL(12,2)), COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(o.extra,'$.pricing.total')) AS DECIMAL(12,2)),0))), 0) AS total
            FROM orders o
            LEFT JOIN products p ON p.id = o.product_id
            WHERE o.order_status = 'confirmed'
              AND o.created_at BETWEEN ? AND ?
            GROUP BY o.product_id, p.title
            ORDER BY revenue DESC
            LIMIT 10
        ");
        $stmt->execute([$from, $to]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    /* ====================================================
       BY SHIPPER
    ==================================================== */
    private function getByShipper(string $from, string $to): array
    {
        $stmt = $this->db->prepare("
            SELECT
                JSON_UNQUOTE(JSON_EXTRACT(extra,'$.shipping.provider')) AS provider,
                COUNT(*) AS orders_count,
                COALESCE(SUM(CAST(JSON_UNQUOTE(JSON_EXTRACT(extra,'$.pricing.shipping_price')) AS DECIMAL(12,2))), 0) AS shipping_revenue,
                COALESCE(SUM(COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(extra,'$.pricing.negotiated_price')) AS DECIMAL(12,2)), CAST(total_amount AS DECIMAL(12,2)), COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(extra,'$.pricing.total')) AS DECIMAL(12,2)),0))), 0) AS total
            FROM orders
            WHERE order_status = 'confirmed'
              AND created_at BETWEEN ? AND ?
            GROUP BY JSON_EXTRACT(extra,'$.shipping.provider')
            ORDER BY orders_count DESC
        ");
        $stmt->execute([$from, $to]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    /* ====================================================
       BY WILAYA
    ==================================================== */
    private function getByWilaya(string $from, string $to): array
    {
        $stmt = $this->db->prepare("
            SELECT
                JSON_UNQUOTE(JSON_EXTRACT(extra,'$.shipping.destination.state_code')) AS wilaya_code,
                JSON_UNQUOTE(JSON_EXTRACT(extra,'$.shipping.destination.city_name'))  AS city_name,
                COUNT(*) AS orders_count,
                COALESCE(SUM(COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(extra,'$.pricing.negotiated_price')) AS DECIMAL(12,2)), CAST(total_amount AS DECIMAL(12,2)), COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(extra,'$.pricing.total')) AS DECIMAL(12,2)),0))), 0) AS revenue
            FROM orders
            WHERE order_status = 'confirmed'
              AND created_at BETWEEN ? AND ?
            GROUP BY wilaya_code, city_name
            ORDER BY orders_count DESC
            LIMIT 15
        ");
        $stmt->execute([$from, $to]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    /* ====================================================
       TOP ORDERS
    ==================================================== */
    private function getTopOrders(string $from, string $to): array
    {
        $stmt = $this->db->prepare("
            SELECT
                o.id,
                o.full_name,
                o.phone,
                CASE
                    WHEN JSON_EXTRACT(o.extra,'$.pricing.negotiated_price') IS NOT NULL
                        THEN GREATEST(CAST(o.total_amount AS DECIMAL(12,2)) - COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(o.extra,'$.pricing.shipping_price')) AS DECIMAL(12,2)),0), 0)
                    ELSE COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(o.extra,'$.pricing.products_total')) AS DECIMAL(12,2)),0)
                END AS product_total,
                COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(o.extra,'$.pricing.shipping_price')) AS DECIMAL(12,2)),0) AS shipping,
                COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(o.extra,'$.pricing.negotiated_price')) AS DECIMAL(12,2)), CAST(o.total_amount AS DECIMAL(12,2)), COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(o.extra,'$.pricing.total')) AS DECIMAL(12,2)),0)) AS total,
                JSON_UNQUOTE(JSON_EXTRACT(o.extra,'$.shipping.provider'))      AS provider,
                o.created_at
            FROM orders o
            WHERE o.order_status = 'confirmed'
              AND o.created_at BETWEEN ? AND ?
            ORDER BY COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(o.extra,'$.pricing.negotiated_price')) AS DECIMAL(12,2)), CAST(o.total_amount AS DECIMAL(12,2)), COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(o.extra,'$.pricing.total')) AS DECIMAL(12,2)),0)) DESC
            LIMIT 10
        ");
        $stmt->execute([$from, $to]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }
}