package main

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

type PSOItem struct {
	Vendor       string  `json:"vendor"`
	PartNumber   string  `json:"part_number"`
	Description  string  `json:"description"`
	QtyOnHand    float64 `json:"qty_on_hand"`
	SuggestedQty float64 `json:"suggested_qty"`
	UnitCost     float64 `json:"unit_cost"`
	OrderNumber  string  `json:"order_number"`
	OnOrderQty   float64 `json:"on_order_qty"`
}

type PIRequest struct {
	PartNumber     string `json:"part_number"`
	QuantityOnHand string `json:"quantity_on_hand"`
	BinLocation    string `json:"bin_location"`
}

type PADReportRow struct {
	PartNumber  string  `json:"part_number"`
	Description string  `json:"description"`
	BinLocation string  `json:"bin_location"`
	StockType   string  `json:"stock_type"`
	Qty         float64 `json:"qty"`
	Cost        float64 `json:"cost"`
	Extended    float64 `json:"extended"`
	PIDate      string  `json:"pi_date"`
}

type POSubmitRequest struct {
	OrderNumber string  `json:"order_number"`
	PartNumber  string  `json:"part_number"`
	Qty         float64 `json:"qty"`
	CustomerRO  string  `json:"customer_ro"`
}

type POItem struct {
	PartNumber     string  `json:"part_number"`
	Description    string  `json:"description"`
	QtyOrdered     float64 `json:"qty_ordered"`
	QtyReceived    float64 `json:"qty_received"`
	QtyBackordered float64 `json:"qty_backordered"`
	UnitCost       float64 `json:"unit_cost"`
	CustomerRO     string  `json:"customer_ro"`
}

