<?php

namespace App\Http\Controllers;


use App\Models\Beneficiary;
use App\Models\Claim;
use App\Models\Cover;
use App\Models\Dependant;
use App\Models\KycDocument;
use App\Models\Member;
use App\Models\MemberHistory;
use App\Models\Months_covered;
use App\Models\Oldmembers_status;
use App\Models\premium;
use App\Models\Premium_payment;
use App\Models\Sponsor;
use App\Models\User;
use App\Models\UserLog;
use App\Notifications\CompletedTask;
use Carbon\Carbon;
use DB;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Hash;

class MemberprofileController extends Controller
{
    public function index($id)
    {
        $member = Member::find($id);
        $notificationCount = UserLog::where('action', 'LIKE', '%' . $member->national_id . '%')->count();
        $kycCount = KycDocument::where('member_id', $id)->count();
        $claimsCount = Claim::where('policy_holder_id', $id)->count();


        $dependants = Dependant::where('member_id', $id)->count();

        $overAgeDependants = Dependant::where('member_id', $id)
            ->whereNotNull('d_o_b')
            ->get()
            ->filter(function ($d) {
                return Carbon::parse($d->d_o_b)->age > 21;
            });

        $overAgeCount = $overAgeDependants->count();


        $beneficiaries = Beneficiary::where('member_id', $id)->count();

        //Start
        $premium = premium::where('member_id', $id)->first();
        if (!$premium) {
            $premium = premium::where('member_id', 1)->first();
        }
        //Initialization
        $registration_date = Carbon::parse($member->registration_date);
        $today = Carbon::now();
        $length = $registration_date->diffInMonths($today);
        $date_birth = Carbon::parse($member->date_of_birth);
        $age = $date_birth->diffInYears($today);
        $last_covers = DB::select('(SELECT months FROM months_covereds WHERE payment_id IN (SELECT id FROM premium_payments WHERE member_id =' . $id . ') order by id DESC limit 1) ');
        foreach ($last_covers as $last_coverr) {
            $last_cover = $last_coverr->months;
        }
        if (!$last_covers) {
            $last_cover = $registration_date->addMonth($premium->waiting_period)->toString();
        }
        $last_date = Carbon::parse($last_cover);

        $unpaidmonths = $last_date->diffInMonths($today);

        //Pending due to payment
        $oldmember_status = Oldmembers_status::where('member_id', $id)->latest()->first();
        //Checking the waiting period
        if ($premium->cover->waiting_period <= $length) {
            if ($last_date < $today) {
                if ($unpaidmonths < 3) {
                    ///// Peding Period late payment
                    if (!$oldmember_status) {
                        $color = "text-success";
                        $status = "Active";
                    } else {
                        $created = Carbon::parse($oldmember_status->created_at);
                        $nummonths = $created->diffInMonths($today);
                        if ($nummonths < 3) {
                            $color = "text-warning";
                            $status = "Pending";
                        } else {
                            $color = "text-success";
                            $status = "Active";
                        }
                    }
                } elseif (($unpaidmonths >= 3) && ($unpaidmonths < 6)) {
                    $color = "text-danger";
                    $status = "Lapsed";
                } elseif ($unpaidmonths >= 6) {
                    $color = "text-black";
                    $status = "Restart";
                }
            } else {
                ///// Peding Period late payment
                if (!$oldmember_status) {
                    $color = "text-success";
                    $status = "Active";
                } else {
                    $created = Carbon::parse($oldmember_status->created_at);
                    $nummonths = $created->diffInMonths($today);
                    if ($nummonths < 3) {
                        $color = "text-warning";
                        $status = "Pending";
                    } else {
                        $color = "text-success";
                        $status = "Active";
                    }
                }
            }
        } else {
            $color = "text-warning";
            $status = "Pending";
        }
        ///// End of profile Check 

        //Lists
        $covers = Cover::all();
        $sponsors = Sponsor::all();
        $payment = Premium_payment::where('member_id', $id)->count();
        $arrears = DB::select('(SELECT DATE_FORMAT(months, "%Y-%M") as months FROM arrears_collections WHERE payment_id IN (SELECT id FROM premium_payments WHERE member_id =' . $id . ')) ');
        $coverss = DB::select('(SELECT DATE_FORMAT(months, "%Y-%M") as months FROM months_covereds WHERE payment_id IN (SELECT id FROM premium_payments WHERE member_id =' . $id . ')) ');
        return view('members.profile', compact(

            'today',
            'unpaidmonths',
            'last_date',
            'member',
            'last_cover',
            'dependants',
            'beneficiaries',
            'covers',
            'sponsors',
            'premium',
            'payment',
            'coverss',
            'length',
            'age',
            'color',
            'arrears',
            'status',
            'notificationCount',
            'kycCount',
            'claimsCount',
            'overAgeCount',
'overAgeDependants',
        ));
    }



