package suratkontrol

import (
	"bridging-rssa/config"
	suratkontrol "bridging-rssa/models/bpjs/surat_kontrol"
	"errors"
	"log"

	"gorm.io/gorm"
)

func GetDataKunjunganPasien(idxdaftar string) (*suratkontrol.KunjunganPasien, error) {
	var kunjunganPasien *suratkontrol.KunjunganPasien
	err := config.SatuDataDB.Raw(`select dkp."id", dkp."IDXDAFTAR" as idxdaftar, dkp."Nomor_sep" as nomorsep, 
										dlr."Nama" as namaklinik, dlr."Id_location_simgos" as idkliniksimgos, dlr."Kode_hfis_bpjs" as kodehfisklinik,
										dk."Nama_lengkap" as namadokter, 
										dp."KDDOKTER" as kodedokter, dp."HFIS_code" as kodehfisdokter,
										dsu."Kode" as kodesubspesialis, dsu."Subspesialis" as subspesialis,
										ds."Spesialis" as spesialis,
									array(
										select dgd."Gelar_depan"
											from data_pegawai_daftar_gelar_depan dpdgd 
											left join daftar_gelar_depan dgd on dpdgd.daftar_gelar_depan_id = dgd.id
											where dpdgd.data_pegawai_id = dp.id
										) AS gelardepan,
									array(
										select dgb."Gelar_belakang"
											from data_pegawai_daftar_gelar_belakang dpdgb 
											left join daftar_gelar_belakang dgb on dpdgb.daftar_gelar_belakang_id = dgb.id
											where dpdgb.data_pegawai_id = dp.id
										) AS gelarbelakang,
										dkp."Ruangan" as ruangan,
										dkp."DPJP" as id_dokter,
										dkp."NOMR" as "No_rm"
									from data_kunjungan_pasien dkp 
									left join daftar_lokasi_ruang dlr on dkp."Ruangan" = dlr.id 
									left join data_pegawai dp on dkp."DPJP" = dp.id 
									left join data_ktp dk on dp."KTP" = dk.id 
									left join daftar_subspesialis dsu on dp."Subspesialis" = dsu.id
									left join daftar_spesialis ds on dsu."FK_daftar_spesialis_ID" = ds.id
									where dkp."IDXDAFTAR" = ?`, idxdaftar).First(&kunjunganPasien).Error
	if err != nil {
		if errors.Is(err, gorm.ErrRecordNotFound) {
			errMsg := errors.New("Data Tidak Ditemukan")
			log.Println(errMsg)
			return nil, errMsg
		}
		log.Println(err)
		return nil, err
	}
	log.Println("Data Kunjungan Pasien : ", kunjunganPasien)
	return kunjunganPasien, nil
}

func InsertDataSuratKontrol(reqInsert *suratkontrol.DataSuratKontrol) error {
	log.Println("Req Insert Database : ", reqInsert)
	insertQuery := `insert into "data_surat_kontrol" ("id", "date_created", "Tanggal_cetak_surat_kontrol", "Tanggal_rencana_kontrol", 
					"Nomor_surat_kontrol", "Kunjungan_pasien", "Ruangan_surat_kontrol", "DPJP_surat_kontrol", "Status_surat_kontrol", 
					"User_created_surat_kontrol", "Tipe_surat_kontrol", "User_updated_surat_kontrol")
					values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`
	err := config.SatuDataDB.Exec(insertQuery,
		reqInsert.ID,
		reqInsert.DateCreated,
		reqInsert.TglCetakSurKon,
		reqInsert.TglRencanaKontrol,
		reqInsert.NomorSurKon,
		reqInsert.KunjunganPasien,
		reqInsert.RuanganSurKon,
		reqInsert.DPJPSurKon,
		reqInsert.StatusSurKon,
		reqInsert.UserCreatedSurKon,
		reqInsert.TipeSurKon,
		reqInsert.UserUpdatedSurKon,
	).Error
	if err != nil {
		log.Println(err)
		return err
	}
	return nil
}

