<?php

namespace App\Http\Controllers;

use App\Models\Appointment;
use App\Models\Locker;
use App\Models\LockerRegistration;
use App\Models\LockerRegistrationItem;
use App\Services\ReportService;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;
use Barryvdh\DomPDF\Facade\Pdf;
use Illuminate\Support\Facades\Schema;

class ReportController extends Controller
{
    protected $reportService;

    public function __construct(ReportService $reportService)
    {
        $this->reportService = $reportService;
    }

    /**
     * Main reports dashboard
     */
    public function index()
    {
        return view('reports.index');
    }

    public function lockers()
    {
        return view('reports.lockers');
    }

    public function referrals(Request $request)
    {
        $fromDate = $request->get('from_date', now()->subDays(30)->format('Y-m-d'));
        $toDate = $request->get('to_date', now()->format('Y-m-d'));
        $status = $request->get('status');
        $referredBy = $request->get('referred_by_client_id');

        $baseQuery = LockerRegistration::with(['client', 'locker', 'membershipPlan', 'referrer'])
            ->whereHas('client', function ($query) {
                $query->byTenant();
            })
            ->whereNotNull('referred_member_by')
            ->when($fromDate, fn($query) => $query->whereDate('registration_date', '>=', $fromDate))
            ->when($toDate, fn($query) => $query->whereDate('registration_date', '<=', $toDate))
            ->when($status, fn($query) => $query->where('status', $status))
            ->when($referredBy, fn($query) => $query->where('referred_member_by', $referredBy));

        $commissionUsedColumn = Schema::hasColumn('locker_registrations', 'commission_used')
            ? 'commission_used'
            : 'total_commission_used';

        $summaryQuery = clone $baseQuery;
        $summary = [
            'total_referrals' => $summaryQuery->count(),
            'commission_earned' => $summaryQuery->sum('referred_commission'),
            'commission_used' => $summaryQuery->sum($commissionUsedColumn),
            'balance' => $summaryQuery->sum('referred_commission') - $summaryQuery->sum($commissionUsedColumn),
        ];

        $records = $baseQuery->orderByDesc('registration_date')->paginate(25)->withQueryString();

        // Debug: Check if referrer relationships are loaded
        \Log::info('Query count: ' . $baseQuery->count());
        \Log::info('Records loaded: ' . $records->count());
        foreach ($records->take(3) as $record) {
            \Log::info('Record ID: ' . $record->id . ', Referrer ID: ' . $record->referred_member_by . ', Referrer loaded: ' . ($record->relationLoaded('referrer') ? 'YES' : 'NO'));
            \Log::info('Referrer name: ' . ($record->referrer ? $record->referrer->name : 'NULL'));
        }

        $referrerIds = LockerRegistration::select('referred_member_by')
            ->whereNotNull('referred_member_by')
            ->distinct()
            ->pluck('referred_member_by')
            ->filter()
            ->toArray();

        $customer_members = \App\Models\CustomerMember::whereIn('id', $referrerIds)
            ->whereHas('client', function ($query) {
                $query->byTenant();
            })
            ->active()
            ->orderBy('name')
            ->get();


        // Return JSON for AJAX requests
        if ($request->ajax() || $request->wantsJson()) {
            $data = collect($records->items())->map(function ($record) {
                $used = $record->commission_used ?? $record->total_commission_used ?? 0;
                $balance = ($record->referred_commission ?? 0) - $used;

                return array_merge($record->toArray(), [
                    'referrer_name' => optional($record->referrer)->name,
                    'commission_used' => $used,
                    'commission_balance' => $balance,
                ]);
            })->all();

            return response()->json([
                'success' => true,
                'data' => $data,
                'summary' => $summary,
                'pagination' => [
                    'current_page' => $records->currentPage(),
                    'last_page' => $records->lastPage(),
                    'per_page' => $records->perPage(),
                    'total' => $records->total(),
                    'from' => $records->firstItem(),
                    'to' => $records->lastItem(),
                ]
            ]);
        }

        return view('reports.referrals', compact('records', 'summary', 'fromDate', 'toDate', 'status', 'referredBy', 'customer_members'));
    }

    public function lockerExpiry(Request $request)
    {
        $records = collect();
        $fromDate = $request->input('from_date');
        $toDate = $request->input('to_date');
        $selectedRange = $request->input('range');

        $today = Carbon::today();
        $end30 = $today->copy()->addDays(30);
        $end15 = $today->copy()->addDays(15);
        $end1 = $today->copy()->addDays(1);

        $count30 = LockerRegistrationItem::whereDate('end_date', '>=', $today)
            ->whereDate('end_date', '<=', $end30)
            ->count();

        $count15 = LockerRegistrationItem::whereDate('end_date', '>=', $today)
            ->whereDate('end_date', '<=', $end15)
            ->count();

        $count1 = LockerRegistrationItem::whereDate('end_date', '>=', $today)
            ->whereDate('end_date', '<=', $end1)
            ->count();

        $expiredIds = DB::table('locker_registration_items as item')
            ->leftJoin('locker_registration_items as next_item', function ($join) {
                $join->on('next_item.registration_id', '=', 'item.registration_id')
                    ->on('next_item.locker_id', '=', 'item.locker_id')
                    ->whereColumn('next_item.start_date', '>', 'item.end_date');
            })
            ->whereDate('item.end_date', '<', $today)
            ->whereNull('next_item.id')
            ->pluck('item.id');

        $countExpired = LockerRegistrationItem::whereIn('id', $expiredIds)->count();

        if ($selectedRange || $request->filled('from_date') || $request->filled('to_date')) {
            $query = LockerRegistrationItem::with(['registration.client', 'locker'])
                ->whereNotNull('end_date');

            if ($selectedRange === '30') {
                $query->whereDate('end_date', '>=', $today)
                    ->whereDate('end_date', '<=', $end30);
            } elseif ($selectedRange === '15') {
                $query->whereDate('end_date', '>=', $today)
                    ->whereDate('end_date', '<=', $end15);
            } elseif ($selectedRange === '1') {
                $query->whereDate('end_date', '>=', $today)
                    ->whereDate('end_date', '<=', $end1);
            } elseif ($selectedRange === 'expired') {
                $query->whereIn('id', $expiredIds);
            }

            if ($fromDate) {
                $query->whereDate('end_date', '>=', $fromDate);
            }

            if ($toDate) {
                $query->whereDate('end_date', '<=', $toDate);
            }

            $records = $query->orderBy('end_date')->get();
        }

        return view('reports.locker-expiry', compact(
            'records',
            'fromDate',
            'toDate',
            'selectedRange',
            'count30',
            'count15',
            'count1',
            'countExpired'
        ));
    }

