package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"io"
	"log"
	"net/http"
	"os"
	"path/filepath"
	"strings"
	"sync"
	"time"

	_ "modernc.org/sqlite"
)

var db *sql.DB

func loggingMiddleware(next http.Handler) http.Handler {
	return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
		ip := r.RemoteAddr
		if colonIdx := strings.LastIndex(ip, ":"); colonIdx != -1 {
			ip = ip[:colonIdx]
		}
		log.Printf(">> SYSTEM ACTIVITY: Device [%s] executed [%s] %s", ip, r.Method, r.URL.RequestURI())
		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)
	}
}

func convertToFloat(v interface{}) float64 {
	if v == nil { return 0.0 }
	switch t := v.(type) {
	case float64: return t
	case float32: return float64(t)
	case int64:   return float64(t)
	case int:     return float64(t)
	case []byte:
		var f float64
		fmt.Sscanf(string(t), "%f", &f)
		return f
	case string:
		var f float64
		fmt.Sscanf(t, "%f", &f)
		return f
	}
	return 0.0
}

func coalesceString(m map[string]interface{}, keys ...string) string {
	for _, k := range keys {
		if val, ok := m[k]; ok && val != nil {
			return strings.TrimSpace(fmt.Sprintf("%v", val))
		}
	}
	return ""
}

func coalesceFloat(m map[string]interface{}, keys ...string) float64 {
	for _, k := range keys {
		if val, ok := m[k]; ok && val != nil {
			return convertToFloat(val)
		}
	}
	return 0.0
}

func copyFileContents(src, dst string) error {
	in, err := os.Open(src)
	if err != nil { return err }
	defer in.Close()

	if err := os.MkdirAll(filepath.Dir(dst), 0755); err != nil { return err }

	out, err := os.Create(dst)
	if err != nil { return err }
	defer out.Close()

	if _, err = io.Copy(out, in); err != nil { return err }
	return out.Sync()
}

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

type PSIInvoiceRequest struct {
	InvoiceID       string    `json:"invoice_id"`
	Status          string    `json:"status"`
	Salesman        string    `json:"salesman"`
	CustomerName    string    `json:"customer_name"`
	CustomerID      string    `json:"customer_id"`
	PONumber        string    `json:"po_number"`
	ShipVia         string    `json:"ship_via"`
	SaleType        string    `json:"sale_type"`
	Make            string    `json:"make"`
	Model           string    `json:"model"`
	WorkDesc        string    `json:"work_desc"`
	Subtotal        float64   `json:"subtotal"`
	LaborCharges    float64   `json:"labor_charges"`
	DeliveryCharges float64   `json:"delivery_charges"`
	TaxAmount       float64   `json:"tax_amount"`
	TotalAmount     float64   `json:"total_amount"`
	Items           []PSIItem `json:"items"`
}

type StoredInvoice struct {
	InvoiceID       string    `json:"invoice_id"`
	CreatedAt       string    `json:"created_at"`
	CustomerID      string    `json:"customer_id"`
	CustomerName    string    `json:"customer_name"`
	Make            string    `json:"make"`
	Model           string    `json:"model"`
	WorkDesc        string    `json:"work_desc"`
	SaleType        string    `json:"sale_type"`
	Subtotal        float64   `json:"subtotal"`
	LaborCharges    float64   `json:"labor_charges"`
	DeliveryCharges float64   `json:"delivery_charges"`
	TaxAmount       float64   `json:"tax_amount"`
	TotalAmount     float64   `json:"total_amount"`
	Items           []PSIItem `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"`
}

type LoginRequest struct {
	OperatorID string `json:"operator_id"`
	Password   string `json:"password"`
}

type InitYTDRequest struct {
	PartsSales float64 `json:"parts_sales"`
	PartsCost  float64 `json:"parts_cost"`
	Labor      float64 `json:"labor"`
	Delivery   float64 `json:"delivery"`
	Tax        float64 `json:"tax"`
}

// --- E-COMMERCE ADDITIONS ---

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"`
}

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

type visitor struct {
	lastSeen time.Time
	tokens   int
}

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 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:")

	if isCategorySearch {
		cleanQuery = strings.TrimPrefix(cleanQuery, "CAT:")
	} else {
		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 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, '')
			FROM parts 
			WHERE UPPER(special_id) = ? 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, '')
			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(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 {
		log.Printf(">> WEB INV DB ERROR: %v", err)
		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 string
		
		if err := rows.Scan(&p.PartNumber, &p.Description, &price, &qoh, &store2Qoh, &onOrder, &rot, &husq, &rep, &repBy, &oth, &specs, &img, &lookup); 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
		
		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)
}

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"`
}

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 or access denied"}`, 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,
	})
}

// --- PHASE 4.5 CHECKOUT & DASHBOARD INTEGRATION ---

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 WebOrderSummary struct {
	InvoiceNumber string  `json:"invoice_number"`
	SaleDate      string  `json:"sale_date"`
	CustomerName  string  `json:"customer_name"`
	FinalTotal    float64 `json:"final_total"`
	Status        string  `json:"status"`
}

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 parameters"}`, 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 {
		log.Printf(">> WEB CHECKOUT ERROR: %v", err)
		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,
		"message":  "Order successfully placed.",
	})
}

func handleWebOrdersDashboard(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")
	
	query := `
		SELECT 
			h.invoice_number, h.sale_date, 
			COALESCE(
				(SELECT REPLACE(description, 'WEB ORDER BY: ', '') FROM sales_items WHERE sale_id = h.sale_id AND part_number = 'SYS:NOTE' AND description LIKE 'WEB ORDER BY:%' LIMIT 1),
				c.name, 
				'WEB GUEST'
			),
			IFNULL(h.final_total, 0.0), h.status
		FROM sales_history h
		LEFT JOIN customers c ON TRIM(UPPER(h.customer_id)) = TRIM(UPPER(c.customer_id))
		WHERE h.sale_type = 'WEB' AND h.status = 'PENDING'
		ORDER BY h.sale_date DESC, h.invoice_number DESC LIMIT 100`

	rows, err := db.Query(query)
	if err != nil {
		log.Printf(">> WEB ORDERS DASHBOARD ERROR: %v", err)
		w.Write([]byte(`[]`))
		return
	}
	defer rows.Close()

	var results []WebOrderSummary
	for rows.Next() {
		var o WebOrderSummary
		if err := rows.Scan(&o.InvoiceNumber, &o.SaleDate, &o.CustomerName, &o.FinalTotal, &o.Status); err == nil {
			results = append(results, o)
		}
	}

	if results == nil { results = []WebOrderSummary{} }
	json.NewEncoder(w).Encode(results)
}

type WebOrderCancelRequest struct {
	InvoiceNumber string `json:"invoice_number"`
}

func handleWebOrderCancel(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")
	if r.Method != http.MethodPost {
		http.Error(w, `{"error": "Method not allowed"}`, http.StatusMethodNotAllowed)
		return
	}

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

	if !strings.HasPrefix(strings.ToUpper(req.InvoiceNumber), "W-") {
		http.Error(w, `{"error": "Invalid order number"}`, http.StatusBadRequest)
		return
	}

	_, err := db.Exec("UPDATE sales_history SET status = 'CANCELLED' WHERE invoice_number = ? AND sale_type = 'WEB'", req.InvoiceNumber)
	if err != nil {
		http.Error(w, `{"error": "Database error"}`, http.StatusInternalServerError)
		return
	}

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

// --- END E-COMMERCE ADDITIONS ---

func handleCntrl(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")

	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
				}
			}
		}

		if configMap["company_name"] == "" { configMap["company_name"] = "CHALLENGER POS" }
		if configMap["screensaver_msg"] == "" { configMap["screensaver_msg"] = "CHALLENGER 2026" }
		if configMap["screensaver_timeout"] == "" { configMap["screensaver_timeout"] = "5" }

		json.NewEncoder(w).Encode(configMap)
		return
	}

	if r.Method == http.MethodPost {
		var req map[string]string
		if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
			http.Error(w, `{"error": "Invalid request"}`, http.StatusBadRequest)
			return
		}

		for key, val := range req {
			db.Exec("DELETE FROM system_config WHERE key = ?", key)
			db.Exec("INSERT INTO system_config (key, value) VALUES (?, ?)", key, val)
		}

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

