<?php

namespace Modules\Insurance\Http\Controllers;

use Carbon\Carbon;
use Illuminate\Http\Request;
use Streamline\Models\Drug;
use Streamline\Models\InsuranceExpenditure;
use Streamline\Models\InsuranceGroup;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Validator;
use Illuminate\Support\Facades\Auth;
use Streamline\Models\InsurancePremiums;
use Streamline\Models\InsuranceSubscription;

class InsuranceReportsController extends Controller {

    public function __construct() {
        $this->middleware('auth');
        $this->middleware('permission:insurance-reports', ['only' => ['index']]);
        $this->middleware('permission:insurance-almost-expiring', ['only' => ['view_expiry_status', 'view_expiry_status_details']]);
        $this->middleware('permission:insurance-drugs-covered', ['only' => ['drugs_covered']]);
        $this->middleware('permission:expired-insurance', ['only' => ['expired_insurance', 'expired_insurance_details']]);
        $this->middleware('permission:insurance-premiums-report', ['only' => ['insurance_transactions']]);
        $this->middleware('permission:insurance-cumulative-premiums-report', ['only' => ['cumulative_premiums_report']]);
        $this->middleware('permission:insurance-income-received', ['only' => ['insurance_income_received']]);
        $this->middleware('permission:insurance-expenditure', ['only' => ['insurance_expenditure']]);
    }

    public function index(){}

    public function view_expiry_status(Request $request) {
        $insurance_group_options = InsuranceGroup::orderBy('name', 'asc')->pluck('name', 'id')->toArray();
        $insurance_group_options = ['' => '- select -'] + $insurance_group_options;
        $today = Carbon::today();
        $two_months_from_now = Carbon::now()->addMonths(2);
        if ($request->has('group_id')) {
            $insurance_groups = InsuranceGroup::orderBy('insurance_subscriptions.end_date', 'asc')
                ->join('insurance_subscriptions', 'insurance_groups.id', '=', 'insurance_subscriptions.group_id')
                ->select('insurance_groups.name', 'insurance_groups.id', 'insurance_subscriptions.start_date', 'insurance_subscriptions.end_date', 'insurance_subscriptions.payment_date')
                ->whereBetween('insurance_subscriptions.end_date', [$today, $two_months_from_now])
                ->where('insurance_subscriptions.group_id', $request->group_id)
                ->get();
        } else {
            $insurance_groups = InsuranceGroup::orderBy('insurance_subscriptions.end_date', 'asc')
                ->join('insurance_subscriptions', 'insurance_groups.id', '=', 'insurance_subscriptions.group_id')
                ->select('insurance_groups.name', 'insurance_groups.id', 'insurance_subscriptions.start_date', 'insurance_subscriptions.end_date', 'insurance_subscriptions.payment_date')
                ->whereBetween('insurance_subscriptions.end_date', [$today, $two_months_from_now])
                ->get();
        }

        $group_ids = DB::table('insurance_groups')->select('id')->distinct()->get();

        $total_insurance = DB::table('insurance_subscriptions')->sum('amount');

        return view('insurance::insurance_reports.view_expiry_status', compact('total_insurance', 'insurance_groups', 'group_ids', 'insurance_group_options'));
    }

    public function view_expiry_status_details($id) {

        $insurance_subscriptions = InsuranceSubscription::where(['group_id' => $id])->select('amount', 'start_date', 'end_date', 'payment_date')
            ->orderBy('start_date', 'asc')->paginate(50);

        $result = DB::table('insurance_groups')->select('name')->where('id', $id)->first();
        $group_name = $result->name;

        return view('insurance::insurance_reports.view_expiry_status_details', compact('group_name', 'insurance_subscriptions'));
    }