    public function exportLockerExpiry(Request $request)
    {
        $today = Carbon::today();
        $end30 = $today->copy()->addDays(30);
        $end15 = $today->copy()->addDays(15);
        $end1 = $today->copy()->addDays(1);

        $expiredIds = DB::table('locker_registration_items as item')
            ->leftJoin('locker_registration_items as next_item', function ($join) {
                $join->on('next_item.registration_id', '=', 'item.registration_id')
                    ->on('next_item.locker_id', '=', 'item.locker_id')
                    ->whereColumn('next_item.start_date', '>', 'item.end_date');
            })
            ->whereDate('item.end_date', '<', $today)
            ->whereNull('next_item.id')
            ->pluck('item.id');

        $query = LockerRegistrationItem::with(['registration.client', 'locker'])
            ->whereNotNull('end_date');

        $selectedRange = $request->input('range');
        if ($selectedRange === '30') {
            $query->whereDate('end_date', '>=', $today)->whereDate('end_date', '<=', $end30);
        } elseif ($selectedRange === '15') {
            $query->whereDate('end_date', '>=', $today)->whereDate('end_date', '<=', $end15);
        } elseif ($selectedRange === '1') {
            $query->whereDate('end_date', '>=', $today)->whereDate('end_date', '<=', $end1);
        } elseif ($selectedRange === 'expired') {
            $query->whereIn('id', $expiredIds);
        }

        if ($request->filled('from_date')) {
            $query->whereDate('end_date', '>=', $request->from_date);
        }

        if ($request->filled('to_date')) {
            $query->whereDate('end_date', '<=', $request->to_date);
        }

        $records = $query->orderBy('end_date')->get();

        $filename = "locker_expiry_report_" . now()->format('Y-m-d_H-i-s') . ".xls";
        $headers = [
            'Content-Type' => 'application/vnd.ms-excel',
            'Content-Disposition' => "attachment; filename=\"$filename\"",
        ];

        $callback = function () use ($records, $request, $selectedRange) {
            $file = fopen('php://output', 'w');
            echo pack("CCC", 0xEF, 0xBB, 0xBF);

            fputcsv($file, ['LOCKER EXPIRY REPORT']);
            fputcsv($file, ['Generated on:', now()->format('Y-m-d H:i:s')]);
            fputcsv($file, ['Total Records:', $records->count()]);
            fputcsv($file, []);

            if ($selectedRange || $request->filled('from_date') || $request->filled('to_date')) {
                fputcsv($file, ['Filters Applied:']);
                if ($selectedRange) {
                    fputcsv($file, ['Range:', $selectedRange]);
                }
                if ($request->filled('from_date')) {
                    fputcsv($file, ['From Date:', $request->from_date]);
                }
                if ($request->filled('to_date')) {
                    fputcsv($file, ['To Date:', $request->to_date]);
                }
                fputcsv($file, []);
            }

            fputcsv($file, [
                'Locker Number',
                'Client Name',
                'Client Phone',
                'Start Date',
                'End Date',
                'Amount',
                'Status'
            ]);

            foreach ($records as $record) {
                $client = optional(optional($record->registration)->client);
                $locker = optional($record->locker);

                fputcsv($file, [
                    $locker->locker_number ?? 'N/A',
                    $client->name ?? 'N/A',
                    $client->phone ?? 'N/A',
                    $record->start_date ? Carbon::parse($record->start_date)->format('Y-m-d') : 'N/A',
                    $record->end_date ? Carbon::parse($record->end_date)->format('Y-m-d') : 'N/A',
                    $record->amount ?? 0,
                    $record->status ?? 'N/A'
                ]);
            }

            fclose($file);
        };

        return response()->stream($callback, 200, $headers);
    }

    public function exportLockerExpiryTXT(Request $request)
    {
        $today = Carbon::today();
        $end30 = $today->copy()->addDays(30);
        $end15 = $today->copy()->addDays(15);
        $end1 = $today->copy()->addDays(1);

        $expiredIds = DB::table('locker_registration_items as item')
            ->leftJoin('locker_registration_items as next_item', function ($join) {
                $join->on('next_item.registration_id', '=', 'item.registration_id')
                    ->on('next_item.locker_id', '=', 'item.locker_id')
                    ->whereColumn('next_item.start_date', '>', 'item.end_date');
            })
            ->whereDate('item.end_date', '<', $today)
            ->whereNull('next_item.id')
            ->pluck('item.id');

        $query = LockerRegistrationItem::with(['registration.client', 'locker'])
            ->whereNotNull('end_date');

        $selectedRange = $request->input('range');
        if ($selectedRange === '30') {
            $query->whereDate('end_date', '>=', $today)->whereDate('end_date', '<=', $end30);
        } elseif ($selectedRange === '15') {
            $query->whereDate('end_date', '>=', $today)->whereDate('end_date', '<=', $end15);
        } elseif ($selectedRange === '1') {
            $query->whereDate('end_date', '>=', $today)->whereDate('end_date', '<=', $end1);
        } elseif ($selectedRange === 'expired') {
            $query->whereIn('id', $expiredIds);
        }

        if ($request->filled('from_date')) {
            $query->whereDate('end_date', '>=', $request->from_date);
        }

        if ($request->filled('to_date')) {
            $query->whereDate('end_date', '<=', $request->to_date);
        }

        $records = $query->orderBy('end_date')->get();

        $filename = "locker_expiry_report_" . date('Y-m-d_H-i') . ".txt";
        $headers = [
            'Content-Type' => 'text/plain',
            'Content-Disposition' => "attachment; filename=\"$filename\"",
        ];

        $callback = function () use ($records, $request, $selectedRange) {
            echo "LOCKER EXPIRY REPORT\n";
            echo "===================\n\n";
            echo "Generated on: " . now()->format('Y-m-d H:i') . "\n";
            echo "Total Records: " . $records->count() . "\n";

            if ($selectedRange || $request->filled('from_date') || $request->filled('to_date')) {
                echo "\nFilters Applied:\n";
                if ($selectedRange) {
                    echo "- Range: $selectedRange\n";
                }
                if ($request->filled('from_date')) {
                    echo "- From Date: " . $request->from_date . "\n";
                }
                if ($request->filled('to_date')) {
                    echo "- To Date: " . $request->to_date . "\n";
                }
            }

            echo "\n" . str_repeat("=", 100) . "\n";
            echo str_pad("Locker", 12) . " | ";
            echo str_pad("Client", 20) . " | ";
            echo str_pad("Phone", 15) . " | ";
            echo str_pad("Start", 12) . " | ";
            echo str_pad("End", 12) . " | ";
            echo str_pad("Amount", 10) . " | ";
            echo "Status\n";
            echo str_repeat("-", 100) . "\n";

            foreach ($records as $record) {
                $client = optional(optional($record->registration)->client);
                $locker = optional($record->locker);
                $start = $record->start_date ? Carbon::parse($record->start_date)->format('Y-m-d') : 'N/A';
                $end = $record->end_date ? Carbon::parse($record->end_date)->format('Y-m-d') : 'N/A';

                echo str_pad($locker->locker_number ?? 'N/A', 12) . " | ";
                echo str_pad($client->name ?? 'N/A', 20) . " | ";
                echo str_pad($client->phone ?? 'N/A', 15) . " | ";
                echo str_pad($start, 12) . " | ";
                echo str_pad($end, 12) . " | ";
                echo str_pad((string)($record->amount ?? 0), 10) . " | ";
                echo ($record->status ?? 'N/A') . "\n";
            }

            echo "\nReport generated by MaySafeBox System\n";
        };

        return response()->stream($callback, 200, $headers);
    }

    public function lockersReport(Request $request)
    {
        $query = Locker::query();

        if ($request->filled('status')) {
            $query->where('status', $request->status);
        }

        if ($request->filled('size')) {
            $query->where('locker_size', $request->size);
        }

        if ($request->filled('type')) {
            $query->where('locker_type', $request->type);
        }

        $count = $query->count();
        $lockers = $query->get();

        return response()->json([
            'data' => $lockers,
            'count' => $count
        ]);
    }

    /**
     * Export lockers report as XLS
     */
    public function exportLockers(Request $request)
    {
        $query = Locker::query();

        if ($request->filled('status')) {
            $query->where('status', $request->status);
        }

        if ($request->filled('size')) {
            $query->where('locker_size', $request->size);
        }

        if ($request->filled('type')) {
            $query->where('locker_type', $request->type);
        }

        $lockers = $query->get();

        $filename = "lockers_report_" . now()->format('Y-m-d_H-i-s') . ".xls";
        $headers = [
            'Content-Type' => 'application/vnd.ms-excel',
            'Content-Disposition' => "attachment; filename=\"$filename\"",
        ];

        $callback = function () use ($lockers) {
            $file = fopen('php://output', 'w');

            // Excel header for proper formatting
            echo pack("CCC", 0xEF, 0xBB, 0xBF);

            // CSV header
            fputcsv($file, [
                'Locker Number',
                'Locker Group',
                'Type',
                'Size',
                'Location',
                'Status',
                'Amount',
                'Created Date'
            ]);

            // CSV data
            foreach ($lockers as $locker) {
                fputcsv($file, [
                    $locker->locker_number ?? 'N/A',
                    'Group ' . ($locker->locker_group_id ?? 'N/A'),
                    $locker->locker_type ?? 'N/A',
                    $locker->locker_size ?? 'N/A',
                    $locker->locker_row_column ?? 'N/A',
                    ucfirst($locker->status ?? 'N/A'),
                    '$' . number_format($locker->locker_amount ?? 0, 2),
                    $locker->created_at ? $locker->created_at->format('Y-m-d H:i:s') : 'N/A'
                ]);
            }

            fclose($file);
        };

        return response()->stream($callback, 200, $headers);
    }