func GetDataSuratKontrol(offset, limit string) ([]*suratkontrol.GetDataSuratKontrol, error) {
	var dataSurKon []*suratkontrol.GetDataSuratKontrol
	err := config.SatuDataDB.Raw(`select dsk."id", dsk."Tanggal_cetak_surat_kontrol", dsk."Tanggal_rencana_kontrol",
		dsk."Nomor_surat_kontrol", 
		(select dlr."Nama"
		from data_kunjungan_pasien dkp
		left join daftar_lokasi_ruang dlr on dkp."Ruangan" = dlr.id where dkp."id" = dsk."Kunjungan_pasien" LIMIT 1) as "Poli_asal", 
		dlr."Nama" as "Poli_tuju",
		dkp2."Nomor_sep", dkp2."Nama_lengkap", dkp2."IDXDAFTAR",
		array(
			select dgd. "Gelar_depan"
			from data_pegawai_daftar_gelar_depan dpdgd
			left join daftar_gelar_depan dgd on dpdgd.daftar_gelar_depan_id = dgd.id where dpdgd.data_pegawai_id = dp.id
		) AS gelardepan ,
		dk."Nama_lengkap" as "Nama_lengkap_dokter",
		array(
			select dgb. "Gelar_belakang"
			from data_pegawai_daftar_gelar_belakang dpdgb
			left join daftar_gelar_belakang dgb on dpdgb.daftar_gelar_belakang_id = dgb.id where dpdgb.data_pegawai_id = dp.id
		) AS gelarbelakang,
		ds."Subspesialis",
		ds."Kode" as "Kode_poli",
		ds."id" as "ID_poli",
		dp."HFIS_code" as "Kode_dokter",
		dp."id" as "id_dokter",
		dp."Code_dpjp" as "Kode_dpjp",
		dkp2."NOMR" as "No_rm",
		dkp2."Diagnosis_primer_utama" as "Kode_diagnosa",
		dkp2."Display_diagnosis_primer_utama" as "Nama_diagnosa",
		dsk."Tipe_surat_kontrol" as "Tipe_SurKon"
		from data_surat_kontrol dsk
		left join data_kunjungan_pasien dkp2 on dkp2."id" = dsk."Kunjungan_pasien"
		left join daftar_lokasi_ruang dlr on dsk."Ruangan_surat_kontrol" = dlr."id"
		left join data_pegawai dp on dsk."DPJP_surat_kontrol" = dp."id"
		left join data_ktp dk on dp."KTP" = dk. "id"
		left join daftar_subspesialis ds on dp."Subspesialis" = ds."id"
		where dsk."Status_surat_kontrol" = '1' offset ? limit ?`, offset, limit).Scan(&dataSurKon).Error
	if err != nil {
		if errors.Is(err, gorm.ErrRecordNotFound) {
			errMsg := errors.New("DPJP anda belum ada subspesialis")
			log.Println(errMsg)
			return nil, errMsg
		}
	}
	return dataSurKon, nil
}

