<?php

namespace App\Services;

use App\Models\Order;
use App\Models\Expense;
use App\Repositories\Eloquent\OrderRepository;
use App\Repositories\Eloquent\ExpenseRepository;
use App\Repositories\Eloquent\PaymentRepository;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

class AnalyticsService
{
    public function __construct(
        protected OrderRepository $orderRepository,
        protected ExpenseRepository $expenseRepository,
        protected PaymentRepository $paymentRepository,
        protected LoggingService $loggingService
    ) {}

    public function getDashboardSummary(?int $branchId = null, $date = null)
    {
        $this->loggingService->logInfo('Fetching dashboard summary', ['branch_id' => $branchId]);

        $date = $date ? Carbon::parse($date) : today();
        $startOfDay = $date->copy()->startOfDay();
        $endOfDay = $date->copy()->endOfDay();

        // Today's Orders
        $ordersQuery = Order::whereBetween('created_at', [$startOfDay, $endOfDay]);
        if ($branchId) {
            $ordersQuery->where('branch_id', $branchId);
        }
        $todayOrders = $ordersQuery->get();
        $completedOrders = $todayOrders->where('status', Order::STATUS_COMPLETED);

        // Today's Expenses
        $expensesQuery = Expense::whereBetween('expense_date', [$startOfDay, $endOfDay]);
        if ($branchId) {
            $expensesQuery->where('branch_id', $branchId);
        }
        $todayExpenses = $expensesQuery->get();

        // Revenue and Expenses
        $totalRevenue = $completedOrders->sum('total_amount');
        $totalExpenses = $todayExpenses->sum('amount');

        return [
            'date' => $date->toDateString(),
            'orders' => [
                'total' => $todayOrders->count(),
                'completed' => $completedOrders->count(),
                'pending' => $todayOrders->where('status', Order::STATUS_PENDING)->count(),
                'cancelled' => $todayOrders->where('status', Order::STATUS_CANCELLED)->count(),
            ],
            'revenue' => [
                'total' => $totalRevenue,
                'average_order_value' => $completedOrders->count() > 0 ? $totalRevenue / $completedOrders->count() : 0,
            ],
            'expenses' => [
                'total' => $totalExpenses,
                'count' => $todayExpenses->count(),
            ],
            'net_income' => $totalRevenue - $totalExpenses,
        ];
    }

    public function getSalesReport($startDate, $endDate, ?int $branchId = null)
    {
        $this->loggingService->logInfo('Fetching sales report', [
            'branch_id' => $branchId,
            'start_date' => $startDate,
            'end_date' => $endDate
        ]);

        // Parse dates to ensure we include the full day range
        $start = Carbon::parse($startDate)->startOfDay();
        $end = Carbon::parse($endDate)->endOfDay();

        $ordersQuery = Order::whereBetween('created_at', [$start, $end]);
        if ($branchId) {
            $ordersQuery->where('branch_id', $branchId);
        }
        $orders = $ordersQuery->get();
        $completedOrders = $orders->where('status', Order::STATUS_COMPLETED);

        // Group by date
        $dailySales = $completedOrders->groupBy(function ($order) {
            return $order->created_at->toDateString();
        })->map(function ($dayOrders) {
            return [
                'count' => $dayOrders->count(),
                'total' => $dayOrders->sum('total_amount'),
                'average' => $dayOrders->avg('total_amount'),
            ];
        });

        // Payment method breakdown
        $paymentSummary = $this->getPaymentSummary($startDate, $endDate, $branchId);

        return [
            'period' => [
                'start' => $startDate,
                'end' => $endDate,
            ],
            'summary' => [
                'total_orders' => $completedOrders->count(),
                'total_revenue' => $completedOrders->sum('total_amount'),
                'average_order_value' => $completedOrders->count() > 0 ? $completedOrders->avg('total_amount') : 0,
                'total_discount' => $completedOrders->sum('discount_amount'),
            ],
            'daily_sales' => $dailySales,
            'payment_methods' => $paymentSummary,
        ];
    }