    public function expired_insurance() {
        $insurance_groups = InsuranceGroup::where('insurance_subscriptions.end_date', '<', date('Y-m-d'))->orderBy('insurance_subscriptions.end_date', 'desc')
            ->join('insurance_subscriptions', 'insurance_groups.id', '=', 'insurance_subscriptions.group_id')
            ->select('insurance_groups.*', 'insurance_subscriptions.start_date', 'insurance_subscriptions.end_date', 'insurance_subscriptions.payment_date', 'insurance_subscriptions.covered_member_ids', 'insurance_subscriptions.covered_member_premiums_paid')
            ->get(50); //this is the old query. I have left if just in case

        $insurance_premiums = DB::select("SELECT insurance_subscriptions.group_id, end_date, start_date, amount, payment_date, covered_member_ids,covered_member_premiums_paid FROM insurance_subscriptions INNER JOIN (SELECT group_id, MAX(end_date) AS max_end_date FROM insurance_subscriptions GROUP BY group_id) ms ON insurance_subscriptions.group_id = ms.group_id AND end_date = max_end_date");

        return view('insurance::insurance_reports.expired_insurance', compact('insurance_groups', 'insurance_premiums'));
    }

    public function expired_insurance_details($id) {
        $insurance_subscriptions = DB::table('insurance_subscriptions')->where(['group_id' => $id])
            ->select('amount', 'start_date', 'end_date', 'payment_date', 'covered_member_ids')->orderBy('start_date', 'asc')->paginate(50);

        $result = DB::table('insurance_groups')->select('name')->where('id', $id)->first();
        $group_name = $result->name;

        return view('insurance::insurance_reports.view_expiry_status_details', compact('group_name', 'insurance_subscriptions'));
    }

    public function insurance_transactions(Request $request) {

        if($request->start_date){
            $start = Carbon::createFromFormat('d/m/Y', $request->start_date)->toDateString();
            $end = Carbon::createFromFormat('d/m/Y', $request->end_date)->toDateString();
        } else {
            $start = Carbon::now()->startOfDay()->toDateTimeString();
            $end = Carbon::now()->endOfDay()->toDateTimeString();
        }

        $search_text = "Searching between " . streamline_date($start) . " and " . streamline_date($end);

        $start = date("Y-m-d", strtotime($start));
        $end = date("Y-m-d", strtotime($end));

        $insurance_transactions = DB::table('insurance_subscriptions')
            ->whereBetween('payment_date', [$start, $end])->get();

        $insurance_groups = DB::table('insurance_groups')->whereNull('deleted_at')->pluck('name', 'id');

        return view('insurance::insurance_reports.insurance_transactions', compact('insurance_transactions', 'start', 'end', 'search_text', 'insurance_groups'));
    }

    public function cumulative_premiums_report(Request $request) {

        if($request->start_date){
            $start_date = Carbon::createFromFormat('d/m/Y', $request->start_date)->toDateString();
            $end_date = Carbon::createFromFormat('d/m/Y', $request->end_date)->toDateString();
        } else {
            $start_date = Carbon::now()->startOfDay()->toDateTimeString();
            $end_date = Carbon::now()->endOfDay()->toDateTimeString();
        }

        $reports = DB::table('insurance_groups')
            ->whereBetween('insurance_subscriptions.payment_date', [$start_date, $end_date])
            ->orderBy('name', 'asc')
            ->join('insurance_subscriptions', 'insurance_groups.id', '=', 'insurance_subscriptions.group_id')
            ->select('insurance_groups.*', 'insurance_subscriptions.start_date', 'insurance_subscriptions.end_date', 'insurance_subscriptions.payment_date')
            ->paginate(50);

        $search_text = "Searching between " . streamline_date($start_date) . " and " . streamline_date($end_date);

        return view('insurance::insurance_reports.cumulative_premiums_report', compact('reports', 'start_date', 'end_date', 'search_text'));
    }

    public function group_premiums_report(Request $request) {
        $start_date = $request->start_date;
        $end_date = $request->end_date;
        $id = $request->id;

        $group_transactions = DB::table('insurance_subscriptions')->where(['group_id' => $id])
            ->whereBetween('payment_date', [$start_date, $end_date])->orderBy('payment_date', 'asc')->paginate(50);

        $insurance_groups = InsuranceGroup::orderBy('name', 'asc')->pluck('name', 'id');

        return view('insurance::insurance_reports.group_premiums_report', compact('group_transactions', 'insurance_groups'));
    }

