web-giao-ban-go/handlers/api.go

package handlers

import (
	"encoding/json"
	"fmt"
	"net/http"
	"strings"
	"sync"
	"time"

	"web-giao-ban-go/config"
	"web-giao-ban-go/db"
	"web-giao-ban-go/license"
)

// Helper to generate partition filters: namkt IN (...) AND thangkt IN (...)
func GetThangNamFilter(fromStr, toStr, prefix string) string {
	f, err1 := time.Parse("2006-01-02 15:04:05", fromStr)
	t, err2 := time.Parse("2006-01-02 15:04:05", toStr)
	if err1 != nil || err2 != nil {
		// Try date only
		f, err1 = time.Parse("2006-01-02", fromStr[:10])
		t, err2 = time.Parse("2006-01-02", toStr[:10])
		if err1 != nil || err2 != nil {
			return ""
		}
	}

	thangsMap := make(map[string]bool)
	namsMap := make(map[string]bool)

	curr := time.Date(f.Year(), f.Month(), 1, 0, 0, 0, 0, time.UTC)
	end := time.Date(t.Year(), t.Month(), 1, 0, 0, 0, 0, time.UTC)
	count := 0

	for !curr.After(end) && count < 24 {
		thangsMap[fmt.Sprintf("'%02d'", curr.Month())] = true
		namsMap[fmt.Sprintf("'%d'", curr.Year())] = true
		curr = curr.AddDate(0, 1, 0)
		count++
	}

	var thangsArr, namsArr []string
	for k := range thangsMap {
		thangsArr = append(thangsArr, k)
	}
	for k := range namsMap {
		namsArr = append(namsArr, k)
	}

	if len(thangsArr) == 0 || len(namsArr) == 0 {
		return ""
	}

	p := ""
	if prefix != "" {
		p = prefix + "."
	}
	return fmt.Sprintf(" AND %snamkt IN (%s) AND %sthangkt IN (%s) ",
		p, strings.Join(namsArr, ","),
		p, strings.Join(thangsArr, ","),
	)
}

func checkLicense(w http.ResponseWriter) bool {
	valid, msg := license.CheckValid()
	if !valid {
		w.Header().Set("Content-Type", "application/json; charset=utf-8")
		w.WriteHeader(http.StatusForbidden)
		_ = json.NewEncoder(w).Encode(map[string]interface{}{
			"success":       false,
			"license_valid": false,
			"message":       msg,
			"error":         msg,
		})
		return false
	}
	return true
}

// GET /api/databases
func HandleGetDatabases(w http.ResponseWriter, r *http.Request) {
	cfg := config.Get()
	w.Header().Set("Content-Type", "application/json; charset=utf-8")
	licStatus := license.GetStatus()
	_ = json.NewEncoder(w).Encode(map[string]interface{}{
		"success":       true,
		"data":          cfg.Database.Databases,
		"license_valid": licStatus.IsValid,
		"license":       licStatus,
	})
}

type OutpatientData struct {
	Khoa     []map[string]interface{} `json:"khoa"`
	BANT     map[string]interface{}   `json:"bant"`
	YDCT     map[string]interface{}   `json:"ydct"`
	CLS      []map[string]interface{} `json:"cls"`
	Top10    []map[string]interface{} `json:"top10"`
	TiemNgua int                      `json:"tiemngua"`
}

