<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
use Illuminate\Support\Str;

use Validator;
use Carbon\Carbon;

use Illuminate\Support\Facades\Crypt;
use Maatwebsite\Excel\Facades\Excel;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use App\Exports\Export_age;
use App\Exports\MME_Export\Reception\ReceptionExport;
use App\Exports\MME_Export\Lab\LabExport;
use App\Exports\MME_Export\Counselling\CounsellingExport;
use App\Exports\MME_Export\STI\STIExport;
use App\Exports\MME_Export\Prevention\PreventionExport;
use App\Exports\MME_Export\CervicalCancer\CervicalExport;
use App\Exports\MME_Export\CMV\CMVExport;
use App\Exports\MME_Export\MentalHealth\MentalExport;
use App\Exports\MME_Export\NCD\NCDExport;
use App\Exports\MME_Export\TB\TBExport;
use App\Exports\RiskbackExcel\RefillRisk;
use App\Helper\ExportHelper;
use Illuminate\Contracts\Encryption\DecryptException;
//use App\Exports\RefillRisk;
use DateTime;


class MME_ExportController extends Controller
{
	//new Ncd view
	 protected $DB;

    public function __construct() {
        $this->DB = (new ExportHelper())->DB;
    }
	protected $table_code = "General";
	protected $final_risklog = [];
	protected	$final_log = [];

	public function mme_export_View()
	{
		$mam_userType = Auth()->user()->type;
		//return view("MME.mme_export");
		//return view('MME.mme_export',['userType'=> $mam_userType]);
		$latestRecords = [];
		foreach ($this->DB as $data_row) {
			$latestRecord = DB::connection($data_row)
				->table('followup_generals')
				->latest('created_at')  // Change to your date column
				->first();

			if ($latestRecord) {
				$latestRecords[$data_row] = $latestRecord->created_at;
			} else {
				$latestRecords[$data_row] = 'No records found';
			}
		}
		// // Output the latest records from all 4 databases
		// //foreach ($latestRecords as $db_data => $latestDateTime) {
		// //	echo "Latest record from $db_data: $latestDateTime \n";
		// //}

		return view("MME.mme_export", ["userType" => $mam_userType, "latestRecords" => $latestRecords]);
	}
	public function mme_export(Request $request)
	{
		switch ($request["road"]) {
			case "2":
				return $this->Counselling_Export($request);
				break;
			case "3":
				return $this->STI_Export($request);
				break;
			case "4":
				return $this->Prevention_Export($request);
				break;
			case "5":
				return $this->Cervical_Export($request);
				break;
			case "6":
				return $this->CMV_Export($request);
				break;
			case "7":
				return $this->NCD_Export($request);
				break;
			case "8": //TB03
			case "9": //PreTB
			case "10": //IPT
				return $this->TB_Export($request);
				break;
			case "11":
				return $this->MentalScreen($request);
				break;
			default:
				return redirect()->back();
				break;
		}
	}


	public function Counselling_Export($request)
	{
		$table_name = null;
		$act_table = null;
		$counselling_records = collect([]);
		switch ($request["other"]) {
			case "counsel_data":
				$act_table = "counsellor_records";
				$table_name = "CounsellorRecords";
				$export_name = "Counselling_Export";
				$encryptes = ["Counsellor", "Main Risk", "Sub Risk", "HTSdone", "Reason", "Status", "PrEP Status", "Gender"];
				$date_type = ["Counselling_Date", "Reg Date"];
				break;
			case "hts_data":
				$act_table = "coulsellings";
				$table_name = "Coulselling";
				$export_name = "HTS_Export";
				$encryptes = ["Gender", "Counsellor", "Service_Modality", "Mode of Entry", "New_Old", "Test_Location", "Main Risk", "Sub Risk", "HIV_Test_Determine", "HIV_Test_UNI", "HIV_Test_STAT", "HIV_Final_Result", "Syphillis_RDT", "Syphillis_RPR", "Syphillis_VDRL", "Hepatitis_B", "Hepatitis_C", "Req_Doctor"];
				$date_type = ["Counselling_Date", "HIV_Test_Date", "Syp_Test_Date", "Hep_Test_Date"];
				break;
		}

		if ($table_name != null && $act_table != null) {
			$modelClassName = "App\\Models\\" . $table_name; // extend model
			$model = app()->make($modelClassName); // resolves the model from the service container.
			foreach ($request["clinics"] as $clinic) {
				$model->setConnection($this->DB[$clinic]);
				$counselling_data = $model
					->whereBetween("Counselling_Date", [$request["From_date"], $request["To_date"]])
					->leftJoin("patients", "patients.Pid", "=", $act_table . ".Pid")
					->when($request["other"] == "hts_data", function ($query) use ($act_table) {
						return $query->leftJoin("labs", function ($join) use ($act_table) {
							$join->on("labs.CID", "=", $act_table . ".Pid")
								->whereColumn("labs.vdate", "=", "Counselling_Date");
						});
					})
					->select($act_table . ".*", "Date of Birth", "patients.Agey", "patients.Agem", "patients.Gender", "patients.FuchiaID", $act_table . ".Pid", "Risk Log", "Former Risk", "Risk Change_Date", "patients.Main Risk", "patients.Sub Risk", $act_table . ".created_at", $act_table . ".updated_at")
					->when($request["other"] == "hts_data", function ($query) {
						return $query->addSelect("labs.Req_Doctor");
					})
					->get();
				$counselling_records = $counselling_records->merge($counselling_data);
			}
		} else {
			abort(404);
		}
		foreach ($counselling_records as $key => $counselling_record) {
			$counselling_record = Export_age::Export_general($counselling_record, $counselling_record["Counselling_Date"], $counselling_record["Date of Birth"], $counselling_record);
			if ($counselling_record["Date of Birth"] == null) {
				$counselling_record = ExportHelper::NoCofidential($counselling_record, $counselling_record["Counselling_Date"]);
			}
			$carbonDate = Carbon::createFromFormat('Y-m-d', $counselling_record['Counselling_Date']);
			$carbonDate = Carbon::createFromFormat('d-m-Y', $carbonDate->format('d-m-Y'));
			$vdate = new DateTime($carbonDate);
			if ($counselling_record["Risk Log"] != null) {
				$forRiskCheck[1]['Pid'] = $counselling_record['Pid'];
				$forRiskCheck[1]['Risk Log'] = $counselling_record['Risk Log'];
				if (!array_key_exists($counselling_record['Pid'], $this->final_log) && $counselling_record['Risk Log'] != null) {
					$this->final_risklog = RefillRisk::FillRisk($forRiskCheck);
					$this->final_log[$counselling_record['Pid']] = $this->final_risklog;
				}
				if (array_key_exists($counselling_record['Pid'], $this->final_log)) {
					foreach (array_reverse($this->final_log[$counselling_record['Pid']][$counselling_record['Pid']]) as $date => $data) {
						if (strlen($date) == 10) {
							$riskChangeDate = new DateTime($date);
							if ($vdate < $riskChangeDate) {
								$counselling_record['Main Risk'] = Crypt::encrypt_light($data['Old Risk'], 'General');
								$counselling_record['Sub Risk'] = Crypt::encrypt_light($data['Old Sub Risk'], 'General');
							}
						}
					}
				}
			} elseif ($counselling_record['Risk Change_Date'] != null && $counselling_record['Former Risk'] != null && $counselling_record['Former Risk'] != "731") {
				$riskChangeDate = Carbon::createFromFormat('Y-m-d', $counselling_record['Risk Change_Date']);
				$riskChangeDate = new DateTime(Carbon::createFromFormat('d-m-Y', $riskChangeDate->format('d-m-Y')));
				if ($vdate < $riskChangeDate) {
					$counselling_record['Main Risk'] = $counselling_record['Former Risk'];
					$counselling_record['Sub Risk'] = '';
				}
			}

			foreach ($encryptes as $key => $encrypte) {
				$counselling_record[$encrypte] = Crypt::decrypt_light($counselling_record[$encrypte], "General");
				if (($encrypte == "Main Risk" || $encrypte == "Sub Risk") && $counselling_record[$encrypte] == "-") {
					$counselling_record[$encrypte] = null;
				}
				$counselling_record[$encrypte] = Crypt::codeBook($counselling_record[$encrypte], "encode");
			}
			foreach ($date_type as $column) {
				$dateString = $counselling_record[$column];
				if (!empty($dateString)) {
					$carbonDate = Carbon::createFromFormat("Y-m-d", $dateString);
					$ddString = $carbonDate->format("d-m-Y");

					$carbonDate = Carbon::createFromFormat("d-m-Y", $ddString); // Assuming you have a Carbon instance
					$counselling_record[$column] = Date::dateTimeToExcel($carbonDate->startOfDay()); // Convert to Excel-compatible date
				}
			}
		}

		return Excel::download(new CounsellingExport($counselling_records, $export_name), $export_name . "-" . date("d-m-Y") . "." . $request["typeExport"]);
	}