    /**
     * Export lockers report as PDF
     */
    public function exportLockersPDF(Request $request)
    {
        $query = Locker::query();

        if ($request->filled('status')) {
            $query->where('status', $request->status);
        }

        if ($request->filled('size')) {
            $query->where('locker_size', $request->size);
        }

        if ($request->filled('type')) {
            $query->where('locker_type', $request->type);
        }

        $lockers = $query->get();

        // Build filter summary for header
        $filterSummary = 'All Lockers';
        $activeFilters = [];

        if ($request->filled('status')) {
            $activeFilters[] = 'Status: ' . ucfirst($request->status);
        }
        if ($request->filled('size')) {
            $size = $request->size === 'Extra large' ? 'Extra Large' : ucfirst($request->size);
            $activeFilters[] = 'Size: ' . $size;
        }
        if ($request->filled('type')) {
            $activeFilters[] = 'Type: ' . $request->type;
        }

        if (!empty($activeFilters)) {
            $filterSummary = implode(', ', $activeFilters);
        }

        $data = [
            'lockers' => $lockers,
            'filterSummary' => $filterSummary,
            'generatedOn' => now()->format('Y-m-d H:i:s'),
            'totalCount' => $lockers->count()
        ];

        $filename = "lockers_report_" . now()->format('Y-m-d_H-i-s') . ".pdf";

        $pdf = PDF::loadView('reports.lockers-pdf', $data);
        return $pdf->download($filename);
    }

    /**
     * Export lockers report as TXT
     */
    public function exportLockersTXT(Request $request)
    {
        $query = Locker::query();

        if ($request->filled('status')) {
            $query->where('status', $request->status);
        }

        if ($request->filled('size')) {
            $query->where('locker_size', $request->size);
        }

        if ($request->filled('type')) {
            $query->where('locker_type', $request->type);
        }

        $lockers = $query->get();

        // Build filter summary
        $filterParts = [];
        if ($request->filled('status')) $filterParts[] = 'Status: ' . ucfirst($request->status);
        if ($request->filled('size')) {
            $size = $request->size === 'Extra large' ? 'Extra Large' : ucfirst($request->size);
            $filterParts[] = 'Size: ' . $size;
        }
        if ($request->filled('type')) $filterParts[] = 'Type: ' . $request->type;

        $filterSummary = empty($filterParts) ? 'All Lockers' : implode(', ', $filterParts);

        $filename = "lockers_report_" . date('Y-m-d_H-i') . ".txt";
        $headers = [
            'Content-Type' => 'text/plain',
            'Content-Disposition' => "attachment; filename=\"$filename\"",
        ];

        $callback = function () use ($lockers, $filterSummary) {
            echo "LOCKERS REPORT\n";
            echo "================\n\n";
            echo "Generated on: " . now()->format('Y-m-d H:i') . "\n";
            echo "Filters: $filterSummary\n";
            echo "Total Lockers: " . $lockers->count() . "\n\n";
            echo "LOCKER DETAILS\n";
            echo "==============\n\n";

            // Table header
            echo str_pad("Locker Number", 15) . " | ";
            echo str_pad("Type", 12) . " | ";
            echo str_pad("Size", 10) . " | ";
            echo str_pad("Status", 12) . " | ";
            echo str_pad("Amount", 10) . " | ";
            echo "Created Date\n";
            echo str_repeat("-", 70) . "\n";

            // Data rows
            foreach ($lockers as $locker) {
                echo str_pad($locker->locker_number ?? 'N/A', 15) . " | ";
                echo str_pad($locker->locker_type ?? 'N/A', 12) . " | ";
                echo str_pad($locker->locker_size ?? 'N/A', 10) . " | ";
                echo str_pad(ucfirst($locker->status ?? 'N/A'), 12) . " | ";
                echo str_pad('$' . number_format($locker->locker_amount ?? 0, 2), 10) . " | ";
                echo ($locker->created_at ? $locker->created_at->format('Y-m-d H:i:s') : 'N/A') . "\n";
            }

            echo "\n\nReport generated by MaySafeBox System\n";
        };

        return response()->stream($callback, 200, $headers);
    }

    public function appointments()
    {
        return view('reports.appointments');
    }

    public function appointmentsReport(Request $request)
    {
        // return response()->json([
        //     'status' => 'success',
        //     'message' => 'Appointments report generated successfully',
        //     'data' => $request->all()
        // ]);

        $appointments = Appointment::query();

        if ($request->filled('status')) {
            $appointments->where('status', $request->status);
        }

        // Filter by date range using from_date and to_date
        if ($request->filled('from_date')) {
            $appointments->whereDate('appointment_date', '>=', $request->from_date);
        }

        if ($request->filled('to_date')) {
            $appointments->whereDate('appointment_date', '<=', $request->to_date);
        }

        $appointments = $appointments->with(['client'])->get();
        return response()->json([
            'data' => $appointments,
            'count' => $appointments->count()
        ]);
    }

    /**
     * Analytics dashboard
     */
    public function analytics(Request $request)
    {
        $period = $request->get('period', '30days');
        $analytics = $this->reportService->generateDashboardAnalytics($period);

        return view('reports.analytics', compact('analytics', 'period'));
    }

    /**
     * Attendance report page
     */
    public function attendance(Request $request)
    {
        $startDate = $request->get('start_date', now()->subDays(30)->format('Y-m-d'));
        $endDate = $request->get('end_date', now()->format('Y-m-d'));

        $filters = [
            'employee_id' => $request->get('employee_id'),
            'department_id' => $request->get('department_id'),
            'status' => $request->get('status')
        ];

        $report = $this->reportService->generateAttendanceReport($startDate, $endDate, $filters);

        // Get filter options
        $employees = \App\Models\User::byTenant()->active()->get();
        $departments = \App\Models\Department::byTenant()->get();

        return view('reports.attendance', compact('report', 'startDate', 'endDate', 'filters', 'employees', 'departments'));
    }

    /**
     * Transit report page
     */
    public function transit(Request $request)
    {
        $startDate = $request->get('start_date', now()->subDays(30)->format('Y-m-d'));
        $endDate = $request->get('end_date', now()->format('Y-m-d'));

        $filters = [
            'employee_id' => $request->get('employee_id'),
            'client_id' => $request->get('client_id'),
            'status' => $request->get('status')
        ];

        $report = $this->reportService->generateTransitReport($startDate, $endDate, $filters);

        // Get filter options
        $employees = \App\Models\User::byTenant()->active()->get();
        $clients = \App\Models\Client::byTenant()->active()->get();

        return view('reports.transit', compact('report', 'startDate', 'endDate', 'filters', 'employees', 'clients'));
    }

    /**
     * Geofence violations report page
     */
    public function geofence(Request $request)
    {
        $startDate = $request->get('start_date', now()->subDays(30)->format('Y-m-d'));
        $endDate = $request->get('end_date', now()->format('Y-m-d'));

        $filters = [
            'geofence_id' => $request->get('geofence_id'),
            'employee_id' => $request->get('employee_id'),
            'violation_type' => $request->get('violation_type')
        ];

        $report = $this->reportService->generateGeofenceReport($startDate, $endDate, $filters);

        // Get filter options
        $geofences = \App\Models\Geofence::byTenant()->active()->get();
        $employees = \App\Models\User::byTenant()->active()->get();

        return view('reports.geofence', compact('report', 'startDate', 'endDate', 'filters', 'geofences', 'employees'));
    }