func queryOutpatient(database, fromDate, toDate string) (*OutpatientData, error) {
	conn, err := db.GetDB(database)
	if err != nil {
		return nil, err
	}

	fnKb := GetThangNamFilter(fromDate, toDate, "kb")
	fnLs := GetThangNamFilter(fromDate, toDate, "ls")
	fnNone := GetThangNamFilter(fromDate, toDate, "")

	out := &OutpatientData{
		Khoa:  make([]map[string]interface{}, 0),
		BANT:  map[string]interface{}{"moi": 0, "taikham": 0, "tong": 0},
		YDCT:  map[string]interface{}{"dien_cham": 0, "vltl": 0, "sac_thuoc": 0, "cay_chi": 0},
		CLS:   make([]map[string]interface{}, 0),
		Top10: make([]map[string]interface{}, 0),
	}

	var wg sync.WaitGroup
	var mu sync.Mutex

	// 1. Khoa KCB
	wg.Add(1)
	go func() {
		defer wg.Done()
		sqlKhoa := fmt.Sprintf(`
			SELECT 
				COALESCE(dv.madv, 'KHAC') AS madv,
				COALESCE(dv.tendv, 'Khoa khác') AS tendv,
				COUNT(kb.makb) AS tong_kham,
				SUM(CASE WHEN dt.bhyt IN (1, 2) THEN 1 ELSE 0 END) AS bhyt,
				SUM(CASE WHEN dt.bhyt IN (0, 4) THEN 1 ELSE 0 END) AS thuphi,
				SUM(CASE WHEN kb.dakham = 6 THEN 1 ELSE 0 END) AS chuyenvien
			FROM current.khambenh kb
			LEFT JOIN current.psdangky dk ON kb.mabn = dk.mabn AND kb.makb = dk.makb AND COALESCE(dk.xoa, 0) = 0
			LEFT JOIN current.dmdoituong dt ON dk.madt = dt.madt
			LEFT JOIN current.dmdonvi dv ON kb.madv = dv.madv
			WHERE kb.ngaykcb BETWEEN $1 AND $2 %s
				AND COALESCE(kb.xoa, 0) = 0
				AND COALESCE(kb.dakham, 0) != 0
			GROUP BY dv.madv, dv.tendv
			ORDER BY tong_kham DESC;
		`, fnKb)

		rows, err := conn.Query(sqlKhoa, fromDate, toDate)
		if err == nil {
			defer rows.Close()
			var list []map[string]interface{}
			for rows.Next() {
				var madv, tendv string
				var tong, bhyt, thuphi, cv int
				_ = rows.Scan(&madv, &tendv, &tong, &bhyt, &thuphi, &cv)
				list = append(list, map[string]interface{}{
					"madv": madv, "tendv": tendv, "tong_kham": tong,
					"bhyt": bhyt, "thuphi": thuphi, "chuyenvien": cv,
				})
			}
			mu.Lock()
			out.Khoa = list
			mu.Unlock()
		}
	}()

	// 2. BANT
	wg.Add(1)
	go func() {
		defer wg.Done()
		sqlBant := `
			SELECT 
				COALESCE(SUM(CASE WHEN COALESCE(dk.taikham, 0) = 0 THEN 1 ELSE 0 END), 0) AS moi,
				COALESCE(SUM(CASE WHEN COALESCE(dk.taikham, 0) = 1 THEN 1 ELSE 0 END), 0) AS taikham,
				COUNT(*) AS tong
			FROM current.bnnoitru nt
			JOIN current.psdangky dk ON nt.mabn = dk.mabn AND nt.makb = dk.makb AND COALESCE(dk.xoa, 0) = 0
			WHERE nt.namvien = 0 AND COALESCE(nt.xoa, 0) = 0
				AND nt.ngayvv BETWEEN $1 AND $2;
		`
		var moi, taikham, tong int
		err := conn.QueryRow(sqlBant, fromDate, toDate).Scan(&moi, &taikham, &tong)
		if err == nil {
			mu.Lock()
			out.BANT = map[string]interface{}{"moi": moi, "taikham": taikham, "tong": tong}
			mu.Unlock()
		}
	}()

	// 3. YDCT
	wg.Add(1)
	go func() {
		defer wg.Done()
		sqlYdct := fmt.Sprintf(`
			SELECT 
				COALESCE(SUM(CASE WHEN cls.tencls ILIKE '%%Điện châm%%' THEN ls.soluong ELSE 0 END), 0) AS dien_cham,
				COALESCE(SUM(CASE WHEN cls.tencls ILIKE '%%Vật lý trị liệu%%' OR cls.tencls ILIKE '%%VLTL%%' OR cls.macls IN ('DI131','DI135') THEN ls.soluong ELSE 0 END), 0) AS vltl,
				COALESCE(SUM(CASE WHEN cls.tencls ILIKE '%%Sắc thuốc%%' THEN ls.soluong ELSE 0 END), 0) AS sac_thuoc,
				COALESCE(SUM(CASE WHEN cls.tencls ILIKE '%%Cấy chỉ%%' OR cls.tencls ILIKE '%%Chôn chỉ%%' THEN ls.soluong ELSE 0 END), 0) AS cay_chi
			FROM current.chidinhcls ls
			JOIN current.dmcls cls ON ls.macls = cls.macls
			WHERE ls.ngaykcb BETWEEN $1 AND $2 %s
				AND COALESCE(ls.xoa, 0) = 0;
		`, fnLs)
		var dc, vltl, st, cc float64
		err := conn.QueryRow(sqlYdct, fromDate, toDate).Scan(&dc, &vltl, &st, &cc)
		if err == nil {
			mu.Lock()
			out.YDCT = map[string]interface{}{
				"dien_cham": int(dc), "vltl": int(vltl), "sac_thuoc": int(st), "cay_chi": int(cc),
			}
			mu.Unlock()
		}
	}()

	// 4. CLS
	wg.Add(1)
	go func() {
		defer wg.Done()
		sqlCls := fmt.Sprintf(`
			SELECT 
				cls.kho,
				cls.maloai,
				COALESCE(loaicls.tenloai, cls.maloai) AS tenloai,
				COUNT(DISTINCT ls.makb) AS so_bn,
				COUNT(*) AS so_luot
			FROM current.chidinhcls ls
			JOIN current.dmcls cls ON ls.macls = cls.macls
			LEFT JOIN current.dmloaicls loaicls ON cls.maloai = loaicls.maloai
			WHERE ls.ngaykcb BETWEEN $1 AND $2 %s
				AND COALESCE(ls.xoa, 0) = 0
				AND cls.kho IN ('XN', 'HA', 'CN')
			GROUP BY cls.kho, cls.maloai, loaicls.tenloai
			ORDER BY cls.kho, count(*) DESC;
		`, fnLs)
		rows, err := conn.Query(sqlCls, fromDate, toDate)
		if err == nil {
			defer rows.Close()
			var list []map[string]interface{}
			for rows.Next() {
				var kho, maloai, tenloai string
				var soBn, soLuot int
				_ = rows.Scan(&kho, &maloai, &tenloai, &soBn, &soLuot)
				list = append(list, map[string]interface{}{
					"kho": kho, "maloai": maloai, "tenloai": tenloai, "so_bn": soBn, "so_luot": soLuot,
				})
			}
			mu.Lock()
			out.CLS = list
			mu.Unlock()
		}
	}()

	// 5. Top 10
	wg.Add(1)
	go func() {
		defer wg.Done()
		sqlTop10 := fmt.Sprintf(`
			SELECT maicd, kqcdoan, COUNT(*) AS sl
			FROM current.khambenh
			WHERE ngaykcb BETWEEN $1 AND $2 %s
				AND COALESCE(xoa, 0) = 0
				AND maicd IS NOT NULL AND maicd != ''
			GROUP BY maicd, kqcdoan
			ORDER BY sl DESC
			LIMIT 10;
		`, fnNone)
		rows, err := conn.Query(sqlTop10, fromDate, toDate)
		if err == nil {
			defer rows.Close()
			var list []map[string]interface{}
			for rows.Next() {
				var maicd, kqcdoan string
				var sl int
				_ = rows.Scan(&maicd, &kqcdoan, &sl)
				list = append(list, map[string]interface{}{
					"maicd": maicd, "kqcdoan": kqcdoan, "sl": sl,
				})
			}
			mu.Lock()
			out.Top10 = list
			mu.Unlock()
		}
	}()

	// 6. Tiêm ngừa
	wg.Add(1)
	go func() {
		defer wg.Done()
		sqlTn := fmt.Sprintf(`
			SELECT COUNT(kb.makb) AS sl
			FROM current.khambenh kb
			LEFT JOIN current.dmphong ph ON kb.maphong = ph.maphong
			LEFT JOIN current.dmdonvi dv ON kb.madv = dv.madv
			WHERE kb.ngaykcb BETWEEN $1 AND $2 %s
				AND COALESCE(kb.xoa, 0) = 0
				AND COALESCE(kb.dakham, 0) != 0
				AND (
					COALESCE(ph.loai_bc_giaoban, 0) = 2 OR 
					ph.maphong = 'TN' OR 
					dv.madv = 'PTN' OR 
					ph.tenphong ILIKE '%%Tiêm ngừa%%' OR 
					ph.tenphong ILIKE '%%Tiêm chủng%%'
				);
		`, fnKb)
		var sl int
		err := conn.QueryRow(sqlTn, fromDate, toDate).Scan(&sl)
		if err == nil {
			mu.Lock()
			out.TiemNgua = sl
			mu.Unlock()
		}
	}()

	wg.Wait()
	return out, nil
}

