<?php

namespace App\Http\Controllers;
//namespace App\Exports;
use Illuminate\Http\Request;
use App\Models\PtConfig;
use App\Models\Patients;
use App\Models\Followup_general;
use App\Models\Dispensing;
use App\Models\tb_registerO3;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use Carbon\Carbon;
use Illuminate\Support\Facades\Schema;
use App\Exports\TB03\TB03_Report;
use App\Exports\TB03\TB08_Report;
use App\Exports\TB03\TB_IPT_Report;
use App\Exports\TB03\TB03_Export;
use Maatwebsite\Excel\Facades\Excel;
use Illuminate\Support\Facades\Crypt;
use Illuminate\Support\Arr;
use Illuminate\Support\Facades\Log;
use App\Exports\Export_age;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

class Tb03Controller extends Controller
{
  public function import(Request $request)
  {
    $request->validate([
      'import_file' => ['required', 'file', 'mimes:csv,txt,xlsx'],
    ]);

    $file = $request->file('import_file');
    $extension = strtolower((string) $file->getClientOriginalExtension());
    $path = $file->getRealPath();

    try {
      [$created, $updated, $skipped] = $this->importTb03FromPath($path, $extension, (string) $request->session()->get('tb.clinic', ''));
      return redirect()
        ->back()
        ->with('status', "TB03 import completed. Added {$created}, updated {$updated}, skipped {$skipped}.");
    } catch (\Throwable $e) {
      Log::error('TB03 import failed', ['message' => $e->getMessage()]);
      return redirect()->back()->withErrors(['import_file' => 'TB03 import failed: ' . $e->getMessage()]);
    }
  }

  public function tb03_View(Request $request)
  {
    $editRecord = null;
    if ($request->filled('edit')) {
      $editRecord = tb_registerO3::findOrFail($request->query('edit'));
    }

    return view('TB.tb03', [
      "report_view" => null,
      'editRecord' => $editRecord,
      'clinics' => (array) config('tb_entry.clinics', []),
    ]);
  }

  public function manage(Request $request)
  {
    $recentCutoff = Carbon::today()->subDays(60)->format('Y-m-d');
    $query = tb_registerO3::query()
      ->orderByDesc('TreDate_TB03')
      ->orderByDesc('id');

    $keyword = trim((string) $request->input('q', ''));
    if ($keyword !== '') {
      $query->where(function ($q) use ($keyword) {
        $like = '%' . $keyword . '%';
        $q->where('Pid_TB03', 'like', $like)
          ->orWhere('TNumber_TB03', 'like', $like)
          ->orWhere('Name_TB03', 'like', $like)
          ->orWhere('FuchiaID_TB03', 'like', $like);
      });
    }

    $pid = trim((string) $request->input('pid', ''));
    if ($pid !== '') {
      $query->where('Pid_TB03', 'like', '%' . $pid . '%');
    }

    $tbNumber = trim((string) $request->input('tb_number', ''));
    if ($tbNumber !== '') {
      $query->where('TNumber_TB03', 'like', '%' . $tbNumber . '%');
    }

    $name = trim((string) $request->input('name', ''));
    if ($name !== '') {
      $query->where('Name_TB03', 'like', '%' . $name . '%');
    }

    $clinicInput = trim((string) $request->input('clinic', ''));
    $clinic = $clinicInput;
    if ($clinic === '') {
      $clinic = (string) $request->session()->get('tb.clinic', '');
    }
    if ($clinic !== '') {
      $query->where('Clinic_code', $this->normalizeTb03Clinic($clinic));
    }

    $dateFrom = trim((string) $request->input('date_from', ''));
    if ($dateFrom !== '') {
      $query->whereDate('TreDate_TB03', '>=', $this->normalizeDateValue($dateFrom));
    }

    $dateTo = trim((string) $request->input('date_to', ''));
    if ($dateTo !== '') {
      $query->whereDate('TreDate_TB03', '<=', $this->normalizeDateValue($dateTo));
    }

    $showAll = (string) $request->input('all', '') === '1';
    $hasSearchFilters = $keyword !== '' || $pid !== '' || $tbNumber !== '' || $name !== '' || $dateFrom !== '' || $dateTo !== '' || $request->filled('clinic');
    $recentOnly = !$showAll && !$hasSearchFilters;
    if ($recentOnly) {
      $query->whereDate('TreDate_TB03', '>=', $recentCutoff);
    }

    $records = $query->paginate(25)->withQueryString();

    return view('TB.tb03_manage', [
      'records' => $records,
      'filters' => [
        'q' => $keyword,
        'pid' => $pid,
        'tb_number' => $tbNumber,
        'name' => $name,
        'clinic' => $clinicInput !== '' ? $clinicInput : $clinic,
        'date_from' => $dateFrom,
        'date_to' => $dateTo,
      ],
      'showAll' => $showAll,
      'recentOnly' => $recentOnly,
      'recentCutoff' => $recentCutoff,
      'clinics' => (array) config('tb_entry.clinics', []),
    ]);
  }

