<?php

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use App\Models\AdminActivity;
use App\Models\AdminRole;
use App\Models\KycThirdParty;
use App\Models\mobilead;
use App\Models\onpremise;
use App\Models\OnpremiseInfo;
use App\Models\OnPremiseRemoval;
use App\Models\OnPremiseAdditional;
use App\Models\OnPremiseChange;
use App\Models\StreetLampsAds;
use App\Models\StreetLampInfo;
use App\Models\ThirdPartyMobile;
use App\Models\ThirdPartyOffchargeAndExpunction;
use App\Models\ThirdPartyOutdoor;
use App\Models\ThirdPartyUpgradeAndDowngrade;
use App\Models\ThirdPartyOutdoorTransfer;
use App\Models\ThirdPartyOutdoorSignDetails;
use App\Models\SmallFormatAds;
use App\Models\SmallFormatDetails;
use App\Models\FirstPartyTemporaryAds;
use App\Models\FirstPartyTemporaryAdsDetails;
use App\Models\PaymentFirstPartyTemporaryAds;
use App\Models\User;
use App\Models\UserAdmin;
use App\Models\MobileAdsVehicleLicense;
use App\Models\MobilePayment;
use App\Models\ThirdPartyMobileVehicleLicense;
use App\Models\PaymentThirdPartyMobile;
use App\Models\PaymentOnPremise;
use App\Models\PaymentThirdPartyOutdoorSignage;
use App\Models\KycThirdPartyPayment;
use App\Models\PaymentSmallFormatAds;
use App\Models\PaymentStreetLamp;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Facades\Validator;

class ReportController extends Controller  {

    // public function mobilePermitReport() {

    //         $user = Auth::user();
            
    // }
    
