package main

import (
	"database/sql"
	"fmt"
	"html/template"
	"io"
	"log"
	"mime/multipart"
	"net/http"
	"os"
	"path/filepath"
	"sort"
	"strconv"
	"strings"
	"time"

	"encoding/json"

	_ "github.com/denisenkom/go-mssqldb"
	"github.com/gorilla/sessions"
)

var store = sessions.NewCookieStore([]byte("ehongmd-secret-key-2025"))

type Province struct {
	PR_CODE string
	PR_NAME string
}

type Branch struct {
	PR_CODE string
	BR_NAME string
	ADDRESS string
	MOBILE  string
	GPS_N   string
	GPS_E   string
}

type Employee struct {
	EMP_ID    string
	US_NAME   string
	BARCODE   string
	US_PASSWD string
	TNAME     string
	FNAME     string
	LNAME     string
	NICK      string
}

type Promotion struct {
	PN_ID          int
	PN_NAME        string
	PN_DETAIL      string
	PN_IMG_1       string
	PN_DATE        string
	PN_TIME        string
	PN_ALLDETAIL_1 string
	PN_STATUS      string
	PN_TYPE        string
	PN_IMG_2       string
	PN_IMG_3       string
	PN_IMG_4       string
	PN_ALLDETAIL_2 string
	PN_ALLDETAIL_3 string
	PN_ALLDETAIL_4 string
	PN_STATUSCLOSE string
	US_NAME        string
	FNAME          string
	PNS_STATUSNAME string
	PNT_TYPENAME   string
	US_NAMEEDIT    string
	PN_DATEEDIT    string
	PN_TIMEEDIT    string
}

type MotorcycleFuel struct {
	PtNo            string
	PtName          string
	PdImg           string
	PdStatus        string
	FmlStatus       string
	Count           int
	BrandNo         string
	ProductTechName string
	ModelFName      string
	FmlNo           string
	BrandName       string
	ModelName       string
	Price           float64
	SerieNo         string
}

type GroupedMotorcycle struct {
	BrandName   string
	Motorcycles []MotorcycleFuel
}

type FamilyGroup struct {
	FamilyName  string
	BrandName   string
	TotalCount  int
	Motorcycles []MotorcycleFuel
}

type BrandGroup struct {
	BrandName string
	Families  []FamilyGroup
}

type NewsGroup struct {
	PNS_STATUS     string
	PNS_STATUSNAME string
}

type NewsType struct {
	PNT_TYPE     string
	PNT_TYPENAME string
}