    /**
     * Employee performance report
     */
    public function employeePerformance(Request $request)
    {
        $period = $request->get('period', '30days');
        $employeeId = $request->get('employee_id');

        $endDate = now();
        $startDate = match ($period) {
            '7days' => now()->subDays(7),
            '30days' => now()->subDays(30),
            '90days' => now()->subDays(90),
            '1year' => now()->subYear(),
            default => now()->subDays(30)
        };

        if ($employeeId) {
            $employee = \App\Models\User::byTenant()->findOrFail($employeeId);
            $performance = $this->getEmployeeDetailedPerformance($employee, $startDate, $endDate);
        } else {
            $performance = $this->getAllEmployeesPerformance($startDate, $endDate);
        }

        $employees = \App\Models\User::byTenant()->active()->get();

        return view('reports.employee-performance', compact('performance', 'period', 'startDate', 'endDate', 'employees', 'employeeId'));
    }

    /**
     * Client performance report
     */
    public function clientPerformance(Request $request)
    {
        $period = $request->get('period', '30days');
        $clientId = $request->get('client_id');

        $endDate = now();
        $startDate = match ($period) {
            '7days' => now()->subDays(7),
            '30days' => now()->subDays(30),
            '90days' => now()->subDays(90),
            '1year' => now()->subYear(),
            default => now()->subDays(30)
        };

        if ($clientId) {
            $client = \App\Models\Client::byTenant()->findOrFail($clientId);
            $performance = $this->getClientDetailedPerformance($client, $startDate, $endDate);
        } else {
            $performance = $this->getAllClientsPerformance($startDate, $endDate);
        }

        $clients = \App\Models\Client::byTenant()->active()->get();

        return view('reports.client-performance', compact('performance', 'period', 'startDate', 'endDate', 'clients', 'clientId'));
    }

    /**
     * Export attendance report
     */
    public function exportAttendance(Request $request)
    {
        $startDate = $request->get('start_date', now()->subDays(30)->format('Y-m-d'));
        $endDate = $request->get('end_date', now()->format('Y-m-d'));

        $filters = [
            'employee_id' => $request->get('employee_id'),
            'department_id' => $request->get('department_id'),
            'status' => $request->get('status')
        ];

        $report = $this->reportService->generateAttendanceReport($startDate, $endDate, $filters);

        $filename = "attendance_report_{$startDate}_to_{$endDate}.csv";
        $headers = [
            'Content-Type' => 'text/csv',
            'Content-Disposition' => "attachment; filename=\"$filename\"",
        ];

        $callback = function () use ($report) {
            $file = fopen('php://output', 'w');

            // CSV header
            fputcsv($file, [
                'Employee ID',
                'Employee Name',
                'Department',
                'Date',
                'Status',
                'Check In',
                'Check Out',
                'Working Hours',
                'Overtime Hours'
            ]);

            // CSV data
            foreach ($report['details'] as $attendance) {
                fputcsv($file, [
                    $attendance->employee->profile->employee_id ?? 'N/A',
                    $attendance->employee->full_name,
                    $attendance->employee->departments->pluck('name')->join(', ') ?: 'N/A',
                    $attendance->date,
                    $attendance->status,
                    $attendance->check_in?->format('h:i A') ?? 'N/A',
                    $attendance->check_out?->format('h:i A') ?? 'N/A',
                    $attendance->working_hours ?? 'N/A',
                    $attendance->overtime_hours ?? 'N/A'
                ]);
            }

            fclose($file);
        };

        return response()->stream($callback, 200, $headers);
    }

    /**
     * Export transit report
     */
    public function exportTransit(Request $request)
    {
        $startDate = $request->get('start_date', now()->subDays(30)->format('Y-m-d'));
        $endDate = $request->get('end_date', now()->format('Y-m-d'));

        $filters = [
            'employee_id' => $request->get('employee_id'),
            'client_id' => $request->get('client_id'),
            'status' => $request->get('status')
        ];

        $report = $this->reportService->generateTransitReport($startDate, $endDate, $filters);

        $filename = "transit_report_{$startDate}_to_{$endDate}.csv";
        $headers = [
            'Content-Type' => 'text/csv',
            'Content-Disposition' => "attachment; filename=\"$filename\"",
        ];

        $callback = function () use ($report) {
            $file = fopen('php://output', 'w');

            // CSV header
            fputcsv($file, [
                'Employee ID',
                'Employee Name',
                'Client',
                'From Location',
                'To Location',
                'Start Time',
                'Estimated Arrival',
                'Actual Arrival',
                'Distance (km)',
                'Status',
                'Vehicle',
                'Purpose'
            ]);

            // CSV data
            foreach ($report['details'] as $transit) {
                fputcsv($file, [
                    $transit->employee->profile->employee_id ?? 'N/A',
                    $transit->employee->full_name,
                    $transit->client?->name ?? 'N/A',
                    $transit->from_location,
                    $transit->to_location,
                    $transit->start_time->format('Y-m-d H:i'),
                    $transit->estimated_arrival?->format('Y-m-d H:i') ?? 'N/A',
                    $transit->actual_arrival?->format('Y-m-d H:i') ?? 'N/A',
                    $transit->distance ?? 'N/A',
                    $transit->status,
                    $transit->vehicle ?? 'N/A',
                    $transit->purpose ?? 'N/A'
                ]);
            }

            fclose($file);
        };

        return response()->stream($callback, 200, $headers);
    }

    /**
     * Export geofence violations report
     */
    public function exportGeofence(Request $request)
    {
        $startDate = $request->get('start_date', now()->subDays(30)->format('Y-m-d'));
        $endDate = $request->get('end_date', now()->format('Y-m-d'));

        $filters = [
            'geofence_id' => $request->get('geofence_id'),
            'employee_id' => $request->get('employee_id'),
            'violation_type' => $request->get('violation_type')
        ];

        $report = $this->reportService->generateGeofenceReport($startDate, $endDate, $filters);

        $filename = "geofence_violations_report_{$startDate}_to_{$endDate}.csv";
        $headers = [
            'Content-Type' => 'text/csv',
            'Content-Disposition' => "attachment; filename=\"$filename\"",
        ];

        $callback = function () use ($report) {
            $file = fopen('php://output', 'w');

            // CSV header
            fputcsv($file, [
                'Employee ID',
                'Employee Name',
                'Geofence',
                'Violation Type',
                'Violation Time',
                'Latitude',
                'Longitude',
                'Status',
                'Resolution Notes',
                'Action Taken',
                'Resolved By',
                'Resolved At'
            ]);

            // CSV data
            foreach ($report['details'] as $violation) {
                fputcsv($file, [
                    $violation->employee->profile->employee_id ?? 'N/A',
                    $violation->employee->full_name,
                    $violation->geofence->name,
                    $violation->violation_type,
                    $violation->violation_time->format('Y-m-d H:i'),
                    $violation->latitude,
                    $violation->longitude,
                    $violation->is_resolved ? 'Resolved' : 'Unresolved',
                    $violation->resolution_notes ?? 'N/A',
                    $violation->action_taken ?? 'N/A',
                    $violation->resolvedBy?->full_name ?? 'N/A',
                    $violation->resolved_at?->format('Y-m-d H:i') ?? 'N/A'
                ]);
            }

            fclose($file);
        };

        return response()->stream($callback, 200, $headers);
    }

    /**
     * API endpoint for real-time dashboard data
     */
    public function dashboardData(Request $request)
    {
        $period = $request->get('period', '30days');
        $analytics = $this->reportService->generateDashboardAnalytics($period);

        return response()->json([
            'success' => true,
            'data' => $analytics
        ]);
    }