// GET /api/giao-ban-ngoai-tru
func HandleGiaoBanNgoaiTru(w http.ResponseWriter, r *http.Request) {
	if !checkLicense(w) {
		return
	}
	w.Header().Set("Content-Type", "application/json; charset=utf-8")
	q := r.URL.Query()
	database := q.Get("database")
	from := q.Get("from")
	to := q.Get("to")
	compareFrom := q.Get("compareFrom")
	compareTo := q.Get("compareTo")

	if from == "" {
		from = "2026-03-01 00:00:00"
	}
	if to == "" {
		to = "2026-03-31 23:59:59"
	}

	curData, err := queryOutpatient(database, from, to)
	if err != nil {
		w.WriteHeader(http.StatusInternalServerError)
		_ = json.NewEncoder(w).Encode(map[string]interface{}{"success": false, "message": err.Error()})
		return
	}

	var compData *OutpatientData
	if compareFrom != "" && compareTo != "" {
		compData, _ = queryOutpatient(database, compareFrom, compareTo)
	}

	_ = json.NewEncoder(w).Encode(map[string]interface{}{
		"success": true,
		"data": map[string]interface{}{
			"current": curData,
			"compare": compData,
			"periodCurrent": map[string]string{
				"from": from, "to": to,
			},
			"periodCompare": func() interface{} {
				if compareFrom != "" && compareTo != "" {
					return map[string]string{"from": compareFrom, "to": compareTo}
				}
				return nil
			}(),
		},
	})
}