type POReceiveRequest struct {
	PONumber    string  `json:"po_number"`
	PartNumber  string  `json:"part_number"`
	QtyReceived float64 `json:"qty_received"`
}

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

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

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

	partNo := strings.ToUpper(strings.TrimSpace(req.PartNumber))
	if partNo == "" {
		http.Error(w, `{"error": "Part number required"}`, http.StatusBadRequest)
		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["pi_date"] { db.Exec("ALTER TABLE parts ADD COLUMN pi_date TEXT") }
	if !validPartCols["quantity_on_hand"] { db.Exec("ALTER TABLE parts ADD COLUMN quantity_on_hand REAL") }
	if !validPartCols["bin_location"] { db.Exec("ALTER TABLE parts ADD COLUMN bin_location TEXT") }

	currentTimeStr := time.Now().Format("01/02/2006")
	qtyFloat := convertToFloat(req.QuantityOnHand)

	_, err := db.Exec(`UPDATE parts SET quantity_on_hand = ?, bin_location = ?, pi_date = ? WHERE TRIM(UPPER(part_number)) = TRIM(UPPER(?))`,
		qtyFloat, req.BinLocation, currentTimeStr, partNo)

	if err != nil {
		log.Printf(">> PI UPDATE ERROR: %v", err)
		http.Error(w, `{"error": "Failed to update inventory"}`, http.StatusInternalServerError)
		return
	}

	auditLogID := fmt.Sprintf("PI-%d", time.Now().UnixNano())
	auditSummary := fmt.Sprintf("PI UPD: QTY %.2f BIN %s", qtyFloat, req.BinLocation)
	if len(auditSummary) > 28 { auditSummary = auditSummary[:28] }

	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, "PHYSICAL INVENTORY COUNT", qtyFloat, 0.0)

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

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

	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["source"] { db.Exec("ALTER TABLE parts ADD COLUMN source TEXT") }
	if !validPartCols["description"] { db.Exec("ALTER TABLE parts ADD COLUMN description TEXT") }
	if !validPartCols["cost"] { db.Exec("ALTER TABLE parts ADD COLUMN cost REAL") }
	if !validPartCols["quantity_on_hand"] { db.Exec("ALTER TABLE parts ADD COLUMN quantity_on_hand REAL") }
	if !validPartCols["stock_type"] { db.Exec("ALTER TABLE parts ADD COLUMN stock_type TEXT DEFAULT 'ST'") }
	if !validPartCols["ytd_sales"] { db.Exec("ALTER TABLE parts ADD COLUMN ytd_sales REAL DEFAULT 0.0") }
	if !validPartCols["order_number"] { db.Exec("ALTER TABLE parts ADD COLUMN order_number TEXT") }
	if !validPartCols["on_order"] { db.Exec("ALTER TABLE parts ADD COLUMN on_order REAL DEFAULT 0.0") }

	var results []PSOItem

	query := `
		SELECT 
			COALESCE(p.source, 'UNKNOWN') as vendor,
			COALESCE(p.part_number, 'UNKNOWN') as part_number,
			COALESCE(p.description, '') as description,
			CAST(COALESCE(NULLIF(p.quantity_on_hand, ''), 0.0) AS REAL) as qty_on_hand,
			CAST(COALESCE(NULLIF(p.cost, ''), 0.0) AS REAL) as unit_cost,
			COALESCE(NULLIF(p.stock_type, ''), 'ST') as stock_type,
			CAST(COALESCE(NULLIF(p.ytd_sales, ''), 0.0) AS REAL) as ytd_sales,
			COALESCE((SELECT po_number FROM po_items WHERE part_number = p.part_number AND qty_backordered > 0 ORDER BY id DESC LIMIT 1), p.order_number, '') as order_number,
			CAST(COALESCE((SELECT SUM(qty_backordered) FROM po_items WHERE part_number = p.part_number AND qty_backordered > 0), p.on_order, 0.0) AS REAL) as on_order_qty
		FROM parts p
	`
	
	var rows *sql.Rows
	var err error

	if source != "" && source != "ALL" {
		query += " WHERE UPPER(COALESCE(p.source, '')) LIKE ? OR UPPER(COALESCE(p.part_number, '')) LIKE ?"
		term := source + "%"
		rows, err = db.Query(query, term, term)
	} else {
		rows, err = db.Query(query)
	}

	if err != nil {
		log.Printf(">> PSO QUERY ERROR: %v", err)
		w.Write([]byte(`[]`))
		return
	}
	defer rows.Close()

	for rows.Next() {
		var item PSOItem
		var stockType string
		var ytdSales float64

		if err := rows.Scan(&item.Vendor, &item.PartNumber, &item.Description, &item.QtyOnHand, &item.UnitCost, &stockType, &ytdSales, &item.OrderNumber, &item.OnOrderQty); err == nil {
			
			cleanType := strings.ToUpper(strings.TrimSpace(stockType))
			if cleanType != "ST" && cleanType != "" {
				continue
			}

			if incZero != "Y" && item.QtyOnHand > 0 {
				continue
			}
			
			var idealStock float64 = 0.0
			
			if ytdSales > 0 {
				idealStock = float64(int((ytdSales / 4.0) + 0.99)) 
				if idealStock < 2.0 { idealStock = 2.0 } 
			} else {
				idealStock = 1.0 
			}

			item.SuggestedQty = idealStock - item.QtyOnHand
			if item.SuggestedQty < 0 { 
				item.SuggestedQty = 0 
			}

			if item.SuggestedQty > 0 {
				results = append(results, item)
			}
		} else {
			log.Printf(">> PSO ROW SCAN ERROR: %v", err)
		}
	}

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