	public function STI_Export($request)
	{
		$table_name = null;
		$act_table = null;
		$sti_values = collect([]);
		switch ($request["other"]) {
			case "Male":
				$table_name = "Stimale";
				$act_table = "stimales";


				// "0" to ""
				$defaultValue_Manipulation4 = [
					"esti_size",
					"des_size",
					"estimated_siz",
				];

				// "5" to "3"
				$defaultValue_Manipulation2 = [
					"tbl_treat_diagnosis_first_visit",
					"epi_discharge",
					"unprot_sex_new_part",
					"genital_signs",
				];
				// "-1" to "1"
				$defaultValue_Manipulation3 = [
					"tre_azythro",
					"tre_cefixim",
					"tre_ciprofloxacin",
					"tre_tinidazole",
					"tre_fluconazole",
					"tre_doxycycline",
					"tre_ceftriaxone",
					"tre_benz_pen",
					"no_treat",
				];
				// null to default "0"
				$defaultValue_Manipulation1 = [
					"urethral_disc",
					"dysuria",
					"genital_prut",
					"genital_pain",
					//"genital_pain_hl",
					"genital_ulcer",
					"pain",
					"ulcer",
					"prodromal_itch",
					"vesicles",
					"recurrent",
					"suspects_herpes",
					"ing_lymph_node",
					"unilateal",
					"leg_ulcer",
					"scrotal_swelling",
					"td_ntd",
					"gen_wart",
					"physical_exam",
					"urinated_wit_1h",
					"discharge",
					"discharge_milk",
					"colour",
					"erythema",
					"blisters",
					"gen_ulcer",
					"sing_multi",
					"pain_full_less",
					"herpes_suspect",
					"inguinal_bubo",
					"fluctant",
					"tendr_ntender",
					"oth_leg_inf",
					"phy_genital_wart",
					"crab_lice",
					"scabies",
					"gscrotal_swelling",
					"unilateal_bilateral",
					"gtender_ntender",
					"erythem",
					//"des_size",
					"tbl_treat_diagnosis_first_visit",
					"epi_discharge",
					"unprot_sex_new_part",
					"genital_signs",

					"tre_azythro",
					"tre_cefixim",
					"tre_ciprofloxacin",
					"tre_tinidazole",
					"tre_fluconazole",
					"tre_doxycycline",
					"tre_ceftriaxone",
					"tre_benz_pen",
					"no_treat",
					"al_Penicillin",
					"al_sulfa",
					"part_treat",
					"condom_giv",
				];
				$encrypted_columns = [
					"Gender",
					"last_vis_within",
					"about_clinic",
					"demo_remarks",
					"visit_type",
					"visit_time",
					"followup_visit",
					"episode",
					"Reason for Visit",
					"Main Risk",
					"Sub Risk",
					"urethral_disc",
					"urethral_disc_hl",
					"dysuria",
					"dysuria_hl",
					"genital_prut",
					"genital_prut_hl",
					"genital_pain",
					"genital_pain_hl",
					"genital_ulcer",
					"genital_ulcer_hl",
					"pain",
					"ulcer",
					"prodromal_itch",
					"vesicles",
					"recurrent",
					"last_episode",
					"suspects_herpes",
					"ing_lymph_node",
					"ing_lymph_node_hl",
					"unilateal",
					"leg_ulcer",
					"scrotal_swelling",
					"scrotal_swelling_hl",
					"td_ntd",
					"gen_wart",
					"gen_wart_hl",
					"physical_exam",
					"urinated_wit_1h",
					"discharge",
					"discharge_milk",
					"colour",
					"erythema",
					"blisters",
					"gen_ulcer",
					"esti_size",
					"sing_multi",
					"pain_full_less",
					"herpes_suspect",
					"inguinal_bubo",
					"fluctant",
					"tendr_ntender",
					"oth_leg_inf",
					"phy_genital_wart",
					"crab_lice",
					"scabies",
					"gscrotal_swelling",
					"estimated_siz",
					"unilateal_bilateral",
					"gtender_ntender",
					"erythem",
					"des_size",
					"tbl_treat_diagnosis_first_visit",
					"epi_discharge",
					"unprot_sex_new_part",
					"genital_signs",
					'previous_sti', //new
					'prior_sti', //new
					"pri_syphillis",
					"sec_syphillis",
					"chancroid",
					"gen_herpes",
					"gen_scabies",
					"gud_other",
					"Gonorhoea",
					"non_gono_urethritis",
					"non_gono_procti",
					"trichomonas",
					"genital_candidiosis",
					"beterial_vaginosis",
					"congenial_syphillis",
					"latent_syphillis",
					"molluscum_contag",
					"bubos",
					"othstd_genital_warts",
					"ostd_other",
					"tre_azythro",
					'acyclovir', //new
					'clotrimazole', //new
					'tre_podophyllin', //new
					'counsel_disclosure', //new
					'treatment_side_effect', //new
					'other_lymph_node', //new
					"tre_cefixim",
					"tre_ciprofloxacin",
					"tre_tinidazole",
					"tre_fluconazole",
					"tre_doxycycline",
					"tre_ceftriaxone",
					"tre_benz_pen",
					"no_treat",
					"al_Penicillin",
					"al_sulfa",
					"part_treat",
					"condom_giv",
				];

				$encrypted_38 = ["presumptive_diag", "tre_remarks", "followup", "clinician_name"];
				break;
			case "Female":
				$table_name = "Stifemale";
				$act_table = "stifemales";

				$defaultValue_Manipulation4 = [
					"esti_size",
					"des_size",
					"estimated_siz",
				];

				// "5" to "3"
				$defaultValue_Manipulation2 = [
					"tbl_treat_diagnosis_first_visit",

				];
				// "-1" to "1"
				$defaultValue_Manipulation3 = [
					"tre_azythro",
					"tre_cefixim",
					"tre_ciprofloxacin",
					"tre_tinidazole",
					"tre_fluconazole",
					"tre_doxycycline",
					"tre_ceftriaxone",
					"tre_benz_pen",
					"no_treat",
				];
				// null to default "0"
				$defaultValue_Manipulation1 = [
					"urethral_disc",

				];

				$encrypted_columns = [
					"Gender",
					"last_vis_within",
					"vtype",
					"about_clinic",
					"demo_remarks",
					"episode",
					"rea_for_visit",
					"Main Risk",
					"Sub Risk",
					"abn_vaginal_disc",
					"abn_vaginal_disc_long",
					"linked_menstru",
					"amount",
					"colour",
					"colour_oth",
					"abn_veginal_odour",
					"l_abn_pain",
					"l_abon_pain_hl",
					"fever",
					"rec_terminate_preg",
					"dyspareunia",
					"dysuria",
					"dysuria_hl",
					"gen_prutitus",
					"gen_prutitus_hl",
					"gen_burn_pain",
					"gen_burn_pain_hl",
					"gen_ulcer",
					"gen_ulcer_hl",
					"pain",
					"ulcer",
					"prodromal_itch",
					"vesicles",
					"recurrent",
					"recurrent_last_episode",
					"patient_suspects_herpes",
					"inguinal_ln",
					"inguinal_ln_hl",
					"unilateal_Bilateral",
					"leg_ulcer_oth_inf",
					"genital_warts",
					"genital_warts_hl",
					"phy_exam_done",
					"washed_inside",
					"vulvar_erythema",
					"vulvar_odema",
					"vaginal_discharge",
					"vag_dis_amount",
					"homogeneous",
					"homogeneous_col",
					"smell_without_KOH",
					"vaginal_wall_injury",
					"adnexal_tenderness",
					"adnexal_enlargement",
					"genital_blisters",
					"gential_ulcer",
					"gential_ulcerl",
					"gent_ulcer_sm",
					"gential_ulcer_pain",
					"susp_herpes",
					"inguinal_bubo",
					"fluctuant",
					"fluctuant_tender",
					"oth_leg_infection",
					//ok
					"genital_wart",
					"crab_lice",
					"scablices",
					"KOH_smell_test",
					"pH_vagina",
					"prev_STI",
					"patient_genital_ulcer",
					"patient_compl_low_abd",
					"new_pat_past_3mont",
					"part_compl_gential_sym",
					"sworker",
					"rg_score",
					"risk",
					//ok
					"abn_yellow_disc",
					"dysuria_risk_ass",
					"low_abd_pain",
					"pain_dur_sexual",
					"unp_sex_new_clients",
					"partner_ulcer",

					"pri_syphillis",
					"sec_syphillis",
					"chancroid",
					"gen_herpes",
					"gen_scabies",
					"gud_other",
					"other_plz_specify",
					"Gonorhoea",
					"non_gono_urethritis",
					"non_gono_cervities",
					"trichomonas",
					//ok
					"genital_candidiosis",
					"beterial_vaginosis",
					"congenial_syphillis",
					"latent_syphillis",
					"latent_syphillis_preg",
					"molluscum_contag",
					"bubos",
					"othstd_genital_warts",
					"ostd_other",
					"tre_azythro",
					"tre_cefixim",
					"tre_ciprofloxacin",
					"tre_tinidazole",
					"tre_fluconazole",
					"tre_doxycycline",
					"tre_ceftriaxone",
					"tre_benz_pen",
					"tre_Other",
					"clotrimazole_vaginal_tab",
					"no_treatment",
					"al_Penicillin",
					"al_sulfa",
					"part_treat",
					"condom_giv",
					"first_visit",
					"other_STD",

					"endocervical_mucopus",
					"endocervical_colour",
					"cerv_motion_tenderness",
					"if_only_abnormal_abundant",
					"high_rg_score",
					"high_risk",
					"acyclovir",
					"clotrimazole",

					"tre_podophyllin",
					"counsel_disclosure",
					"counsellor_sti_sign_symptom",
					"counsel_vaginal",
					"treatment_side_effect",
				];

				//, "risk_cal_remark"

				$encrypted_38 = ["oth_GI_sympt", "genital_blisters_Location", "des_size", "presumptive_diag", "tre_remarks", "followup", "clinician"];
				break;
		}
		if ($table_name != null && $act_table != null) {
			$modelClassName = "App\\Models\\" . $table_name; // extend model
			$model = app()->make($modelClassName); // resolves the model from the service container.
			foreach ($request["clinics"] as $clinic) {
				$model->setConnection($this->DB[$clinic]);
				$sti_values_data = $model
					->whereBetween("Visit_date", [$request["From_date"], $request["To_date"]])
					->leftJoin("patients", "patients.Pid", "=", $act_table . ".CID")
					->select($act_table . ".*", "Date of Birth", "patients.Agey", "patients.Agem", "patients.Gender", "patients.FuchiaID", "patients.Pid", "Risk Log", "Former Risk", "Risk Change_Date", "patients.Main Risk", "patients.Sub Risk", $act_table . ".created_at", $act_table . ".updated_at")
					->get();
				$sti_values = $sti_values->merge($sti_values_data);
			}
		} else {
			abort(404);
		}
		foreach ($sti_values as $key => $sti_value) {
			$carbonDate = Carbon::createFromFormat('Y-m-d', $sti_value['Visit_date']);
			$carbonDate = Carbon::createFromFormat('d-m-Y', $carbonDate->format('d-m-Y'));
			$vdate = new DateTime($carbonDate);
			$sti_value['Pid'] = $sti_value['CID'];

			$sti_value = Export_age::Export_general($sti_value, $sti_value["Visit_date"], $sti_value["Date of Birth"], $sti_value);

			if ($sti_value["Date of Birth"] == null) {
				$sti_value = ExportHelper::NoCofidential($sti_value, $sti_value["Visit_date"]);
			}

			if ($sti_value["Risk Log"] != null) {
				$forRiskCheck[1]['Pid'] = $sti_value['CID'];
				$forRiskCheck[1]['Risk Log'] = $sti_value['Risk Log'];
				if (!array_key_exists($sti_value['CID'], $this->final_log) && $sti_value['Risk Log'] != null) {
					$this->final_risklog = RefillRisk::FillRisk($forRiskCheck);
					$this->final_log[$sti_value['CID']] = $this->final_risklog;
				}
				if (array_key_exists($sti_value['CID'], $this->final_log)) {
					foreach (array_reverse($this->final_log[$sti_value['CID']][$sti_value['CID']]) as $date => $data) {
						if (strlen($date) == 10) {
							$riskChangeDate = new DateTime($date);
							if ($vdate < $riskChangeDate) {
								$sti_value['Main Risk'] = Crypt::encrypt_light($data['Old Risk'], 'General');
								$sti_value['Sub Risk'] = Crypt::encrypt_light($data['Old Sub Risk'], 'General');
							}
						}
					}
				}
			} elseif ($sti_value['Risk Change_Date'] != null && $sti_value['Former Risk'] != null && $sti_value['Former Risk'] != "731") {
				$riskChangeDate = Carbon::createFromFormat('Y-m-d', $sti_value['Risk Change_Date']);
				$riskChangeDate = new DateTime(Carbon::createFromFormat('d-m-Y', $riskChangeDate->format('d-m-Y')));
				if ($vdate < $riskChangeDate) {
					$sti_value['Main Risk'] = $sti_value['Former Risk'];
					$sti_value['Sub Risk'] = '';
				}
			}
			foreach ($encrypted_columns as $key => $encrypte) {
				$sti_value[$encrypte] = Crypt::decrypt_light($sti_value[$encrypte], "General");

				if (($encrypte == "Main Risk" || $encrypte == "Sub Risk") && $sti_value[$encrypte] == "-") {
					$sti_value[$encrypte] = null;
				}
				$sti_value[$encrypte] = Crypt::codeBook($sti_value[$encrypte], "encode");
				// This condition is to manipulate the data with old data from STI
				// blank in excel to "0" value
				if (in_array($encrypte, $defaultValue_Manipulation1)) {
					if ($sti_value[$encrypte] == null) {
						$sti_value[$encrypte] = "0";
					}
				}

				// "5" in excel to "3"
				if (in_array($encrypte, $defaultValue_Manipulation2)) {
					if ($sti_value[$encrypte] == "5") {
						$sti_value[$encrypte] = "3";
					}
				}
				// "-1" in excel to "1"
				if (in_array($encrypte, $defaultValue_Manipulation3)) {
					if ($sti_value[$encrypte] == "-1") {
						$sti_value[$encrypte] = "1";
					}
				}
				// "0" in excel to ""
				if (in_array($encrypte, $defaultValue_Manipulation4)) {
					if ($sti_value[$encrypte] == "0") {
						$sti_value[$encrypte] = "";
					}
				}
				// for sti female risk score calculation for 6 questions with variable //ok"abn_yellow_disc","dysuria_risk_ass",
				//	"low_abd_pain","pain_dur_sexual","unp_sex_new_clients","partner_ulcer",


				if ($encrypte == "abn_yellow_disc") {
					if ($sti_value[$encrypte] == '1') {
						$Q1_val = 2;
					} else {
						$Q1_val = 0;
					}
				} else if ($encrypte == "dysuria_risk_ass") {
					if ($sti_value[$encrypte] == '1') {
						$Q2_val = 1;
					} else {
						$Q2_val = 0;
					}
				} else if ($encrypte == "low_abd_pain") {
					if ($sti_value[$encrypte] == '1') {
						$Q3_val = 1;
					} else {
						$Q3_val = 0;
					}
				} else if ($encrypte == "pain_dur_sexual") {
					if ($sti_value[$encrypte] == '1') {
						$Q4_val = 1;
					} else {
						$Q4_val = 0;
					}
				} else if ($encrypte == "unp_sex_new_clients") {
					if ($sti_value[$encrypte] == '1') {
						$Q5_val = 1;
					} else {
						$Q5_val = 0;
					}
				} else if ($encrypte == "partner_ulcer") {
					if ($sti_value[$encrypte] == '1') {
						$Q6_val = 2;
					} else {
						$Q6_val = 0;
					}
					$risk_score_for6q = $Q1_val + $Q2_val + $Q3_val + $Q4_val + $Q5_val + $Q6_val;
					$sti_value['risk_score_for6q'] = $risk_score_for6q;
				}
			}
			foreach ($encrypted_38 as $column) {
				$sti_value[$column] = Crypt::decryptString($sti_value[$column], "General");

				// "0" in excel to ""
				if (in_array($column, $encrypted_38)) {
					if ($sti_value[$column] == "0") {
						$sti_value[$column] = "";
					}
				}
			}
			$carbonDate = Carbon::createFromFormat("Y-m-d", $sti_value["Visit_date"]);
			$carbonDate = Carbon::createFromFormat("d-m-Y", $carbonDate->format("d-m-Y"));
			$sti_value["Visit_date"] = Date::dateTimeToExcel($carbonDate->startOfDay());
		}
		return Excel::download(new STIExport($sti_values, $request["other"]), "STI_" . $request["other"] . "-" . date("d-m-Y") . "." . $request["typeExport"]);
	}