  public function edit(tb_registerO3 $tb03Record)
  {
    return redirect()->route('tb03.create', ['edit' => $tb03Record->id]);
  }

  public function destroy(tb_registerO3 $tb03Record)
  {
    $tb03Record->delete();

    return redirect()
      ->route('tb03.manage')
      ->with('status', 'TB03 record deleted successfully.');
  }

  public function Tb03(Request $request)
  {
    $table = "General";
    $notice = $request->input('notice');
    if ($notice == "Find TB03 patient") {
      $Tb03_general = [];
      $Pid03 = trim((string) $request->input('Pid03', ''));
      $TB03_Id = trim((string) $request->input('TB03_Id', ''));
      if ($Pid03 === '' && $TB03_Id === '') {
        return response()->json(['message' => 'General ID or TB register number is required'], 400);
      }

      $historyQuery = tb_registerO3::query()
        ->where(function ($query) use ($Pid03, $TB03_Id) {
          $hasClause = false;
          if ($Pid03 !== '') {
            $query->where('Pid_TB03', $Pid03);
            $hasClause = true;
          }
          if ($TB03_Id !== '' && $TB03_Id !== "-") {
            $method = $hasClause ? 'orWhere' : 'where';
            $query->{$method}('TNumber_TB03', $TB03_Id);
          }
        });

      $clinic = $request->session()->get('tb.clinic');
      $clinicHistory = clone $historyQuery;
      if ($clinic) {
        $clinicHistory->where('Clinic_code', $clinic);
      }
      $Tb03_general[1] = $clinicHistory->orderBy('TreDate_TB03')->orderBy('id')->get();
      if ($Tb03_general[1]->isEmpty()) {
        $Tb03_general[1] = $historyQuery->orderBy('TreDate_TB03')->orderBy('id')->get();
      }

      $latest = $Tb03_general[1]->last();
      $Tb03_general[0] = [
        'Pid' => $latest->Pid_TB03 ?? $Pid03,
        'Name' => $latest->Name_TB03 ?? '',
        'Name_TB03' => $latest->Name_TB03 ?? '',
        'Township' => $latest->Township_TB03 ?? '',
        'Region' => $latest->State_TB03 ?? '',
        'Agey' => $latest->Age_TB03 ?? '',
        'Agem' => '',
        'Gender' => $latest->Gender ?? '',
        'Clinic Code' => $latest->Clinic_code ?? $request->session()->get('tb.clinic'),
        'Reg Date' => $latest->TreDate_TB03 ?? '',
        'Date of Birth' => '',
      ];

      return response()->json([
        $Tb03_general[0],
        $Tb03_general[1]->values(),
      ]);
    } else if ($notice == "TB_03 Register") {
      $clinicCode = $this->normalizeTb03Clinic((string) ($request->input("Clinic Code") ?: $request->session()->get('tb.clinic', '')));
      $pid = trim((string) $request->input("TB03_pid", ''));
      if (!$this->isGeneralIdFormatValid($pid)) {
        return response()->json(['message' => 'General ID must be 10, 11, or 12 digits.'], 422);
      }
      if (!$this->isGeneralIdPrefixValidForClinic($pid, $clinicCode)) {
        return response()->json(['message' => $this->tb03GeneralIdClinicValidationMessage($pid, $clinicCode)], 422);
      }

      tb_registerO3::create([
        'Clinic_code' => $clinicCode,
        'Pid_TB03' => $request->input("TB03_pid"),
        'Name_TB03' => $request->input("TB03_name"),
        'FuchiaID_TB03' => $request->input("TB03_fuchiaId"),
        'TNumber_TB03' => $request->input("TB03_id"),
        'Age_TB03' => $request->input("TB03_age"),
        'Gender' => $request->input("TB03_gender"),
        'TreDate_TB03' => $request->input("TB03_tremDate"),
        'State_TB03' => $request->input("TB03_state"),
        'Township_TB03' => $request->input("TB03_town"),
        'Nationality_TB03' => $request->input("TB03_nationality"),
        'FaciName_TB03' => $request->input("TB03_facility"),
        'RePariod_TB03' => $request->input("TB03_reportPeriod"),
        'TranferIn_TB03' => $request->input("TB03_transfer"),
        'ReferFrom_TB03' => $request->input("TB03_referFrom"),
        'TypePatient_TB03' => $request->input("TB03_typePatient"),
        'TBsite_TB03' => $request->input("TB03_site"),
        'EPTBsite_TB03' => $request->input("TB03_EPTB"),
        'TreRegimens_TB03' => $request->input("TB03_treRegimens"),
        'Smoke_status_TB03' => $request->input("TB03_smoke"),
        'DMstatue_TB03' => $request->input("TB03_dm"),
        'Hivstatus_TB03' => $request->input("TB03_hiv"),
        'ART_start_TB03' => $request->input("TB03_ART"),
        'CPT_start_TB03' => $request->input("TB03_CPT"),
        'Microscope_Res_TB03' => $request->input("TB03_microscope"),
        'Xpert_Res_TB03' => $request->input("TB03_Xpert"),
        'XRay_Res_TB03' => $request->input("TB03_Xray"),
        'Calture_Res_TB03' => $request->input("TB03_culture"),
        'Counsellor_TB03' => $request->input("TB03_counselor"),
        'BioClinical_TB03' => $request->input("TB03_bactlogical"),
        '2ndMicroscope_Res_TB03' => $request->input("TB03_2ndmicroscope"),
        '2ndXpert_Res_TB03' => $request->input("TB03_2ndXpert"),
        '3rdMicroscope_Res_TB03' => $request->input("TB03_3rdmicroscope"),
        '3rdXpert_Res_TB03' => $request->input("TB03_3rdXpert"),
        '5rdMicroscope_Res_TB03' => $request->input("TB03_5thmicroscope"),
        '5rdXpert_Res_TB03' => $request->input("TB03_5thXpert"),
        'EndTX_Microscope_Res_TB03' => $request->input("TB03_endmicroscope"),
        'EndTX_XRay_Res_TB03' => $request->input("TB03_endXray"),
        'EndTX_Xpert_Res_TB03' => $request->input("TB03_endXpert"),
        'TrementOut_TB03' => $request->input("TB03_outcome"),
        'Intial_RegimenDate_TB03' => $request->input("TB03_IRSD"),
        'TrementOut_Date_TB03' => $request->input("TB03_outDate"),
        'EstimentOut_Date_TB03' => $request->input("TB03_EoutDate"),
        'Remark_TB03' => $request->input("TB03_remark"),
        '2ndCulture_Res_TB03' => $request->input('TB03_culture_m2'),
        '3rdCulture_Res_TB03' => $request->input('TB03_culture_m3'),
        '5rdCulture_Res_TB03' => $request->input('TB03_culture_m5'),
        '1stDst' => $request->input('1st_dst'),

      ]);
      return response()->json(["Register 03 save Successfully"]);
    } else if ($notice == "TB_03 Update") {
      $rid = $request->input("Rid");
      $clinicCode = $this->normalizeTb03Clinic((string) ($request->input("Clinic Code") ?: $request->session()->get('tb.clinic', '')));
      tb_registerO3::where("id", $rid)->update([
        'Clinic_code' => $clinicCode,
        'Pid_TB03' => $request->input("TB03_pid"),
        'Name_TB03' => $request->input("TB03_name"),
        'FuchiaID_TB03' => $request->input("TB03_fuchiaId"),
        'TNumber_TB03' => $request->input("TB03_id"),
        'Age_TB03' => $request->input("TB03_age"),
        'Gender' => $request->input("TB03_gender"),
        'TreDate_TB03' => $request->input("TB03_tremDate"),
        'State_TB03' => $request->input("TB03_state"),
        'Township_TB03' => $request->input("TB03_town"),
        'Nationality_TB03' => $request->input("TB03_nationality"),
        'FaciName_TB03' => $request->input("TB03_facility"),
        'RePariod_TB03' => $request->input("TB03_reportPeriod"),
        'TranferIn_TB03' => $request->input("TB03_transfer"),
        'ReferFrom_TB03' => $request->input("TB03_referFrom"),
        'TypePatient_TB03' => $request->input("TB03_typePatient"),
        'TBsite_TB03' => $request->input("TB03_site"),
        'EPTBsite_TB03' => $request->input("TB03_EPTB"),
        'TreRegimens_TB03' => $request->input("TB03_treRegimens"),
        'Smoke_status_TB03' => $request->input("TB03_smoke"),
        'DMstatue_TB03' => $request->input("TB03_dm"),
        'Hivstatus_TB03' => $request->input("TB03_hiv"),
        'ART_start_TB03' => $request->input("TB03_ART"),
        'CPT_start_TB03' => $request->input("TB03_CPT"),
        'Microscope_Res_TB03' => $request->input("TB03_microscope"),
        'Xpert_Res_TB03' => $request->input("TB03_Xpert"),
        'XRay_Res_TB03' => $request->input("TB03_Xray"),
        'Calture_Res_TB03' => $request->input("TB03_culture"),
        'Counsellor_TB03' => $request->input("TB03_counselor"),
        'BioClinical_TB03' => $request->input("TB03_bactlogical"),
        '2ndMicroscope_Res_TB03' => $request->input("TB03_2ndmicroscope"),
        '2ndXpert_Res_TB03' => $request->input("TB03_2ndXpert"),
        '3rdMicroscope_Res_TB03' => $request->input("TB03_3rdmicroscope"),
        '3rdXpert_Res_TB03' => $request->input("TB03_3rdXpert"),
        '5rdMicroscope_Res_TB03' => $request->input("TB03_5thmicroscope"),
        '5rdXpert_Res_TB03' => $request->input("TB03_5thXpert"),
        'EndTX_Microscope_Res_TB03' => $request->input("TB03_endmicroscope"),
        'EndTX_XRay_Res_TB03' => $request->input("TB03_endXray"),
        'EndTX_Xpert_Res_TB03' => $request->input("TB03_endXpert"),
        'TrementOut_TB03' => $request->input("TB03_outcome"),
        'Intial_RegimenDate_TB03' => $request->input("TB03_IRSD"),
        'TrementOut_Date_TB03' => $request->input("TB03_outDate"),
        'EstimentOut_Date_TB03' => $request->input("TB03_EoutDate"),
        'Remark_TB03' => $request->input("TB03_remark"),
        '2ndCulture_Res_TB03' => $request['TB03_culture_m2'],
        '3rdCulture_Res_TB03' => $request['TB03_culture_m3'],
        '5rdCulture_Res_TB03' => $request['TB03_culture_m5'],
        '1stDst' => $request['1st_dst'],
      ]);
      return response()->json(["Register Update save Successfully"]);
    } else if ($notice == "TB 03 Report" || $notice == "TB 03 Export") {
      $dateForm = date("Y-m-d", strtotime($request["dateFrom"]));
      $dateTo = date("Y-m-d", strtotime($request["dateTo"]));
      $TB03_report_dataes = tb_registerO3::whereBetween("TreDate_TB03", [$dateForm, $dateTo])
        ->with([
          'ptconfig' => function ($query) {
            $query->select("Pid", 'Date of Birth', 'Agey', 'Agem', "Main Risk", "Sub Risk", "Gender", "FuchiaID");
          }
        ])->get()->makeHidden(['created_at', 'updated_at']);

      $TB03_report_date = ["TreDate_TB03", "ART_start_TB03", "CPT_start_TB03", "Intial_RegimenDate_TB03", "TrementOut_Date_TB03", "EstimentOut_Date_TB03"];
      foreach ($TB03_report_dataes as $index => $TB03_report_data) {
        if ($TB03_report_data["ptconfig"] != null) {
          $TB03_report_data = Export_age::Export_general($TB03_report_data["ptconfig"], $TB03_report_data["TreDate_TB03"], $TB03_report_data["ptconfig"]["Date of Birth"], $TB03_report_data);
          //dd($TB03_report_data);
          $TB03_report_data["Gender"] = Crypt::decrypt_light($TB03_report_data["ptconfig"]["Gender"], "General");
          if ($TB03_report_data["Gender"] == "Male") {
            $TB03_report_data["Gender"] = "M";
          } else {
            $TB03_report_data["Gender"] = "F";
          };
        }


        if ($notice != "TB 03 Report") {
          foreach ($TB03_report_date as $column) {
            if ($TB03_report_data[$column] != null) {
              $carbonDate = Carbon::createFromFormat('Y-m-d', $TB03_report_data[$column]);
              $carbonDate = Carbon::createFromFormat('d-m-Y', $carbonDate->format('d-m-Y'));
              $TB03_report_dataes[$index][$column] = Date::dateTimeToExcel($carbonDate->toDateTime());
            }
          }
        }
      }

      if ($notice == "TB 03 Report") {
        $selected_report = $request["TB_03_report"];
        $report_type = $request["TB_03_report_type"];
        $cal_resultes = [];
        if ($selected_report == "TB_O7") {
          if ($report_type == "Report_view") {
            $report_view = new TB03_Report($TB03_report_dataes, $cal_resultes);
            $report_view->calculating_report();
            $report_view->cal_resultes["report_type"] = "Report_view";
            // dd($test->cal_resultes);
            return view('TB.export.tb03_report_07', [
              'cal_resultes' => $report_view->cal_resultes,
            ]);
          } else {

            return Excel::download(new TB03_Report($TB03_report_dataes, $cal_resultes), 'TB03 Report' . date("d-m-Y") . '.xlsx');
          }
        } //07
        else if ($selected_report == "TB_O8") {
          if ($report_type == "Report_view") {
            $report_view = new TB08_Report($TB03_report_dataes, $cal_resultes);
            $report_view->calculate_report_O8();
            $report_view->cal_resultes["report_type"] = "Report_view";

            return view('TB.export.tb03_report_08', [
              'cal_resultes' => $report_view->cal_resultes,
            ]);
          } else {
            return Excel::download(new TB08_Report($TB03_report_dataes, $cal_resultes), 'TB_08 Report' . date("d-m-Y") . '.xlsx');
          }
        } //08
        else {
          if ($report_type == "Report_view") {
            $report_view = new TB_IPT_Report($TB03_report_dataes, $cal_resultes);
            $report_view->calculate_report_IPT();
            $report_view->cal_resultes["report_type"] = "Report_view";
            // dd($test->cal_resultes);
            return view('TB.export.tb03_report_IPT', [
              'cal_resultes' => $report_view->cal_resultes,
            ]);
          } else {
            return Excel::download(new TB_IPT_Report($TB03_report_dataes, $cal_resultes), 'TB_IPT Report' . date("d-m-Y") . '.xlsx');
          }
        } //IPT


      } //TB all Report
      else {

        return Excel::download(new TB03_Export($TB03_report_dataes), 'TB03 Export' . date("d-m-Y") . '.xlsx');
      } //TB all Export

    } else if ($notice == "Remvoe TB03") {
      $delete = tb_registerO3::where('id', $request["id"])->where('Pid_TB03', $request["Pid"])->delete();
      return response()->json($delete);
    }
  }