type InpatientData struct {
	Khoa []map[string]interface{} `json:"khoa"`
}

func queryInpatient(database, fromDate, toDate string) (*InpatientData, error) {
	conn, err := db.GetDB(database)
	if err != nil {
		return nil, err
	}

	sqlKhoa := `
		SELECT 
			dv.madv, 
			dv.tendv,
			SUM(CASE WHEN nt.ngayvv < $1 AND (nt.ravien = 0 OR nt.ngayrv >= $1) THEN 1 ELSE 0 END) AS benh_cu,
			SUM(CASE WHEN nt.ngayvv BETWEEN $1 AND $2 THEN 1 ELSE 0 END) AS benh_moi,
			SUM(CASE WHEN nt.ngayrv BETWEEN $1 AND $2 AND nt.ravien = 1 AND COALESCE(nt.makq, '01') NOT IN ('04','05','06') THEN 1 ELSE 0 END) AS on_xin_ve,
			SUM(CASE WHEN nt.ngayrv BETWEEN $1 AND $2 AND (nt.makq IN ('04','06') OR (nt.maxt = '09' AND nt.makq = '04')) THEN 1 ELSE 0 END) AS nang_xin_ve,
			SUM(CASE WHEN nt.ngayrv BETWEEN $1 AND $2 AND (nt.ravien = 2 OR nt.maxt = '08') THEN 1 ELSE 0 END) AS tron_vien,
			SUM(CASE WHEN nt.ngayrv BETWEEN $1 AND $2 AND (nt.ravien = 4 OR nt.maxt = '03') THEN 1 ELSE 0 END) AS chuyen_vien,
			SUM(CASE WHEN nt.ngayrv BETWEEN $1 AND $2 AND (nt.ravien = 3 OR nt.maxt = '06') THEN 1 ELSE 0 END) AS tu_vong,
			SUM(CASE WHEN nt.ngayvv <= $2 AND (nt.ravien = 0 OR nt.ngayrv > $2) THEN 1 ELSE 0 END) AS benh_hien_con,
			SUM(CASE WHEN nt.ngayvv <= $2 AND (nt.ravien = 0 OR nt.ngayrv >= $1) AND (COALESCE(nt.benhnang, 0) = 1 OR COALESCE(nt.mabatheodoi, '') != '' OR dv.madv IN ('17','29')) THEN 1 ELSE 0 END) AS benh_theo_doi,
			SUM(CASE WHEN nt.ngayvv <= $2 AND (nt.ravien = 0 OR nt.ngayrv >= $1) AND (nt.chamsoc ILIKE '%cấp I%' OR nt.chamsoc ILIKE '%cấp 1%' OR nt.chamsoc IN ('1', 'CS I', 'CSI')) THEN 1 ELSE 0 END) AS chamsoc_cap1
		FROM current.bnnoitru nt
		JOIN current.dmdonvi dv ON nt.madv = dv.madv
		WHERE nt.namvien = 1 AND COALESCE(nt.xoa, 0) = 0
			AND nt.ngayvv >= ($1::date - INTERVAL '180 days')
		GROUP BY dv.madv, dv.tendv
		HAVING SUM(CASE WHEN nt.ngayvv <= $2 AND (nt.ravien = 0 OR nt.ngayrv >= $1) THEN 1 ELSE 0 END) > 0
		ORDER BY benh_moi DESC;
	`

	rows, err := conn.Query(sqlKhoa, fromDate, toDate)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	list := make([]map[string]interface{}, 0)
	for rows.Next() {
		var madv, tendv string
		var cu, moi, on, nang, tron, cv, tv, hiencon, td, cs1 int
		_ = rows.Scan(&madv, &tendv, &cu, &moi, &on, &nang, &tron, &cv, &tv, &hiencon, &td, &cs1)
		list = append(list, map[string]interface{}{
			"madv": madv, "tendv": tendv,
			"benh_cu": cu, "benh_moi": moi, "on_xin_ve": on, "nang_xin_ve": nang,
			"tron_vien": tron, "chuyen_vien": cv, "tu_vong": tv, "benh_hien_con": hiencon,
			"benh_theo_doi": td, "chamsoc_cap1": cs1,
		})
	}

	return &InpatientData{Khoa: list}, nil
}

