<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use App\Models\Order;
use App\Models\Product;
use App\Models\Transaction;
use App\Models\Expense;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Models\ExpenseCategory;

class ReportController extends Controller
{
    public function sales()
    {
        $products = Product::active()->get();
        return view('admin.reports.sales', compact('products'));
    }

    public function revenue()
    {
        return view('admin.reports.revenue');
    }

    public function transactions()
    {
        return view('admin.reports.transactions');
    }

    public function expenses()
    {
        $categories = ExpenseCategory::orderBy('name')->get();
        return view('admin.reports.expenses', compact('categories'));
    }

    public function disbursements()
    {
        return view('admin.reports.disbursements');
    }

    public function getSalesData(Request $request)
    {
        $request->validate([
            'start_date' => 'required|date',
            'end_date' => 'required|date|after_or_equal:start_date',
            'product_id' => 'nullable|exists:products,id'
        ]);

        $query = Order::with(['items.product'])
            ->whereBetween('created_at', [
                Carbon::parse($request->start_date)->startOfDay(),
                Carbon::parse($request->end_date)->endOfDay()
            ]);

        if ($request->product_id) {
            $query->whereHas('items', function ($q) use ($request) {
                $q->where('product_id', $request->product_id);
            });
        }

        $sales = $query->get();

        // Calculate totals from order items
        $totals = [
            'amount' => 0,
            'quantity' => 0
        ];

        foreach ($sales as $sale) {
            foreach ($sale->items as $item) {
                $totals['amount'] += $item->subtotal;
                $totals['quantity'] += $item->quantity;
            }
        }

        return response()->json([
            'data' => $sales,
            'totals' => $totals
        ]);
    }

    public function getRevenueData(Request $request)
{
    $request->validate([
        'start_date' => 'required|date',
        'end_date' => 'required|date|after_or_equal:start_date'
    ]);

    $revenue = DB::table('orders')
        ->join('order_items', 'orders.id', '=', 'order_items.order_id')
        ->leftJoin('stock_logs', 'order_items.product_id', '=', 'stock_logs.product_id')
        ->whereBetween('orders.created_at', [
            Carbon::parse($request->start_date)->startOfDay(),
            Carbon::parse($request->end_date)->endOfDay()
        ])
        ->select(
            DB::raw('DATE(orders.created_at) as date'),
            DB::raw('SUM(order_items.subtotal) as revenue'),
            DB::raw('SUM(order_items.subtotal - (order_items.quantity * COALESCE(stock_logs.purchase_price, 0))) as profit')
        )
        ->groupBy('date')
        ->get();

    return response()->json($revenue);
}

public function getTransactionData(Request $request)
{
    $request->validate([
        'start_date' => 'required|date',
        'end_date' => 'required|date|after_or_equal:start_date',
        'type' => 'nullable|string'
    ]);

    $startDate = Carbon::parse($request->start_date)->startOfDay();
    $endDate = Carbon::parse($request->end_date)->endOfDay();

    // Get sales transactions from orders
    $sales = Order::query()
        ->whereBetween('created_at', [$startDate, $endDate])
        ->select([
            'created_at',
            'order_number as reference',
            DB::raw("'sale' as type"),
            'subtotal as amount',
            'order_status as status',
            DB::raw("CONCAT('Order #', order_number) as description")
        ]);

    // Get purchase transactions from stock logs
    $purchases = DB::table('stock_logs')
        ->join('products', 'stock_logs.product_id', '=', 'products.id')
        ->whereBetween('stock_logs.created_at', [$startDate, $endDate])
        ->where('stock_logs.type', 'purchase')
        ->select([
            'stock_logs.created_at',
            DB::raw('CONCAT("STK-", stock_logs.id) as reference'),
            DB::raw("'purchase' as type"),
            DB::raw('(stock_logs.quantity * stock_logs.purchase_price) as amount'),
            DB::raw("'completed' as status"),
            DB::raw("CONCAT('Purchased ', stock_logs.quantity, ' units of ', products.name) as description")
        ]);

    // Combine and filter based on type
    if ($request->type === 'sale') {
        $transactions = $sales->get();
    } elseif ($request->type === 'purchase') {
        $transactions = $purchases->get();
    } else {
        $transactions = $sales->union($purchases)->orderBy('created_at', 'desc')->get();
    }

    return response()->json($transactions);
}

public function getExpenseData(Request $request)
{
    $request->validate([
        'start_date' => 'required|date',
        'end_date' => 'required|date|after_or_equal:start_date',
        'category_id' => 'nullable|exists:expense_categories,id'
    ]);

    $query = Expense::with('category')
        ->whereBetween('expense_date', [
            $request->start_date,
            $request->end_date
        ]);

    if ($request->category_id) {
        $query->where('category_id', $request->category_id);
    }

    $expenses = $query->get();

    // Calculate chart data
    $chartData = $expenses->groupBy('category.name')
        ->map(fn($items) => $items->sum('amount'))
        ->sortDesc();

    // Calculate average monthly expense
    $monthsDiff = Carbon::parse($request->start_date)->diffInMonths(Carbon::parse($request->end_date)) + 1;
    $averageMonthly = $expenses->sum('amount') / ($monthsDiff ?: 1);

    return response()->json([
        'expenses' => $expenses->map(function($expense) {
            return [
                'date' => $expense->expense_date->format('Y-m-d'),
                'category' => $expense->category->name,
                'description' => $expense->description,
                'reference' => $expense->reference,
                'amount' => $expense->amount,
                'status' => $expense->status
            ];
        }),
        'chart_data' => [
            'labels' => $chartData->keys(),
            'values' => $chartData->values()
        ],
        'average_monthly' => $averageMonthly
    ]);
}

    // Add other data retrieval methods similarly...
}