package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"html/template"
	"log"
	"net/http"
	"net/url"
	"strings"

	_ "github.com/microsoft/go-mssqldb"
)

var db *sql.DB

type Brand struct {
	BrandID   string
	BrandName string
}

// 1. เพิ่ม ProductID ในโครงสร้างข้อมูล
type Product struct {
	ProductID    int     `json:"product_id"`
	BrandID      string  `json:"brand_id"`
	BrandName    string  `json:"brand_name"`
	ProductName  string  `json:"product_name"`
	ProductPrice float64 `json:"product_price"`
	ProductImg   string  `json:"product_img"`
}

type MainPageData struct {
	User   string
	Brands []Brand
}

// โครงสร้างข้อมูลสำหรับตัวเลือกกิจกรรม (Booth)
type Booth struct {
	IF_ID   string
	BS_ID   int
	BS_NAME string
}

// โครงสร้างข้อมูลสำหรับตัวเลือกช่องทางทั่วไป (InfoSource)
type InfoSource struct {
	IF_ID   string
	IF_NAME string
}

// โครงสร้างข้อมูลสำหรับอาชีพ
type Occupation struct {
	OC_CODE string
	OC_NAME string
}

type Province struct {
	PR_CODE string
	PR_NAME string
}

type Amphure struct {
	AM_CODE string
	AM_NAME string
}

type District struct {
	TU_CODE string
	TU_NAME string
}

type Employee struct {
	Tname    string
	Fname    string
	Lname    string
	Nick     string
	Barcode  string
	BranchID string
}

// ปรับปรุง InsertPageData ให้รองรับ Provinces และ Branches และ Employee
type InsertPageData struct {
	User        string
	Product     Product
	Booths      []Booth
	InfoFrom    []InfoSource
	Occupations []Occupation
	Provinces   []Province
	Branches    []Branch
	Employee    Employee
}

type Branch struct {
	BR_ID   string
	BR_NAME string
	PR_CODE string
}