func main() {
	// 1. กำหนดค่าการเชื่อมต่อ (Connection String)
	connStr := "server=192.168.10.61;user id=pond;password=Ehongmd2025;port=1433;database=CONFIG;encrypt=disable"

	// 2. เปิดการเชื่อมต่อ Database
	db, err := sql.Open("sqlserver", connStr)
	if err != nil {
		log.Fatal("Error opening database: ", err)
	}
	defer db.Close()

	// 3. ตรวจสอบว่าเชื่อมต่อได้จริงหรือไม่ (Ping)
	err = db.Ping()
	if err != nil {
		log.Fatal("Cannot connect to database: ", err)
	}
	fmt.Println("Successfully connected to SQL Server!")

	// 4. จัดการ Routing สำหรับ Web Server
	// Serve static files (CSS, JS, Images)
	http.Handle("/assets/", http.StripPrefix("/assets/", http.FileServer(http.Dir("assets"))))
	http.Handle("/images/", http.StripPrefix("/images/", http.FileServer(http.Dir("images"))))

	// API For Product Image Upload
	http.HandleFunc("/api/upload_product_image", func(w http.ResponseWriter, r *http.Request) {
		if r.Method != http.MethodPost {
			http.Error(w, "Method not allowed", http.StatusMethodNotAllowed)
			return
		}

		// Parse multipart form
		err := r.ParseMultipartForm(10 << 20) // 10MB max
		if err != nil {
			http.Error(w, err.Error(), http.StatusInternalServerError)
			return
		}

		file, handler, err := r.FormFile("image")
		if err != nil {
			http.Error(w, "Error retrieving image", http.StatusBadRequest)
			return
		}
		defer file.Close()

		tech := strings.TrimSpace(r.FormValue("tech"))
		brand := strings.TrimSpace(r.FormValue("brand"))
		serie := strings.TrimSpace(r.FormValue("serie"))
		fml := strings.TrimSpace(r.FormValue("fml"))
		model := strings.TrimSpace(r.FormValue("model"))

		if tech == "" || brand == "" || serie == "" || fml == "" {
			http.Error(w, "Missing required parameters", http.StatusBadRequest)
			return
		}

		// Create filename (sanitize)
		ext := filepath.Ext(handler.Filename)
		// Include model in filename for better uniqueness
		cleanModel := strings.ReplaceAll(model, " ", "_")
		newFilename := fmt.Sprintf("pd_%s_%s_%s_%s_%s%s", brand, serie, fml, tech, cleanModel, ext)
		newFilename = strings.ReplaceAll(newFilename, " ", "_")

		// Sanitize for Windows filename restrictions (\ / : * ? " < > |)
		invalidChars := []string{"\\", "/", ":", "*", "?", "\"", "<", ">", "|"}
		for _, char := range invalidChars {
			newFilename = strings.ReplaceAll(newFilename, char, "_")
		}

		dstPath := filepath.Join("images", newFilename)

		// Create images directory if not exists
		if _, err := os.Stat("images"); os.IsNotExist(err) {
			os.Mkdir("images", 0755)
		}

		// Save file
		dst, err := os.Create(dstPath)
		if err != nil {
			http.Error(w, "Error saving image", http.StatusInternalServerError)
			return
		}
		defer dst.Close()

		if _, err := io.Copy(dst, file); err != nil {
			http.Error(w, "Error copying image", http.StatusInternalServerError)
			return
		}

		// Update database with UPSERT logic on CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE
		// Key fields: PRODUCT_TECHNAME, BRAND_NO, SERIE_NO
		upsertSQL := `
			IF EXISTS (SELECT 1 FROM CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE WHERE RTRIM(PRODUCT_TECHNAME) = RTRIM(@p1) AND RTRIM(BRAND_NO) = RTRIM(@p2) AND RTRIM(SERIE_NO) = RTRIM(@p3))
			BEGIN
				UPDATE CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE 
				SET PD_IMG = @p4 
				WHERE RTRIM(PRODUCT_TECHNAME) = RTRIM(@p1) AND RTRIM(BRAND_NO) = RTRIM(@p2) AND RTRIM(SERIE_NO) = RTRIM(@p3)
			END
			ELSE
			BEGIN
				INSERT INTO CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE (PRODUCT_TECHNAME, BRAND_NO, SERIE_NO, PD_STATUS, PD_IMG)
				VALUES (@p1, @p2, @p3, 'O', @p4)
			END
		`
		_, err = db.Exec(upsertSQL, tech, brand, serie, newFilename)

		if err != nil {
			log.Printf("❌ Upload DB Error (Upsert): %v", err)
			http.Error(w, "Database update failed", http.StatusInternalServerError)
			return
		}

		log.Printf("✅ Uploaded image for %s: %s", tech, newFilename)
		w.Header().Set("Content-Type", "application/json")
		fmt.Fprintf(w, `{"status":"success", "filename":"%s"}`, newFilename)
	})

	// API For Product Type Update (Vehicle Type)
	http.HandleFunc("/api/update_product_type", func(w http.ResponseWriter, r *http.Request) {
		if r.Method != http.MethodPost {
			http.Error(w, "Method not allowed", http.StatusMethodNotAllowed)
			return
		}
		tech := strings.TrimSpace(r.FormValue("tech"))
		brand := strings.TrimSpace(r.FormValue("brand"))
		serie := strings.TrimSpace(r.FormValue("serie"))
		fml := strings.TrimSpace(r.FormValue("fml"))
		ptNo := strings.TrimSpace(r.FormValue("pt_no"))
		// model := strings.TrimSpace(r.FormValue("model")) // Not used in current UPSERT requirement but available

		if tech == "" || brand == "" || serie == "" || fml == "" || ptNo == "" {
			http.Error(w, "Missing required parameters", http.StatusBadRequest)
			return
		}

		// UPSERT logic on CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE
		// Key fields: PRODUCT_TECHNAME, BRAND_NO, SERIE_NO
		upsertSQL := `
			IF EXISTS (SELECT 1 FROM CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE WHERE RTRIM(PRODUCT_TECHNAME) = RTRIM(@p1) AND RTRIM(BRAND_NO) = RTRIM(@p2) AND RTRIM(SERIE_NO) = RTRIM(@p3))
			BEGIN
				UPDATE CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE 
				SET PT_NO = @p4 
				WHERE RTRIM(PRODUCT_TECHNAME) = RTRIM(@p1) AND RTRIM(BRAND_NO) = RTRIM(@p2) AND RTRIM(SERIE_NO) = RTRIM(@p3)
			END
			ELSE
			BEGIN
				INSERT INTO CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE (PRODUCT_TECHNAME, BRAND_NO, SERIE_NO, PD_STATUS, PT_NO)
				VALUES (@p1, @p2, @p3, 'O', @p4)
			END
		`
		_, err := db.Exec(upsertSQL, tech, brand, serie, ptNo)

		if err != nil {
			log.Printf("❌ Update Type Error (Upsert): %v", err)
			w.Header().Set("Content-Type", "application/json")
			fmt.Fprintf(w, `{"success":false, "message":"%v"}`, err)
			return
		}

		log.Printf("✅ Updated type (UPSERT) for %s to %s", tech, ptNo)
		w.Header().Set("Content-Type", "application/json")
		fmt.Fprintf(w, `{"success":true}`)
	})

	// API For Bulk Product Type Update
	http.HandleFunc("/api/bulk_update_product_type", func(w http.ResponseWriter, r *http.Request) {
		if r.Method != http.MethodPost {
			http.Error(w, "Method not allowed", http.StatusMethodNotAllowed)
			return
		}

		var req struct {
			Products []struct {
				Tech  string `json:"tech"`
				Brand string `json:"brand"`
				Serie string `json:"serie"`
				FML   string `json:"fml"`
			} `json:"products"`
			PtNo string `json:"pt_no"`
		}
		if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
			log.Printf("❌ JSON Decode Error (Bulk Type): %v", err)
			http.Error(w, "Invalid request: "+err.Error(), http.StatusBadRequest)
			return
		}
		if len(req.Products) == 0 || req.PtNo == "" {
			http.Error(w, "Missing required parameters", http.StatusBadRequest)
			return
		}

		tx, err := db.Begin()
		if err != nil {
			log.Printf("❌ Transaction Begin Error: %v", err)
			w.Header().Set("Content-Type", "application/json")
			fmt.Fprintf(w, `{"success":false, "message":"%v"}`, err)
			return
		}

		// UPSERT logic for Bulk
		upsertSQL := `
			IF EXISTS (SELECT 1 FROM CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE WHERE RTRIM(PRODUCT_TECHNAME) = RTRIM(@p1) AND RTRIM(BRAND_NO) = RTRIM(@p2) AND RTRIM(SERIE_NO) = RTRIM(@p3))
			BEGIN
				UPDATE CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE 
				SET PT_NO = @p4 
				WHERE RTRIM(PRODUCT_TECHNAME) = RTRIM(@p1) AND RTRIM(BRAND_NO) = RTRIM(@p2) AND RTRIM(SERIE_NO) = RTRIM(@p3)
			END
			ELSE
			BEGIN
				INSERT INTO CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE (PRODUCT_TECHNAME, BRAND_NO, SERIE_NO, PD_STATUS, PT_NO)
				VALUES (@p1, @p2, @p3, 'O', @p4)
			END
		`

		stmt, err := tx.Prepare(upsertSQL)
		if err != nil {
			tx.Rollback()
			log.Printf("❌ Prepare Statement Error: %v", err)
			w.Header().Set("Content-Type", "application/json")
			fmt.Fprintf(w, `{"success":false, "message":"%v"}`, err)
			return
		}
		defer stmt.Close()

		for _, p := range req.Products {
			tTech := strings.TrimSpace(p.Tech)
			tBrand := strings.TrimSpace(p.Brand)
			tSerie := strings.TrimSpace(p.Serie)
			_, err := stmt.Exec(tTech, tBrand, tSerie, req.PtNo)
			if err != nil {
				tx.Rollback()
				log.Printf("❌ Bulk Update Exec Error: %v", err)
				w.Header().Set("Content-Type", "application/json")
				fmt.Fprintf(w, `{"success":false, "message":"%v"}`, err)
				return
			}
		}

		if err := tx.Commit(); err != nil {
			log.Printf("❌ Transaction Commit Error: %v", err)
			w.Header().Set("Content-Type", "application/json")
			fmt.Fprintf(w, `{"success":false, "message":"%v"}`, err)
			return
		}

		log.Printf("✅ Bulk updated type (UPSERT) for %d products to %s", len(req.Products), req.PtNo)
		w.Header().Set("Content-Type", "application/json")
		fmt.Fprintf(w, `{"success":true}`)
	})

	// API For Branches
	http.HandleFunc("/api/branches", func(w http.ResponseWriter, r *http.Request) {
		prCode := r.URL.Query().Get("pr_code")
		if prCode == "" {
			http.Error(w, "pr_code is required", http.StatusBadRequest)
			return
		}

		rows, err := db.Query(`
			SELECT PR_CODE, BR_NAME, ADDRESS, MOBILE, gps_n, gps_e 
			FROM EHONGDB.dbo.BRANCH 
			WHERE PR_CODE = @p1 
			AND MOBILE IS NOT NULL AND gps_n IS NOT NULL AND gps_e IS NOT NULL AND ADDRESS IS NOT NULL
		`, prCode)
		if err != nil {
			log.Printf("Error fetching branches for prCode %s: %v", prCode, err)
			http.Error(w, err.Error(), http.StatusInternalServerError)
			return
		}
		defer rows.Close()

		var branches []Branch
		for rows.Next() {
			var b Branch
			if err := rows.Scan(&b.PR_CODE, &b.BR_NAME, &b.ADDRESS, &b.MOBILE, &b.GPS_N, &b.GPS_E); err != nil {
				continue
			}
			branches = append(branches, b)
		}

		log.Printf("Fetched %d branches for prCode %s", len(branches), prCode)

		w.Header().Set("Content-Type", "application/json")
		jsonData, _ := json.Marshal(branches)
		log.Printf("Sending JSON: %s", string(jsonData))
		w.Write(jsonData)
	})

	// Login Handler
	http.HandleFunc("/login", func(w http.ResponseWriter, r *http.Request) {
		if r.Method != http.MethodPost {
			http.Redirect(w, r, "/backend_setting/index.html", http.StatusSeeOther)
			return
		}

		user := r.FormValue("USER")
		pass := r.FormValue("PASS")

		log.Printf("Login attempt for user: %s", user)

		var emp Employee
		err := db.QueryRow(`
			SELECT EMP_ID, US_NAME, BARCODE, US_PASSWD, TNAME, FNAME, LNAME, NICK 
			FROM EHONGDB.dbo.EMPLOYEE 
			WHERE EM_STATUS = 'NM' 
			AND (US_NAME = @p1 OR BARCODE = @p2) 
			AND US_PASSWD = @p3
		`, user, user, pass).Scan(&emp.EMP_ID, &emp.US_NAME, &emp.BARCODE, &emp.US_PASSWD, &emp.TNAME, &emp.FNAME, &emp.LNAME, &emp.NICK)

		if err != nil {
			if err == sql.ErrNoRows {
				log.Printf("Login failed for user: %s (Invalid credentials)", user)
				http.Redirect(w, r, "/backend_setting/index.html?error=invalid", http.StatusSeeOther)
			} else {
				log.Printf("Database error during login: %v", err)
				http.Error(w, "Database error", http.StatusInternalServerError)
			}
			return
		}

		log.Printf("Login successful for: %s (%s %s)", emp.US_NAME, emp.FNAME, emp.LNAME)

		// Create session
		session, _ := store.Get(r, "ehong-session")
		session.Values["user_id"] = emp.EMP_ID
		session.Values["user_name"] = emp.US_NAME
		session.Values["full_name"] = emp.FNAME + " " + emp.LNAME
		session.Save(r, w)

		// Redirect to main.html
		http.Redirect(w, r, "/backend_setting/main.html", http.StatusSeeOther)
	})

	// Promotion Save Handler
	http.HandleFunc("/api/promotion/save", func(w http.ResponseWriter, r *http.Request) {
		if r.Method != http.MethodPost {
			http.Error(w, "Method not allowed", http.StatusMethodNotAllowed)
			return
		}

		// Parse multipart form
		err := r.ParseMultipartForm(32 << 20) // 32MB max
		if err != nil {
			log.Printf("ParseMultipartForm error: %v", err)
			http.Error(w, err.Error(), http.StatusInternalServerError)
			return
		}

		pnName := r.FormValue("PN_NAME")
		pnDate := r.FormValue("PN_DATE")
		pnTime := r.FormValue("PN_TIME")
		pnStatus := r.FormValue("PN_STATUS")
		pnStatusClose := r.FormValue("PN_STATUSCLOSE")
		usName := r.FormValue("US_NAME")

		// Try both with and without brackets just in case
		files := r.MultipartForm.File["PN_IMAGES"]
		if len(files) == 0 {
			files = r.MultipartForm.File["PN_IMAGES[]"]
		}

		log.Printf("Received %d files for PN_IMAGES", len(files))
		if len(files) == 0 {
			w.Header().Set("Content-Type", "application/json")
			json.NewEncoder(w).Encode(map[string]interface{}{
				"success": false,
				"message": "กรุณาเลือกรูปภาพอย่างน้อย 1 รูป",
			})
			return
		}

		// Ensure directory exists
		uploadDir := filepath.Join("images", "promotions")
		os.MkdirAll(uploadDir, os.ModePerm)

		successCount := 0
		for i, fileHeader := range files {
			// Use closure to handle defer in loop
			func(idx int, fh *multipart.FileHeader) {
				file, err := fh.Open()
				if err != nil {
					log.Printf("Error opening file %d: %v", idx, err)
					return
				}
				defer file.Close()

				// Create local file
				filename := fmt.Sprintf("%d_%d_%s", time.Now().UnixNano(), idx, fh.Filename)
				filePath := filepath.Join(uploadDir, filename)
				dst, err := os.Create(filePath)
				if err != nil {
					log.Printf("Error creating file %s: %v", filePath, err)
					return
				}
				defer dst.Close()

				// Copy content
				if _, err := io.Copy(dst, file); err != nil {
					log.Printf("Error copying file %s: %v", filePath, err)
					return
				}

				// Insert into database
				// PN_NAME, PN_IMG_1, PN_DATE, PN_TIME, PN_STATUS, PN_STATUSCLOSE, US_NAME
				_, err = db.Exec(`
					INSERT INTO CONFIG.dbo.NEWEMD_PROMOTION_NEWS 
					(PN_NAME, PN_IMG_1, PN_DATE, PN_TIME, PN_STATUS, PN_STATUSCLOSE, US_NAME) 
					VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7)
				`, pnName, filename, pnDate, pnTime, pnStatus, pnStatusClose, usName)

				if err == nil {
					successCount++
				} else {
					log.Printf("Error inserting database row %d: %v", idx, err)
				}
			}(i, fileHeader)
		}

		w.Header().Set("Content-Type", "application/json")
		if successCount > 0 {
			json.NewEncoder(w).Encode(map[string]interface{}{
				"success": true,
				"message": fmt.Sprintf("บันทึกข้อมูลเสร็จแล้ว %d รายการ", successCount),
			})
		} else {
			json.NewEncoder(w).Encode(map[string]interface{}{
				"success": false,
				"message": "ไม่สามารถบันทึกข้อมูลได้ กรุณาลองใหม่",
			})
		}
	})

	// News Save Handler (Handles up to 4 images)
	http.HandleFunc("/api/news/save", func(w http.ResponseWriter, r *http.Request) {
		if r.Method != http.MethodPost {
			http.Error(w, "Method not allowed", http.StatusMethodNotAllowed)
			return
		}

		err := r.ParseMultipartForm(32 << 20)
		if err != nil {
			http.Error(w, err.Error(), http.StatusInternalServerError)
			return
		}

		pnName := r.FormValue("PN_NAME")
		if len(pnName) > 255 {
			pnName = pnName[:255]
		}
		pnDetail := r.FormValue("PN_DETAIL")
		pnDate := r.FormValue("PN_DATE")
		pnTime := r.FormValue("PN_TIME")
		pnStatus := r.FormValue("PN_STATUS")
		pnType := r.FormValue("PN_TYPE")
		usName := r.FormValue("US_NAME")
		pnAllDetail1 := r.FormValue("PN_ALLDETAIL_1")
		pnAllDetail2 := r.FormValue("PN_ALLDETAIL_2")
		pnAllDetail3 := r.FormValue("PN_ALLDETAIL_3")
		pnAllDetail4 := r.FormValue("PN_ALLDETAIL_4")
		pnStatusClose := r.FormValue("PN_STATUSCLOSE")

		files := r.MultipartForm.File["PN_IMAGES"]
		if len(files) == 0 {
			files = r.MultipartForm.File["PN_IMAGES[]"]
		}

		if len(files) > 4 {
			w.Header().Set("Content-Type", "application/json")
			json.NewEncoder(w).Encode(map[string]interface{}{
				"success": false,
				"message": "สามารถอัพโหลดรูปภาพได้สูงสุด 4 รูปเท่านั้น",
			})
			return
		}

		uploadDir := filepath.Join("images", "news")
		os.MkdirAll(uploadDir, os.ModePerm)

		imgNames := make([]string, 4)
		for i, fileHeader := range files {
			if i >= 4 {
				break
			}
			file, err := fileHeader.Open()
			if err != nil {
				continue
			}
			defer file.Close()

			originalName := fileHeader.Filename
			// Ensure total filename (news_TIMESTAMP_i_original) does not exceed 100 chars
			// Base: "news_" (5) + TIMESTAMP (approx 20) + "_" (1) + i (1) + "_" (1) = ~28 chars
			// Allowance for original name is roughly 70 chars
			prefix := fmt.Sprintf("news_%d_%d_", time.Now().UnixNano(), i)
			maxOrgLen := 100 - len(prefix)
			if len(originalName) > maxOrgLen {
				ext := filepath.Ext(originalName)
				nameOnly := strings.TrimSuffix(originalName, ext)
				if len(nameOnly) > maxOrgLen-len(ext) {
					nameOnly = nameOnly[:maxOrgLen-len(ext)]
				}
				originalName = nameOnly + ext
			}

			filename := prefix + originalName
			filePath := filepath.Join(uploadDir, filename)
			dst, err := os.Create(filePath)
			if err != nil {
				continue
			}
			defer dst.Close()

			if _, err := io.Copy(dst, file); err == nil {
				imgNames[i] = filename
			}
		}

		_, err = db.Exec(`
			INSERT INTO CONFIG.dbo.NEWEMD_PROMOTION_NEWS 
			(PN_NAME, PN_DETAIL, PN_IMG_1, PN_DATE, PN_TIME, PN_ALLDETAIL_1, PN_STATUS, PN_TYPE, PN_IMG_2, PN_IMG_3, PN_IMG_4, PN_ALLDETAIL_2, PN_ALLDETAIL_3, PN_ALLDETAIL_4, PN_STATUSCLOSE, US_NAME) 
			VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16)
		`, pnName, pnDetail, imgNames[0], pnDate, pnTime, pnAllDetail1, pnStatus, pnType, imgNames[1], imgNames[2], imgNames[3], pnAllDetail2, pnAllDetail3, pnAllDetail4, pnStatusClose, usName)

		w.Header().Set("Content-Type", "application/json")
		if err != nil {
			log.Printf("❌ Error saving news: %v", err)
			json.NewEncoder(w).Encode(map[string]interface{}{"success": false, "message": "เกิดข้อผิดพลาดในการบันทึกข้อมูล"})
		} else {
			json.NewEncoder(w).Encode(map[string]interface{}{"success": true, "message": "บันทึกข้อมูลข่าวเสร็จเรียบร้อยแล้ว"})
		}
	})

	// News Update Handler
	http.HandleFunc("/api/news/update", func(w http.ResponseWriter, r *http.Request) {
		if r.Method != http.MethodPost {
			http.Error(w, "Method not allowed", http.StatusMethodNotAllowed)
			return
		}

		err := r.ParseMultipartForm(32 << 20)
		if err != nil {
			http.Error(w, err.Error(), http.StatusInternalServerError)
			return
		}

		pnIdRaw := r.FormValue("PN_ID")
		pnId, _ := strconv.Atoi(pnIdRaw)
		pnName := r.FormValue("PN_NAME")
		if len(pnName) > 255 {
			pnName = pnName[:255]
		}
		pnDetail := r.FormValue("PN_DETAIL")
		pnStatus := r.FormValue("PN_STATUS")
		pnType := r.FormValue("PN_TYPE")
		pnAllDetail1 := r.FormValue("PN_ALLDETAIL_1")
		pnAllDetail2 := r.FormValue("PN_ALLDETAIL_2")
		pnAllDetail3 := r.FormValue("PN_ALLDETAIL_3")
		pnAllDetail4 := r.FormValue("PN_ALLDETAIL_4")
		usNameEdit := r.FormValue("US_NAMEEDIT")
		pnDateEdit := r.FormValue("PN_DATEEDIT")
		pnTimeEdit := r.FormValue("PN_TIMEEDIT")

		uploadDir := filepath.Join("images", "news")
		os.MkdirAll(uploadDir, os.ModePerm)

		// Start building dynamic query
		query := "UPDATE CONFIG.dbo.NEWEMD_PROMOTION_NEWS SET PN_NAME=@p1, PN_DETAIL=@p2, PN_STATUS=@p3, PN_TYPE=@p4, PN_ALLDETAIL_1=@p5, PN_ALLDETAIL_2=@p6, PN_ALLDETAIL_3=@p7, PN_ALLDETAIL_4=@p8, US_NAMEEDIT=@p9, PN_DATEEDIT=@p10, PN_TIMEEDIT=@p11"
		params := []interface{}{pnName, pnDetail, pnStatus, pnType, pnAllDetail1, pnAllDetail2, pnAllDetail3, pnAllDetail4, usNameEdit, pnDateEdit, pnTimeEdit}
		paramCount := 11

		// Check for image updates
		for i := 1; i <= 4; i++ {
			file, header, err := r.FormFile(fmt.Sprintf("PN_IMG_%d", i))
			if err == nil {
				defer file.Close()

				prefix := fmt.Sprintf("news_upd_%d_%d_", time.Now().UnixNano(), i)
				originalName := header.Filename
				// Truncate filename if needed
				if len(prefix)+len(originalName) > 100 {
					ext := filepath.Ext(originalName)
					maxLen := 100 - len(prefix) - len(ext)
					if maxLen > 0 {
						originalName = originalName[:maxLen] + ext
					} else {
						originalName = originalName[:5] + ext // fallback
					}
				}
				filename := prefix + originalName

				filePath := filepath.Join(uploadDir, filename)
				dst, err := os.Create(filePath)
				if err == nil {
					if _, err := io.Copy(dst, file); err == nil {
						paramCount++
						query += fmt.Sprintf(", PN_IMG_%d=@p%d", i, paramCount)
						params = append(params, filename)
					}
					dst.Close()
				}
			}
		}

		paramCount++
		query += fmt.Sprintf(" WHERE PN_ID=@p%d", paramCount)
		params = append(params, pnId)

		_, err = db.Exec(query, params...)

		w.Header().Set("Content-Type", "application/json")
		if err != nil {
			log.Printf("❌ Error updating news %d: %v", pnId, err)
			json.NewEncoder(w).Encode(map[string]interface{}{"success": false, "message": "เกิดข้อผิดพลาดในการบันทึกข้อมูล"})
		} else {
			json.NewEncoder(w).Encode(map[string]interface{}{"success": true, "message": "แก้ไขข้อมูลข่าวเรียบร้อยแล้ว"})
		}
	})

	// Promotion Image Upload Handler
	http.HandleFunc("/api/promotion/upload-image", func(w http.ResponseWriter, r *http.Request) {
		if r.Method != http.MethodPost {
			http.Error(w, "Method not allowed", http.StatusMethodNotAllowed)
			return
		}

		err := r.ParseMultipartForm(10 << 20) // 10MB limit
		if err != nil {
			http.Error(w, err.Error(), http.StatusInternalServerError)
			return
		}

		pnId := r.FormValue("id")
		file, header, err := r.FormFile("image")
		if err != nil {
			http.Error(w, "Image file is required", http.StatusBadRequest)
			return
		}
		defer file.Close()

		uploadDir := filepath.Join("images", "promotions")
		os.MkdirAll(uploadDir, os.ModePerm)

		// Create unique filename
		ext := filepath.Ext(header.Filename)
		filename := fmt.Sprintf("promo_%s_%d%s", pnId, time.Now().Unix(), ext)
		filePath := filepath.Join(uploadDir, filename)

		dst, err := os.Create(filePath)
		if err != nil {
			log.Printf("❌ Error creating file: %v", err)
			http.Error(w, "Failed to save image", http.StatusInternalServerError)
			return
		}
		defer dst.Close()

		if _, err := io.Copy(dst, file); err != nil {
			log.Printf("❌ Error copying file: %v", err)
			http.Error(w, "Failed to save image", http.StatusInternalServerError)
			return
		}

		// Update database
		_, err = db.Exec(`UPDATE CONFIG.dbo.NEWEMD_PROMOTION_NEWS SET PN_IMG_1 = @p1 WHERE PN_ID = @p2`, filename, pnId)

		w.Header().Set("Content-Type", "application/json")
		if err != nil {
			log.Printf("❌ Error updating database: %v", err)
			json.NewEncoder(w).Encode(map[string]interface{}{"status": "error", "message": "Database update failed"})
		} else {
			log.Printf("✅ Updated image for promotion %s: %s", pnId, filename)
			json.NewEncoder(w).Encode(map[string]interface{}{"status": "success", "filename": filename})
		}
	})

	// Promotion Toggle Status Handler
	http.HandleFunc("/api/promotion/toggle-status", func(w http.ResponseWriter, r *http.Request) {
		w.Header().Set("Content-Type", "application/json")
		if r.Method != http.MethodPost {
			w.WriteHeader(http.StatusMethodNotAllowed)
			json.NewEncoder(w).Encode(map[string]interface{}{"success": false, "message": "Method not allowed"})
			return
		}

		var req struct {
			ID     int    `json:"id"`
			Status string `json:"status"`
		}
		if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
			w.WriteHeader(http.StatusBadRequest)
			json.NewEncoder(w).Encode(map[string]interface{}{"success": false, "message": "Invalid request"})
			return
		}
		// Only accept 'O' or 'C'
		if req.Status != "O" && req.Status != "C" {
			json.NewEncoder(w).Encode(map[string]interface{}{"success": false, "message": "Invalid status value"})
			return
		}

		_, err := db.Exec(`UPDATE CONFIG.dbo.NEWEMD_PROMOTION_NEWS SET PN_STATUSCLOSE = @p1 WHERE PN_ID = @p2`, req.Status, req.ID)
		if err != nil {
			log.Printf("❌ Error toggling promotion status: %v", err)
			json.NewEncoder(w).Encode(map[string]interface{}{"success": false, "message": "Database error"})
			return
		}

		log.Printf("✅ Toggled promotion %d to status %s", req.ID, req.Status)
		json.NewEncoder(w).Encode(map[string]interface{}{"success": true})
	})

	// Promotion Delete Handler (Supports Bulk Delete)
	http.HandleFunc("/api/promotion/delete", func(w http.ResponseWriter, r *http.Request) {
		w.Header().Set("Content-Type", "application/json")
		if r.Method != http.MethodPost {
			w.WriteHeader(http.StatusMethodNotAllowed)
			json.NewEncoder(w).Encode(map[string]interface{}{"success": false, "message": "Method not allowed"})
			return
		}

		var req struct {
			IDs []int `json:"ids"`
			ID  int   `json:"id"` // Keep single ID support for backward compatibility if needed
		}
		if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
			w.WriteHeader(http.StatusBadRequest)
			json.NewEncoder(w).Encode(map[string]interface{}{"success": false, "message": "Invalid request"})
			return
		}

		// Consolidate IDs
		targetIDs := req.IDs
		if len(targetIDs) == 0 && req.ID != 0 {
			targetIDs = []int{req.ID}
		}

		if len(targetIDs) == 0 {
			json.NewEncoder(w).Encode(map[string]interface{}{"success": false, "message": "No IDs provided"})
			return
		}

		// Use a transaction for bulk delete
		tx, err := db.Begin()
		if err != nil {
			log.Printf("❌ Error starting transaction: %v", err)
			json.NewEncoder(w).Encode(map[string]interface{}{"success": false, "message": "Database error"})
			return
		}
		defer tx.Rollback()

		for _, id := range targetIDs {
			_, err := tx.Exec(`DELETE FROM CONFIG.dbo.NEWEMD_PROMOTION_NEWS WHERE PN_ID = @p1`, id)
			if err != nil {
				log.Printf("❌ Error deleting promotion %d: %v", id, err)
				json.NewEncoder(w).Encode(map[string]interface{}{"success": false, "message": fmt.Sprintf("Error deleting ID %d", id)})
				return
			}
		}

		if err := tx.Commit(); err != nil {
			log.Printf("❌ Error committing transaction: %v", err)
			json.NewEncoder(w).Encode(map[string]interface{}{"success": false, "message": "Database error during commit"})
			return
		}

		log.Printf("✅ Deleted promotions: %v", targetIDs)
		json.NewEncoder(w).Encode(map[string]interface{}{"success": true})
	})

	// Logout Handler
	http.HandleFunc("/logout", func(w http.ResponseWriter, r *http.Request) {
		session, _ := store.Get(r, "ehong-session")
		session.Options.MaxAge = -1
		session.Save(r, w)
		http.Redirect(w, r, "/backend_setting/index.html", http.StatusSeeOther)
	})

	http.HandleFunc("/", func(w http.ResponseWriter, r *http.Request) {
		path := r.URL.Path

		// Check session for backend pages
		if strings.HasPrefix(path, "/backend_setting/") &&
			!strings.HasSuffix(path, "index.html") &&
			!strings.Contains(path, "/css/") &&
			!strings.Contains(path, "/js/") &&
			!strings.Contains(path, "/images/") {

			session, _ := store.Get(r, "ehong-session")
			if auth, ok := session.Values["user_id"].(string); !ok || auth == "" {
				log.Printf("Unauthorized access attempt to: %s", path)
				http.Redirect(w, r, "/backend_setting/index.html", http.StatusSeeOther)
				return
			}
		}

		if path == "/" {
			path = "/index"
		}

		// Remove leading slash for local file path
		cleanPath := strings.TrimPrefix(path, "/")

		// Determine the base name without extension for matching
		basePath := strings.TrimSuffix(cleanPath, ".html")
		basePath = strings.TrimSuffix(basePath, "/") // Trim trailing slash
		if basePath == "" {
			basePath = "index"
		}

		// If path has no extension or is .html, try processing as template
		ext := filepath.Ext(cleanPath)
		if ext == "" || ext == ".html" || strings.HasSuffix(cleanPath, "/") {
			htmlFile := basePath + ".html"
			if _, err := os.Stat(htmlFile); err == nil {
				// Special data for branches
				var data interface{}
				if basePath == "branches" {
					log.Println("Fetching provinces for branches page...")
					rows, err := db.Query(`
						SELECT PR_CODE, PR_NAME 
						FROM EHONGDB.dbo.PROV 
						WHERE PR_CODE IN ('13','30','31','32','36','39','40','41','42','44','46') 
						ORDER BY PR_NAME
					`)
					if err != nil {
						log.Printf("❌ Error querying provinces: %v", err)
					} else {
						defer rows.Close()
						var provinces []Province
						for rows.Next() {
							var p Province
							if err := rows.Scan(&p.PR_CODE, &p.PR_NAME); err == nil {
								provinces = append(provinces, p)
							}
						}
						log.Printf("✅ Found %d provinces for branches page", len(provinces))
						data = map[string]interface{}{
							"Provinces": provinces,
						}
					}
				}

				if basePath == "backend_setting/main_promotion" {
					log.Println("Fetching promotions for main_promotion page...")
					rows, err := db.Query(`
						SELECT T1.PN_ID, T1.PN_NAME, T1.PN_IMG_1, T1.PN_DATE, T1.PN_TIME, T1.PN_STATUS, T1.PN_STATUSCLOSE, T1.US_NAME, T2.FNAME 
						FROM CONFIG.dbo.NEWEMD_PROMOTION_NEWS T1
						LEFT JOIN EHONGDB.dbo.EMPLOYEE T2 ON T1.US_NAME = T2.US_NAME
						WHERE T1.PN_STATUS IN ('P')
						ORDER BY T1.PN_ID DESC
					`)
					if err != nil {
						log.Printf("❌ Error querying promotions: %v", err)
					} else {
						defer rows.Close()
						thaiMonthNames := []string{
							"มกราคม", "กุมภาพันธ์", "มีนาคม", "เมษายน", "พฤษภาคม", "มิถุนายน",
							"กรกฎาคม", "สิงหาคม", "กันยายน", "ตุลาคม", "พฤศจิกายน", "ธันวาคม",
						}
						var promotions []Promotion
						for rows.Next() {
							var p Promotion
							var pnId sql.NullInt32
							var pnName, pnImg, pnStatus, pnStatusClose, usName, fname sql.NullString
							var pnDate, pnTime sql.NullTime

							err := rows.Scan(&pnId, &pnName, &pnImg, &pnDate, &pnTime, &pnStatus, &pnStatusClose, &usName, &fname)
							if err == nil {
								p.PN_ID = int(pnId.Int32)
								p.PN_NAME = pnName.String
								p.PN_IMG_1 = pnImg.String
								p.PN_STATUS = pnStatus.String
								p.PN_STATUSCLOSE = pnStatusClose.String
								p.US_NAME = usName.String
								p.FNAME = fname.String
								// Format date as Thai Buddhist calendar
								if pnDate.Valid && pnDate.Time.Year() > 1 {
									d := pnDate.Time
									thaiYear := d.Year() + 543
									p.PN_DATE = fmt.Sprintf("%d %s %d", d.Day(), thaiMonthNames[d.Month()-1], thaiYear)
								}
								// Format time as HH:MM:SS น.
								if pnTime.Valid && !pnTime.Time.IsZero() {
									t := pnTime.Time
									p.PN_TIME = fmt.Sprintf("%02d:%02d:%02d น.", t.Hour(), t.Minute(), t.Second())
								}
								promotions = append(promotions, p)
							}
						}
						log.Printf("✅ Found %d promotions", len(promotions))
						data = map[string]interface{}{
							"Promotions": promotions,
						}
					}
				}

				if basePath == "backend_setting/main_news" {
					log.Println("Fetching news data for main_news page...")
					newsGroups := []NewsGroup{}
					newsTypes := []NewsType{}
					promotions := []Promotion{} // Reuse Promotion struct for table display

					// 1. Fetch News Groups
					rowsG, err := db.Query(`SELECT PNS_STATUS, PNS_STATUSNAME FROM CONFIG.dbo.NEWEMD_PN_STATUS WHERE PNS_STATUS NOT IN ('P')`)
					if err == nil {
						defer rowsG.Close()
						for rowsG.Next() {
							var g NewsGroup
							if err := rowsG.Scan(&g.PNS_STATUS, &g.PNS_STATUSNAME); err == nil {
								newsGroups = append(newsGroups, g)
							}
						}
					}

					// 2. Fetch News Types
					rowsT, err := db.Query(`SELECT PNT_TYPE, PNT_TYPENAME FROM CONFIG.dbo.NEWEMD_PN_TYPE`)
					if err == nil {
						defer rowsT.Close()
						for rowsT.Next() {
							var t NewsType
							if err := rowsT.Scan(&t.PNT_TYPE, &t.PNT_TYPENAME); err == nil {
								newsTypes = append(newsTypes, t)
							}
						}
					}

					// 3. Fetch News List (Reusing promotion query pattern)
					rowsL, err := db.Query(`
						SELECT 
						T1.PN_ID, T1.PN_NAME, T1.PN_DETAIL, T1.PN_IMG_1, T1.PN_DATE, T1.PN_TIME,
						T1.PN_ALLDETAIL_1, T1.PN_STATUS, T1.PN_TYPE, T1.PN_IMG_2, T1.PN_IMG_3, 
						T1.PN_IMG_4, T1.PN_ALLDETAIL_2, T1.PN_ALLDETAIL_3, T1.PN_ALLDETAIL_4, T1.PN_STATUSCLOSE, T1.US_NAME,
						T3.PNS_STATUS, T3.PNS_STATUSNAME,
						T4.PNT_TYPE, T4.PNT_TYPENAME, T2.FNAME, T1.US_NAMEEDIT, T1.PN_DATEEDIT, T1.PN_TIMEEDIT
						FROM CONFIG.dbo.NEWEMD_PROMOTION_NEWS T1
						LEFT JOIN EHONGDB.dbo.EMPLOYEE T2 ON T1.US_NAME = T2.US_NAME
						LEFT JOIN CONFIG.dbo.NEWEMD_PN_STATUS T3 ON T1.PN_STATUS = T3.PNS_STATUS
						LEFT JOIN CONFIG.dbo.NEWEMD_PN_TYPE T4 ON T1.PN_TYPE = T4.PNT_TYPE
						WHERE T1.PN_STATUS IN ('PN','S','ON')
						ORDER BY T1.PN_ID DESC
					`)
					if err == nil {
						defer rowsL.Close()
						thaiMonthNames := []string{
							"มกราคม", "กุมภาพันธ์", "มีนาคม", "เมษายน", "พฤษภาคม", "มิถุนายน",
							"กรกฎาคม", "สิงหาคม", "กันยายน", "ตุลาคม", "พฤศจิกายน", "ธันวาคม",
						}
						for rowsL.Next() {
							var p Promotion
							var pnId sql.NullInt32
							var pnName, pnDetail, pnImg, pnAllDetail1, pnStatus, pnType, pnImg2, pnImg3, pnImg4, pnAllDetail2, pnAllDetail3, pnAllDetail4, pnStatusClose, usName, pnsStatus, pnsName, pntType, pntName, fname, usNameEdit, pnDateEdit, pnTimeEdit sql.NullString
							var pnDate, pnTime sql.NullTime

							if err := rowsL.Scan(&pnId, &pnName, &pnDetail, &pnImg, &pnDate, &pnTime, &pnAllDetail1, &pnStatus, &pnType, &pnImg2, &pnImg3, &pnImg4, &pnAllDetail2, &pnAllDetail3, &pnAllDetail4, &pnStatusClose, &usName, &pnsStatus, &pnsName, &pntType, &pntName, &fname, &usNameEdit, &pnDateEdit, &pnTimeEdit); err == nil {
								p.PN_ID = int(pnId.Int32)
								p.PN_NAME = pnName.String
								p.PN_DETAIL = pnDetail.String
								p.PN_IMG_1 = pnImg.String
								p.PN_ALLDETAIL_1 = pnAllDetail1.String
								p.PN_STATUS = pnStatus.String
								p.PN_TYPE = pnType.String
								p.PN_IMG_2 = pnImg2.String
								p.PN_IMG_3 = pnImg3.String
								p.PN_IMG_4 = pnImg4.String
								p.PN_ALLDETAIL_2 = pnAllDetail2.String
								p.PN_ALLDETAIL_3 = pnAllDetail3.String
								p.PN_ALLDETAIL_4 = pnAllDetail4.String
								p.PN_STATUSCLOSE = pnStatusClose.String
								p.US_NAME = usName.String
								p.FNAME = fname.String
								p.PNS_STATUSNAME = pnsName.String
								p.PNT_TYPENAME = pntName.String
								p.US_NAMEEDIT = usNameEdit.String
								p.PN_DATEEDIT = pnDateEdit.String
								p.PN_TIMEEDIT = pnTimeEdit.String
								if pnDate.Valid && pnDate.Time.Year() > 1 {
									d := pnDate.Time
									p.PN_DATE = fmt.Sprintf("%d %s %d", d.Day(), thaiMonthNames[d.Month()-1], d.Year()+543)
								}
								if pnTime.Valid && !pnTime.Time.IsZero() {
									t := pnTime.Time
									p.PN_TIME = fmt.Sprintf("%02d:%02d:%02d น.", t.Hour(), t.Minute(), t.Second())
								}
								promotions = append(promotions, p)
							}
						}
					}

					data = map[string]interface{}{
						"NewsGroups": newsGroups,
						"NewsTypes":  newsTypes,
						"Promotions": promotions,
					}
				}

				if basePath == "product_ev" {
					log.Println("Fetching motorcycle EV data for frontend...")
					evData, err := getMotorcycleEVData(db)
					if err == nil {
						motorcycles, ok := evData["Motorcycles"].([]MotorcycleFuel)
						if ok {
							var gOne, gTwo, gThree, gFour []MotorcycleFuel
							for _, m := range motorcycles {
								// 1. PD_STATUS must be 'O' (Open)
								// 2. FML_STATUS must be 'O' (Open)
								if m.PdStatus != "O" || m.FmlStatus != "O" {
									continue
								}

								// Let's show all that match price criteria since the user didn't specify a filter, just price.

								if m.Price > 0 && m.Price <= 20000 {
									gOne = append(gOne, m)
								} else if m.Price > 20000 && m.Price <= 30000 {
									gTwo = append(gTwo, m)
								} else if m.Price > 30000 && m.Price <= 40000 {
									gThree = append(gThree, m)
								} else if m.Price > 40000 {
									gFour = append(gFour, m)
								}
							}

							// Sort each group from lowest to highest price, prioritizing in-stock items
							sort.Slice(gOne, func(i, j int) bool {
								if gOne[i].Count == 0 && gOne[j].Count > 0 {
									return false
								}
								if gOne[i].Count > 0 && gOne[j].Count == 0 {
									return true
								}
								return gOne[i].Price < gOne[j].Price
							})
							sort.Slice(gTwo, func(i, j int) bool {
								if gTwo[i].Count == 0 && gTwo[j].Count > 0 {
									return false
								}
								if gTwo[i].Count > 0 && gTwo[j].Count == 0 {
									return true
								}
								return gTwo[i].Price < gTwo[j].Price
							})
							sort.Slice(gThree, func(i, j int) bool {
								if gThree[i].Count == 0 && gThree[j].Count > 0 {
									return false
								}
								if gThree[i].Count > 0 && gThree[j].Count == 0 {
									return true
								}
								return gThree[i].Price < gThree[j].Price
							})
							sort.Slice(gFour, func(i, j int) bool {
								if gFour[i].Count == 0 && gFour[j].Count > 0 {
									return false
								}
								if gFour[i].Count > 0 && gFour[j].Count == 0 {
									return true
								}
								return gFour[i].Price < gFour[j].Price
							})

							data = map[string]interface{}{
								"GroupOne":    gOne,
								"GroupTwo":    gTwo,
								"GroupThree":  gThree,
								"GroupFour":   gFour,
								"Motorcycles": motorcycles,
							}
						}
					} else {
						log.Printf("Error fetching EV data: %v", err)
					}
				}

				if basePath == "product_motorcycle" {
					log.Println("Fetching dynamic motorcycle fuel data (PT_NO grouping)...")
					fuelData, err := getMotorcycleFuelData(db)
					if err == nil {
						motorcycles, ok := fuelData["Motorcycles"].([]MotorcycleFuel)
						if ok {
							// Create a helper function to group a flat slice into families
							groupByFamily := func(motorcycles []MotorcycleFuel) []FamilyGroup {
								familyMap := make(map[string]*FamilyGroup)
								var familyNames []string

								for _, m := range motorcycles {
									name := strings.TrimSpace(m.ModelFName)
									if name == "" {
										name = "อื่นๆ"
									}
									if _, exists := familyMap[name]; !exists {
										familyMap[name] = &FamilyGroup{
											FamilyName: name,
											BrandName:  m.BrandName,
										}
										familyNames = append(familyNames, name)
									}
									familyMap[name].Motorcycles = append(familyMap[name].Motorcycles, m)
									familyMap[name].TotalCount++
								}

								// Keep the original order of appearance for families, but sort motorcycles within
								var result []FamilyGroup
								for _, name := range familyNames {
									fg := *familyMap[name]
									// Sort within family: Availability (In stock first) then Brand then Price
									sort.Slice(fg.Motorcycles, func(i, j int) bool {
										if fg.Motorcycles[i].Count == 0 && fg.Motorcycles[j].Count > 0 {
											return false
										}
										if fg.Motorcycles[i].Count > 0 && fg.Motorcycles[j].Count == 0 {
											return true
										}
										if fg.Motorcycles[i].BrandName != fg.Motorcycles[j].BrandName {
											return fg.Motorcycles[i].BrandName < fg.Motorcycles[j].BrandName
										}
										return fg.Motorcycles[i].Price < fg.Motorcycles[j].Price
									})
									result = append(result, fg)
								}
								return result
							}

							var rawFamily, rawAuto, rawSport, rawSemi, rawMini, rawOthers []MotorcycleFuel
							for _, m := range motorcycles {
								if m.PdStatus != "O" || m.FmlStatus != "O" {
									continue
								}
								switch strings.TrimSpace(m.PtNo) {
								case "F":
									rawFamily = append(rawFamily, m)
								case "A":
									rawAuto = append(rawAuto, m)
								case "S":
									rawSport = append(rawSport, m)
								case "SM-S":
									rawSemi = append(rawSemi, m)
								case "MN-S":
									rawMini = append(rawMini, m)
								default:
									rawOthers = append(rawOthers, m)
								}
							}

							// Extract unique brands (sorted)
							brandMap := make(map[string]bool)
							var brands []map[string]string
							for _, m := range motorcycles {
								if m.PdStatus == "O" && m.FmlStatus == "O" {
									if !brandMap[m.BrandName] {
										brandMap[m.BrandName] = true
										brands = append(brands, map[string]string{"BrandName": m.BrandName})
									}
								}
							}
							sort.Slice(brands, func(i, j int) bool {
								return brands[i]["BrandName"] < brands[j]["BrandName"]
							})

							data = map[string]interface{}{
								"GroupFamily":    groupByFamily(rawFamily),
								"GroupAutomatic": groupByFamily(rawAuto),
								"GroupSport":     groupByFamily(rawSport),
								"GroupSemiSport": groupByFamily(rawSemi),
								"GroupMiniSport": groupByFamily(rawMini),
								"GroupOthers":    groupByFamily(rawOthers),
								"Motorcycles":    motorcycles,
								"Brand":          brands,
							}
						}
					} else {
						log.Printf("Error fetching motorcycle fuel data: %v", err)
					}
				}

				if basePath == "backend_setting/main_motorcycle_fuel" {
					log.Println("Fetching motorcycle fuel data...")
					data, err = getMotorcycleFuelData(db)
				}

				if basePath == "backend_setting/main_used_motorcycle_fuel" {
					log.Println("Fetching used motorcycle fuel data...")
					data, err = getUsedMotorcycleFuelData(db)
				}

				if basePath == "backend_setting/main_motorcycle_ev" {
					log.Println("Fetching motorcycle EV data...")
					data, err = getMotorcycleEVData(db)
				}

				if basePath == "backend_setting/main_used_motorcycle_ev" {
					log.Println("Fetching used motorcycle EV data...")
					data, err = getUsedMotorcycleEVData(db)
				}

				// Prepare template data
				type UserInfo struct {
					UserID   string
					UserName string
					FullName string
				}

				session, _ := store.Get(r, "ehong-session")
				var user UserInfo
				if uid, ok := session.Values["user_id"].(string); ok {
					user.UserID = uid
					user.UserName = session.Values["user_name"].(string)
					user.FullName = session.Values["full_name"].(string)
				}

				// Wrap data in a map if it's not already one, and add User info
				templateData := make(map[string]interface{})
				if data != nil {
					if d, ok := data.(map[string]interface{}); ok {
						templateData = d
					} else {
						templateData["Data"] = data
					}
				}
				// Prepare Thai Date
				thaiMonths := []string{
					"มกราคม", "กุมภาพันธ์", "มีนาคม", "เมษายน", "พฤษภาคม", "มิถุนายน",
					"กรกฎาคม", "สิงหาคม", "กันยายน", "ตุลาคม", "พฤศจิกายน", "ธันวาคม",
				}
				now := time.Now()
				thaiYear := now.Year() + 543
				thaiMonth := thaiMonths[now.Month()-1]
				templateData["CurrentDate"] = fmt.Sprintf("%d %s %d", now.Day(), thaiMonth, thaiYear)

				templateData["User"] = user
				templateData["CurrentYear"] = now.Year()
				templateData["CurrentPage"] = filepath.Base(basePath)

				// Use Go Template to include navbar, footer, etc.
				tmplFiles := []string{
					htmlFile,
					"templates/navbar.html",
					"templates/footer.html",
					"templates/head_links.html",
					"backend_setting/templates/backend_head.html",
					"backend_setting/templates/backend_header.html",
					"backend_setting/templates/backend_menu.html",
					"backend_setting/templates/backend_footer.html",
					"backend_setting/templates/backend_js.html",
					"backend_setting/templates/datatable_code.html",
				}
				tmpl := template.New(filepath.Base(htmlFile)).Funcs(template.FuncMap{
					"add": func(a, b int) int {
						return a + b
					},
					"formatNumber": func(n float64) string {
						// Simple formatting with commas for thousands
						s := fmt.Sprintf("%.2f", n)
						if n < 1000 {
							return s
						}
						parts := strings.Split(s, ".")
						intPart := parts[0]
						decPart := parts[1]

						var result []string
						for len(intPart) > 3 {
							result = append([]string{intPart[len(intPart)-3:]}, result...)
							intPart = intPart[:len(intPart)-3]
						}
						if len(intPart) > 0 {
							result = append([]string{intPart}, result...)
						}
						return strings.Join(result, ",") + "." + decPart
					},
				})
				tmpl, err = tmpl.ParseFiles(tmplFiles...)
				if err != nil {
					log.Println("Error parsing templates:", err)
					http.Error(w, err.Error(), http.StatusInternalServerError)
					return
				}
				err = tmpl.Execute(w, templateData)
				if err != nil {
					log.Println("Error executing template:", err)
				}
				return
			}
		}

		// Fallback to ServeFile for assets or specific extensions
		http.ServeFile(w, r, "."+r.URL.Path)
	})

	// Update Family Status Handler (UPSERT)
	http.HandleFunc("/api/update_family_status", func(w http.ResponseWriter, r *http.Request) {
		if r.Method != http.MethodPost {
			http.Error(w, "Method not allowed", http.StatusMethodNotAllowed)
			return
		}

		brandNo := r.FormValue("BRAND_NO")
		fmlNo := r.FormValue("FML_NO")
		serieNo := r.FormValue("SERIE_NO")
		newStatus := r.FormValue("NEW_STATUS") // 'O' for Open, 'C' for Closed

		if brandNo == "" || fmlNo == "" || serieNo == "" || newStatus == "" {
			http.Error(w, "Missing required fields", http.StatusBadRequest)
			return
		}

		log.Printf("Updating Family Status (UPSERT): Brand=%s, Family=%s, Serie=%s, Status=%s", brandNo, fmlNo, serieNo, newStatus)

		// UPSERT logic on CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE_FML
		upsertSQL := `
			IF EXISTS (SELECT 1 FROM CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE_FML WHERE RTRIM(BRAND_NO) = RTRIM(@p1) AND RTRIM(FML_NO) = RTRIM(@p2) AND RTRIM(SERIE_NO) = RTRIM(@p3))
			BEGIN
				UPDATE CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE_FML 
				SET FML_STATUS = @p4 
				WHERE RTRIM(BRAND_NO) = RTRIM(@p1) AND RTRIM(FML_NO) = RTRIM(@p2) AND RTRIM(SERIE_NO) = RTRIM(@p3)
			END
			ELSE
			BEGIN
				INSERT INTO CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE_FML (BRAND_NO, FML_NO, SERIE_NO, FML_STATUS)
				VALUES (@p1, @p2, @p3, @p4)
			END
		`
		_, err := db.Exec(upsertSQL, brandNo, fmlNo, serieNo, newStatus)

		w.Header().Set("Content-Type", "application/json")
		if err != nil {
			log.Printf("❌ Update error (Family UPSERT): %v", err)
			json.NewEncoder(w).Encode(map[string]interface{}{
				"success": false,
				"message": err.Error(),
			})
			return
		}

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

	// Update Model Status Handler (UPSERT)
	http.HandleFunc("/api/update_model_status", func(w http.ResponseWriter, r *http.Request) {
		if r.Method != http.MethodPost {
			http.Error(w, "Method not allowed", http.StatusMethodNotAllowed)
			return
		}

		brandNo := r.FormValue("BRAND_NO")
		fmlNo := r.FormValue("FML_NO")
		techName := r.FormValue("PRODUCT_TECHNAME")
		serieNo := r.FormValue("SERIE_NO")
		newStatus := r.FormValue("NEW_STATUS") // 'O' for Open, 'C' for Closed

		if brandNo == "" || fmlNo == "" || techName == "" || serieNo == "" || newStatus == "" {
			http.Error(w, "Missing required fields", http.StatusBadRequest)
			return
		}

		log.Printf("Updating Model Status (UPSERT): Brand=%s, Family=%s, Tech=%s, Serie=%s, Status=%s", brandNo, fmlNo, techName, serieNo, newStatus)

		// UPSERT logic on CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE
		upsertSQL := `
			IF EXISTS (SELECT 1 FROM CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE WHERE RTRIM(PRODUCT_TECHNAME) = RTRIM(@p1) AND RTRIM(BRAND_NO) = RTRIM(@p2) AND RTRIM(SERIE_NO) = RTRIM(@p3))
			BEGIN
				UPDATE CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE 
				SET PD_STATUS = @p4 
				WHERE RTRIM(PRODUCT_TECHNAME) = RTRIM(@p1) AND RTRIM(BRAND_NO) = RTRIM(@p2) AND RTRIM(SERIE_NO) = RTRIM(@p3)
			END
			ELSE
			BEGIN
				INSERT INTO CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE (PRODUCT_TECHNAME, BRAND_NO, SERIE_NO, PD_STATUS)
				VALUES (@p1, @p2, @p3, @p4)
			END
		`
		_, err := db.Exec(upsertSQL, techName, brandNo, serieNo, newStatus)

		w.Header().Set("Content-Type", "application/json")
		if err != nil {
			log.Printf("❌ Update error (Model UPSERT): %v", err)
			json.NewEncoder(w).Encode(map[string]interface{}{
				"success": false,
				"message": err.Error(),
			})
			return
		}

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

	type BulkVisibilityReq struct {
		Status   string `json:"status"`
		Products []struct {
			Brand string `json:"brand"`
			Fml   string `json:"fml"`
			Tech  string `json:"tech"`
			Serie string `json:"serie"`
		} `json:"products"`
	}

	// Bulk Update Family Status (UPSERT)
	http.HandleFunc("/api/bulk_update_family_status", func(w http.ResponseWriter, r *http.Request) {
		if r.Method != http.MethodPost {
			http.Error(w, "Method not allowed", http.StatusMethodNotAllowed)
			return
		}

		defer func() {
			if rec := recover(); rec != nil {
				log.Printf("🔥 Panic in bulk_update_family_status: %v", rec)
				fmt.Fprintf(w, `{"success":false, "message":"Internal Server Panic"}`)
			}
		}()

		var req BulkVisibilityReq
		if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
			http.Error(w, "Invalid request", http.StatusBadRequest)
			return
		}
		if req.Status == "" || len(req.Products) == 0 {
			http.Error(w, "Missing parameters", http.StatusBadRequest)
			return
		}

		log.Printf("Bulk Family Visibility: Status=%s, Count=%d", req.Status, len(req.Products))

		tx, err := db.Begin()
		if err != nil {
			w.Header().Set("Content-Type", "application/json")
			fmt.Fprintf(w, `{"success":false, "message":"%v"}`, err)
			return
		}
		defer tx.Rollback()

		upsertSQL := `
			IF EXISTS (SELECT 1 FROM CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE_FML WHERE RTRIM(BRAND_NO) = RTRIM(@p1) AND RTRIM(FML_NO) = RTRIM(@p2) AND RTRIM(SERIE_NO) = RTRIM(@p3))
			BEGIN
				UPDATE CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE_FML 
				SET FML_STATUS = @p4 
				WHERE RTRIM(BRAND_NO) = RTRIM(@p1) AND RTRIM(FML_NO) = RTRIM(@p2) AND RTRIM(SERIE_NO) = RTRIM(@p3)
			END
			ELSE
			BEGIN
				INSERT INTO CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE_FML (BRAND_NO, FML_NO, SERIE_NO, FML_STATUS)
				VALUES (@p1, @p2, @p3, @p4)
			END
		`
		stmt, err := tx.Prepare(upsertSQL)
		if err != nil {
			log.Printf("❌ Prepare Error (Bulk Family): %v", err)
			w.Header().Set("Content-Type", "application/json")
			fmt.Fprintf(w, `{"success":false, "message":"Prepare failed: %v"}`, err)
			return
		}
		defer stmt.Close()

		// Use a map to track unique families to update (to avoid redundant DB calls)
		type famKey struct{ b, f, s string }
		seen := make(map[famKey]bool)

		for _, p := range req.Products {
			k := famKey{p.Brand, p.Fml, p.Serie}
			if seen[k] {
				continue
			}
			_, err = stmt.Exec(p.Brand, p.Fml, p.Serie, req.Status)
			if err != nil {
				log.Printf("❌ Bulk Family Update Error (UPSERT): %v", err)
				w.Header().Set("Content-Type", "application/json")
				fmt.Fprintf(w, `{"success":false, "message":"%v"}`, err)
				return
			}
			seen[k] = true
		}

		if err := tx.Commit(); err != nil {
			log.Printf("❌ Commit Error (Bulk Family): %v", err)
			w.Header().Set("Content-Type", "application/json")
			fmt.Fprintf(w, `{"success":false, "message":"%v"}`, err)
			return
		}
		w.Header().Set("Content-Type", "application/json")
		fmt.Fprintf(w, `{"success":true}`)
	})

	// Bulk Update Model Status (UPSERT)
	http.HandleFunc("/api/bulk_update_model_status", func(w http.ResponseWriter, r *http.Request) {
		if r.Method != http.MethodPost {
			http.Error(w, "Method not allowed", http.StatusMethodNotAllowed)
			return
		}

		defer func() {
			if rec := recover(); rec != nil {
				log.Printf("🔥 Panic in bulk_update_model_status: %v", rec)
				fmt.Fprintf(w, `{"success":false, "message":"Internal Server Panic"}`)
			}
		}()

		var req BulkVisibilityReq
		if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
			http.Error(w, "Invalid request", http.StatusBadRequest)
			return
		}
		if req.Status == "" || len(req.Products) == 0 {
			http.Error(w, "Missing parameters", http.StatusBadRequest)
			return
		}

		log.Printf("Bulk Model Visibility: Status=%s, Count=%d", req.Status, len(req.Products))

		tx, err := db.Begin()
		if err != nil {
			w.Header().Set("Content-Type", "application/json")
			fmt.Fprintf(w, `{"success":false, "message":"%v"}`, err)
			return
		}
		defer tx.Rollback()

		upsertSQL := `
			IF EXISTS (SELECT 1 FROM CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE WHERE RTRIM(PRODUCT_TECHNAME) = RTRIM(@p1) AND RTRIM(BRAND_NO) = RTRIM(@p2) AND RTRIM(SERIE_NO) = RTRIM(@p3))
			BEGIN
				UPDATE CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE 
				SET PD_STATUS = @p4 
				WHERE RTRIM(PRODUCT_TECHNAME) = RTRIM(@p1) AND RTRIM(BRAND_NO) = RTRIM(@p2) AND RTRIM(SERIE_NO) = RTRIM(@p3)
			END
			ELSE
			BEGIN
				INSERT INTO CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE (PRODUCT_TECHNAME, BRAND_NO, SERIE_NO, PD_STATUS)
				VALUES (@p1, @p2, @p3, @p4)
			END
		`
		stmt, err := tx.Prepare(upsertSQL)
		if err != nil {
			log.Printf("❌ Prepare Error (Bulk Model): %v", err)
			w.Header().Set("Content-Type", "application/json")
			fmt.Fprintf(w, `{"success":false, "message":"Prepare failed: %v"}`, err)
			return
		}
		defer stmt.Close()

		for _, p := range req.Products {
			_, err = stmt.Exec(p.Tech, p.Brand, p.Serie, req.Status)
			if err != nil {
				log.Printf("❌ Bulk Model Update Error (UPSERT): %v", err)
				w.Header().Set("Content-Type", "application/json")
				fmt.Fprintf(w, `{"success":false, "message":"%v"}`, err)
				return
			}
		}

		if err := tx.Commit(); err != nil {
			log.Printf("❌ Commit Error (Bulk Model): %v", err)
			w.Header().Set("Content-Type", "application/json")
			fmt.Fprintf(w, `{"success":false, "message":"%v"}`, err)
			return
		}
		w.Header().Set("Content-Type", "application/json")
		fmt.Fprintf(w, `{"success":true}`)
	})

	// 5. กำหนด Port และรัน Server
	fmt.Println("✅ Server running at http://localhost:9900")
	log.Fatal(http.ListenAndServe(":9900", nil))
}

// Helper functions for data fetching

func getMotorcycleFuelData(db *sql.DB) (map[string]interface{}, error) {
	ptypeFilter := "PTYPE_NO LIKE 'N'"
	return fetchMotorcycleFuelData(db, ptypeFilter, "")
}

func getUsedMotorcycleFuelData(db *sql.DB) (map[string]interface{}, error) {
	ptypeFilter := "PTYPE_NO LIKE 'R'"
	return fetchMotorcycleFuelData(db, ptypeFilter, "AND ISNULL(d.cnt1, 0) > 0")
}

func fetchMotorcycleFuelData(db *sql.DB, ptypeFilter string, stockFilter string) (map[string]interface{}, error) {
	query := fmt.Sprintf(`
					SELECT
						f.PT_NO,
						f.PD_STATUS,
						f.PD_IMG,
						g.FML_STATUS,
						e.PT_NAME,
						RTRIM(d.PRODUCT_TECHNAME) AS TECH_REF,
						d.cnt1,
						SUM(a.PRODUCT_NUM) AS PRODUCT_NUM,
						RTRIM(a.BRAND_NO) AS BRAND_NO,
						b.BRAND_NAME,
						RTRIM(a.SERIE_NO) AS SERIE_NO,
						RTRIM(a.FML_NO) AS FML_NO,
						c.FML_NAME,
						RTRIM(a.PRODUCT_TECHNAME) AS PRODUCT_TECHNAME,
						RTRIM(a.PRODUCT_NAME1) AS PRODUCT_NAME1,
						a.STD_SALEPRICE
					FROM EHONGDB.dbo.PRODUCT_PRODUCTDATA a
					LEFT OUTER JOIN(
						SELECT 
							BRAND_NO, 
							BRAND_NAME 
						FROM EHONGDB.dbo.product_basebrand
						WHERE SERIE_NO = '002' AND BRAND_NO IN ('BAJ', 'CYC', 'GPX', 'HON', 'KTM', 'KWS', 'MTP', 'RYE', 'RYU', 'SCO', 'STL', 'SZK', 'VSP', 'YMH')
					) b ON (a.BRAND_NO = b.BRAND_NO)
					LEFT OUTER JOIN(
					SELECT 
						SERIE_NO,
						BRAND_NO,
						FML_NO,
						FML_NAME
					FROM EHONGDB.dbo.PRODUCT_FAMILY 
					where SERIE_NO = '002' AND BRAND_NO IN ('BAJ', 'CYC', 'GPX', 'HON', 'KTM', 'KWS', 'MTP', 'RYE', 'RYU', 'SCO', 'STL', 'SZK', 'VSP', 'YMH')
					) c ON (a.FML_NO = c.FML_NO) AND (a.BRAND_NO = c.BRAND_NO) 
					LEFT OUTER JOIN (
					SELECT
						BRAND_NO,
						PRODUCT_TECHNAME,
						COUNT(PRODUCT_TECHNAME) as cnt1 
					FROM EHONGDB.dbo.PRODUCT_VPRODUCTSERIAL 
					WHERE SERIE_NO ='002' AND STATUS='I' AND PTYPE_NO <> 'F' AND PSTATE LIKE 'FS' AND %s
						AND PRODUCT_NAME1 NOT LIKE '%%สามล้อ%%' AND PRODUCT_NAME1 NOT LIKE '%%ไฟฟ้า%%' AND PRODUCT_NAME1 NOT LIKE '%%ทดสอบ%%'
					GROUP BY 
						BRAND_NO,
						PRODUCT_TECHNAME,
						PRODUCT_NAME1,
						STD_SALEPRICE
					) d ON (a.BRAND_NO = d.BRAND_NO) AND (a.PRODUCT_TECHNAME = d.PRODUCT_TECHNAME) 
					LEFT OUTER JOIN (
					SELECT 
						PRODUCT_TECHNAME,
						BRAND_NO,
						SERIE_NO,
						PT_NO,
						PD_STATUS,
						PD_IMG
					FROM CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE
					) f ON (f.PRODUCT_TECHNAME = a.PRODUCT_TECHNAME) AND (f.BRAND_NO = a.BRAND_NO) AND (f.SERIE_NO = a.SERIE_NO)
					LEFT OUTER JOIN (
					SELECT 
						BRAND_NO,
						FML_NO,	
						SERIE_NO,	
						FML_STATUS
					FROM CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE_FML
					) g ON (g.FML_NO = a.FML_NO) AND (g.BRAND_NO = a.BRAND_NO) AND (g.SERIE_NO = a.SERIE_NO)
					LEFT OUTER JOIN (
					SELECT
						PT_NO,
						PT_NAME
					FROM CONFIG.dbo.NEWEMD_PRODUCT_TYPE
					) e ON (e.PT_NO = f.PT_NO)
					WHERE a.SERIE_NO = '002' AND a.BRAND_NO IN ('BAJ', 'CYC', 'GPX', 'HON', 'KTM', 'KWS', 'MTP', 'RYE', 'RYU', 'SCO', 'STL', 'SZK', 'VSP', 'YMH')
					AND c.FML_NAME NOT LIKE '%%สามล้อ%%' AND c.FML_NAME NOT LIKE '%%ไฟฟ้า%%' AND c.FML_NAME NOT LIKE '%%ทดสอบ%%'
					AND a.PRODUCT_NAME1 NOT LIKE '%%สามล้อ%%' AND a.PRODUCT_NAME1 NOT LIKE '%%ไฟฟ้า%%' AND a.PRODUCT_NAME1 NOT LIKE '%%ทดสอบ%%'
					AND a.STD_SALEPRICE NOT IN ('0') AND a.PRODUCT_TECHNAME <> '' AND a.FML_NO <> '' %s
					GROUP BY
						f.PT_NO,
						f.PD_STATUS,
						f.PD_IMG,
						g.FML_STATUS,
						e.PT_NAME,
						d.PRODUCT_TECHNAME,
						d.cnt1,
						a.BRAND_NO,
						b.BRAND_NAME,
						a.SERIE_NO,
						a.FML_NO,
						c.FML_NAME,
						a.PRODUCT_TECHNAME,
						a.PRODUCT_NAME1,
						a.STD_SALEPRICE
					ORDER BY 
						a.BRAND_NO, 
						b.BRAND_NAME
					`, ptypeFilter, stockFilter)

	rows, err := db.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var motorcycles []MotorcycleFuel
	for rows.Next() {
		var m MotorcycleFuel
		var ptNo, ptName, pdImg, pdStatus, fmlStatus, dProductTechName, brandNo, brandName, serieNo, fmlNo, fmlName, aProductTechName, productName1 sql.NullString
		var cnt1, productNum sql.NullInt32
		var price sql.NullFloat64

		if err := rows.Scan(&ptNo, &pdStatus, &pdImg, &fmlStatus, &ptName, &dProductTechName, &cnt1, &productNum, &brandNo, &brandName, &serieNo, &fmlNo, &fmlName, &aProductTechName, &productName1, &price); err == nil {
			m.PtNo = ptNo.String
			m.PtName = ptName.String
			m.PdImg = pdImg.String
			m.PdStatus = pdStatus.String
			m.FmlStatus = fmlStatus.String
			m.Count = int(cnt1.Int32)
			m.BrandNo = brandNo.String
			m.ProductTechName = aProductTechName.String
			m.ModelFName = fmlName.String
			m.FmlNo = fmlNo.String
			m.BrandName = brandName.String
			m.ModelName = productName1.String
			m.Price = price.Float64
			m.SerieNo = serieNo.String
			motorcycles = append(motorcycles, m)
		}
	}

	brandQuery := `
		select 
			BRAND_NO, 
			BRAND_NAME 
		from EHONGDB.dbo.product_basebrand
		where serie_no = '002' AND BRAND_NO NOT IN (
			'AIM','AJ','EMB','JUM','LIO','MOD','SAG','SUR','TAD','TNT','TRM','TYT','YAD','PP','ELT','VCA','ATV','SR','SUN','BST','ZIP',
			'BAC','BEN','CGV','DIP','HAR','JRD','KWY','LIF','NKT','ZON','SBI','SYM','TGR'
		) ORDER BY BRAND_NAME
	`
	var brands []string
	brandRows, berr := db.Query(brandQuery)
	if berr == nil {
		defer brandRows.Close()
		for brandRows.Next() {
			var bNo, bName sql.NullString
			if err := brandRows.Scan(&bNo, &bName); err == nil {
				brands = append(brands, bName.String)
			}
		}
	}

	var productTypes []map[string]string
	typeRows, terr := db.Query("SELECT PT_NO, PT_NAME FROM CONFIG.dbo.NEWEMD_PRODUCT_TYPE ORDER BY PT_NAME")
	if terr == nil {
		defer typeRows.Close()
		for typeRows.Next() {
			var tNo, tName sql.NullString
			if err := typeRows.Scan(&tNo, &tName); err == nil {
				productTypes = append(productTypes, map[string]string{
					"PT_NO":   tNo.String,
					"PT_NAME": tName.String,
				})
			}
		}
	}

	type brandFamilyKey struct{ brand, family string }
	familyMap := make(map[brandFamilyKey][]MotorcycleFuel)
	var familyOrder []brandFamilyKey
	seen := make(map[brandFamilyKey]bool)
	for _, m := range motorcycles {
		k := brandFamilyKey{m.BrandName, m.ModelFName}
		familyMap[k] = append(familyMap[k], m)
		if !seen[k] {
			familyOrder = append(familyOrder, k)
			seen[k] = true
		}
	}

	brandFamilyMap := make(map[string][]FamilyGroup)
	for _, k := range familyOrder {
		items := familyMap[k]
		brandFamilyMap[k.brand] = append(brandFamilyMap[k.brand], FamilyGroup{
			FamilyName:  k.family,
			Motorcycles: items,
		})
	}

	var brandGroups []BrandGroup
	for _, bName := range brands {
		if fams, ok := brandFamilyMap[bName]; ok {
			brandGroups = append(brandGroups, BrandGroup{
				BrandName: bName,
				Families:  fams,
			})
		}
	}

	return map[string]interface{}{
		"Motorcycles":  motorcycles,
		"BrandGroups":  brandGroups,
		"Brands":       brands,
		"ProductTypes": productTypes,
	}, nil
}

func getMotorcycleEVData(db *sql.DB) (map[string]interface{}, error) {
	ptypeFilter := "PTYPE_NO LIKE 'N'"
	return fetchMotorcycleEVData(db, ptypeFilter, "")
}

func getUsedMotorcycleEVData(db *sql.DB) (map[string]interface{}, error) {
	ptypeFilter := "PTYPE_NO LIKE 'R'"
	return fetchMotorcycleEVData(db, ptypeFilter, "AND ISNULL(d.cnt1, 0) > 0")
}

func fetchMotorcycleEVData(db *sql.DB, ptypeFilter string, stockFilter string) (map[string]interface{}, error) {
	query := fmt.Sprintf(`
					SELECT
						f.PT_NO,
						f.PD_STATUS,
						f.PD_IMG,
						g.FML_STATUS,
						e.PT_NAME,
						RTRIM(d.PRODUCT_TECHNAME) AS TECH_REF,
						d.cnt1,
						SUM(a.PRODUCT_NUM) AS PRODUCT_NUM,
						RTRIM(a.BRAND_NO) AS BRAND_NO,
						b.BRAND_NAME,
						RTRIM(a.SERIE_NO) AS SERIE_NO,
						RTRIM(a.FML_NO) AS FML_NO,
						c.FML_NAME,
						RTRIM(a.PRODUCT_TECHNAME) AS PRODUCT_TECHNAME,
						RTRIM(a.PRODUCT_NAME1) AS PRODUCT_NAME1,
						a.STD_SALEPRICE
					FROM EHONGDB.dbo.PRODUCT_PRODUCTDATA a
					LEFT OUTER JOIN(
						SELECT 
							BRAND_NO, 
							BRAND_NAME 
						FROM EHONGDB.dbo.product_basebrand
						WHERE SERIE_NO = '039' AND BRAND_NO IN ('AIM', 'EMB', 'MOD', 'SUR', 'TAD', 'YAD')
					) b ON (a.BRAND_NO = b.BRAND_NO)
					LEFT OUTER JOIN(
					SELECT 
						SERIE_NO,
						BRAND_NO,
						FML_NO,
						FML_NAME
					FROM EHONGDB.dbo.PRODUCT_FAMILY 
					where SERIE_NO = '039' AND BRAND_NO IN ('AIM', 'EMB', 'MOD', 'SUR', 'TAD', 'YAD')
					) c ON (a.FML_NO = c.FML_NO) AND (a.BRAND_NO = c.BRAND_NO) 
					LEFT OUTER JOIN (
					SELECT
						BRAND_NO,
						PRODUCT_TECHNAME,
						COUNT(PRODUCT_TECHNAME) as cnt1 
					FROM EHONGDB.dbo.PRODUCT_VPRODUCTSERIAL 
					WHERE SERIE_NO ='039' AND STATUS='I' AND PTYPE_NO <> 'F' AND PSTATE LIKE 'FS' AND %s 
						AND PRODUCT_NAME1 NOT LIKE '%%สามล้อ%%' AND PRODUCT_NAME1 NOT LIKE '%%ทดสอบ%%'
					GROUP BY 
						BRAND_NO,
						PRODUCT_TECHNAME,
						PRODUCT_NAME1,
						STD_SALEPRICE,
						FML_NO
					) d ON (a.BRAND_NO = d.BRAND_NO) AND (a.PRODUCT_TECHNAME = d.PRODUCT_TECHNAME) 
					LEFT OUTER JOIN (
					SELECT 
						PRODUCT_TECHNAME,
						BRAND_NO,
						SERIE_NO,
						PT_NO,
						PD_STATUS,
						PD_IMG
					FROM CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE
					) f ON (f.PRODUCT_TECHNAME = a.PRODUCT_TECHNAME) AND (f.BRAND_NO = a.BRAND_NO) AND (f.SERIE_NO = a.SERIE_NO)
					LEFT OUTER JOIN (
					SELECT 
						BRAND_NO,
						FML_NO,	
						SERIE_NO,	
						FML_STATUS
					FROM CONFIG.dbo.NEWEMD_PRODUCTDATAUPDATE_FML
					) g ON (g.FML_NO = a.FML_NO) AND (g.BRAND_NO = a.BRAND_NO) AND (g.SERIE_NO = a.SERIE_NO)
					LEFT OUTER JOIN (
					SELECT
						PT_NO,
						PT_NAME
					FROM CONFIG.dbo.NEWEMD_PRODUCT_TYPE
					) e ON (e.PT_NO = f.PT_NO)
					WHERE a.SERIE_NO = '039' AND a.BRAND_NO IN ('AIM', 'EMB', 'MOD', 'SUR', 'TAD', 'YAD')
					AND c.FML_NAME NOT LIKE '%%สามล้อ%%' AND c.FML_NAME NOT LIKE '%%ทดสอบ%%'
					AND a.PRODUCT_NAME1 NOT LIKE '%%สามล้อ%%' AND a.PRODUCT_NAME1 NOT LIKE '%%ทดสอบ%%'
					AND a.STD_SALEPRICE NOT IN ('0') 
					%s
					GROUP BY 
						f.PT_NO,
						f.PD_STATUS,
						f.PD_IMG,
						g.FML_STATUS,
						e.PT_NAME,
						d.PRODUCT_TECHNAME,
						d.cnt1,
						a.BRAND_NO,
						b.BRAND_NAME,
						a.SERIE_NO,
						a.FML_NO,
						c.FML_NAME,
						a.PRODUCT_TECHNAME,
						a.PRODUCT_NAME1,
						a.STD_SALEPRICE
					ORDER BY 
						a.BRAND_NO, 
						b.BRAND_NAME
					`, ptypeFilter, stockFilter)

	rows, err := db.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var motorcycles []MotorcycleFuel
	for rows.Next() {
		var m MotorcycleFuel
		var ptNo, ptName, pdImg, pdStatus, fmlStatus, dProductTechName, brandNo, brandName, serieNo, fmlNo, fmlName, aProductTechName, productName1 sql.NullString
		var cnt1, productNum sql.NullInt32
		var price sql.NullFloat64

		if err := rows.Scan(&ptNo, &pdStatus, &pdImg, &fmlStatus, &ptName, &dProductTechName, &cnt1, &productNum, &brandNo, &brandName, &serieNo, &fmlNo, &fmlName, &aProductTechName, &productName1, &price); err == nil {
			m.PtNo = ptNo.String
			m.PtName = ptName.String
			m.PdImg = pdImg.String
			m.PdStatus = pdStatus.String
			m.FmlStatus = fmlStatus.String
			m.Count = int(cnt1.Int32)
			m.BrandNo = brandNo.String
			m.ProductTechName = aProductTechName.String
			m.ModelFName = fmlName.String
			m.FmlNo = fmlNo.String
			m.BrandName = brandName.String
			m.ModelName = productName1.String
			m.Price = price.Float64
			m.SerieNo = serieNo.String
			motorcycles = append(motorcycles, m)
		}
	}

	brandQuery := `
		select 
			BRAND_NO, 
			BRAND_NAME 
		from EHONGDB.dbo.product_basebrand
		where SERIE_NO = '039' AND BRAND_NO IN ('AIM', 'EMB', 'MOD', 'SUR', 'TAD', 'YAD')
		ORDER BY BRAND_NAME
	`
	var brands []string
	brandRows, berr := db.Query(brandQuery)
	if berr == nil {
		defer brandRows.Close()
		for brandRows.Next() {
			var bNo, bName sql.NullString
			if err := brandRows.Scan(&bNo, &bName); err == nil {
				brands = append(brands, bName.String)
			}
		}
	}

	var productTypes []map[string]string
	typeRows, terr := db.Query("SELECT PT_NO, PT_NAME FROM CONFIG.dbo.NEWEMD_PRODUCT_TYPE ORDER BY PT_NAME")
	if terr == nil {
		defer typeRows.Close()
		for typeRows.Next() {
			var tNo, tName sql.NullString
			if err := typeRows.Scan(&tNo, &tName); err == nil {
				productTypes = append(productTypes, map[string]string{
					"PT_NO":   tNo.String,
					"PT_NAME": tName.String,
				})
			}
		}
	}

	type brandFamilyKey struct{ brand, family string }
	familyMap := make(map[brandFamilyKey][]MotorcycleFuel)
	var familyOrder []brandFamilyKey
	seen := make(map[brandFamilyKey]bool)
	for _, m := range motorcycles {
		k := brandFamilyKey{m.BrandName, m.ModelFName}
		familyMap[k] = append(familyMap[k], m)
		if !seen[k] {
			familyOrder = append(familyOrder, k)
			seen[k] = true
		}
	}

	brandFamilyMap := make(map[string][]FamilyGroup)
	for _, k := range familyOrder {
		items := familyMap[k]
		brandFamilyMap[k.brand] = append(brandFamilyMap[k.brand], FamilyGroup{
			FamilyName:  k.family,
			Motorcycles: items,
		})
	}

	var brandGroups []BrandGroup
	for _, bName := range brands {
		if fams, ok := brandFamilyMap[bName]; ok {
			brandGroups = append(brandGroups, BrandGroup{
				BrandName: bName,
				Families:  fams,
			})
		}
	}

	return map[string]interface{}{
		"Motorcycles":  motorcycles,
		"BrandGroups":  brandGroups,
		"Brands":       brands,
		"ProductTypes": productTypes,
	}, nil
}