	public function Prevention_Export($request)
	{
		$table_name = null; //model Name
		$act_table = null; //for left join
		$export_name = null;
		$prevention_values = collect([]);
		switch ($request["other"]) {
			case "log_sheet":
				$table_name = "PreventionLogsheet";
				$act_table = "prevention_logsheets";
				$export_name = "LogSheet";
				$encrypted_columns = ["Main_Risk", "Sub_Risk", "HIV Status", "Initial Risk", "Changed_Risk", "HIV_Final_result", "Gender"];
				$prevent_dates = ["date_confirm", "Reg_Date", "Visit_Date", "Risk changed Date", "OST_Initial_Date"];
				break;
			case "cbs":
				$table_name = "PreventionCBS";
				$act_table = "prevention_c_b_s";
				$export_name = "CBS";
				$encrypted_columns = [
					"Main_Risk",
					"Sub_Risk",
					"HIV_determine_result",
					"HIV result",
					"HIV Sero-Status",
					"Gender", // decrypt
				];
				$prevent_dates = ["Visit_Date", "date_confirm"];
				break;
			case "confidential":
				$table_name = "Patients";
				$act_table = "patients";
				$export_name = "Server_Confidential";
				$encrypted_columns = [
					"Main Risk",
					"Sub Risk",
					"Gender",
					"Former Risk", // decrypt
				];
				$prevent_dates = ["Reg Date", "Risk Change_Date"];
				break;
		}
		if ($table_name != null && $act_table != null) {
			$modelClassName = "App\\Models\\" . $table_name; // extend model
			$model = app()->make($modelClassName); // resolves the model from the service container.
			foreach ($request["clinics"] as $clinic) {
				$model->setConnection($this->DB[$clinic]);
				switch ($request["other"]) {
					case 'log_sheet':
					case 'cbs':
						$prevention_values_data = $model
							->whereBetween("Visit_Date", [$request["From_date"], $request["To_date"]])
							->leftJoin("patients", "patients.Pid", "=", $act_table . ".Pid")
							->select(
								$act_table . ".*",
								"Date of Birth",
								"patients.Agey",
								"patients.Agem",
								"patients.Gender",
								"patients.FuchiaID",
								"patients.PrEPCode",
								"patients.Main Risk",
								"patients.Sub Risk",
								"patients.Risk Log",
								"patients.Former Risk",
								"patients.Risk Change_Date",
								$act_table . ".created_at",
								$act_table . ".updated_at"
							)
							->get();
						break;
					case 'confidential':
						$prevention_values_data = $model->whereBetween("Reg Date", [$request["From_date"], $request["To_date"]])->get();
						break;
				}
				$prevention_values = $prevention_values->merge($prevention_values_data);
			}

			// For confidential patient export, keep only the latest record per Pid across all clinics
			if ($request["other"] == "confidential") {
				$prevention_values = $prevention_values
					->groupBy("Pid")
					->map(function ($rows) {
						return $rows->sortByDesc(function ($row) {
							$regDate = $row["Reg Date"] ?? null;
							$timestamp = $regDate ? Carbon::createFromFormat("Y-m-d", $regDate)->timestamp : 0;
							$updated = isset($row["updated_at"]) ? strtotime($row["updated_at"]) : 0;
							return [$timestamp, $updated];
						})->first();
					})
					->values();
			}
		} else {
			abort(404);
		}

		foreach ($prevention_values as $key => $prevention_value) {
			if ($prevention_value["Date of Birth"] != null) {
				$prevention_value = Export_age::Export_general($prevention_value, $prevention_value["Visit_Date"], $prevention_value["Date of Birth"], $prevention_value);
			} else {
				$prevention_value['He Code'] = null;
				$prevention_value['Clinic Code'] = null;
				$prevention_value['PrEPCode'] = null;
			}
			if ($request["other"] != "confidential") {
				$carbonDate = Carbon::createFromFormat('Y-m-d', $prevention_value['Visit_Date']);
				$carbonDate = Carbon::createFromFormat('d-m-Y', $carbonDate->format('d-m-Y'));
				$vdate = new DateTime($carbonDate);
				if ($prevention_value["Risk Log"] != null) {
					$forRiskCheck[1]['Pid'] = $prevention_value['Pid'];
					$forRiskCheck[1]['Risk Log'] = $prevention_value['Risk Log'];
					if (!array_key_exists($prevention_value['Pid'], $this->final_log) && $prevention_value['Risk Log'] != null) {
						$this->final_risklog = RefillRisk::FillRisk($forRiskCheck);
						$this->final_log[$prevention_value['Pid']] = $this->final_risklog;
					}
					if (array_key_exists($prevention_value['Pid'], $this->final_log)) {
						foreach (array_reverse($this->final_log[$prevention_value['Pid']][$prevention_value['Pid']]) as $date => $data) {
							if (strlen($date) == 10) {
								$riskChangeDate = new DateTime($date);
								if ($vdate < $riskChangeDate) {
									$prevention_value['Main Risk'] = Crypt::encrypt_light($data['Old Risk'], 'General');
									$prevention_value['Sub Risk'] = Crypt::encrypt_light($data['Old Sub Risk'], 'General');
								}
							}
						}
					}
				} elseif ($prevention_value['Risk Change_Date'] != null && $prevention_value['Former Risk'] != null && $prevention_value['Former Risk'] != "731") {
					$riskChangeDate = Carbon::createFromFormat('Y-m-d', $prevention_value['Risk Change_Date']);
					$riskChangeDate = new DateTime(Carbon::createFromFormat('d-m-Y', $riskChangeDate->format('d-m-Y')));
					if ($vdate < $riskChangeDate) {
						$prevention_value['Main Risk'] = $prevention_value['Former Risk'];
						$prevention_value['Sub Risk'] = '';
					}
				}
				if ($request["other"] == "log_sheet") {
					$visit_year = explode('-', $prevention_value["Visit_Date"])[0];
					$model->setConnection($prevention_value->getConnectionName());
					$final_new_old = $model->whereYear('Visit_Date', $visit_year)
						->where("Pid", $prevention_value["Pid"])
						->where('Visit_Date', '<', $prevention_value["Visit_Date"])->exists();
					if ($final_new_old) {
						$prevention_value["New_Old"] = "Old";
					} else {
						$prevention_value["New_Old"] = "New";
					}
				}
			}
			foreach ($encrypted_columns as $key => $encrypte) {
				$prevention_value[$encrypte] = Crypt::decrypt_light($prevention_value[$encrypte], "General");
				if (($encrypte == "Main Risk" || $encrypte == "Sub Risk") && $prevention_value[$encrypte] == "-") {
					$prevention_value[$encrypte] = null;
				}
				$prevention_value[$encrypte] = Crypt::codeBook($prevention_value[$encrypte], "encode");
			}
			 if ($request["other"]=="confidential") {
				if (isset($prevention_value["Date of Birth"])) {
				    try {
					$prevention_value["Date of Birth"] = Crypt::decryptString($prevention_value["Date of Birth"]);
				    } catch (DecryptException $e) {
					\Log::warning('Failed to decrypt "Date of Birth" due to invalid payload: ' . $e->getMessage());
					$prevention_value["Date of Birth"] = null; // Or ''
				    }
				}


			 	$time=strtotime($prevention_value["Date of Birth"]);
			 	$prevention_value["Date of Birth"]=date('Y-m-d',$time);
			 }
			foreach ($prevent_dates as $column) {
				if ($prevention_value[$column] != null) {
					$carbonDate = Carbon::createFromFormat("Y-m-d", $prevention_value[$column]);
					$carbonDate = Carbon::createFromFormat("d-m-Y", $carbonDate->format("d-m-Y"));
					$prevention_value[$column] = Date::dateTimeToExcel($carbonDate->startOfDay());
				}
			}
		}
		//dd($prevention_values[0]);

		return Excel::download(new PreventionExport($prevention_values, $export_name), $export_name . "-" . date("d-m-Y") . "." . $request["typeExport"]);
	}