  public function tb03_goback()
  {
    return redirect()->back();
  }

  public function downloadSync(Request $request)
  {
    $request->validate([
      'since' => ['nullable', 'date'],
    ]);

    $query = tb_registerO3::query()->latest('updated_at');

    if ($request->filled('since')) {
      try {
        $query->where('updated_at', '>=', Carbon::parse($request->input('since')));
      } catch (\Throwable $e) {
        // validation already handles invalid dates
      }
    }

    return response()->json([
      'records' => $query->get()->map(fn (tb_registerO3 $record) => $this->transformForSync($record)),
      'synced_at' => now()->toISOString(),
    ]);
  }

  public function uploadSync(Request $request)
  {
    $payload = $request->validate([
      'records' => ['array'],
      'records.*' => ['array'],
    ]);

    $results = [
      'synced' => [],
      'errors' => [],
    ];

    foreach (($payload['records'] ?? []) as $index => $row) {
      $localUuid = $row['_local_uuid'] ?? $row['local_uuid'] ?? null;
      $serverId = $row['server_id'] ?? $row['id'] ?? $row['Rid'] ?? null;

      try {
        $data = $this->normalizeSyncPayload($row, $request->session()->get('tb.clinic'));
        $record = null;

        if ($serverId) {
          $record = tb_registerO3::find($serverId);
        }

        if (!$record && !empty($data['Pid_TB03']) && !empty($data['TreDate_TB03'])) {
          $record = tb_registerO3::query()
            ->where('Pid_TB03', $data['Pid_TB03'])
            ->where('TreDate_TB03', $data['TreDate_TB03'])
            ->when(!empty($data['Clinic_code']), fn ($query) => $query->where('Clinic_code', $data['Clinic_code']))
            ->first();
        }

        if (
          !$record
          && !$this->isGeneralIdFormatValid((string) ($data['Pid_TB03'] ?? ''))
        ) {
          throw new \RuntimeException('General ID must be 10, 11, or 12 digits.');
        }

        if (
          !$record
          && !$this->isGeneralIdPrefixValidForClinic((string) ($data['Pid_TB03'] ?? ''), (string) ($data['Clinic_code'] ?? ''))
        ) {
          throw new \RuntimeException(
            $this->tb03GeneralIdClinicValidationMessage((string) ($data['Pid_TB03'] ?? ''), (string) ($data['Clinic_code'] ?? ''))
          );
        }

        if ($record) {
          $record->fill($data);
          $record->save();
        } else {
          $record = tb_registerO3::create($data);
        }

        $results['synced'][] = [
          'index' => $index,
          'local_uuid' => $localUuid,
          'server_id' => $record->id,
          'record' => $this->transformForSync($record->fresh()),
        ];
      } catch (\Throwable $e) {
        $results['errors'][] = [
          'index' => $index,
          'local_uuid' => $localUuid,
          'message' => $e->getMessage(),
        ];
      }
    }

    return response()->json($results);
  }