func handlePADReport(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")
	source := strings.TrimSpace(r.URL.Query().Get("source"))
	
	if source == "" {
		w.Write([]byte(`[]`))
		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["source"] { db.Exec("ALTER TABLE parts ADD COLUMN source TEXT") }
	if !validPartCols["description"] { db.Exec("ALTER TABLE parts ADD COLUMN description TEXT") }
	if !validPartCols["bin_location"] { db.Exec("ALTER TABLE parts ADD COLUMN bin_location TEXT") }
	if !validPartCols["cost"] { db.Exec("ALTER TABLE parts ADD COLUMN cost REAL") }
	if !validPartCols["quantity_on_hand"] { db.Exec("ALTER TABLE parts ADD COLUMN quantity_on_hand REAL") }
	if !validPartCols["stock_type"] { db.Exec("ALTER TABLE parts ADD COLUMN stock_type TEXT DEFAULT 'ST'") }
	if !validPartCols["pi_date"] { db.Exec("ALTER TABLE parts ADD COLUMN pi_date TEXT") }

	baseQuery := `
		SELECT 
			COALESCE(part_number, '') as part_number,
			COALESCE(description, '') as description,
			COALESCE(bin_location, '') as bin_location,
			COALESCE(NULLIF(stock_type, ''), 'ST') as stock_type,
			CAST(COALESCE(NULLIF(quantity_on_hand, ''), 0.0) AS REAL) as qty,
			CAST(COALESCE(NULLIF(cost, ''), 0.0) AS REAL) as cost,
			(CAST(COALESCE(NULLIF(quantity_on_hand, ''), 0.0) AS REAL) * CAST(COALESCE(NULLIF(cost, ''), 0.0) AS REAL)) as extended,
			COALESCE(NULLIF(TRIM(pi_date), ''), 'NEVER') as pi_date
		FROM parts
	`

	var rows *sql.Rows
	var err error

	if strings.ToUpper(source) == "ALL" {
		rows, err = db.Query(baseQuery + " ORDER BY part_number")
	} else {
		rows, err = db.Query(baseQuery + " WHERE TRIM(UPPER(source)) = TRIM(UPPER(?)) ORDER BY part_number", source)
	}

	if err != nil {
		log.Printf(">> PAD QUERY ERROR: %v", err)
		w.Write([]byte(`[]`))
		return
	}
	defer rows.Close()

	var report []PADReportRow
	for rows.Next() {
		var row PADReportRow
		if err := rows.Scan(&row.PartNumber, &row.Description, &row.BinLocation, &row.StockType, &row.Qty, &row.Cost, &row.Extended, &row.PIDate); err == nil {
			report = append(report, row)
		} else {
			log.Printf(">> PAD ROW SCAN ERROR: %v", err)
		}
	}

	if report == nil {
		report = []PADReportRow{}
	}
	json.NewEncoder(w).Encode(report)
}

func handleBINReport(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")
	binLoc := strings.TrimSpace(r.URL.Query().Get("bin"))
	sourceLoc := strings.TrimSpace(r.URL.Query().Get("source"))
	
	if binLoc == "" && sourceLoc == "" {
		w.Write([]byte(`[]`))
		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["source"] { db.Exec("ALTER TABLE parts ADD COLUMN source TEXT") }
	if !validPartCols["description"] { db.Exec("ALTER TABLE parts ADD COLUMN description TEXT") }
	if !validPartCols["bin_location"] { db.Exec("ALTER TABLE parts ADD COLUMN bin_location TEXT") }
	if !validPartCols["cost"] { db.Exec("ALTER TABLE parts ADD COLUMN cost REAL") }
	if !validPartCols["quantity_on_hand"] { db.Exec("ALTER TABLE parts ADD COLUMN quantity_on_hand REAL") }
	if !validPartCols["stock_type"] { db.Exec("ALTER TABLE parts ADD COLUMN stock_type TEXT DEFAULT 'ST'") }
	if !validPartCols["pi_date"] { db.Exec("ALTER TABLE parts ADD COLUMN pi_date TEXT") }

	baseQuery := `
		SELECT 
			COALESCE(part_number, '') as part_number,
			COALESCE(description, '') as description,
			COALESCE(bin_location, '') as bin_location,
			COALESCE(NULLIF(stock_type, ''), 'ST') as stock_type,
			CAST(COALESCE(NULLIF(quantity_on_hand, ''), 0.0) AS REAL) as qty,
			CAST(COALESCE(NULLIF(cost, ''), 0.0) AS REAL) as cost,
			(CAST(COALESCE(NULLIF(quantity_on_hand, ''), 0.0) AS REAL) * CAST(COALESCE(NULLIF(cost, ''), 0.0) AS REAL)) as extended,
			COALESCE(NULLIF(TRIM(pi_date), ''), 'NEVER') as pi_date
		FROM parts
	`

	var whereClauses []string
	var args []interface{}

	if strings.ToUpper(binLoc) != "ALL" && binLoc != "" {
		whereClauses = append(whereClauses, "TRIM(UPPER(bin_location)) LIKE UPPER(?)")
		args = append(args, binLoc+"%")
	}

	if strings.ToUpper(sourceLoc) != "ALL" && sourceLoc != "" {
		whereClauses = append(whereClauses, "TRIM(UPPER(source)) = UPPER(?)")
		args = append(args, sourceLoc)
	}

	query := baseQuery
	if len(whereClauses) > 0 {
		query += " WHERE " + strings.Join(whereClauses, " AND ")
	}
	query += " ORDER BY bin_location, part_number"

	rows, err := db.Query(query, args...)

	if err != nil {
		log.Printf(">> BIN QUERY ERROR: %v", err)
		w.Write([]byte(`[]`))
		return
	}
	defer rows.Close()

	var report []PADReportRow
	for rows.Next() {
		var row PADReportRow
		if err := rows.Scan(&row.PartNumber, &row.Description, &row.BinLocation, &row.StockType, &row.Qty, &row.Cost, &row.Extended, &row.PIDate); err == nil {
			report = append(report, row)
		} else {
			log.Printf(">> BIN ROW SCAN ERROR: %v", err)
		}
	}

	if report == nil {
		report = []PADReportRow{}
	}
	json.NewEncoder(w).Encode(report)
}

func handleUncountedReport(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")
	binLoc := strings.TrimSpace(r.URL.Query().Get("bin"))
	sourceLoc := strings.TrimSpace(r.URL.Query().Get("source"))
	piDate := strings.TrimSpace(r.URL.Query().Get("date")) 
	
	if piDate == "" {
		http.Error(w, `{"error": "Missing PI Date"}`, http.StatusBadRequest)
		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["source"] { db.Exec("ALTER TABLE parts ADD COLUMN source TEXT") }
	if !validPartCols["description"] { db.Exec("ALTER TABLE parts ADD COLUMN description TEXT") }
	if !validPartCols["bin_location"] { db.Exec("ALTER TABLE parts ADD COLUMN bin_location TEXT") }
	if !validPartCols["cost"] { db.Exec("ALTER TABLE parts ADD COLUMN cost REAL") }
	if !validPartCols["quantity_on_hand"] { db.Exec("ALTER TABLE parts ADD COLUMN quantity_on_hand REAL") }
	if !validPartCols["stock_type"] { db.Exec("ALTER TABLE parts ADD COLUMN stock_type TEXT DEFAULT 'ST'") }
	if !validPartCols["pi_date"] { db.Exec("ALTER TABLE parts ADD COLUMN pi_date TEXT") }

	baseQuery := `
		SELECT 
			COALESCE(part_number, '') as part_number,
			COALESCE(description, '') as description,
			COALESCE(bin_location, '') as bin_location,
			COALESCE(NULLIF(stock_type, ''), 'ST') as stock_type,
			CAST(COALESCE(NULLIF(quantity_on_hand, ''), 0.0) AS REAL) as qty,
			CAST(COALESCE(NULLIF(cost, ''), 0.0) AS REAL) as cost,
			(CAST(COALESCE(NULLIF(quantity_on_hand, ''), 0.0) AS REAL) * CAST(COALESCE(NULLIF(cost, ''), 0.0) AS REAL)) as extended,
			COALESCE(NULLIF(TRIM(pi_date), ''), 'NEVER') as pi_date
		FROM parts
		WHERE CAST(COALESCE(NULLIF(quantity_on_hand, ''), 0.0) AS REAL) > 0
		AND (
			pi_date IS NULL 
			OR TRIM(pi_date) = '' 
			OR TRIM(pi_date) = 'NEVER'
			OR substr(pi_date, 7, 4) || '-' || substr(pi_date, 1, 2) || '-' || substr(pi_date, 4, 2) < ?
		)
	`

	var args []interface{}
	args = append(args, piDate)

	if strings.ToUpper(binLoc) != "ALL" && binLoc != "" {
		baseQuery += " AND TRIM(UPPER(bin_location)) LIKE UPPER(?)"
		args = append(args, binLoc+"%")
	}

	if strings.ToUpper(sourceLoc) != "ALL" && sourceLoc != "" {
		baseQuery += " AND TRIM(UPPER(source)) = UPPER(?)"
		args = append(args, sourceLoc)
	}

	baseQuery += " ORDER BY bin_location, part_number"

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

	var report []PADReportRow
	for rows.Next() {
		var row PADReportRow
		if err := rows.Scan(&row.PartNumber, &row.Description, &row.BinLocation, &row.StockType, &row.Qty, &row.Cost, &row.Extended, &row.PIDate); err == nil {
			report = append(report, row)
		} else {
			log.Printf(">> UNCOUNTED ROW SCAN ERROR: %v", err)
		}
	}

	if report == nil {
		report = []PADReportRow{}
	}
	json.NewEncoder(w).Encode(report)
}

func handlePOSubmit(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 POSubmitRequest
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		http.Error(w, `{"error": "Invalid payload parameters"}`, http.StatusBadRequest)
		return
	}

	poNo := strings.ToUpper(strings.TrimSpace(req.OrderNumber))
	partNo := strings.ToUpper(strings.TrimSpace(req.PartNumber))
	customerRO := strings.ToUpper(strings.TrimSpace(req.CustomerRO))
	today := time.Now().Format("01/02/2006")

	var desc string
	var cost float64
	err := db.QueryRow("SELECT COALESCE(description, 'MANUAL ENTRY'), CAST(COALESCE(NULLIF(cost, ''), 0.0) AS REAL) FROM parts WHERE TRIM(UPPER(part_number)) = ?", partNo).Scan(&desc, &cost)
	if err != nil {
		desc = "MANUAL ENTRY"
		cost = 0.0
	}

	_, err = db.Exec("INSERT OR IGNORE INTO purchase_orders (po_number, vendor, date_created, status) VALUES (?, 'VARIOUS', ?, 'PENDING')", poNo, today)
	if err != nil {
		log.Printf(">> PO HEADER CREATION ERROR: %v", err)
	}

	_, err = db.Exec(`INSERT INTO po_items (po_number, part_number, description, qty_ordered, qty_received, qty_backordered, unit_cost, customer_ro) 
		VALUES (?, ?, ?, ?, 0, ?, ?, ?)`, poNo, partNo, desc, req.Qty, req.Qty, cost, customerRO)
	
	if err != nil {
		log.Printf(">> PO ITEM INSERT ERROR: %v", err)
		http.Error(w, `{"error": "Failed to save PO line"}`, 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["on_order"] { db.Exec("ALTER TABLE parts ADD COLUMN on_order REAL DEFAULT 0.0") }
	if !validPartCols["order_number"] { db.Exec("ALTER TABLE parts ADD COLUMN order_number TEXT") }
	if !validPartCols["order_date"] { db.Exec("ALTER TABLE parts ADD COLUMN order_date TEXT") }

	db.Exec("UPDATE parts SET on_order = COALESCE(on_order, 0) + ?, order_number = ?, order_date = ? WHERE TRIM(UPPER(part_number)) = ?", req.Qty, poNo, today, partNo)

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

func handlePOList(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")
	
	query := `SELECT po_number, date_created, status FROM purchase_orders ORDER BY po_number DESC LIMIT 100`
	rows, err := db.Query(query)
	if err != nil {
		w.Write([]byte(`[]`))
		return
	}
	defer rows.Close()

	var results []map[string]interface{}
	for rows.Next() {
		var po, date, status sql.NullString
		if err := rows.Scan(&po, &date, &status); err == nil {
			results = append(results, map[string]interface{}{
				"po_number":    strings.TrimSpace(po.String),
				"date_created": strings.TrimSpace(date.String),
				"status":       strings.TrimSpace(status.String),
			})
		}
	}

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

func handlePOFetch(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")
	poNo := strings.ToUpper(strings.TrimSpace(r.URL.Query().Get("po")))
	
	if poNo == "" {
		w.Write([]byte(`[]`))
		return
	}

	query := `SELECT part_number, description, qty_ordered, qty_received, qty_backordered, unit_cost, COALESCE(customer_ro, '') FROM po_items WHERE po_number = ?`
	rows, err := db.Query(query, poNo)
	
	if err != nil {
		log.Printf(">> PO FETCH ERROR: %v", err)
		w.Write([]byte(`[]`))
		return
	}
	defer rows.Close()

	var items []POItem
	for rows.Next() {
		var item POItem
		if err := rows.Scan(&item.PartNumber, &item.Description, &item.QtyOrdered, &item.QtyReceived, &item.QtyBackordered, &item.UnitCost, &item.CustomerRO); err == nil {
			items = append(items, item)
		}
	}

	if items == nil {
		items = []POItem{}
	}
	
	json.NewEncoder(w).Encode(items)
}

func handlePOReceive(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 POReceiveRequest
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		http.Error(w, `{"error": "Invalid payload parameters"}`, http.StatusBadRequest)
		return
	}

	poNo := strings.ToUpper(strings.TrimSpace(req.PONumber))
	partNo := strings.ToUpper(strings.TrimSpace(req.PartNumber))

	_, err := db.Exec(`UPDATE po_items SET qty_received = COALESCE(qty_received, 0) + ?, qty_backordered = qty_ordered - (COALESCE(qty_received, 0) + ?) WHERE po_number = ? AND part_number = ?`, req.QtyReceived, req.QtyReceived, poNo, partNo)
	if err != nil {
		log.Printf(">> PO RECEIPT UPDATE ERROR: %v", err)
		http.Error(w, `{"error": "Failed to update PO receipt"}`, http.StatusInternalServerError)
		return
	}

	_, err = db.Exec(`UPDATE parts SET quantity_on_hand = COALESCE(quantity_on_hand, 0) + ?, on_order = MAX(0, COALESCE(on_order, 0) - ?) WHERE TRIM(UPPER(part_number)) = ?`, req.QtyReceived, req.QtyReceived, partNo)
	if err != nil {
		log.Printf(">> INVENTORY RECEIPT UPDATE ERROR: %v", err)
	}

	db.Exec(`UPDATE purchase_orders SET status = 'RECEIVED' WHERE po_number = ? AND NOT EXISTS (SELECT 1 FROM po_items WHERE po_number = ? AND qty_backordered > 0)`, poNo, poNo)

	auditLogID := fmt.Sprintf("RCV-%d", time.Now().UnixNano())
	today := time.Now().Format("01/02/2006")
	
	db.Exec("INSERT INTO sales_history (sale_id, invoice_number, sale_date, customer_id, final_total) VALUES (?, ?, ?, 'VENDOR RCV', 0.0)", auditLogID, "RCV-"+poNo, today)
	db.Exec("INSERT INTO sales_items (sale_id, part_number, description, quantity, sell_price, line_total) VALUES (?, ?, 'INVENTORY RECEIPT', ?, 0.0, 0.0)", auditLogID, partNo, req.QtyReceived)

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

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

	poNo := strings.ToUpper(strings.TrimSpace(r.URL.Query().Get("po")))
	if poNo == "" {
		http.Error(w, `{"error": "Missing PO number"}`, http.StatusBadRequest)
		return
	}

	type rollbackItem struct {
		PartNo string
		Qty    float64
	}
	var itemsToRollback []rollbackItem

	rows, err := db.Query("SELECT part_number, qty_backordered FROM po_items WHERE po_number = ? AND qty_backordered > 0", poNo)
	if err == nil {
		for rows.Next() {
			var partNo string
			var qtyBackordered float64
			if err := rows.Scan(&partNo, &qtyBackordered); err == nil {
				itemsToRollback = append(itemsToRollback, rollbackItem{PartNo: partNo, Qty: qtyBackordered})
			}
		}
		rows.Close()
	}

	for _, item := range itemsToRollback {
		db.Exec("UPDATE parts SET on_order = MAX(0, COALESCE(on_order, 0) - ?) WHERE TRIM(UPPER(part_number)) = ?", item.Qty, item.PartNo)
	}

	db.Exec("UPDATE parts SET order_number = '', order_date = '' WHERE TRIM(UPPER(order_number)) = ?", poNo)

	_, err = db.Exec("DELETE FROM purchase_orders WHERE po_number = ?", poNo)
	if err != nil {
		log.Printf(">> PO DELETE ERROR: %v", err)
		http.Error(w, `{"error": "Failed to delete PO"}`, http.StatusInternalServerError)
		return
	}

	_, err = db.Exec("DELETE FROM po_items WHERE po_number = ?", poNo)
	if err != nil {
		log.Printf(">> PO ITEMS DELETE ERROR: %v", err)
	}

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

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