package main

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

	_ "modernc.org/sqlite"
)

var db *sql.DB

// --- DATA STRUCTURES ---

type SafePart struct {
	PartNumber  string  `json:"part_number"`
	Description string  `json:"description"`
	RetailPrice float64 `json:"retail_price"`
	QOH         int     `json:"qoh"`
	Store2QOH   int     `json:"store2_qoh"`
	OnOrder     int     `json:"on_order"`
	Alts        string  `json:"alts"` 
	Specs       string  `json:"specs"` 
	ImageUrl    string  `json:"image_url"`
	LookupUrl   string  `json:"lookup_url"`
	Equipment   string  `json:"equipment"`
}

type WebLoginRequest struct {
	Identifier string `json:"identifier"`
	PIN        string `json:"pin"`
}

type WebCustomerAuth struct {
	CustomerID string `json:"customer_id"`
	Name       string `json:"name"`
	Email      string `json:"email"`
}

type WebCartItem struct {
	PartNumber  string  `json:"part_number"`
	Description string  `json:"description"`
	Qty         float64 `json:"qty"`
	UnitPrice   float64 `json:"unit_price"`
}

type WebCheckoutRequest struct {
	CustomerID      string        `json:"customer_id"`
	CustomerName    string        `json:"customer_name"`
	CustomerPhone   string        `json:"customer_phone"`
	CustomerAddress string        `json:"customer_address"`
	Items           []WebCartItem `json:"items"`
}

type JournalEntry struct {
	InvoiceNumber  string  `json:"invoice_number"`
	DateCreated    string  `json:"date_created"`
	CustomerName   string  `json:"customer_name"`
	Make           string  `json:"make"`
	Model          string  `json:"model"`
	WorkDesc       string  `json:"work_desc"`
	FinalTotal     float64 `json:"final_total"`
	Subtotal       float64 `json:"subtotal"`
	LaborAmount    float64 `json:"labor_amount"`
	DeliveryAmount float64 `json:"delivery_amount"`
	TaxAmount      float64 `json:"tax_amount"`
}

type JournalItem struct {
	Qty         float64 `json:"qty"`
	PartNumber  string  `json:"part_number"`
	Description string  `json:"description"`
	UnitPrice   float64 `json:"unit_price"`
	LineTotal   float64 `json:"line_total"`
}

var (
	visitors         = make(map[string]*visitor)
	mu               sync.Mutex
	webCheckoutMutex sync.Mutex
)

type visitor struct {
	lastSeen time.Time
	tokens   int
}

// --- MIDDLEWARE ---

func rateLimitMiddleware(next http.HandlerFunc) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		ip := strings.Split(r.RemoteAddr, ":")[0]
		
		mu.Lock()
		v, exists := visitors[ip]
		if !exists {
			visitors[ip] = &visitor{lastSeen: time.Now(), tokens: 20}
			v = visitors[ip]
		}
		if time.Since(v.lastSeen) > time.Minute {
			v.tokens = 20 
		}
		v.lastSeen = time.Now()
		v.tokens--
		mu.Unlock()

		if v.tokens < 0 {
			http.Error(w, `{"error": "Rate limit exceeded"}`, http.StatusTooManyRequests)
			return
		}
		next.ServeHTTP(w, r)
	}
}

func noCacheHandler(h http.Handler) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		w.Header().Set("Cache-Control", "no-cache, no-store, must-revalidate")
		w.Header().Set("Pragma", "no-cache")
		w.Header().Set("Expires", "0")
		h.ServeHTTP(w, r)
	}
}

// --- PUBLIC READ-ONLY SETTINGS ---

func handleCntrlRead(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")
	w.Header().Set("Access-Control-Allow-Origin", "*")

	if r.Method == http.MethodGet {
		configMap := make(map[string]string)
		rows, err := db.Query("SELECT key, value FROM system_config")
		if err == nil {
			defer rows.Close()
			for rows.Next() {
				var key, value string
				if err := rows.Scan(&key, &value); err == nil {
					configMap[key] = value
				}
			}
		}
		json.NewEncoder(w).Encode(configMap)
		return
	}
	http.Error(w, "Method not allowed", http.StatusMethodNotAllowed)
}

// --- INVENTORY SEARCH ROUTE ---

