package handlers

import (
	"database/sql"
	"encoding/json"
	"net/http"
	"strings"

	"report_go/internal/db"
)

type TrendRow struct {
	Label string `json:"label"`
	Total int64  `json:"total"`
}

type ProvinceRow struct {
	Province string `json:"province"`
	Total    int64  `json:"total"`
}

type SLARow struct {
	Label string `json:"label"`
	Total int64  `json:"total"`
}

type AnalyticsResponse struct {
	Ok        bool          `json:"ok"`
	Trend     []TrendRow    `json:"trend"`
	Provinces []ProvinceRow `json:"provinces"`
	SLA       []SLARow      `json:"sla"`
	Message   string        `json:"message,omitempty"`
}

func CaseAnalyticsHandler(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json; charset=utf-8")

	conn := db.GetDB()
	if conn == nil {
		_ = json.NewEncoder(w).Encode(AnalyticsResponse{
			Ok:      false,
			Message: "ไม่สามารถเชื่อมต่อฐานข้อมูลได้",
		})
		return
	}

	dateFrom := strings.TrimSpace(r.URL.Query().Get("date_from"))
	dateTo := strings.TrimSpace(r.URL.Query().Get("date_to"))
	groupBy := strings.TrimSpace(r.URL.Query().Get("group_by")) // day | month | year

	if groupBy == "" {
		groupBy = "day"
	}

	trendSQL := buildTrendSQL(groupBy)

	trendRows, err := conn.Query(trendSQL, dateFrom, dateTo)
	if err != nil {
		_ = json.NewEncoder(w).Encode(AnalyticsResponse{
			Ok:      false,
			Message: "โหลดข้อมูลกราฟแนวโน้มไม่สำเร็จ: " + err.Error(),
		})
		return
	}
	defer trendRows.Close()

	var trend []TrendRow
	for trendRows.Next() {
		var row TrendRow
		if err := trendRows.Scan(&row.Label, &row.Total); err != nil {
			_ = json.NewEncoder(w).Encode(AnalyticsResponse{
				Ok:      false,
				Message: "อ่านข้อมูลกราฟแนวโน้มไม่สำเร็จ: " + err.Error(),
			})
			return
		}
		trend = append(trend, row)
	}

	provinceSQL := `
SELECT 
	COALESCE(NULLIF(TRIM(province_name), ''), 'ไม่ระบุจังหวัด') AS province,
	COUNT(*) AS total
FROM service_cases
WHERE reported_at BETWEEN ? AND ?
GROUP BY province
ORDER BY total DESC
LIMIT 10
`
	provinceRows, err := conn.Query(provinceSQL, dateFrom, dateTo)
	if err != nil {
		_ = json.NewEncoder(w).Encode(AnalyticsResponse{
			Ok:      false,
			Message: "โหลดข้อมูลจังหวัดไม่สำเร็จ: " + err.Error(),
		})
		return
	}
	defer provinceRows.Close()

	var provinces []ProvinceRow
	for provinceRows.Next() {
		var row ProvinceRow
		if err := provinceRows.Scan(&row.Province, &row.Total); err != nil {
			_ = json.NewEncoder(w).Encode(AnalyticsResponse{
				Ok:      false,
				Message: "อ่านข้อมูลจังหวัดไม่สำเร็จ: " + err.Error(),
			})
			return
		}
		provinces = append(provinces, row)
	}

	slaSQL := `
SELECT 
	CASE
		WHEN completed_at IS NULL THEN 'ยังไม่ปิดเคส'
		WHEN TIMESTAMPDIFF(MINUTE, reported_at, completed_at) <= 30 THEN '≤ 30 นาที'
		WHEN TIMESTAMPDIFF(MINUTE, reported_at, completed_at) BETWEEN 31 AND 60 THEN '31-60 นาที'
		WHEN TIMESTAMPDIFF(MINUTE, reported_at, completed_at) BETWEEN 61 AND 120 THEN '61-120 นาที'
		ELSE '> 120 นาที'
	END AS sla_label,
	COUNT(*) AS total
FROM service_cases
WHERE reported_at BETWEEN ? AND ?
GROUP BY sla_label
ORDER BY 
	CASE sla_label
		WHEN '≤ 30 นาที' THEN 1
		WHEN '31-60 นาที' THEN 2
		WHEN '61-120 นาที' THEN 3
		WHEN '> 120 นาที' THEN 4
		WHEN 'ยังไม่ปิดเคส' THEN 5
		ELSE 99
	END
`
	slaRows, err := conn.Query(slaSQL, dateFrom, dateTo)
	if err != nil {
		_ = json.NewEncoder(w).Encode(AnalyticsResponse{
			Ok:      false,
			Message: "โหลดข้อมูล SLA ไม่สำเร็จ: " + err.Error(),
		})
		return
	}
	defer slaRows.Close()

	var sla []SLARow
	for slaRows.Next() {
		var row SLARow
		if err := slaRows.Scan(&row.Label, &row.Total); err != nil {
			_ = json.NewEncoder(w).Encode(AnalyticsResponse{
				Ok:      false,
				Message: "อ่านข้อมูล SLA ไม่สำเร็จ: " + err.Error(),
			})
			return
		}
		sla = append(sla, row)
	}

	_ = json.NewEncoder(w).Encode(AnalyticsResponse{
		Ok:        true,
		Trend:     trend,
		Provinces: provinces,
		SLA:       sla,
	})
}

func buildTrendSQL(groupBy string) string {
	switch groupBy {
	case "month":
		return `
SELECT 
	DATE_FORMAT(reported_at, '%Y-%m') AS label,
	COUNT(*) AS total
FROM service_cases
WHERE reported_at BETWEEN ? AND ?
GROUP BY DATE_FORMAT(reported_at, '%Y-%m')
ORDER BY label
`
	case "year":
		return `
SELECT 
	DATE_FORMAT(reported_at, '%Y') AS label,
	COUNT(*) AS total
FROM service_cases
WHERE reported_at BETWEEN ? AND ?
GROUP BY DATE_FORMAT(reported_at, '%Y')
ORDER BY label
`
	default:
		return `
SELECT 
	DATE_FORMAT(reported_at, '%Y-%m-%d') AS label,
	COUNT(*) AS total
FROM service_cases
WHERE reported_at BETWEEN ? AND ?
GROUP BY DATE_FORMAT(reported_at, '%Y-%m-%d')
ORDER BY label
`
	}
}

// กัน import sql unused ในบางโปรเจกต์ที่เปิด strict build
var _ sql.NullString