<?php

namespace App\Http\Controllers\Admin;

use App\Models\Expense;
use App\Models\ExpenseCategory;
use App\Models\Invoice;
use App\Models\InvoiceDetail;
use App\Models\Product;
use App\Models\Category;
use App\Http\Controllers\Controller;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class FinancialReportsController extends Controller
{
    public function __construct()
    {
        // Apply middleware for permissions
        $this->middleware('permission:finance-reports', ['only' => ['index', 'expenses', 'profits', 'sales', 'topProducts']]);
    }

    /**
     * Display the main financial reports dashboard.
     */
    public function index(Request $request)
    {
        // Get selected year (default to current year if not provided)
        $selectedYear = $request->input('year', Carbon::now()->year);

        // Get total expenses for the current month
        $currentMonthExpenses = Expense::where('status', 'approved')->whereMonth('created_at', Carbon::now()->month)
            ->whereYear('created_at', Carbon::now()->year)
            ->sum('amount');

        // Get total sales for the current month
        $currentMonthSales = Invoice::whereMonth('created_at', Carbon::now()->month)
            ->whereYear('created_at', Carbon::now()->year)
            ->where('status', 'sending_to_invotek')
            ->sum('total_price');

        // Get total returns for the current month
        $currentMonthReturns = Invoice::whereMonth('created_at', Carbon::now()->month)
            ->whereYear('created_at', Carbon::now()->year)
            ->where('status', 'returned')
            ->sum('total_price');

        // Calculate net sales (sales minus returns)
        $netSales = $currentMonthSales - $currentMonthReturns;

        // Get total profit for the current month (net sales - expenses)
        $currentMonthProfit = $netSales - $currentMonthExpenses;

        // Get top 5 selling products for the current month
        $topProducts = InvoiceDetail::select('product_id', DB::raw('SUM(quantity) as total_quantity'))
            ->whereHas('invoice', function ($query) {
                $query->whereMonth('created_at', Carbon::now()->month)
                    ->whereYear('created_at', Carbon::now()->year)
                    ->where('status', 'sending_to_invotek');
            })
            ->groupBy('product_id')
            ->orderByDesc('total_quantity')
            ->limit(5)
            ->with('product')
            ->get();

        // Get monthly data for the selected year (for charts)
        $monthlyExpenses = $this->getMonthlyData('expenses', $selectedYear);
        $monthlySales = $this->getMonthlyData('sales', $selectedYear);
        $monthlyReturns = $this->getMonthlyData('returns', $selectedYear);
        $monthlyProfits = $this->getMonthlyData('profits', $selectedYear);


        return view('admin.financial_reports.index', compact(
            'currentMonthExpenses',
            'currentMonthSales',
            'currentMonthReturns',
            'currentMonthProfit',
            'topProducts',
            'monthlyExpenses',
            'monthlySales',
            'monthlyReturns',
            'monthlyProfits',
            'selectedYear'
        ));
    }

    /**
     * Display expense reports with filtering options.
     */
    public function expenses(Request $request)
    {
        // Get selected year (default to current year if not provided)
        $selectedYear = $request->input('year', Carbon::now()->year);

        // Start building the query
        $query = Expense::with('category');

        // Apply filters if any
        if ($request->filled('category_id')) {
            $query->where('expense_category_id', $request->category_id);
        }

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

        if ($request->filled('start_date') && $request->filled('end_date')) {
            $query->whereBetween('expense_date', [$request->start_date, $request->end_date]);
        } elseif ($request->filled('start_date')) {
            $query->where('expense_date', '>=', $request->start_date);
        } elseif ($request->filled('end_date')) {
            $query->where('expense_date', '<=', $request->end_date);
        }

        if ($request->filled('amount_min')) {
            $query->where('amount', '>=', $request->amount_min);
        }

        if ($request->filled('amount_max')) {
            $query->where('amount', '<=', $request->amount_max);
        }

        // Debug information
        \Log::info('Expenses Query SQL: ' . $query->toSql());
        \Log::info('Expenses Query Bindings: ' . json_encode($query->getBindings()));
        \Log::info('Expenses Count: ' . $query->count());
        \Log::info('All Expenses Count: ' . Expense::count());

        // Get all expense categories for the filter dropdown
        $categories = ExpenseCategory::all();

        // Get the expenses with pagination
        $expenses = $query->latest('expense_date')->paginate(15);

        // Calculate total amount of filtered expenses
        $totalAmount = $query->where('status', 'approved')->sum('amount');

        // Calculate totals by category for pie chart
        $expensesByCategory = Expense::select('expense_category_id', DB::raw('SUM(amount) as total_amount'))
            ->groupBy('expense_category_id')
            ->with('category')
            ->get();

        // Debug category data
        \Log::info('Expenses By Category Count: ' . $expensesByCategory->count());
        \Log::info('Expenses By Category Data: ' . json_encode($expensesByCategory));

        // Calculate monthly expenses for the current year for line chart
        $monthlyData = $this->getMonthlyData('expenses', $selectedYear);

        // Debug monthly data
        \Log::info('Monthly Data: ' . json_encode($monthlyData));

        // Ensure we're converting data values to float to prevent JS errors
        if (isset($monthlyData['data']) && is_array($monthlyData['data'])) {
            $monthlyData['data'] = array_map(function($value) {
                return (float)$value;
            }, $monthlyData['data']);
        }

        return view('admin.financial_reports.expenses', compact(
            'expenses',
            'categories',
            'totalAmount',
            'expensesByCategory',
            'monthlyData',
            'selectedYear'
        ));
    }

    /**
     * Display profit reports with filtering options.
     */
    public function profits(Request $request)
    {
        // Get selected year (default to current year if not provided)
        $selectedYear = $request->input('year', Carbon::now()->year);

        // Define the date range for the query
        $startDate = $request->filled('start_date') ? $request->start_date : Carbon::now()->startOfMonth()->toDateString();
        $endDate = $request->filled('end_date') ? $request->end_date : Carbon::now()->endOfMonth()->toDateString();

        // Get total sales within the date range
        $sales = Invoice::whereBetween('created_at', [$startDate, $endDate])
            ->where(function($query) {
                $query->where('status', 'sending_to_invotek')
                      ->orWhereNull('status');
            })
            ->sum('total_price');

        // Get total returns within the date range
        $returns = Invoice::whereBetween('created_at', [$startDate, $endDate])
        ->where('status', 'returned')
        ->sum('total_price');

        // Get total expenses within the date range
        $expenses = Expense::where('status', 'approved')->whereBetween('created_at', [$startDate, $endDate])
            ->sum('amount');

        // Calculate total profit (sales - returns - expenses)
        $profit = $sales - $returns - $expenses;

        // Get daily profits within the date range for the chart
        $dailyProfits = $this->getDailyProfitData($startDate, $endDate);

        // Get monthly profit data for the year
        $monthlyProfits = $this->getMonthlyData('profits', $selectedYear);

        return view('admin.financial_reports.profits', compact(
            'sales',
            'returns',
            'expenses',
            'profit',
            'dailyProfits',
            'monthlyProfits',
            'startDate',
            'endDate',
            'selectedYear'
        ));
    }

    /**
     * Display sales reports with filtering options.
     */
    public function sales(Request $request)
    {
        // Get selected year (default to current year if not provided)
        $selectedYear = $request->input('year', Carbon::now()->year);

        // Start building the query
        $query = Invoice::with('client');

        // Apply filters if any
        if ($request->filled('status')) {
            $query->where('status', $request->status);
        }

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

        if ($request->filled('start_date') && $request->filled('end_date')) {
            $query->whereBetween('created_at', [$request->start_date, $request->end_date]);
        } elseif ($request->filled('start_date')) {
            $query->where('created_at', '>=', $request->start_date);
        } elseif ($request->filled('end_date')) {
            $query->where('created_at', '<=', $request->end_date);
        }

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

        // Get the invoices with pagination
        $invoices = $query->latest()->paginate(15);

        // Calculate total sales amount (excluding returns)
        $totalSales = clone $query;
        $totalSales = $totalSales->where(function($q) {
            $q->where('status', 'sending_to_invotek')
              ->orWhereNull('status');
        })->sum('total_price');

        // Calculate total returns amount
        $totalReturns = clone $query;
        $totalReturns = $totalReturns->where('status', 'returned')->sum('total_price');

        // Calculate sales by payment method for pie chart
        $salesByPaymentMethod = Invoice::select(
                DB::raw('CASE
                    WHEN payment_method_code = "21" THEN "021"
                    ELSE payment_method_code
                END as payment_method_code'),
                DB::raw('SUM(total_price) as total_amount')
            )
            ->where(function($q) {
                $q->where('status', 'sending_to_invotek')
                  ->orWhereNull('status');
            })
            ->groupBy(DB::raw('CASE
                WHEN payment_method_code = "21" THEN "021"
                ELSE payment_method_code
            END'))
            ->get();
        // Calculate monthly sales for the current year for line chart
        $monthlySales = $this->getMonthlyData('sales', $selectedYear);
        $monthlyReturns = $this->getMonthlyData('returns', $selectedYear);

        return view('admin.financial_reports.sales', compact(
            'invoices',
            'totalSales',
            'totalReturns',
            'salesByPaymentMethod',
            'monthlySales',
            'monthlyReturns',
            'selectedYear'
        ));
    }

    /**
     * Display top-selling products report with filtering options.
     */
    public function topProducts(Request $request)
    {
        // Define the date range for the query
        $startDate = $request->filled('start_date') ? $request->start_date : Carbon::now()->startOfMonth()->toDateString();
        $endDate = $request->filled('end_date') ? $request->end_date : Carbon::now()->endOfMonth()->toDateString();

        // Start building the query
        $query = InvoiceDetail::select(
                'product_id',
                DB::raw('SUM(quantity) as total_quantity'),
                DB::raw('SUM(quantity * price) as total_sales')
            )
            ->whereHas('invoice', function ($q) use ($startDate, $endDate) {
                $q->whereBetween('created_at', [$startDate, $endDate]);

                // Add status filter if provided
                if (request()->filled('status')) {
                    $q->where('status', request('status'));
                }
            })
            ->groupBy('product_id')
            ->with('product.category');

        // Filter by category if provided
        if ($request->filled('category_id')) {
            $query->whereHas('product', function ($q) use ($request) {
                $q->where('category_id', $request->category_id);
            });
        }

        // Order by the specified field
        $orderBy = $request->get('order_by', 'total_quantity');
        $query->orderByDesc($orderBy);

        // Get the top products
        $topProducts = $query->paginate(15);

        // Get all categories for the filter dropdown
        $categories = Category::all();

        // Get sales by category for pie chart
        $salesByCategory = InvoiceDetail::join('products', 'invoice_details.product_id', '=', 'products.id')
            ->join('categories', 'products.category_id', '=', 'categories.id')
            ->join('invoices', 'invoice_details.invoice_id', '=', 'invoices.id')
            ->whereBetween('invoices.created_at', [$startDate, $endDate])
            ->select('categories.id', 'categories.name', DB::raw('SUM(invoice_details.quantity * invoice_details.price) as total_sales'))
            ->groupBy('categories.id', 'categories.name')
            ->get();

        return view('admin.financial_reports.top_products', compact(
            'topProducts',
            'categories',
            'salesByCategory',
            'startDate',
            'endDate'
        ));
    }

    /**
     * Get monthly data for charts.
     */
    private function getMonthlyData($type, $year = null)
    {
        // Default to current year if not provided
        $year = $year ?: Carbon::now()->year;

        $months = [];
        $data = [];

        \Log::info('Getting monthly data for type: ' . $type . ' and year: ' . $year);

        for ($month = 1; $month <= 12; $month++) {
            $months[] = Carbon::createFromDate($year, $month, 1)->format('M');

            if ($type === 'expenses') {
                $monthQuery = Expense::whereYear('expense_date', $year)
                    ->whereMonth('expense_date', $month);

                \Log::info("Month {$month} SQL: " . $monthQuery->toSql());
                \Log::info("Month {$month} Bindings: " . json_encode($monthQuery->getBindings()));
                \Log::info("Month {$month} Count: " . $monthQuery->count());

                $data[] = $monthQuery->sum('amount');

                \Log::info("Month {$month} Sum: " . $data[count($data) - 1]);
            } elseif ($type === 'sales') {
                $data[] = Invoice::whereYear('created_at', $year)
                    ->whereMonth('created_at', $month)
                    ->where(function($q) {
                        $q->where('status', 'sending_to_invotek')
                          ->orWhereNull('status');
                    })
                    ->sum('total_price');
            } elseif ($type === 'returns') {
                $data[] = Invoice::whereYear('created_at', $year)
                    ->whereMonth('created_at', $month)
                    ->where('status', 'returned')
                    ->sum('total_price');
            } elseif ($type === 'profits') {
                $sales = Invoice::whereYear('created_at', $year)
                    ->whereMonth('created_at', $month)
                    ->where(function($q) {
                        $q->where('status', 'sending_to_invotek')
                          ->orWhereNull('status');
                    })
                    ->sum('total_price');

                $returns = Invoice::whereYear('created_at', $year)
                    ->whereMonth('created_at', $month)
                    ->where('status', 'returned')
                    ->sum('total_price');

                $expenses = Expense::whereYear('expense_date', $year)
                    ->whereMonth('expense_date', $month)
                    ->sum('amount');

                $data[] = $sales - $returns - $expenses;
            }
        }

        \Log::info('Monthly data results: ' . json_encode([
            'labels' => $months,
            'data' => $data
        ]));

        return [
            'labels' => $months,
            'data' => $data
        ];
    }

    /**
     * Get daily profit data for charts.
     */
    private function getDailyProfitData($startDate, $endDate)
    {
        $startDate = Carbon::parse($startDate);
        $endDate = Carbon::parse($endDate);
        $dates = [];
        $data = [];

        for ($date = $startDate; $date->lte($endDate); $date->addDay()) {
            $currentDate = $date->format('Y-m-d');
            $dates[] = $date->format('d M');

            $sales = Invoice::whereDate('created_at', $currentDate)
                ->where(function($q) {
                    $q->where('status', 'sending_to_invotek')
                      ->orWhereNull('status');
                })
                ->sum('total_price');

            $returns = Invoice::whereDate('created_at', $currentDate)
                ->where('status', 'returned')
                ->sum('total_price');

            $expenses = Expense::whereDate('expense_date', $currentDate)->sum('amount');
            $data[] = $sales - $returns - $expenses;
        }

        return [
            'labels' => $dates,
            'data' => $data
        ];
    }
}