  private function normalizeSyncPayload(array $row, ?string $sessionClinic): array
  {
    $mapping = [
      'Clinic_code' => 'Clinic Code',
      'Pid_TB03' => 'TB03_pid',
      'Name_TB03' => 'TB03_name',
      'FuchiaID_TB03' => 'TB03_fuchiaId',
      'TNumber_TB03' => 'TB03_id',
      'Age_TB03' => 'TB03_age',
      'Gender' => 'TB03_gender',
      'TreDate_TB03' => 'TB03_tremDate',
      'State_TB03' => 'TB03_state',
      'Township_TB03' => 'TB03_town',
      'Nationality_TB03' => 'TB03_nationality',
      'FaciName_TB03' => 'TB03_facility',
      'RePariod_TB03' => 'TB03_reportPeriod',
      'TranferIn_TB03' => 'TB03_transfer',
      'ReferFrom_TB03' => 'TB03_referFrom',
      'TypePatient_TB03' => 'TB03_typePatient',
      'TBsite_TB03' => 'TB03_site',
      'EPTBsite_TB03' => 'TB03_EPTB',
      'TreRegimens_TB03' => 'TB03_treRegimens',
      'Smoke_status_TB03' => 'TB03_smoke',
      'DMstatue_TB03' => 'TB03_dm',
      'Hivstatus_TB03' => 'TB03_hiv',
      'ART_start_TB03' => 'TB03_ART',
      'CPT_start_TB03' => 'TB03_CPT',
      'Microscope_Res_TB03' => 'TB03_microscope',
      'Xpert_Res_TB03' => 'TB03_Xpert',
      'XRay_Res_TB03' => 'TB03_Xray',
      'Calture_Res_TB03' => 'TB03_culture',
      'Counsellor_TB03' => 'TB03_counselor',
      'BioClinical_TB03' => 'TB03_bactlogical',
      '2ndMicroscope_Res_TB03' => 'TB03_2ndmicroscope',
      '2ndXpert_Res_TB03' => 'TB03_2ndXpert',
      '2ndCulture_Res_TB03' => 'TB03_culture_m2',
      '3rdMicroscope_Res_TB03' => 'TB03_3rdmicroscope',
      '3rdXpert_Res_TB03' => 'TB03_3rdXpert',
      '3rdCulture_Res_TB03' => 'TB03_culture_m3',
      '5rdMicroscope_Res_TB03' => 'TB03_5thmicroscope',
      '5rdXpert_Res_TB03' => 'TB03_5thXpert',
      '5rdCulture_Res_TB03' => 'TB03_culture_m5',
      'EndTX_Microscope_Res_TB03' => 'TB03_endmicroscope',
      'EndTX_XRay_Res_TB03' => 'TB03_endXray',
      'EndTX_Xpert_Res_TB03' => 'TB03_endXpert',
      '1stDst' => '1st_dst',
      'TrementOut_TB03' => 'TB03_outcome',
      'Intial_RegimenDate_TB03' => 'TB03_IRSD',
      'TrementOut_Date_TB03' => 'TB03_outDate',
      'EstimentOut_Date_TB03' => 'TB03_EoutDate',
      'Remark_TB03' => 'TB03_remark',
    ];

    $fillable = (new tb_registerO3())->getFillable();
    $data = Arr::only($row, $fillable);

    foreach ($mapping as $column => $input) {
      if (!array_key_exists($column, $data) && array_key_exists($input, $row)) {
        $data[$column] = $row[$input];
      }
    }

    if (empty($data['Clinic_code']) && array_key_exists('TB03_clinic', $row)) {
      $data['Clinic_code'] = $row['TB03_clinic'];
    }

    if (empty($data['Clinic_code']) && $sessionClinic) {
      $data['Clinic_code'] = $sessionClinic;
    }

    if (($data['Gender'] ?? null) === 'Female') {
      $data['Gender'] = 'F';
    } elseif (($data['Gender'] ?? null) === 'Male') {
      $data['Gender'] = 'M';
    }

    foreach ([
      'TreDate_TB03',
      'ART_start_TB03',
      'CPT_start_TB03',
      'Intial_RegimenDate_TB03',
      'TrementOut_Date_TB03',
      'EstimentOut_Date_TB03',
    ] as $dateField) {
      if (!empty($data[$dateField])) {
        $time = strtotime((string) $data[$dateField]);
        $data[$dateField] = $time ? date('Y-m-d', $time) : null;
      } else {
        $data[$dateField] = null;
      }
    }

    return array_filter($data, fn ($value) => $value !== '');
  }