    /**
     * API endpoint for chart data
     */
    public function chartData(Request $request)
    {
        $type = $request->get('type');
        $period = $request->get('period', '30days');

        $endDate = now();
        $startDate = match ($period) {
            '7days' => now()->subDays(7),
            '30days' => now()->subDays(30),
            '90days' => now()->subDays(90),
            '1year' => now()->subYear(),
            default => now()->subDays(30)
        };

        $data = match ($type) {
            'attendance_trends' => $this->getAttendanceChartData($startDate, $endDate),
            'transit_trends' => $this->getTransitChartData($startDate, $endDate),
            'violation_trends' => $this->getViolationChartData($startDate, $endDate),
            'department_performance' => $this->getDepartmentChartData($startDate, $endDate),
            default => []
        };

        return response()->json([
            'success' => true,
            'data' => $data
        ]);
    }

    // Helper methods for detailed performance reports
    private function getEmployeeDetailedPerformance($employee, $startDate, $endDate)
    {
        $attendances = \App\Models\Attendance::where('employee_id', $employee->id)
            ->whereBetween('date', [$startDate, $endDate])
            ->get();

        $transits = \App\Models\Transit::where('employee_id', $employee->id)
            ->whereBetween('start_time', [$startDate, $endDate])
            ->get();

        $violations = \App\Models\GeofenceViolation::where('employee_id', $employee->id)
            ->whereBetween('violation_time', [$startDate, $endDate])
            ->get();

        return [
            'employee' => $employee,
            'attendance_summary' => $this->reportService->calculateAttendanceSummary($attendances),
            'transit_summary' => $this->reportService->calculateTransitSummary($transits),
            'violation_summary' => $this->reportService->calculateViolationSummary($violations),
            'performance_score' => $this->calculateEmployeePerformanceScore($attendances, $transits, $violations)
        ];
    }

    private function getAllEmployeesPerformance($startDate, $endDate)
    {
        $employees = \App\Models\User::byTenant()->active()->get();
        $performanceData = [];

        foreach ($employees as $employee) {
            $performanceData[] = $this->getEmployeeDetailedPerformance($employee, $startDate, $endDate);
        }

        // Sort by performance score
        usort($performanceData, function ($a, $b) {
            return $b['performance_score'] <=> $a['performance_score'];
        });

        return $performanceData;
    }

    private function getClientDetailedPerformance($client, $startDate, $endDate)
    {
        $transits = \App\Models\Transit::where('client_id', $client->id)
            ->whereBetween('start_time', [$startDate, $endDate])
            ->get();

        $violations = \App\Models\GeofenceViolation::whereHas('geofence', function ($query) use ($client) {
            $query->where('client_id', $client->id);
        })->whereBetween('violation_time', [$startDate, $endDate])->get();

        return [
            'client' => $client,
            'transit_summary' => $this->reportService->calculateTransitSummary($transits),
            'violation_summary' => $this->reportService->calculateViolationSummary($violations),
            'assigned_employees' => $transits->pluck('employee_id')->unique()->count(),
            'performance_metrics' => $this->calculateClientPerformanceMetrics($transits, $violations)
        ];
    }

    private function getAllClientsPerformance($startDate, $endDate)
    {
        $clients = \App\Models\Client::byTenant()->active()->get();
        $performanceData = [];

        foreach ($clients as $client) {
            $performanceData[] = $this->getClientDetailedPerformance($client, $startDate, $endDate);
        }

        return $performanceData;
    }

    private function calculateEmployeePerformanceScore($attendances, $transits, $violations)
    {
        $attendanceScore = 0;
        $transitScore = 0;
        $violationScore = 0;

        // Attendance score (40% weight)
        if ($attendances->isNotEmpty()) {
            $presentCount = $attendances->where('status', 'present')->count();
            $attendanceRate = ($presentCount / $attendances->count()) * 100;
            $attendanceScore = ($attendanceRate / 100) * 40;
        }

        // Transit score (35% weight)
        if ($transits->isNotEmpty()) {
            $completedCount = $transits->where('status', 'arrived')->count();
            $completionRate = ($completedCount / $transits->count()) * 100;
            $transitScore = ($completionRate / 100) * 35;
        }

        // Violation score (25% weight) - fewer violations = higher score
        $violationScore = max(0, 25 - ($violations->count() * 2));

        return round($attendanceScore + $transitScore + $violationScore, 2);
    }

    private function calculateClientPerformanceMetrics($transits, $violations)
    {
        return [
            'service_efficiency' => $transits->isNotEmpty()
                ? round(($transits->where('status', 'arrived')->count() / $transits->count()) * 100, 2)
                : 0,
            'security_compliance' => 100 - min(100, ($violations->count() * 10)),
            'employee_utilization' => $transits->pluck('employee_id')->unique()->count(),
            'total_distance' => $transits->sum('distance')
        ];
    }

    // Chart data methods
    private function getAttendanceChartData($startDate, $endDate)
    {
        $attendances = \App\Models\Attendance::byTenant()
            ->whereBetween('date', [$startDate, $endDate])
            ->get()
            ->groupBy('date');

        $labels = [];
        $presentData = [];
        $absentData = [];

        $period = \Carbon\CarbonPeriod::create($startDate, $endDate);
        foreach ($period as $date) {
            $dateStr = $date->format('Y-m-d');
            $dayAttendances = $attendances->get($dateStr, collect());

            $labels[] = $date->format('M d');
            $presentData[] = $dayAttendances->where('status', 'present')->count();
            $absentData[] = $dayAttendances->where('status', 'absent')->count();
        }

        return [
            'labels' => $labels,
            'datasets' => [
                [
                    'label' => 'Present',
                    'data' => $presentData,
                    'backgroundColor' => 'rgba(75, 192, 192, 0.2)',
                    'borderColor' => 'rgba(75, 192, 192, 1)',
                    'borderWidth' => 1
                ],
                [
                    'label' => 'Absent',
                    'data' => $absentData,
                    'backgroundColor' => 'rgba(255, 99, 132, 0.2)',
                    'borderColor' => 'rgba(255, 99, 132, 1)',
                    'borderWidth' => 1
                ]
            ]
        ];
    }

    private function getTransitChartData($startDate, $endDate)
    {
        $transits = \App\Models\Transit::byTenant()
            ->whereBetween('start_time', [$startDate, $endDate])
            ->get()
            ->groupBy(function ($transit) {
                return \Carbon\Carbon::parse($transit->start_time)->format('Y-m-d');
            });

        $labels = [];
        $completedData = [];
        $delayedData = [];

        $period = \Carbon\CarbonPeriod::create($startDate, $endDate);
        foreach ($period as $date) {
            $dateStr = $date->format('Y-m-d');
            $dayTransits = $transits->get($dateStr, collect());

            $labels[] = $date->format('M d');
            $completedData[] = $dayTransits->where('status', 'arrived')->count();
            $delayedData[] = $dayTransits->where('status', 'delayed')->count();
        }

        return [
            'labels' => $labels,
            'datasets' => [
                [
                    'label' => 'Completed',
                    'data' => $completedData,
                    'backgroundColor' => 'rgba(54, 162, 235, 0.2)',
                    'borderColor' => 'rgba(54, 162, 235, 1)',
                    'borderWidth' => 1
                ],
                [
                    'label' => 'Delayed',
                    'data' => $delayedData,
                    'backgroundColor' => 'rgba(255, 206, 86, 0.2)',
                    'borderColor' => 'rgba(255, 206, 86, 1)',
                    'borderWidth' => 1
                ]
            ]
        ];
    }

    private function getViolationChartData($startDate, $endDate)
    {
        $violations = \App\Models\GeofenceViolation::byTenant()
            ->whereBetween('violation_time', [$startDate, $endDate])
            ->get()
            ->groupBy(function ($violation) {
                return \Carbon\Carbon::parse($violation->violation_time)->format('Y-m-d');
            });

        $labels = [];
        $violationData = [];

        $period = \Carbon\CarbonPeriod::create($startDate, $endDate);
        foreach ($period as $date) {
            $dateStr = $date->format('Y-m-d');
            $dayViolations = $violations->get($dateStr, collect());

            $labels[] = $date->format('M d');
            $violationData[] = $dayViolations->count();
        }

        return [
            'labels' => $labels,
            'datasets' => [
                [
                    'label' => 'Violations',
                    'data' => $violationData,
                    'backgroundColor' => 'rgba(255, 99, 132, 0.2)',
                    'borderColor' => 'rgba(255, 99, 132, 1)',
                    'borderWidth' => 1
                ]
            ]
        ];
    }