func main() {
	var err error
	connStr := "server=192.168.10.61;user id=pond;password=Ehongmd2025;port=1433;database=CONFIG;encrypt=disable"
	db, err = sql.Open("sqlserver", connStr)
	if err != nil {
		log.Fatal(err)
	}

	http.HandleFunc("/_go.lead/", func(w http.ResponseWriter, r *http.Request) {
		// Strip /_go.lead prefix to get the actual file path
		path := strings.TrimPrefix(r.URL.Path, "/_go.lead")
		if path == "" || path == "/" {
			http.ServeFile(w, r, "index.html")
			return
		}
		http.ServeFile(w, r, "."+path)
	})

	http.HandleFunc("/_go.lead/main", func(w http.ResponseWriter, r *http.Request) {
		cookie, err := r.Cookie("user_session")
		if err != nil {
			http.Redirect(w, r, "/_go.lead/", http.StatusSeeOther)
			return
		}
		displayName, _ := url.QueryUnescape(cookie.Value)

		var brands []Brand
		query := "SELECT BRAND_ID, BRAND_NAME FROM CONFIG.dbo.NEWEHONGMD_BRAND WHERE BRAND_STATUS NOT IN ('1')"
		rows, err := db.Query(query)
		if err != nil {
			log.Println("Database Error:", err)
		} else {
			defer rows.Close()
			for rows.Next() {
				var b Brand
				if err := rows.Scan(&b.BrandID, &b.BrandName); err == nil {
					brands = append(brands, b)
				}
			}
		}

		data := MainPageData{User: displayName, Brands: brands}
		tmpl, _ := template.ParseFiles("main.html")
		tmpl.Execute(w, data)
	})

	// --- 2. ปรับปรุง API ดึงสินค้า (ดึง PRODUCT_ID มาด้วย) ---
	http.HandleFunc("/_go.lead/api/products", func(w http.ResponseWriter, r *http.Request) {
		brandID := r.URL.Query().Get("brand_id")
		query := `SELECT PRODUCT_ID, BRAND_ID, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_IMG 
				  FROM CONFIG.dbo.NEWEHONGMD_PRODUCT WHERE BRAND_ID = @brandID`

		rows, err := db.Query(query, sql.Named("brandID", brandID))
		if err != nil {
			http.Error(w, err.Error(), http.StatusInternalServerError)
			return
		}
		defer rows.Close()

		var products []Product
		for rows.Next() {
			var p Product
			if err := rows.Scan(&p.ProductID, &p.BrandID, &p.ProductName, &p.ProductPrice, &p.ProductImg); err == nil {
				products = append(products, p)
			}
		}
		w.Header().Set("Content-Type", "application/json")
		json.NewEncoder(w).Encode(products)
	})

	// --- 3. เพิ่ม Handler สำหรับหน้า maininsert.html ---
	http.HandleFunc("/_go.lead/maininsert", func(w http.ResponseWriter, r *http.Request) {
		cookie, _ := r.Cookie("user_session")
		displayName, _ := url.QueryUnescape(cookie.Value)
		
		productID := r.URL.Query().Get("product_id")
		
		// 1. SELECT ข้อมูลสินค้าตัวที่เลือก
		queryProduct := `SELECT a.PRODUCT_ID, a.BRAND_ID, a.PRODUCT_NAME, a.PRODUCT_PRICE, a.PRODUCT_IMG, b.BRAND_NAME 
			FROM CONFIG.dbo.NEWEHONGMD_PRODUCT a 
			LEFT OUTER JOIN (SELECT BRAND_ID, BRAND_NAME FROM CONFIG.dbo.NEWEHONGMD_BRAND) b ON (a.BRAND_ID = b.BRAND_ID) 
			WHERE a.PRODUCT_ID = @pID`
					
		var p Product
		err := db.QueryRow(queryProduct, sql.Named("pID", productID)).
			Scan(&p.ProductID, &p.BrandID, &p.ProductName, &p.ProductPrice, &p.ProductImg, &p.BrandName)

		if err != nil {
			log.Println("Product not found:", err)
		}

		// 2. ดึงข้อมูล Booths (IF_ID = '8')
		var booths []Booth
		queryBooths := "SELECT IF_ID, BS_ID, BS_NAME FROM CONFIG.dbo.NEWEHONGMD_BOOTHS WHERE IF_ID = '8' AND BS_ID IN (5, 15, 16, 17) ORDER BY BS_ID"
		rowsB, _ := db.Query(queryBooths)
		if rowsB != nil {
			defer rowsB.Close()
			for rowsB.Next() {
				var b Booth
				if err := rowsB.Scan(&b.IF_ID, &b.BS_ID, &b.BS_NAME); err == nil {
					booths = append(booths, b)
				}
			}
		}

		// 3. ดึงข้อมูล InfoFrom (ยกเว้น '8' และ '16')
		var infoFrom []InfoSource
		queryInfo := "SELECT IF_ID, IF_NAME FROM CONFIG.dbo.NEWEHONGMD_INFOFROM WHERE IF_ID NOT IN ('8', '16') ORDER BY IF_ID"
		rowsI, _ := db.Query(queryInfo)
		if rowsI != nil {
			defer rowsI.Close()
			for rowsI.Next() {
				var i InfoSource
				if err := rowsI.Scan(&i.IF_ID, &i.IF_NAME); err == nil {
					infoFrom = append(infoFrom, i)
				}
			}
		}

		// 4. ดึงข้อมูลอาชีพ
		var occupations []Occupation
		queryOcc := "SELECT OC_CODE, OC_NAME FROM EHONGDB.dbo.OCCUPATION ORDER BY OC_CODE"
		rowsO, _ := db.Query(queryOcc)
		if rowsO != nil {
			defer rowsO.Close()
			for rowsO.Next() {
				var o Occupation
				if err := rowsO.Scan(&o.OC_CODE, &o.OC_NAME); err == nil {
					occupations = append(occupations, o)
				}
			}
		}

		// 5. ดึงข้อมูลจังหวัด (Query ตามเงื่อนไขที่คุณให้มา)
			var provinces []Province
			queryProv := `SELECT DISTINCT RTRIM(p.PR_CODE) AS PR_CODE, RTRIM(p.PR_NAME) AS PR_NAME
						FROM EHONGDB.dbo.PROV p
						INNER JOIN EHONGDB.dbo.BRANCH b ON p.PR_CODE = b.PR_CODE
						WHERE b.ACTIVATE = '0' 
						AND b.BR_ID NOT IN ('0002','0003','0018','0034','0059','0082','0105','0116','0501','0502','0503','0506','0507')
						ORDER BY PR_NAME`
			
			rowsP, errP := db.Query(queryProv)
			if errP != nil {
				log.Println("❌ Province Query Error:", errP)
			} else if rowsP != nil {
				defer rowsP.Close()
				for rowsP.Next() {
					var p Province
					if err := rowsP.Scan(&p.PR_CODE, &p.PR_NAME); err == nil {
						provinces = append(provinces, p)
					}
				}
				log.Printf("✅ Loaded %d provinces\n", len(provinces))
			}

			data := InsertPageData{
				User:        displayName,
				Product:     p,
				Booths:      booths,
				InfoFrom:    infoFrom,
				Occupations: occupations,
				Provinces:   provinces,
			}

			// ดึงข้อมูลสาขา (เพื่อให้หน้า HTML ไม่ Error)
			var branches []Branch
			queryBr := "SELECT BRANCH_ID, BRANCH_NAME FROM [CONFIG].[dbo].[NEWEHONGMD_BRANCH] WHERE BRANCH_STATUS = '1' ORDER BY BRANCH_NAME"
			rowsBr, _ := db.Query(queryBr)
			if rowsBr != nil {
				defer rowsBr.Close()
				for rowsBr.Next() {
					var b Branch
					if err := rowsBr.Scan(&b.BR_ID, &b.BR_NAME); err == nil {
						branches = append(branches, b)
					}
				}
			}
			// ดึงข้อมูลพนักงานจาก Cookies
			getCookie := func(name string) string {
				c, err := r.Cookie(name)
				if err != nil {
					return ""
				}
				val, _ := url.QueryUnescape(c.Value)
				return val
			}

			data.Employee = Employee{
				Barcode:  getCookie("user_barcode"),
				Tname:    getCookie("user_tname"),
				Fname:    getCookie("user_fname"),
				Lname:    getCookie("user_lname"),
				Nick:     getCookie("user_nick"),
				BranchID: getCookie("user_branch"),
			}

			data.Branches = branches

			tmpl, _ := template.ParseFiles("maininsert.html")
			tmpl.Execute(w, data)
		})

	// API ดึงอำเภอ
	http.HandleFunc("/_go.lead/api/amphures", func(w http.ResponseWriter, r *http.Request) {
		prCode := r.URL.Query().Get("pr_code")
		query := "SELECT RTRIM(AM_CODE), RTRIM(AM_NAME) FROM EHONGDB.dbo.AMP WHERE PR_CODE = @prCode ORDER BY AM_NAME"
		rows, err := db.Query(query, sql.Named("prCode", prCode))
		if err != nil {
			http.Error(w, err.Error(), 500)
			return
		}
		defer rows.Close()
		var list []Amphure
		for rows.Next() {
			var a Amphure
			rows.Scan(&a.AM_CODE, &a.AM_NAME)
			list = append(list, a)
		}
		w.Header().Set("Content-Type", "application/json")
		json.NewEncoder(w).Encode(list)
	})

	// API ดึงตำบล
	http.HandleFunc("/_go.lead/api/districts", func(w http.ResponseWriter, r *http.Request) {
		prCode := r.URL.Query().Get("pr_code")
		amCode := r.URL.Query().Get("am_code")
		query := "SELECT RTRIM(TU_CODE), RTRIM(TU_NAME) FROM EHONGDB.dbo.TUM WHERE PR_CODE = @prCode AND AM_CODE = @amCode ORDER BY TU_NAME"
		rows, err := db.Query(query, sql.Named("prCode", prCode), sql.Named("amCode", amCode))
		if err != nil {
			http.Error(w, err.Error(), 500)
			return
		}
		defer rows.Close()
		var list []District
		for rows.Next() {
			var d District
			rows.Scan(&d.TU_CODE, &d.TU_NAME)
			list = append(list, d)
		}
		w.Header().Set("Content-Type", "application/json")
		json.NewEncoder(w).Encode(list)
	})

	// API สำหรับดึงสาขาตามจังหวัด
	http.HandleFunc("/_go.lead/api/branches", func(w http.ResponseWriter, r *http.Request) {
		prCode := r.URL.Query().Get("pr_code")
		
		var branches []Branch
		query := `SELECT BR_ID, BR_NAME, PR_CODE
				FROM EHONGDB.dbo.BRANCH
				WHERE BR_ID NOT IN ('0002','0003','0018','0034','0059','0082','0105','0116',
				'0501','0502','0503','0506','0507','0508','0510','0512',
				'0888','0889','0900','0901','0902','0903','0904','0905',
				'0906','0907','0908','0990','0991','0995')
				AND ACTIVATE = '0'
				AND PR_CODE = @prCode
				ORDER BY BR_ID`

		rows, err := db.Query(query, sql.Named("prCode", prCode))
		if err != nil {
			log.Println("Branch Query Error:", err)
			http.Error(w, err.Error(), 500)
			return
		}
		defer rows.Close()

		for rows.Next() {
			var b Branch
			if err := rows.Scan(&b.BR_ID, &b.BR_NAME, &b.PR_CODE); err == nil {
				branches = append(branches, b)
			}
		}

		w.Header().Set("Content-Type", "application/json")
		json.NewEncoder(w).Encode(branches)
	})

	// Endpoint สำหรับแสดงหน้า Preview ข้อมูล
	http.HandleFunc("/_go.lead/insertdata", func(w http.ResponseWriter, r *http.Request) {
		if r.Method != http.MethodPost {
			http.Redirect(w, r, "/_go.lead/main", http.StatusSeeOther)
			return
		}

		err := r.ParseForm()
		if err != nil {
			http.Error(w, "ParseForm error: "+err.Error(), 500)
			return
		}

		// รับค่าจากฟอร์ม
		data := map[string]string{
			"product_id":   r.FormValue("product_id"),
			"ProductName":  r.FormValue("ProductName"),
			"ProductPrice": r.FormValue("ProductPrice"),
			"ProductImg":   r.FormValue("ProductImg"),
			"BrandName":    r.FormValue("BrandName"),
			"brand":        r.FormValue("brand"),
			"model":        r.FormValue("model"),
			"firstname":    r.FormValue("firstname"),
			"lastname":     r.FormValue("lastname"),
			"phone":        r.FormValue("phone"),
			"email":        r.FormValue("email"),
			"career":       r.FormValue("career"),
			"career_name":  r.FormValue("career_name"),
			"province":     r.FormValue("province"),
			"province_name": r.FormValue("province_name"),
			"amphure":      r.FormValue("amphure"),
			"amphure_name": r.FormValue("amphure_name"),
			"district":     r.FormValue("district"),
			"district_name": r.FormValue("district_name"),
			"branch":       r.FormValue("branch"),
			"branch_name":  r.FormValue("branch_name"),
			"info_source":  r.FormValue("info_source"),
			"info_name":    r.FormValue("info_name"),
			"timeline":     r.FormValue("timeline"),
			"timeline_name": r.FormValue("timeline_name"),
			"reg_date":      r.FormValue("reg_date"),
			"reg_time":      r.FormValue("reg_time"),
			"lat":           r.FormValue("lat"),
			"lng":           r.FormValue("lng"),
			"price":          r.FormValue("price"),
			"comment":        r.FormValue("comment"),
			"emp_barcode":    r.FormValue("emp_barcode"),
			"emp_tname":      r.FormValue("emp_tname"),
			"emp_fname":      r.FormValue("emp_fname"),
			"emp_lname":      r.FormValue("emp_lname"),
			"emp_nick":       r.FormValue("emp_nick"),
			"emp_br_id":      r.FormValue("emp_br_id"),
		}

		// แสดงข้อมูลใน Console เพื่อตรวจสอบ
		log.Println("=== Preview Data ===")
		for key, value := range data {
			log.Printf("%s: %s\n", key, value)
		}
		log.Println("===================")

		// รับข้อมูลผู้ใช้จาก Cookie เพื่อไปโชว์ที่ Header
		cookie, _ := r.Cookie("user_session")
		displayName, _ := url.QueryUnescape(cookie.Value)

		// ส่งข้อมูลไปแสดงในหน้า Preview
		tmpl, _ := template.ParseFiles("preview.html")
		tmpl.Execute(w, map[string]interface{}{
			"Data": data,
			"User": displayName,
		})
	})

	http.HandleFunc("/_go.lead/save_lead", func(w http.ResponseWriter, r *http.Request) {
		if r.Method != http.MethodPost {
			http.Redirect(w, r, "/_go.lead/main", http.StatusSeeOther)
			return
		}

		err := r.ParseForm()
		if err != nil {
			http.Error(w, "ParseForm error: "+err.Error(), 500)
			return
		}

		// Sanitize phone number (remove dashes) to fit 10-char limit
		phone := strings.ReplaceAll(r.FormValue("INTERRAT_PHONE"), "-", "")

		query := `INSERT INTO [CONFIG].[dbo].[NEWEHONGMD_INTERRAT] (
			INTERRAT_FRISTNAME, INTERRAT_LASTNAME, INTERRAT_PHONE, INTERRAT_EMAIL,
			INTERRAT_MOTORNAME, PRODUCT_ID, INTERRAT_PROVINCE, INTERRAT_DISTRICT,
			INTERRAT_SUB_DISTRICT, INTERRAT_DATE, INTERRAT_TIME, BR_ID,
			IF_ID, INTERRAT_ADMIN, PR_CODE, CONTACT_STATUS,
			SCORE_STATUS, BS_ID, OC_CODE, SUGGESTION,
			INTERRAT_SOCIAL_DETAIL, APP_CODE, INTERRAT_LATITUDE, INTERRAT_LONGITUDE
		) VALUES (
			@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12,
			@p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24
		)`

		_, err = db.Exec(query,
			sql.Named("p1", r.FormValue("INTERRAT_FRISTNAME")),
			sql.Named("p2", r.FormValue("INTERRAT_LASTNAME")),
			sql.Named("p3", phone),
			sql.Named("p4", r.FormValue("INTERRAT_EMAIL")),
			sql.Named("p5", r.FormValue("INTERRAT_MOTORNAME")),
			sql.Named("p6", r.FormValue("PRODUCT_ID")),
			sql.Named("p7", r.FormValue("INTERRAT_PROVINCE")),
			sql.Named("p8", r.FormValue("INTERRAT_DISTRICT")),
			sql.Named("p9", r.FormValue("INTERRAT_SUB_DISTRICT")),
			sql.Named("p10", r.FormValue("INTERRAT_DATE")),
			sql.Named("p11", r.FormValue("INTERRAT_TIME")),
			sql.Named("p12", r.FormValue("BR_ID")),
			sql.Named("p13", r.FormValue("IF_ID")),
			sql.Named("p14", r.FormValue("INTERRAT_ADMIN")),
			sql.Named("p15", r.FormValue("PR_CODE")),
			sql.Named("p16", r.FormValue("CONTACT_STATUS")),
			sql.Named("p17", r.FormValue("SCORE_STATUS")),
			sql.Named("p18", r.FormValue("BS_ID")),
			sql.Named("p19", r.FormValue("OC_CODE")),
			sql.Named("p20", r.FormValue("SUGGESTION")),
			sql.Named("p21", r.FormValue("INTERRAT_SOCIAL_DETAIL")),
			sql.Named("p22", r.FormValue("APP_CODE")),
			sql.Named("p23", r.FormValue("INTERRAT_LATITUDE")),
			sql.Named("p24", r.FormValue("INTERRAT_LONGITUDE")),
		)

		if err != nil {
			log.Println("Insert Error:", err)
			w.Header().Set("Content-Type", "text/html; charset=utf-8")
			errMsg := strings.ReplaceAll(err.Error(), "'", "")
			errMsg = strings.ReplaceAll(errMsg, "\n", " ")
			fmt.Fprintf(w, `
				<script src="https://cdn.jsdelivr.net/npm/sweetalert2@11"></script>
				<link href="https://fonts.googleapis.com/css2?family=Kanit:wght@300&display=swap" rel="stylesheet">
				<style>body { font-family: 'Kanit', sans-serif; }</style>
				<script>
					window.onload = function() {
						Swal.fire({
							icon: 'error',
							title: 'บันทึกข้อมูลไม่สำเร็จ',
							text: '%s',
							confirmButtonColor: '#ff3b3b',
							confirmButtonText: 'ตกลง'
						}).then(() => {
							history.back();
						});
					};
				</script>
			`, errMsg)
			return
		}

		w.Header().Set("Content-Type", "text/html; charset=utf-8")
		fmt.Fprintf(w, `
			<script src="https://cdn.jsdelivr.net/npm/sweetalert2@11"></script>
			<link href="https://fonts.googleapis.com/css2?family=Kanit:wght@300&display=swap" rel="stylesheet">
			<style>body { font-family: 'Kanit', sans-serif; }</style>
			<script>
				window.onload = function() {
					Swal.fire({
						icon: 'success',
						title: 'สำเร็จ!',
						text: 'บันทึกข้อมูลเรียบร้อยแล้ว',
						confirmButtonColor: '#28a745',
						confirmButtonText: 'กลับหน้าหลัก'
					}).then(() => {
						window.location = '/_go.lead/main';
					});
				};
			</script>
		`)
	})

	http.HandleFunc("/_go.lead/login", loginHandler)
	http.HandleFunc("/_go.lead/logout", logoutHandler)

	fmt.Println("✅ Server running at http://localhost:9087/_go.lead/")
	log.Fatal(http.ListenAndServe(":9087", nil))
}