    public function insurance_income_received(Request $request) {

        if($request->start_date){
            $start_date = Carbon::createFromFormat('d/m/Y', $request->start_date)->toDateString();
            $end_date = Carbon::createFromFormat('d/m/Y', $request->end_date)->toDateString();
        } else {
            $start_date = Carbon::now()->startOfDay()->toDateTimeString();
            $end_date = Carbon::now()->endOfDay()->toDateTimeString();
        }

        $premiums = 0;
        $co_payments = 0;

        $insurance_subscriptions = DB::table('insurance_subscriptions')->whereBetween('payment_date', [$start_date, $end_date])
            ->select('family_amount')->get();

        foreach ($insurance_subscriptions as $insurance_subscription) {
            $arrayPremium = explode(",", $insurance_subscription->family_amount);

            for ($x = 0; $x < count($arrayPremium); $x++) {
                if (is_numeric($arrayPremium[$x])){
                    $premiums += $arrayPremium[$x];
                }
            }
        }

        /*$single_group_receipts_sum = DB::table('single_group_receipts')->whereBetween('payment_date', [$start_date, $end_date])
                        ->sum('amount');
        $premiums += $single_group_receipts_sum;*/

        $service_deposits = DB::table('service_deposits')->whereBetween('created_at', [$start_date, $end_date])
            ->where('service_type', '=', 'Co_Payment')->select('patient_amount_paid')->get();

        foreach ($service_deposits as $service_deposit) {
            $co_payments += $service_deposit->patient_amount_paid;
        }

        $insurance_claims = DB::table('insurance_claims')
            ->whereBetween('created_at', [$start_date, $end_date])
            ->whereNotIn('claim_status', [1,4,5]) // should be adjudicated or released
            ->whereNull('deleted_at')->get();

        $expenditure_total = 0;

        foreach ($insurance_claims as $insurance_claim) {
            $expenditure_total += $insurance_claim->primary_plan_claim_total;
        }

        //store dates in session variable
        session()->put('start_date', $start_date);
        session()->put('end_date', $end_date);

        $total = $premiums + $co_payments;

        $search_text = "Searching between " . streamline_date($start_date) . " and " . streamline_date($end_date);

        return view('insurance::insurance_reports.insurance_income_received', compact('premiums', 'co_payments', 'total', 'search_text', 'expenditure_total'));
    }

    public function total_insurance_expenditure_redirect() {
        $start_date = session()->get('start_date');
        $end_date = session()->get('end_date');

        $insurance_claims = DB::table('insurance_claims')
            ->whereBetween('created_at', [$start_date, $end_date])
            ->whereNotIn('claim_status', [1,4,5]) // should be adjudicated or released
            ->whereNull('deleted_at')->get();

        $drugs_total = 0;
        $investigations_total = 0;
        $procedures_total = 0;
        $bed_stay_total = 0;
        $sundries_total = 0;
        $services_total = 0;

        foreach ($insurance_claims as $insurance_claim) {
            if ($insurance_claim->item_type == 1) {
                $services_total += $insurance_claim->primary_plan_claim_total;
            } elseif ($insurance_claim->item_type == 2) {
                $procedures_total += $insurance_claim->primary_plan_claim_total;
            } elseif ($insurance_claim->item_type == 3) {
                $investigations_total += $insurance_claim->primary_plan_claim_total;
            } elseif ($insurance_claim->item_type == 4) {
                $drugs_total += $insurance_claim->primary_plan_claim_total;
            } elseif ($insurance_claim->item_type == 5) {
                $sundries_total += $insurance_claim->primary_plan_claim_total;
            } elseif ($insurance_claim->item_type == 6) {
                $bed_stay_total += $insurance_claim->primary_plan_claim_total;
            }
        }

        $total = $drugs_total + $investigations_total + $procedures_total + $services_total + $sundries_total + $bed_stay_total;

        $insurance_groups_select = InsuranceGroup::orderBy('name', 'asc')->pluck('name', 'id')->toArray();
        $insurance_groups_select = ['' => '- select -'] + $insurance_groups_select;
        $group_id = 0;

        return view('insurance::insurance_reports.insurance_expenditure', compact('bed_stay_total', 'drugs_total', 'investigations_total', 'procedures_total', 'total', 'sundries_total', 'start_date', 'end_date', 'services_total', 'insurance_groups_select', 'group_id'));
    }