    /*public function status()
    {

        $members = Member::all();
        // $members = Member::take(500)->get();
        foreach ($members as $member) {
            $registration_date = Carbon::parse($member->registration_date);
            $now = Carbon::now();
            $length = $registration_date->diffInMonths($now);
            $date_birth = Carbon::parse($member->date_of_birth);
            $age = $date_birth->diffInYears($now);


            //Start
            $premium = premium::where('member_id', $member->id)->first();
            if (!$premium) {
                $premium = premium::where('member_id', 1)->first();
            }
            //Initialization
            $registration_date = Carbon::parse($member->registration_date);
            $today = Carbon::now();
            $length = $registration_date->diffInMonths($today);
            $date_birth = Carbon::parse($member->date_of_birth);
            $age = $date_birth->diffInYears($today);
            $last_covers = DB::select('(SELECT months FROM months_covereds WHERE payment_id IN (SELECT id FROM premium_payments WHERE member_id =' . $member->id . ') order by id DESC limit 1) ');
            foreach ($last_covers as $last_coverr) {
                $last_cover = $last_coverr->months;
            }
            if (!$last_covers) {
                $last_cover = $registration_date->addMonth($premium->waiting_period)->toString();
            }
            $last_date = Carbon::parse($last_cover);

            $unpaidmonths = $last_date->diffInMonths($today);

            //Pending due to payment
            $oldmember_status = Oldmembers_status::where('member_id', $member->id)->latest()->first();
            //Checking the waiting period
            if ($premium->cover->waiting_period <= $length) {
                if ($last_date < $today) {
                    if ($unpaidmonths < 3) {
                        ///// Peding Period late payment
                        if (!$oldmember_status) {
                            $color = "text-success";
                            $status = "Active";
                        } else {
                            $created = Carbon::parse($oldmember_status->created_at);
                            $nummonths = $created->diffInMonths($today);
                            if ($nummonths < 3) {
                                $color = "text-warning";
                                $status = "Pending";
                            } else {
                                $color = "text-success";
                                $status = "Active";
                            }
                        }
                    } elseif (($unpaidmonths >= 3) && ($unpaidmonths < 6)) {
                        $color = "text-danger";
                        $status = "Elapsed";
                    } elseif ($unpaidmonths >= 6) {
                        $color = "text-black";
                        $status = "Restart";
                    }
                } else {
                    ///// Peding Period late payment
                    if (!$oldmember_status) {
                        $color = "text-success";
                        $status = "Active";
                    } else {
                        $created = Carbon::parse($oldmember_status->created_at);
                        $nummonths = $created->diffInMonths($today);
                        if ($nummonths < 3) {
                            $color = "text-warning";
                            $status = "Pending";
                        } else {
                            $color = "text-success";
                            $status = "Active";
                        }
                    }
                }
            } else {
                $color = "text-warning";
                $status = "Pending";
            }
            ///// End of profile Check 
            $memberupdate = Member::find($member->id);
            $memberupdate->status1 = $status;
            $memberupdate->save();
        }
        return redirect()->back()->with('success', 'Done successfully!');
    }*/


    public function status()
    {
        $members = Member::all();
        $today = Carbon::now();

        foreach ($members as $member) {

            // =========================
            // Dates
            // =========================
            $registrationDate = Carbon::parse($member->registration_date);
            $monthsSinceRegistration = $registrationDate->diffInMonths($today);

            // =========================
            // Premium (fallback to default)
            // =========================
            $premium = Premium::where('member_id', $member->id)->first()
                ?? Premium::where('member_id', 1)->first();

            // =========================
            // Last covered date
            // =========================
            $lastCoverRow = DB::selectOne("
            SELECT months FROM months_covereds
            WHERE payment_id IN (
                SELECT id FROM premium_payments WHERE member_id = ?
            )
            ORDER BY id DESC
            LIMIT 1
        ", [$member->id]);

            if ($lastCoverRow) {
                $lastCoveredDate = Carbon::parse($lastCoverRow->months);
            } else {
                $lastCoveredDate = $registrationDate
                    ->copy()
                    ->addMonths($premium->cover->waiting_period);
            }

            $unpaidMonths = $lastCoveredDate->diffInMonths($today);
            $waitingCompleted = $monthsSinceRegistration >= $premium->cover->waiting_period;

            // =========================
            // Old status
            // =========================
            $oldStatus = Oldmembers_status::where('member_id', $member->id)
                ->latest()
                ->first();

            // =========================
            // Status & color decision
            // =========================
            if (!$waitingCompleted) {

                $status = 'Pending';
                $color  = 'text-warning';
            } elseif ($unpaidMonths < 3) {

                if (!$oldStatus) {
                    $status = 'Active';
                    $color  = 'text-success';
                } else {
                    $monthsInOldStatus = Carbon::parse($oldStatus->created_at)
                        ->diffInMonths($today);

                    if ($monthsInOldStatus < 3) {
                        $status = 'Pending';
                        $color  = 'text-warning';
                    } else {
                        $status = 'Active';
                        $color  = 'text-success';
                    }
                }
            } elseif ($unpaidMonths < 6) {

                $status = 'Elapsed';
                $color  = 'text-danger';
            } else {

                $status = 'Restart';
                $color  = 'text-dark';
            }

            // =========================
            // Save
            // =========================
            $member->update([
                'status1' => $status,
                'status_color' => $color   // <-- make sure this column exists
            ]);
        }

        return redirect()->back()->with('success', 'Done successfully!');
    }
}
