cpone_midleware/internal/repository/lab_registration.go

package repository

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"strconv"
	"strings"
	"time"

	mysql "github.com/go-sql-driver/mysql"
)

const labDepartmentModule = "LABORATORIUM"

type LabRegistrationParams struct {
	MedrecID         string
	RegistrationDate time.Time
	DepartmentID     string
	DoctorID         string
	SenderDoctorID   string
	Referrer         string
	PatientType      string
	TariffPeriodID   int64
	CompanyID        string
	InsuranceID      string
	ProviderID       string
	GroupTariffID    int64
	PolicyNumber     string
	EmployeeID       string
	EmployeeName     string
	ClassID          string
	DailyShiftID     *int64
	WorkingDiagnosis string
	Notes            string
	Channel          string
}

type LabRegistrationOutcome struct {
	HTTPStatus int
	Success    bool
	Status     string
	Message    string
	Data       *LabRegistrationData
}

type LabRegistrationData struct {
	Registration      LabRegistration `json:"registration"`
	Transaction       *LabTransaction `json:"transaction"`
	Layanan           []LabService    `json:"layanan"`
	AddedLayananIDs   *[]string       `json:"added_layanan_ids,omitempty"`
	SkippedLayananIDs *[]string       `json:"skipped_layanan_ids,omitempty"`
	Package           *LabPackage     `json:"package,omitempty"`
}

type LabPackage struct {
	PaketDispenserID int64    `json:"PaketDispenserID,omitempty"`
	PaketID          string   `json:"PaketID"`
	Nama             string   `json:"Nama"`
	KunjunganKe      int64    `json:"KunjunganKe,omitempty"`
	AddedLayananIDs  []string `json:"added_layanan_ids,omitempty"`
}

type LabRegistration struct {
	RegID          string  `json:"RegID"`
	MEDRECID       string  `json:"MEDRECID"`
	Tanggal        string  `json:"Tanggal"`
	DepartemenID   string  `json:"DepartemenID"`
	DokterID       *string `json:"DokterID"`
	JenisPasienID  int64   `json:"JenisPasienID"`
	JenisRegID     *int64  `json:"JenisRegID"`
	PeriodeTarifID int64   `json:"PeriodeTarifID"`
	PerusahaanID   *string `json:"PerusahaanID"`
	AsuransiID     *string `json:"AsuransiID"`
	ProviderID     *string `json:"ProviderID"`
	GroupTarifID   *int64  `json:"GroupTarifID"`
	NomerPolis     *string `json:"NomerPolis"`
}

type LabTransaction struct {
	TrxDepartemenID int64   `json:"TrxDepartemenID"`
	TrxID           string  `json:"TrxID"`
	GrandTotal      float64 `json:"GrandTotal"`
}

type LabService struct {
	TrxLayananID       int64   `json:"TrxLayananID"`
	HeaderLayananID    *string `json:"HeaderLayananID"`
	LayananID          *string `json:"LayananID"`
	Nama               *string `json:"Nama"`
	Jumlah             *string `json:"Jumlah"`
	Cito               *string `json:"Cito"`
	Harga              *string `json:"Harga"`
	Diskon             *string `json:"Diskon"`
	TipeDiskon         *string `json:"TipeDiskon"`
	DitanggungPasien   *string `json:"DitanggungPasien"`
	DitanggungPenjamin *string `json:"DitanggungPenjamin"`
}

type ValidationError struct {
	Message string
}

func (e ValidationError) Error() string { return e.Message }

type dbLabRegistration struct {
	RegID          string
	MEDRECID       string
	Tanggal        time.Time
	DepartemenID   string
	DokterID       sql.NullString
	JenisPasienID  int64
	JenisRegID     sql.NullInt64
	PeriodeTarifID int64
	PerusahaanID   sql.NullString
	AsuransiID     sql.NullString
	ProviderID     sql.NullString
	GroupTarifID   sql.NullInt64
	NomerPolis     sql.NullString
}

