<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use App\Models\Emirate;
use App\Models\Nationality;
use App\Models\PaymentReport;
use App\Models\PricingPlan;
use App\Models\Sect;
use App\Models\User;
use App\Models\UserMatch;
use App\Models\UserRequest;
use Barryvdh\DomPDF\Facade\Pdf;
use Carbon\Carbon;
use Illuminate\Http\Request;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use Yajra\DataTables\Facades\DataTables;


class ReportController extends Controller
{
    public function maleCustomeRegistration(Request $request)
    {
        $users = [];
        $today = date("Y-m-d");
        $nationalities = Nationality::where('status', 1)->get();
        $emirates = Emirate::where('status', 1)->get();
        $sects = Sect::where('status', 1)->get();
        $plans = PricingPlan::where('status', 1)->get();

        return view('admin.report.maleuser', compact('users','today', 'nationalities', 'sects', 'plans', 'emirates'));
    }

    public function femaleCustomeRegistration(Request $request)
    {
        $users = [];
        $today = date("Y-m-d");
        $nationalities = Nationality::where('status', 1)->get();
        $emirates = Emirate::where('status', 1)->get();
        $sects = Sect::where('status', 1)->get();
        $plans = PricingPlan::where('status', 1)->get();

        return view('admin.report.femaleuser', compact('users','today', 'nationalities', 'sects', 'plans', 'emirates'));
    }

    public function getUserRegistrationList(Request $request)
    {
        $sqlBuilder = User::select([
            'users.id',
            'users.name',
            'users.nick_name',
            'users.dial_code',
            'users.phone',
            'users.email',
            'users.status',
            'users.gender',
            'users.created_at',
            'users.emirates_id',
            'users.profile_image',
            'users.user_number',
            'users.parent_approval',
            'sects.name as sect_name',
            'nationalities.name as nationality_name',
            'user_plans.name as plan_name',
        ])
        ->distinct()
        ->leftJoin('nationalities', 'users.nationality_id', '=', 'nationalities.id')
        ->leftJoin('user_details', 'users.id', '=', 'user_details.user_id')
        ->leftJoin('sects', 'user_details.sect_id', '=', 'sects.id')
        ->leftJoin('user_plans', 'users.id', '=', 'user_plans.user_id')
        ->where(['role_id' => 2 ,'deleted' => 0]);

        if ($request->has('gender') && $request->gender != 'all') {
            $sqlBuilder->where('users.gender', $request->gender);
        }

        $sqlBuilder->orderBy('id', 'desc');

        return DataTables::of($sqlBuilder)
            ->filter(function ($query) use ($request) {
                if ($request->has('search') && !empty($request->search['value'])) {
                    $query->where(function ($query) use ($request) {
                        $query->where('users.name', 'ilike', "%{$request->search['value']}%")
                        ->orWhere('users.nick_name', 'ilike', "%{$request->search['value']}%")
                        ->orWhere('users.dial_code', 'ilike', "%{$request->search['value']}%")
                        ->orWhere('users.phone', 'ilike', "%{$request->search['value']}%")
                        ->orWhere('users.email', 'ilike', "%{$request->search['value']}%")
                        ->orWhere('users.user_number', 'ilike', "%{$request->search['value']}%");
                    });
                }

                if ($request->has('nationality_id') && $request->nationality_id != 'all') {
                    $query->where('users.nationality_id', $request->nationality_id);
                }

                if($request->has('emirates_id') && $request->emirates_id != 'all'){
                    $query->where('users.emirates_id', $request->emirates_id);
                }

                if ($request->has('sect_id') && $request->sect_id != 'all') {
                    $query->where('user_details.sect_id', $request->sect_id);
                }

                if ($request->has('plan_id') && $request->plan_id != 'all') {
                    $query->where('user_plans.plan_id', $request->plan_id)->where('user_plans.status', 1);
                }

                if ($request->has('status') && $request->status != 'all') {
                    $query->where('users.status', $request->status);
                }

                if ($request->has('parent_approval') && $request->parent_approval != 'all') {
                    $query->where('users.parent_approval', $request->parent_approval);
                }

                if ($request->has('start_date') && !empty($request->start_date)) {
                    $query->whereDate('users.created_at', '>=', date('Y-m-d', strtotime($request->start_date)));
                }

                if ($request->has('end_date') && !empty($request->end_date)) {
                    $query->whereDate('users.created_at', '<=', date('Y-m-d', strtotime($request->end_date)));
                }
            })
            ->editColumn('user_image', function ($data) {
                $profileImage = !empty($data->profile_image_url) ? $data->profile_image_url : asset('assets/images/camera_placeholder.png');
                return '<div style="text-align:center;"><img src="' . $profileImage . '" class="rounded-circle avatar-xs" alt="profile-image"></div>';
                
            })
            ->editColumn('subscription_plan', function ($data) {
                return $data->plan_name ?? 'N/A';
            })
            ->editColumn('phone', function ($data) {
                return $data->dial_code . ' ' . $data->phone;
            })
            ->editColumn('status', function ($data) {
            return $data->status == 1 
                ? '<span class="badge bg-success">Active</span>' 
                : '<span class="badge bg-danger">Inactive</span>';
            })
            ->editColumn('parent_approval', function ($data) {
                switch ($data->parent_approval) {
                    case 0:
                        return '<span class="badge bg-warning">Not Approved</span>';
                        break;
                    case 1:
                        return '<span class="badge bg-success">Approved</span>';
                        break;
                    case 2:
                        return '<span class="badge bg-danger">Rejected</span>';
                        break;
                    default:
                    return '<span class="badge bg-warning">Not Approved</span>';
                        break;
                }
            })
            ->editColumn('created_at', function ($data) {
            return $data->created_at->format('d/m/y H:i A');
            })
            ->addColumn('action', function ($data) {
                if($data->gender == GENDER_MALE){
                    $url = route('admin.users.editmale', ['id' => $data->id]);
                } else{
                    $url = route('admin.users.editfemale', ['id' => $data->id]);
                }

                $detailsUrl = route('admin.users.details', ['id' => $data->id]);
                $html = '<div class="dropdown">
                                        <a href="#" class="btn btn-soft-secondary btn-sm" role="button" id="dropdownMenuLink5" data-bs-toggle="dropdown" aria-expanded="false">
                                            <i class="ri-more-2-fill"></i>
                                        </a>

                                        <ul class="dropdown-menu" aria-labelledby="dropdownMenuLink5">
                                            <li><a class="dropdown-item" href="' . $detailsUrl . '">Details</a></li>
                                        </ul>
                                    </div>';
            return $html;
            })
            ->rawColumns(['parent_approval','user_image','status','action'])
            ->make(true);
    }