    public function getExpenseReport($startDate, $endDate, ?int $branchId = null)
    {
        $this->loggingService->logInfo('Fetching expense report', [
            'branch_id' => $branchId,
            'start_date' => $startDate,
            'end_date' => $endDate
        ]);

        // Parse dates to ensure we include the full day range
        $start = Carbon::parse($startDate)->startOfDay();
        $end = Carbon::parse($endDate)->endOfDay();

        $expensesQuery = Expense::whereBetween('expense_date', [$start, $end]);
        if ($branchId) {
            $expensesQuery->where('branch_id', $branchId);
        }
        $expenses = $expensesQuery->get();

        $categorySummary = $this->getExpenseSummaryByCategory($startDate, $endDate, $branchId);

        // Group by date
        $dailyExpenses = $expenses->groupBy(function ($expense) {
            return $expense->expense_date->toDateString();
        })->map(function ($dayExpenses) {
            return [
                'count' => $dayExpenses->count(),
                'total' => $dayExpenses->sum('amount'),
            ];
        });

        return [
            'period' => [
                'start' => $startDate,
                'end' => $endDate,
            ],
            'summary' => [
                'total_expenses' => $expenses->sum('amount'),
                'count' => $expenses->count(),
            ],
            'by_category' => $categorySummary,
            'daily_expenses' => $dailyExpenses,
        ];
    }

    public function getProfitLossReport($startDate, $endDate, ?int $branchId = null)
    {
        $this->loggingService->logInfo('Fetching profit/loss report', [
            'branch_id' => $branchId,
            'start_date' => $startDate,
            'end_date' => $endDate
        ]);

        // Parse dates to ensure we include the full day range
        $start = Carbon::parse($startDate)->startOfDay();
        $end = Carbon::parse($endDate)->endOfDay();

        $ordersQuery = Order::whereBetween('created_at', [$start, $end]);
        if ($branchId) {
            $ordersQuery->where('branch_id', $branchId);
        }
        $orders = $ordersQuery->get();
        $completedOrders = $orders->where('status', Order::STATUS_COMPLETED);

        $expensesQuery = Expense::whereBetween('expense_date', [$start, $end]);
        if ($branchId) {
            $expensesQuery->where('branch_id', $branchId);
        }
        $expenses = $expensesQuery->get();

        $totalRevenue = $completedOrders->sum('total_amount');
        $totalExpenses = $expenses->sum('amount');
        $netProfit = $totalRevenue - $totalExpenses;

        return [
            'period' => [
                'start' => $startDate,
                'end' => $endDate,
            ],
            'revenue' => [
                'gross_sales' => $completedOrders->sum('subtotal'),
                'discounts' => $completedOrders->sum('discount_amount'),
                'net_sales' => $totalRevenue,
            ],
            'expenses' => [
                'total' => $totalExpenses,
                'by_category' => $this->getExpenseSummaryByCategory($startDate, $endDate, $branchId),
            ],
            'net_profit' => $netProfit,
            'profit_margin' => $totalRevenue > 0 ? ($netProfit / $totalRevenue) * 100 : 0,
        ];
    }

    public function getTopSellingProducts(int $limit, $startDate, $endDate, ?int $branchId = null)
    {
        $this->loggingService->logInfo('Fetching top products', [
            'branch_id' => $branchId,
            'start_date' => $startDate,
            'end_date' => $endDate
        ]);

        // Parse dates to ensure we include the full day range
        $start = Carbon::parse($startDate)->startOfDay();
        $end = Carbon::parse($endDate)->endOfDay();

        $query = DB::table('order_items')
            ->join('orders', 'order_items.order_id', '=', 'orders.id')
            ->join('products', 'order_items.product_id', '=', 'products.id')
            ->where('orders.status', Order::STATUS_COMPLETED)
            ->whereBetween('orders.created_at', [$start, $end]);

        if ($branchId) {
            $query->where('orders.branch_id', $branchId);
        }

        return $query->select(
                'products.id',
                'products.name',
                DB::raw('SUM(order_items.quantity) as total_quantity'),
                DB::raw('SUM(order_items.total_price) as total_revenue'),
                DB::raw('COUNT(DISTINCT orders.id) as order_count')
            )
            ->groupBy('products.id', 'products.name')
            ->orderByDesc('total_revenue')
            ->limit($limit)
            ->get();
    }