type dbLabTransaction struct {
	TrxDepartemenID int64
	TrxID           string
}

type dbLabPatient struct {
	MEDRECID     string
	Nama         string
	TanggalLahir sql.NullTime
	Kelamin      sql.NullString
}

func (r MySQLLayananRepository) RegisterLab(ctx context.Context, params LabRegistrationParams) (LabRegistrationOutcome, error) {
	var lastErr error
	for attempt := 0; attempt < 3; attempt++ {
		outcome, err := r.registerLabAttempt(ctx, params)
		if err == nil {
			return outcome, nil
		}

		lastErr = err
		if !isConcurrencyError(err) || ctx.Err() != nil {
			return LabRegistrationOutcome{}, err
		}
	}

	return LabRegistrationOutcome{}, lastErr
}

func (r MySQLLayananRepository) registerLabAttempt(ctx context.Context, params LabRegistrationParams) (outcome LabRegistrationOutcome, err error) {
	tx, err := r.db.BeginTx(ctx, nil)
	if err != nil {
		return LabRegistrationOutcome{}, err
	}

	defer func() {
		if recovered := recover(); recovered != nil {
			_ = tx.Rollback()
			panic(recovered)
		}
		if err != nil {
			_ = tx.Rollback()
		}
	}()

	outcome, err = registerLabInTransaction(ctx, tx, params)
	if err != nil {
		return LabRegistrationOutcome{}, err
	}
	if err = tx.Commit(); err != nil {
		return LabRegistrationOutcome{}, err
	}

	return outcome, nil
}