    private function getDepartmentChartData($startDate, $endDate)
    {
        $attendances = \App\Models\Attendance::byTenant()
            ->with(['employee.departments'])
            ->whereBetween('date', [$startDate, $endDate])
            ->get();

        $departmentData = $attendances->groupBy(function ($attendance) {
            return $attendance->employee->departments->pluck('name')->join(', ') ?: 'No Department';
        })->map(function ($deptAttendances) {
            $total = $deptAttendances->count();
            $present = $deptAttendances->where('status', 'present')->count();
            return [
                'total' => $total,
                'present' => $present,
                'rate' => $total > 0 ? round(($present / $total) * 100, 2) : 0
            ];
        });

        return [
            'labels' => $departmentData->keys(),
            'datasets' => [
                [
                    'label' => 'Attendance Rate (%)',
                    'data' => $departmentData->pluck('rate'),
                    'backgroundColor' => 'rgba(75, 192, 192, 0.2)',
                    'borderColor' => 'rgba(75, 192, 192, 1)',
                    'borderWidth' => 1
                ]
            ]
        ];
    }

    /**
     * Patrol report page
     */
    public function patrol(Request $request)
    {
        $startDate = $request->get('start_date', now()->subDays(30)->format('Y-m-d'));
        $endDate = $request->get('end_date', now()->format('Y-m-d'));

        $filters = [
            'client_id' => $request->get('client_id'),
            'service_id' => $request->get('service_id'),
            'employee_id' => $request->get('employee_id'),
            'report_type' => $request->get('report_type', 'summary')
        ];

        // Get filter options
        $clients = \App\Models\Client::byTenant()->active()->pluck('name', 'id');
        $services = \App\Models\ClientService::byTenant()->active()->pluck('service_name', 'id');
        $employees = \App\Models\User::byTenant()->active()->pluck('full_name', 'id');

        return view('reports.patrol', compact(
            'startDate',
            'endDate',
            'filters',
            'clients',
            'services',
            'employees'
        ));
    }

    /**
     * Export appointments report as TXT file
     */
    public function exportAppointmentsTXT(Request $request)
    {
        $appointments = Appointment::query();

        // Apply same filters as appointmentsReport
        if ($request->filled('status')) {
            $appointments->where('status', $request->status);
        }

        if ($request->filled('from_date')) {
            $appointments->whereDate('appointment_date', '>=', $request->from_date);
        }

        if ($request->filled('to_date')) {
            $appointments->whereDate('appointment_date', '<=', $request->to_date);
        }

        $appointments = $appointments->with(['client'])->get();

        $filename = 'appointments_report_' . date('Y-m-d_H-i-s') . '.txt';

        $headers = [
            'Content-Type' => 'text/plain',
            'Content-Disposition' => 'attachment; filename="' . $filename . '"',
        ];

        $callback = function () use ($appointments, $request) {
            $file = fopen('php://output', 'w');

            // Header
            fwrite($file, "APPOINTMENTS REPORT\n");
            fwrite($file, "Generated on: " . now()->format('Y-m-d H:i:s') . "\n");
            fwrite($file, "Total Appointments: " . $appointments->count() . "\n");

            // Filters applied
            if ($request->filled('from_date') || $request->filled('to_date') || $request->filled('status')) {
                fwrite($file, "\nFilters Applied:\n");
                if ($request->filled('from_date')) {
                    fwrite($file, "- From Date: " . $request->from_date . "\n");
                }
                if ($request->filled('to_date')) {
                    fwrite($file, "- To Date: " . $request->to_date . "\n");
                }
                if ($request->filled('status')) {
                    fwrite($file, "- Status: " . ucfirst($request->status) . "\n");
                }
            }

            fwrite($file, "\n" . str_repeat("=", 80) . "\n");
            fwrite($file, sprintf(
                "%-20s %-20s %-15s %-15s %-12s %-10s\n",
                'Client',
                'Title',
                'Date',
                'Time',
                'Status',
                'Contact'
            ));
            fwrite($file, str_repeat("-", 80) . "\n");

            // Data rows
            foreach ($appointments as $appointment) {
                fwrite($file, sprintf(
                    "%-20s %-20s %-15s %-15s %-12s $%-9.2f\n",
                    $appointment->client->name ?? 'N/A',
                    $appointment->appointment_title ?? 'N/A',
                    $appointment->appointment_date ? $appointment->appointment_date->format('Y-m-d') : 'N/A',
                    $appointment->appointment_time ?? 'N/A',
                    ucfirst($appointment->status ?? 'N/A'),
                    floatval($appointment->client->phone ?? 'N/A')
                ));
            }

            fwrite($file, str_repeat("=", 80) . "\n");
            fwrite($file, "\nReport End\n");

            fclose($file);
        };

        return response()->stream($callback, 200, $headers);
    }

    /**
     * Export appointments report as XLSX
     */
    public function exportAppointmentsXLSX(Request $request)
    {
        $query = \App\Models\Appointment::with(['client']);

        // Apply same filters as appointmentsReport
        if ($request->filled('status')) {
            $query->where('status', $request->status);
        }

        if ($request->filled('from_date')) {
            $query->whereDate('appointment_date', '>=', $request->from_date);
        }

        if ($request->filled('to_date')) {
            $query->whereDate('appointment_date', '<=', $request->to_date);
        }

        $appointments = $query->get();

        $filename = "appointments_report_" . now()->format('Y-m-d_H-i-s') . ".xls";

        $headers = [
            'Content-Type' => 'application/vnd.ms-excel',
            'Content-Disposition' => "attachment; filename=\"$filename\"",
        ];

        $callback = function () use ($appointments, $request) {
            $file = fopen('php://output', 'w');

            // UTF-8 BOM
            echo pack("CCC", 0xEF, 0xBB, 0xBF);

            // Report Header
            fputcsv($file, ['APPOINTMENTS REPORT']);
            fputcsv($file, ['Generated on: ' . now()->format('Y-m-d H:i:s')]);
            fputcsv($file, ['Total Appointments: ' . $appointments->count()]);
            fputcsv($file, []);

            // Filters Applied
            if ($request->filled('from_date') || $request->filled('to_date') || $request->filled('status')) {
                fputcsv($file, ['Filters Applied:']);

                if ($request->filled('from_date')) {
                    fputcsv($file, ['From Date:', $request->from_date]);
                }

                if ($request->filled('to_date')) {
                    fputcsv($file, ['To Date:', $request->to_date]);
                }

                if ($request->filled('status')) {
                    fputcsv($file, ['Status:', ucfirst($request->status)]);
                }

                fputcsv($file, []);
            }

            // Table Header
            fputcsv($file, [
                'Client Name',
                'Service/Title',
                'Date',
                'Time',
                'Status',
                'Contact'
            ]);

            // Data Rows
            foreach ($appointments as $appointment) {
                fputcsv($file, [
                    $appointment->client->name ?? 'N/A',
                    $appointment->appointment_title ?? 'N/A',
                    $appointment->appointment_date ? $appointment->appointment_date->format('Y-m-d') : 'N/A',
                    $appointment->appointment_time ?? 'N/A',
                    ucfirst($appointment->status ?? 'N/A'),
                    $appointment->client->phone ?? 'N/A'
                ]);
            }

            fclose($file);
        };

        return response()->stream($callback, 200, $headers);
    }