func handleWebInventory(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Access-Control-Allow-Origin", "*")
	w.Header().Set("Content-Type", "application/json")
	
	if r.Method == http.MethodOptions {
		w.Header().Set("Access-Control-Allow-Methods", "GET")
		return
	}

	query := r.URL.Query().Get("q")
	if query == "" {
		http.Error(w, `{"error": "Search query required"}`, http.StatusBadRequest)
		return
	}

	cleanQuery := strings.ToUpper(strings.TrimSpace(query))
	isCategorySearch := strings.HasPrefix(cleanQuery, "CAT:")
	isEqCategorySearch := strings.HasPrefix(cleanQuery, "EQCAT:")
	isEquipSearch := cleanQuery == "EQUIPMENT"

	if isCategorySearch {
		cleanQuery = strings.TrimPrefix(cleanQuery, "CAT:")
	} else if isEqCategorySearch {
		cleanQuery = strings.TrimPrefix(cleanQuery, "EQCAT:")
	} else if !isEquipSearch {
		var catCount int
		db.QueryRow("SELECT COUNT(*) FROM parts WHERE UPPER(special_id) = ? AND COALESCE(web_store, 'Y') = 'Y'", cleanQuery).Scan(&catCount)
		if catCount > 0 {
			isCategorySearch = true
		}
	}

	searchPrefix := cleanQuery + "%"
	searchContains := "%" + cleanQuery + "%"

	var rows *sql.Rows
	var err error

	if isEquipSearch {
		rows, err = db.Query(`
			SELECT 
				COALESCE(part_number, ''), COALESCE(description, ''), IFNULL(trade_price, 0.0), IFNULL(quantity_on_hand, 0), IFNULL(store2_qoh, 0),
				IFNULL(on_order, 0),
				COALESCE(rotary_part_number, ''), COALESCE(husqvarna_part_number, ''), COALESCE(replaces, ''), COALESCE(replaced_by, ''),
				COALESCE(other_part_number, ''), COALESCE(specs, ''), COALESCE(image_url, ''), COALESCE(lookup_url, ''), COALESCE(equipment, 'N')
			FROM parts 
			WHERE COALESCE(equipment, 'N') = 'Y' AND COALESCE(web_store, 'Y') = 'Y'
			ORDER BY description ASC LIMIT 500`)
	} else if isEqCategorySearch {
		rows, err = db.Query(`
			SELECT 
				COALESCE(part_number, ''), COALESCE(description, ''), IFNULL(trade_price, 0.0), IFNULL(quantity_on_hand, 0), IFNULL(store2_qoh, 0),
				IFNULL(on_order, 0),
				COALESCE(rotary_part_number, ''), COALESCE(husqvarna_part_number, ''), COALESCE(replaces, ''), COALESCE(replaced_by, ''),
				COALESCE(other_part_number, ''), COALESCE(specs, ''), COALESCE(image_url, ''), COALESCE(lookup_url, ''), COALESCE(equipment, 'N')
			FROM parts 
			WHERE UPPER(special_id) = ? AND COALESCE(equipment, 'N') = 'Y' AND COALESCE(web_store, 'Y') = 'Y'
			ORDER BY description ASC LIMIT 500`, cleanQuery)
	} else if isCategorySearch {
		rows, err = db.Query(`
			SELECT 
				COALESCE(part_number, ''), COALESCE(description, ''), IFNULL(trade_price, 0.0), IFNULL(quantity_on_hand, 0), IFNULL(store2_qoh, 0),
				IFNULL(on_order, 0),
				COALESCE(rotary_part_number, ''), COALESCE(husqvarna_part_number, ''), COALESCE(replaces, ''), COALESCE(replaced_by, ''),
				COALESCE(other_part_number, ''), COALESCE(specs, ''), COALESCE(image_url, ''), COALESCE(lookup_url, ''), COALESCE(equipment, 'N')
			FROM parts 
			WHERE UPPER(special_id) = ? AND COALESCE(equipment, 'N') != 'Y' AND COALESCE(web_store, 'Y') = 'Y'
			ORDER BY part_number ASC LIMIT 500`, cleanQuery)
	} else {
		rows, err = db.Query(`
			SELECT 
				COALESCE(part_number, ''), COALESCE(description, ''), IFNULL(trade_price, 0.0), IFNULL(quantity_on_hand, 0), IFNULL(store2_qoh, 0),
				IFNULL(on_order, 0),
				COALESCE(rotary_part_number, ''), COALESCE(husqvarna_part_number, ''), COALESCE(replaces, ''), COALESCE(replaced_by, ''),
				COALESCE(other_part_number, ''), COALESCE(specs, ''), COALESCE(image_url, ''), COALESCE(lookup_url, ''), COALESCE(equipment, 'N')
			FROM parts 
			WHERE (part_number LIKE ? 
			   OR description LIKE ? 
			   OR rotary_part_number LIKE ? 
			   OR husqvarna_part_number LIKE ? 
			   OR replaces LIKE ? 
			   OR replaced_by LIKE ? 
			   OR other_part_number LIKE ?
			   OR special_id LIKE ?)
			   AND COALESCE(equipment, 'N') != 'Y'
			   AND COALESCE(web_store, 'Y') = 'Y'
			ORDER BY 
				CASE WHEN part_number = ? THEN 1 
					 WHEN special_id = ? THEN 2
					 WHEN description LIKE ? THEN 3
					 ELSE 4 END ASC, 
				part_number ASC
			LIMIT 500`, 
			searchPrefix, searchContains, searchPrefix, searchPrefix, searchPrefix, searchPrefix, searchPrefix, searchPrefix, 
			cleanQuery, cleanQuery, searchPrefix)
	}
	
	if err != nil {
		http.Error(w, `{"error": "Database error"}`, http.StatusInternalServerError)
		return
	}
	defer rows.Close()

	var parts []SafePart
	for rows.Next() {
		var p SafePart
		var price float64
		var qoh float64
		var store2Qoh float64
		var onOrder float64
		var rot, husq, rep, repBy, oth, specs, img, lookup, equip string
		
		if err := rows.Scan(&p.PartNumber, &p.Description, &price, &qoh, &store2Qoh, &onOrder, &rot, &husq, &rep, &repBy, &oth, &specs, &img, &lookup, &equip); err != nil {
			continue
		}
		
		p.RetailPrice = price
		p.QOH = int(qoh)
		p.Store2QOH = int(store2Qoh)
		p.OnOrder = int(onOrder)
		p.Specs = specs
		p.ImageUrl = img
		p.LookupUrl = lookup
		p.Equipment = equip
		
		var altList []string
		if rot != "" { altList = append(altList, rot) }
		if husq != "" { altList = append(altList, husq) }
		if rep != "" { altList = append(altList, rep) }
		if repBy != "" { altList = append(altList, repBy) }
		if oth != "" { altList = append(altList, oth) }
		
		p.Alts = strings.Join(altList, ", ")
		parts = append(parts, p)
	}

	if parts == nil { parts = []SafePart{} }
	json.NewEncoder(w).Encode(parts)
}