	public function Cervical_Export($request)
	{
		$cervical_values = collect([]);
		$cervical_dates = ["Visit_date", "LMP", "UCG_test_date", "Postpone_date", "Date", "Followup_date", "AE_Date", "AE_followUp_Date"];
		$modelClassName = "App\\Models\\Cervicalcancer"; // extend model
		$model = app()->make($modelClassName); // resolves the model from the service container.
		foreach ($request["clinics"] as $clinic) {
			$model->setConnection($this->DB[$clinic]);
			$cervical_values_data = $model
				->whereBetween("Visit_date", [$request["From_date"], $request["To_date"]])
				->leftJoin("patients", "patients.Pid", "=", "cervicalcancers.General ID")
				->select(
					"cervicalcancers.*",
					"Date of Birth",
					"patients.Agey",
					"patients.Agem",
					"patients.Gender",
					"patients.FuchiaID",
					"patients.Pid",
					"patients.Main Risk",
					"patients.Sub Risk",
					"cervicalcancers.created_at",
					"cervicalcancers.updated_at",
					"patients.Risk Log",
					"patients.Former Risk",
					"patients.Risk Change_Date"
				)
				->get();
			$cervical_values = $cervical_values->merge($cervical_values_data);
		}


		foreach ($cervical_values as $key => $cervical_value) {
			$cervical_value["Pid"] = $cervical_value["General ID"];


			if ($cervical_value["Date of Birth"] == null || $cervical_value["Pid"] == null) {
				$cervical_value = ExportHelper::NoCofidential($cervical_value, $cervical_value["Visit_date"]);
			} else {
				$cervical_value = Export_age::Export_general($cervical_value, $cervical_value["Visit_date"], $cervical_value["Date of Birth"], $cervical_value);
			}
			foreach ($cervical_dates as $cervical_date) {
				if ($cervical_value[$cervical_date] != null) {
					$carbonDate = Carbon::createFromFormat("Y-m-d", $cervical_value[$cervical_date]);
					$carbonDate = Carbon::createFromFormat("d-m-Y", $carbonDate->format("d-m-Y"));
					$cervical_value[$cervical_date] = Date::dateTimeToExcel($carbonDate->startOfDay());
				}
			}
		}
		return Excel::download(new CervicalExport($cervical_values), "Cervical_CancerExport-" . date("d-m-Y") . "." . $request["typeExport"]);
	}