    public function exportReferralsXLSX(Request $request)
    {
        $query = \App\Models\LockerRegistration::with(['client', 'referrer'])
            ->whereHas('client', function($query) {
                $query->byTenant();
            })
            ->whereNotNull('referred_member_by')
            ->whereHas('referrer', function($query) {
                $query->whereHas('client', function ($query) {
                    $query->byTenant();
                });
            });

        // Filters
        if ($request->filled('from_date')) {
            $query->whereDate('registration_date', '>=', $request->from_date);
        }

        if ($request->filled('to_date')) {
            $query->whereDate('registration_date', '<=', $request->to_date);
        }

        if ($request->filled('status')) {
            $query->where('status', $request->status);
        }

        if ($request->filled('referred_by_client_id')) {
            $query->where('referred_member_by', $request->referred_by_client_id);
        }

        $referrals = $query->get();
        
        // Debug: Check if data is retrieved
        \Log::info('Export Referrals - Query count: ' . $query->count());
        \Log::info('Export Referrals - Records found: ' . $referrals->count());
        \Log::info('Export Referrals - Filters: ' . json_encode($request->all()));

        $filename = "referrals_report_" . now()->format('Y-m-d_H-i-s') . ".xls";

        $headers = [
            'Content-Type' => 'application/vnd.ms-excel',
            'Content-Disposition' => "attachment; filename=\"$filename\"",
        ];

        $callback = function () use ($referrals, $request) {
            $file = fopen('php://output', 'w');

            // UTF-8 BOM
            echo pack("CCC", 0xEF, 0xBB, 0xBF);

            // Report Header
            fputcsv($file, ['REFERRAL REPORT']);
            fputcsv($file, ['Generated on: ' . now()->format('Y-m-d H:i:s')]);
            fputcsv($file, ['Total Referrals: ' . $referrals->count()]);
            fputcsv($file, []);

            // Filters Applied
            if ($request->filled('from_date') || $request->filled('to_date') || $request->filled('status')) {
                fputcsv($file, ['Filters Applied:']);

                if ($request->filled('from_date')) {
                    fputcsv($file, ['From Date:', $request->from_date]);
                }

                if ($request->filled('to_date')) {
                    fputcsv($file, ['To Date:', $request->to_date]);
                }

                if ($request->filled('status')) {
                    fputcsv($file, ['Status:', ucfirst($request->status)]);
                }

                fputcsv($file, []);
            }

            // Table Header
            fputcsv($file, [
                'Date',
                'Referred Member',
                'Referrer Member',
                'Contact',
                'Commission',
                'Commission Used',
                'Balance',
                'Status'
            ]);

            // Data Rows
            foreach ($referrals as $referral) {
                $used = $referral->commission_used ?? 0;
                $balance = ($referral->referred_commission ?? 0) - $used;
                
                $registrationDate = 'N/A';
                if ($referral->registration_date) {
                    $registrationDate = is_object($referral->registration_date) && method_exists($referral->registration_date, 'format')
                        ? $referral->registration_date->format('Y-m-d')
                        : (string) $referral->registration_date;
                }

                fputcsv($file, [
                    $registrationDate,
                    $referral->client ? $referral->client->name : 'N/A',
                    $referral->referrer ? $referral->referrer->name : 'N/A',
                    $referral->client ? $referral->client->phone : 'N/A',
                    number_format($referral->referred_commission ?? 0, 2),
                    number_format($used, 2),
                    number_format($balance, 2),
                    ucfirst($referral->status ?? 'N/A')
                ]);
            }

            fclose($file);
        };

        return response()->stream($callback, 200, $headers);
    }

    public function exportReferralsTXT(Request $request)
    {
        $query = \App\Models\LockerRegistration::with(['client', 'referrer'])
            ->whereHas('client', function($query) {
                $query->byTenant();
            })
            ->whereNotNull('referred_member_by')
            ->whereHas('referrer', function($query) {
                $query->whereHas('client', function ($query) {
                    $query->byTenant();
                });
            });

        // Filters
        if ($request->filled('from_date')) {
            $query->whereDate('registration_date', '>=', $request->from_date);
        }

        if ($request->filled('to_date')) {
            $query->whereDate('registration_date', '<=', $request->to_date);
        }

        if ($request->filled('status')) {
            $query->where('status', $request->status);
        }

        if ($request->filled('referred_by_client_id')) {
            $query->where('referred_member_by', $request->referred_by_client_id);
        }

        $referrals = $query->get();
        
        // Debug: Check if data is retrieved
        \Log::info('Export Referrals TXT - Query count: ' . $query->count());
        \Log::info('Export Referrals TXT - Records found: ' . $referrals->count());

        $filename = "referrals_report_" . now()->format('Y-m-d_H-i-s') . ".txt";

        $headers = [
            'Content-Type' => 'text/plain',
            'Content-Disposition' => "attachment; filename=\"$filename\"",
        ];

        $callback = function () use ($referrals, $request) {
            $file = fopen('php://output', 'w');

            // Report Header
            fwrite($file, "REFERRAL REPORT\n");
            fwrite($file, "Generated on: " . now()->format('Y-m-d H:i:s') . "\n");
            fwrite($file, "Total Referrals: " . $referrals->count() . "\n");
            fwrite($file, "\n");

            // Filters Applied
            if ($request->filled('from_date') || $request->filled('to_date') || $request->filled('status')) {
                fwrite($file, "Filters Applied:\n");

                if ($request->filled('from_date')) {
                    fwrite($file, "From Date: " . $request->from_date . "\n");
                }

                if ($request->filled('to_date')) {
                    fwrite($file, "To Date: " . $request->to_date . "\n");
                }

                if ($request->filled('status')) {
                    fwrite($file, "Status: " . ucfirst($request->status) . "\n");
                }

                fwrite($file, "\n");
            }

            // Table Header
            fwrite($file, str_repeat("=", 100) . "\n");
            fwrite($file, sprintf("%-12s %-20s %-20s %-15s %-12s %-12s %-12s %-10s\n", 
                'Date', 'Referred Member', 'Referrer Member', 'Contact', 'Commission', 'Used', 'Balance', 'Status'));
            fwrite($file, str_repeat("=", 100) . "\n");

            // Data Rows
            foreach ($referrals as $referral) {
                $used = $referral->commission_used ?? 0;
                $balance = ($referral->referred_commission ?? 0) - $used;
                
                $registrationDate = 'N/A';
                if ($referral->registration_date) {
                    $registrationDate = is_object($referral->registration_date) && method_exists($referral->registration_date, 'format')
                        ? $referral->registration_date->format('Y-m-d')
                        : (string) $referral->registration_date;
                }

                fwrite($file, sprintf("%-12s %-20s %-20s %-15s %-12s %-12s %-12s %-10s\n",
                    $registrationDate,
                    $referral->client ? $referral->client->name : 'N/A',
                    $referral->referrer ? $referral->referrer->name : 'N/A',
                    $referral->client ? $referral->client->phone : 'N/A',
                    number_format($referral->referred_commission ?? 0, 2),
                    number_format($used, 2),
                    number_format($balance, 2),
                    ucfirst($referral->status ?? 'N/A')
                ));
            }

            fwrite($file, str_repeat("=", 100) . "\n");
            fwrite($file, "\nReport End\n");

            fclose($file);
        };

        return response()->stream($callback, 200, $headers);
    }

    public function income()
    {
        return view('reports.income');
    }

    public function incomeReport(Request $request)
    {
        $payments = \App\Models\Payment::query();

        // Filter by status
        if ($request->filled('status')) {
            $payments->where('status', $request->status);
        }

        // Filter by payment mode
        if ($request->filled('payment_mode')) {
            $payments->where('payment_mode', $request->payment_mode);
        }

        // Filter by date
        if ($request->filled('from_date')) {
            $payments->whereDate('payment_date', '>=', $request->from_date);
        }

        if ($request->filled('to_date')) {
            $payments->whereDate('payment_date', '<=', $request->to_date);
        }

        $payments = $payments->with(['registration.client'])->get();

        return response()->json([
            'data' => $payments,
            'count' => $payments->count(),
            'total_income' => $payments->sum('paid_amount')
        ]);
    }