    public function getCustomeRegistrationExcel(Request $request)
    {
        $users = User::select('users.*')->distinct()->with('nationality', 'details.sect', 'plan')
        ->leftJoin('nationalities', 'users.nationality_id', '=', 'nationalities.id')
        ->leftJoin('user_details', 'users.id', '=', 'user_details.user_id')
        ->leftJoin('sects', 'user_details.sect_id', '=', 'sects.id')
        ->leftJoin('user_plans', 'users.id', '=', 'user_plans.user_id')->where('role_id', 2)->where('deleted', 0)->orderBy('users.id', 'desc'); 
        
        if ($request->has('search') && !empty($request->search)) {
            $users->where(function ($query) use ($request) {
                $query->where('users.name', 'ilike', "%{$request->search['value']}%")
                ->orWhere('users.nick_name', 'ilike', "%{$request->search['value']}%")
                ->orWhere('users.dial_code', 'ilike', "%{$request->search['value']}%")
                ->orWhere('users.phone', 'ilike', "%{$request->search['value']}%")
                ->orWhere('users.email', 'ilike', "%{$request->search['value']}%")
                ->orWhere('users.user_number', 'ilike', "%{$request->search['value']}%");
            });
        }

        if ($request->has('gender') && $request->gender != 'all') {
            $users->where('users.gender', $request->gender);
        }

        if ($request->has('nationality_id') && $request->nationality_id != 'all') {
            $users->where('users.nationality_id', $request->nationality_id);
        }

        if($request->has('emirates_id') && $request->emirates_id != 'all'){
            $users->where('users.emirates_id', $request->emirates_id);
        }

        if ($request->has('parent_approval') && $request->parent_approval != 'all') {
            $users->where('users.parent_approval', $request->parent_approval);
        }

        if ($request->has('sect_id') && $request->sect_id != 'all') {
            $users->where('user_details.sect_id', $request->sect_id);
        }

        if ($request->has('plan_id') && $request->plan_id != 'all') {
            $users->where('user_plans.plan_id', $request->plan_id)->where('user_plans.status', 1);
        }

        if ($request->has('status') && $request->status != 'all') {
            $users->where('users.status', $request->status);
        }

        if ($request->has('start_date') && !empty($request->start_date)) {
            $users->whereDate('users.created_at', '>=', date('Y-m-d', strtotime($request->start_date)));
        }

        if ($request->has('end_date') && !empty($request->end_date)) {
            $users->whereDate('users.created_at', '<=', date('Y-m-d', strtotime($request->end_date)));
        }

        $users = $users->get();

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Users');

        // Setting headers for the Excel sheet
        $sheet->setCellValue('A1', 'Sl No');
        $sheet->setCellValue('B1', 'User ID');
        $sheet->setCellValue('C1', 'Name');
        $sheet->setCellValue('D1', 'Mobile Number');
        $sheet->setCellValue('E1', 'Nationality');
        $sheet->setCellValue('F1', 'Sect');
        $sheet->setCellValue('G1', 'Subscription Plan');
        $sheet->setCellValue('H1', 'Status');
        $sheet->setCellValue('I1', 'Created Date');
        if($request->gender == GENDER_FEMALE){
            $sheet->setCellValue('J1', 'Parental Approval');
        }

        // Add user data to the sheet
        $row = 2; 
        foreach ($users as $index => $user) {
            $sheet->setCellValue('A' . $row, $index + 1); 
            $sheet->setCellValue('B' . $row, $user->user_number); 
            $sheet->setCellValue('C' . $row, $user->name); 
            $sheet->setCellValue('D' . $row, $user->dial_code . ' ' . $user->phone); 
            $sheet->setCellValue('E' . $row, $user->nationality->name ?? 'N/A'); 
            $sheet->setCellValue('F' . $row, $user->details->sect->name ?? 'N/A'); 
            $sheet->setCellValue('G' . $row, $user->plan->name ?? 'N/A'); 
            $sheet->setCellValue('H' . $row, $user->status == 1 ? 'Active' : 'Inactive'); 
            $sheet->setCellValue('I' . $row, $user->created_at->format('d M, Y - h:i A')); 
            if($request->gender == GENDER_FEMALE){
                $sheet->setCellValue('J' . $row, $user->parent_approval == 0 ? 'Not Approved' : 'Approved'); 
            }
            $row++;
        }

        // Apply styling to header row
        $sheet->getStyle('A1:J1')->getFont()->setBold(true);
        $sheet->getStyle('A1:J1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('A1:J1')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $sheet->getStyle('A1:J1')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
        $sheet->getStyle('A1:J1')->getFill()->getStartColor()->setARGB('FFCCFFCC');

        // Generate the Excel file for download
        $writer = new Xlsx($spreadsheet);
        $fileName = "User_Registration_Export_" . date('Y_m_d_H_i_s') . ".xlsx";

        header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        header("Content-Disposition: attachment;filename=\"$fileName\"");
        header('Cache-Control: max-age=0');

        $writer->save('php://output');
        exit();
    }

    public function getCustomeRegistrationPdf(Request $request)
    {
        $users = User::select('users.*')->distinct()->with('nationality', 'details', 'plan')
        ->leftJoin('nationalities', 'users.nationality_id', '=', 'nationalities.id')
        ->leftJoin('user_details', 'users.id', '=', 'user_details.user_id')
        ->leftJoin('sects', 'user_details.sect_id', '=', 'sects.id')
        ->leftJoin('user_plans', 'users.id', '=', 'user_plans.user_id')->where('role_id', 2)->where('deleted', 0)->orderBy('users.id', 'desc');
        
        if ($request->has('search') && !empty($request->search)) {
            $users->where(function ($query) use ($request) {
                $query->where('users.name', 'ilike', "%{$request->search['value']}%")
                ->orWhere('users.nick_name', 'ilike', "%{$request->search['value']}%")
                ->orWhere('users.dial_code', 'ilike', "%{$request->search['value']}%")
                ->orWhere('users.phone', 'ilike', "%{$request->search['value']}%")
                ->orWhere('users.email', 'ilike', "%{$request->search['value']}%")
                ->orWhere('users.user_number', 'ilike', "%{$request->search['value']}%");
            });
        }

        if ($request->has('gender') && $request->gender != 'all') {
            $users->where('users.gender', $request->gender);
        }

        if ($request->has('parent_approval') && $request->parent_approval != 'all') {
            $users->where('users.parent_approval', $request->parent_approval);
        }

        if ($request->has('nationality_id') && $request->nationality_id != 'all') {
            $users->where('users.country_id', $request->nationality_id);
        }

        if($request->has('emirates_id') && $request->emirates_id != 'all'){
            $users->where('users.emirates_id', $request->emirates_id);
        }

        if ($request->has('sect_id') && $request->sect_id != 'all') {
            $users->where('user_details.sect_id', $request->sect_id);
        }

        if ($request->has('plan_id') && $request->plan_id != 'all') {
            $users->where('user_plans.plan_id', $request->plan_id)->where('user_plans.status', 1);
        }

        if ($request->has('status') && $request->status != 'all') {
            $users->where('users.status', $request->status);
        }

        if ($request->has('start_date') && !empty($request->start_date)) {
            $users->whereDate('users.created_at', '>=', date('Y-m-d', strtotime($request->start_date)));
        }

        if ($request->has('end_date') && !empty($request->end_date)) {
            $users->whereDate('users.created_at', '<=', date('Y-m-d', strtotime($request->end_date)));
        }

        $users = $users->get();

        $gender = $request->gender;

        $pdf = Pdf::loadView('admin.report.user_pdf', compact('users','gender'));
        return $pdf->download("Customers_Export_" . date('Y_m_d_H_i_s') . ".pdf");
        
    }

    public function matchMaking(Request $request)
    {
        $matches = [];
        $today = date("Y-m-d");
        $nationalities = Nationality::where('status', 1)->get();
        $emirates = Emirate::where('status', 1)->get();
        $sects = Sect::where('status', 1)->get();
        $plans = PricingPlan::where('status', 1)->get();

        return view('admin.report.matchmaking', compact('matches','today', 'nationalities', 'sects', 'plans', 'emirates'));
    }

    public function getMatchMakingList(Request $request)
    {
        $sqlBuilder = UserMatch::select([
            'user_matches.id',
            'user_matches.user_id',
            'user_matches.matched_user_id',
            'user_matches.match_percentage',
            'user_matches.created_at',
            'user_matches.updated_at',
            'users.name as user_name',
            'match_users.name as match_user_name',
            'users.gender',
        ])
        ->leftJoin('users', 'user_matches.user_id', '=', 'users.id')
        ->leftJoin('users as match_users', 'user_matches.matched_user_id', '=', 'match_users.id')
        ->leftJoin('user_plans', 'users.id', '=', 'user_plans.user_id')
        ->leftJoin('user_details', 'users.id', '=', 'user_details.user_id')
        ->orderBy('user_matches.updated_at', 'desc');

        return DataTables::of($sqlBuilder)
            ->filter(function ($query) use ($request) {
                if ($request->has('search') && !empty($request->search['value'])) {
                    $query->where(function ($query) use ($request) {
                        $query->where('users.name', 'ilike', "%{$request->search['value']}%")
                        ->orWhere('match_users.name', 'ilike', "%{$request->search['value']}%");
                    });
                }

                if ($request->has('user_id') && !empty($request->user_id)) {
                    $query->whereIn('user_matches.user_id', $request->user_id);
                }

                if ($request->has('gender') && $request->gender != 'all') {
                    $query->where('users.gender', $request->gender);
                }

                if ($request->has('nationality_id') && $request->nationality_id != 'all') {
                    $query->where('users.nationality_id', $request->nationality_id);
                }

                if($request->has('emirates_id') && $request->emirates_id != 'all'){
                    $query->where('users.emirates_id', $request->emirates_id);
                }

                if ($request->has('sect_id') && $request->sect_id != 'all') {
                    $query->where('user_details.sect_id', $request->sect_id);
                }

                if ($request->has('plan_id') && $request->plan_id != 'all') {
                    $query->where('user_plans.plan_id', $request->plan_id)->where('user_plans.status', 1);
                }

                if ($request->has('parent_approval') && $request->parent_approval != 'all') {
                    $query->where('users.parent_approval', $request->parent_approval);
                }

                if ($request->has('match_percentage') && $request->match_percentage != '') {
                    $query->where('user_matches.match_percentage', $request->match_percentage);
                }

                if ($request->has('start_date') && !empty($request->start_date)) {
                    $query->whereDate('user_matches.created_at', '>=', date('Y-m-d', strtotime($request->start_date)));
                }

                if ($request->has('end_date') && !empty($request->end_date)) {
                    $query->whereDate('user_matches.created_at', '<=', date('Y-m-d', strtotime($request->end_date)));
                }
            })
            ->editColumn('created_at', function ($data) {
                return $data->updated_at->format('d/m/y');
            })
            ->editColumn('match_percentage', function ($data) {
                return $data->match_percentage . '%';
            })
            ->addColumn('action', function ($data) {
                return '<div class="dropdown">
                            <a href="#" class="btn btn-soft-secondary btn-sm" role="button" id="dropdownMenuLink5" data-bs-toggle="dropdown" aria-expanded="false">
                                <i class="ri-more-2-fill"></i>
                            </a>
                            <ul class="dropdown-menu" aria-labelledby="dropdownMenuLink5">
                                <li><a class="dropdown-item" href="'.route('admin.reports.matchmakingreport.details',['id' => $data->id]).'">Details</a></li>
                            </ul>
                        </div>';
            })
            ->rawColumns(['match_percentage','action'])
            ->make(true);
    }

    public function getMatchMakingReportExcel(Request $request)
    {
        $matches = UserMatch::select([
            'user_matches.id',
            'user_matches.user_id',
            'user_matches.matched_user_id',
            'user_matches.match_percentage',
            'user_matches.created_at',
            'users.name as user_name',
            'match_users.name as match_user_name',
        ])
        ->leftJoin('users', 'user_matches.user_id', '=', 'users.id')
        ->leftJoin('users as match_users', 'user_matches.matched_user_id', '=', 'match_users.id')
        ->leftJoin('user_plans', 'users.id', '=', 'user_plans.user_id')
        ->leftJoin('user_details', 'users.id', '=', 'user_details.user_id');

        if ($request->has('search') && !empty($request->search['value'])) {
            $matches->where(function ($query) use ($request) {
                $query->where('users.name', 'ilike', "%{$request->search['value']}%")
                ->orWhere('match_users.name', 'ilike', "%{$request->search['value']}%");
            });
        }

        if ($request->has('user_id') && !empty($request->user_id)) {
            $matches->whereIn('user_matches.user_id', $request->user_id);
        }

        if ($request->has('gender') && $request->gender != 'all') {
            $matches->where('users.gender', $request->gender);
        }

        if ($request->has('nationality_id') && $request->nationality_id != 'all') {
            $matches->where('users.nationality_id', $request->nationality_id);
        }

        if($request->has('emirates_id') && $request->emirates_id != 'all'){
            $matches->where('users.emirates_id', $request->emirates_id);
        }

        if ($request->has('sect_id') && $request->sect_id != 'all') {
            $matches->where('user_details.sect_id', $request->sect_id);
        }

        if ($request->has('plan_id') && $request->plan_id != 'all') {
            $matches->where('user_plans.plan_id', $request->plan_id)->where('user_plans.status', 1);
        }

        if ($request->has('parent_approval') && $request->parent_approval != 'all') {
            $matches->where('users.parent_approval', $request->parent_approval);
        }

        if ($request->has('match_percentage') && $request->match_percentage != '') {
            $matches->where('user_matches.match_percentage', $request->match_percentage);
        }

        if ($request->has('start_date') && !empty($request->start_date)) {
            $matches->whereDate('user_matches.created_at', '>=', date('Y-m-d', strtotime($request->start_date)));
        }

        if ($request->has('end_date') && !empty($request->end_date)) {
            $matches->whereDate('user_matches.created_at', '<=', date('Y-m-d', strtotime($request->end_date)));
        }

        $matches = $matches->get();

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Matchmaking');

        // Setting headers for the Excel sheet
        $sheet->setCellValue('A1', 'Sl No');
        $sheet->setCellValue('B1', 'User Name');
        $sheet->setCellValue('C1', 'Matched User Name');
        $sheet->setCellValue('D1', 'Match Percentage');
        $sheet->setCellValue('E1', 'Created Date');

        // Add user data to the sheet
        $row = 2; 
        foreach ($matches as $index => $match) {
            $sheet->setCellValue('A' . $row, $index + 1); 
            $sheet->setCellValue('B' . $row, $match->user_name); 
            $sheet->setCellValue('C' . $row, $match->match_user_name); 
            $sheet->setCellValue('D' . $row, $match->match_percentage . '%'); 
            $sheet->setCellValue('E' . $row, Carbon::parse($match->created_at)->format('d M, Y - h:i A')); 
            $row++;
        }

        // Apply styling to header row
        $sheet->getStyle('A1:E1')->getFont()->setBold(true);
        $sheet->getStyle('A1:E1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('A1:E1')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $sheet->getStyle('A1:E1')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
        $sheet->getStyle('A1:E1')->getFill()->getStartColor()->setARGB('FFCCFFCC');

        // Generate the Excel file for download
        $writer = new Xlsx($spreadsheet);
        $fileName = "Matchmaking_Performance_Export_" . date('Y_m_d_H_i_s') . ".xlsx";

        header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        header("Content-Disposition: attachment;filename=\"$fileName\"");
        header('Cache-Control: max-age=0');

        $writer->save('php://output');
        exit();
    }

    public function getMatchMakingReportPdf(Request $request)
    {
        $matches = UserMatch::select([
            'user_matches.id',
            'user_matches.user_id',
            'user_matches.matched_user_id',
            'user_matches.match_percentage',
            'user_matches.created_at',
            'users.name as user_name',
            'match_users.name as match_user_name',
            'users.*'
        ])
        ->leftJoin('users', 'user_matches.user_id', '=', 'users.id')
        ->leftJoin('users as match_users', 'user_matches.matched_user_id', '=', 'match_users.id')
        ->leftJoin('user_plans', 'users.id', '=', 'user_plans.user_id')
        ->leftJoin('user_details', 'users.id', '=', 'user_details.user_id');

        if ($request->has('search') && !empty($request->search['value'])) {
            $matches->where(function ($query) use ($request) {
                $query->where('users.name', 'ilike', "%{$request->search['value']}%")
                ->orWhere('match_users.name', 'ilike', "%{$request->search['value']}%");
            });
        }

        if ($request->has('user_id') && !empty($request->user_id)) {
            $matches->whereIn('user_matches.user_id', $request->user_id);
        }

        if ($request->has('gender') && $request->gender != 'all') {
            $matches->where('users.gender', $request->gender);
        }

        if ($request->has('nationality_id') && $request->nationality_id != 'all') {
            $matches->where('users.nationality_id', $request->nationality_id);
        }

        if($request->has('emirates_id') && $request->emirates_id != 'all'){
            $matches->where('users.emirates_id', $request->emirates_id);
        }

        if ($request->has('sect_id') && $request->sect_id != 'all') {
            $matches->where('user_details.sect_id', $request->sect_id);
        }

        if ($request->has('plan_id') && $request->plan_id != 'all') {
            $matches->where('user_plans.plan_id', $request->plan_id)->where('user_plans.status', 1);
        }

        if ($request->has('parent_approval') && $request->parent_approval != 'all') {
            $matches->where('users.parent_approval', $request->parent_approval);
        }

        if ($request->has('match_percentage') && $request->match_percentage != '') {
            $matches->where('user_matches.match_percentage', $request->match_percentage);
        }

        if ($request->has('start_date') && !empty($request->start_date)) {
            $matches->whereDate('user_matches.created_at', '>=', date('Y-m-d', strtotime($request->start_date)));
        }

        if ($request->has('end_date') && !empty($request->end_date)) {
            $matches->whereDate('user_matches.created_at', '<=', date('Y-m-d', strtotime($request->end_date)));
        }

        $matches = $matches->get();

        $pdf = Pdf::loadView('admin.report.matchmaking_pdf', compact('matches'));
        return $pdf->download("Matchmaking_Performance_Export_" . date('Y_m_d_H_i_s') . ".pdf");
    }

    public function getMatchmakingDetails($id)
    {
        $match = UserMatch::findOrFail($id);
        $user = User::with(['details','plan','preference.sect', 'preference.education', 'preference.profession', 'dressCodes.dressCode', 'maritalStatuses.maritalStatus', 'sects.sect', 'professions.profession', 'educations.education', 'preference.city_emirate','children'])->findOrFail($match->user_id);
        $user1 = User::with(['details','plan','preference.sect', 'preference.education', 'preference.profession', 'dressCodes.dressCode', 'maritalStatuses.maritalStatus', 'sects.sect', 'professions.profession', 'educations.education', 'preference.city_emirate','children'])->findOrFail($match->matched_user_id);
        $pageTitle = 'Matchmaking Details';
        $back = route('admin.reports.matchmakingreport');
        //echo '<pre>'; print_r($user1->toArray()); exit;

        return view('admin.report.matchdetails', compact('user','user1','match','back','pageTitle'));
    }

    public function paymentReport(Request $request)
    {
        $paymentReports = [];
        $today = date("Y-m-d");
        return view('admin.report.payments', compact('paymentReports','today'));
    }

    public function getPaymentReportList(Request $request)
    {
        $sqlBuilder = PaymentReport::select([
            'payment_reports.transaction_id',
            'users.name',
            'users.gender',
            'users.user_number',
            'users.dial_code',
            'users.phone',
            'payment_reports.amount',
            'payment_reports.method_type',
            'payment_reports.payment_status',
            'user_plans.created_at',
            'payment_reports.currency',
            'user_plans.type as plan_type',
            'user_plans.name as user_plan_name',
        ])
        ->with(['user'])
        ->leftJoin('users', 'payment_reports.user_id', '=', 'users.id')
        ->leftJoin('user_plans', 'users.id', '=', 'user_plans.user_id')
        ->orderBy('payment_reports.id', 'desc');

        return DataTables::of($sqlBuilder)
            ->filter(function ($query) use ($request) {
            if ($request->has('search') && !empty($request->search['value'])) {
                $query->where(function ($q) use ($request) {
                $q->where('users.user_number', 'ilike', "%{$request->search['value']}%")
                ->orWhere('payment_reports.transaction_id', 'ilike', "%{$request->search['value']}%")
                ->orWhere('payment_reports.payment_status', 'ilike', "%{$request->search['value']}%")
                ->orWhere('users.name', 'ilike', "%{$request->search['value']}%");
                });
            }

            if ($request->has('user_id') && !empty($request->user_id)) {
                $query->whereIn('payment_reports.user_id', $request->user_id);
            }

            // if ($request->has('status') && $request->status != 'all') {
            //     $query->where('payment_reports.payment_status', $request->status);
            // }
            if ($request->has('status') && $request->status != 'all') {

                if ($request->status === 'approved') {
                    $query->where(function ($q) {
                        $q->where('payment_reports.payment_status', 'approved')
                          ->orWhere(function ($sub) {
                              $sub->where(function ($p) {
                                  $p->where('user_plans.type', 'free')
                                    ->orWhere('user_plans.type', 0);
                              });
                          });
                    });
                } else {
                    
                    $query->where('payment_reports.payment_status', $request->status)
                          ->where(function ($q) {
                              $q->where('user_plans.type', '!=', 'free')
                                ->where('user_plans.type', '!=', 0);
                          });
                }
            }


            if ($request->has('start_date') && !empty($request->start_date)) {
                $query->whereDate('user_plans.created_at', '>=', date('Y-m-d', strtotime($request->start_date)));
            }

            if ($request->has('end_date') && !empty($request->end_date)) {
                $query->whereDate('user_plans.created_at', '<=', date('Y-m-d', strtotime($request->end_date)));
            }
            })
            ->addColumn('customer_name', function ($data) {
            return $data->name??'';
            })
            ->addColumn('ad_amount', function ($data) {
                return $data->amount . ' ' . $data->currency;
                })
            ->addColumn('method_type', function ($data) {
                switch ($data->method_type) {
                    case 1:
                        return 'Credit Card';
                        break;
                    
                    default:
                        return 'N/A';
                        break;
                };
            })
            ->editColumn('created_at', function ($data) {
                return $data->created_at?$data->created_at->format('d/m/y H:i A'):'N/A';
                })
            ->editColumn('user_details', function ($data) {
                return $data->user_number.'<br>'.$data->name.'<br>'.$data->dial_code.' '.$data->phone;
            })
            ->editColumn('payment_status', function ($data) {

                // If FREE plan → always approved
                if (
                    strtolower($data->plan_type) === 'free' || 
                    $data->plan_type == 0
                ) {
                    return 'Approved';
                }
            
                return $data->payment_status;
            })

            ->rawColumns(['status','user_details'])
            ->make(true);


    }

    public function getPaymentReportExcel(Request $request)
    {
        $payments = PaymentReport::select([
            'payment_reports.transaction_id',
            'users.name',
            'users.gender',
            'users.user_number',
            'users.dial_code',
            'users.phone',
            'payment_reports.amount',
            'payment_reports.method_type',
            'payment_reports.payment_status',
            'user_plans.created_at',
            'payment_reports.currency',
            'user_plans.type as plan_type',
            'user_plans.name as user_plan_name',
        ])
        ->with(['user'])
        ->leftJoin('users', 'payment_reports.user_id', '=', 'users.id')
        ->leftJoin('user_plans', 'users.id', '=', 'user_plans.user_id')
        ->orderBy('payment_reports.id', 'desc');

        if ($request->has('search') && !empty($request->search['value'])) {
            $payments->where(function ($q) use ($request) {
            $q->where('users.user_number', 'ilike', "%{$request->search['value']}%")
            ->orWhere('payment_reports.transaction_id', 'ilike', "%{$request->search['value']}%")
            ->orWhere('payment_reports.payment_status', 'ilike', "%{$request->search['value']}%")
            ->orWhere('users.name', 'ilike', "%{$request->search['value']}%");
            });
        }

        if ($request->has('user_id') && !empty($request->user_id)) {
            $payments->whereIn('payment_reports.user_id', $request->user_id);
        }

        // if ($request->has('status') && $request->status != 'all') {
        //     $payments->where('payment_reports.payment_status', $request->status);
        // }
        if ($request->has('status') && $request->status != 'all') {

            if ($request->status === 'approved') {
                $payments->where(function ($q) {
                    $q->where('payment_reports.payment_status', 'approved')
                      ->orWhere(function ($sub) {
                          $sub->where(function ($p) {
                              $p->where('user_plans.type', 'free')
                                ->orWhere('user_plans.type', 0);
                          });
                      });
                });
            } else {
                
                $payments->where('payment_reports.payment_status', $request->status)
                      ->where(function ($q) {
                          $q->where('user_plans.type', '!=', 'free')
                            ->where('user_plans.type', '!=', 0);
                      });
            }
        }

        // if ($request->has('start_date') && !empty($request->start_date)) {
        //     $payments->whereDate('payment_reports.created_at', '>=', date('Y-m-d', strtotime($request->start_date)));
        // }

        // if ($request->has('end_date') && !empty($request->end_date)) {
        //     $payments->whereDate('payment_reports.created_at', '<=', date('Y-m-d', strtotime($request->end_date)));
        // }
        if ($request->has('start_date') && !empty($request->start_date)) {
            $payments->whereDate('user_plans.created_at', '>=', date('Y-m-d', strtotime($request->start_date)));
        }

        if ($request->has('end_date') && !empty($request->end_date)) {
            $payments->whereDate('user_plans.created_at', '<=', date('Y-m-d', strtotime($request->end_date)));
        }

        $payments = $payments->get();

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Payment Reports');

        // Setting headers for the Excel sheet
        $sheet->setCellValue('A1', 'Sl No');
        $sheet->setCellValue('B1', 'Date Subscribed');
        $sheet->setCellValue('C1', 'User Details');
        $sheet->setCellValue('D1', 'Plan Name');
        $sheet->setCellValue('E1', 'Plan Duration');
        $sheet->setCellValue('F1', 'Amount');
        $sheet->setCellValue('G1', 'Payment Method');
        $sheet->setCellValue('H1', 'Status');

        // Add user data to the sheet
        $row = 2;
        foreach ($payments as $index => $payments) {
            if (strtolower($payments->plan_type) === 'free' || $payments->plan_type == 0 ) {
                $status = 'approved';
            }
            $sheet->setCellValue('A' . $row, $index + 1);
            $sheet->setCellValue('B' . $row, $payments->created_at ? $payments->created_at->format('d M, Y - h:i A') : 'N/A');
            $sheet->setCellValue('C' . $row, $payments->user_number . "\n" . $payments->name . "\n" . $payments->dial_code . ' ' . $payments->phone);
            $sheet->setCellValue('D' . $row, $payments->user_plan_name ?? 'N/A');
            $sheet->setCellValue('E' . $row, $payments->plan_type ?? 'N/A');
            $sheet->setCellValue('F' . $row, $payments->amount . ' ' . $payments->currency);
            $sheet->setCellValue('G' . $row, $payments->method_type == 1 ? 'Credit Card' : 'N/A');
            $sheet->setCellValue('H' . $row, ucfirst($status));
            $row++;
        }

        // Apply styling to header row
        $sheet->getStyle('A1:H1')->getFont()->setBold(true);
        $sheet->getStyle('A1:H1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('A1:H1')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $sheet->getStyle('A1:H1')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
        $sheet->getStyle('A1:H1')->getFill()->getStartColor()->setARGB('FFCCFFCC');


        // Auto adjust column width based on content
        foreach (range('A', 'H') as $columnID) {
            $sheet->getColumnDimension($columnID)->setAutoSize(true);
        }

        // Generate the Excel file for download
        $writer = new Xlsx($spreadsheet);
        $fileName = "Payment_Reports_Export_" . date('Y_m_d_H_i_s') . ".xlsx";

        header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        header("Content-Disposition: attachment;filename=\"$fileName\"");
        header('Cache-Control: max-age=0');

        $writer->save('php://output');
        exit();
    }

    public function getPaymentReportPdf(Request $request)
    {
        $payments = PaymentReport::select([
            'payment_reports.transaction_id',
            'users.name',
            'users.gender',
            'users.user_number',
            'users.dial_code',
            'users.phone',
            'payment_reports.amount',
            'payment_reports.method_type',
            'payment_reports.payment_status',
            'payment_reports.currency',
            'user_plans.type as plan_type',
            'user_plans.created_at',
            'user_plans.name as user_plan_name',
        ])
        ->with(['user'])
        ->leftJoin('users', 'payment_reports.user_id', '=', 'users.id')
        ->leftJoin('user_plans', 'users.id', '=', 'user_plans.user_id')
        ->orderBy('payment_reports.id', 'desc');

        if ($request->has('search') && !empty($request->search['value'])) {
            $payments->where(function ($q) use ($request) {
            $q->where('users.user_number', 'ilike', "%{$request->search['value']}%")
            ->orWhere('payment_reports.transaction_id', 'ilike', "%{$request->search['value']}%")
            ->orWhere('payment_reports.payment_status', 'ilike', "%{$request->search['value']}%")
            ->orWhere('users.name', 'ilike', "%{$request->search['value']}%");
            });
        }

        if ($request->has('user_id') && !empty($request->user_id)) {
            $payments->whereIn('payment_reports.user_id', $request->user_id);
        }

        if ($request->has('status') && $request->status != 'all') {
            $payments->where('payment_reports.payment_status', $request->status);
        }

        if ($request->has('start_date') && !empty($request->start_date)) {
            $payments->whereDate('payment_reports.created_at', '>=', date('Y-m-d', strtotime($request->start_date)));
        }

        if ($request->has('end_date') && !empty($request->end_date)) {
            $payments->whereDate('payment_reports.created_at', '<=', date('Y-m-d', strtotime($request->end_date)));
        }

        $paymentReports = $payments->get();
        $pdf = Pdf::loadView('admin.report.payments_pdf', compact('paymentReports'));
        return $pdf->download("Payment_Reports_Export_" . date('Y_m_d_H_i_s') . ".pdf");
    }

    public function proposalReport(Request $request)
    {
        $proposals = [];
        $today = date("Y-m-d");
        return view('admin.report.proposals', compact('proposals','today'));
    }

    public function getProposalReportList(Request $request)
    {
        $sqlBuilder = UserRequest::select([
            'user_requests.*',
            'users.name as receiver_name',
            'users.nick_name',
            'users.dial_code',
            'users.phone',
            'users.email',
            'users.gender',
            'users.created_at',
            'users.profile_image',
            'users.user_number',
            'nationalities.name as nationality_name',
            'sects.name as sect_name',
            'marital_statuses.name as marital_status_name',
            'sender_users.name as sender_name',
            'sender_users.nick_name as sender_nick_name',
            'sender_users.user_number as sender_user_number',
            'sender_users.dial_code as sender_dial_code',
            'sender_users.phone as sender_phone',
            'user_requests.status as request_status',
        ])->with('receiver')
        ->leftJoin('users', 'user_requests.receiver_id', '=', 'users.id')
        ->leftJoin('users as sender_users', 'user_requests.sender_id', '=', 'sender_users.id')
        ->leftJoin('nationalities', 'users.nationality_id', '=', 'nationalities.id')
        ->leftJoin('user_details', 'users.id', '=', 'user_details.user_id')
        ->leftJoin('sects', 'user_details.sect_id', '=', 'sects.id')
        ->leftJoin('marital_statuses', 'user_details.marital_status_id', '=', 'marital_statuses.id');

        $sqlBuilder->orderBy('user_requests.id', 'desc');

        return DataTables::of($sqlBuilder)
            ->filter(function ($query) use ($request) {
                if ($request->has('search') && !empty($request->search['value'])) {
                    $query->where(function ($query) use ($request) {
                        $query->where('users.name', 'ilike', "%{$request->search['value']}%")
                        ->orWhere('users.nick_name', 'ilike', "%{$request->search['value']}%")
                        ->orWhere('users.dial_code', 'ilike', "%{$request->search['value']}%")
                        ->orWhere('users.phone', 'ilike', "%{$request->search['value']}%")
                        ->orWhere('users.email', 'ilike', "%{$request->search['value']}%")
                        ->orWhere('users.user_number', 'ilike', "%{$request->search['value']}%")
                        ->orWhere('nationalities.name', 'ilike', "%{$request->search['value']}%")
                        ->orWhere('sects.name', 'ilike', "%{$request->search['value']}%");
                    });
                }
                

            })
            ->editColumn('user_image', function ($data) {
                $profileImage = !empty($data->receiver->profile_image_url) ? $data->receiver->profile_image_url : asset('assets/images/camera_placeholder.png');
                return '<div style="text-align:center;"><img src="' . $profileImage . '" class="rounded-circle avatar-xs" alt="profile-image"></div>';
                
            })

            ->editColumn('phone', function ($data) {
                return $data->dial_code . ' ' . $data->phone;
            })
            ->editColumn('match_percentage', function ($data) {
                return '0%';
            })
            ->addColumn('receiver_details', function ($data) {
                return $data->user_number . '<br>' . $data->receiver_name . '<br>' . $data->dial_code . ' ' . $data->phone;
            })
            ->addColumn('sender_details', function ($data) {
                return $data->sender_user_number . '<br>' . $data->sender_name . '<br>' . $data->sender_dial_code . ' ' . $data->sender_phone;
            })
            ->editColumn('status', function ($data) {
                switch ($data->status) {
                    case 0:
                        return '<span class="badge bg-warning">Pending</span>';
                    case 1:
                        return '<span class="badge bg-success">Accepted</span>';
                    case 2:
                        return '<span class="badge bg-danger">Rejected</span>';
                    default:
                        return '<span class="badge bg-secondary">Unknown</span>';
                }
            })
            ->editColumn('created_at', function ($data) {
            return $data->created_at->format('d/m/y H:i A');
            })
            ->rawColumns(['sender_details','receiver_details','status','action'])
            ->make(true);
    }



    

}