func GetDataSuratKontrolByID(idxdaftar string) (*suratkontrol.GetDataSuratKontrol, error) {
	var dataSurKon *suratkontrol.GetDataSuratKontrol
	err := config.SatuDataDB.Raw(`select dsk."id", dsk."Tanggal_cetak_surat_kontrol", dsk."Tanggal_rencana_kontrol",
		dsk."Nomor_surat_kontrol", 
		(select dlr."Nama"
		from data_kunjungan_pasien dkp
		left join daftar_lokasi_ruang dlr on dkp."Ruangan" = dlr.id where dkp."id" = dsk."Kunjungan_pasien" LIMIT 1) as "Poli_asal", 
		dlr."Nama" as "Poli_tuju",
		dkp2."Nomor_sep", dkp2."Nama_lengkap", dkp2."IDXDAFTAR",
		array(
			select dgd. "Gelar_depan"
			from data_pegawai_daftar_gelar_depan dpdgd
			left join daftar_gelar_depan dgd on dpdgd.daftar_gelar_depan_id = dgd.id where dpdgd.data_pegawai_id = dp.id
		) AS gelardepan ,
		dk."Nama_lengkap" as "Nama_lengkap_dokter",
		array(
			select dgb. "Gelar_belakang"
			from data_pegawai_daftar_gelar_belakang dpdgb
			left join daftar_gelar_belakang dgb on dpdgb.daftar_gelar_belakang_id = dgb.id where dpdgb.data_pegawai_id = dp.id
		) AS gelarbelakang,
		ds."Subspesialis",
		ds."Kode" as "Kode_poli",
		ds."id" as "ID_poli",
		dp."HFIS_code" as "Kode_dokter",
		dp."id" as "id_dokter",
		dp."Code_dpjp" as "Kode_dpjp",
		dkp2."NOMR" as "No_rm",
		dkp2."Diagnosis_primer_utama" as "Kode_diagnosa",
		dkp2."Display_diagnosis_primer_utama" as "Nama_diagnosa",
		dsk."Tipe_surat_kontrol" as "Tipe_SurKon",
		dsk."User_created_surat_kontrol" as "User"
		from data_surat_kontrol dsk
		left join data_kunjungan_pasien dkp2 on dkp2."id" = dsk."Kunjungan_pasien"
		left join daftar_lokasi_ruang dlr on dsk."Ruangan_surat_kontrol" = dlr."id"
		left join data_pegawai dp on dsk."DPJP_surat_kontrol" = dp."id"
		left join data_ktp dk on dp."KTP" = dk. "id"
		left join daftar_subspesialis ds on dp."Subspesialis" = ds."id"
		where dsk."Status_surat_kontrol" = '1' and dkp2."IDXDAFTAR" = ?`, idxdaftar).First(&dataSurKon).Error
	if err != nil {
		if errors.Is(err, gorm.ErrRecordNotFound) {
			errMsg := errors.New("DPJP anda belum ada subspesialis")
			log.Println(errMsg)
			return nil, errMsg
		}
	}
	return dataSurKon, nil
}

func UpdateDataSuratKontrol(reqUpdate *suratkontrol.DataSuratKontrol) error {
	err := config.SatuDataDB.Exec(`update "data_surat_kontrol" set "date_updated" = ?, "Status_surat_kontrol" = ?, "User_updated_surat_kontrol" = ? where "id" = ?`, reqUpdate.DateUpdated, reqUpdate.StatusSurKon, reqUpdate.UserUpdatedSurKon, reqUpdate.ID).Error
	if err != nil {
		log.Println(err)
		return err
	}
	return nil
}

func GetIDXDaftarBySurKon(idSurKon string) (string, error) {
	var idxDaftar string
	err := config.SatuDataDB.Raw(`select dkp."IDXDAFTAR"
		from "data_surat_kontrol" dsk
		left join "data_kunjungan_pasien" dkp on dkp."id" = dsk."Kunjungan_pasien"
		where dsk."id" = ?`, idSurKon).First(&idxDaftar).Error
	if err != nil {
		log.Println(err)
		return "", err
	}
	return idxDaftar, err
}

func GetAllDataSuratKontrol(idSurKon string) (*suratkontrol.DataSuratKontrol, error) {
	var dataSurKon *suratkontrol.DataSuratKontrol
	err := config.SatuDataDB.Raw(`select * from "data_surat_kontrol" where "id" = ?`, idSurKon).First(&dataSurKon).Error
	if err != nil {
		log.Println(err)
		return nil, err
	}
	return dataSurKon, nil
}