    public function exportIncomeTXT(Request $request)
    {
        $payments = \App\Models\Payment::query();

        // Filters
        if ($request->filled('status')) {
            $payments->where('status', $request->status);
        }

        if ($request->filled('payment_mode')) {
            $payments->where('payment_mode', $request->payment_mode);
        }

        if ($request->filled('from_date')) {
            $payments->whereDate('payment_date', '>=', $request->from_date);
        }

        if ($request->filled('to_date')) {
            $payments->whereDate('payment_date', '<=', $request->to_date);
        }

        $payments = $payments->with(['registration.client'])->get();

        $filename = 'income_report_' . date('Y-m-d_H-i-s') . '.txt';

        $headers = [
            'Content-Type' => 'text/plain',
            'Content-Disposition' => 'attachment; filename="' . $filename . '"',
        ];

        $callback = function () use ($payments, $request) {

            $file = fopen('php://output', 'w');

            // Header
            fwrite($file, "INCOME REPORT\n");
            fwrite($file, "Generated on: " . now()->format('Y-m-d H:i:s') . "\n");
            fwrite($file, "Total Records: " . $payments->count() . "\n");
            fwrite($file, "Total Income: " . number_format($payments->sum('paid_amount'), 2) . "\n");

            // Filters
            if ($request->filled('from_date') || $request->filled('to_date') || $request->filled('status')) {
                fwrite($file, "\nFilters Applied:\n");

                if ($request->filled('from_date')) {
                    fwrite($file, "- From Date: " . $request->from_date . "\n");
                }

                if ($request->filled('to_date')) {
                    fwrite($file, "- To Date: " . $request->to_date . "\n");
                }

                if ($request->filled('status')) {
                    fwrite($file, "- Status: " . ucfirst($request->status) . "\n");
                }
            }

            fwrite($file, "\n" . str_repeat("=", 100) . "\n");

            fwrite($file, sprintf(
                "%-20s %-15s %-10s %-10s %-10s %-10s %-12s\n",
                'Client',
                'Phone',
                'Total',
                'Paid',
                'Balance',
                'Mode',
                'Date'
            ));

            fwrite($file, str_repeat("-", 100) . "\n");

            foreach ($payments as $payment) {

                $client = $payment->registration->client ?? null;

                fwrite($file, sprintf(
                    "%-20s %-15s %-10s %-10s %-10s %-10s %-12s\n",
                    $client->name ?? 'N/A',
                    $client->phone ?? 'N/A',
                    number_format($payment->total_amount, 2),
                    number_format($payment->paid_amount, 2),
                    number_format($payment->balance_amount, 2),
                    ucfirst($payment->payment_mode),
                    $payment->payment_date ? $payment->payment_date->format('Y-m-d') : 'N/A'
                ));
            }

            fwrite($file, str_repeat("=", 100) . "\n");
            fwrite($file, "\nReport End\n");

            fclose($file);
        };

        return response()->stream($callback, 200, $headers);
    }

    /**
     * Export income report as XLS
     */
    // public function exportIncomeXLS(Request $request)
    // {
    //     $payments = \App\Models\Payment::query();

    //     // Filters
    //     if ($request->filled('status')) {
    //         $payments->where('status', $request->status);
    //     }

    //     if ($request->filled('payment_mode')) {
    //         $payments->where('payment_mode', $request->payment_mode);
    //     }

    //     if ($request->filled('from_date')) {
    //         $payments->whereDate('payment_date', '>=', $request->from_date);
    //     }

    //     if ($request->filled('to_date')) {
    //         $payments->whereDate('payment_date', '<=', $request->to_date);
    //     }

    //     $payments = $payments->with(['registration.client'])->get();

    //     $filename = "income_report_" . now()->format('Y-m-d_H-i-s') . ".xls";
    //     $headers = [
    //         'Content-Type' => 'application/vnd.ms-excel',
    //         'Content-Disposition' => "attachment; filename=\"$filename\"",
    //     ];

    //     $callback = function () use ($payments, $request) {
    //         $file = fopen('php://output', 'w');

    //         // Excel header for proper formatting
    //         echo pack("CCC", 0xEF, 0xBB, 0xBF);

    //         // CSV header
    //         fputcsv($file, [
    //             'Client',
    //             'Phone',
    //             'Total Amount',
    //             'Paid Amount',
    //             'Balance Amount',
    //             'Payment Mode',
    //             'Status',
    //             'Payment Date'
    //         ]);

    //         // Data rows
    //         foreach ($payments as $payment) {
    //             $client = $payment->registration->client ?? null;

    //             fputcsv($file, [
    //                 $client->name ?? 'N/A',
    //                 $client->phone ?? 'N/A',
    //                 number_format($payment->total_amount, 2),
    //                 number_format($payment->paid_amount, 2),
    //                 number_format($payment->balance_amount, 2),
    //                 ucfirst($payment->payment_mode),
    //                 ucfirst($payment->status),
    //                 $payment->payment_date ? $payment->payment_date->format('Y-m-d') : 'N/A'
    //             ]);
    //         }

    //         fclose($file);
    //     };

    //     return response()->stream($callback, 200, $headers);
    // }

    public function exportIncomeXLS(Request $request)
    {
        $query = \App\Models\Payment::with('registration.client');

        // 🔍 Filters
        if ($request->filled('status')) {
            $query->where('status', $request->status);
        }

        if ($request->filled('payment_mode')) {
            $query->where('payment_mode', $request->payment_mode);
        }

        if ($request->filled('from_date')) {
            $query->whereDate('payment_date', '>=', $request->from_date);
        }

        if ($request->filled('to_date')) {
            $query->whereDate('payment_date', '<=', $request->to_date);
        }

        $payments = $query->get();

        $filename = "income_report_" . now()->format('Y-m-d_H-i-s') . ".xls";

        $headers = [
            'Content-Type' => 'application/vnd.ms-excel',
            'Content-Disposition' => "attachment; filename=\"$filename\"",
        ];

        $callback = function () use ($payments, $request) {

            $file = fopen('php://output', 'w');

            // UTF-8 BOM
            echo pack("CCC", 0xEF, 0xBB, 0xBF);

            // 🔥 Report Header
            fputcsv($file, ['INCOME REPORT']);
            fputcsv($file, ['Generated on: ' . now()->format('Y-m-d H:i:s')]);
            fputcsv($file, ['Total Records: ' . $payments->count()]);
            fputcsv($file, ['Total Income: ₹' . number_format($payments->sum('paid_amount'), 2)]);
            fputcsv($file, []);

            // 🔍 Filters Applied
            if ($request->filled('from_date') || $request->filled('to_date') || $request->filled('status')) {

                fputcsv($file, ['Filters Applied:']);

                if ($request->filled('from_date')) {
                    fputcsv($file, ['From Date:', $request->from_date]);
                }

                if ($request->filled('to_date')) {
                    fputcsv($file, ['To Date:', $request->to_date]);
                }

                if ($request->filled('status')) {
                    fputcsv($file, ['Status:', ucfirst($request->status)]);
                }

                if ($request->filled('payment_mode')) {
                    fputcsv($file, ['Payment Mode:', ucfirst($request->payment_mode)]);
                }

                fputcsv($file, []);
            }

            // 📊 Table Header
            fputcsv($file, [
                'Client Name',
                'Phone',
                'Total Amount',
                'Paid Amount',
                'Balance Amount',
                'Payment Mode',
                'Status',
                'Payment Date'
            ]);

            // 📋 Data Rows
            foreach ($payments as $payment) {

                $client = $payment->registration->client ?? null;

                fputcsv($file, [
                    $client->name ?? 'N/A',
                    $client->phone ?? 'N/A',
                    number_format($payment->total_amount, 2),
                    number_format($payment->paid_amount, 2),
                    number_format($payment->balance_amount, 2),
                    ucfirst($payment->payment_mode),
                    ucfirst($payment->status),
                    $payment->payment_date
                        ? \Carbon\Carbon::parse($payment->payment_date)->format('Y-m-d H:i:s')
                        : 'N/A'
                ]);
            }

            fclose($file);
        };

        return response()->stream($callback, 200, $headers);
    }
}