func handlePart(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")

	if r.Method == http.MethodGet {
		partNo := strings.TrimSpace(strings.ToUpper(r.URL.Query().Get("part_number")))
		if partNo == "" {
			http.Error(w, `{"error": "Missing part number"}`, http.StatusBadRequest)
			return
		}

		rows, err := db.Query("SELECT * FROM parts WHERE TRIM(UPPER(part_number)) = TRIM(UPPER(?))", partNo)
		if err == nil {
			defer rows.Close()
			if rows.Next() {
				cols, _ := rows.Columns()
				values := make([]interface{}, len(cols))
				valuePtrs := make([]interface{}, len(cols))
				for i := range cols { valuePtrs[i] = &values[i] }
				rows.Scan(valuePtrs...)
				
				rowMap := make(map[string]interface{})
				for i, col := range cols {
					b, ok := values[i].([]byte)
					if ok { rowMap[strings.ToLower(col)] = string(b) } else { rowMap[strings.ToLower(col)] = values[i] }
				}

				resRow := make(map[string]interface{})
				for k, v := range rowMap {
					resRow[k] = v
				}
				
				resRow["found"] = true
				resRow["part_number"] = coalesceString(rowMap, "part_number", "partno")
				resRow["source"] = coalesceString(rowMap, "source")
				resRow["description"] = coalesceString(rowMap, "description", "desc")
				resRow["replaced_by"] = coalesceString(rowMap, "replaced_by")
				resRow["replaces"] = coalesceString(rowMap, "replaces")
				resRow["husqvarna_part_number"] = coalesceString(rowMap, "husqvarna_part_number", "husq")
				resRow["rotary_part_number"] = coalesceString(rowMap, "rotary_part_number", "rotary")
				resRow["other_part_number"] = coalesceString(rowMap, "other_part_number", "other")
				resRow["equipment"] = coalesceString(rowMap, "equipment")
				resRow["model_number"] = coalesceString(rowMap, "model_number")
				resRow["bin_location"] = coalesceString(rowMap, "bin_location", "bin")
				resRow["overflow_bin"] = coalesceString(rowMap, "overflow_bin", "ovfl")
				resRow["stock_type"] = coalesceString(rowMap, "stock_type")
				resRow["months_no_sale"] = coalesceFloat(rowMap, "months_no_sale", "mns")
				resRow["quantity_on_hand"] = coalesceFloat(rowMap, "quantity_on_hand", "on_hand")
				resRow["store2_qoh"] = coalesceFloat(rowMap, "store2_qoh")
				resRow["old_on_hand"] = coalesceFloat(rowMap, "old_on_hand", "old_qty")
				resRow["order_quantity"] = coalesceFloat(rowMap, "order_quantity")
				resRow["order_date"] = coalesceString(rowMap, "order_date")
				resRow["order_status"] = coalesceString(rowMap, "order_status")
				resRow["cost"] = coalesceFloat(rowMap, "cost")
				resRow["trade_price"] = coalesceFloat(rowMap, "trade_price", "price")
				resRow["ytd_sales"] = coalesceFloat(rowMap, "ytd_sales")
				resRow["last_ytd_sales"] = coalesceFloat(rowMap, "last_ytd_sales")
				resRow["trend_date"] = coalesceString(rowMap, "trend_date")
				resRow["pi_date"] = coalesceString(rowMap, "pi_date")
				resRow["date_added"] = coalesceString(rowMap, "date_added")
				resRow["remarks"] = coalesceString(rowMap, "remarks")
				resRow["specs"] = coalesceString(rowMap, "specs")
				resRow["image_url"] = coalesceString(rowMap, "image_url")
				resRow["lookup_url"] = coalesceString(rowMap, "lookup_url")
				resRow["special_id"] = coalesceString(rowMap, "special_id")
				resRow["customer_name"] = coalesceString(rowMap, "customer_name", "last_customer", "last_cust", "customer", "cust_name", "custname")
				resRow["last_sale_date"] = coalesceString(rowMap, "last_sale_date", "last_sale", "sale_date", "date_last_sold")
				resRow["last_invoice"] = coalesceString(rowMap, "last_invoice", "last_inv", "invoice", "last_invoice_num")
				
				resRow["web_store"] = coalesceString(rowMap, "web_store")
				if resRow["web_store"] == "" { resRow["web_store"] = "Y" }

				var liveOrderNum string
				var liveOnOrderQty float64
				var liveOrderDate string
				poErr := db.QueryRow(`
					SELECT i.po_number, CAST(i.qty_backordered AS REAL), COALESCE(h.date_created, '')
					FROM po_items i
					LEFT JOIN purchase_orders h ON i.po_number = h.po_number
					WHERE TRIM(UPPER(i.part_number)) = TRIM(UPPER(?)) AND i.qty_backordered > 0 
					ORDER BY i.id DESC LIMIT 1`, resRow["part_number"]).Scan(&liveOrderNum, &liveOnOrderQty, &liveOrderDate)

				if poErr == nil {
					resRow["on_order"] = liveOnOrderQty
					resRow["order_number"] = liveOrderNum
					if liveOrderDate != "" {
						resRow["order_date"] = liveOrderDate
					}
				} else {
					resRow["on_order"] = coalesceFloat(rowMap, "on_order")
					resRow["order_number"] = coalesceString(rowMap, "order_number")
				}
				
				json.NewEncoder(w).Encode(resRow)
				return
			}
		}
		json.NewEncoder(w).Encode(map[string]interface{}{"found": false})
		return
	}

	if r.Method == http.MethodPost {
		var req map[string]interface{}
		if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
			http.Error(w, `{"error": "Invalid request"}`, http.StatusBadRequest)
			return
		}

		partNo, ok := req["part_number"].(string)
		if !ok || strings.TrimSpace(partNo) == "" {
			http.Error(w, `{"error": "Part number required"}`, http.StatusBadRequest)
			return
		}
		partNo = strings.ToUpper(strings.TrimSpace(partNo))

		var exists int
		err := db.QueryRow("SELECT 1 FROM parts WHERE TRIM(UPPER(part_number)) = TRIM(UPPER(?))", partNo).Scan(&exists)

		currentTimeStr := time.Now().Format("01/02/2006")
		auditLogID := fmt.Sprintf("MNT-%d", time.Now().UnixNano())

		var activeQty float64 = 0.0
		var activePrice float64 = 0.0
		var changedFields []string

		for k, v := range req {
			if k == "part_number" { continue }
			kl := strings.ToLower(k)
			if strings.Contains(kl, "qty") || strings.Contains(kl, "on_hand") || strings.Contains(kl, "count") || strings.Contains(kl, "stock") {
				activeQty = convertToFloat(v)
			}
			if strings.Contains(kl, "cost") || strings.Contains(kl, "price") || strings.Contains(kl, "sell") {
				activePrice = convertToFloat(v)
			}
			changedFields = append(changedFields, fmt.Sprintf("%s:%v", strings.ToUpper(k), v))
		}

		auditSummary := "UPD: " + strings.Join(changedFields, " ")
		if len(auditSummary) > 28 { auditSummary = auditSummary[:28] }

		partCols, err2 := db.Query("PRAGMA table_info(parts)")
		validPartCols := make(map[string]bool)
		if err2 == nil {
			for partCols.Next() {
				var cid int
				var cname, ctype string
				var nn, pk int
				var dflt interface{}
				if err := partCols.Scan(&cid, &cname, &ctype, &nn, &dflt, &pk); err == nil {
					validPartCols[strings.ToLower(cname)] = true
				}
			}
			partCols.Close()
		}

		for k := range req {
			if k != "part_number" && !validPartCols[strings.ToLower(k)] {
				db.Exec(fmt.Sprintf("ALTER TABLE parts ADD COLUMN %q TEXT", k))
			}
		}

		if err == nil && exists == 1 {
			updateFields := []string{}
			values := []interface{}{}
			for k, v := range req {
				if k != "part_number" {
					updateFields = append(updateFields, fmt.Sprintf("%q=?", k)) 
					values = append(values, v)
				}
			}
			
			if len(updateFields) > 0 {
				values = append(values, partNo)
				query := "UPDATE parts SET " + strings.Join(updateFields, ",") + " WHERE TRIM(UPPER(part_number)) = TRIM(UPPER(?))"
				_, err := db.Exec(query, values...)
				if err != nil {
					log.Printf(">> SQL UPDATE ERROR (PARTS): %v\nQuery: %s", err, query)
					http.Error(w, `{"error": "Failed to update"}`, http.StatusInternalServerError)
					return
				}
			}
			
			db.Exec("INSERT INTO sales_history (sale_id, invoice_number, sale_date, customer_id, final_total) VALUES (?, ?, ?, ?, 0.0)", auditLogID, "MAINT", currentTimeStr, auditSummary)
			db.Exec("INSERT INTO sales_items (sale_id, part_number, description, quantity, sell_price, line_total) VALUES (?, ?, ?, ?, ?, 0.0)", auditLogID, partNo, "MAINTENANCE SYSTEM LOG ENTRY", activeQty, activePrice)

		} else {
			fields := []string{"part_number"}
			placeholders := []string{"?"}
			values := []interface{}{partNo}
			for k, v := range req {
				if k != "part_number" {
					fields = append(fields, fmt.Sprintf("%q", k))
					placeholders = append(placeholders, "?")
					values = append(values, v)
				}
			}
			query := "INSERT INTO parts (" + strings.Join(fields, ",") + ") VALUES (" + strings.Join(placeholders, ",") + ")"
			_, err := db.Exec(query, values...)
			if err != nil {
				log.Printf(">> SQL INSERT ERROR (PARTS): %v\nQuery: %s", err, query)
				http.Error(w, `{"error": "Failed to insert"}`, http.StatusInternalServerError)
				return
			}
			db.Exec("INSERT INTO sales_history (sale_id, invoice_number, sale_date, customer_id, final_total) VALUES (?, ?, ?, ?, 0.0)", auditLogID, "MAINT", currentTimeStr, "PART INITIAL CREATION")
			db.Exec("INSERT INTO sales_items (sale_id, part_number, description, quantity, sell_price, line_total) VALUES (?, ?, ?, ?, ?, 0.0)", auditLogID, partNo, "INITIAL ACQUISITION ENTRY", activeQty, activePrice)
		}
		json.NewEncoder(w).Encode(map[string]interface{}{"success": true})
	}

	if r.Method == http.MethodDelete {
		partNo := strings.TrimSpace(strings.ToUpper(r.URL.Query().Get("part_number")))
		if partNo == "" {
			http.Error(w, `{"error": "Missing part number"}`, http.StatusBadRequest)
			return
		}

		var exists int
		var qoh sql.NullFloat64
		err := db.QueryRow("SELECT 1, quantity_on_hand FROM parts WHERE TRIM(UPPER(part_number)) = TRIM(UPPER(?))", partNo).Scan(&exists, &qoh)

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

		if qoh.Valid && qoh.Float64 != 0 {
			http.Error(w, `{"error": "QTY on hand must be zero to delete"}`, http.StatusBadRequest)
			return
		}

		_, err = db.Exec("DELETE FROM parts WHERE TRIM(UPPER(part_number)) = TRIM(UPPER(?))", partNo)
		if err != nil {
			log.Printf(">> SQL DELETE ERROR (PARTS): %v", err)
			http.Error(w, `{"error": "Failed to delete from database"}`, http.StatusInternalServerError)
			return
		}

		currentTimeStr := time.Now().Format("01/02/2006")
		auditLogID := fmt.Sprintf("DEL-%d", time.Now().UnixNano())
		db.Exec("INSERT INTO sales_history (sale_id, invoice_number, sale_date, customer_id, final_total) VALUES (?, ?, ?, ?, 0.0)", 
			auditLogID, "MAINT", currentTimeStr, "PART PURGED: "+partNo)
		db.Exec("INSERT INTO sales_items (sale_id, part_number, description, quantity, sell_price, line_total) VALUES (?, ?, ?, ?, ?, 0.0)", 
			auditLogID, partNo, "PART DELETED FROM SYSTEM", 0, 0)

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

func handleCustomer(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")

	idCol := "customer_id"
	colRows, err := db.Query("PRAGMA table_info(customers)")
	if err == nil {
		for colRows.Next() {
			var cid int
			var cname, ctype string
			var nn, pk int
			var dflt interface{}
			colRows.Scan(&cid, &cname, &ctype, &nn, &dflt, &pk)
			if strings.ToLower(cname) == "id" { idCol = cname }
		}
		colRows.Close()
	}

	if r.Method == http.MethodGet {
		custID := strings.TrimSpace(strings.ToUpper(r.URL.Query().Get("customer_id")))
		if custID == "" {
			http.Error(w, `{"error": "Missing customer ID"}`, http.StatusBadRequest)
			return
		}

		rows, err := db.Query("SELECT * FROM customers WHERE TRIM(UPPER("+idCol+")) = TRIM(UPPER(?))", custID)
		if err == nil {
			defer rows.Close()
			if rows.Next() {
				cols, _ := rows.Columns()
				values := make([]interface{}, len(cols))
				valuePtrs := make([]interface{}, len(cols))
				for i := range cols { valuePtrs[i] = &values[i] }
				rows.Scan(valuePtrs...)
				
				rowMap := make(map[string]interface{})
				for i, col := range cols {
					b, ok := values[i].([]byte)
					if ok { rowMap[strings.ToLower(col)] = string(b) } else { rowMap[strings.ToLower(col)] = values[i] }
				}

				resRow := map[string]interface{}{
					"found":            true,
					"customer_id":      coalesceString(rowMap, "customer_id", "id", "cust_id"),
					"name":             coalesceString(rowMap, "name", "customer_name", "cust_name"),
					"contact_person":   coalesceString(rowMap, "contact_person", "contact"),
					"phone1":           coalesceString(rowMap, "phone1", "phone", "cust_phone"),
					"phone2":           coalesceString(rowMap, "phone2", "alt_phone"),
					"email":            coalesceString(rowMap, "email"),
					"address":          coalesceString(rowMap, "address", "address1"),
					"city":             coalesceString(rowMap, "city"),
					"state":            coalesceString(rowMap, "state"),
					"zip":              coalesceString(rowMap, "zip", "zip_code"),
					"customer_type":    coalesceString(rowMap, "customer_type", "type"),
					"is_vendor":        coalesceString(rowMap, "is_vendor"),
					"notes":            coalesceString(rowMap, "notes", "remarks"),
					"last_invoice":     coalesceString(rowMap, "last_invoice"),
					"account_balance":  coalesceFloat(rowMap, "account_balance", "balance"),
					"ytd_sales":        coalesceFloat(rowMap, "ytd_sales", "ytd"),
					"last_year_sales":  coalesceFloat(rowMap, "last_year_sales", "last_year"),
					"date_added":       coalesceString(rowMap, "date_added", "added"),
					"last_sale_date":   coalesceString(rowMap, "last_sale_date"),
					"credit_limit":     coalesceFloat(rowMap, "credit_limit", "limit"),
				}
				json.NewEncoder(w).Encode(resRow)
				return
			}
		}
		json.NewEncoder(w).Encode(map[string]interface{}{"found": false})
		return
	}

	if r.Method == http.MethodPost {
		var req map[string]interface{}
		if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
			http.Error(w, `{"error": "Invalid request"}`, http.StatusBadRequest)
			return
		}

		custID, _ := req["customer_id"].(string)
		custID = strings.ToUpper(strings.TrimSpace(custID))

		currentTimeStr := time.Now().Format("01/02/2006")
		auditLogID := fmt.Sprintf("CST-%d", time.Now().UnixNano())

		if custID == "" {
			var nextNum int
			db.QueryRow("SELECT IFNULL(MAX(CAST("+idCol+" AS INTEGER)), 1000) + 1 FROM customers").Scan(&nextNum)
			custID = fmt.Sprintf("%d", nextNum)
			req["customer_id"] = custID
			req["date_added"] = currentTimeStr
		}

		var exists int
		db.QueryRow("SELECT 1 FROM customers WHERE TRIM(UPPER("+idCol+")) = TRIM(UPPER(?))", custID).Scan(&exists)

		var logDetails []string
		for k, v := range req {
			if strings.ToLower(k) == "customer_id" || strings.ToLower(k) == "id" || strings.ToLower(k) == "name" { continue }
			logDetails = append(logDetails, fmt.Sprintf("%s:%v", strings.ToUpper(k), v))
		}
		custSummary := "UPD: " + strings.Join(logDetails, " ")
		if len(custSummary) > 28 { custSummary = custSummary[:28] }

		colRows, _ := db.Query("PRAGMA table_info(customers)")
		validCols := make(map[string]bool)
		for colRows.Next() {
			var cid int
			var cname, ctype string
			var nn, pk int
			var dflt interface{}
			if err := colRows.Scan(&cid, &cname, &ctype, &nn, &dflt, &pk); err == nil {
				validCols[strings.ToLower(cname)] = true
			}
		}
		colRows.Close()

		if exists == 1 {
			updateFields := []string{}
			values := []interface{}{}
			for k, v := range req {
				if strings.ToLower(k) != strings.ToLower(idCol) && validCols[strings.ToLower(k)] {
					updateFields = append(updateFields, fmt.Sprintf("%q=?", k))
					values = append(values, v)
				}
			}
			values = append(values, custID)
			
			if len(updateFields) > 0 {
				query := "UPDATE customers SET " + strings.Join(updateFields, ",") + " WHERE " + idCol + " = ?"
				_, err := db.Exec(query, values...)
				if err != nil {
					log.Printf(">> SQL UPDATE ERROR (CUSTOMERS): %v", err)
					http.Error(w, `{"error": "Failed to update"}`, http.StatusInternalServerError)
					return
				}
			}
			db.Exec("INSERT INTO sales_history (sale_id, invoice_number, sale_date, customer_id, final_total) VALUES (?, ?, ?, ?, 0.0)", 
				auditLogID, "MAINT", currentTimeStr, custSummary)
		} else {
			fields := []string{}
			placeholders := []string{}
			values := []interface{}{}
			for k, v := range req {
				dbKey := k
				if strings.ToLower(k) == "customer_id" { dbKey = idCol }
				if validCols[strings.ToLower(dbKey)] {
					fields = append(fields, fmt.Sprintf("%q", dbKey))
					placeholders = append(placeholders, "?")
					values = append(values, v)
				}
			}
			
			if len(fields) > 0 {
				query := "INSERT INTO customers (" + strings.Join(fields, ",") + ") VALUES (" + strings.Join(placeholders, ",") + ")"
				_, err := db.Exec(query, values...)
				if err != nil {
					log.Printf(">> SQL INSERT ERROR (CUSTOMERS): %v\nQuery: %s", err, query)
					http.Error(w, `{"error": "Failed to insert"}`, http.StatusInternalServerError)
					return
				}
			}
			db.Exec("INSERT INTO sales_history (sale_id, invoice_number, sale_date, customer_id, final_total) VALUES (?, ?, ?, ?, 0.0)", 
				auditLogID, "MAINT", currentTimeStr, "CUSTOMER PROFILE CREATED")
		}
		json.NewEncoder(w).Encode(map[string]interface{}{"success": true, "customer_id": custID})
		return
	}

	if r.Method == http.MethodDelete {
		custID := strings.TrimSpace(strings.ToUpper(r.URL.Query().Get("customer_id")))
		if custID == "" {
			http.Error(w, `{"error": "Missing customer ID"}`, http.StatusBadRequest)
			return
		}

		var exists int
		db.QueryRow("SELECT 1 FROM customers WHERE TRIM(UPPER("+idCol+")) = TRIM(UPPER(?))", custID).Scan(&exists)

		if exists != 1 {
			http.Error(w, `{"error": "Customer not found"}`, http.StatusNotFound)
			return
		}

		_, err := db.Exec("DELETE FROM customers WHERE TRIM(UPPER("+idCol+")) = TRIM(UPPER(?))", custID)
		if err != nil {
			log.Printf(">> SQL DELETE ERROR (CUSTOMERS): %v", err)
			http.Error(w, `{"error": "Failed to delete from database"}`, http.StatusInternalServerError)
			return
		}

		currentTimeStr := time.Now().Format("01/02/2006")
		auditLogID := fmt.Sprintf("DEL-%d", time.Now().UnixNano())
		db.Exec("INSERT INTO sales_history (sale_id, invoice_number, sale_date, customer_id, final_total) VALUES (?, ?, ?, ?, 0.0)", 
			auditLogID, "MAINT", currentTimeStr, "CUSTOMER PURGED: "+custID)

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

func handleCustomerSearchPSI(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")
	term := strings.ToUpper(strings.TrimSpace(r.URL.Query().Get("name")))

	colRows, err := db.Query("PRAGMA table_info(customers)")
	if err != nil {
		w.Write([]byte(`[]`))
		return
	}
	var existingCols []string
	for colRows.Next() {
		var cid int
		var name, ctype string
		var nn, pk int
		var dflt interface{}
		if err := colRows.Scan(&cid, &name, &ctype, &nn, &dflt, &pk); err == nil {
			existingCols = append(existingCols, strings.ToLower(name))
		}
	}
	colRows.Close()

	var query string
	var args []interface{}

	if term == "" {
		query = "SELECT * FROM customers ORDER BY name ASC"
	} else {
		var whereClauses []string
		for _, col := range existingCols {
			if col == "name" || col == "customer_name" || col == "phone1" || col == "phone2" || col == "phone" || col == "cust_phone" {
				whereClauses = append(whereClauses, col+" LIKE '%' || ? || '%'")
			}
		}

		if len(whereClauses) == 0 {
			w.Write([]byte(`[]`))
			return
		}

		query = "SELECT * FROM customers WHERE " + strings.Join(whereClauses, " OR ") + " LIMIT 50"
		
		for range whereClauses { args = append(args, term) }
	}

	rows, err := db.Query(query, args...)
	if err != nil {
		w.Write([]byte(`[]`))
		return
	}
	defer rows.Close()

	var results []map[string]interface{}
	columns, _ := rows.Columns()

	for rows.Next() {
		values := make([]interface{}, len(columns))
		valuePtrs := make([]interface{}, len(columns))
		for i := range columns { valuePtrs[i] = &values[i] }
		
		if err := rows.Scan(valuePtrs...); err == nil {
			rowMap := make(map[string]interface{})
			for i, col := range columns {
				b, ok := values[i].([]byte)
				if ok { rowMap[strings.ToLower(col)] = string(b) } else { rowMap[strings.ToLower(col)] = values[i] }
			}

			resRow := map[string]interface{}{
				"customer_id":      coalesceString(rowMap, "customer_id", "id", "cust_id"),
				"name":             coalesceString(rowMap, "name", "customer_name", "cust_name"),
				"contact_person":   coalesceString(rowMap, "contact_person", "contact"),
				"phone1":           coalesceString(rowMap, "phone1", "phone", "cust_phone"),
				"phone2":           coalesceString(rowMap, "phone2", "alt_phone"),
				"email":            coalesceString(rowMap, "email"),
				"address":          coalesceString(rowMap, "address", "address1"),
				"city":             coalesceString(rowMap, "city"),
				"state":            coalesceString(rowMap, "state"),
				"zip":              coalesceString(rowMap, "zip", "zip_code"),
				"customer_type":    coalesceString(rowMap, "customer_type", "type"),
				"is_vendor":        coalesceString(rowMap, "is_vendor"),
				"notes":            coalesceString(rowMap, "notes", "remarks"),
				"last_invoice":     coalesceString(rowMap, "last_invoice"),
				"account_balance":  coalesceFloat(rowMap, "account_balance", "balance"),
				"ytd_sales":        coalesceFloat(rowMap, "ytd_sales", "ytd"),
				"last_year_sales":  coalesceFloat(rowMap, "last_year_sales", "last_year"),
				"date_added":       coalesceString(rowMap, "date_added", "added"),
				"last_sale_date":   coalesceString(rowMap, "last_sale_date"),
				"credit_limit":     coalesceFloat(rowMap, "credit_limit", "limit"),
			}
			results = append(results, resRow)
		}
	}

	if results == nil { results = []map[string]interface{}{} }
	json.NewEncoder(w).Encode(results)
}

func handleStoredInvoicesSearch(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")
	queryTerm := strings.ToUpper(strings.TrimSpace(r.URL.Query().Get("query")))

	sqlQuery := `
		SELECT 
			h.sale_id, h.invoice_number, h.sale_date, h.customer_id, 
			COALESCE(c.name, 'CASH SALE'),
			COALESCE(h.make, ''), COALESCE(h.model, ''), COALESCE(h.work_desc, ''), COALESCE(h.sale_type, '1'),
			IFNULL(h.subtotal, 0.0), IFNULL(h.labor_charges, 0.0), IFNULL(h.delivery_charges, 0.0), 
			IFNULL(h.tax_amount, 0.0), IFNULL(h.final_total, 0.0)
		FROM sales_history h
		LEFT JOIN customers c ON TRIM(UPPER(h.customer_id)) = TRIM(UPPER(c.customer_id))
		WHERE h.status = 'STORED'
	`
	
	var args []interface{}
	if queryTerm != "" {
		sqlQuery += " AND (UPPER(c.name) LIKE ? OR UPPER(c.phone1) LIKE ? OR UPPER(h.customer_id) LIKE ?)"
		likeTerm := "%" + queryTerm + "%"
		args = append(args, likeTerm, likeTerm, likeTerm)
	}
	sqlQuery += " ORDER BY h.sale_date DESC, h.invoice_number DESC LIMIT 50"

	rows, err := db.Query(sqlQuery, args...)
	if err != nil {
		log.Printf(">> STORED INVOICE SEARCH ERROR: %v", err)
		w.Write([]byte(`[]`))
		return
	}
	defer rows.Close()

	var results []StoredInvoice
	for rows.Next() {
		var inv StoredInvoice
		var activeSaleID string
		
		err := rows.Scan(
			&activeSaleID, &inv.InvoiceID, &inv.CreatedAt, &inv.CustomerID, &inv.CustomerName, 
			&inv.Make, &inv.Model, &inv.WorkDesc, &inv.SaleType,
			&inv.Subtotal, &inv.LaborCharges, &inv.DeliveryCharges, 
			&inv.TaxAmount, &inv.TotalAmount,
		)
		if err == nil {
			inv.Items = []PSIItem{}
			
			itemRows, err := db.Query("SELECT COALESCE(part_number, ''), COALESCE(description, ''), IFNULL(quantity, 0.0), IFNULL(sell_price, 0.0), IFNULL(line_total, 0.0) FROM sales_items WHERE sale_id = ? AND part_number NOT IN ('SYS:LABOR', 'SYS:DELIVERY')", activeSaleID)
			if err == nil {
				for itemRows.Next() {
					var item PSIItem
					if err := itemRows.Scan(&item.PartNumber, &item.Description, &item.Qty, &item.UnitPrice, &item.LineTotal); err == nil {
						inv.Items = append(inv.Items, item)
					} else {
						log.Printf(">> WARNING: SILENT ITEM DROP CAUGHT - %v", err)
					}
				}
				itemRows.Close()
			}
			results = append(results, inv)
		}
	}

	if results == nil { results = []StoredInvoice{} }
	json.NewEncoder(w).Encode(results)
}

func handlePostInvoicePSI(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")
	if r.Method != http.MethodPost {
		http.Error(w, `{"error": "Method not allowed"}`, http.StatusMethodNotAllowed)
		return
	}

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

	req.CustomerID = strings.ToUpper(strings.TrimSpace(req.CustomerID))
	req.CustomerName = strings.ToUpper(strings.TrimSpace(req.CustomerName))
	for i := range req.Items {
		req.Items[i].PartNumber = strings.ToUpper(strings.TrimSpace(req.Items[i].PartNumber))
	}

	isStored := req.Status == "STORED"
	currentTimeStr := time.Now().Format("01/02/2006")

	if req.InvoiceID != "" {
		db.Exec("DELETE FROM sales_items WHERE sale_id IN (SELECT sale_id FROM sales_history WHERE invoice_number = ?)", req.InvoiceID)
		db.Exec("DELETE FROM sales_history WHERE invoice_number = ?", req.InvoiceID)
	}

	var generatedInvoiceNo string
	var transactionSaleID string
	var activeInvoiceInt int

	if isStored {
		generatedInvoiceNo = req.InvoiceID
		if generatedInvoiceNo == "" {
			generatedInvoiceNo = fmt.Sprintf("S-%05d", time.Now().Unix()%100000)
		}
		transactionSaleID = fmt.Sprintf("STR-%d", time.Now().UnixNano())
	} else {
		var currentControlNo string
		db.QueryRow("SELECT value FROM system_config WHERE key = 'next_invoice_number'").Scan(&currentControlNo)
		currentControlNo = strings.TrimSpace(currentControlNo)

		activeInvoiceInt = 5001
		if currentControlNo != "" {
			fmt.Sscanf(strings.Replace(currentControlNo, "INV-", "", 1), "%d", &activeInvoiceInt)
		} else {
			db.QueryRow("SELECT IFNULL(MAX(CAST(REPLACE(invoice_number, 'INV-', '') AS INTEGER)), 5000) + 1 FROM sales_history WHERE invoice_number NOT LIKE 'S-%' AND invoice_number NOT LIKE 'W-%'").Scan(&activeInvoiceInt)
		}

		generatedInvoiceNo = fmt.Sprintf("%d", activeInvoiceInt)
		transactionSaleID = fmt.Sprintf("SAL-%d", time.Now().UnixNano())
	}

	statusVal := "COMPLETED"
	if isStored { statusVal = "STORED" }

	_, err := db.Exec(`INSERT INTO sales_history (sale_id, invoice_number, sale_date, customer_id, final_total, tax_amount, status, make, model, work_desc, sale_type, subtotal, labor_charges, delivery_charges) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		transactionSaleID, generatedInvoiceNo, currentTimeStr, req.CustomerID, req.TotalAmount, req.TaxAmount, statusVal, req.Make, req.Model, req.WorkDesc, req.SaleType, req.Subtotal, req.LaborCharges, req.DeliveryCharges)
	if err != nil {
		http.Error(w, "Failed to save invoice reference: "+err.Error(), http.StatusInternalServerError)
		return
	}

	partCols, _ := db.Query("PRAGMA table_info(parts)")
	validPartCols := make(map[string]bool)
	for partCols.Next() {
		var cid int
		var cname, ctype string
		var nn, pk int
		var dflt interface{}
		if err := partCols.Scan(&cid, &cname, &ctype, &nn, &dflt, &pk); err == nil {
			validPartCols[strings.ToLower(cname)] = true
		}
	}
	partCols.Close()

	if !validPartCols["customer_name"] { db.Exec("ALTER TABLE parts ADD COLUMN customer_name TEXT"); validPartCols["customer_name"] = true }
	if !validPartCols["last_sale_date"] { db.Exec("ALTER TABLE parts ADD COLUMN last_sale_date TEXT"); validPartCols["last_sale_date"] = true }
	if !validPartCols["last_invoice"] { db.Exec("ALTER TABLE parts ADD COLUMN last_invoice TEXT"); validPartCols["last_invoice"] = true }
	if !validPartCols["quantity_on_hand"] { db.Exec("ALTER TABLE parts ADD COLUMN quantity_on_hand REAL"); validPartCols["quantity_on_hand"] = true }
	if !validPartCols["old_on_hand"] { db.Exec("ALTER TABLE parts ADD COLUMN old_on_hand REAL"); validPartCols["old_on_hand"] = true }
	if !validPartCols["ytd_sales"] { db.Exec("ALTER TABLE parts ADD COLUMN ytd_sales REAL DEFAULT 0.0"); validPartCols["ytd_sales"] = true }

	for _, item := range req.Items {
		_, err := 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, item.LineTotal)
		
		if err != nil {
			log.Printf(">> CRITICAL ERROR: FAILED TO SAVE PART [%s]: %v", item.PartNumber, err)
		}

		if !isStored {
			var setClauses []string
			var updateArgs []interface{}

			if validPartCols["old_on_hand"] {
				setClauses = append(setClauses, "old_on_hand = IFNULL(quantity_on_hand, 0)")
			}

			if validPartCols["quantity_on_hand"] {
				setClauses = append(setClauses, "quantity_on_hand = IFNULL(quantity_on_hand, 0) - ?")
				updateArgs = append(updateArgs, item.Qty)
			} else if validPartCols["on_hand"] {
				setClauses = append(setClauses, "on_hand = IFNULL(on_hand, 0) - ?")
				updateArgs = append(updateArgs, item.Qty)
			}

			if validPartCols["ytd_sales"] {
				setClauses = append(setClauses, "ytd_sales = IFNULL(ytd_sales, 0) + ?")
				updateArgs = append(updateArgs, item.Qty)
			} else if validPartCols["ytd"] {
				setClauses = append(setClauses, "ytd = IFNULL(ytd, 0) + ?")
				updateArgs = append(updateArgs, item.Qty)
			}

			if validPartCols["last_sale_date"] {
				setClauses = append(setClauses, "last_sale_date = ?")
				updateArgs = append(updateArgs, currentTimeStr)
			} else if validPartCols["last_sale"] {
				setClauses = append(setClauses, "last_sale = ?")
				updateArgs = append(updateArgs, currentTimeStr)
			} else if validPartCols["sale_date"] {
				setClauses = append(setClauses, "sale_date = ?")
				updateArgs = append(updateArgs, currentTimeStr)
			}

			if validPartCols["last_invoice"] {
				setClauses = append(setClauses, "last_invoice = ?")
				updateArgs = append(updateArgs, generatedInvoiceNo)
			} else if validPartCols["last_inv"] {
				setClauses = append(setClauses, "last_inv = ?")
				updateArgs = append(updateArgs, generatedInvoiceNo)
			} else if validPartCols["invoice"] {
				setClauses = append(setClauses, "invoice = ?")
				updateArgs = append(updateArgs, generatedInvoiceNo)
			}

			if validPartCols["customer_name"] {
				setClauses = append(setClauses, "customer_name = ?")
				updateArgs = append(updateArgs, req.CustomerName)
			} else if validPartCols["last_customer"] {
				setClauses = append(setClauses, "last_customer = ?")
				updateArgs = append(updateArgs, req.CustomerName)
			} else if validPartCols["customer"] {
				setClauses = append(setClauses, "customer = ?")
				updateArgs = append(updateArgs, req.CustomerName)
			}

			if len(setClauses) > 0 {
				updateArgs = append(updateArgs, item.PartNumber)
				updateQuery := "UPDATE parts SET " + strings.Join(setClauses, ", ") + " WHERE TRIM(UPPER(part_number)) = TRIM(UPPER(?))"
				
				_, err := db.Exec(updateQuery, updateArgs...)
				if err != nil {
					log.Printf(">> CORE UPDATE EXCEPTION FOR PART [%s]: %v", item.PartNumber, err)
				}
			}
		}
	}

	if req.LaborCharges > 0 {
		db.Exec(`INSERT INTO sales_items (sale_id, part_number, description, quantity, sell_price, line_total) VALUES (?, 'SYS:LABOR', 'MECHANICAL LABOR SERVICE CHARGE', 1.0, ?, ?)`,
			transactionSaleID, req.LaborCharges, req.LaborCharges)
	}
	if req.DeliveryCharges > 0 {
		db.Exec(`INSERT INTO sales_items (sale_id, part_number, description, quantity, sell_price, line_total) VALUES (?, 'SYS:DELIVERY', 'PICKUP/DELIVERY TRANSPORTATION CHARGE', 1.0, ?, ?)`,
			transactionSaleID, req.DeliveryCharges, req.DeliveryCharges)
	}

	if !isStored {
		custCols, _ := db.Query("PRAGMA table_info(customers)")
		validCustCols := make(map[string]bool)
		idCol := "customer_id"
		for custCols.Next() {
			var cid int
			var cname, ctype string
			var nn, pk int
			var dflt interface{}
			if err := custCols.Scan(&cid, &cname, &ctype, &nn, &dflt, &pk); err == nil {
				validCustCols[strings.ToLower(cname)] = true
				if strings.ToLower(cname) == "id" { idCol = "id" }
				if strings.ToLower(cname) == "cust_id" { idCol = "cust_id" }
			}
		}
		custCols.Close()

		if !validCustCols["ytd_sales"] { db.Exec("ALTER TABLE customers ADD COLUMN ytd_sales REAL DEFAULT 0.0") }
		if !validCustCols["last_sale_date"] { db.Exec("ALTER TABLE customers ADD COLUMN last_sale_date TEXT") }
		if !validCustCols["last_invoice"] { db.Exec("ALTER TABLE customers ADD COLUMN last_invoice TEXT") }
		if !validCustCols["account_balance"] { db.Exec("ALTER TABLE customers ADD COLUMN account_balance REAL DEFAULT 0.0") }

		revenueTotal := req.TotalAmount

		_, errCust := db.Exec("UPDATE customers SET ytd_sales = IFNULL(ytd_sales, 0) + ?, last_sale_date = ?, last_invoice = ? WHERE TRIM(UPPER(" + idCol + ")) = TRIM(UPPER(?))", 
			revenueTotal, currentTimeStr, generatedInvoiceNo, req.CustomerID)
			
		if errCust != nil { log.Printf(">> CUSTOMER YTD UPDATE ERROR: %v", errCust) }

		if req.SaleType == "2" {
			db.Exec("UPDATE customers SET account_balance = IFNULL(account_balance, 0) + ? WHERE TRIM(UPPER(" + idCol + ")) = TRIM(UPPER(?))",
				req.TotalAmount, req.CustomerID)
		}

		nextInvoiceValStr := fmt.Sprintf("%d", activeInvoiceInt+1)
		db.Exec("DELETE FROM system_config WHERE key = 'next_invoice_number'")
		db.Exec("INSERT INTO system_config (key, value) VALUES ('next_invoice_number', ?)", nextInvoiceValStr)
	}

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

func handleLogin(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")
	if r.Method != http.MethodPost { return }
	var req LoginRequest
	json.NewDecoder(r.Body).Decode(&req)
	if strings.ToUpper(strings.TrimSpace(req.OperatorID)) == "DAVE" && strings.TrimSpace(req.Password) == "122459" {
		json.NewEncoder(w).Encode(map[string]interface{}{"success": true})
	} else {
		time.Sleep(500 * time.Millisecond)
		json.NewEncoder(w).Encode(map[string]interface{}{"success": false, "message": "INVALID OPERATOR ID OR PASSWORD."})
	}
}

func handlePartHistory(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")
	partNum := strings.ToUpper(strings.TrimSpace(r.URL.Query().Get("part_number")))
	if partNum == "" { partNum = strings.ToUpper(strings.TrimSpace(r.URL.Query().Get("part"))) }

	if partNum == "" {
		w.Write([]byte(`[]`))
		return
	}

	var results []map[string]interface{}
	query := `
		SELECT 
			COALESCE(NULLIF(TRIM(h.invoice_number), ''), NULLIF(TRIM(i.sale_id), ''), 'MAINT') AS doc_no, 
			COALESCE(NULLIF(TRIM(h.sale_date), ''), 'UNKNOWN') AS s_date, 
			IFNULL(i.quantity, 0.0) AS qty, 
			IFNULL(i.sell_price, 0.0) AS price, 
			IFNULL(i.line_total, 0.0) AS ext, 
			COALESCE(NULLIF(TRIM(c.name), ''), NULLIF(TRIM(h.customer_id), ''), 'SYSTEM UPDATE') AS cust_name
		FROM sales_items i
		LEFT JOIN sales_history h ON i.sale_id = h.sale_id
		LEFT JOIN customers c ON TRIM(UPPER(h.customer_id)) = TRIM(UPPER(c.customer_id))
		WHERE (TRIM(UPPER(i.part_number)) = ? OR TRIM(UPPER(i.part_number)) LIKE '%' || ? || '%') 
		AND IFNULL(h.invoice_number, '') NOT LIKE 'S-%' AND IFNULL(h.invoice_number, '') != 'YTD-INIT'
		ORDER BY h.sale_date DESC LIMIT 100`

	rows, err := db.Query(query, partNum, partNum)
	if err != nil {
		w.Write([]byte(`[]`))
		return
	}
	defer rows.Close()

	for rows.Next() {
		var docNo, sDate, custName sql.NullString
		var qty, price, ext sql.NullFloat64

		if err := rows.Scan(&docNo, &sDate, &qty, &price, &ext, &custName); err == nil {
			cleanDoc := strings.TrimSpace(docNo.String)
			rowType := "IN"
			if cleanDoc == "MAINT" { rowType = "MT" }
			if strings.HasPrefix(cleanDoc, "RCV-") { rowType = "RV" }

			results = append(results, map[string]interface{}{
				"doc_number":     cleanDoc,
				"type":           rowType,
				"date":           strings.TrimSpace(sDate.String),
				"qty":            qty.Float64,
				"price":          fmt.Sprintf("%.2f", price.Float64),
				"extended":       ext.Float64,
				"customer_name": " " + strings.TrimSpace(custName.String),
			})
		}
	}
	if results == nil { results = []map[string]interface{}{} }
	json.NewEncoder(w).Encode(results)
}

func handleCustomerHistory(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")
	custID := strings.ToUpper(strings.TrimSpace(r.URL.Query().Get("customer_id")))
	if custID == "" { custID = strings.ToUpper(strings.TrimSpace(r.URL.Query().Get("customer"))) }

	if custID == "" {
		w.Write([]byte(`[]`))
		return
	}

	var results []map[string]interface{}
	
	query := `
		SELECT 
			COALESCE(NULLIF(TRIM(h.invoice_number), ''), 'MAINT') AS inv_no, 
			COALESCE(NULLIF(TRIM(h.sale_date), ''), 'UNKNOWN') AS s_date, 
			IFNULL(h.final_total, 0.0) AS total 
		FROM sales_history h
		LEFT JOIN customers c ON TRIM(UPPER(h.customer_id)) = TRIM(UPPER(c.customer_id))
		WHERE (TRIM(UPPER(h.customer_id)) = TRIM(UPPER(?)) OR TRIM(UPPER(c.name)) = TRIM(UPPER(?)))
		AND h.invoice_number NOT LIKE 'S-%' AND h.invoice_number != 'YTD-INIT'
		ORDER BY h.sale_date DESC LIMIT 100`

	rows, err := db.Query(query, custID, custID)
	if err == nil {
		defer rows.Close()
		for rows.Next() {
			var invNo, sDate sql.NullString
			var total sql.NullFloat64
			if err := rows.Scan(&invNo, &sDate, &total); err == nil {
				results = append(results, map[string]interface{}{
					"invoice_number": strings.TrimSpace(invNo.String),
					"sale_date":      strings.TrimSpace(sDate.String),
					"final_total":    total.Float64,
				})
			}
		}
	}
	if results == nil { results = []map[string]interface{}{} }
	json.NewEncoder(w).Encode(results)
}

func handleJournalSearch(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")

	invoice := strings.TrimSpace(r.URL.Query().Get("invoice"))
	date := strings.TrimSpace(r.URL.Query().Get("date"))
	customer := strings.TrimSpace(r.URL.Query().Get("customer"))

	query := `
		SELECT 
			COALESCE(h.invoice_number, h.sale_id, '') as invoice_number,
			COALESCE(h.sale_date, '') as date_created,
			COALESCE(c.name, 'CASH SALE') as customer_name,
			COALESCE(h.make, '') as make,
			COALESCE(h.model, '') as model,
			COALESCE(h.work_desc, '') as work_desc,
			COALESCE(h.final_total, 0.0) as final_total
		FROM sales_history h
		LEFT JOIN customers c ON TRIM(UPPER(h.customer_id)) = TRIM(UPPER(c.customer_id))
		WHERE h.invoice_number != 'MAINT' AND h.invoice_number NOT LIKE 'S-%' AND h.invoice_number NOT LIKE 'RCV-%' AND h.status != 'STORED' AND h.invoice_number != 'YTD-INIT'
	`
	var args []interface{}

	if invoice != "" {
		query += " AND (h.invoice_number = ? OR h.sale_id = ?)"
		args = append(args, invoice, invoice)
	}
	if date != "" {
		dateParts := strings.Split(date, "/")
		if len(dateParts) == 2 {
			query += " AND h.sale_date LIKE ?"
			args = append(args, dateParts[0]+"/%/"+dateParts[1])
		} else {
			query += " AND h.sale_date = ?"
			args = append(args, date)
		}
	}
	if customer != "" {
		query += " AND (TRIM(UPPER(c.name)) LIKE ? OR TRIM(UPPER(h.customer_id)) LIKE ?)"
		likeTerm := "%" + strings.ToUpper(customer) + "%"
		args = append(args, likeTerm, likeTerm)
	}

	query += " ORDER BY h.sale_date DESC, h.invoice_number DESC LIMIT 1000"

	rows, err := db.Query(query, args...)
	if err != nil {
		log.Printf(">> IJE QUERY EXCEPTION: %v", err)
		w.Write([]byte(`[]`))
		return
	}
	defer rows.Close()

	var results []JournalEntry
	for rows.Next() {
		var entry JournalEntry
		if err := rows.Scan(&entry.InvoiceNumber, &entry.DateCreated, &entry.CustomerName, &entry.Make, &entry.Model, &entry.WorkDesc, &entry.FinalTotal); err == nil {
			results = append(results, entry)
		}
	}

	if results == nil { results = []JournalEntry{} }
	json.NewEncoder(w).Encode(results)
}

func handleJournalDetails(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")
	invoice := strings.TrimSpace(r.URL.Query().Get("invoice"))

	if invoice == "" {
		w.Write([]byte(`[]`))
		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 = ? OR sale_id = ?`, invoice, invoice).Scan(&entry.InvoiceNumber, &entry.DateCreated, &entry.Make, &entry.Model, &entry.WorkDesc, &entry.FinalTotal, &entry.TaxAmount)

	if err != nil {
		log.Printf(">> IJE METADATA ACCESSIBILITY EXCEPTION: %v", err)
		w.Write([]byte(`[]`))
		return
	}

	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 = ? OR sale_id = (SELECT sale_id FROM sales_history WHERE invoice_number = ? LIMIT 1)
	`
	rows, err := db.Query(query, invoice, invoice)
	if err != nil {
		log.Printf(">> IJE DETAIL SCAN EXCEPTION: %v", err)
		w.Write([]byte(`[]`))
		return
	}
	defer rows.Close()

	var items []JournalItem
	var partsSubtotal float64 = 0.0

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

	entry.Subtotal = partsSubtotal
	
	w.Write([]byte(fmt.Sprintf(`[`)))
	
	for idx, item := range items {
		if idx > 0 { w.Write([]byte(",")) }
		json.NewEncoder(w).Encode(map[string]interface{}{
			"qty":             item.Qty,
			"part_number":     item.PartNumber,
			"description":     item.Description,
			"unit_price":      item.UnitPrice,
			"line_total":      item.LineTotal,
			"subtotal":        entry.Subtotal,
			"labor_amount":    entry.LaborAmount,
			"delivery_amount": entry.DeliveryAmount,
			"tax_amount":      entry.TaxAmount,
			"make":            entry.Make,
			"model":           entry.Model,
			"work_desc":       entry.WorkDesc,
		})
	}
	if len(items) == 0 {
		json.NewEncoder(w).Encode(map[string]interface{}{
			"qty":             0,
			"part_number":     "",
			"description":     "NO PARTS ON RECORD",
			"unit_price":      0,
			"line_total":      0,
			"subtotal":        0,
			"labor_amount":    entry.LaborAmount,
			"delivery_amount": entry.DeliveryAmount,
			"tax_amount":      entry.TaxAmount,
			"make":            entry.Make,
			"model":           entry.Model,
			"work_desc":       entry.WorkDesc,
		})
	}
	w.Write([]byte(`]`))
}

func handleScanDB(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")
	schema := make(map[string][]string)
	rows, err := db.Query("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'")
	if err != nil { return }
	defer rows.Close()

	var tables []string
	for rows.Next() {
		var tableName string
		if err := rows.Scan(&tableName); err == nil { tables = append(tables, tableName) }
	}

	for _, table := range tables {
		colRows, err := db.Query("PRAGMA table_info(\"" + table + "\")")
		if err != nil { continue }
		var columns []string
		for colRows.Next() {
			var cid int
			var name, ctype string
			var notnull, pk int
			var dfltValue interface{}
			if err := colRows.Scan(&cid, &name, &ctype, &notnull, &dfltValue, &pk); err == nil {
				columns = append(columns, name)
			}
		}
		colRows.Close()
		schema[table] = columns
	}
	json.NewEncoder(w).Encode(schema)
}

func handleXBUBackup(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "text/event-stream")
	w.Header().Set("Cache-Control", "no-cache")
	w.Header().Set("Connection", "keep-alive")

	flusher, ok := w.(http.Flusher)
	if !ok { http.Error(w, "Streaming unsupported", http.StatusInternalServerError); return }

	targetDrive := strings.TrimSpace(r.URL.Query().Get("path"))
	if targetDrive == "" {
		fmt.Fprintf(w, "data: ERROR: TARGET DRIVE PATH NOT SPECIFIED\n\n")
		flusher.Flush()
		return
	}

	fmt.Fprintf(w, "data: >>> CHECKPOINTING MEMORY BUFFERS...\n\n")
	flusher.Flush()
	db.Exec("PRAGMA wal_checkpoint(TRUNCATE)") 

	srcDir := "C:\\D3"
	
	err := filepath.Walk(srcDir, func(path string, info os.FileInfo, err error) error {
		if err != nil { return err }
		if info.IsDir() { return nil }

		relPath, _ := filepath.Rel(srcDir, path)
		targetPath := filepath.Join(targetDrive, relPath)

		fmt.Fprintf(w, "data: COPYING: \\D3\\%s\n\n", relPath)
		flusher.Flush()

		if err := copyFileContents(path, targetPath); err != nil {
			fmt.Fprintf(w, "data: ERROR COPYING %s: %s\n\n", relPath, err.Error())
			flusher.Flush()
		}
		return nil
	})

	if err != nil {
		fmt.Fprintf(w, "data: ERROR: %s\n\n", err.Error())
	} else {
		fmt.Fprintf(w, "data: DONE\n\n")
	}
	flusher.Flush()
}

func handleXBURestore(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "text/event-stream")
	w.Header().Set("Cache-Control", "no-cache")
	w.Header().Set("Connection", "keep-alive")
	flusher, _ := w.(http.Flusher)

	srcDrive := strings.TrimSpace(r.URL.Query().Get("path"))
	if srcDrive == "" {
		fmt.Fprintf(w, "data: ERROR: SOURCE PATH NOT SPECIFIED\n\n")
		flusher.Flush()
		return
	}

	fmt.Fprintf(w, "data: >>> DISCONNECTING LIVE DATABASE POOL...\n\n")
	flusher.Flush()
	db.Close() 

	targetDir := "C:\\D3"

	err := filepath.Walk(srcDrive, func(path string, info os.FileInfo, err error) error {
		if err != nil { return err }
		if info.IsDir() { return nil }

		relPath, _ := filepath.Rel(srcDrive, path)
		targetPath := filepath.Join(targetDir, relPath)

		fmt.Fprintf(w, "data: RESTORING: \\D3\\%s\n\n", relPath)
		flusher.Flush()

		copyFileContents(path, targetPath)
		return nil
	})

	dbPath := "C:/D3/PRM/CHALLENGER.db?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)"
	db, _ = sql.Open("sqlite", dbPath)

	if err != nil {
		fmt.Fprintf(w, "data: ERROR: %s\n\n", err.Error())
	} else {
		fmt.Fprintf(w, "data: DONE\n\n")
	}
	flusher.Flush()
}

func handleBFAutoCalc(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")
	var sum float64
	err := db.QueryRow("SELECT IFNULL(SUM(ytd_sales), 0) FROM customers").Scan(&sum)
	if err != nil {
		sum = 0.0
	}
	json.NewEncoder(w).Encode(map[string]float64{"ytd_customer_sum": sum})
}

func handleInitYTD(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")
	
	currentYear := time.Now().Year()
	saleID := "YTD-" + fmt.Sprintf("%d", currentYear)
	yearStr := fmt.Sprintf("%%/%d", currentYear)

	var existPartsSales, existPartsCost, existLabor, existDelivery, existTax, existCashTotal float64

	db.QueryRow(`
		SELECT 
			IFNULL(SUM(h.labor_charges), 0),
			IFNULL(SUM(h.delivery_charges), 0),
			IFNULL(SUM(h.tax_amount), 0),
			IFNULL(SUM(h.final_total), 0)
		FROM sales_history h
		WHERE h.sale_date LIKE ? AND h.invoice_number != 'MAINT' AND h.invoice_number NOT LIKE 'S-%' AND h.invoice_number NOT LIKE 'RCV-%' AND h.status != 'STORED' AND h.invoice_number != 'YTD-INIT'
	`, yearStr).Scan(&existLabor, &existDelivery, &existTax, &existCashTotal)

	db.QueryRow(`
		SELECT 
			IFNULL(SUM(i.line_total), 0),
			IFNULL(SUM(i.quantity * IFNULL(p.cost, 0.0)), 0)
		FROM sales_items i
		JOIN sales_history h ON i.sale_id = h.sale_id
		LEFT JOIN parts p ON TRIM(UPPER(i.part_number)) = TRIM(UPPER(p.part_number))
		WHERE h.sale_date LIKE ? AND h.invoice_number != 'MAINT' AND h.invoice_number NOT LIKE 'S-%' AND h.invoice_number NOT LIKE 'RCV-%' AND h.status != 'STORED' AND h.invoice_number != 'YTD-INIT' AND i.part_number NOT IN ('SYS:LABOR', 'SYS:DELIVERY')
	`, yearStr).Scan(&existPartsSales, &existPartsCost)

	if r.Method == http.MethodGet {
		var req InitYTDRequest
		var adjPartsSales, adjLabor, adjDelivery, adjTax, adjPartsCost float64
		
		db.QueryRow("SELECT IFNULL(subtotal, 0), IFNULL(labor_charges, 0), IFNULL(delivery_charges, 0), IFNULL(tax_amount, 0) FROM sales_history WHERE sale_id = ?", saleID).Scan(&adjPartsSales, &adjLabor, &adjDelivery, &adjTax)
		db.QueryRow("SELECT IFNULL(cost, 0) FROM parts WHERE part_number = 'SYS:YTD-PARTS'").Scan(&adjPartsCost)
		
		req.PartsSales = adjPartsSales + existPartsSales
		req.PartsCost = adjPartsCost + existPartsCost
		req.Labor = adjLabor + existLabor
		req.Delivery = adjDelivery + existDelivery
		req.Tax = adjTax + existTax

		json.NewEncoder(w).Encode(req)
		return
	}

	if r.Method != http.MethodPost {
		http.Error(w, "METHOD NOT ALLOWED", http.StatusMethodNotAllowed)
		return
	}

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

	adjPartsSales := req.PartsSales - existPartsSales
	adjPartsCost := req.PartsCost - existPartsCost
	adjLabor := req.Labor - existLabor
	adjDelivery := req.Delivery - existDelivery
	adjTax := req.Tax - existTax
	
	targetTotalCash := req.PartsSales + req.Labor + req.Delivery + req.Tax
	adjTotal := targetTotalCash - existCashTotal

	initDate := fmt.Sprintf("01/01/%d", currentYear)
	invNo := "YTD-INIT"

	db.Exec("DELETE FROM sales_items WHERE sale_id = ?", saleID)
	db.Exec("DELETE FROM sales_history WHERE sale_id = ?", saleID)

	_, err := db.Exec(`INSERT INTO sales_history 
		(sale_id, invoice_number, sale_date, customer_id, final_total, tax_amount, status, subtotal, labor_charges, delivery_charges) 
		VALUES (?, ?, ?, 'CASH', ?, ?, 'COMPLETED', ?, ?, ?)`,
		saleID, invNo, initDate, adjTotal, adjTax, adjPartsSales, adjLabor, adjDelivery)
		
	if err != nil {
		http.Error(w, `{"error": "Failed to create initialization record"}`, http.StatusInternalServerError)
		return
	}

	if adjPartsSales != 0 || adjPartsCost != 0 {
		db.Exec(`INSERT INTO sales_items (sale_id, part_number, description, quantity, sell_price, line_total) VALUES (?, 'SYS:YTD-PARTS', 'YTD PARTS REVENUE', 1, ?, ?)`, saleID, adjPartsSales, adjPartsSales)
		db.Exec(`INSERT OR IGNORE INTO parts (part_number, description, cost, stock_type) VALUES ('SYS:YTD-PARTS', 'YTD PARTS REVENUE', ?, 'SYS')`, adjPartsCost)
		db.Exec(`UPDATE parts SET cost = ? WHERE part_number = 'SYS:YTD-PARTS'`, adjPartsCost)
	}
	if adjLabor != 0 {
		db.Exec(`INSERT INTO sales_items (sale_id, part_number, description, quantity, sell_price, line_total) VALUES (?, 'SYS:LABOR', 'YTD LABOR REVENUE', 1, ?, ?)`, saleID, adjLabor, adjLabor)
	}
	if adjDelivery != 0 {
		db.Exec(`INSERT INTO sales_items (sale_id, part_number, description, quantity, sell_price, line_total) VALUES (?, 'SYS:DELIVERY', 'YTD DELIVERY REVENUE', 1, ?, ?)`, saleID, adjDelivery, adjDelivery)
	}

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

func spawnBackgroundBackupScheduler() {
	go func() {
		for {
			time.Sleep(30 * time.Second) 
			
			var backupTime, backupDrive string
			db.QueryRow("SELECT value FROM system_config WHERE key = 'backup_time'").Scan(&backupTime)
			db.QueryRow("SELECT value FROM system_config WHERE key = 'backup_drive'").Scan(&backupDrive)

			if backupTime == "" || backupDrive == "" { continue }

			currentHourMin := time.Now().Format("15:04")
			if currentHourMin == backupTime {
				log.Printf(">> CRON CONTROLLER: Auto-Backup time match hit (%s). Running background sync...", backupTime)
				db.Exec("PRAGMA wal_checkpoint(TRUNCATE)")
				
				filepath.Walk("C:\\D3", func(path string, info os.FileInfo, err error) error {
					if err == nil && !info.IsDir() {
						rel, _ := filepath.Rel("C:\\D3", path)
						copyFileContents(path, filepath.Join(backupDrive, rel))
					}
					return nil
				})
				log.Printf(">> CRON CONTROLLER: Automated background sync complete.")
				time.Sleep(65 * time.Second) 
			}
		}
	}()
}

func startEOYScheduler() {
	go func() {
		for {
			now := time.Now()
			
			nextYear := now.Year() + 1
			nextJan1 := time.Date(nextYear, time.January, 1, 0, 0, 1, 0, now.Location())
			
			duration := time.Until(nextJan1)
			log.Printf(">> SYSTEM: EOY Scheduler is active. Sleeping for %v until %v", duration, nextJan1.Format("2006-01-02 15:04:05"))
			
			time.Sleep(duration)
			
			log.Println(">> EXECUTING END OF YEAR ROLLOVER...")
			
			_, err1 := db.Exec("UPDATE parts SET last_year_sales = COALESCE(ytd_sales, 0), ytd_sales = 0")
			if err1 != nil {
				log.Printf(">> EOY ERROR (PARTS): %v", err1)
			} else {
				log.Println(">> EOY: Parts sales successfully rolled over.")
			}
			
			_, err2 := db.Exec("UPDATE customers SET last_year_sales = COALESCE(ytd_sales, 0), ytd_sales = 0")
			if err2 != nil {
				log.Printf(">> EOY ERROR (CUSTOMERS): %v", err2)
			} else {
				log.Println(">> EOY: Customer sales successfully rolled over.")
			}
			
			log.Println(">> EOY ROLLOVER COMPLETE.")
			
			time.Sleep(24 * time.Hour)
		}
	}()
}

func handlePartDescReport(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")
	query := strings.ToUpper(strings.TrimSpace(r.URL.Query().Get("q")))

	if query == "" {
		w.Write([]byte(`[]`))
		return
	}

	searchContains := "%" + query + "%"
	
	sqlQuery := `
		SELECT 
			COALESCE(part_number, ''), 
			COALESCE(description, ''), 
			IFNULL(quantity_on_hand, 0), 
			COALESCE(bin_location, ''), 
			COALESCE(special_id, ''), 
			COALESCE(web_store, 'N')
		FROM parts 
		WHERE UPPER(description) LIKE ? OR UPPER(part_number) LIKE ?
		ORDER BY description ASC LIMIT 500
	`

	rows, err := db.Query(sqlQuery, searchContains, searchContains)
	if err != nil {
		log.Printf(">> PART DESC REPORT ERROR: %v", err)
		w.Write([]byte(`[]`))
		return
	}
	defer rows.Close()

	var results []map[string]interface{}
	for rows.Next() {
		var pn, desc, bin, spec, web string
		var qoh float64
		
		if err := rows.Scan(&pn, &desc, &qoh, &bin, &spec, &web); err == nil {
			results = append(results, map[string]interface{}{
				"part_number": pn,
				"description": desc,
				"quantity_on_hand": qoh,
				"bin_location": bin,
				"special_id": spec,
				"web_store": web,
			})
		}
	}

	if results == nil { results = []map[string]interface{}{} }
	json.NewEncoder(w).Encode(results)
}

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)
	}

	db.Exec("CREATE TABLE IF NOT EXISTS parts (part_number TEXT PRIMARY KEY)")
	db.Exec("CREATE TABLE IF NOT EXISTS customers (customer_id TEXT PRIMARY KEY)")
	db.Exec("CREATE TABLE IF NOT EXISTS sales_history (sale_id TEXT PRIMARY KEY)")
	db.Exec("CREATE TABLE IF NOT EXISTS sales_items (item_id INTEGER PRIMARY KEY AUTOINCREMENT)")
	db.Exec("CREATE TABLE IF NOT EXISTS system_config (key TEXT PRIMARY KEY, value TEXT)")
	db.Exec("CREATE TABLE IF NOT EXISTS purchase_orders (po_number TEXT PRIMARY KEY, vendor TEXT, date_created TEXT, status TEXT DEFAULT 'PENDING')")
	db.Exec("CREATE TABLE IF NOT EXISTS po_items (id INTEGER PRIMARY KEY AUTOINCREMENT, po_number TEXT, part_number TEXT, description TEXT, qty_ordered REAL, qty_received REAL, qty_backordered REAL, unit_cost REAL)")

	pCols, _ := db.Query("PRAGMA table_info(parts)")
	validPCols := make(map[string]bool)
	for pCols.Next() {
		var cid int
		var cname, ctype string
		var nn, pk int
		var dflt interface{}
		pCols.Scan(&cid, &cname, &ctype, &nn, &dflt, &pk)
		validPCols[strings.ToLower(cname)] = true
	}
	pCols.Close()

	if !validPCols["description"] { db.Exec("ALTER TABLE parts ADD COLUMN description TEXT") }
	if !validPCols["trade_price"] { db.Exec("ALTER TABLE parts ADD COLUMN trade_price REAL") }
	if !validPCols["quantity_on_hand"] { db.Exec("ALTER TABLE parts ADD COLUMN quantity_on_hand REAL") }
	if !validPCols["store2_qoh"] { db.Exec("ALTER TABLE parts ADD COLUMN store2_qoh REAL DEFAULT 0.0") } 
	if !validPCols["on_order"] { db.Exec("ALTER TABLE parts ADD COLUMN on_order REAL DEFAULT 0.0") }
	if !validPCols["rotary_part_number"] { db.Exec("ALTER TABLE parts ADD COLUMN rotary_part_number TEXT") }
	if !validPCols["husqvarna_part_number"] { db.Exec("ALTER TABLE parts ADD COLUMN husqvarna_part_number TEXT") }
	if !validPCols["replaces"] { db.Exec("ALTER TABLE parts ADD COLUMN replaces TEXT") }
	if !validPCols["replaced_by"] { db.Exec("ALTER TABLE parts ADD COLUMN replaced_by TEXT") }
	if !validPCols["other_part_number"] { db.Exec("ALTER TABLE parts ADD COLUMN other_part_number TEXT") }
	if !validPCols["specs"] { db.Exec("ALTER TABLE parts ADD COLUMN specs TEXT") }
	if !validPCols["image_url"] { db.Exec("ALTER TABLE parts ADD COLUMN image_url TEXT") }
	if !validPCols["lookup_url"] { db.Exec("ALTER TABLE parts ADD COLUMN lookup_url TEXT") }
	if !validPCols["web_store"] { db.Exec("ALTER TABLE parts ADD COLUMN web_store TEXT DEFAULT 'Y'") }
	if !validPCols["special_id"] { db.Exec("ALTER TABLE parts ADD COLUMN special_id TEXT") }

	siCols, _ := db.Query("PRAGMA table_info(sales_items)")
	validSiCols := make(map[string]bool)
	for siCols.Next() {
		var cid int
		var cname, ctype string
		var nn, pk int
		var dflt interface{}
		siCols.Scan(&cid, &cname, &ctype, &nn, &dflt, &pk)
		validSiCols[strings.ToLower(cname)] = true
	}
	siCols.Close()
	if !validSiCols["sale_id"] { db.Exec("ALTER TABLE sales_items ADD COLUMN sale_id TEXT") }
	if !validSiCols["part_number"] { db.Exec("ALTER TABLE sales_items ADD COLUMN part_number TEXT") }
	if !validSiCols["description"] { db.Exec("ALTER TABLE sales_items ADD COLUMN description TEXT") }
	if !validSiCols["quantity"] { db.Exec("ALTER TABLE sales_items ADD COLUMN quantity REAL DEFAULT 0.0") }
	if !validSiCols["sell_price"] { db.Exec("ALTER TABLE sales_items ADD COLUMN sell_price REAL DEFAULT 0.0") }
	if !validSiCols["line_total"] { db.Exec("ALTER TABLE sales_items ADD COLUMN line_total REAL DEFAULT 0.0") }

	db.Exec("CREATE TABLE IF NOT EXISTS pd_tickets (ticket_id TEXT PRIMARY KEY, customer_id TEXT, customer_name TEXT, phone TEXT, address TEXT, pickup_date TEXT, make TEXT, model TEXT, work_desc TEXT, status TEXT, created_at TEXT)")

	pdTktCols, _ := db.Query("PRAGMA table_info(pd_tickets)")
	validPdTktCols := make(map[string]bool)
	for pdTktCols.Next() {
		var cid int
		var cname, ctype string
		var nn, pk int
		var dflt interface{}
		pdTktCols.Scan(&cid, &cname, &ctype, &nn, &dflt, &pk)
		validPdTktCols[strings.ToLower(cname)] = true
	}
	pdTktCols.Close()

	if !validPdTktCols["return_date"] { db.Exec("ALTER TABLE pd_tickets ADD COLUMN return_date TEXT") }
	if !validPdTktCols["delivery_date"] { db.Exec("ALTER TABLE pd_tickets ADD COLUMN delivery_date TEXT") }

	poCols, _ := db.Query("PRAGMA table_info(po_items)")
	validPoCols := make(map[string]bool)
	for poCols.Next() {
		var cid int
		var cname, ctype string
		var nn, pk int
		var dflt interface{}
		poCols.Scan(&cid, &cname, &ctype, &nn, &dflt, &pk)
		validPoCols[strings.ToLower(cname)] = true
	}
	poCols.Close()

	if !validPoCols["qty_ordered"] { db.Exec("ALTER TABLE po_items ADD COLUMN qty_ordered REAL DEFAULT 0.0") }
	if !validPoCols["qty_received"] { db.Exec("ALTER TABLE po_items ADD COLUMN qty_received REAL DEFAULT 0.0") }
	if !validPoCols["qty_backordered"] { db.Exec("ALTER TABLE po_items ADD COLUMN qty_backordered REAL DEFAULT 0.0") }
	if !validPoCols["unit_cost"] { db.Exec("ALTER TABLE po_items ADD COLUMN unit_cost REAL DEFAULT 0.0") }
	if !validPoCols["customer_ro"] { db.Exec("ALTER TABLE po_items ADD COLUMN customer_ro TEXT") }

	shCols, _ := db.Query("PRAGMA table_info(sales_history)")
	validShCols := make(map[string]bool)
	for shCols.Next() {
		var cid int
		var cname, ctype string
		var nn, pk int
		var dflt interface{}
		shCols.Scan(&cid, &cname, &ctype, &nn, &dflt, &pk)
		validShCols[strings.ToLower(cname)] = true
	}
	shCols.Close()

	if !validShCols["tax_amount"] { db.Exec("ALTER TABLE sales_history ADD COLUMN tax_amount REAL DEFAULT 0.0") }
	if !validShCols["status"] { db.Exec("ALTER TABLE sales_history ADD COLUMN status TEXT DEFAULT 'COMPLETED'") }
	if !validShCols["make"] { db.Exec("ALTER TABLE sales_history ADD COLUMN make TEXT") }
	if !validShCols["model"] { db.Exec("ALTER TABLE sales_history ADD COLUMN model TEXT") }
	if !validShCols["work_desc"] { db.Exec("ALTER TABLE sales_history ADD COLUMN work_desc TEXT") }
	if !validShCols["sale_type"] { db.Exec("ALTER TABLE sales_history ADD COLUMN sale_type TEXT") }
	if !validShCols["subtotal"] { db.Exec("ALTER TABLE sales_history ADD COLUMN subtotal REAL DEFAULT 0.0") }
	if !validShCols["labor_charges"] { db.Exec("ALTER TABLE sales_history ADD COLUMN labor_charges REAL DEFAULT 0.0") }
	if !validShCols["delivery_charges"] { db.Exec("ALTER TABLE sales_history ADD COLUMN delivery_charges REAL DEFAULT 0.0") }

	custCols, _ := db.Query("PRAGMA table_info(customers)")
	validCustCols := make(map[string]bool)
	for custCols.Next() {
		var cid int
		var cname, ctype string
		var nn, pk int
		var dflt interface{}
		custCols.Scan(&cid, &cname, &ctype, &nn, &dflt, &pk)
		validCustCols[strings.ToLower(cname)] = true
	}
	custCols.Close()

	if !validCustCols["web_pin"] { 
		db.Exec("ALTER TABLE customers ADD COLUMN web_pin TEXT") 
		db.Exec("UPDATE customers SET web_pin = '1234' WHERE web_pin IS NULL")
	}

	uiRoutes := map[string]string{
		"/facet": "./facet.html", "/menu": "./menu.html", "/main": "./menu.html",
		"/name": "./name.html", "/customer": "./name.html",
		"/part": "./part.html", "/inf": "./inf.html", "/psi": "./psi.html",
		"/ije": "./ije.html", "/pso": "./pso.html", "/pop": "./pop.html",
		"/pi": "./pi.html", "/pad": "./pad.html", "/bin": "./bin.html",
		"/ipo": "./ipo.html", "/fin": "./fin.html", "/dfs": "./dfs.html",
		"/arbal": "./arbal.html", "/ich": "./ich.html", "/xbu": "./xbu.html",
		"/invrep": "./invrep.html", "/csr": "./csr.html", "/dcr": "./dcr.html",
		"/bf": "./bf.html", "/pd": "./pd.html", "/psa": "./psa.html", "/fmp": "./fmp.html",
		"/web": "./web.html", "/webupd": "./webupd.html",
		"/pdr": "./pdr.html",
	}

	for route, filePath := range uiRoutes {
		fp := filePath
		http.HandleFunc(route, func(w http.ResponseWriter, r *http.Request) { http.ServeFile(w, r, fp) })
		http.HandleFunc(strings.ToUpper(route), func(w http.ResponseWriter, r *http.Request) { http.ServeFile(w, r, fp) })
		http.HandleFunc(route+".html", func(w http.ResponseWriter, r *http.Request) { http.ServeFile(w, r, fp) })
		http.HandleFunc(strings.ToUpper(route)+".HTML", func(w http.ResponseWriter, r *http.Request) { http.ServeFile(w, r, fp) })
	}

	http.HandleFunc("/api/cntrl", handleCntrl)
	http.HandleFunc("/api/psi/customer_search", handleCustomerSearchPSI)
	http.HandleFunc("/api/invoice/psi", handlePostInvoicePSI)
	http.HandleFunc("/api/login", handleLogin) 
	http.HandleFunc("/api/part", handlePart) 
	http.HandleFunc("/api/customer", handleCustomer) 
	http.HandleFunc("/api/part/history", handlePartHistory)
	http.HandleFunc("/api/scandb", handleScanDB)
	http.HandleFunc("/api/customer/history", handleCustomerHistory) 
	
	http.HandleFunc("/api/journal/search", handleJournalSearch)
	http.HandleFunc("/api/journal/details", handleJournalDetails)
	http.HandleFunc("/api/invoice/stored", handleStoredInvoicesSearch)
	
	http.HandleFunc("/api/system/xbu_backup", handleXBUBackup)
	http.HandleFunc("/api/system/xbu_restore", handleXBURestore)
	
	http.HandleFunc("/api/bf/auto-calc", handleBFAutoCalc)
	http.HandleFunc("/api/bf/init-ytd", handleInitYTD)

	// E-COMMERCE API ROUTES
	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) 
	http.HandleFunc("/api/web/orders", handleWebOrdersDashboard)
	http.HandleFunc("/api/web/orders/cancel", handleWebOrderCancel) 
	
	// --- RESTORED LEGACY SYSTEM ROUTES ---
	http.HandleFunc("/api/reports/daily", handleDailyReport)
	http.HandleFunc("/api/reports/parts", handleDailyPartsReport)
	http.HandleFunc("/api/reports/summary", handleSummaryReport)
	http.HandleFunc("/api/reports/psa", handlePSAReport)
	http.HandleFunc("/api/reports/fmp", handleFMPReport)
	http.HandleFunc("/api/reports/pso", handlePSOReport)
	http.HandleFunc("/api/pi", handlePI)
	http.HandleFunc("/api/reports/pad", handlePADReport)
	http.HandleFunc("/api/reports/bin", handleBINReport)
	http.HandleFunc("/api/reports/uncounted", handleUncountedReport)
	http.HandleFunc("/api/reports/csr", handleCSRReport)
	http.HandleFunc("/api/reports/part_desc", handlePartDescReport)
	
	http.HandleFunc("/api/fin-totals", handleFinTotals)
	http.HandleFunc("/api/dfs-report", handleDFSReport)
	http.HandleFunc("/api/ar-balances", handleARBalances)
	http.HandleFunc("/api/financial-report", handleFinancialReport)
	
	http.HandleFunc("/api/order/submit", handlePOSubmit)
	http.HandleFunc("/api/orders/list", handlePOList) 
	http.HandleFunc("/api/orders", handlePODelete)
	http.HandleFunc("/api/orders/receive", handlePOFetch)
	http.HandleFunc("/api/inventory/receive", handlePOReceive)

	http.HandleFunc("/api/pd/submit", handlePDSave)
	http.HandleFunc("/api/pd/list", handlePDList)
	http.HandleFunc("/api/pd/update", handlePDUpdate)
	http.HandleFunc("/api/pd/delete", handlePDDelete)
	http.HandleFunc("/api/pd/alert", handlePDAlert)
	
	startEOYScheduler()
	spawnBackgroundBackupScheduler()

	// Tell the web server exactly where the manuals live
	http.Handle("/manuals/", http.StripPrefix("/manuals/", http.FileServer(http.Dir("C:/D3/PRG/MANUALS"))))

	fs := http.FileServer(http.Dir("."))
	http.Handle("/", noCacheHandler(fs))

	loggedMux := loggingMiddleware(http.DefaultServeMux)

	port := ":6060"
	log.Printf("SYSTEM ONLINE: Challenger POS is running on port %s\n", port)
	err = http.ListenAndServe(port, loggedMux)
	if err != nil {
		log.Fatalf("FATAL ERROR: Server crashed - %v\n", err)
	}
}