func registerLabInTransaction(ctx context.Context, tx *sql.Tx, params LabRegistrationParams) (LabRegistrationOutcome, error) {
	departmentActive, err := exists(ctx, tx, `
		SELECT EXISTS(
			SELECT 1 FROM departemen
			WHERE DepartemenID = ? AND NamaModulExternal = ? AND NA = 'N'
		)`, params.DepartmentID, labDepartmentModule)
	if err != nil {
		return LabRegistrationOutcome{}, err
	}
	if !departmentActive {
		return LabRegistrationOutcome{}, ValidationError{Message: "Departemen tujuan bukan departemen laboratorium aktif"}
	}
	if err := validateLabGuarantor(ctx, tx, params); err != nil {
		return LabRegistrationOutcome{}, err
	}

	patient, found, err := findLabPatientForUpdate(ctx, tx, params.MedrecID)
	if err != nil {
		return LabRegistrationOutcome{}, err
	}
	if !found {
		return labOutcome(404, false, "patient_not_found", "Pasien tidak ditemukan", nil), nil
	}

	existing, found, err := findExistingLabRegistrationForUpdate(ctx, tx, patient.MEDRECID, params.DepartmentID, params.RegistrationDate)
	if err != nil {
		return LabRegistrationOutcome{}, err
	}
	if found {
		if !sameLabGuarantor(existing, params) {
			return labOutcome(
				409,
				false,
				"registration_guarantor_conflict",
				"Pasien sudah memiliki registrasi laboratorium pada hari yang sama dengan penjamin berbeda",
				nil,
			), nil
		}
		trx, trxFound, err := findLabTransaction(ctx, tx, existing.RegID, params.DepartmentID)
		if err != nil {
			return LabRegistrationOutcome{}, err
		}
		data, err := buildLabRegistrationData(ctx, tx, existing, optionalLabTransaction(trx, trxFound))
		if err != nil {
			return LabRegistrationOutcome{}, err
		}

		return labOutcome(
			200,
			true,
			"already_registered_visit",
			"Pasien sudah memiliki registrasi laboratorium aktif pada hari dan tujuan yang sama",
			&data,
		), nil
	}

	jakarta, err := time.LoadLocation("Asia/Jakarta")
	if err != nil {
		return LabRegistrationOutcome{}, err
	}
	now := time.Now().In(jakarta)
	nowText := now.Format("2006-01-02 15:04:05")

	senderDoctorID := strings.TrimSpace(params.SenderDoctorID)
	if senderDoctorID == "" && strings.TrimSpace(params.Referrer) != "" {
		senderDoctorID = "Luar"
	}

	regID, err := nextLabRegistrationID(ctx, tx, params.RegistrationDate, params.Channel, nowText)
	if err != nil {
		return LabRegistrationOutcome{}, err
	}
	shiftID, err := resolveDailyShiftID(ctx, tx, params.DailyShiftID, params.RegistrationDate)
	if err != nil {
		return LabRegistrationOutcome{}, err
	}

	var previousVisits int64
	if err := tx.QueryRowContext(ctx, `SELECT COUNT(*) FROM regpas WHERE MEDRECID = ? AND NA = 'N'`, patient.MEDRECID).Scan(&previousVisits); err != nil {
		return LabRegistrationOutcome{}, err
	}

	_, err = tx.ExecContext(ctx, `
		INSERT INTO regpas (
			RegID, MEDRECID, Nama, ALAMATEXTPAS, TELPEXTPAS, TglLahir, JnsKelamin,
			KunjunganKe, Tanggal, JenisPasienID, PeriodeTarifID, PerusahaanID,
			GroupTarifID, AsuransiID, ProviderID, NomerPolis, NIK, NamaKaryawan,
			DepartemenID, Perujuk, JenisRegID,
			DokterID, DokterID2, KelasID, MataUang, Rate, Pulang, BolehPulang, Lunas,
			BatalDaftar, Catatan, ShiftHarianIDDaftar, LoginBuat, TanggalBuat, NA
		) VALUES (?, ?, ?, '', '', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, '3', ?, ?, ?, 'IDR', 1, 'N', 'N', 'N', 'N', ?, ?, ?, ?, 'N')`,
		regID,
		patient.MEDRECID,
		patient.Nama,
		nullTimeValue(patient.TanggalLahir),
		nullStringValue(patient.Kelamin),
		previousVisits+1,
		nowText,
		params.PatientType,
		params.TariffPeriodID,
		params.CompanyID,
		params.GroupTariffID,
		params.InsuranceID,
		params.ProviderID,
		params.PolicyNumber,
		params.EmployeeID,
		params.EmployeeName,
		params.DepartmentID,
		strings.TrimSpace(params.Referrer),
		params.DoctorID,
		senderDoctorID,
		params.ClassID,
		strings.TrimSpace(params.Notes),
		shiftID,
		params.Channel,
		nowText,
	)
	if err != nil {
		return LabRegistrationOutcome{}, err
	}

	result, err := tx.ExecContext(ctx, `
		INSERT INTO trxdepartemen (
			RegID, TrxID, DepartemenID, DokterID, Dokter2, Tanggal, KelasID,
			MataUang, LoginBuat, TanggalBuat, NA
		) VALUES (?, ?, ?, ?, ?, ?, ?, 'IDR', ?, ?, 'N')`,
		regID,
		regID,
		params.DepartmentID,
		params.DoctorID,
		senderDoctorID,
		nowText,
		params.ClassID,
		params.Channel,
		nowText,
	)
	if err != nil {
		return LabRegistrationOutcome{}, err
	}
	trxDepartmentID, err := result.LastInsertId()
	if err != nil {
		return LabRegistrationOutcome{}, err
	}

	if err := upsertLabNominals(ctx, tx, regID, trxDepartmentID, params.DepartmentID); err != nil {
		return LabRegistrationOutcome{}, err
	}
	if err := saveLabDiagnosis(ctx, tx, regID, patient.MEDRECID, strings.TrimSpace(params.WorkingDiagnosis), params.Channel, nowText); err != nil {
		return LabRegistrationOutcome{}, err
	}

	registration, err := findLabRegistrationByID(ctx, tx, regID)
	if err != nil {
		return LabRegistrationOutcome{}, err
	}
	transaction, err := findLabTransactionByID(ctx, tx, trxDepartmentID)
	if err != nil {
		return LabRegistrationOutcome{}, err
	}
	data, err := buildLabRegistrationData(ctx, tx, registration, &transaction)
	if err != nil {
		return LabRegistrationOutcome{}, err
	}

	return labOutcome(201, true, "registered", "Registrasi laboratorium berhasil disimpan", &data), nil
}