// GET /api/giao-ban-noi-tru
func HandleGiaoBanNoiTru(w http.ResponseWriter, r *http.Request) {
	if !checkLicense(w) {
		return
	}
	w.Header().Set("Content-Type", "application/json; charset=utf-8")
	q := r.URL.Query()
	database := q.Get("database")
	from := q.Get("from")
	to := q.Get("to")
	compareFrom := q.Get("compareFrom")
	compareTo := q.Get("compareTo")

	if from == "" {
		from = "2026-03-01 00:00:00"
	}
	if to == "" {
		to = "2026-03-31 23:59:59"
	}

	db.EnsureTable(database)

	curData, err := queryInpatient(database, from, to)
	if err != nil {
		w.WriteHeader(http.StatusInternalServerError)
		_ = json.NewEncoder(w).Encode(map[string]interface{}{"success": false, "message": err.Error()})
		return
	}

	var compData *InpatientData
	if compareFrom != "" && compareTo != "" {
		compData, _ = queryInpatient(database, compareFrom, compareTo)
	}

	_ = json.NewEncoder(w).Encode(map[string]interface{}{
		"success": true,
		"data": map[string]interface{}{
			"current": curData,
			"compare": compData,
			"periodCurrent": map[string]string{
				"from": from, "to": to,
			},
			"periodCompare": func() interface{} {
				if compareFrom != "" && compareTo != "" {
					return map[string]string{"from": compareFrom, "to": compareTo}
				}
				return nil
			}(),
		},
	})
}