    public function getHourlySales($date = null, ?int $branchId = null)
    {
        $this->loggingService->logInfo('Fetching hourly sales', ['branch_id' => $branchId, 'date' => $date]);

        $date = $date ? Carbon::parse($date) : today();

        $query = DB::table('orders')
            ->where('status', Order::STATUS_COMPLETED)
            ->whereDate('created_at', $date);

        if ($branchId) {
            $query->where('branch_id', $branchId);
        }

        // Use CAST(strftime('%H', created_at) AS INTEGER) for SQLite compatibility
        return $query->select(
                DB::raw("CAST(strftime('%H', created_at) AS INTEGER) as hour"),
                DB::raw('COUNT(*) as order_count'),
                DB::raw('SUM(total_amount) as total_revenue')
            )
            ->groupBy('hour')
            ->orderBy('hour')
            ->get();
    }

    public function getSalesByCategory($startDate, $endDate, ?int $branchId = null)
    {
        $this->loggingService->logInfo('Fetching sales by category', [
            'branch_id' => $branchId,
            'start_date' => $startDate,
            'end_date' => $endDate
        ]);

        // Parse dates to ensure we include the full day range
        $start = Carbon::parse($startDate)->startOfDay();
        $end = Carbon::parse($endDate)->endOfDay();

        $query = DB::table('order_items')
            ->join('orders', 'order_items.order_id', '=', 'orders.id')
            ->join('products', 'order_items.product_id', '=', 'products.id')
            ->join('categories', 'products.category_id', '=', 'categories.id')
            ->where('orders.status', Order::STATUS_COMPLETED)
            ->whereBetween('orders.created_at', [$start, $end]);

        if ($branchId) {
            $query->where('orders.branch_id', $branchId);
        }

        return $query->select(
                'categories.id',
                'categories.name',
                DB::raw('SUM(order_items.quantity) as total_quantity'),
                DB::raw('SUM(order_items.total_price) as total_revenue'),
                DB::raw('COUNT(DISTINCT orders.id) as order_count')
            )
            ->groupBy('categories.id', 'categories.name')
            ->orderByDesc('total_revenue')
            ->get();
    }

    public function getSalesByPaymentMethod($startDate, $endDate, ?int $branchId = null)
    {
        $this->loggingService->logInfo('Fetching sales by payment method', [
            'branch_id' => $branchId,
            'start_date' => $startDate,
            'end_date' => $endDate
        ]);

        // Parse dates to ensure we include the full day range
        $start = Carbon::parse($startDate)->startOfDay();
        $end = Carbon::parse($endDate)->endOfDay();

        $query = DB::table('payments')
            ->join('orders', 'payments.order_id', '=', 'orders.id')
            ->where('orders.status', Order::STATUS_COMPLETED)
            ->where('payments.status', 'completed')
            ->whereBetween('payments.created_at', [$start, $end]);

        if ($branchId) {
            $query->where('orders.branch_id', $branchId);
        }

        return $query->select(
                'payments.method as payment_method',
                DB::raw('COUNT(*) as transaction_count'),
                DB::raw('SUM(payments.amount) as total_amount')
            )
            ->groupBy('payments.method')
            ->orderByDesc('total_amount')
            ->get();
    }