  private function importTb03FromPath(string $path, string $extension, string $sessionClinic = ''): array
  {
    $rows = [];
    if ($extension === 'xlsx') {
      $spreadsheet = IOFactory::load($path);
      $sheet = $spreadsheet->getSheetByName('TB03') ?: $spreadsheet->getSheet(0);
      $rows = $sheet->toArray(null, true, true, true);
    } else {
      $rows = array_map('str_getcsv', file($path));
    }

    if (count($rows) < 2) {
      throw new \RuntimeException('File must include header and at least one data row.');
    }

    $headerRow = $rows[1] ?? null;
    if (!is_array($headerRow)) {
      throw new \RuntimeException('Unable to read header row from TB03 file.');
    }

    $headerMap = [];
    foreach ($headerRow as $col => $value) {
      $normalized = $this->normalizeHeader((string) $value);
      if ($normalized !== '') {
        $headerMap[$col] = $normalized;
      }
    }

    $created = 0;
    $updated = 0;
    $skipped = 0;

    foreach ($rows as $rowIndex => $row) {
      if ($rowIndex === 1) {
        continue;
      }
      if (!is_array($row)) {
        $skipped++;
        continue;
      }

      $isEmpty = true;
      foreach ($row as $cellValue) {
        if (trim((string) $cellValue) !== '') {
          $isEmpty = false;
          break;
        }
      }
      if ($isEmpty) {
        continue;
      }

      $mapped = $this->mapTb03ImportRow($headerMap, $row, $sessionClinic);
      if (empty($mapped['Pid_TB03']) || empty($mapped['TreDate_TB03'])) {
        $skipped++;
        continue;
      }

      $record = null;

      if (!empty($mapped['TNumber_TB03'])) {
        $record = tb_registerO3::query()
          ->where('TNumber_TB03', $mapped['TNumber_TB03'])
          ->when(!empty($mapped['Clinic_code']), fn($q) => $q->where('Clinic_code', $mapped['Clinic_code']))
          ->first();
      }

      if (!$record) {
        $record = tb_registerO3::query()
          ->where('Pid_TB03', $mapped['Pid_TB03'])
          ->where('TreDate_TB03', $mapped['TreDate_TB03'])
          ->when(!empty($mapped['Clinic_code']), fn($q) => $q->where('Clinic_code', $mapped['Clinic_code']))
          ->first();
      }

      if ($record) {
        $record->fill($mapped);
        $record->save();
        $updated++;
      } else {
        tb_registerO3::create($mapped);
        $created++;
      }
    }

    return [$created, $updated, $skipped];
  }