// ... คงฟังก์ชัน loginHandler และ logoutHandler ไว้ตามเดิม ...
func loginHandler(w http.ResponseWriter, r *http.Request) {
    if r.Method != http.MethodPost {
        http.Redirect(w, r, "/_go.lead/", http.StatusSeeOther)
        return
    }
    user := r.FormValue("username")
    pass := r.FormValue("password")
    query := `SELECT TNAME, FNAME, LNAME, NICK, BARCODE, BR_ID FROM [EHONGDB].[dbo].[EMPLOYEE] 
              WHERE (BARCODE = @user OR US_NAME = @user) AND US_PASSWD = @pass`
    var tname, fname, lname, nick, barcode, br_id string
    err := db.QueryRow(query, sql.Named("user", user), sql.Named("pass", pass)).
        Scan(&tname, &fname, &lname, &nick, &barcode, &br_id)
    if err != nil {
        w.Header().Set("Content-Type", "text/html; charset=utf-8")
        fmt.Fprintf(w, "<script>alert('เข้าสู่ระบบไม่สำเร็จ'); window.location='/_go.lead/';</script>")
        return
    }
    displayName := fmt.Sprintf("%s : %s%s %s", barcode, tname, fname, lname)

    // ตั้งค่า Cookie พื้นฐาน
    http.SetCookie(w, &http.Cookie{Name: "user_session", Value: url.QueryEscape(displayName), Path: "/", HttpOnly: true})
    
    // ตั้งค่า Cookie ข้อมูลพนักงานสำหรับการบันทึก
    http.SetCookie(w, &http.Cookie{Name: "user_barcode", Value: barcode, Path: "/", HttpOnly: true})
    http.SetCookie(w, &http.Cookie{Name: "user_tname", Value: url.QueryEscape(tname), Path: "/", HttpOnly: true})
    http.SetCookie(w, &http.Cookie{Name: "user_fname", Value: url.QueryEscape(fname), Path: "/", HttpOnly: true})
    http.SetCookie(w, &http.Cookie{Name: "user_lname", Value: url.QueryEscape(lname), Path: "/", HttpOnly: true})
    http.SetCookie(w, &http.Cookie{Name: "user_nick", Value: url.QueryEscape(nick), Path: "/", HttpOnly: true})
    http.SetCookie(w, &http.Cookie{Name: "user_branch", Value: br_id, Path: "/", HttpOnly: true})

    http.Redirect(w, r, "/_go.lead/main", http.StatusSeeOther)
}


func logoutHandler(w http.ResponseWriter, r *http.Request) {
    http.SetCookie(w, &http.Cookie{Name: "user_session", Value: "", Path: "/", MaxAge: -1})
    http.Redirect(w, r, "/_go.lead/", http.StatusSeeOther)
}