func validateLabGuarantor(ctx context.Context, tx *sql.Tx, params LabRegistrationParams) error {
	if params.PatientType == "0" {
		return nil
	}

	active, err := exists(ctx, tx, `
		SELECT EXISTS(
			SELECT 1 FROM jenispasien
			WHERE JenisPasienID = ? AND NA = 'N'
		)`, params.PatientType)
	if err != nil {
		return err
	}
	if !active {
		return ValidationError{Message: "Jenis pasien penjamin tidak ditemukan atau sudah tidak aktif"}
	}

	guarantors := []struct {
		label string
		id    string
	}{
		{label: "Perusahaan", id: params.CompanyID},
		{label: "Asuransi/penanggung", id: params.InsuranceID},
		{label: "Provider", id: params.ProviderID},
	}
	for _, guarantor := range guarantors {
		if guarantor.id == "" {
			continue
		}
		active, err = exists(ctx, tx, `
			SELECT EXISTS(
				SELECT 1 FROM perusahaan
				WHERE PerusahaanID = ? AND NA = 'N'
			)`, guarantor.id)
		if err != nil {
			return err
		}
		if !active {
			return ValidationError{Message: guarantor.label + " tidak ditemukan atau sudah tidak aktif"}
		}
	}

	var tariffPeriodID int64
	err = tx.QueryRowContext(ctx, `
		SELECT pg.PeriodeTarifID
		FROM perusahaan_grouptarif AS pg
		INNER JOIN perusahaan_tarif AS pt
			ON pt.Perusahaan_TarifID = pg.Perusahaan_TarifID
		WHERE pg.GroupTarifID = ?
			AND pt.PerusahaanID = ?
			AND pg.NA = 'N'
			AND DATE(pg.TanggalBerlaku) <= ?
			AND DATE(pg.TanggalBerakhir) >= ?
		LIMIT 1`,
		params.GroupTariffID,
		params.CompanyID,
		params.RegistrationDate.Format("2006-01-02"),
		params.RegistrationDate.Format("2006-01-02"),
	).Scan(&tariffPeriodID)
	if errors.Is(err, sql.ErrNoRows) {
		return ValidationError{Message: "Group tarif tidak aktif, tidak berlaku pada tanggal registrasi, atau bukan milik perusahaan yang dipilih"}
	}
	if err != nil {
		return err
	}
	if tariffPeriodID != params.TariffPeriodID {
		return ValidationError{Message: fmt.Sprintf("Periode tarif harus %d sesuai group tarif yang dipilih", tariffPeriodID)}
	}

	return nil
}

func sameLabGuarantor(existing dbLabRegistration, params LabRegistrationParams) bool {
	return strconv.FormatInt(existing.JenisPasienID, 10) == params.PatientType &&
		existing.PeriodeTarifID == params.TariffPeriodID &&
		nullInt64(existing.GroupTarifID) == params.GroupTariffID &&
		strings.TrimSpace(nullableString(existing.PerusahaanID)) == params.CompanyID &&
		strings.TrimSpace(nullableString(existing.AsuransiID)) == params.InsuranceID &&
		strings.TrimSpace(nullableString(existing.ProviderID)) == params.ProviderID &&
		strings.TrimSpace(nullableString(existing.NomerPolis)) == params.PolicyNumber
}

func nullInt64(value sql.NullInt64) int64 {
	if !value.Valid {
		return 0
	}
	return value.Int64
}

func nullableString(value sql.NullString) string {
	if !value.Valid {
		return ""
	}
	return value.String
}