// GET /api/danh-sach-chuyen-vien
func HandleDanhSachChuyenVien(w http.ResponseWriter, r *http.Request) {
	if !checkLicense(w) {
		return
	}
	w.Header().Set("Content-Type", "application/json; charset=utf-8")
	q := r.URL.Query()
	database := q.Get("database")
	from := q.Get("from")
	to := q.Get("to")
	madv := q.Get("madv")

	if from == "" {
		from = "2026-03-01 00:00:00"
	}
	if to == "" {
		to = "2026-03-31 23:59:59"
	}

	conn, err := db.GetDB(database)
	if err != nil {
		w.WriteHeader(http.StatusInternalServerError)
		_ = json.NewEncoder(w).Encode(map[string]interface{}{"success": false, "message": err.Error()})
		return
	}
	db.EnsureTable(database)

	sqlWhere := `
		WHERE nt.namvien = 1 AND COALESCE(nt.xoa, 0) = 0
			AND nt.ngayrv BETWEEN $1 AND $2
			AND (nt.ravien = 4 OR nt.maxt = '03')
	`
	args := []interface{}{from, to}
	if madv != "" && madv != "ALL" {
		args = append(args, madv)
		sqlWhere += fmt.Sprintf(" AND nt.madv = $%d ", len(args))
	}

	sqlStr := fmt.Sprintf(`
		SELECT 
			nt.mabn, 
			nt.maba, 
			bn.holot || ' ' || bn.ten AS hoten,
			COALESCE(bn.diachi, '') AS diachi,
			to_char(nt.ngayvv, 'DD/MM/YYYY HH24:MI') AS ngayvv,
			to_char(nt.ngayrv, 'DD/MM/YYYY HH24:MI') AS ngayrv,
			COALESCE(dv.tendv, nt.madv) AS tendv,
			COALESCE(nt.kqcdoan, '') AS chandoan_chinh,
			COALESCE(nt.kqcdoanp, '') AS chandoan_phu,
			COALESCE(cls_sub.ketqua_cls, 'Chưa có kết quả') AS ketqua_cls,
			COALESCE(gb.ghichu, nt.ghichu, '') AS ghichu
		FROM current.bnnoitru nt
		JOIN current.dmbenhnhan bn ON nt.mabn = bn.mabn
		LEFT JOIN current.dmdonvi dv ON nt.madv = dv.madv
		LEFT JOIN current.giaoban_ghichu gb ON nt.maba = gb.maba AND gb.loai_danhsach = 'CHUYEN_VIEN'
		LEFT JOIN LATERAL (
			SELECT string_agg(DISTINCT cls.tencls || COALESCE(': ' || NULLIF(ha.ketluan,''), ''), '; ') AS ketqua_cls
			FROM current.chidinhcls ls
			JOIN current.dmcls cls ON ls.macls = cls.macls
			LEFT JOIN current.pskhamha ha ON ls.mabn = ha.mabn AND ls.macls = ha.macls AND ls.makb = ha.makb
			WHERE ls.mabn = nt.mabn AND ls.makb = nt.makb AND COALESCE(ls.xoa, 0) = 0
		) cls_sub ON true
		%s
		ORDER BY nt.ngayrv DESC
		LIMIT 100;
	`, sqlWhere)

	rows, err := conn.Query(sqlStr, args...)
	if err != nil {
		w.WriteHeader(http.StatusInternalServerError)
		_ = json.NewEncoder(w).Encode(map[string]interface{}{"success": false, "message": err.Error()})
		return
	}
	defer rows.Close()

	list := make([]map[string]interface{}, 0)
	for rows.Next() {
		var mabn, maba, hoten, diachi, ngayvv, ngayrv, tendv, cdChinh, cdPhu, cls, ghichu string
		_ = rows.Scan(&mabn, &maba, &hoten, &diachi, &ngayvv, &ngayrv, &tendv, &cdChinh, &cdPhu, &cls, &ghichu)
		list = append(list, map[string]interface{}{
			"mabn": mabn, "maba": maba, "hoten": hoten, "diachi": diachi,
			"ngayvv": ngayvv, "ngayrv": ngayrv, "tendv": tendv,
			"chandoan_chinh": cdChinh, "chandoan_phu": cdPhu,
			"ketqua_cls": db.CleanRTF(cls), "ghichu": ghichu,
		})
	}

	_ = json.NewEncoder(w).Encode(map[string]interface{}{
		"success": true,
		"data":    list,
	})
}