    public function mobilePermitReport(Request $request) {
        
        $startDate = Carbon::parse($request->input('start_date'));
        $endDate = Carbon::parse($request->input('end_date'));
    
        $totalMobileAds = mobilead::whereBetween('created_at', [$startDate, $endDate])->where('final_status', 'approved')->count();
        $totalVehicleLicenses = MobileAdsVehicleLicense::whereBetween('created_at', [$startDate, $endDate])->whereHas('mobileAds', function($query) { $query->where('final_status', 'approved');})->count();
        $partialBranding = MobileAdsVehicleLicense::where('branding_details', 'Partial Branding')->whereBetween('created_at', [$startDate, $endDate])->whereHas('mobileAds', function($query) { $query->where('final_status', 'approved');})->count();
        $fullBranding = MobileAdsVehicleLicense::where('branding_details', 'Full Branding')->whereBetween('created_at', [$startDate, $endDate])->whereHas('mobileAds', function($query) { $query->where('final_status', 'approved');})->count();
        $WrapAroundBranding = MobileAdsVehicleLicense::where('branding_details', 'Wrap Around Branding')->whereBetween('created_at', [$startDate, $endDate])->whereHas('mobileAds', function($query) { $query->where('final_status', 'approved');})->count();
        $Trailer = MobileAdsVehicleLicense::where('vehicle_type', 'Trailer')->whereBetween('created_at', [$startDate, $endDate])->whereHas('mobileAds', function($query) { $query->where('final_status', 'approved');})->count();
        $Truck = MobileAdsVehicleLicense::where('vehicle_type', 'Truck')->whereBetween('created_at', [$startDate, $endDate])->whereHas('mobileAds', function($query) { $query->where('final_status', 'approved');})->count();
        $Coaster = MobileAdsVehicleLicense::where('vehicle_type', 'Coaster')->whereBetween('created_at', [$startDate, $endDate])->whereHas('mobileAds', function($query) { $query->where('final_status', 'approved');})->count();
        $Civilian = MobileAdsVehicleLicense::where('vehicle_type', 'Civilian')->whereBetween('created_at', [$startDate, $endDate])->whereHas('mobileAds', function($query) { $query->where('final_status', 'approved');})->count();
        $Bus = MobileAdsVehicleLicense::where('vehicle_type', 'Bus')->whereBetween('created_at', [$startDate, $endDate])->whereHas('mobileAds', function($query) { $query->where('final_status', 'approved');})->count();
        $Pickup = MobileAdsVehicleLicense::where('vehicle_type', 'Pickup')->whereBetween('created_at', [$startDate, $endDate])->whereHas('mobileAds', function($query) { $query->where('final_status', 'approved');})->count();
        $SUV = MobileAdsVehicleLicense::where('vehicle_type', 'SUV')->whereBetween('created_at', [$startDate, $endDate])->whereHas('mobileAds', function($query) { $query->where('final_status', 'approved');})->count();
        $Motorcycle = MobileAdsVehicleLicense::where('vehicle_type', 'Motorcycle')->whereBetween('created_at', [$startDate, $endDate])->whereHas('mobileAds', function($query) { $query->where('final_status', 'approved');})->count();
        $Push_Cart = MobileAdsVehicleLicense::where('vehicle_type', 'Push-Cart')->whereBetween('created_at', [$startDate, $endDate])->whereHas('mobileAds', function($query) { $query->where('final_status', 'approved');})->count();
        $Bicycle = MobileAdsVehicleLicense::where('vehicle_type', 'Bicycle')->whereBetween('created_at', [$startDate, $endDate])->whereHas('mobileAds', function($query) { $query->where('final_status', 'approved');})->count();
        $Tricycle = MobileAdsVehicleLicense::where('vehicle_type', 'Tricycle')->whereBetween('created_at', [$startDate, $endDate])->whereHas('mobileAds', function($query) { $query->where('final_status', 'approved');})->count();
        $Car = MobileAdsVehicleLicense::where('vehicle_type', 'Car')->whereBetween('created_at', [$startDate, $endDate])->whereHas('mobileAds', function($query) { $query->where('final_status', 'approved');})->count();
        $totalAmountPaid = MobilePayment::whereBetween('created_at', [$startDate, $endDate])->sum('amount_paid');
        
        // Get breakdown of amount paid (e.g., per day)
        $paymentBreakdown = MobilePayment::selectRaw('DATE(created_at) as date, SUM(amount_paid) as total')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->groupBy('date')
            ->orderBy('date')
            ->get();
    
        // Weekly Breakdown
        $paymentBreakdownByWeek = MobilePayment::selectRaw('YEARWEEK(created_at) as week, SUM(amount_paid) as total')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->groupBy('week')
            ->orderBy('week')
            ->get();
    
        // Monthly Breakdown
        $paymentBreakdownByMonth = MobilePayment::selectRaw("DATE_FORMAT(created_at, '%Y-%m') as month, SUM(amount_paid) as total")
            ->whereBetween('created_at', [$startDate, $endDate])
            ->groupBy('month')
            ->orderBy('month')
            ->get();
    
        return response()->json([
            'total_mobile_ads' => $totalMobileAds,
            'total_vehicle_licenses' => $totalVehicleLicenses,
            'total_amount_paid' => $totalAmountPaid,
            'branding_type' => [              
                            'partial branding' => $partialBranding,
                            'full branding' => $fullBranding,
                            'wrap around branding' => $WrapAroundBranding,
                        ],
            'vehicle_type' => [
                        'Trailer' => $Trailer,
                        'Truck' => $Truck,
                        'Coaster' => $Coaster,
                        'Civilian' => $Civilian,
                        'Bus' => $Bus,
                        'Pickup' => $Pickup,
                        'SUV' => $SUV,
                        'Motorcycle' => $Motorcycle,
                        'Push Cart' => $Push_Cart,
                        'Bicycle' => $Bicycle,
                        'Tricycle' => $Tricycle,
                        'Car' => $Car,
            ],
            'payment_breakdown_daily' => $paymentBreakdown,
            'payment_breakdown_weekly' => $paymentBreakdownByWeek,
            'payment_breakdown_monthly' => $paymentBreakdownByMonth,
        ]);
    }
    
    
    public function onpremisePermitReport(Request $request) {
        
        $startDate = Carbon::parse($request->input('start_date'));
        $endDate = Carbon::parse($request->input('end_date'));
        $lga = $request->input('lga');
    
        $totalOnpremise = onpremise::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->where('lga', $lga);
            })
            ->count();
        $singleBusiness = onpremise::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->where('lga', $lga);
            })
            ->where('property_type', 'Single Business Unit')
            ->count();
        $multitenanted = onpremise::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->where('lga', $lga);
            })
            ->where('property_type', 'Multi-tenanted Business')
            ->count();
        $projectingSigns = OnpremiseInfo::where('type_of_sign', 'Projecting Signs')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('onpremise', function ($q) use ($lga) {
                    $q->where('lga', $lga);
                });
            })
            ->count();
        $neonWall = OnpremiseInfo::where('type_of_sign', 'Neon Wall/Canopy/Roof Signs/Flat Signs')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('onpremise', function ($q) use ($lga) {
                    $q->where('lga', $lga);
                });
            })
            ->count();
        $wall = OnpremiseInfo::where('type_of_sign', 'Wall/Canopy/Roof Signs/Flat Signs')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('onpremise', function ($q) use ($lga) {
                    $q->where('lga', $lga);
                });
            })
            ->count();
        $neon = OnpremiseInfo::where('type_of_sign', 'Neon Freestanding/Sky Sign')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('onpremise', function ($q) use ($lga) {
                    $q->where('lga', $lga);
                });
            })
            ->count();
        $window = OnpremiseInfo::where('type_of_sign', 'Window Display')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('onpremise', function ($q) use ($lga) {
                    $q->where('lga', $lga);
                });
            })
            ->count();
        $wallDrapes = OnpremiseInfo::where('type_of_sign', 'Wall Drapes')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('onpremise', function ($q) use ($lga) {
                    $q->where('lga', $lga);
                });
            })
            ->count();
        $freestanding = OnpremiseInfo::where('type_of_sign', 'Freestanding')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('onpremise', function ($q) use ($lga) {
                    $q->where('lga', $lga);
                });
            })
            ->count();
        $skySigns = OnpremiseInfo::where('type_of_sign', 'Sky Signs')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('onpremise', function ($q) use ($lga) {
                    $q->where('lga', $lga);
                });
            })
            ->count();
        $flags = OnpremiseInfo::where('type_of_sign', 'Flags')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('onpremise', function ($q) use ($lga) {
                    $q->where('lga', $lga);
                });
            })
            ->count();
        $smallshops = OnpremiseInfo::where('type_of_sign', 'Small shops')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('onpremise', function ($q) use ($lga) {
                    $q->where('lga', $lga);
                });
            })
            ->count();
        $led = OnpremiseInfo::where('type_of_sign', 'Led Screens At Filling Stations')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('onpremise', function ($q) use ($lga) {
                    $q->where('lga', $lga);
                });
            })
            ->count();
        $ledSigns = OnpremiseInfo::where('type_of_sign', 'LED Sign')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('onpremise', function ($q) use ($lga) {
                    $q->where('lga', $lga);
                });
            })
            ->count();
        $street = OnpremiseInfo::where('type_of_sign', 'Street Light Pole Directional Sign')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('onpremise', function ($q) use ($lga) {
                    $q->where('lga', $lga);
                });
            })
            ->count();
        $streetDirectional = OnpremiseInfo::where('type_of_sign', 'Street Directional Sign')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('onpremise', function ($q) use ($lga) {
                    $q->where('lga', $lga);
                });
            })
            ->count();
        $tankFarm = OnpremiseInfo::where('type_of_sign', 'Tank Farm')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('onpremise', function ($q) use ($lga) {
                    $q->where('lga', $lga);
                });
            })
            ->count();
        $totalAmountPaid = PaymentOnPremise::whereBetween('created_at', [$startDate, $endDate])->whereNot('application_purpose', 'Application Fee')
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('user.onpremise', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->sum('amount_paid');
        $totalApplicationFee = PaymentOnPremise::whereBetween('created_at', [$startDate, $endDate])->where('application_purpose', 'Application Fee')
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('user.onpremise', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->sum('amount_paid');
        
        // Get breakdown of amount paid (e.g., per day)
        $paymentBreakdown = PaymentOnPremise::selectRaw('DATE(created_at) as date, SUM(amount_paid) as total')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('user.onpremise', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->groupBy('date')
            ->orderBy('date')
            ->get();
    
        // Weekly Breakdown
        $paymentBreakdownByWeek = PaymentOnPremise::selectRaw('YEARWEEK(created_at) as week, SUM(amount_paid) as total')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('user.onpremise', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->groupBy('week')
            ->orderBy('week')
            ->get();
    
        // Monthly Breakdown
        $paymentBreakdownByMonth = PaymentOnPremise::selectRaw("DATE_FORMAT(created_at, '%Y-%m') as month, SUM(amount_paid) as total")
            ->whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('user.onpremise', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->groupBy('month')
            ->orderBy('month')
            ->get();
    
        return response()->json([
            'total_on_premise' => $totalOnpremise,
            'total_application_fee' => $totalApplicationFee,
            'total_amount_paid' => $totalAmountPaid,
            'property_type' => [
                            'Single Business Unit' => $singleBusiness,
                            'Multi-tenanted Business' => $multitenanted,
                        ],
            'product_type' => [
                        'Projecting Signs' => $projectingSigns,
                        'Wall/Canopy/Roof Signs/Flat Signs' => $wall,
                        'Neon Wall/Canopy/Roof Signs/Flat Signs' => $neonWall,
                        'Neon Freestanding/Sky Sign' => $neon,
                        'Window Display' => $window,
                        'Wall Drapes' => $wallDrapes,
                        'Freestanding' => $freestanding,
                        'Sky Signs' => $skySigns,
                        'Flags' => $flags,
                        'Small shops' => $smallshops,
                        'Led Screens At Filling Stations' => $led,
                        'LED Sign' => $ledSigns,
                        'Street Light Pole Directional Sign' => $street,
                        'Street Directional Sign' => $streetDirectional,
                        'Tank Farm' => $tankFarm,
            ],
            'payment_breakdown_daily' => $paymentBreakdown,
            'payment_breakdown_weekly' => $paymentBreakdownByWeek,
            'payment_breakdown_monthly' => $paymentBreakdownByMonth,
        ]);
    }
    

    public function thridPartyPermitReport(Request $request) {
        
        $startDate = Carbon::parse($request->input('start_date'));
        $endDate = Carbon::parse($request->input('end_date'));
        $lga = $request->input('lga');
    
        $totalOutdoor = ThirdPartyOutdoor::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->where('lga', $lga);
            })
            ->count();
        $Rural = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('street_category', 'Rural Zone N')
            ->count();
        $Normal = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('street_category', 'Normal Zone N')
            ->count();
        $Restrictive = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('street_category', 'Restrictive Zone N')
            ->count();
        $High = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('street_category', 'High Street N')
            ->count();
        $Local = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('street_category', 'Local & International Airport N')
            ->count();
        $Encroachment = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('street_category', 'Encroachment Zone N')
            ->count();
        $NotApplicable = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('street_category', 'Not Applicable')
            ->count();
        $Sheet16 = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', '16 Sheet')
            ->count();
        $Sheet48 = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', '48 Sheet')
            ->count();
        $Sheet96 = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', '96 Sheet')
            ->count();
        $RooftopStatic = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', 'Rooftop/Static')
            ->count();
        $RooftopUltrawave = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', 'Rooftop/Ultrawave')
            ->count();
        $UnipoleNeon = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', 'Unipole(Neon)')
            ->count();
        $Unipole = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', 'Unipole')
            ->count();
        $FrontBack = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', 'Front/Back Lit')
            ->count();
        $SheetUltrawave48 = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', '48 Sheet Ultrawave')
            ->count();
        $WallPanelUltrawave = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', 'Wall Panel Ultrawave')
            ->count();
        $SheetUltrawave96 = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', '96 Sheet Ultrawave')
            ->count();
        $UnipoleUltrawave = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', 'Unipole Ultrawave')
            ->count();
        $UnipoleNeonUltrawave = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', 'Unipole(Neon) Ultrawave')
            ->count();
        $WallDrape = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', 'Wall Drape')
            ->count();
        $WrapAround = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', 'Wrap Around')
            ->count();
        $IconicStructureBillboard = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', 'Iconic Structure (Billboard)')
            ->count();
        $TankFarm = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', 'Tank Farm')
            ->count();
        $WaterTank = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', 'Water Tank')
            ->count();
        $WaterTankUltrawave = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', 'Water Tank Ultrawave')
            ->count();
        $AdvertprivateInitiative = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', 'Advert on Bridge built on private sector Initiative')
            ->count();
        $BridgePanelPedestrian = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', 'Bridge Panel (Pedestrian/vehicular)')
            ->count();
        $AdvertprivateInitiativeUltrawave = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', 'Advert on Bridge built on private sector Initiative Ultrawave')
            ->count();
        $BridgePanelPedestrianUltrawave = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', 'Bridge Panel Ultrawave (Pedestrian/vehicular)')
            ->count();
        $OverheadsignsA = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', 'Overhead Gantries/Directional signs(A)')
            ->count();
        $OverheadsignsB = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', 'Overhead Gantries/Directional signs(B)')
            ->count();
        $OverheadsignsC = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', 'Overhead Gantries/Directional signs(C)')
            ->count();
        $OverheadsignsUltrawaveA = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', 'Overhead Gantries/Directional signs Ultrawave(A)')
            ->count();
        $OverheadsignsUltrawaveB = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', 'Overhead Gantries/Directional signs Ultrawave(B)')
            ->count();
        $OverheadsignsUltrawaveC = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', 'Overhead Gantries/Directional signs Ultrawave(C)')
            ->count();
        $LED = ThirdPartyOutdoorSignDetails::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->where('designation', 'LED')
            ->count();
        $totalAmountPaid = PaymentThirdPartyOutdoorSignage::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('user.thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->sum('amount_paid');
        $totalKycThirdPartyPayment = KycThirdPartyPayment::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('kycThirdParty', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->sum('amount_paid');
        
        // Get breakdown of amount paid (e.g., per day)
        $paymentBreakdown = PaymentThirdPartyOutdoorSignage::selectRaw('DATE(created_at) as date, SUM(amount_paid) as total')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('user.thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->groupBy('date');

        $kycPaymentBreakdown = KycThirdPartyPayment::selectRaw('DATE(created_at) as date, SUM(amount_paid) as total')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('kycThirdParty', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->groupBy('date');

        // Merge both queries using `unionAll()`
        $finalBreakdownDay = DB::table(DB::raw("({$paymentBreakdown->toSql()}) as p"))
            ->mergeBindings($paymentBreakdown->getQuery())
            ->select('p.date', DB::raw('SUM(p.total) as total')) // Aggregate the totals after union
            ->unionAll(
                DB::table(DB::raw("({$kycPaymentBreakdown->toSql()}) as k"))
                    ->mergeBindings($kycPaymentBreakdown->getQuery())
                    ->select('k.date', 'k.total') // Select the same columns
            )
            ->groupBy('date') // Group by date after union
            ->orderBy('date') // Order by date
            ->get();
    
        // Weekly Breakdown
        $paymentBreakdownByWeek = PaymentThirdPartyOutdoorSignage::selectRaw('YEARWEEK(created_at) as week, SUM(amount_paid) as total')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('user.thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->groupBy('week');

        $kycPaymentBreakdownByWeek = KycThirdPartyPayment::selectRaw('YEARWEEK(created_at) as week, SUM(amount_paid) as total')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('kycThirdParty', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->groupBy('week');

        // Combine the queries with unionAll()
        $finalBreakdownByWeek = DB::table(DB::raw("({$paymentBreakdownByWeek->toSql()}) as p"))
            ->mergeBindings($paymentBreakdownByWeek->getQuery())
            ->select('p.week', DB::raw('SUM(p.total) as total')) // Aggregate the totals after union
            ->unionAll(
                DB::table(DB::raw("({$kycPaymentBreakdownByWeek->toSql()}) as k"))
                    ->mergeBindings($kycPaymentBreakdownByWeek->getQuery())
                    ->select('k.week', 'k.total') // Select the same columns
            )
            ->groupBy('week') // Group by date after union
            ->orderBy('week') // Order by date
            ->get();
    
        // Monthly Breakdown
        $paymentBreakdownByMonth = PaymentThirdPartyOutdoorSignage::selectRaw("DATE_FORMAT(created_at, '%Y-%m') as month, SUM(amount_paid) as total")
            ->whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('user.thirdPartyOutdoor', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->groupBy('month');
        
        $kycPaymentBreakdownByMonth = KycThirdPartyPayment::selectRaw("DATE_FORMAT(created_at, '%Y-%m') as month, SUM(amount_paid) as total")
            ->whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->whereHas('kycThirdParty', function ($subQuery) use ($lga) {
                    $subQuery->where('lga', $lga);
                });
            })
            ->groupBy('month');

        // Combine both queries with unionAll()
        $finalPaymentBreakdownByMonth = DB::table(DB::raw("({$paymentBreakdownByMonth->toSql()}) as p"))
            ->mergeBindings($paymentBreakdownByMonth->getQuery())
            ->select('p.month', DB::raw('SUM(p.total) as total')) // Aggregate the totals after union
            ->unionAll(
                DB::table(DB::raw("({$kycPaymentBreakdownByMonth->toSql()}) as k"))
                    ->mergeBindings($kycPaymentBreakdownByMonth->getQuery())
                    ->select('k.month', 'k.total') // Select the same columns
            )
            ->groupBy('month') // Group by date after union
            ->orderBy('month') // Order by date
            ->get();

    
        return response()->json([
            'total_out_door' => $totalOutdoor,
            'total_eoi_fee' => $totalKycThirdPartyPayment,
            'total_permit_fee' => $totalAmountPaid,
            'street_category' => [
                            'Rural Zone N' => $Rural,
                            'Normal Zone N' => $Normal,
                            'Restrictive Zone N' => $Restrictive,
                            'High Street N' => $High,
                            'Local & International Airport N' => $Local,
                            'Encroachment Zone N' => $Encroachment,
                            'Not Applicable' => $NotApplicable,
                        ],
            'product_type' => [
                        '16 Sheet' => $Sheet16,
                        '48 Sheet' => $Sheet48,
                        '96 Sheet' => $Sheet96,
                        'Rooftop/Static' => $RooftopStatic,
                        'Rooftop/Ultrawave' => $RooftopUltrawave,
                        'Unipole(Neon)' => $UnipoleNeon,
                        'Unipole' => $Unipole,
                        'Front/Back Lit' => $FrontBack,
                        '48 Sheet Ultrawave' => $SheetUltrawave48,
                        '96 Sheet Ultrawave' => $SheetUltrawave96,
                        'Wall Panel Ultrawave' => $WallPanelUltrawave,
                        'Unipole Ultrawave' => $UnipoleUltrawave,
                        'Unipole (Neon) Ultrawave' => $UnipoleNeonUltrawave,
                        'Wall Drape' => $WallDrape,
                        'WrapAround' => $WrapAround,
                        'Iconic Structure (Billboard)' => $IconicStructureBillboard,
                        'Tank Farm' => $TankFarm,
                        'Water Tank' => $WaterTank,
                        'Water Tank Ultrawave' => $WaterTankUltrawave,
                        'Advert on Bridge built on private sector Initiative' => $AdvertprivateInitiative,
                        'Bridge Panel (Pedestrian/vehicular)' => $BridgePanelPedestrian,
                        'Advert on Bridge built on private sector Initiative Ultrawave' => $AdvertprivateInitiativeUltrawave,
                        'Bridge Panel Ultrawave (Pedestrian/vehicular)' => $BridgePanelPedestrianUltrawave,
                        'Overhead Gantries/Directional signs(A)' => $OverheadsignsA,
                        'Overhead Gantries/Directional signs(B)' => $OverheadsignsB,
                        'Overhead Gantries/Directional signs(C)' => $OverheadsignsC,
                        'Overhead Gantries/Directional signs Ultrawave(A)' => $OverheadsignsUltrawaveA,
                        'Overhead Gantries/Directional signs Ultrawave(B)' => $OverheadsignsUltrawaveB,
                        'Overhead Gantries/Directional signs Ultrawave(C)' => $OverheadsignsUltrawaveC,
                        'LED' => $LED,
            ],
            'payment_breakdown_daily' => $finalBreakdownDay,
            'payment_breakdown_weekly' => $finalBreakdownByWeek,
            'payment_breakdown_monthly' => $finalPaymentBreakdownByMonth,
        ]);
    }
    
    
    public function thirdPartyMobilePermitReport(Request $request) {
        
        $startDate = Carbon::parse($request->input('start_date'));
        $endDate = Carbon::parse($request->input('end_date'));
    
        $totalMobileAds = ThirdPartyMobile::whereBetween('created_at', [$startDate, $endDate])->count();
        $totalVehicleLicenses = ThirdPartyMobileVehicleLicense::whereBetween('created_at', [$startDate, $endDate])->count();
        // $partialBranding = ThirdPartyMobileVehicleLicense::where('branding_details', 'Partial Branding')->whereBetween('created_at', [$startDate, $endDate])->count();
        // $fullBranding = ThirdPartyMobileVehicleLicense::where('branding_details', 'Full Branding')->whereBetween('created_at', [$startDate, $endDate])->count();
        // $WrapAroundBranding = ThirdPartyMobileVehicleLicense::where('branding_details', 'Wrap Around Branding')->whereBetween('created_at', [$startDate, $endDate])->count();
        $LightCab = ThirdPartyMobileVehicleLicense::where('vehicle_type', 'Light Box Cab')->whereBetween('created_at', [$startDate, $endDate])->count();
        $LightPlus = ThirdPartyMobileVehicleLicense::where('vehicle_type', 'Light Box Plus Doors Cab')->whereBetween('created_at', [$startDate, $endDate])->count();
        $PromotionalStatic = ThirdPartyMobileVehicleLicense::where('vehicle_type', 'Promotional Advert Vehicles (PAV) Static')->whereBetween('created_at', [$startDate, $endDate])->count();
        $PromotionalUltrawave = ThirdPartyMobileVehicleLicense::where('vehicle_type', 'Promotional Advert Vehicles (PAV) Ultrawave & LED')->whereBetween('created_at', [$startDate, $endDate])->count();
        $Bus = ThirdPartyMobileVehicleLicense::where('vehicle_type', 'Intra-State Luxurious Bus Advertising (LAGBUS/LAMATA/LAWMA)')->whereBetween('created_at', [$startDate, $endDate])->count();
        $totalAmountPaid = PaymentThirdPartyMobile::whereBetween('created_at', [$startDate, $endDate])->sum('amount_paid');
        
        // Get breakdown of amount paid (e.g., per day)
        $paymentBreakdown = PaymentThirdPartyMobile::selectRaw('DATE(created_at) as date, SUM(amount_paid) as total')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->groupBy('date')
            ->orderBy('date')
            ->get();
    
        // Weekly Breakdown
        $paymentBreakdownByWeek = PaymentThirdPartyMobile::selectRaw('YEARWEEK(created_at) as week, SUM(amount_paid) as total')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->groupBy('week')
            ->orderBy('week')
            ->get();
    
        // Monthly Breakdown
        $paymentBreakdownByMonth = PaymentThirdPartyMobile::selectRaw("DATE_FORMAT(created_at, '%Y-%m') as month, SUM(amount_paid) as total")
            ->whereBetween('created_at', [$startDate, $endDate])
            ->groupBy('month')
            ->orderBy('month')
            ->get();
    
        return response()->json([
            'total_mobile_ads' => $totalMobileAds,
            'total_vehicle_licenses' => $totalVehicleLicenses,
            'total_amount_paid' => $totalAmountPaid,
            // 'branding_type' => [              
            //                 'partial branding' => $partialBranding,
            //                 'full branding' => $fullBranding,
            //                 'wrap around branding' => $WrapAroundBranding,
            //             ],
            'vehicle_type' => [
                        'Light Box Cab' => $LightCab,
                        'Light Box Plus Doors Cab' => $LightPlus,
                        'Promotional Advert Vehicles (PAV) Static' => $PromotionalStatic,
                        'Promotional Advert Vehicles (PAV) Ultrawave & LED' => $PromotionalUltrawave,
                        'Intra-State Luxurious Bus Advertising (LAGBUS/LAMATA/LAWMA)' => $Bus,
            ],
            'payment_breakdown_daily' => $paymentBreakdown,
            'payment_breakdown_weekly' => $paymentBreakdownByWeek,
            'payment_breakdown_monthly' => $paymentBreakdownByMonth,
        ]);
    }
    
    
    public function temporaryadsReport(Request $request) {
        
        $startDate = Carbon::parse($request->input('start_date'));
        $endDate = Carbon::parse($request->input('end_date'));
    
        $totalMobileAds = FirstPartyTemporaryAds::whereBetween('created_at', [$startDate, $endDate])->count();
        $totalVehicleLicenses = FirstPartyTemporaryAdsDetails::whereBetween('created_at', [$startDate, $endDate])->count();
        // $partialBranding = ThirdPartyMobileVehicleLicense::where('branding_details', 'Partial Branding')->whereBetween('created_at', [$startDate, $endDate])->count();
        // $fullBranding = ThirdPartyMobileVehicleLicense::where('branding_details', 'Full Branding')->whereBetween('created_at', [$startDate, $endDate])->count();
        // $WrapAroundBranding = ThirdPartyMobileVehicleLicense::where('branding_details', 'Wrap Around Branding')->whereBetween('created_at', [$startDate, $endDate])->count();
        $Branded = FirstPartyTemporaryAdsDetails::where('type_sign', 'Branded T-Shirt')->whereBetween('created_at', [$startDate, $endDate])->count();
        $City = FirstPartyTemporaryAdsDetails::where('type_sign', 'City Walker/Skaters')->whereBetween('created_at', [$startDate, $endDate])->count();
        $Banner = FirstPartyTemporaryAdsDetails::where('type_sign', 'Banner On Vehicle(Car, Bus)')->whereBetween('created_at', [$startDate, $endDate])->count();
        $WallDrape = FirstPartyTemporaryAdsDetails::where('type_sign', 'Wall Drape For Non-Practitioner')->whereBetween('created_at', [$startDate, $endDate])->count();
        $BannerTruck = FirstPartyTemporaryAdsDetails::where('type_sign', 'Banner On A Truck')->whereBetween('created_at', [$startDate, $endDate])->count();
        $BannerRig = FirstPartyTemporaryAdsDetails::where('type_sign', 'Banner On Rig')->whereBetween('created_at', [$startDate, $endDate])->count();
        $Feather = FirstPartyTemporaryAdsDetails::where('type_sign', 'Feather Sign')->whereBetween('created_at', [$startDate, $endDate])->count();
        $Cart = FirstPartyTemporaryAdsDetails::where('type_sign', 'Cart/Trolley/Stand')->whereBetween('created_at', [$startDate, $endDate])->count();
        $Flags = FirstPartyTemporaryAdsDetails::where('type_sign', 'Flags')->whereBetween('created_at', [$startDate, $endDate])->count();
        $Banners = FirstPartyTemporaryAdsDetails::where('type_sign', 'Banners')->whereBetween('created_at', [$startDate, $endDate])->count();
        $Parasols = FirstPartyTemporaryAdsDetails::where('type_sign', 'Parasols')->whereBetween('created_at', [$startDate, $endDate])->count();
        $Roll = FirstPartyTemporaryAdsDetails::where('type_sign', 'Roll-Up Banner')->whereBetween('created_at', [$startDate, $endDate])->count();
        $Gazebos = FirstPartyTemporaryAdsDetails::where('type_sign', 'Gazebos/Tent')->whereBetween('created_at', [$startDate, $endDate])->count();
        $Kiosk = FirstPartyTemporaryAdsDetails::where('type_sign', 'Kiosk')->whereBetween('created_at', [$startDate, $endDate])->count();
        $Inflatables = FirstPartyTemporaryAdsDetails::where('type_sign', 'Inflatables/Mascot')->whereBetween('created_at', [$startDate, $endDate])->count();
        $BannerPremise = FirstPartyTemporaryAdsDetails::where('type_sign', 'Banner On Premise')->whereBetween('created_at', [$startDate, $endDate])->count();
        $WallPractitioner = FirstPartyTemporaryAdsDetails::where('type_sign', 'Wall Drape For Practitioner')->whereBetween('created_at', [$startDate, $endDate])->count();
        $DevelopmentBoards = FirstPartyTemporaryAdsDetails::where('type_sign', 'Development Boards')->whereBetween('created_at', [$startDate, $endDate])->count();
        $DevelopmentBoardsGov = FirstPartyTemporaryAdsDetails::where('type_sign', 'Development Boards For Government Contractor')->whereBetween('created_at', [$startDate, $endDate])->count();
        $FlagPole = FirstPartyTemporaryAdsDetails::where('type_sign', 'Flag Pole/Feather Banner')->whereBetween('created_at', [$startDate, $endDate])->count();
        $Mascot = FirstPartyTemporaryAdsDetails::where('type_sign', 'Inflatables/Mascot')->whereBetween('created_at', [$startDate, $endDate])->count();
        $WaterAdvertisement = FirstPartyTemporaryAdsDetails::where('type_sign', 'Water Advertisement')->whereBetween('created_at', [$startDate, $endDate])->count();
        $WrapAround = FirstPartyTemporaryAdsDetails::where('type_sign', 'Wrap Around(Construction Site)')->whereBetween('created_at', [$startDate, $endDate])->count();
        $Blimp = FirstPartyTemporaryAdsDetails::where('type_sign', 'Blimp')->whereBetween('created_at', [$startDate, $endDate])->count();
        $WaterTank = FirstPartyTemporaryAdsDetails::where('type_sign', 'Water Tank')->whereBetween('created_at', [$startDate, $endDate])->count();
        $Standard = FirstPartyTemporaryAdsDetails::where('type_sign', 'Standard')->whereBetween('created_at', [$startDate, $endDate])->count();
        $Intermediate = FirstPartyTemporaryAdsDetails::where('type_sign', 'Intermediate')->whereBetween('created_at', [$startDate, $endDate])->count();
        $Premium = FirstPartyTemporaryAdsDetails::where('type_sign', 'Premium')->whereBetween('created_at', [$startDate, $endDate])->count();
        $totalAmountPaid = PaymentFirstPartyTemporaryAds::whereBetween('created_at', [$startDate, $endDate])->sum('amount_paid');
        
        // Get breakdown of amount paid (e.g., per day)
        $paymentBreakdown = PaymentFirstPartyTemporaryAds::selectRaw('DATE(created_at) as date, SUM(amount_paid) as total')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->groupBy('date')
            ->orderBy('date')
            ->get();
    
        // Weekly Breakdown
        $paymentBreakdownByWeek = PaymentFirstPartyTemporaryAds::selectRaw('YEARWEEK(created_at) as week, SUM(amount_paid) as total')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->groupBy('week')
            ->orderBy('week')
            ->get();
    
        // Monthly Breakdown
        $paymentBreakdownByMonth = PaymentFirstPartyTemporaryAds::selectRaw("DATE_FORMAT(created_at, '%Y-%m') as month, SUM(amount_paid) as total")
            ->whereBetween('created_at', [$startDate, $endDate])
            ->groupBy('month')
            ->orderBy('month')
            ->get();
    
        return response()->json([
            'total_temporary_mobile_ads' => $totalMobileAds,
            'total_sign_types' => $totalVehicleLicenses,
            'total_amount_paid' => $totalAmountPaid,
            // 'branding_type' => [              
            //                 'partial branding' => $partialBranding,
            //                 'full branding' => $fullBranding,
            //                 'wrap around branding' => $WrapAroundBranding,
            //             ],
            'product_type' => [
                        'Branded T-Shirt' => $Branded,
                        'City Walker/Skaters' => $City,
                        'Banner On Vehicle(Car, Bus)' => $Banner,
                        'Wall Drape For Non-Practitioner' => $WallDrape,
                        'Banner On A Truck' => $BannerTruck,
                        'Banner On Rig' => $BannerRig,
                        'Feather Sign' => $Feather,
                        'Cart/Trolley/Stand' => $Cart,
                        'Flags' => $Flags,
                        'Banners' => $Banners,
                        'Parasols' => $Parasols,
                        'Roll-Up Banner' => $Roll,
                        'Gazebos/Tent' => $Gazebos,
                        'Kiosk' => $Kiosk,
                        'Inflatables/Mascot' => $Inflatables,
                        'Banner On Premise' => $BannerPremise,
                        'Wall Drape For Practitioner' => $WallPractitioner,
                        'Development Boards' => $DevelopmentBoards,
                        'Development Boards For Government Contractor' => $DevelopmentBoardsGov,
                        'Flag Pole/Feather Banner' => $FlagPole,
                        'Water Advertisement' => $WaterAdvertisement,
                        'Wrap Around(Construction Site)' => $WrapAround,
                        'Blimp' => $Blimp,
                        'Water Tank' => $WaterTank,
                        'Standard' => $Standard,
                        'Intermediate' => $Intermediate,
                        'Premium' => $Premium,
            ],
            'payment_breakdown_daily' => $paymentBreakdown,
            'payment_breakdown_weekly' => $paymentBreakdownByWeek,
            'payment_breakdown_monthly' => $paymentBreakdownByMonth,
        ]);
    }
    
    
    public function smallFormatReport(Request $request) {
        
        $startDate = Carbon::parse($request->input('start_date'));
        $endDate = Carbon::parse($request->input('end_date'));
    
        $totalMobileAds = SmallFormatAds::whereBetween('created_at', [$startDate, $endDate])->count();
        $totalVehicleLicenses = SmallFormatDetails::whereBetween('created_at', [$startDate, $endDate])->count();
        $partialBranding = SmallFormatDetails::where('sign_format', 'portrait')->whereBetween('created_at', [$startDate, $endDate])->count();
        $fullBranding = SmallFormatDetails::where('sign_format', 'landscape')->whereBetween('created_at', [$startDate, $endDate])->count();
        $kiosk = SmallFormatDetails::where('type_signs', 'Kiosk Branding')->whereBetween('created_at', [$startDate, $endDate])->count();
        $SMEkiosk = SmallFormatDetails::where('type_signs', 'SME Kiosk')->whereBetween('created_at', [$startDate, $endDate])->count();
        $ThirdPartykiosk = SmallFormatDetails::where('type_signs', 'Third Party Kiosk')->whereBetween('created_at', [$startDate, $endDate])->count();
        $roundabout = SmallFormatDetails::where('type_signs', 'Round-About')->whereBetween('created_at', [$startDate, $endDate])->count();
        $Trafficboot = SmallFormatDetails::where('type_signs', 'Traffic Warden Boot')->whereBetween('created_at', [$startDate, $endDate])->count();
        $trafficbranding = SmallFormatDetails::where('type_signs', 'Traffic Light Branding')->whereBetween('created_at', [$startDate, $endDate])->count();
        $CompositeSign = SmallFormatDetails::where('type_signs', 'Composite Directional Sign')->whereBetween('created_at', [$startDate, $endDate])->count();
        $StreetDirectional = SmallFormatDetails::where('type_signs', 'Street Directional Sign')->whereBetween('created_at', [$startDate, $endDate])->count();
        $aFrame = SmallFormatDetails::where('type_signs', 'A-Frame Board')->whereBetween('created_at', [$startDate, $endDate])->count();
        $smallformat = SmallFormatDetails::where('type_signs', 'Small Format Iconic Cube')->whereBetween('created_at', [$startDate, $endDate])->count();
        $BusShelter = SmallFormatDetails::where('type_signs', 'Bus Shelter')->whereBetween('created_at', [$startDate, $endDate])->count();
        $streetfurniture = SmallFormatDetails::where('type_signs', 'Street Furniture')->whereBetween('created_at', [$startDate, $endDate])->count();
        $buildingbranding = SmallFormatDetails::where('type_signs', 'Building Branding')->whereBetween('created_at', [$startDate, $endDate])->count();
        $WaterAdvertisement = SmallFormatDetails::where('type_signs', 'Water Advertisement')->whereBetween('created_at', [$startDate, $endDate])->count();
        $wallmoral = SmallFormatDetails::where('type_signs', 'Wall Moral')->whereBetween('created_at', [$startDate, $endDate])->count();
        $FloorMat = SmallFormatDetails::where('type_signs', 'Floor Mat')->whereBetween('created_at', [$startDate, $endDate])->count();
        $containers = SmallFormatDetails::where('type_signs', 'Containers')->whereBetween('created_at', [$startDate, $endDate])->count();
        $entrance = SmallFormatDetails::where('type_signs', 'Entrance Arcade')->whereBetween('created_at', [$startDate, $endDate])->count();
        $LED = SmallFormatDetails::where('type_signs', 'LED At Filling Station')->whereBetween('created_at', [$startDate, $endDate])->count();
        $other = SmallFormatDetails::where('type_signs', 'Other')->whereBetween('created_at', [$startDate, $endDate])->count();
        $totalAmountPaid = PaymentSmallFormatAds::whereBetween('created_at', [$startDate, $endDate])->sum('amount_paid');
        
        // Get breakdown of amount paid (e.g., per day)
        $paymentBreakdown = PaymentSmallFormatAds::selectRaw('DATE(created_at) as date, SUM(amount_paid) as total')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->groupBy('date')
            ->orderBy('date')
            ->get();
    
        // Weekly Breakdown
        $paymentBreakdownByWeek = PaymentSmallFormatAds::selectRaw('YEARWEEK(created_at) as week, SUM(amount_paid) as total')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->groupBy('week')
            ->orderBy('week')
            ->get();
    
        // Monthly Breakdown
        $paymentBreakdownByMonth = PaymentSmallFormatAds::selectRaw("DATE_FORMAT(created_at, '%Y-%m') as month, SUM(amount_paid) as total")
            ->whereBetween('created_at', [$startDate, $endDate])
            ->groupBy('month')
            ->orderBy('month')
            ->get();
    
        return response()->json([
            'total_small_farmat_ads' => $totalMobileAds,
            'total_signs' => $totalVehicleLicenses,
            'total_amount_paid' => $totalAmountPaid,
            'sign_format' => [              
                            'partial branding' => $partialBranding,
                            'full branding' => $fullBranding,
                        ],
            'sign_type' => [
                        'Kiosk Branding' => $kiosk,
                        'SME Kiosk' => $SMEkiosk,
                        'Third Party Kiosk' => $ThirdPartykiosk,
                        'Round About' => $roundabout,
                        'Traffic Warden Boot' => $Trafficboot,
                        'Traffic Light Branding' => $trafficbranding,
                        'Composite Directional Sign' => $CompositeSign,
                        'Street Directional Sign' => $StreetDirectional,
                        'A Frame Board' => $aFrame,
                        'Small Format Iconic Cube' => $smallformat,
                        'Bus Shelter' => $BusShelter,
                        'Street Furniture' => $streetfurniture,
                        'Building Branding' => $buildingbranding,
                        'Water Advertisement' => $WaterAdvertisement,
                        'Wall Moral' => $wallmoral,
                        'Floor Mat' => $FloorMat,
                        'Containers' => $containers,
                        'Entrance Arcade' => $entrance,
                        'LED At Filling Station' => $LED,
                        'Other' => $other,
            ],
            'payment_breakdown_daily' => $paymentBreakdown,
            'payment_breakdown_weekly' => $paymentBreakdownByWeek,
            'payment_breakdown_monthly' => $paymentBreakdownByMonth,
        ]);
    }
    
    
    public function streetLampReport(Request $request) {
        
        $startDate = Carbon::parse($request->input('start_date'));
        $endDate = Carbon::parse($request->input('end_date'));
    
        $totalMobileAds = StreetLampsAds::whereBetween('created_at', [$startDate, $endDate])->count();
        $totalVehicleLicenses = StreetLampInfo::whereBetween('created_at', [$startDate, $endDate])->count();
        $partialBranding = StreetLampInfo::where('type_of_sign', 'LED')->whereBetween('created_at', [$startDate, $endDate])->count();
        $fullBranding = StreetLampInfo::where('type_of_sign', 'FLEX')->whereBetween('created_at', [$startDate, $endDate])->count();
        $WrapAroundBranding = StreetLampInfo::where('type_of_sign', 'BACKLIT')->whereBetween('created_at', [$startDate, $endDate])->count();
        $totalAmountPaid = PaymentStreetLamp::whereBetween('created_at', [$startDate, $endDate])->sum('amount_paid');
        
        // Get breakdown of amount paid (e.g., per day)
        $paymentBreakdown = PaymentStreetLamp::selectRaw('DATE(created_at) as date, SUM(amount_paid) as total')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->groupBy('date')
            ->orderBy('date')
            ->get();
    
        // Weekly Breakdown
        $paymentBreakdownByWeek = PaymentStreetLamp::selectRaw('YEARWEEK(created_at) as week, SUM(amount_paid) as total')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->groupBy('week')
            ->orderBy('week')
            ->get();
    
        // Monthly Breakdown
        $paymentBreakdownByMonth = PaymentStreetLamp::selectRaw("DATE_FORMAT(created_at, '%Y-%m') as month, SUM(amount_paid) as total")
            ->whereBetween('created_at', [$startDate, $endDate])
            ->groupBy('month')
            ->orderBy('month')
            ->get();
    
        return response()->json([
            'total_street_lamp_ads' => $totalMobileAds,
            'total_signs' => $totalVehicleLicenses,
            'total_amount_paid' => $totalAmountPaid,
            'type_of_sign' => [              
                            'LED' => $partialBranding,
                            'FLEX' => $fullBranding,
                            'BACKLIT' => $WrapAroundBranding,
                        ],
            'payment_breakdown_daily' => $paymentBreakdown,
            'payment_breakdown_weekly' => $paymentBreakdownByWeek,
            'payment_breakdown_monthly' => $paymentBreakdownByMonth,
        ]);
    }
    
    
    public function customerReport() {
            
        $userId = Auth::user()->id;
    
        $totalMobileAds = mobilead::where('user_id', $userId)->count();
        $totalStreetLamp = StreetLampsAds::where('user_id', $userId)->count();
        $totalSmallFormatAds = SmallFormatAds::where('user_id', $userId)->count();
        $totalFirstPartyTemporaryAds = FirstPartyTemporaryAds::where('user_id', $userId)->count();
        $totalThirdPartyMobile = ThirdPartyMobile::where('user_id', $userId)->count();
        $totalThirdPartyOutdoor = ThirdPartyOutdoor::where('user_id', $userId)->count();
        $totalOnpremise = onpremise::where('user_id', $userId)->count();
        
        $totalMobileAdsPending = mobilead::where('user_id', $userId)->where(function($query) {
        $query->where('final_status', 'pending')
              ->orWhereNull('final_status');
                })->count();
        $totalStreetLampPending = StreetLampsAds::where('user_id', $userId)->where(function($query) {
        $query->where('final_status', 'pending')
              ->orWhereNull('final_status');
                })->count();
        $totalSmallFormatAdsids = SmallFormatAds::where('user_id', $userId)->get();
        $totalSmallFormatAdsPending = [];
        foreach($totalSmallFormatAdsids as $totalSmallFormatAdsid){
            $totalSmallFormatAdsPending [] = SmallFormatDetails::where('small_format_ads_id', $totalSmallFormatAdsid->id)->where(function($query) {
            $query->where('md_approval', 'pending')
                  ->orWhereNull('md_approval');
                    })->count();   
        }
        
        $totalFirstPartyTemporaryAdsPending = FirstPartyTemporaryAds::where('user_id', $userId)->where(function($query) {
        $query->where('md_approval', 'pending')
              ->orWhereNull('md_approval');
                })->count();
        $totalThirdPartyMobilePending = ThirdPartyMobile::where('user_id', $userId)->where(function($query) {
        $query->where('final_status', 'pending')
              ->orWhereNull('final_status');
                })->count();
        $totalThirdPartyOutdoorPending = ThirdPartyOutdoor::where('user_id', $userId)->where(function($query) {
        $query->where('md_second_approval', 'pending')
              ->orWhereNull('md_second_approval');
                })->count();
        $totalOnpremisePending = onpremise::where('user_id', $userId)->where(function($query) {
        $query->where('final_status', 'pending')
              ->orWhereNull('final_status');
                })->count();
                
                
                
        $totalMobileAdscompleted = mobilead::where('user_id', $userId)->where(function($query) {
        $query->where('final_status', 'approved');
                })->count();
        $totalStreetLampcompleted = StreetLampsAds::where('user_id', $userId)->where(function($query) {
        $query->where('final_status', 'approved');
                })->count();
        $totalSmallFormatAdsids = SmallFormatAds::where('user_id', $userId)->get();
        $totalSmallFormatAdscompleted = [];
        foreach($totalSmallFormatAdsids as $totalSmallFormatAdsid){
            $totalSmallFormatAdscompleted [] = SmallFormatDetails::where('small_format_ads_id', $totalSmallFormatAdsid->id)->where(function($query) {
            $query->where('md_approval', 'approved');
                    })->count();   
        }
        
        $totalFirstPartyTemporaryAdscompleted = FirstPartyTemporaryAds::where('user_id', $userId)->where(function($query) {
        $query->where('md_approval', 'approved');
                })->count();
        $totalThirdPartyMobilecompleted = ThirdPartyMobile::where('user_id', $userId)->where(function($query) {
        $query->where('final_status', 'approved');
                })->count();
        $totalThirdPartyOutdoorcompleted = ThirdPartyOutdoor::where('user_id', $userId)->where(function($query) {
        $query->where('md_second_approval', 'approved');
                })->count();
        $totalOnpremisecompleted = onpremise::where('user_id', $userId)->where(function($query) {
        $query->where('final_status', 'approved');
                })->count();
                
                
                
        $totalMobileAdsdeclined = mobilead::where('user_id', $userId)->where(function($query) {
        $query->where('hod_permit_status', 'declined');
                })->count();
        $totalStreetLampdeclined = StreetLampsAds::where('user_id', $userId)->where(function($query) {
        $query->where('hod_permit_status', 'declined');
                })->count();
        $totalSmallFormatAdsids = SmallFormatAds::where('user_id', $userId)->get();
        $totalSmallFormatAdsdeclined = [];
        foreach($totalSmallFormatAdsids as $totalSmallFormatAdsid){
            $totalSmallFormatAdsdeclined [] = SmallFormatDetails::where('small_format_ads_id', $totalSmallFormatAdsid->id)->where(function($query) {
            $query->where('md_approval', 'disapproved');
                    })->count();   
        }
        
        $totalFirstPartyTemporaryAdsdeclined = FirstPartyTemporaryAds::where('user_id', $userId)->where(function($query) {
        $query->where('md_approval', 'disapproved');
                })->count();
        $totalThirdPartyMobiledeclined = ThirdPartyMobile::where('user_id', $userId)->where(function($query) {
        $query->where('hod_permit_status', 'declined');
                })->count();
        $totalThirdPartyOutdoordeclined = ThirdPartyOutdoor::where('user_id', $userId)->where(function($query) {
        $query->where('md_second_approval', 'disapproved');
                })->count();
        $totalOnpremisedeclined = onpremise::where('user_id', $userId)->where(function($query) {
        $query->where('final_status', 'declined');
                })->count();
        
        
    
        return response()->json([
            'total' => $totalMobileAds + $totalStreetLamp + $totalSmallFormatAds + $totalFirstPartyTemporaryAds + $totalThirdPartyMobile + $totalThirdPartyOutdoor + $totalOnpremise,
            'total_pending' => $totalMobileAdsPending + $totalStreetLampPending + array_sum($totalSmallFormatAdsPending) + $totalFirstPartyTemporaryAdsPending + $totalThirdPartyMobilePending + $totalThirdPartyOutdoorPending + $totalOnpremisePending,
            'total_completed' => $totalMobileAdscompleted + $totalStreetLampcompleted + array_sum($totalSmallFormatAdscompleted) + $totalFirstPartyTemporaryAdscompleted + $totalThirdPartyMobilecompleted + $totalThirdPartyOutdoorcompleted + $totalOnpremisecompleted,
            'total_declined' => $totalMobileAdsdeclined + $totalStreetLampdeclined + array_sum($totalSmallFormatAdsdeclined) + $totalFirstPartyTemporaryAdsdeclined+ $totalThirdPartyMobiledeclined + $totalThirdPartyOutdoordeclined + $totalOnpremisedeclined,
        ]);
    }
    
    public function mobiledownloadcsv (Request $request) {
        
        $startDate = Carbon::parse($request->input('start_date'));
        $endDate = Carbon::parse($request->input('end_date'));
        
        $csvMobileAds = mobilead::whereBetween('created_at', [$startDate, $endDate])
            ->where('final_status', 'approved')
            ->with(['mobileAdsVehicleLicense', 'user'])
            ->get();
        // var_dump($csvMobileAds->toArray());die();
        
        // Prepare CSV content
        $csvHeader = ['Brand Name', 'Email', 'Phone Number', 'Vehicle License', 'Branding Details', 'Vehicle Type'];
        $csvLines = [];
        
        foreach ($csvMobileAds as $mobileAd) {
            // Check if there are related vehicle licenses
            if ($mobileAd->mobileAdsVehicleLicense->isNotEmpty()) {
                
                foreach ($mobileAd->mobileAdsVehicleLicense as $license) {
                    $csvLines[] = [
                        $mobileAd->brand_name,
                        $mobileAd->user->email,
                        $mobileAd->user->phone_number,
                        $license->vehicle_license ?? '',
                        $license->branding_details ?? '',
                        $license->vehicle_type ?? ''
                    ];
                }
            } else {
                // If no vehicle licenses, still include the brand name with empty values
                $csvLines[] = [
                    $mobileAd->brand_name,
                    '',
                    '',
                    '',
                    '',
                    ''
                ];
            }
        }
        
        // Generate CSV content
        $csvContent = implode(',', $csvHeader) . "\n";
        
        foreach ($csvLines as $line) {
            // Escape fields that might contain commas or quotes
            $escapedLine = array_map(function ($field) {
                return '"' . str_replace('"', '""', $field) . '"';
            }, $line);
            
            $csvContent .= implode(',', $escapedLine) . "\n";
        }
        
        // Define filename with date range
        $filename = 'mobile_ads_export_' . $startDate . '_to_' . $endDate . '.csv';
        
        // Return as download response
        return response()->streamDownload(function () use ($csvContent) {
            echo $csvContent;
        }, $filename, [
            'Content-Type' => 'text/csv',
        ]);
    }
    
    public function onpremisedownloadcsv (Request $request) {
        
        $startDate = Carbon::parse($request->input('start_date'));
        $endDate = Carbon::parse($request->input('end_date'));
        $lga = $request->input('lga');
        
        $csvMobileAds = onpremise::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->where('lga', $lga);
            })
            ->with(['onpremiseInfo'])
            ->get();
        
        // Prepare CSV content
        $csvHeader = ['Business Name', 'Email', 'Phone Number', 'Sign Address', 'L.G.A', 'Property Type', 'Street Category', 'Type Of Sign', 'Number Of Sign', 'Length (m)', 'Width (m)'];
        $csvLines = [];
        
        foreach ($csvMobileAds as $mobileAd) {
            // Check if there are related vehicle licenses
            if ($mobileAd->onpremiseInfo->isNotEmpty()) {
                
                foreach ($mobileAd->onpremiseInfo as $license) {
                    $csvLines[] = [
                        $mobileAd->business_name,
                        $mobileAd->email,
                        $mobileAd->phone_number,
                        $mobileAd->sign_address,
                        $mobileAd->lga,
                        $mobileAd->property_type,
                        $mobileAd->street_category,
                        $license->type_of_sign ?? '',
                        $license->number_of_sign ?? '',
                        $license->length_in_meter ?? '',
                        $license->width_in_meter ?? ''
                    ];
                }
            } else {
                // If no vehicle licenses, still include the brand name with empty values
                $csvLines[] = [
                    $mobileAd->business_name,
                    $mobileAd->email,
                    $mobileAd->phone_number,
                    $mobileAd->sign_address,
                    $mobileAd->property_type,
                    $mobileAd->street_category,
                    '',
                    '',
                    '',
                    ''
                ];
            }
        }
        
        // Generate CSV content
        $csvContent = implode(',', $csvHeader) . "\n";
        
        foreach ($csvLines as $line) {
            // Escape fields that might contain commas or quotes
            $escapedLine = array_map(function ($field) {
                return '"' . str_replace('"', '""', $field) . '"';
            }, $line);
            
            $csvContent .= implode(',', $escapedLine) . "\n";
        }
        
        // Define filename with date range
        $filename = 'onpremise_ads_export_' . $startDate . '_to_' . $endDate . '.csv';
        
        // Return as download response
        return response()->streamDownload(function () use ($csvContent) {
            echo $csvContent;
        }, $filename, [
            'Content-Type' => 'text/csv',
        ]);
    }
    
    public function outdoordownloadcsv (Request $request) {
        
        $startDate = Carbon::parse($request->input('start_date'));
        $endDate = Carbon::parse($request->input('end_date'));
        $lga = $request->input('lga');
        
        $csvMobileAds = ThirdPartyOutdoor::whereBetween('created_at', [$startDate, $endDate])
            ->when($lga, function ($query, $lga) {
                return $query->where('lga', $lga);
            })
            ->with(['thirdPartyOutdoorSignDetails', 'user'])
            ->get();
            
        
        // Prepare CSV content
        $csvHeader = ['Business Name', 'Email', 'Phone Number', 'Sign Address', 'L.G.A', 'Type Of Sign', 'Property Owner', 'Property Owner Phone', 'Street Category', 'Dimension Of Sign', 'Number Faces', 'Signage Format', 'Sign Illumination', 'Sign Interaction'];
        $csvLines = [];
        
        foreach ($csvMobileAds as $mobileAd) {
            // Check if there are related vehicle licenses
            if ($mobileAd->thirdPartyOutdoorSignDetails) {
                
                foreach ($mobileAd->thirdPartyOutdoorSignDetails as $license) {
                    $csvLines[] = [
                        $mobileAd->user->business_name,
                        $mobileAd->user->email,
                        $mobileAd->user->phone_number,
                        $mobileAd->address_proposed_site,
                        $mobileAd->lga,
                        $mobileAd->type_sign,
                        $mobileAd->property_owner_name,
                        $mobileAd->property_owner_phone,
                        $license->street_category ?? '',
                        $license->dimension_sign ?? '',
                        $license->number_faces ?? '',
                        $license->signage_format ?? '',
                        $license->sign_illumination ?? '',
                        $license->sign_interaction ?? ''
                    ];
                }
            } else {
                // If no vehicle licenses, still include the brand name with empty values
                $csvLines[] = [
                    $mobileAd->business_name,
                    $mobileAd->email,
                    $mobileAd->phone_number,
                    $mobileAd->sign_address,
                    $mobileAd->property_type,
                    $mobileAd->street_category,
                    '',
                    '',
                    '',
                    ''
                ];
            }
        }
        
        // Generate CSV content
        $csvContent = implode(',', $csvHeader) . "\n";
        
        foreach ($csvLines as $line) {
            // Escape fields that might contain commas or quotes
            $escapedLine = array_map(function ($field) {
                return '"' . str_replace('"', '""', $field) . '"';
            }, $line);
            
            $csvContent .= implode(',', $escapedLine) . "\n";
        }
        
        // Define filename with date range
        $filename = 'outdoor_ads_export_' . $startDate . '_to_' . $endDate . '.csv';
        
        // Return as download response
        return response()->streamDownload(function () use ($csvContent) {
            echo $csvContent;
        }, $filename, [
            'Content-Type' => 'text/csv',
        ]);
    }
    
    public function thirdpartymobiledownloadcsv (Request $request) {
        
        $startDate = Carbon::parse($request->input('start_date'));
        $endDate = Carbon::parse($request->input('end_date'));
        
        $csvMobileAds = ThirdPartyMobile::whereBetween('created_at', [$startDate, $endDate])
            ->where('final_status', 'approved')
            ->with(['thirdPartyMobileVehicleLincense', 'user'])
            ->get();
        // var_dump($csvMobileAds->toArray());die();
        
        // Prepare CSV content
        $csvHeader = ['Brand Name', 'Email', 'Phone Number', 'Apcon Number', 'Vehicle License', 'Vehicle Type'];
        $csvLines = [];
        
        foreach ($csvMobileAds as $mobileAd) {
            // Check if there are related vehicle licenses
            if ($mobileAd->thirdPartyMobileVehicleLincense->isNotEmpty()) {
                
                foreach ($mobileAd->thirdPartyMobileVehicleLincense as $license) {
                    $csvLines[] = [
                        $mobileAd->brand_name,
                        $mobileAd->user->email,
                        $mobileAd->user->phone_number,
                        $mobileAd->apcon_number ?? '',
                        $license->vehicle_license ?? '',
                        $license->vehicle_type ?? ''
                    ];
                }
            } else {
                // If no vehicle licenses, still include the brand name with empty values
                $csvLines[] = [
                    $mobileAd->brand_name,
                    '',
                    '',
                    '',
                    '',
                    ''
                ];
            }
        }
        
        // Generate CSV content
        $csvContent = implode(',', $csvHeader) . "\n";
        
        foreach ($csvLines as $line) {
            // Escape fields that might contain commas or quotes
            $escapedLine = array_map(function ($field) {
                return '"' . str_replace('"', '""', $field) . '"';
            }, $line);
            
            $csvContent .= implode(',', $escapedLine) . "\n";
        }
        
        // Define filename with date range
        $filename = 'thirdparty_mobile_ads_export_' . $startDate . '_to_' . $endDate . '.csv';
        
        // Return as download response
        return response()->streamDownload(function () use ($csvContent) {
            echo $csvContent;
        }, $filename, [
            'Content-Type' => 'text/csv',
        ]);
    }
    
    public function temporarydownloadcsv (Request $request) {
        
        $startDate = Carbon::parse($request->input('start_date'));
        $endDate = Carbon::parse($request->input('end_date'));
        
        $csvMobileAds = FirstPartyTemporaryAds::whereBetween('created_at', [$startDate, $endDate])
            ->with(['firstPartyTemporaryAdsDetails', 'user'])
            ->get();
        // var_dump($csvMobileAds->toArray());die();
        
        // Prepare CSV content
        $csvHeader = ['Name Of Company', 'Company Address', 'L.G.A', 'Location', 'Start Date', 'End Date', 'Duration', 'Email', 'Phone Number', 'Class Of Sign', 'Type Of Sign', 'Size (m)', 'Number Of Days', 'Duration In Days', 'Traffic Statement'];
        $csvLines = [];
        
        foreach ($csvMobileAds as $mobileAd) {
            // Check if there are related vehicle licenses
            if ($mobileAd->firstPartyTemporaryAdsDetails->isNotEmpty()) {
                
                foreach ($mobileAd->firstPartyTemporaryAdsDetails as $license) {
                    $csvLines[] = [
                        $mobileAd->name_of_company,
                        $mobileAd->company_address,
                        $mobileAd->lga,
                        $mobileAd->site_location,
                        $mobileAd->start_date,
                        $mobileAd->end_date,
                        $mobileAd->duration,
                        $mobileAd->user->email,
                        $mobileAd->user->phone_number,
                        $license->class_signs ?? '',
                        $license->type_sign ?? '',
                        $license->size_ads ?? '',
                        $license->number_of_days ?? '',
                        $license->days_in_duration ?? '',
                        $license->traffic_statement ?? '',
                    ];
                }
            } else {
                // If no vehicle licenses, still include the brand name with empty values
                $csvLines[] = [
                    $mobileAd->brand_name,
                    '',
                    '',
                    '',
                    '',
                    ''
                ];
            }
        }
        
        // Generate CSV content
        $csvContent = implode(',', $csvHeader) . "\n";
        
        foreach ($csvLines as $line) {
            // Escape fields that might contain commas or quotes
            $escapedLine = array_map(function ($field) {
                return '"' . str_replace('"', '""', $field) . '"';
            }, $line);
            
            $csvContent .= implode(',', $escapedLine) . "\n";
        }
        
        // Define filename with date range
        $filename = 'temporary_ads_export_' . $startDate . '_to_' . $endDate . '.csv';
        
        // Return as download response
        return response()->streamDownload(function () use ($csvContent) {
            echo $csvContent;
        }, $filename, [
            'Content-Type' => 'text/csv',
        ]);
    }
    
    public function smallformatdownloadcsv (Request $request) {
        
        $startDate = Carbon::parse($request->input('start_date'));
        $endDate = Carbon::parse($request->input('end_date'));
        
        $csvMobileAds = SmallFormatAds::whereBetween('created_at', [$startDate, $endDate])
            ->with(['smallFormatDetails', 'user'])
            ->get();
        // var_dump($csvMobileAds->toArray());die();
        
        // Prepare CSV content
        $csvHeader = ['Type Of Signs', 'Sign Format', 'Sign Site', 'Illumination', 'Interactivity', 'Electricity Source', 'Email', 'Phone Number', 'Address Of Site', 'L.G.A', 'Property Owner Phone Number'];
        $csvLines = [];
        
        foreach ($csvMobileAds as $mobileAd) {
            // Check if there are related vehicle licenses
            if ($mobileAd->smallFormatDetails->isNotEmpty()) {
                
                foreach ($mobileAd->smallFormatDetails as $license) {
                    $csvLines[] = [
                        $license->type_signs ?? '',
                        $license->sign_format ?? '',
                        $license->sign_site ?? '',
                        $license->illumination ?? '',
                        $license->interactivity ?? '',
                        $license->electricity_source ?? '',
                        $mobileAd->user->email,
                        $mobileAd->user->phone_number,
                        $mobileAd->address_site,
                        $mobileAd->lga,
                        $mobileAd->property_owner_phone
                    ];
                }
            } else {
                // If no vehicle licenses, still include the brand name with empty values
                $csvLines[] = [
                    $mobileAd->property_owner_phone,
                    '',
                    '',
                    '',
                    '',
                    ''
                ];
            }
        }
        
        // Generate CSV content
        $csvContent = implode(',', $csvHeader) . "\n";
        
        foreach ($csvLines as $line) {
            // Escape fields that might contain commas or quotes
            $escapedLine = array_map(function ($field) {
                return '"' . str_replace('"', '""', $field) . '"';
            }, $line);
            
            $csvContent .= implode(',', $escapedLine) . "\n";
        }
        
        // Define filename with date range
        $filename = 'small_format_ads_export_' . $startDate . '_to_' . $endDate . '.csv';
        
        // Return as download response
        return response()->streamDownload(function () use ($csvContent) {
            echo $csvContent;
        }, $filename, [
            'Content-Type' => 'text/csv',
        ]);
    }
    
    public function streetLampdownloadcsv (Request $request) {
        
        $startDate = Carbon::parse($request->input('start_date'));
        $endDate = Carbon::parse($request->input('end_date'));
        
        $csvMobileAds = StreetLampsAds::whereBetween('created_at', [$startDate, $endDate])
            ->with(['streetLampInfo', 'user'])
            ->get();
        // var_dump($csvMobileAds->toArray());die();
        
        // Prepare CSV content
        $csvHeader = ['Name Of Display', 'Type Of Signs', 'Email', 'Phone Number', 'Address Of Site', 'Start Date', 'End Date', 'Number Of Poles', 'Street Category'];
        $csvLines = [];
        
        foreach ($csvMobileAds as $mobileAd) {
            // Check if there are related vehicle licenses
            if ($mobileAd->streetLampInfo->isNotEmpty()) {
                
                foreach ($mobileAd->streetLampInfo as $license) {
                    $csvLines[] = [
                        $mobileAd->name_of_display,
                        $license->type_of_sign ?? '',
                        $mobileAd->user->email,
                        $mobileAd->user->phone_number,
                        $license->site_address ?? '',
                        $mobileAd->start_date,
                        $mobileAd->due_date,
                        $license->number_poles,
                        $license->street_category ?? ''
                    ];
                }
            } else {
                // If no vehicle licenses, still include the brand name with empty values
                $csvLines[] = [
                    $mobileAd->name_of_display,
                    '',
                    '',
                    '',
                    '',
                    ''
                ];
            }
        }
        
        // Generate CSV content
        $csvContent = implode(',', $csvHeader) . "\n";
        
        foreach ($csvLines as $line) {
            // Escape fields that might contain commas or quotes
            $escapedLine = array_map(function ($field) {
                return '"' . str_replace('"', '""', $field) . '"';
            }, $line);
            
            $csvContent .= implode(',', $escapedLine) . "\n";
        }
        
        // Define filename with date range
        $filename = 'streetLamp_ads_export_' . $startDate . '_to_' . $endDate . '.csv';
        
        // Return as download response
        return response()->streamDownload(function () use ($csvContent) {
            echo $csvContent;
        }, $filename, [
            'Content-Type' => 'text/csv',
        ]);
    }

}