func findLabRegistrationByID(ctx context.Context, tx *sql.Tx, regID string) (dbLabRegistration, error) {
	var registration dbLabRegistration
	err := tx.QueryRowContext(ctx, `
		SELECT RegID, MEDRECID, Tanggal, DepartemenID, DokterID,
			JenisPasienID, JenisRegID, PeriodeTarifID, PerusahaanID,
			AsuransiID, ProviderID, GroupTarifID, NomerPolis
		FROM regpas WHERE RegID = ? LIMIT 1`, regID).Scan(
		&registration.RegID,
		&registration.MEDRECID,
		&registration.Tanggal,
		&registration.DepartemenID,
		&registration.DokterID,
		&registration.JenisPasienID,
		&registration.JenisRegID,
		&registration.PeriodeTarifID,
		&registration.PerusahaanID,
		&registration.AsuransiID,
		&registration.ProviderID,
		&registration.GroupTarifID,
		&registration.NomerPolis,
	)
	return registration, err
}

func findLabTransactionByID(ctx context.Context, tx *sql.Tx, trxDepartmentID int64) (dbLabTransaction, error) {
	var transaction dbLabTransaction
	err := tx.QueryRowContext(ctx, `
		SELECT TrxDepartemenID, TrxID
		FROM trxdepartemen WHERE TrxDepartemenID = ? LIMIT 1`, trxDepartmentID).Scan(
		&transaction.TrxDepartemenID,
		&transaction.TrxID,
	)
	return transaction, err
}

func findLabPatientForUpdate(ctx context.Context, tx *sql.Tx, medrecID string) (dbLabPatient, bool, error) {
	var patient dbLabPatient
	err := tx.QueryRowContext(ctx, `
		SELECT MEDRECID, Nama, TanggalLahir, Kelamin
		FROM medrec
		WHERE MEDRECID = ? AND NA = 'N'
		LIMIT 1 FOR UPDATE`, medrecID).Scan(
		&patient.MEDRECID,
		&patient.Nama,
		&patient.TanggalLahir,
		&patient.Kelamin,
	)
	if errors.Is(err, sql.ErrNoRows) {
		return dbLabPatient{}, false, nil
	}
	if err != nil {
		return dbLabPatient{}, false, err
	}
	return patient, true, nil
}

func findExistingLabRegistrationForUpdate(
	ctx context.Context,
	tx *sql.Tx,
	medrecID string,
	departmentID string,
	registrationDate time.Time,
) (dbLabRegistration, bool, error) {
	var registration dbLabRegistration
	err := tx.QueryRowContext(ctx, `
		SELECT RegID, MEDRECID, Tanggal, DepartemenID, DokterID,
			JenisPasienID, JenisRegID, PeriodeTarifID, PerusahaanID,
			AsuransiID, ProviderID, GroupTarifID, NomerPolis
		FROM regpas
		WHERE MEDRECID = ?
			AND DepartemenID = ?
			AND DATE(Tanggal) = ?
			AND NA = 'N'
			AND (BatalDaftar IS NULL OR BatalDaftar = 'N' OR BatalDaftar = '')
		LIMIT 1 FOR UPDATE`, medrecID, departmentID, registrationDate.Format("2006-01-02")).Scan(
		&registration.RegID,
		&registration.MEDRECID,
		&registration.Tanggal,
		&registration.DepartemenID,
		&registration.DokterID,
		&registration.JenisPasienID,
		&registration.JenisRegID,
		&registration.PeriodeTarifID,
		&registration.PerusahaanID,
		&registration.AsuransiID,
		&registration.ProviderID,
		&registration.GroupTarifID,
		&registration.NomerPolis,
	)
	if errors.Is(err, sql.ErrNoRows) {
		return dbLabRegistration{}, false, nil
	}
	if err != nil {
		return dbLabRegistration{}, false, err
	}
	return registration, true, nil
}