// GET /api/danh-sach-theo-doi
func HandleDanhSachTheoDoi(w http.ResponseWriter, r *http.Request) {
	if !checkLicense(w) {
		return
	}
	w.Header().Set("Content-Type", "application/json; charset=utf-8")
	q := r.URL.Query()
	database := q.Get("database")
	from := q.Get("from")
	to := q.Get("to")
	madv := q.Get("madv")

	if from == "" {
		from = "2026-03-01 00:00:00"
	}
	if to == "" {
		to = "2026-03-31 23:59:59"
	}

	conn, err := db.GetDB(database)
	if err != nil {
		w.WriteHeader(http.StatusInternalServerError)
		_ = json.NewEncoder(w).Encode(map[string]interface{}{"success": false, "message": err.Error()})
		return
	}
	db.EnsureTable(database)

	sqlWhere := `
		WHERE nt.namvien = 1 AND COALESCE(nt.xoa, 0) = 0
			AND (nt.ngayvv <= $2 AND (nt.ravien = 0 OR nt.ngayrv >= $1))
			AND (
				COALESCE(nt.benhnang, 0) = 1 OR 
				COALESCE(nt.mabatheodoi, '') != '' OR 
				nt.chamsoc ILIKE '%cấp I%' OR 
				nt.chamsoc ILIKE '%cấp 1%' OR 
				nt.chamsoc IN ('1', 'CS I', 'CSI') OR
				nt.madv IN ('17', '29')
			)
	`
	args := []interface{}{from, to}
	if madv != "" && madv != "ALL" {
		args = append(args, madv)
		sqlWhere += fmt.Sprintf(" AND nt.madv = $%d ", len(args))
	}

	sqlStr := fmt.Sprintf(`
		SELECT 
			nt.mabn, 
			nt.maba, 
			bn.holot || ' ' || bn.ten AS hoten,
			COALESCE(bn.diachi, '') AS diachi,
			to_char(nt.ngayvv, 'DD/MM/YYYY HH24:MI') AS ngayvv,
			to_char(nt.ngayrv, 'DD/MM/YYYY HH24:MI') AS ngayrv,
			COALESCE(dv.tendv, nt.madv) AS tendv,
			COALESCE(nt.kqcdoan, '') AS chandoan_chinh,
			COALESCE(nt.kqcdoanp, '') AS chandoan_phu,
			COALESCE(nt.chamsoc, 'Theo dõi đặc biệt') AS chamsoc,
			COALESCE(cls_sub.ketqua_cls, 'Chưa có kết quả') AS ketqua_cls,
			COALESCE(gb.ghichu, nt.ghichu, '') AS ghichu
		FROM current.bnnoitru nt
		JOIN current.dmbenhnhan bn ON nt.mabn = bn.mabn
		LEFT JOIN current.dmdonvi dv ON nt.madv = dv.madv
		LEFT JOIN current.giaoban_ghichu gb ON nt.maba = gb.maba AND gb.loai_danhsach = 'THEO_DOI'
		LEFT JOIN LATERAL (
			SELECT string_agg(DISTINCT cls.tencls || COALESCE(': ' || NULLIF(ha.ketluan,''), ''), '; ') AS ketqua_cls
			FROM current.chidinhcls ls
			JOIN current.dmcls cls ON ls.macls = cls.macls
			LEFT JOIN current.pskhamha ha ON ls.mabn = ha.mabn AND ls.macls = ha.macls AND ls.makb = ha.makb
			WHERE ls.mabn = nt.mabn AND ls.makb = nt.makb AND COALESCE(ls.xoa, 0) = 0
		) cls_sub ON true
		%s
		ORDER BY nt.ngayvv DESC
		LIMIT 100;
	`, sqlWhere)

	rows, err := conn.Query(sqlStr, args...)
	if err != nil {
		w.WriteHeader(http.StatusInternalServerError)
		_ = json.NewEncoder(w).Encode(map[string]interface{}{"success": false, "message": err.Error()})
		return
	}
	defer rows.Close()

	list := make([]map[string]interface{}, 0)
	for rows.Next() {
		var mabn, maba, hoten, diachi, ngayvv, ngayrv, tendv, cdChinh, cdPhu, chamsoc, cls, ghichu string
		_ = rows.Scan(&mabn, &maba, &hoten, &diachi, &ngayvv, &ngayrv, &tendv, &cdChinh, &cdPhu, &chamsoc, &cls, &ghichu)
		list = append(list, map[string]interface{}{
			"mabn": mabn, "maba": maba, "hoten": hoten, "diachi": diachi,
			"ngayvv": ngayvv, "ngayrv": ngayrv, "tendv": tendv,
			"chandoan_chinh": cdChinh, "chandoan_phu": cdPhu,
			"chamsoc": chamsoc, "ketqua_cls": db.CleanRTF(cls), "ghichu": ghichu,
		})
	}

	_ = json.NewEncoder(w).Encode(map[string]interface{}{
		"success": true,
		"data":    list,
	})
}