	public function CMV_Export($request)
	{
		$cmv_values = collect([]);
		$cmv_dates = ["Visit_date", "Art_StartDate", "Recent_CD4Date"];
		$encryptes = ["Art_Status", "Currnt_Art_Regime", "Most_CD4", "Gender"];
		$modelClassName = "App\\Models\\cmv"; // extend model
		$model = app()->make($modelClassName); // resolves the model from the service container.
		foreach ($request["clinics"] as $clinic) {
			$model->setConnection($this->DB[$clinic]);
			$cmv_values_data = $model
				->whereBetween("Visit_date", [$request["From_date"], $request["To_date"]])
				->leftJoin("patients", "patients.Pid", "=", "cmvs.Pid_cmv")
				->select("cmvs.*", "Date of Birth", "patients.Agey", "patients.Agem", "patients.Gender", "patients.FuchiaID", "patients.Pid", "patients.Main Risk", "patients.Sub Risk")
				->get();
			$cmv_values = $cmv_values->merge($cmv_values_data);
		}

		foreach ($cmv_values as $key => $cmv_value) {
			$cmv_value["Pid"] = $cmv_value["Pid_cmv"];

			if ($cmv_value["Pid_cmv"] == null) {
				$cmv_value["FuchiaID"] = $cmv_value["FuchiaID_cmv"];
				$cmv_value["Gender"] = $cmv_value["Sex"];

				$cmv_value["Register Agey"] = $cmv_value["Agey_2"];


				foreach ($cmv_dates as $cmv_date) {

					if ($cmv_value[$cmv_date] != null) {
						$carbonDate = Carbon::createFromFormat("Y-m-d", $cmv_value[$cmv_date]);
						$carbonDate = Carbon::createFromFormat("d-m-Y", $carbonDate->format("d-m-Y"));
						$cmv_value[$cmv_date] = Date::dateTimeToExcel($carbonDate->startOfDay());
					}
				}
				foreach ($encryptes as $encrypte) {
					$cmv_value[$encrypte] = Crypt::decrypt_light($cmv_value[$encrypte], "General");
					$cmv_value[$encrypte] = Crypt::codeBook($cmv_value[$encrypte], "encode");
				}
			} else {


				$cmv_value = Export_age::Export_general($cmv_value, $cmv_value["Visit_date"], $cmv_value["Date of Birth"], $cmv_value);
				if ($cmv_value["Date of Birth"] == null) {
					$cmv_value = ExportHelper::NoCofidential($cmv_value, $cmv_value["Visit_date"]);
				}
				foreach ($cmv_dates as $cmv_date) {
					if ($cmv_value[$cmv_date] != null) {
						$carbonDate = Carbon::createFromFormat("Y-m-d", $cmv_value[$cmv_date]);
						$carbonDate = Carbon::createFromFormat("d-m-Y", $carbonDate->format("d-m-Y"));
						$cmv_value[$cmv_date] = Date::dateTimeToExcel($carbonDate->startOfDay());
					}
				}
				foreach ($encryptes as $encrypte) {
					$cmv_value[$encrypte] = Crypt::decrypt_light($cmv_value[$encrypte], "General");
					$cmv_value[$encrypte] = Crypt::codeBook($cmv_value[$encrypte], "encode");
				}
			}
		}
		return Excel::download(new CMVExport($cmv_values), "CMV_Export-" . date("d-m-Y") . "." . $request["typeExport"]);
	}