  private function mapTb03ImportRow(array $headerMap, array $row, string $sessionClinic = ''): array
  {
    $fieldMap = [
      'clinic' => 'Clinic_code',
      'state_region_name' => 'State_TB03',
      'township_name' => 'Township_TB03',
      'facility_name' => 'FaciName_TB03',
      'reporting_period' => 'RePariod_TB03',
      'treatment_registar_date' => 'TreDate_TB03',
      'treatment_register_date' => 'TreDate_TB03',
      'general_id' => 'Pid_TB03',
      'nationality' => 'Nationality_TB03',
      'sex' => 'Gender',
      'register_age' => 'Age_TB03',
      'current_age' => null,
      'refered_from' => 'ReferFrom_TB03',
      'type_of_patient_s' => 'TypePatient_TB03',
      'type_of_patients' => 'TypePatient_TB03',
      'type_of_disease' => 'TBsite_TB03',
      'specify_if_ep_site_of_eptb' => 'EPTBsite_TB03',
      'transfer_in' => 'TranferIn_TB03',
      'treatment_regimens' => 'TreRegimens_TB03',
      'treatment_start_date' => 'Intial_RegimenDate_TB03',
      'smoking_status' => 'Smoke_status_TB03',
      'dm_status' => 'DMstatue_TB03',
      'hiv_status' => 'Hivstatus_TB03',
      'cpt_start_date' => 'CPT_start_TB03',
      'art_start_date' => 'ART_start_TB03',
      'microscope_result' => 'Microscope_Res_TB03',
      'x_ray_result' => 'XRay_Res_TB03',
      'xray_result' => 'XRay_Res_TB03',
      'xpert_result' => 'Xpert_Res_TB03',
      'culture_result' => 'Calture_Res_TB03',
      '2nd_month_microscope_result' => '2ndMicroscope_Res_TB03',
      '2nd_month_xpert_result' => '2ndXpert_Res_TB03',
      '2nd_month_culture' => '2ndCulture_Res_TB03',
      '3rd_month_microscope_result' => '3rdMicroscope_Res_TB03',
      '3rd_month_xpert_result' => '3rdXpert_Res_TB03',
      '3rd_month_culture' => '3rdCulture_Res_TB03',
      '5th_month_microscope_result' => '5rdMicroscope_Res_TB03',
      '5th_month_xpert_result' => '5rdXpert_Res_TB03',
      '5th_month_culture' => '5rdCulture_Res_TB03',
      'end_of_tx_microscope_result' => 'EndTX_Microscope_Res_TB03',
      'end_of_tx_x_ray_result' => 'EndTX_XRay_Res_TB03',
      'end_of_tx_xray_result' => 'EndTX_XRay_Res_TB03',
      'end_of_tx_xpert_result' => 'EndTX_Xpert_Res_TB03',
      '1st_line_dst_result' => '1stDst',
      'treatment_outcome' => 'TrementOut_TB03',
      'initial_regimen_started_date' => 'Intial_RegimenDate_TB03',
      'outcome_date' => 'TrementOut_Date_TB03',
      'tb_code' => 'TNumber_TB03',
      'bacteriological_clinical' => 'BioClinical_TB03',
      'consular_name' => 'Counsellor_TB03',
      'remark' => 'Remark_TB03',
      'fuchia_id' => 'FuchiaID_TB03',
    ];

    $payload = [];
    foreach ($row as $col => $raw) {
      if (!isset($headerMap[$col])) {
        continue;
      }
      $sourceKey = $headerMap[$col];
      $target = $fieldMap[$sourceKey] ?? null;
      if (!$target) {
        continue;
      }
      $value = is_string($raw) ? trim($raw) : $raw;
      if ($value === '' || $value === null) {
        continue;
      }
      $payload[$target] = $value;
    }

    if (empty($payload['Clinic_code']) && $sessionClinic !== '') {
      $payload['Clinic_code'] = $sessionClinic;
    }
    if (!empty($payload['Clinic_code'])) {
      $payload['Clinic_code'] = $this->normalizeTb03Clinic((string) $payload['Clinic_code']);
    }

    if (!empty($payload['Gender'])) {
      $gender = strtoupper(trim((string) $payload['Gender']));
      if (in_array($gender, ['MALE', 'M'], true)) {
        $payload['Gender'] = 'M';
      } elseif (in_array($gender, ['FEMALE', 'F'], true)) {
        $payload['Gender'] = 'F';
      }
    }

    foreach ([
      'TreDate_TB03',
      'ART_start_TB03',
      'CPT_start_TB03',
      'Intial_RegimenDate_TB03',
      'TrementOut_Date_TB03',
      'EstimentOut_Date_TB03',
    ] as $dateField) {
      if (array_key_exists($dateField, $payload)) {
        $payload[$dateField] = $this->normalizeImportDate($payload[$dateField]);
      }
    }

    $fillable = (new tb_registerO3())->getFillable();
    return Arr::only($payload, $fillable);
  }