// POST /api/cap-nhat-ghi-chu
func HandleCapNhatGhiChu(w http.ResponseWriter, r *http.Request) {
	if !checkLicense(w) {
		return
	}
	w.Header().Set("Content-Type", "application/json; charset=utf-8")
	var body struct {
		Maba     string `json:"maba"`
		Mabn     string `json:"mabn"`
		Loai     string `json:"loai"`
		Ghichu   string `json:"ghichu"`
		Database string `json:"database"`
	}
	if err := json.NewDecoder(r.Body).Decode(&body); err != nil || body.Maba == "" || body.Loai == "" {
		w.WriteHeader(http.StatusBadRequest)
		_ = json.NewEncoder(w).Encode(map[string]interface{}{"success": false, "message": "Thiếu thông tin maba hoặc loai danh sách"})
		return
	}

	conn, err := db.GetDB(body.Database)
	if err != nil {
		w.WriteHeader(http.StatusInternalServerError)
		_ = json.NewEncoder(w).Encode(map[string]interface{}{"success": false, "message": err.Error()})
		return
	}
	db.EnsureTable(body.Database)

	sqlUpsert := `
		INSERT INTO current.giaoban_ghichu (maba, mabn, loai_danhsach, ghichu, ngay_capnhat)
		VALUES ($1, $2, $3, $4, NOW())
		ON CONFLICT (maba, loai_danhsach)
		DO UPDATE SET ghichu = EXCLUDED.ghichu, ngay_capnhat = NOW();
	`
	_, err = conn.Exec(sqlUpsert, body.Maba, body.Mabn, body.Loai, body.Ghichu)
	if err != nil {
		w.WriteHeader(http.StatusInternalServerError)
		_ = json.NewEncoder(w).Encode(map[string]interface{}{"success": false, "message": err.Error()})
		return
	}

	// Try sync to bnnoitru.ghichu
	_, _ = conn.Exec("UPDATE current.bnnoitru SET ghichu = $1 WHERE maba = $2;", body.Ghichu, body.Maba)

	_ = json.NewEncoder(w).Encode(map[string]interface{}{
		"success": true,
		"message": "Đã lưu ghi chú thành công!",
	})
}