// --- CUSTOMER LOGIN & HISTORY ---

func handleWebCustomerLogin(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Access-Control-Allow-Origin", "*")
	w.Header().Set("Content-Type", "application/json")
	
	if r.Method == http.MethodOptions {
		w.Header().Set("Access-Control-Allow-Methods", "POST")
		w.Header().Set("Access-Control-Allow-Headers", "Content-Type")
		return
	}

	var req WebLoginRequest
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		http.Error(w, `{"error": "Invalid payload"}`, http.StatusBadRequest)
		return
	}

	identifier := strings.ToUpper(strings.TrimSpace(req.Identifier))
	pin := strings.TrimSpace(req.PIN)

	var auth WebCustomerAuth
	err := db.QueryRow(`
		SELECT customer_id, name, COALESCE(email, '') 
		FROM customers 
		WHERE (UPPER(customer_id) = ? OR phone1 = ? OR phone2 = ?) 
		AND COALESCE(web_pin, '') = ?`, 
		identifier, identifier, identifier, pin).Scan(&auth.CustomerID, &auth.Name, &auth.Email)

	if err != nil {
		time.Sleep(1 * time.Second) 
		http.Error(w, `{"error": "Invalid login credentials"}`, http.StatusUnauthorized)
		return
	}

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

func handleWebCustomerHistory(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Access-Control-Allow-Origin", "*")
	w.Header().Set("Content-Type", "application/json")

	customerID := strings.ToUpper(strings.TrimSpace(r.URL.Query().Get("customer_id")))
	if customerID == "" {
		http.Error(w, `{"error": "Customer ID required"}`, http.StatusBadRequest)
		return
	}

	query := `
		SELECT invoice_number, sale_date, make, model, work_desc, final_total
		FROM sales_history 
		WHERE TRIM(UPPER(customer_id)) = ? AND invoice_number NOT LIKE 'S-%' AND invoice_number != 'YTD-INIT' AND invoice_number != 'MAINT'
		ORDER BY sale_date DESC, invoice_number DESC LIMIT 50`

	rows, err := db.Query(query, customerID)
	if err != nil {
		http.Error(w, `{"error": "Database error"}`, http.StatusInternalServerError)
		return
	}
	defer rows.Close()

	var history []map[string]interface{}
	for rows.Next() {
		var invNo, sDate, make, model, workDesc sql.NullString
		var total sql.NullFloat64

		if err := rows.Scan(&invNo, &sDate, &make, &model, &workDesc, &total); err == nil {
			history = append(history, map[string]interface{}{
				"invoice_number": strings.TrimSpace(invNo.String),
				"sale_date":      strings.TrimSpace(sDate.String),
				"make":           strings.TrimSpace(make.String),
				"model":          strings.TrimSpace(model.String),
				"work_desc":      strings.TrimSpace(workDesc.String),
				"total":          total.Float64,
			})
		}
	}
	if history == nil { history = []map[string]interface{}{} }
	json.NewEncoder(w).Encode(history)
}