	public function NCD_Export($request)
	{
		$table_name = null;
		$act_table = null;
		$test_date = null;
		$ncd_values = collect([]);
		switch ($request["other"]) {
			case "Register":
				$table_name = "ncd_pt_register";
				$act_table = "ncd_pt_registers";
				$test_date = "Reg_Date";
				$encrypted_columns = ["Gender"];
				$ncd_dates = ["Reg_Date", "1stBP_date", "1st_DiagDate", "1st_RBS_date", "2ndBP_date", "2nd_DiagDate", "2nd_RBS_date", "3rdBP_date", "death_date"];
				break;
			case "Follow_Up":
				$table_name = "ncdFollowup";
				$act_table = "ncd_followups";
				$test_date = "Visit_date";
				$encrypted_columns = ["Gender"];
				$ncd_dates = ["Visit_date", "Next_Appointment", "FBS_test_date", "2HPP_test_date", "Lab_res_Date", "death_date", "Reg_Date"];
				break;
		}
		if ($table_name != null && $act_table != null) {
			$modelClassName = "App\\Models\\" . $table_name; // extend model
			$model = app()->make($modelClassName); // resolves the model from the service container.
			foreach ($request["clinics"] as $clinic) {

				$model->setConnection($this->DB[$clinic]);
				$ncd_values_data = $model
					->whereBetween($test_date, [$request["From_date"], $request["To_date"]])
					->leftJoin("patients", "patients.Pid", "=", $act_table . ".Pid")
					->when($request["other"] == "Follow_Up", function ($query) {
						return $query->leftJoin("ncd_pt_registers", "ncd_pt_registers.Pid", "=", "ncd_followups.Pid");
					})
					->select($act_table . ".*", "Date of Birth", "patients.Agey", "patients.Agem", "patients.Gender", "patients.FuchiaID", "patients.Main Risk", "patients.Sub Risk")
					->when($request["other"] == "Follow_Up", function ($query) {
						return $query->addSelect("ncd_pt_registers.visit_Age");
					})
					->get();
				$ncd_values = $ncd_values->merge($ncd_values_data);
			}
		} else {
			abort(404);
		}
		foreach ($ncd_values as $key => $ncd_value) {


			// this code is to caputer what is wrong in data.
			//if ($ncd_value["Pid"] == "7118005845") {
			// var_dump($ncd_value);
			//}
			//$ncd_value = Export_age::Export_general($ncd_value, $ncd_value[$test_date], $ncd_value["Date of Birth"], $ncd_value);
			//if ($ncd_value["Pid"] == "7118005845") {
			//  dd($ncd_value);
			// }

			// to avoid no id with null 
			if ($ncd_value["Date of Birth"] == null || $ncd_value["Pid"] == null) {
				$ncd_value = ExportHelper::NoCofidential($ncd_value, $ncd_value[$test_date]);
			} else {
				$ncd_value = Export_age::Export_general($ncd_value, $ncd_value[$test_date], $ncd_value["Date of Birth"], $ncd_value);
			}

			foreach ($ncd_dates as $ncd_date) {
				if ($ncd_value[$ncd_date] !== null) {
					$carbonDate = Carbon::createFromFormat("Y-m-d", $ncd_value[$ncd_date]);
					$carbonDate = Carbon::createFromFormat("d-m-Y", $carbonDate->format("d-m-Y"));
					$ncd_value[$ncd_date] = Date::dateTimeToExcel($carbonDate->startOfDay());
				}
			}
			foreach ($encrypted_columns as $encrypte) {
				$ncd_value[$encrypte] = Crypt::decrypt_light($ncd_value[$encrypte], "General");
			}
		}
		return Excel::download(new NCDExport($ncd_values, $request["other"]), "NCD_" . $request["other"] . "-" . date("d-m-Y") . "." . $request["typeExport"]);
	}

	public function TB_Export($request)
	{
		$target_id = null;
		$table_name = null;
		$act_table = null;
		$export_name = null;
		$patient_vdate = null;
		$tb_values = collect([]);
		switch ($request["road"]) {
			case "8":
				$target_id = "Pid_TB03";
				$table_name = "tb_registerO3";
				$act_table = "tb_register_o3_s";
				$export_name = "TB03_Register";
				$patient_vdate = "TreDate_TB03";
				$date_values = [
					"TreDate_TB03",
					"ART_start_TB03",
					"CPT_start_TB03",
					"Intial_RegimenDate_TB03",
					"TrementOut_Date_TB03",
					"EstimentOut_Date_TB03"
				];
				break;
			case "9":
				$target_id = "Pid_preTB";
				$table_name = "preTB";
				$act_table = "pre_t_b_s";
				$export_name = "Pre_TB";
				$patient_vdate = "TBscreenDate_preTB";
				$date_values = [
					"VisitDate_preTB",
					"NextVDate_preTB",
					"TBscreenDate_preTB",
					"HTCDate_preTB",
					"AFBDate_preTB",
					"GeneXpertDate_preTB",
					"CXRDate_preTB"
				];
				break;
			case "10":
				$target_id = "Pid_iptTB";
				$table_name = "Tbipt";
				$act_table = "tbipts";
				$export_name = "IPT";
				$patient_vdate = "IPT_regDate";
				$date_values = ["IPT_regDate", "IPT_startDate", "IPT_disconDate"];
				break;
		}

		if ($table_name != null && $export_name != null && $act_table != null) {
			$encryptes = ["Main Risk", "Sub Risk", "Gender"];
			$modelClassName = "App\\Models\\" . $table_name; // extend model
			$model = app()->make($modelClassName); // resolves the model from the service container.
			foreach ($request["clinics"] as $clinic) {
				$model->setConnection($this->DB[$clinic]);
				$tb_values_data = $model
					->whereBetween($patient_vdate, [$request["From_date"], $request["To_date"]])
					->leftJoin("patients", "patients.Pid", "=", $act_table . '.' . $target_id)
					->select(
						$act_table . ".*",
						"Date of Birth",
						"patients.Agey",
						"patients.Agem",
						"patients.Gender",
						"patients.FuchiaID",
						"patients.Pid",
						"patients.Main Risk",
						"patients.Sub Risk"
					)
					->get();
				$tb_values = $tb_values->merge($tb_values_data);
			}

			foreach ($tb_values as $tb_value) {
				$tb_value["Pid"] = $tb_value[$target_id];
				$tb_value = Export_age::Export_general($tb_value, $tb_value[$patient_vdate], $tb_value["Date of Birth"], $tb_value);
				if ($tb_value["Date of Birth"] == null) {
					$tb_value = ExportHelper::NoCofidential($tb_value, $tb_value[$patient_vdate]);
				}
				foreach ($encryptes as $key => $encrypte) {
					$tb_value[$encrypte] = Crypt::decrypt_light($tb_value[$encrypte], "General");
					if ($request["road"] == "8" && $encrypte == "Gender") {
						if ($tb_value[$encrypte] == "Male") {
							$tb_value[$encrypte] = "M";
						} else {
							$tb_value[$encrypte] = "F";
						}
					}
				}
				foreach ($date_values as $date_value) {
					if ($tb_value[$date_value] != null) {
						$carbonDate = Carbon::createFromFormat("Y-m-d", $tb_value[$date_value]);
						$carbonDate = Carbon::createFromFormat("d-m-Y", $carbonDate->format("d-m-Y"));
						$tb_value[$date_value] = Date::dateTimeToExcel($carbonDate->startOfDay());
					}
				}
			}
		}
		return Excel::download(new TBExport($tb_values, $export_name), $export_name . "-" . date("d-m-Y") . "." . $request["typeExport"]);
	}

	// public function MentalScreen($request)
	// {
	// 	$table_name = null;
	// 	$act_table = null;
	// 	$export_name = null;
	// 	$mental_values = collect([]);
	// 	switch ($request["other"]) {
	// 		case "Screen":
	// 			$table_name = "Mental_Health";
	// 			$act_table = "mental__healths";
	// 			$export_name = "Screening";
	// 			$visitDate = "Counselling_Date";
	// 			$encrypted_columns = ["Main_Risk", "Sub_Risk", "Gender", "Final_Result"];

	// 			$mentalDate = ["Counselling_Date"];
	// 			break;
	// 		case "Register":
	// 			$table_name = "mentalRegister";
	// 			$act_table = "mental_registers";
	// 			$export_name = "Register";
	// 			$visitDate = "Reg_date";
	// 			//$encrypted_columns = ["Main Risk", "Sub Risk", "Gender","Final_Result"];
	// 			$encrypted_columns = ["Main_Risk", "Sub_Risk", "Gender", "Final_Result"];
	// 			$mentalDate = ["Reg_date", "Next_meetdate"];
	// 			break;
	// 		case "FollowUp":
	// 			$table_name = "mentalFollow";
	// 			$act_table = "mental_follows";
	// 			$export_name = "Followup";
	// 			$visitDate = "Visit_date";
	// 			$mentalDate = ["Visit_date", "Md_nextFollowDate", "Csl_nextFollowDate"];
	// 			//$encrypted_columns = ["Main Risk","Sub Risk","Gender","Former Risk","Final_Result",];
	// 			$encrypted_columns = ["Main_Risk", "Sub_Risk", "Gender", "Final_Result"];

	// 			break;
	// 	}

	// 	if ($table_name != null && $act_table != null) {
	// 		foreach ($this->DB as $connectionName) {
	// 			$indexExists = DB::connection($connectionName)
	// 				->select("SHOW INDEX FROM labs WHERE Key_name = 'idx_labs_cid_id'");

	// 			if (empty($indexExists)) {
	// 				DB::connection($connectionName)->statement("CREATE INDEX idx_labs_cid_id ON labs (CID, id)");
	// 			}

	// 			$indexExistsVdate = DB::connection($connectionName)
	// 				->select("SHOW INDEX FROM labs WHERE Key_name = 'idx_labs_cid_vdate'");

