<?php

namespace App\Http\Controllers\Backoffice;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use App\Models\Transaction;
use App\Models\FinanceGroup;
use App\Models\Site;
use Carbon\Carbon;
use App\Support\BusinessTime;
use Illuminate\Support\Facades\DB;

class ReportsController extends Controller
{
    public function index(Request $request)
    {
        $data = $this->buildReportData($request);

        return view('backoffice.reports.index', $data);
    }

    public function export(Request $request)
    {
        $data = $this->buildReportData($request);
        $data['reportTitleName'] = setting('site_name', config('app.name'));

        $fileDateFrom = Carbon::parse($data['dateFromInput'])->format('d-m-Y');
        $fileDateTo = Carbon::parse($data['dateToInput'])->format('d-m-Y');
        $filename = "raporlar-{$fileDateFrom}_to_{$fileDateTo}.xls";

        return response()
            ->view('backoffice.reports.export', $data)
            ->header('Content-Type', 'application/vnd.ms-excel; charset=UTF-8')
            ->header('Content-Disposition', 'attachment; filename="' . $filename . '"');
    }

    private function buildReportData(Request $request): array
    {
        $rawFrom = $request->input('date_from', BusinessTime::currentBusinessDate()->format('Y-m-d'));
        $rawTo = $request->input('date_to', BusinessTime::currentBusinessDate()->format('Y-m-d'));

        $dateFromInput = $this->normalizeDateInput($rawFrom);
        $dateToInput = $this->normalizeDateInput($rawTo);

        $dateFrom = BusinessTime::startOfBusinessDay($dateFromInput);
        $dateTo = BusinessTime::endOfBusinessDay($dateToInput);
        $dateFromDisplay = Carbon::parse($dateFromInput)->format('d/m/Y');
        $dateToDisplay = Carbon::parse($dateToInput)->format('d/m/Y');
        $financeGroupId = $request->input('finance_group_id');
        $siteId = $request->input('site_id');
        $approvedStatuses = ['approved', 'completed'];

        // Base query
        $query = Transaction::whereBetween('created_at', [$dateFrom, $dateTo])
            ->whereIn('status', $approvedStatuses);

        if ($financeGroupId) {
            $query->where('finance_group_id', $financeGroupId);
        }
        if ($siteId) {
            $query->where('site_id', $siteId);
        }

        // Overall statistics (approved only)
        $totalDeposits = (clone $query)->deposits()->sum('amount');
        $totalWithdrawals = (clone $query)->withdrawals()->sum('amount');
        $completedDeposits = $totalDeposits;
        $completedWithdrawals = $totalWithdrawals;
        $approvedTransactionCount = (clone $query)->count();

        // Daily breakdown
        $dailyStats = Transaction::whereBetween('created_at', [$dateFrom, $dateTo])
            ->whereIn('status', $approvedStatuses)
            ->when($financeGroupId, function ($q) use ($financeGroupId) {
                return $q->where('finance_group_id', $financeGroupId);
            })
            ->when($siteId, function ($q) use ($siteId) {
                return $q->where('site_id', $siteId);
            })
            ->select(
                DB::raw('DATE(DATE_SUB(created_at, INTERVAL 6 HOUR)) as date'),
                DB::raw('SUM(CASE WHEN type = "deposit" THEN amount ELSE 0 END) as total_deposits'),
                DB::raw('SUM(CASE WHEN type = "withdrawal" THEN amount ELSE 0 END) as total_withdrawals'),
                DB::raw('COUNT(CASE WHEN type = "deposit" THEN 1 END) as deposit_count'),
                DB::raw('COUNT(CASE WHEN type = "withdrawal" THEN 1 END) as withdrawal_count')
            )
            ->groupBy('date')
            ->orderBy('date', 'desc')
            ->get();

        // Finance group breakdown
        $groupStats = Transaction::whereBetween('created_at', [$dateFrom, $dateTo])
            ->whereIn('status', $approvedStatuses)
            ->when($financeGroupId, function ($q) use ($financeGroupId) {
                return $q->where('finance_group_id', $financeGroupId);
            })
            ->when($siteId, function ($q) use ($siteId) {
                return $q->where('site_id', $siteId);
            })
            ->select(
                'finance_group_id',
                DB::raw('SUM(CASE WHEN type = "deposit" THEN amount ELSE 0 END) as total_deposits'),
                DB::raw('SUM(CASE WHEN type = "withdrawal" THEN amount ELSE 0 END) as total_withdrawals'),
                DB::raw('COUNT(*) as transaction_count')
            )
            ->groupBy('finance_group_id')
            ->with('financeGroup')
            ->get();

        // Site breakdown
        $siteStats = Transaction::whereBetween('created_at', [$dateFrom, $dateTo])
            ->whereIn('status', $approvedStatuses)
            ->when($financeGroupId, function ($q) use ($financeGroupId) {
                return $q->where('finance_group_id', $financeGroupId);
            })
            ->when($siteId, function ($q) use ($siteId) {
                return $q->where('site_id', $siteId);
            })
            ->select(
                'site_id',
                DB::raw('SUM(CASE WHEN type = "deposit" THEN amount ELSE 0 END) as total_deposits'),
                DB::raw('SUM(CASE WHEN type = "withdrawal" THEN amount ELSE 0 END) as total_withdrawals'),
                DB::raw('SUM(CASE WHEN type = "deposit" THEN amount ELSE 0 END) as completed_deposits'),
                DB::raw('SUM(CASE WHEN type = "withdrawal" THEN amount ELSE 0 END) as completed_withdrawals'),
                DB::raw('COUNT(*) as transaction_count')
            )
            ->groupBy('site_id')
            ->with('site')
            ->orderByDesc('transaction_count')
            ->get();

        // Approved totals by site and business date
        $siteDateApprovedStats = Transaction::whereBetween('created_at', [$dateFrom, $dateTo])
            ->whereIn('status', $approvedStatuses)
            ->when($financeGroupId, function ($q) use ($financeGroupId) {
                return $q->where('finance_group_id', $financeGroupId);
            })
            ->when($siteId, function ($q) use ($siteId) {
                return $q->where('site_id', $siteId);
            })
            ->select(
                DB::raw('DATE(DATE_SUB(created_at, INTERVAL 6 HOUR)) as date'),
                'site_id',
                DB::raw('SUM(CASE WHEN type = "deposit" THEN amount ELSE 0 END) as approved_deposits'),
                DB::raw('SUM(CASE WHEN type = "withdrawal" THEN amount ELSE 0 END) as approved_withdrawals'),
                DB::raw('COUNT(CASE WHEN type = "deposit" THEN 1 END) as approved_deposit_count'),
                DB::raw('COUNT(CASE WHEN type = "withdrawal" THEN 1 END) as approved_withdrawal_count')
            )
            ->groupBy('date', 'site_id')
            ->with('site')
            ->orderBy('date', 'desc')
            ->orderBy('site_id')
            ->get();

        // Performance by site + finance group + business date
        $siteGroupDatePerformance = Transaction::whereBetween('created_at', [$dateFrom, $dateTo])
            ->whereIn('status', $approvedStatuses)
            ->when($financeGroupId, function ($q) use ($financeGroupId) {
                return $q->where('finance_group_id', $financeGroupId);
            })
            ->when($siteId, function ($q) use ($siteId) {
                return $q->where('site_id', $siteId);
            })
            ->select(
                DB::raw('DATE(DATE_SUB(created_at, INTERVAL 6 HOUR)) as date'),
                'site_id',
                'finance_group_id',
                DB::raw('SUM(CASE WHEN type = "deposit" THEN amount ELSE 0 END) as total_deposits'),
                DB::raw('SUM(CASE WHEN type = "withdrawal" THEN amount ELSE 0 END) as total_withdrawals'),
                DB::raw('SUM(CASE WHEN type = "deposit" THEN amount ELSE 0 END) as approved_deposits'),
                DB::raw('SUM(CASE WHEN type = "withdrawal" THEN amount ELSE 0 END) as approved_withdrawals'),
                DB::raw('COUNT(*) as approved_transaction_count')
            )
            ->groupBy('date', 'site_id', 'finance_group_id')
            ->with(['site', 'financeGroup'])
            ->orderBy('date', 'desc')
            ->orderBy('site_id')
            ->orderBy('finance_group_id')
            ->get();

        $financeGroups = FinanceGroup::all();
        $sites = Site::orderBy('name')->get(['id', 'name', 'is_active']);

        $financeGroupName = null;
        if ($financeGroupId) {
            $match = $financeGroups->firstWhere('id', (int) $financeGroupId);
            $financeGroupName = $match ? $match->name : null;
        }

        $siteName = null;
        if ($siteId) {
            $selectedSite = $sites->firstWhere('id', (int) $siteId);
            $siteName = $selectedSite ? $selectedSite->name : null;
        }

        return compact(
            'totalDeposits',
            'totalWithdrawals',
            'completedDeposits',
            'completedWithdrawals',
            'approvedTransactionCount',
            'dailyStats',
            'groupStats',
            'siteStats',
            'siteDateApprovedStats',
            'siteGroupDatePerformance',
            'financeGroups',
            'sites',
            'dateFromInput',
            'dateToInput',
            'dateFromDisplay',
            'dateToDisplay',
            'financeGroupId',
            'financeGroupName',
            'siteId',
            'siteName'
        );
    }

    private function normalizeDateInput(string $value): string
    {
        if (str_contains($value, '/')) {
            return Carbon::createFromFormat('d/m/Y', $value)->format('Y-m-d');
        }

        return Carbon::parse($value)->format('Y-m-d');
    }
}