func SearchDataSurKon(req suratkontrol.RequestCariPasien, query string, args []interface{}) ([]*suratkontrol.GetDataSuratKontrol, error) {
	var dataSurKon []*suratkontrol.GetDataSuratKontrol
	query += ` and date(dsk."Tanggal_rencana_kontrol") between ? and ? offset ? limit ?`
	querySelect := `select dsk."id", dsk."Tanggal_cetak_surat_kontrol", dsk."Tanggal_rencana_kontrol",
		dsk."Nomor_surat_kontrol", 
		(select dlr."Nama"
		from data_kunjungan_pasien dkp
		left join daftar_lokasi_ruang dlr on dkp."Ruangan" = dlr.id where dkp."id" = dsk."Kunjungan_pasien" LIMIT 1) as "Poli_asal", 
		dlr."Nama" as "Poli_tuju",
		dkp2."Nomor_sep", dkp2."Nama_lengkap", dkp2."IDXDAFTAR",
		array(
			select dgd. "Gelar_depan"
			from data_pegawai_daftar_gelar_depan dpdgd
			left join daftar_gelar_depan dgd on dpdgd.daftar_gelar_depan_id = dgd.id where dpdgd.data_pegawai_id = dp.id
		) AS gelardepan ,
		dk."Nama_lengkap" as "Nama_lengkap_dokter",
		array(
			select dgb. "Gelar_belakang"
			from data_pegawai_daftar_gelar_belakang dpdgb
			left join daftar_gelar_belakang dgb on dpdgb.daftar_gelar_belakang_id = dgb.id where dpdgb.data_pegawai_id = dp.id
		) AS gelarbelakang,
		ds."Subspesialis",
		ds."Kode" as "Kode_poli",
		ds."id" as "ID_poli",
		dp."HFIS_code" as "Kode_dokter",
		dp."id" as "id_dokter",
		dp."Code_dpjp" as "Kode_dpjp",
		dkp2."NOMR" as "No_rm",
		dkp2."Diagnosis_primer_utama" as "Kode_diagnosa",
		dkp2."Display_diagnosis_primer_utama" as "Nama_diagnosa",
		dsk."Tipe_surat_kontrol" as "Tipe_SurKon"
		from data_surat_kontrol dsk
		left join data_kunjungan_pasien dkp2 on dkp2."id" = dsk."Kunjungan_pasien"
		left join daftar_lokasi_ruang dlr on dsk."Ruangan_surat_kontrol" = dlr."id"
		left join data_pegawai dp on dsk."DPJP_surat_kontrol" = dp."id"
		left join data_ktp dk on dp."KTP" = dk. "id"
		left join daftar_subspesialis ds on dp."Subspesialis" = ds."id"
		where dsk."Status_surat_kontrol" = '1' and dsk."Tipe_surat_kontrol" = ? `
	querySelect += query
	err := config.SatuDataDB.Raw(querySelect, args...).Find(&dataSurKon).Error
	if err != nil {
		log.Println(err)
		return nil, err
	}
	return dataSurKon, nil
}

func CountDataSurKon(req suratkontrol.RequestCariPasien, query string, args []interface{}) (int, error) {
	var jumlahData int
	query += ` and date(dsk."Tanggal_rencana_kontrol") between ? and ?`
	querySelect := `select count(dsk.*)
		from data_surat_kontrol dsk
		left join data_kunjungan_pasien dkp2 on dkp2."id" = dsk."Kunjungan_pasien"
		left join daftar_lokasi_ruang dlr on dsk."Ruangan_surat_kontrol" = dlr."id"
		left join data_pegawai dp on dsk."DPJP_surat_kontrol" = dp."id"
		left join data_ktp dk on dp."KTP" = dk. "id"
		left join daftar_subspesialis ds on dp."Subspesialis" = ds."id"
		where dsk."Status_surat_kontrol" = '1' and dsk."Tipe_surat_kontrol" = ? `
	querySelect += query
	err := config.SatuDataDB.Raw(querySelect, args...).Find(&jumlahData).Error
	if err != nil {
		log.Println(err)
		return 0, err
	}
	return jumlahData, nil
}

func GetNomorSurKon(idPasien string) (string, error) {
	var noSurKon string
	querySelect := `select dsk."Nomor_surat_kontrol" from data_surat_kontrol dsk where dsk."Kunjungan_pasien" = ? and dsk."Tipe_surat_kontrol" <> '1' order by dsk."date_created"`
	err := config.SatuDataDB.Raw(querySelect, idPasien).Scan(&noSurKon).Error
	if err != nil {
		log.Println(err)
		return "", err
	}
	log.Println("Nomor SurKon :", noSurKon)
	return noSurKon, nil
}