	// 			if (empty($indexExistsVdate)) {
	// 				DB::connection($connectionName)->statement("CREATE INDEX idx_labs_cid_vdate ON labs (CID, vdate)");
	// 			}
	// 		}


	// 		$modelClassName = "App\\Models\\" . $table_name;
	// 		$modelInstance = app()->make($modelClassName); // Base model to clone later

	// 		$mental_values = collect();

	// 		// ✅ Loop through clinic connections to fetch data
	// 		foreach ($request["clinics"] as $clinic) {
	// 			$model = clone $modelInstance;
	// 			$model->setConnection($this->DB[$clinic]);

	// 			// Get latest lab per CID using subquery
	// 			$latestLabSub = DB::connection($this->DB[$clinic])
	// 				->table('labs as l1')
	// 				->select('l1.id', 'l1.CID', 'l1.Final_Result', 'l1.vdate')
	// 				->whereRaw('l1.id = (
	// 			    SELECT MAX(l2.id)
	// 			    FROM labs as l2
	// 			    WHERE l2.CID = l1.CID
	// 			)');

	// 			// Main query
	// 				$mental_Data = $model
	// 					->whereBetween($act_table . '.' . $visitDate, [$request["From_date"], $request["To_date"]])
	// 					->leftJoin("patients", "patients.Pid", "=", $act_table . ".Pid")
	// 					->when(in_array($request["other"], ["Screen", "Register", "FollowUp"]), function ($query) use ($act_table, $latestLabSub) {
	// 						return $query->leftJoinSub($latestLabSub, 'latest_labs', function ($join) use ($act_table) {
	// 							$join->on('latest_labs.CID', '=', $act_table . '.Pid');
	// 						});
	// 					})
	// 					->select(
	// 						$act_table . ".*",
	// 					DB::raw("`patients`.`Date of Birth` as `Date_of_Birth`"),
	// 					"patients.Agey",
	// 					"patients.Agem",
	// 					"patients.Gender",
	// 					"patients.FuchiaID",
	// 					"patients.PrEPCode",
	// 					DB::raw("`patients`.`Main Risk` as `Main_Risk`"),
	// 					DB::raw("`patients`.`Sub Risk` as `Sub_Risk`"),
	// 					DB::raw("`patients`.`Risk Log` as `Risk_Log`"),
	// 					DB::raw("`patients`.`Former Risk` as `Former_Risk`"),
	// 					DB::raw("`patients`.`Risk Change_Date` as `Risk_Change_Date`"),
	// 					$act_table . ".created_at",
	// 					$act_table . ".updated_at"
	// 				)
	// 				->when(in_array($request["other"], ["Screen", "Register", "FollowUp"]), function ($query) {
	// 					return $query->addSelect("latest_labs.Final_Result");
	// 					})
	// 					->get();

	// 				$mental_values = $mental_values->merge($mental_Data);
	// 			}
	// 			// Pick the latest patient details per Pid across selected clinics, then re-apply to each row
	// 			$latestPatients = $mental_values
	// 				->groupBy('Pid')
	// 				->map(function ($rows) {
	// 					return $rows->sortByDesc(function ($row) {
	// 						$updated = isset($row['updated_at']) ? strtotime($row['updated_at']) : 0;
	// 						$created = isset($row['created_at']) ? strtotime($row['created_at']) : 0;
	// 						$reg = isset($row['Reg_date']) ? strtotime($row['Reg_date']) : 0;
	// 						return [$updated, $created, $reg];
	// 					})->first();
	// 				});

	// 			$mental_values = $mental_values->map(function ($row) use ($latestPatients) {
	// 				$pid = $row['Pid'] ?? null;
	// 				if ($pid !== null && isset($latestPatients[$pid])) {
	// 					$latest = $latestPatients[$pid];
	// 					foreach ([
	// 						'Date of Birth' => 'Date_of_Birth',
	// 						'Agey' => 'Agey',
	// 						'Agem' => 'Agem',
	// 						'Gender' => 'Gender',
	// 						'FuchiaID' => 'FuchiaID',
	// 						'PrEPCode' => 'PrEPCode',
	// 						'Main Risk' => 'Main_Risk',
	// 						'Sub Risk' => 'Sub_Risk',
	// 						'Risk Log' => 'Risk_Log',
	// 						'Former Risk' => 'Former_Risk',
	// 						'Risk Change_Date' => 'Risk_Change_Date',
	// 					] as $source => $target) {
	// 						if (isset($latest[$target])) {
	// 							$row[$target] = $latest[$target];
	// 						} elseif (isset($latest[$source])) {
	// 							$row[$target] = $latest[$source];
	// 						}
	// 					}
	// 				}
	// 				return $row;
	// 			});

	// 			foreach ($mental_values as $key => $mental_value) {
	// 				//PHQ4 Q1 Q2 Q3 GAD 7, PHQ9 Scoring 

	// 				if ($mental_value["Q1_Q2"] == "") {
	// 					$mental_value["Q1_Q2"] = "";
	// 			} elseif ($mental_value["Q1_Q2"] == 0) {
	// 				$mental_value["Q1_Q2"] = "0";
	// 			}

	// 			if ($mental_value["Q3_Q4"] == "") {
	// 				$mental_value["Q3_Q4"] = "";
	// 			} elseif ($mental_value["Q3_Q4"] == 0) {
	// 				$mental_value["Q3_Q4"] = "0";
	// 			}

	// 			if ($mental_value["gad7_amount"] == "") {
	// 				$mental_value["gad7_amount"] = "";
	// 			} elseif ($mental_value["gad7_amount"] == 0) {
	// 				$mental_value["gad7_amount"] = "0";
	// 			}

	// 			if ($mental_value["PHQ9_amount"] == "") {
	// 				$mental_value["PHQ9_amount"] = "";
	// 			} elseif ($mental_value["PHQ9_amount"] == 0) {
	// 				$mental_value["PHQ9_amount"] = "0";
	// 			}


	// 			$mental_value = Export_age::Export_general($mental_value, $mental_value[$visitDate], $mental_value["Date of Birth"], $mental_value);
	// 			if ($mental_value["Date of Birth"] == null) {
	// 				$mental_value = ExportHelper::NoCofidential($mental_value, $mental_value[$visitDate]);
	// 			}
	// 			if ($mental_value["Hiv_status"] != null) {
	// 				//$mental_value["Final_Result"] =Crypt::codeBook($mental_value[$encrypte], "encode");
	// 				$mental_value["Hiv_status"] = Crypt::codeBook($mental_value["Hiv_status"], "encode");
	// 			}

	// 			$carbonDate = Carbon::createFromFormat('Y-m-d', $mental_value[$visitDate]);
	// 			$carbonDate = Carbon::createFromFormat('d-m-Y', $carbonDate->format('d-m-Y'));
	// 			$vdate = new DateTime($carbonDate);
	// 			if ($mental_value["Risk_Log"] != null) {
	// 				$forRiskCheck[1]['Pid'] = $mental_value['Pid'];
	// 				$forRiskCheck[1]['Risk Log'] = $mental_value['Risk_Log'];
	// 				if (!array_key_exists($mental_value['Pid'], $this->final_log) && $mental_value['Risk_Log'] != null) {
	// 					$this->final_risklog = RefillRisk::FillRisk($forRiskCheck);
	// 					$this->final_log[$mental_value['Pid']] = $this->final_risklog;
	// 				}
	// 				if (array_key_exists($mental_value['Pid'], $this->final_log)) {
	// 					foreach (array_reverse($this->final_log[$mental_value['Pid']][$mental_value['Pid']]) as $date => $data) {
	// 						if (strlen($date) == 10) {
	// 							$riskChangeDate = new DateTime($date);
	// 							if ($vdate < $riskChangeDate) {
	// 								$mental_value['Main_Risk'] = Crypt::encrypt_light($data['Old Risk'], 'General');
	// 								$mental_value['Sub_Risk'] = Crypt::encrypt_light($data['Old Sub Risk'], 'General');
	// 							}
	// 						}
	// 					}
	// 				}
	// 			} elseif ($mental_value['Risk_Change_Date'] != null && $mental_value['Former_Risk'] != null && $mental_value['Former_Risk'] != "731") {
	// 				$riskChangeDate = Carbon::createFromFormat('Y-m-d', $mental_value['Risk_Change_Date']);
	// 				$riskChangeDate = new DateTime(Carbon::createFromFormat('d-m-Y', $riskChangeDate->format('d-m-Y')));
	// 				if ($vdate < $riskChangeDate) {
	// 					$mental_value['Main_Risk'] = $mental_value['Former_Risk'];
	// 					$mental_value['Sub_Risk'] = '';
	// 				}
	// 			}