func handleWebCustomerInvoice(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Access-Control-Allow-Origin", "*")
	w.Header().Set("Content-Type", "application/json")

	invoiceNo := strings.TrimSpace(r.URL.Query().Get("invoice_number"))
	customerID := strings.ToUpper(strings.TrimSpace(r.URL.Query().Get("customer_id"))) 

	if invoiceNo == "" || customerID == "" {
		http.Error(w, `{"error": "Invoice Number and Customer ID required"}`, http.StatusBadRequest)
		return
	}

	var entry JournalEntry
	err := db.QueryRow(`
		SELECT 
			COALESCE(invoice_number, ''), COALESCE(sale_date, ''), COALESCE(make, ''), COALESCE(model, ''), COALESCE(work_desc, ''), COALESCE(final_total, 0.0), COALESCE(tax_amount, 0.0)
		FROM sales_history 
		WHERE invoice_number = ? AND TRIM(UPPER(customer_id)) = ?`, invoiceNo, customerID).
		Scan(&entry.InvoiceNumber, &entry.DateCreated, &entry.Make, &entry.Model, &entry.WorkDesc, &entry.FinalTotal, &entry.TaxAmount)

	if err != nil {
		http.Error(w, `{"error": "Invoice not found"}`, http.StatusNotFound)
		return
	}

	itemRows, err := db.Query(`
		SELECT IFNULL(quantity, 0.0), COALESCE(part_number, ''), COALESCE(description, ''), IFNULL(sell_price, 0.0), IFNULL(line_total, 0.0)
		FROM sales_items 
		WHERE sale_id = (SELECT sale_id FROM sales_history WHERE invoice_number = ? LIMIT 1)`, invoiceNo)
	
	if err != nil {
		http.Error(w, `{"error": "Item scan failed"}`, http.StatusInternalServerError)
		return
	}
	defer itemRows.Close()

	var items []JournalItem
	for itemRows.Next() {
		var itm JournalItem
		if err := itemRows.Scan(&itm.Qty, &itm.PartNumber, &itm.Description, &itm.UnitPrice, &itm.LineTotal); err == nil {
			if itm.PartNumber == "SYS:LABOR" {
				entry.LaborAmount = itm.LineTotal
			} else if itm.PartNumber == "SYS:DELIVERY" {
				entry.DeliveryAmount = itm.LineTotal
			} else {
				entry.Subtotal += itm.LineTotal
				items = append(items, itm)
			}
		}
	}

	json.NewEncoder(w).Encode(map[string]interface{}{"invoice_details": entry, "items": items})
}

// --- ORDER CHECKOUT ---