    public function getBranchComparison($startDate, $endDate)
    {
        $this->loggingService->logInfo('Fetching branch comparison', [
            'start_date' => $startDate,
            'end_date' => $endDate
        ]);

        // Parse dates to ensure we include the full day range
        $start = Carbon::parse($startDate)->startOfDay();
        $end = Carbon::parse($endDate)->endOfDay();

        $salesQuery = DB::table('orders')
            ->join('branches', 'orders.branch_id', '=', 'branches.id')
            ->where('orders.status', Order::STATUS_COMPLETED)
            ->whereBetween('orders.created_at', [$start, $end])
            ->select(
                'branches.id',
                'branches.name',
                DB::raw('COUNT(*) as order_count'),
                DB::raw('SUM(orders.total_amount) as total_revenue'),
                DB::raw('AVG(orders.total_amount) as avg_order_value')
            )
            ->groupBy('branches.id', 'branches.name')
            ->get();

        $expensesQuery = DB::table('expenses')
            ->join('branches', 'expenses.branch_id', '=', 'branches.id')
            ->whereBetween('expenses.expense_date', [$start, $end])
            ->select(
                'branches.id',
                DB::raw('SUM(expenses.amount) as total_expenses')
            )
            ->groupBy('branches.id')
            ->get()
            ->keyBy('id');

        return $salesQuery->map(function ($branch) use ($expensesQuery) {
            $expenses = $expensesQuery->get($branch->id);
            return [
                'branch_id' => $branch->id,
                'branch_name' => $branch->name,
                'order_count' => $branch->order_count,
                'total_revenue' => $branch->total_revenue,
                'avg_order_value' => $branch->avg_order_value,
                'total_expenses' => $expenses ? $expenses->total_expenses : 0,
                'net_profit' => $branch->total_revenue - ($expenses ? $expenses->total_expenses : 0),
            ];
        });
    }

    public function getInventoryValuation(?int $branchId = null)
    {
        $this->loggingService->logInfo('Fetching inventory valuation', ['branch_id' => $branchId]);

        $query = DB::table('stock_ledgers')
            ->join('ingredients', 'stock_ledgers.ingredient_id', '=', 'ingredients.id');

        if ($branchId) {
            $query->where('stock_ledgers.branch_id', $branchId);
        }

        return $query->select(
                'ingredients.id',
                'ingredients.name',
                'ingredients.unit',
                'stock_ledgers.quantity',
                'ingredients.cost_per_unit',
                DB::raw('(stock_ledgers.quantity * ingredients.cost_per_unit) as total_value')
            )
            ->orderByDesc('total_value')
            ->get();
    }

    public function getLowStockAlerts(?int $branchId = null)
    {
        $this->loggingService->logInfo('Fetching low stock alerts', ['branch_id' => $branchId]);

        $query = DB::table('stock_ledgers')
            ->join('ingredients', 'stock_ledgers.ingredient_id', '=', 'ingredients.id')
            ->whereColumn('stock_ledgers.quantity', '<=', 'ingredients.reorder_level');

        if ($branchId) {
            $query->where('stock_ledgers.branch_id', $branchId);
        }

        return $query->select(
                'ingredients.id',
                'ingredients.name',
                'ingredients.unit',
                'stock_ledgers.quantity',
                'ingredients.reorder_level',
                'ingredients.minimum_stock',
                DB::raw('(ingredients.reorder_level - stock_ledgers.quantity) as shortage')
            )
            ->orderByDesc('shortage')
            ->get();
    }

    protected function getPaymentSummary($startDate, $endDate, ?int $branchId = null)
    {
        // Parse dates to ensure we include the full day range
        $start = Carbon::parse($startDate)->startOfDay();
        $end = Carbon::parse($endDate)->endOfDay();

        $query = DB::table('payments')
            ->join('orders', 'payments.order_id', '=', 'orders.id')
            ->where('payments.status', 'completed')
            ->whereBetween('payments.created_at', [$start, $end]);

        if ($branchId) {
            $query->where('orders.branch_id', $branchId);
        }

        return $query->select(
                'payments.method as payment_method',
                DB::raw('COUNT(*) as count'),
                DB::raw('SUM(payments.amount) as total')
            )
            ->groupBy('payments.method')
            ->get();
    }

    protected function getExpenseSummaryByCategory($startDate, $endDate, ?int $branchId = null)
    {
        // Parse dates to ensure we include the full day range
        $start = Carbon::parse($startDate)->startOfDay();
        $end = Carbon::parse($endDate)->endOfDay();

        $query = DB::table('expenses')
            ->join('expense_categories', 'expenses.expense_category_id', '=', 'expense_categories.id')
            ->whereBetween('expenses.expense_date', [$start, $end]);

        if ($branchId) {
            $query->where('expenses.branch_id', $branchId);
        }

        return $query->select(
                'expense_categories.id',
                'expense_categories.name',
                DB::raw('COUNT(*) as count'),
                DB::raw('SUM(expenses.amount) as total')
            )
            ->groupBy('expense_categories.id', 'expense_categories.name')
            ->get();
    }
}