func findLabTransaction(ctx context.Context, tx *sql.Tx, regID string, departmentID string) (dbLabTransaction, bool, error) {
	var transaction dbLabTransaction
	err := tx.QueryRowContext(ctx, `
		SELECT TrxDepartemenID, TrxID
		FROM trxdepartemen
		WHERE RegID = ? AND DepartemenID = ? AND NA = 'N'
		LIMIT 1`, regID, departmentID).Scan(&transaction.TrxDepartemenID, &transaction.TrxID)
	if errors.Is(err, sql.ErrNoRows) {
		return dbLabTransaction{}, false, nil
	}
	if err != nil {
		return dbLabTransaction{}, false, err
	}
	return transaction, true, nil
}

func nextLabRegistrationID(ctx context.Context, tx *sql.Tx, date time.Time, channel string, now string) (string, error) {
	prefix := "CPLAB" + date.Format("060102")
	lastCounter, err := lastLabCounter(ctx, tx, `
		SELECT TrxID FROM trxidlaboratorium
		WHERE TrxID LIKE ?
		ORDER BY TrxID DESC LIMIT 1 FOR UPDATE`, prefix+"%")
	if err != nil {
		return "", err
	}
	if lastCounter == "" {
		lastCounter, err = lastLabCounter(ctx, tx, `
			SELECT TrxID FROM trxdepartemen
			WHERE TrxID LIKE ?
			ORDER BY TrxID DESC LIMIT 1 FOR UPDATE`, prefix+"%")
		if err != nil {
			return "", err
		}
	}

	sequence := 1
	if lastCounter != "" {
		parsed, _ := strconv.Atoi(strings.TrimPrefix(lastCounter, prefix))
		sequence = parsed + 1
	}
	regID := fmt.Sprintf("%s%04d", prefix, sequence)

	_, err = tx.ExecContext(ctx, `
		INSERT INTO trxidlaboratorium (TrxID, UserID, Tanggal)
		VALUES (?, ?, ?)`, regID, channel, now)
	if err != nil {
		return "", err
	}
	return regID, nil
}

func lastLabCounter(ctx context.Context, tx *sql.Tx, query string, args ...any) (string, error) {
	var counter string
	err := tx.QueryRowContext(ctx, query, args...).Scan(&counter)
	if errors.Is(err, sql.ErrNoRows) {
		return "", nil
	}
	return counter, err
}

func resolveDailyShiftID(ctx context.Context, tx *sql.Tx, requested *int64, date time.Time) (any, error) {
	if requested != nil {
		active, err := exists(ctx, tx, `
			SELECT EXISTS(
				SELECT 1 FROM shiftharian
				WHERE ShiftHarianID = ? AND NA = 'N'
			)`, *requested)
		if err != nil {
			return nil, err
		}
		if !active {
			return nil, ValidationError{Message: "Shift harian tidak ditemukan atau sudah tidak aktif"}
		}
		return *requested, nil
	}

	var shiftID sql.NullInt64
	err := tx.QueryRowContext(ctx, `
		SELECT ShiftHarianID FROM shiftharian
		WHERE DATE(Tanggal) = ? AND NA = 'N'
		ORDER BY ShiftHarianID DESC LIMIT 1`, date.Format("2006-01-02")).Scan(&shiftID)
	if errors.Is(err, sql.ErrNoRows) {
		return "", nil
	}
	if err != nil {
		return nil, err
	}
	if !shiftID.Valid {
		return "", nil
	}
	return shiftID.Int64, nil
}

