<?php

namespace App\Services;

use App\Models\Order;
use App\Models\Shipment;
use Illuminate\Contracts\Pagination\LengthAwarePaginator;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;

class DeliveryAnalyticsService
{
    public function getMonthlyKpis(int $month, int $year, ?int $carrierId = null): array
    {
        $baseQuery = $this->baseReportQuery($month, $year, $carrierId);

        $aggregated = (clone $baseQuery)
            ->selectRaw('COUNT(DISTINCT orders.id) as total_orders')
            ->selectRaw('COUNT(DISTINCT CASE WHEN shipments.status IN (?, ?) THEN orders.id END) as total_shipped', [Shipment::STATUS_CREATED, Shipment::STATUS_DELIVERED])
            ->selectRaw('COUNT(DISTINCT CASE WHEN shipments.status = ? OR orders.status = ? THEN orders.id END) as total_delivered', [Shipment::STATUS_DELIVERED, Order::STATUS_DELIVERED])
            ->selectRaw('COUNT(DISTINCT CASE WHEN orders.status = ? THEN orders.id END) as total_rto', [Order::STATUS_RETURNED])
            ->selectRaw('COUNT(DISTINCT CASE WHEN orders.payment_method = ? THEN orders.id END) as total_cod_orders', ['cod'])
            ->selectRaw('COUNT(DISTINCT CASE WHEN orders.payment_method <> ? OR orders.payment_method IS NULL THEN orders.id END) as total_prepaid_orders', ['cod'])
            ->selectRaw('SUM(CASE WHEN orders.payment_method = ? THEN orders.total_payable ELSE 0 END) as total_cod_amount', ['cod'])
            ->selectRaw('SUM(CASE WHEN orders.payment_method <> ? OR orders.payment_method IS NULL THEN orders.total_payable ELSE 0 END) as total_prepaid_amount', ['cod'])
            ->selectRaw('AVG(CASE WHEN shipments.status = ? OR orders.status = ? THEN TIMESTAMPDIFF(HOUR, orders.created_at, shipments.updated_at) END) as avg_delivery_hours', [Shipment::STATUS_DELIVERED, Order::STATUS_DELIVERED])
            ->first();

        $totalOrders = (int) ($aggregated->total_orders ?? 0);
        $totalShipped = (int) ($aggregated->total_shipped ?? 0);
        $totalDelivered = (int) ($aggregated->total_delivered ?? 0);
        $totalRto = (int) ($aggregated->total_rto ?? 0);
        $successRate = $totalShipped > 0 ? round(($totalDelivered / $totalShipped) * 100, 2) : 0.0;
        $avgDeliveryHours = (float) ($aggregated->avg_delivery_hours ?? 0);

        return [
            'total_orders' => $totalOrders,
            'total_shipped' => $totalShipped,
            'total_delivered' => $totalDelivered,
            'total_rto' => $totalRto,
            'delivery_success_rate' => $successRate,
            'total_cod_orders' => (int) ($aggregated->total_cod_orders ?? 0),
            'total_prepaid_orders' => (int) ($aggregated->total_prepaid_orders ?? 0),
            'total_cod_amount' => round((float) ($aggregated->total_cod_amount ?? 0), 2),
            'total_prepaid_amount' => round((float) ($aggregated->total_prepaid_amount ?? 0), 2),
            'average_delivery_time_days' => $avgDeliveryHours > 0 ? round($avgDeliveryHours / 24, 2) : 0.0,
        ];
    }

    public function getMonthlyReportRows(int $month, int $year, ?int $carrierId = null, int $perPage = 25): LengthAwarePaginator
    {
        $query = $this->baseReportQuery($month, $year, $carrierId)
            ->select([
                'orders.id as order_id',
                DB::raw("CONCAT('#', orders.id) as order_number"),
                'orders.created_at as order_date',
                'orders.status as order_status',
                'orders.total_payable as order_amount',
                'orders.payment_method',
                'shipments.status as shipment_status',
                'carriers.name as carrier_name',
            ])
            ->orderByDesc('orders.created_at');

        return $query->paginate($perPage)->withQueryString();
    }

    public function getMonthlyReportRowsForExport(int $month, int $year, ?int $carrierId = null): Collection
    {
        return $this->baseReportQuery($month, $year, $carrierId)
            ->select([
                'orders.id as order_id',
                DB::raw("CONCAT('#', orders.id) as order_number"),
                'orders.created_at as order_date',
                'orders.status as order_status',
                'orders.total_payable as order_amount',
                'orders.payment_method',
                'shipments.status as shipment_status',
                'carriers.name as carrier_name',
            ])
            ->orderByDesc('orders.created_at')
            ->get();
    }

    private function baseReportQuery(int $month, int $year, ?int $carrierId = null)
    {
        $query = DB::table('shipments')
            ->join('orders', 'orders.id', '=', 'shipments.order_id')
            ->leftJoin('carriers', 'carriers.id', '=', 'shipments.carrier_id')
            ->whereMonth('orders.created_at', $month)
            ->whereYear('orders.created_at', $year);

        if ($carrierId) {
            $query->where('shipments.carrier_id', $carrierId);
        }

        return $query;
    }
}