	// 			foreach ($encrypted_columns as $key => $encrypte) {
	// 				$mental_value[$encrypte] = Crypt::decrypt_light($mental_value[$encrypte], "General");
	// 				if (($encrypte == "Main_Risk" || $encrypte == "Sub_Risk") && $mental_value[$encrypte] == "-") {
	// 					$mental_value[$encrypte] = null;
	// 				}


	// 				// For lab'result HIV pos or neg

	// 				if ($encrypte == "Final_Result" && $mental_value[$encrypte] == null) {
	// 					//$mental_value["Final_Result"] =Crypt::codeBook($mental_value[$encrypte], "encode");
	// 					$mental_value[$encrypte] = "Unknown";
	// 				}
	// 				$mental_value[$encrypte] = Crypt::codeBook($mental_value[$encrypte], "encode");
	// 			}
	// 			foreach ($mentalDate as $column) {
	// 				if ($mental_value[$column] != null) {
	// 					$carbonDate = Carbon::createFromFormat("Y-m-d", $mental_value[$column]);
	// 					$carbonDate = Carbon::createFromFormat("d-m-Y", $carbonDate->format("d-m-Y"));
	// 					$mental_value[$column] = Date::dateTimeToExcel($carbonDate->startOfDay());
	// 				}
	// 			}
	// 		}
	// 	}
	// 	//log::info($mental_values);
	// 	return Excel::download(new MentalExport($mental_values, $export_name), 'Mental Health-' . $export_name . "-" . date("d-m-Y") . "." . $request["typeExport"]);
	// }

	public function MentalScreen($request)
{
    $table_name = null;
    $act_table = null;
    $export_name = null;
    $visitDate = null;
    $encrypted_columns = [];
    $mentalDate = [];

    switch ($request["other"]) {
        case "Screen":
            $table_name = "Mental_Health";
            $act_table = "mental__healths";
            $export_name = "Screening";
            $visitDate = "Counselling_Date";
            $encrypted_columns = ["Main_Risk", "Sub_Risk", "Gender", "Final_Result"];
            $mentalDate = ["Counselling_Date"];
            break;

        case "Register":
            $table_name = "mentalRegister";
            $act_table = "mental_registers";
            $export_name = "Register";
            $visitDate = "Reg_date";
            $encrypted_columns = ["Main_Risk", "Sub_Risk", "Gender", "Final_Result"];
            $mentalDate = ["Reg_date", "Next_meetdate"];
            break;

        case "FollowUp":
            $table_name = "mentalFollow";
            $act_table = "mental_follows";
            $export_name = "Followup";
            $visitDate = "Visit_date";
            $encrypted_columns = ["Main_Risk", "Sub_Risk", "Gender", "Final_Result"];
            $mentalDate = ["Visit_date", "Md_nextFollowDate", "Csl_nextFollowDate"];
            break;
    }

    if (!$table_name || !$act_table) {
        abort(400, "Invalid Mental export request");
    }

    // Default date range when not supplied (e.g., pre-generate all data)
    if (!$request->filled("From_date") || !$request->filled("To_date")) {
        $request["From_date"] = "2000-01-01";
        $request["To_date"] = Carbon::now()->format("Y-m-d");
    }

    $modelClassName = "App\\Models\\" . $table_name;
    $modelInstance = app()->make($modelClassName);

    $mental_values = collect();

    /**
     * ======================================================
     * CLINIC LOOP (ISOLATED)
     * ======================================================
     */
    foreach ($request["clinics"] as $clinic) {

        $connection = $this->DB[$clinic];
        $model = clone $modelInstance;
        $model->setConnection($connection);

        /**
         * Latest lab per CID (clinic-local)
         */
        $latestLabSub = DB::connection($connection)
            ->table('labs as l1')
            ->select('l1.CID', 'l1.Final_Result', 'l1.vdate')
            ->whereRaw('l1.id = (
                SELECT MAX(l2.id)
                FROM labs l2
                WHERE l2.CID = l1.CID
            )');

        /**
         * MAIN QUERY (clinic-local)
         */
        $clinicData = $model
            ->whereBetween($act_table . '.' . $visitDate, [$request["From_date"], $request["To_date"]])
            ->leftJoin("patients", "patients.Pid", "=", $act_table . ".Pid")
            ->leftJoinSub($latestLabSub, 'latest_labs', function ($join) use ($act_table) {
                $join->on('latest_labs.CID', '=', $act_table . '.Pid');
            })
            ->select(
                DB::raw("'{$connection}' as clinic_source"),
                $act_table . ".*",
                DB::raw("`patients`.`Date of Birth` as Date_of_Birth"),
                "patients.Agey",
                "patients.Agem",
                "patients.Gender",
                "patients.FuchiaID",
                "patients.PrEPCode",
                DB::raw("`patients`.`Main Risk` as Main_Risk"),
                DB::raw("`patients`.`Sub Risk` as Sub_Risk"),
                DB::raw("`patients`.`Risk Log` as Risk_Log"),
                DB::raw("`patients`.`Former Risk` as Former_Risk"),
                DB::raw("`patients`.`Risk Change_Date` as Risk_Change_Date"),
                "latest_labs.Final_Result",
                $act_table . ".created_at",
                $act_table . ".updated_at"
            )
            ->get();

        /**
         * ======================================================
         * LATEST PATIENT SNAPSHOT (PER CLINIC ONLY)
         * ======================================================
         */
        $latestPatients = $clinicData
            ->groupBy('Pid')
            ->map(function ($rows) {
                return $rows->sortByDesc(function ($row) {
                    return max(
                        strtotime($row->updated_at ?? '1970-01-01'),
                        strtotime($row->created_at ?? '1970-01-01')
                    );
                })->first();
            });

        /**
         * APPLY LATEST PATIENT DATA (NO CROSS-CLINIC)
         */
        $clinicData = $clinicData->map(function ($row) use ($latestPatients) {
            $pid = $row->Pid;
            if (isset($latestPatients[$pid])) {
                $latest = $latestPatients[$pid];
                foreach ([
                    'Date_of_Birth',
                    'Agey',
                    'Agem',
                    'Gender',
                    'FuchiaID',
                    'PrEPCode',
                    'Main_Risk',
                    'Sub_Risk',
                    'Risk_Log',
                    'Former_Risk',
                    'Risk_Change_Date'
                ] as $field) {
                    if (isset($latest->$field)) {
                        $row->$field = $latest->$field;
                    }
                }
            }
            return $row;
        });

        $mental_values = $mental_values->merge($clinicData);
    }

    /**
     * ======================================================
     * FINAL TRANSFORMATIONS (SAFE)
     * ======================================================
     */
    foreach ($mental_values as $mental_value) {

        $mental_value = Export_age::Export_general(
            $mental_value,
            $mental_value->$visitDate,
            $mental_value->Date_of_Birth,
            $mental_value
        );

        if ($mental_value->Date_of_Birth == null) {
            $mental_value = ExportHelper::NoCofidential(
                $mental_value,
                $mental_value->$visitDate
            );
        }

        foreach ($encrypted_columns as $col) {
            $mental_value->$col = Crypt::decrypt_light($mental_value->$col, "General");
            if ($mental_value->$col === "-" || $mental_value->$col === null) {
                $mental_value->$col = "Unknown";
            }
            $mental_value->$col = Crypt::codeBook($mental_value->$col, "encode");
        }

        foreach ($mentalDate as $column) {
            if (!empty($mental_value->$column)) {
                $carbon = Carbon::createFromFormat("Y-m-d", $mental_value->$column)
                    ->startOfDay();
                $mental_value->$column = Date::dateTimeToExcel($carbon);
            }
        }
    }

    return Excel::download(
        new MentalExport($mental_values, $export_name),
        'Mental Health-' . $export_name . '-' . date("d-m-Y") . "." . $request["typeExport"]
    );
}

}