func upsertLabNominals(ctx context.Context, tx *sql.Tx, regID string, trxDepartmentID int64, departmentID string) error {
	regpasNominalExists, err := exists(ctx, tx, `SELECT EXISTS(SELECT 1 FROM regpas_nominal WHERE RegID = ?)`, regID)
	if err != nil {
		return err
	}
	if regpasNominalExists {
		_, err = tx.ExecContext(ctx, `
			UPDATE regpas_nominal SET
				SetAdministrasi = 'Otomatis', BiayaAdministrasi = 0,
				AdmDitanggungPasien = 0, AdmDitanggungPenjamin = 0,
				AdmDitanggungPasienDefault = 0, AdmDitanggungPenjaminDefault = 0
			WHERE RegID = ?`, regID)
	} else {
		_, err = tx.ExecContext(ctx, `
			INSERT INTO regpas_nominal (
				RegID, SetAdministrasi, BiayaAdministrasi, AdmDitanggungPasien,
				AdmDitanggungPenjamin, AdmDitanggungPasienDefault, AdmDitanggungPenjaminDefault
			) VALUES (?, 'Otomatis', 0, 0, 0, 0, 0)`, regID)
	}
	if err != nil {
		return err
	}

	trxNominalExists, err := exists(ctx, tx, `
		SELECT EXISTS(SELECT 1 FROM trxdepartemen_nominal WHERE TrxDepartemenID = ?)`, trxDepartmentID)
	if err != nil {
		return err
	}
	if trxNominalExists {
		_, err = tx.ExecContext(ctx, `
			UPDATE trxdepartemen_nominal SET
				RegID = ?, DepartemenID = ?, JumlahNominalTrxLayanan = 0,
				JumlahNominalTrxItem = 0, JumlahNominalTrxTT = 0,
				JumlahNominalTotalTrx = 0
			WHERE TrxDepartemenID = ?`, regID, departmentID, trxDepartmentID)
	} else {
		_, err = tx.ExecContext(ctx, `
			INSERT INTO trxdepartemen_nominal (
				TrxDepartemenID, RegID, DepartemenID, JumlahNominalTrxLayanan,
				JumlahNominalTrxItem, JumlahNominalTrxTT, JumlahNominalTotalTrx
			) VALUES (?, ?, ?, 0, 0, 0, 0)`, trxDepartmentID, regID, departmentID)
	}
	return err
}

func saveLabDiagnosis(ctx context.Context, tx *sql.Tx, regID string, medrecID string, diagnosis string, channel string, now string) error {
	_, err := tx.ExecContext(ctx, `
		INSERT INTO diagnosa_log (
			RegID, MEDRECID, DiagnosaKerja0, DiagnosaKerja, TanggalBuat, LoginBuat, NA
		) VALUES (?, ?, '', ?, ?, ?, 'N')`, regID, medrecID, diagnosis, now, channel)
	if err != nil {
		return err
	}
	_, err = tx.ExecContext(ctx, `
		INSERT INTO regpas_diagnosa (RegID, MEDRECID, DiagnosaKerja, DiagnosaKerjaOleh)
		VALUES (?, ?, ?, ?)`, regID, medrecID, diagnosis, channel)
	return err
}

func buildLabRegistrationData(ctx context.Context, tx *sql.Tx, registration dbLabRegistration, transaction *dbLabTransaction) (LabRegistrationData, error) {
	data := LabRegistrationData{
		Registration: LabRegistration{
			RegID:          registration.RegID,
			MEDRECID:       registration.MEDRECID,
			Tanggal:        registration.Tanggal.Format("2006-01-02 15:04:05"),
			DepartemenID:   registration.DepartemenID,
			DokterID:       nullStringPtr(registration.DokterID),
			JenisPasienID:  registration.JenisPasienID,
			JenisRegID:     nullInt64Ptr(registration.JenisRegID),
			PeriodeTarifID: registration.PeriodeTarifID,
			PerusahaanID:   nullStringPtr(registration.PerusahaanID),
			AsuransiID:     nullStringPtr(registration.AsuransiID),
			ProviderID:     nullStringPtr(registration.ProviderID),
			GroupTarifID:   nullInt64Ptr(registration.GroupTarifID),
			NomerPolis:     nullStringPtr(registration.NomerPolis),
		},
		Layanan: make([]LabService, 0),
	}
	if transaction == nil {
		return data, nil
	}

	services, grandTotal, err := findLabServices(ctx, tx, transaction.TrxDepartemenID)
	if err != nil {
		return LabRegistrationData{}, err
	}
	data.Transaction = &LabTransaction{
		TrxDepartemenID: transaction.TrxDepartemenID,
		TrxID:           transaction.TrxID,
		GrandTotal:      grandTotal,
	}
	data.Layanan = services
	return data, nil
}