func handleWebCheckout(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Access-Control-Allow-Origin", "*")
	w.Header().Set("Content-Type", "application/json")
	
	if r.Method == http.MethodOptions {
		w.Header().Set("Access-Control-Allow-Methods", "POST")
		w.Header().Set("Access-Control-Allow-Headers", "Content-Type")
		return
	}

	if r.Method != http.MethodPost {
		http.Error(w, `{"error": "Method not allowed"}`, http.StatusMethodNotAllowed)
		return
	}

	var req WebCheckoutRequest
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		http.Error(w, `{"error": "Invalid payload"}`, http.StatusBadRequest)
		return
	}

	if len(req.Items) == 0 {
		http.Error(w, `{"error": "Cart is empty"}`, http.StatusBadRequest)
		return
	}

	webCheckoutMutex.Lock()
	defer webCheckoutMutex.Unlock()

	currentTimeStr := time.Now().Format("01/02/2006")
	transactionSaleID := fmt.Sprintf("WEB-%d", time.Now().UnixNano())
	
	var activeInvoiceInt int
	db.QueryRow("SELECT IFNULL(MAX(CAST(REPLACE(invoice_number, 'W-', '') AS INTEGER)), 5000) + 1 FROM sales_history WHERE invoice_number LIKE 'W-%'").Scan(&activeInvoiceInt)
	if activeInvoiceInt < 5000 { activeInvoiceInt = 5001 } 
	generatedInvoiceNo := fmt.Sprintf("W-%d", activeInvoiceInt)

	var subtotal float64 = 0.0
	for _, item := range req.Items {
		subtotal += (item.Qty * item.UnitPrice)
	}
	
	taxAmount := subtotal * 0.06
	finalTotal := subtotal + taxAmount

	custID := "WEB-GUEST"
	if req.CustomerID != "" { custID = req.CustomerID }
	
	custName := "WEB GUEST CHECKOUT"
	if req.CustomerName != "" { custName = req.CustomerName }

	_, err := db.Exec(`INSERT INTO sales_history (sale_id, invoice_number, sale_date, customer_id, final_total, tax_amount, status, sale_type, subtotal) VALUES (?, ?, ?, ?, ?, ?, 'PENDING', 'WEB', ?)`,
		transactionSaleID, generatedInvoiceNo, currentTimeStr, custID, finalTotal, taxAmount, subtotal)
	if err != nil {
		http.Error(w, `{"error": "Failed to save order"}`, http.StatusInternalServerError)
		return
	}

	for _, item := range req.Items {
		lineTotal := item.Qty * item.UnitPrice
		db.Exec(`INSERT INTO sales_items (sale_id, part_number, description, quantity, sell_price, line_total) VALUES (?, ?, ?, ?, ?, ?)`,
			transactionSaleID, item.PartNumber, item.Description, item.Qty, item.UnitPrice, lineTotal)
	}
	
	db.Exec(`INSERT INTO sales_items (sale_id, part_number, description, quantity, sell_price, line_total) VALUES (?, 'SYS:NOTE', ?, 0, 0, 0)`,
			transactionSaleID, "WEB ORDER BY: " + custName)
			
	if req.CustomerPhone != "" {
		db.Exec(`INSERT INTO sales_items (sale_id, part_number, description, quantity, sell_price, line_total) VALUES (?, 'SYS:NOTE', ?, 0, 0, 0)`,
				transactionSaleID, "PHONE: " + req.CustomerPhone)
	}
	
	if req.CustomerAddress != "" {
		db.Exec(`INSERT INTO sales_items (sale_id, part_number, description, quantity, sell_price, line_total) VALUES (?, 'SYS:NOTE', ?, 0, 0, 0)`,
				transactionSaleID, "SHIP/BILL TO: " + req.CustomerAddress)
	}

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

func main() {
	var err error

	dbPath := "C:/D3/PRM/CHALLENGER.db?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)"
	db, err = sql.Open("sqlite", dbPath)
	if err != nil {
		log.Fatalf("FATAL ERROR: Failed to open database at %s - %v\n", dbPath, err)
	}
	defer db.Close() 

	if err = db.Ping(); err != nil {
		log.Fatalf("FATAL ERROR: Cannot reach DB - %v\n", err)
	}

	// Read-Only System config for pulling promo banners safely
	http.HandleFunc("/api/cntrl", handleCntrlRead)
	
	// Public E-Commerce Endpoints
	http.HandleFunc("/api/web/inventory", rateLimitMiddleware(handleWebInventory))
	http.HandleFunc("/api/web/login", rateLimitMiddleware(handleWebCustomerLogin))
	http.HandleFunc("/api/web/customer/history", rateLimitMiddleware(handleWebCustomerHistory))
	http.HandleFunc("/api/web/customer/invoice", rateLimitMiddleware(handleWebCustomerInvoice))
	http.HandleFunc("/api/web/checkout", handleWebCheckout) 

	// Serve the static files (HTML, CSS, JS, Images, Manuals)
	http.Handle("/manuals/", http.StripPrefix("/manuals/", http.FileServer(http.Dir("C:/D3/PRG/MANUALS"))))
	fs := http.FileServer(http.Dir("."))
	http.Handle("/", noCacheHandler(fs))

	// Listen on PORT 6061 specifically for the public store
	port := ":6061"
	log.Printf("PUBLIC STORE ONLINE: Web server running securely on port %s\n", port)
	err = http.ListenAndServe(port, nil)
	if err != nil {
		log.Fatalf("FATAL ERROR: Web server crashed - %v\n", err)
	}
}