  private function normalizeHeader(string $header): string
  {
    $h = trim(mb_strtolower($header));
    $h = str_replace(["'", '’'], '', $h);
    $h = preg_replace('/[^a-z0-9]+/u', '_', $h);
    return trim((string) $h, '_');
  }

  private function normalizeImportDate($value): ?string
  {
    if ($value === null || $value === '') {
      return null;
    }

    if (is_numeric($value)) {
      try {
        return Carbon::instance(Date::excelToDateTimeObject((float) $value))->format('Y-m-d');
      } catch (\Throwable $e) {
        // fall through
      }
    }

    $raw = trim((string) $value);
    if ($raw === '') {
      return null;
    }

    foreach (['Y-m-d', 'd-m-Y', 'm/d/Y', 'n/j/Y', 'd/m/Y', 'n/j/y'] as $format) {
      $dt = \DateTime::createFromFormat('!' . $format, $raw);
      $errors = \DateTime::getLastErrors();
      if ($dt && ($errors['warning_count'] ?? 0) === 0 && ($errors['error_count'] ?? 0) === 0) {
        return $dt->format('Y-m-d');
      }
    }

    try {
      return Carbon::parse($raw)->format('Y-m-d');
    } catch (\Throwable $e) {
      return null;
    }
  }

  private function normalizeTb03Clinic(string $clinic): string
  {
    $raw = trim($clinic);
    if ($raw === '') {
      return $raw;
    }

    $map = [
      'pb' => 'Paungbyin',
      'paungbyin' => 'Paungbyin',
      'mkn' => 'Myitkyina',
      'myitkyina' => 'Myitkyina',
      'myikyina' => 'Myitkyina',
      'mohnyin' => 'Mohnyin',
      'kl' => 'Kale',
      'kale' => 'Kale',
      'hpyts' => 'Hpayarthonesu',
      'hpayarthonesu' => 'Hpayarthonesu',
      'lahe' => 'Lahe',
      'nanyun' => 'Nanyun',
      'kbc' => 'KBC',
      'htp' => 'Htantabin',
      'htantabin' => 'Htantabin',
    ];

    $key = mb_strtolower($raw);
    return $map[$key] ?? $raw;
  }

