<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
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\MME_Export\TB\PreTbRecordExport;
use App\Exports\MME_Export\TB\PreTbTb03ConfidExport;
use App\Models\PreTbRecord;
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 = [];
	private const PREVENTION_AE_HIV_STATUS_CACHE_CONNECTION = 'MAM_NAP';
	private const PREVENTION_AE_HIV_STATUS_CACHE_TABLE = 'prevention_ae_hiv_status_cache';
	private const PREVENTION_AE_HIV_STATUS_CACHE_MAX_AGE_HOURS = 12;

	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 "18": // PreTB + TB03 + confidential patient data
				return $this->PreTbTb03Confid_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",
					"tbl_demog_first_visit",
					"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",
				];

				$encrypted_38 = ["oth_GI_sympt", "genital_blisters_Location", "des_size", "presumptive_diag", "tre_remarks", "followup", "clinician", "risk_cal_remark"];
				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] = $this->decryptStringForExport($sti_value[$column]);

				// "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"]);
	}

	private function decryptStringForExport($value)
	{
		if ($value === null || $value === '') {
			return '';
		}

		try {
			return Crypt::decryptString($value);
		} catch (DecryptException $e) {
			return $value;
		}
	}

	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) {
				$connection = $this->DB[$clinic];
				$model->setConnection($connection);
				switch ($request["other"]) {
					case 'log_sheet':
					case 'cbs':
						$preventionQuery = $model
							->whereBetween($act_table . ".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"
							);

						if ($request["other"] === "log_sheet") {
							$duColumns = [
								'Shared_NS',
								'Shared_waterforinj',
								'Othersite_inj',
								'Cxofinj',
								'Polydruguse',
								'Overdose',
								'Increase_freq_dosage',
								'Work_family_impact',
								'Other_impact',
								'Criticised',
								'Detoxification',
								'Problematic_DU',
								'BI_done',
								'Explain_harm_act',
								'Introduce_services',
								'Discuss_redue_risk',
								'Explain_OST_ref',
							];

							if (Schema::connection($connection)->hasTable('prevention_problematic_dus')) {
								$preventionQuery->leftJoin('prevention_problematic_dus as pdu', function ($join) use ($act_table) {
									$join->on('pdu.Pid', '=', $act_table . '.Pid')
										->on('pdu.Visit_Date', '=', $act_table . '.Visit_Date');
								});

								foreach ($duColumns as $duColumn) {
									$preventionQuery->addSelect('pdu.' . $duColumn . ' as du_' . $duColumn);
								}
							} else {
								foreach ($duColumns as $duColumn) {
									$preventionQuery->addSelect(DB::raw('NULL as du_' . $duColumn));
								}
							}
						}

						$prevention_values_data = $preventionQuery->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);
		}

		$aeHivStatusCache = collect([]);
		if ($request["other"] == "log_sheet") {
			$this->refreshPreventionAeHivStatusCacheIfNeeded();
			$aeHivStatusCache = $this->preventionAeHivStatusCacheFor(
				$prevention_values->pluck('Pid')->filter()->unique()->values()->all()
			);
		}

		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"] == "log_sheet") {
				$pid = (string) ($prevention_value["Pid"] ?? '');
				$prevention_value["AE HIV Status"] = $this->resolvePreventionAeHivStatus(
					$prevention_value["HIV Status"] ?? null,
					$aeHivStatusCache->get($pid, collect([]))
				);
			}
			 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"]);
	}

	private function refreshPreventionAeHivStatusCacheIfNeeded(): void
	{
		$this->ensurePreventionAeHivStatusCacheTable();

		$query = DB::connection(self::PREVENTION_AE_HIV_STATUS_CACHE_CONNECTION)
			->table(self::PREVENTION_AE_HIV_STATUS_CACHE_TABLE);
		$count = (clone $query)->count();
		$lastSyncedAt = (clone $query)->max('synced_at');

		if ($count > 0 && $lastSyncedAt && Carbon::parse($lastSyncedAt)->gt(now()->subHours(self::PREVENTION_AE_HIV_STATUS_CACHE_MAX_AGE_HOURS))) {
			return;
		}

		$this->rebuildPreventionAeHivStatusCache();
	}

	private function ensurePreventionAeHivStatusCacheTable(): void
	{
		$schema = Schema::connection(self::PREVENTION_AE_HIV_STATUS_CACHE_CONNECTION);
		if ($schema->hasTable(self::PREVENTION_AE_HIV_STATUS_CACHE_TABLE)) {
			return;
		}

		$schema->create(self::PREVENTION_AE_HIV_STATUS_CACHE_TABLE, function ($table) {
			$table->id();
			$table->string('pid', 50)->index();
			$table->string('source_connection', 50);
			$table->string('source_table', 50);
			$table->unsignedBigInteger('source_id');
			$table->date('visit_date')->nullable()->index();
			$table->string('result_status', 20)->index();
			$table->string('raw_result', 255)->nullable();
			$table->timestamp('source_updated_at')->nullable();
			$table->timestamp('synced_at')->nullable()->index();
			$table->timestamps();
			$table->unique(['source_connection', 'source_table', 'source_id'], 'ae_hiv_source_unique');
		});
	}

	private function rebuildPreventionAeHivStatusCache(): void
	{
		$cache = DB::connection(self::PREVENTION_AE_HIV_STATUS_CACHE_CONNECTION)
			->table(self::PREVENTION_AE_HIV_STATUS_CACHE_TABLE);
		$cache->truncate();

		$rows = [];
		$flushRows = function () use (&$rows) {
			if (empty($rows)) {
				return;
			}
			DB::connection(self::PREVENTION_AE_HIV_STATUS_CACHE_CONNECTION)
				->table(self::PREVENTION_AE_HIV_STATUS_CACHE_TABLE)
				->insert($rows);
			$rows = [];
		};

		foreach ($this->DB as $connection) {
			if (Schema::connection($connection)->hasTable('labs')) {
				DB::connection($connection)
					->table('labs')
					->select('id', 'CID', 'Visit_date', 'vdate', 'Final_Result', 'updated_at')
					->whereNotNull('CID')
					->whereNotNull('Final_Result')
					->orderBy('id')
					->chunkById(1000, function ($labs) use (&$rows, $flushRows, $connection) {
						foreach ($labs as $lab) {
							$rawResult = $this->decryptLightForAeHivStatus($lab->Final_Result);
							$status = $this->normalizeAeHivResultStatus($rawResult);
							if ($status === null) {
								continue;
							}
							$rows[] = $this->preventionAeHivStatusCacheRow(
								$connection,
								'labs',
								$lab->id,
								$lab->CID,
								$lab->vdate ?: $lab->Visit_date,
								$status,
								$rawResult,
								$lab->updated_at
							);
							if (count($rows) >= 1000) {
								$flushRows();
							}
						}
					});
			}

			if (Schema::connection($connection)->hasTable('prevention_c_b_s')) {
				DB::connection($connection)
					->table('prevention_c_b_s')
					->select('id', 'Pid', 'Visit_Date', 'HIV result', 'HIV Sero-Status', 'updated_at')
					->whereNotNull('Pid')
					->where(function ($query) {
						$query->whereNotNull('HIV result')
							->orWhereNotNull('HIV Sero-Status');
					})
					->orderBy('id')
					->chunkById(1000, function ($cbsRows) use (&$rows, $flushRows, $connection) {
						foreach ($cbsRows as $cbs) {
							$rawResult = $this->decryptLightForAeHivStatus($cbs->{'HIV Sero-Status'} ?: $cbs->{'HIV result'});
							$status = $this->normalizeAeHivResultStatus($rawResult);
							if ($status === null) {
								continue;
							}
							$rows[] = $this->preventionAeHivStatusCacheRow(
								$connection,
								'prevention_c_b_s',
								$cbs->id,
								$cbs->Pid,
								$cbs->Visit_Date,
								$status,
								$rawResult,
								$cbs->updated_at
							);
							if (count($rows) >= 1000) {
								$flushRows();
							}
						}
					});
			}
		}

		$flushRows();
	}

	private function preventionAeHivStatusCacheRow($connection, $sourceTable, $sourceId, $pid, $visitDate, $status, $rawResult, $sourceUpdatedAt): array
	{
		$now = now();
		return [
			'pid' => (string) $pid,
			'source_connection' => $connection,
			'source_table' => $sourceTable,
			'source_id' => $sourceId,
			'visit_date' => $visitDate ?: null,
			'result_status' => $status,
			'raw_result' => $rawResult,
			'source_updated_at' => $sourceUpdatedAt,
			'synced_at' => $now,
			'created_at' => $now,
			'updated_at' => $now,
		];
	}

	private function preventionAeHivStatusCacheFor(array $pids)
	{
		$pids = array_values(array_filter(array_map('strval', $pids)));
		if (empty($pids)) {
			return collect([]);
		}

		return DB::connection(self::PREVENTION_AE_HIV_STATUS_CACHE_CONNECTION)
			->table(self::PREVENTION_AE_HIV_STATUS_CACHE_TABLE)
			->whereIn('pid', $pids)
			->get()
			->groupBy('pid');
	}

	private function resolvePreventionAeHivStatus($hivStatus, $cachedRows): ?string
	{
		$status = $this->normalizeAeHivLogsheetStatus($hivStatus);
		if ($status === 'not_needed') {
			return null;
		}

		$hasAnyEvidence = $cachedRows->contains(function ($row) {
			return in_array($row->result_status, ['negative', 'positive'], true);
		});
		$hasNegativeEvidence = $cachedRows->contains(function ($row) {
			return $row->result_status === 'negative' && in_array($row->source_table, ['labs', 'prevention_c_b_s'], true);
		});
		$hasPositiveLabEvidence = $cachedRows->contains(function ($row) {
			return $row->result_status === 'positive' && $row->source_table === 'labs';
		});

		if ($status === 'unknown') {
			return $hasAnyEvidence ? 'Check - HIV result found' : 'OK';
		}
		if ($status === 'known_negative') {
			return $hasNegativeEvidence ? 'OK' : 'Check - no negative result';
		}
		if ($status === 'known_positive') {
			return $hasPositiveLabEvidence ? 'OK' : 'Check - no positive lab result';
		}

		return 'Check - unknown HIV status value';
	}

	private function normalizeAeHivLogsheetStatus($value): ?string
	{
		$value = $this->normalizeAeHivText($value);

		if ($value === '4' || str_contains($value, 'not')) {
			return 'not_needed';
		}
		if ($value === '1' || str_contains($value, 'unknown')) {
			return 'unknown';
		}
		if ($value === '2' || str_contains($value, 'negative')) {
			return 'known_negative';
		}
		if ($value === '3' || str_contains($value, 'positive')) {
			return 'known_positive';
		}

		return null;
	}

	private function normalizeAeHivResultStatus($value): ?string
	{
		$value = $this->normalizeAeHivText($value);
		if ($value === '') {
			return null;
		}
		if (str_contains($value, 'non reactive') || str_contains($value, 'negative') || $value === '2') {
			return 'negative';
		}
		if (str_contains($value, 'positive') || str_contains($value, 'reactive') || $value === '3') {
			return 'positive';
		}

		return null;
	}

	private function normalizeAeHivText($value): string
	{
		return trim(preg_replace('/\s+/', ' ', str_replace(['-', '_'], ' ', strtolower(trim((string) $value)))));
	}

	private function decryptLightForAeHivStatus($value)
	{
		if ($value === null || $value === '') {
			return $value;
		}

		try {
			return Crypt::decrypt_light($value, 'General');
		} catch (\Throwable $e) {
			return $value;
		}
	}

	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 = "cid";
				$table_name = "PreTbRecord";
				$act_table = "pre_tb_records";
				$export_name = "Pre_TB_records";
				$patient_vdate = "date_of_screening";
				$date_values = ["date_of_screening", "dofnv", "chest_xray_date", "genexpert_date", "truenat_date", "hiv_det_date", "crp_date", "radio_request_date", "sputum_afb_date", "antibiotic_date", "tb_treat_date"];
				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 ($request["road"] == "9") {
			$valueType = strtolower((string) ($request["other"] ?? "codes"));
			if (!in_array($valueType, ["codes", "labels"], true)) {
				$valueType = "codes";
			}

			$preTbValues = collect([]);
			$model = app()->make(PreTbRecord::class);
			foreach ($request["clinics"] as $clinic) {
				$model->setConnection($this->DB[$clinic]);
				$rows = $model
					->whereBetween("date_of_screening", [$request["From_date"], $request["To_date"]])
					->get();
				$tb03Lookup = [];
				$cids = $rows->pluck("cid")->filter()->map(fn($cid) => (string) $cid)->unique()->values();
				if ($cids->isNotEmpty()) {
					try {
						$matches = DB::connection($this->DB[$clinic])
							->table("tb_register_o3_s")
							->whereIn("Pid_TB03", $cids)
							->pluck("Pid_TB03");
						foreach ($matches as $pid) {
							$tb03Lookup[(string) $pid] = true;
						}
					} catch (\Throwable $e) {
						$tb03Lookup = [];
					}
				}
				$rows->each(function ($row) use ($tb03Lookup) {
					$row["tb03_exists"] = isset($tb03Lookup[(string) $row["cid"]]);
				});
				$preTbValues = $preTbValues->merge($rows);
			}

			return Excel::download(
				new PreTbRecordExport($preTbValues, $valueType),
				$export_name . '_' . $valueType . '-' . date("d-m-Y") . "." . $request["typeExport"]
			);
		}

		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 PreTbTb03Confid_Export($request)
	{
		$allRows = collect([]);

		foreach ($request["clinics"] as $clinic) {
			$connection = $this->DB[$clinic];
			$schema = Schema::connection($connection);
			if (!$schema->hasTable("pre_tb_records") || !$schema->hasTable("patients")) {
				continue;
			}

			$hasTb03 = $schema->hasTable("tb_register_o3_s");
			$query = DB::connection($connection)
				->table("pre_tb_records as pre")
				->whereNull("pre.deleted_at")
				->whereBetween("pre.date_of_screening", [$request["From_date"], $request["To_date"]])
				->leftJoin("patients as patient", "patient.Pid", "=", "pre.cid");

			if ($hasTb03) {
				$latestTb03 = DB::connection($connection)
					->table("tb_register_o3_s")
					->selectRaw("Pid_TB03, MAX(id) as latest_id")
					->whereNotNull("Pid_TB03")
					->groupBy("Pid_TB03");
				$query->leftJoinSub($latestTb03, "latest_tb03", function ($join) {
					$join->on("latest_tb03.Pid_TB03", "=", "pre.cid");
				})->leftJoin("tb_register_o3_s as tb03", "tb03.id", "=", "latest_tb03.latest_id");
			}

			$patientColumn = function ($column, $alias) use ($schema) {
				return $schema->hasColumn("patients", $column)
					? DB::raw("`patient`.`{$column}` as `{$alias}`")
					: DB::raw("NULL as `{$alias}`");
			};
			$tbColumn = function ($column, $alias) use ($hasTb03) {
				return $hasTb03
					? DB::raw("`tb03`.`{$column}` as `{$alias}`")
					: DB::raw("NULL as `{$alias}`");
			};

			$rows = $query->select([
				DB::raw("pre.cid as general_id"),
				$patientColumn("Name", "patient_name"),
				$patientColumn("Father", "father_name"),
				$patientColumn("Gender", "sex"),
				$patientColumn("Date of Birth", "dob"),
				$patientColumn("Agey", "stored_register_age_year"),
				$patientColumn("Agem", "stored_register_age_month"),
				$patientColumn("Reg Date", "patient_reg_date"),
				$patientColumn("Township", "township"),
				DB::raw("pre.date_of_screening as screening_date"),
				DB::raw("pre.cough_present as cough"),
				DB::raw("pre.weight_loss_present as weight_loss"),
				DB::raw("pre.hemoptysis_present as hemoptysis"),
				DB::raw("pre.appetite_loss_present as loss_of_appetite"),
				DB::raw("pre.fever_present as fever"),
				DB::raw("pre.night_sweats_present as night_sweats"),
				DB::raw("pre.fatigue_present as fatigue"),
				DB::raw("pre.chest_pain_present as chest_pain"),
				"pre.weight", "pre.height", "pre.bmi", "pre.malnutrition", "pre.smoking",
				DB::raw("pre.DM as dm"),
				"pre.alcohol", "pre.hiv_status", "pre.hiv_tx",
				DB::raw("pre.chest_xray as chest_xray"),
				"pre.chest_xray_date", "pre.md_diagnosis", "pre.radiologist_result",
				"pre.sputum_afb_res", "pre.sputum_afb_date", "pre.genexpert_date", "pre.genexpert_res",
				$tbColumn("TypePatient_TB03", "type_of_patient"),
				$tbColumn("TBsite_TB03", "type_of_disease"),
				$tbColumn("TreDate_TB03", "treatment_start_date"),
				$tbColumn("BioClinical_TB03", "bio_clinical"),
			])->get();

			$rows->each(function ($row) use ($connection) {
				$row->patient_name = $this->decryptConfidentialExportValue($row->patient_name);
				$row->father_name = $this->decryptConfidentialExportValue($row->father_name);
				$row->sex = $this->decryptConfidentialExportValue($row->sex);
				$row->dob = $this->decryptConfidentialExportValue($row->dob);
				$row->township = $this->decryptConfidentialExportValue($row->township);
				$ageInput = [
					"Pid" => $row->general_id,
					"Agey" => $row->stored_register_age_year,
					"Agem" => $row->stored_register_age_month,
					"Reg Date" => $row->patient_reg_date,
				];
				$ages = Export_age::Export_general(
					$ageInput,
					$row->screening_date,
					$row->dob,
					$ageInput
				);
				$row->register_age_year = $ages["Register Agey"] ?? null;
				$row->register_age_month = $ages["Register Agem"] ?? null;
				$row->current_age_year = $ages["Current Agey"] ?? null;
				$row->current_age_month = $ages["Current Agem"] ?? null;
				$row->clinic_site = $connection === "MAM_C1" ? "MAM C" : $connection;
			});

			$allRows = $allRows->merge($rows);
		}

		return Excel::download(
			new PreTbTb03ConfidExport($allRows),
			"PreTB_TB03_Confid-" . date("d-m-Y") . "." . $request["typeExport"]
		);
	}

	private function decryptConfidentialExportValue($value)
	{
		if ($value === null || $value === "") {
			return $value;
		}
		try {
			return Crypt::decryptString($value);
		} catch (\Throwable $e) {
			try {
				return Crypt::decrypt_light($value, "General");
			} catch (\Throwable $fallbackException) {
				return null;
			}
		}
	}

	// 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"]
    );
}

}