func findLabServices(ctx context.Context, tx *sql.Tx, trxDepartmentID int64) ([]LabService, float64, error) {
	rows, err := tx.QueryContext(ctx, `
		SELECT tl.TrxLayananID, tl.HeaderLayananID, tl.LayananID, ml.Nama,
			tl.Jumlah, tl.Cito, tl.Harga, tl.Diskon, tl.TipeDiskon,
			tl.DitanggungPasien, tl.DitanggungPenjamin
		FROM trxlayanan AS tl
		LEFT JOIN masterlayanan AS ml ON ml.LayananID = tl.LayananID
		WHERE tl.TrxDepartemenID = ? AND tl.NA = 'N'
		ORDER BY tl.TrxLayananID`, trxDepartmentID)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	services := make([]LabService, 0)
	grandTotal := 0.0
	for rows.Next() {
		var service LabService
		var headerID sql.NullString
		var layananID, name, quantity, cito, price, discount, discountType sql.NullString
		var patientAmount, guarantorAmount sql.NullString
		if err := rows.Scan(
			&service.TrxLayananID,
			&headerID,
			&layananID,
			&name,
			&quantity,
			&cito,
			&price,
			&discount,
			&discountType,
			&patientAmount,
			&guarantorAmount,
		); err != nil {
			return nil, 0, err
		}

		service.HeaderLayananID = nullStringPtr(headerID)
		service.LayananID = nullStringPtr(layananID)
		service.Nama = nullStringPtr(name)
		service.Jumlah = nullStringPtr(quantity)
		service.Cito = nullStringPtr(cito)
		service.Harga = nullStringPtr(price)
		service.Diskon = nullStringPtr(discount)
		service.TipeDiskon = nullStringPtr(discountType)
		service.DitanggungPasien = nullStringPtr(patientAmount)
		service.DitanggungPenjamin = nullStringPtr(guarantorAmount)
		grandTotal += parseNullableDecimal(patientAmount) + parseNullableDecimal(guarantorAmount)
		services = append(services, service)
	}
	if err := rows.Err(); err != nil {
		return nil, 0, err
	}
	return services, grandTotal, nil
}

func exists(ctx context.Context, tx *sql.Tx, query string, args ...any) (bool, error) {
	var value bool
	err := tx.QueryRowContext(ctx, query, args...).Scan(&value)
	return value, err
}

func optionalLabTransaction(value dbLabTransaction, valid bool) *dbLabTransaction {
	if !valid {
		return nil
	}
	return &value
}

func labOutcome(httpStatus int, success bool, status string, message string, data *LabRegistrationData) LabRegistrationOutcome {
	return LabRegistrationOutcome{
		HTTPStatus: httpStatus,
		Success:    success,
		Status:     status,
		Message:    message,
		Data:       data,
	}
}

func nullTimeValue(value sql.NullTime) any {
	if !value.Valid {
		return nil
	}
	return value.Time
}

func nullStringValue(value sql.NullString) any {
	if !value.Valid {
		return nil
	}
	return value.String
}

func parseNullableDecimal(value sql.NullString) float64 {
	if !value.Valid {
		return 0
	}
	parsed, _ := strconv.ParseFloat(value.String, 64)
	return parsed
}

func isConcurrencyError(err error) bool {
	var mysqlErr *mysql.MySQLError
	if errors.As(err, &mysqlErr) && (mysqlErr.Number == 1205 || mysqlErr.Number == 1213) {
		return true
	}

	message := strings.ToLower(err.Error())
	return strings.Contains(message, "deadlock") ||
		strings.Contains(message, "lock wait timeout") ||
		strings.Contains(message, "serialization failure")
}