  private function clinicGeneralIdPrefixes(): array
  {
    return (array) config('tb_entry.general_id_prefixes', []);
  }

  private function isGeneralIdFormatValid(string $pid): bool
  {
    return preg_match('/^[0-9]{10,12}$/', trim($pid)) === 1;
  }

  private function isGeneralIdPrefixValidForClinic(string $pid, string $clinic): bool
  {
    $pid = trim($pid);
    $clinic = $this->normalizeTb03Clinic($clinic);
    $prefixes = $this->clinicGeneralIdPrefixes();
    if ($pid === '' || $clinic === '' || !array_key_exists($clinic, $prefixes)) {
      return false;
    }

    $prefix = (string) $prefixes[$clinic];
    return $prefix !== '' && str_starts_with($pid, $prefix);
  }

  private function tb03GeneralIdClinicValidationMessage(string $pid, string $clinic): string
  {
    if (!$this->isGeneralIdFormatValid($pid)) {
      return 'General ID must be 10, 11, or 12 digits.';
    }

    $clinic = $this->normalizeTb03Clinic($clinic);
    $prefix = (string) ($this->clinicGeneralIdPrefixes()[$clinic] ?? '');
    if ($clinic === '' || $prefix === '') {
      return 'Clinic is required and must have a configured General ID prefix.';
    }
    return "General ID must start with {$prefix} for clinic {$clinic}.";
  }

  private function normalizeDateValue(string $value): string
  {
    $time = strtotime($value);
    return $time ? date('Y-m-d', $time) : $value;
  }

  private function transformForSync(tb_registerO3 $record): array
  {
    $payload = $record->toArray();
    $payload['server_id'] = $record->id;

    foreach ([
      'TreDate_TB03',
      'ART_start_TB03',
      'CPT_start_TB03',
      'Intial_RegimenDate_TB03',
      'TrementOut_Date_TB03',
      'EstimentOut_Date_TB03',
      'created_at',
      'updated_at',
    ] as $field) {
      if ($record->{$field}) {
        $payload[$field] = $record->{$field} instanceof \DateTimeInterface
          ? $record->{$field}->format(str_ends_with($field, '_at') ? 'c' : 'Y-m-d')
          : (string) $record->{$field};
      }
    }

    return $payload;
  }
}