    public function insurance_deposit_income() {
        $start_date = session()->get('start_date');
        $end_date = session()->get('end_date');

        $service_deposits = DB::table('service_deposits')->whereBetween('created_at', [$start_date, $end_date])
            ->where('service_type', '=', 'Co_Payment')->orderBy('created_at', 'asc')->get();

        $groups = DB::table('insurance_members')->orderBy('id', 'asc')->pluck('group_id', 'patient_id');

        $families = DB::table('insurance_members')->orderBy('id', 'asc')->pluck('family_id', 'patient_id');

        $search_text = "Showing details for " . streamline_date($start_date) . " to " . streamline_date($end_date);

        return view('insurance::insurance_reports.insurance_deposit_income', compact('service_deposits', 'families', 'groups', 'search_text'));
    }

    public function insurance_income_received_details() {
        $start_date = session()->get('start_date');
        $end_date = session()->get('end_date');

        $insurance_subscriptions = DB::table('insurance_subscriptions')->whereBetween('payment_date', [$start_date, $end_date])
            ->orderBy('payment_date', 'asc')->get();

        $single_group_receipts = []; /*DB::table('single_group_receipts')->whereBetween('payment_date', [$start_date, $end_date])
                        ->get();*/

        $search_text = "Showing details for " . streamline_date($start_date) . " to " . streamline_date($end_date);

        return view('insurance::insurance_reports.insurance_income_received_details', compact('insurance_subscriptions', 'search_text', 'single_group_receipts'));
    }

    public function insurance_expenditure(Request $request) {
        $start_date = Carbon::now()->startOfDay()->toDateTimeString();
        $end_date = Carbon::now()->endOfDay()->toDateTimeString();
        $filter = [];
        $group_id = 0;

        try {
            if($request->start_date){
                $start_date = Carbon::createFromFormat('d/m/Y', $request->start_date)->toDateString();
                $end_date = Carbon::createFromFormat('d/m/Y', $request->end_date)->toDateString();
            } elseif (!empty(session()->get('start_date'))) {
                $start_date = Carbon::createFromFormat('Y-m-d', session()->get('start_date'))->toDateString();
                $end_date = Carbon::createFromFormat('Y-m-d', session()->get('end_date'))->toDateString();
            }
        } catch (\Exception $e) {}

        if (isset($request->group_id)) {
            $filter[] = ['group_id', '=', $request->group_id];
            $group_id = $request->group_id;
            session()->put('group_id',  $request->group_id);
        }

        $insurance_claims = DB::table('insurance_claims')
            ->where($filter)
            ->whereBetween('created_at', [$start_date, $end_date])
            ->whereNotIn('claim_status', [1,4,5]) // should be adjudicated or released
            ->whereNull('deleted_at')->get();

        $drugs_total = 0;
        $investigations_total = 0;
        $procedures_total = 0;
        $bed_stay_total = 0;
        $sundries_total = 0;
        $services_total = 0;

        foreach ($insurance_claims as $insurance_claim) {
            if ($insurance_claim->item_type == 1) {
                $services_total += $insurance_claim->primary_plan_claim_total;
            } elseif ($insurance_claim->item_type == 2) {
                $procedures_total += $insurance_claim->primary_plan_claim_total;
            } elseif ($insurance_claim->item_type == 3) {
                $investigations_total += $insurance_claim->primary_plan_claim_total;
            } elseif ($insurance_claim->item_type == 4) {
                $drugs_total += $insurance_claim->primary_plan_claim_total;
            } elseif ($insurance_claim->item_type == 5) {
                $sundries_total += $insurance_claim->primary_plan_claim_total;
            } elseif ($insurance_claim->item_type == 6) {
                $bed_stay_total += $insurance_claim->primary_plan_claim_total;
            }
        }

        $total = $drugs_total + $investigations_total + $procedures_total + $services_total + $sundries_total + $bed_stay_total;

        //store dates in session variable
        session()->put('start_date', $start_date);
        session()->put('end_date', $end_date);

        $insurance_groups_select = InsuranceGroup::orderBy('name', 'asc')->pluck('name', 'id')->toArray();
        $insurance_groups_select = ['' => '- select -'] + $insurance_groups_select;

        return view('insurance::insurance_reports.insurance_expenditure', compact('drugs_total', 'investigations_total', 'procedures_total', 'services_total', 'total', 'sundries_total', 'start_date', 'end_date', 'bed_stay_total', 'insurance_groups_select', 'group_id'));
    }

    public function insurance_expenditure_details($item_type) {
        //get date
        $start_date = session()->get('start_date');
        $end_date = session()->get('end_date');
        $group_id = session()->get('group_id');

        $item_type_name = [1 => 'Services', 2 => 'Procedures', 3 => 'Investigations', 4 => 'Drugs', 5 => 'Sundries', 6 => 'Inpatient Accommodation'][$item_type] ?? "";
        if(!empty($group_id)) $insurance_claims = DB::table('insurance_claims')
            ->where([['item_type', $item_type], ['group_id', $group_id]])
            ->whereBetween('created_at', [$start_date, $end_date])
            ->whereNotIn('claim_status', [1,4,5]) // should be adjudicated or released
            ->whereNull('deleted_at')->get();
        else $insurance_claims = DB::table('insurance_claims')
            ->where('item_type', $item_type)
            ->whereBetween('created_at', [$start_date, $end_date])
            ->whereNotIn('claim_status', [1,4,5]) // should be adjudicated or released
            ->whereNull('deleted_at')->get();

        if ($item_type == 1) {
            $items = DB::table('services')->where('available', 1)->pluck('name', 'id');
        } elseif ($item_type == 2) {
            $items = DB::table('procedures')->where('available', 1)->pluck('name', 'id');
        } elseif ($item_type == 3) {
            $items = DB::table('investigations')->pluck('name', 'id');
        } elseif ($item_type == 4) {
            $items = DB::table('drugs')->pluck('name', 'id');
        } elseif ($item_type == 5) {
            $items = DB::table('sundries')->where('available', 1)->pluck('name', 'id');
        } elseif ($item_type == 6) {
            $items = DB::table('inpatient_bed_categories')->pluck('name', 'id');
        } else {
            $items = [];
        }

        return view('insurance::insurance_reports.insurance_expenditure_details', compact('insurance_claims', 'start_date', 'end_date', 'item_type_name', 'items', 'item_type'));
    }

    public function expired_premiums_search(Request $request)
    {
        $search_by = $request->search_by;
        $reg_date = $request->reg_date;
        $start_date = $request->start_date;
        $end_date = $request->end_date;

        if (!is_null($search_by) && $search_by != "3"){
            if($search_by == "1"){
                // custom date
                $start_date_search = Carbon::parse($reg_date)->startOfDay()->toDateString();
                $end_date_search = Carbon::parse($reg_date)->endOfDay()->toDateString();
            } elseif($search_by == "2"){
                // custom date range
                $start_date_search = Carbon::parse($start_date)->startOfDay()->toDateString();
                $end_date_search = Carbon::parse($end_date)->endOfDay()->toDateString();
            } else {
                $start_date_search = Carbon::now()->subDay()->toDateString();
                $end_date_search = Carbon::now()->toDateString();
            }

            $insurance_premiums = DB::select("SELECT insurance_subscriptions.group_id, end_date, start_date, amount, payment_date, covered_member_ids,covered_member_premiums_paid FROM insurance_subscriptions INNER JOIN (SELECT group_id, MAX(end_date) AS max_end_date FROM insurance_subscriptions GROUP BY group_id) ms ON insurance_subscriptions.group_id = ms.group_id AND end_date = max_end_date WHERE end_date BETWEEN '{$start_date_search}' AND '{$end_date_search}'");
        } else {
            $insurance_premiums = DB::select("SELECT insurance_subscriptions.group_id, end_date, start_date, amount, payment_date, covered_member_ids,covered_member_premiums_paid FROM insurance_subscriptions INNER JOIN (SELECT group_id, MAX(end_date) AS max_end_date FROM insurance_subscriptions GROUP BY group_id) ms ON insurance_subscriptions.group_id = ms.group_id AND end_date = max_end_date");
        }

        return view('insurance::insurance_reports.expired_insurance', compact('insurance_premiums'));
    }

}