package main

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

type FinPeriodData struct {
	Sales      string `json:"sales"`
	Cost       string `json:"cost"`
	Margin     string `json:"margin"`
	Percentage string `json:"percentage"`
}

type AccountTotals struct {
	Monthly FinPeriodData `json:"monthly"`
	YTD     FinPeriodData `json:"ytd"`
}

type DFSMonth struct {
	PartsRevenue  float64 `json:"parts_revenue"`
	LaborDelivery float64 `json:"labor_delivery"`
	COGS          float64 `json:"cogs"`
	GrossProfit   float64 `json:"gross_profit"`
}

type DFSYTD struct {
	PartsRevenue      float64 `json:"parts_revenue"`
	LaborRevenue      float64 `json:"labor_revenue"`
	DeliveryRevenue   float64 `json:"delivery_revenue"`
	OperatingRevenue  float64 `json:"operating_revenue"`
	TaxCollected      float64 `json:"tax_collected"`
	ManualAdjustments float64 `json:"manual_adjustments"`
	CashReceipts      float64 `json:"cash_receipts"`
	COGS              float64 `json:"cogs"`
	GrossProfit       float64 `json:"gross_profit"`
}

type DFSAssets struct {
	InventoryValuation float64 `json:"inventory_valuation"`
	ARCustomer         float64 `json:"ar_customer"`
	ARWorkorders       float64 `json:"ar_workorders"`
	TotalAssets        float64 `json:"total_assets"`
}

type DFSReportResponse struct {
	YTD     DFSYTD       `json:"ytd"`
	Assets  DFSAssets    `json:"assets"`
	Monthly [12]DFSMonth `json:"monthly"`
}

type FinancialReport struct {
	InventoryTotal float64 `json:"inventory_total"`
	CashBalance    float64 `json:"cash_balance"`
	ARBalance      float64 `json:"ar_balance"`
	SalesMonth     float64 `json:"sales_month"`
	COGSMonth      float64 `json:"cogs_month"`
	TaxMonth       float64 `json:"tax_month"`
	SalesYTD       float64 `json:"sales_ytd"`
	COGSYTD        float64 `json:"cogs_ytd"`
	TaxYTD         float64 `json:"tax_ytd"`
}

type SummarySales struct {
	Gross         float64 `json:"gross"`
	Parts         float64 `json:"parts"`
	Labor         float64 `json:"labor"`
	Delivery      float64 `json:"delivery"`
	Tax           float64 `json:"tax"`
	TotalReceipts float64 `json:"total_receipts"`
	InvoiceCount  int     `json:"invoice_count"`
	Profit        float64 `json:"profit"`
}

type SummaryParts struct {
	QtySold      float64 `json:"qty_sold"`
	NewAdded     int     `json:"new_added"`
	Deleted      int     `json:"deleted"`
	PriceUpdates int     `json:"price_updates"`
	PICounts     int     `json:"pi_counts"`
}

type SummaryCustomers struct {
	NewAdded int `json:"new_added"`
	Updated  int `json:"updated"`
	Deleted  int `json:"deleted"`
}

type SummaryResponse struct {
	Sales     SummarySales     `json:"sales"`
	Parts     SummaryParts     `json:"parts"`
	Customers SummaryCustomers `json:"customers"`
}

type PSAReportRow struct {
	PartNumber   string  `json:"part_number"`
	Description  string  `json:"description"`
	Source       string  `json:"source"`
	Qty          float64 `json:"qty"`
	Cost         float64 `json:"cost"`
	Extended     float64 `json:"extended"`
	LastSaleDate string  `json:"last_sale_date"`
	MonthsNoSale int     `json:"months_no_sale"`
}

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

	targetDate := strings.TrimSpace(r.URL.Query().Get("date"))
	if targetDate == "" {
		targetDate = time.Now().Format("01/02/2006")
	}

	dateParts := strings.Split(targetDate, "/")
	if len(dateParts) == 3 && len(dateParts[2]) == 2 {
		targetDate = fmt.Sprintf("%02s/%02s/20%s", dateParts[0], dateParts[1], dateParts[2])
	} else if len(dateParts) == 2 && len(dateParts[1]) == 2 {
		targetDate = fmt.Sprintf("%02s/20%s", dateParts[0], dateParts[1])
	}
	dateParts = strings.Split(targetDate, "/")

	isMonthQuery := len(dateParts) == 2

	var dateCondition string
	var dateArg interface{}

	if isMonthQuery {
		dateCondition = "h.sale_date LIKE ?"
		dateArg = dateParts[0] + "/%/" + dateParts[1]
	} else {
		dateCondition = "h.sale_date = ?"
		dateArg = targetDate
	}

	var res SummaryResponse

	db.QueryRow(`
		SELECT 
			IFNULL(SUM(h.final_total), 0),
			IFNULL(SUM(h.subtotal), 0),
			IFNULL(SUM(h.labor_charges), 0),
			IFNULL(SUM(h.delivery_charges), 0),
			IFNULL(SUM(h.tax_amount), 0),
			COUNT(h.sale_id)
		FROM sales_history h
		WHERE `+dateCondition+` 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'
	`, dateArg).Scan(&res.Sales.TotalReceipts, &res.Sales.Parts, &res.Sales.Labor, &res.Sales.Delivery, &res.Sales.Tax, &res.Sales.InvoiceCount)

	res.Sales.Gross = res.Sales.Parts + res.Sales.Labor + res.Sales.Delivery

	var partsCost float64
	db.QueryRow(`
		SELECT 
			IFNULL(SUM(i.quantity * IFNULL(p.cost, 0.0)), 0),
			IFNULL(SUM(i.quantity), 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 `+dateCondition+` 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')
	`, dateArg).Scan(&partsCost, &res.Parts.QtySold)

	res.Sales.Profit = res.Sales.Gross - partsCost

	db.QueryRow(`SELECT COUNT(*) FROM sales_history h WHERE `+dateCondition+` AND h.invoice_number = 'MAINT' AND h.sale_id LIKE 'MNT-%' AND h.customer_id = 'PART INITIAL CREATION'`, dateArg).Scan(&res.Parts.NewAdded)
	db.QueryRow(`SELECT COUNT(*) FROM sales_history h WHERE `+dateCondition+` AND h.invoice_number = 'MAINT' AND h.sale_id LIKE 'DEL-%' AND h.customer_id LIKE 'PART PURGED:%'`, dateArg).Scan(&res.Parts.Deleted)
	db.QueryRow(`SELECT COUNT(*) FROM sales_history h WHERE `+dateCondition+` AND h.invoice_number = 'MAINT' AND h.sale_id LIKE 'MNT-%' AND h.customer_id LIKE 'UPD:%'`, dateArg).Scan(&res.Parts.PriceUpdates)
	db.QueryRow(`SELECT COUNT(*) FROM sales_history h WHERE `+dateCondition+` AND h.invoice_number = 'MAINT' AND h.sale_id LIKE 'PI-%'`, dateArg).Scan(&res.Parts.PICounts)

	db.QueryRow(`SELECT COUNT(*) FROM sales_history h WHERE `+dateCondition+` AND h.invoice_number = 'MAINT' AND h.sale_id LIKE 'CST-%' AND h.customer_id = 'CUSTOMER PROFILE CREATED'`, dateArg).Scan(&res.Customers.NewAdded)
	db.QueryRow(`SELECT COUNT(*) FROM sales_history h WHERE `+dateCondition+` AND h.invoice_number = 'MAINT' AND h.sale_id LIKE 'CST-%' AND h.customer_id LIKE 'UPD:%'`, dateArg).Scan(&res.Customers.Updated)
	db.QueryRow(`SELECT COUNT(*) FROM sales_history h WHERE `+dateCondition+` AND h.invoice_number = 'MAINT' AND h.sale_id LIKE 'DEL-%' AND h.customer_id LIKE 'CUSTOMER PURGED:%'`, dateArg).Scan(&res.Customers.Deleted)

	json.NewEncoder(w).Encode(res)
}

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

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

	rows, err := db.Query("SELECT * FROM customers")
	if err != nil {
		log.Printf(">> ARBAL QUERY ERROR: %v", err)
		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] }
			}

			bal := coalesceFloat(rowMap, "account_balance", "balance", "acct_balance")
			
			if bal > 0.001 { 
				
				name := coalesceString(rowMap, "name", "customer_name", "cust_name")
				if name == "" { name = "UNKNOWN" }
				
				phone := coalesceString(rowMap, "phone1", "phone", "cust_phone")
				if phone == "" { phone = "---" }

				lastSale := coalesceString(rowMap, "last_sale_date", "last_sale", "last_payment")
				if strings.Contains(lastSale, "T") {
					lastSale = strings.Split(lastSale, "T")[0]
				} else if len(lastSale) > 10 {
					lastSale = lastSale[:10]
				}

				resRow := map[string]interface{}{
					"customer_id":      coalesceString(rowMap, "customer_id", "id", "cust_id"),
					"name":             name,
					"customer_name":    name,
					"contact_person":   coalesceString(rowMap, "contact_person", "contact"),
					"phone1":           phone,
					"phone":            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":  bal,
					"balance":          bal,
					"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":   lastSale,
					"last_payment":     lastSale,
					"credit_limit":     coalesceFloat(rowMap, "credit_limit", "limit"),
				}
				results = append(results, resRow)
			}
		}
	}

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

func handleDailyReport(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")
	
	targetDate := strings.TrimSpace(r.URL.Query().Get("date"))
	if targetDate == "" {
		targetDate = time.Now().Format("01/02/2006")
	}

	dateParts := strings.Split(targetDate, "/")
	if len(dateParts) == 3 && len(dateParts[2]) == 2 {
		targetDate = fmt.Sprintf("%02s/%02s/20%s", dateParts[0], dateParts[1], dateParts[2])
	} else if len(dateParts) == 2 && len(dateParts[1]) == 2 {
		targetDate = fmt.Sprintf("%02s/20%s", dateParts[0], dateParts[1])
	}
	dateParts = strings.Split(targetDate, "/")

	isMonthQuery := len(dateParts) == 2
	
	var dateCondition string
	var dateArg interface{}
	
	if isMonthQuery {
		dateCondition = "h.sale_date LIKE ?"
		dateArg = dateParts[0] + "/%/" + dateParts[1]
	} else {
		dateCondition = "h.sale_date = ?"
		dateArg = targetDate
	}

	var res struct {
		TotalCash      float64 `json:"total_cash"`
		PartsRetail    float64 `json:"parts_retail"`
		PartsCost      float64 `json:"parts_cost"`
		LaborTotal     float64 `json:"labor_total"`
		DeliveryTotal  float64 `json:"delivery_total"`
		TaxTotal       float64 `json:"tax_total"`

		YTDCash        float64 `json:"ytd_cash"`
		YTDPartsRetail float64 `json:"ytd_parts_retail"`
		YTDPartsCost   float64 `json:"ytd_parts_cost"`
		YTDLabor       float64 `json:"ytd_labor"`
		YTDDelivery    float64 `json:"ytd_delivery"`
		YTDTax         float64 `json:"ytd_tax"`
		
		InventoryValue float64 `json:"inventory_value"`
	}

	err := db.QueryRow(`
		SELECT 
			IFNULL(SUM(h.final_total), 0),
			IFNULL(SUM(h.labor_charges), 0),
			IFNULL(SUM(h.delivery_charges), 0),
			IFNULL(SUM(h.tax_amount), 0)
		FROM sales_history h
		WHERE `+dateCondition+` 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'
	`, dateArg).Scan(&res.TotalCash, &res.LaborTotal, &res.DeliveryTotal, &res.TaxTotal)

	if err != nil { log.Printf(">> DCR SUMMARY ERROR: %v", err) }

	err = 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 `+dateCondition+` 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')
	`, dateArg).Scan(&res.PartsRetail, &res.PartsCost)

	if err != nil { log.Printf(">> DCR COST ERROR: %v", err) }

	currentYear := "2026"
	if len(dateParts) > 0 {
		currentYear = dateParts[len(dateParts)-1]
	}

	db.QueryRow(`
		SELECT 
			IFNULL(SUM(h.final_total), 0),
			IFNULL(SUM(h.labor_charges), 0),
			IFNULL(SUM(h.delivery_charges), 0),
			IFNULL(SUM(h.tax_amount), 0)
		FROM sales_history h
		WHERE (h.sale_date LIKE ? OR h.invoice_number = 'YTD-INIT') AND h.invoice_number != 'MAINT' AND h.invoice_number NOT LIKE 'S-%' AND h.invoice_number NOT LIKE 'RCV-%' AND h.status != 'STORED'
	`, "%/"+currentYear).Scan(&res.YTDCash, &res.YTDLabor, &res.YTDDelivery, &res.YTDTax)

	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 ? OR h.invoice_number = 'YTD-INIT') AND h.invoice_number != 'MAINT' AND h.invoice_number NOT LIKE 'S-%' AND h.invoice_number NOT LIKE 'RCV-%' AND h.status != 'STORED' AND i.part_number NOT IN ('SYS:LABOR', 'SYS:DELIVERY')
	`, "%/"+currentYear).Scan(&res.YTDPartsRetail, &res.YTDPartsCost)

	db.QueryRow(`SELECT IFNULL(SUM(CAST(quantity_on_hand AS REAL) * CAST(cost AS REAL)), 0) FROM parts`).Scan(&res.InventoryValue)

	json.NewEncoder(w).Encode(res)
}

func handleDailyPartsReport(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")
	
	targetDate := strings.TrimSpace(r.URL.Query().Get("date"))
	if targetDate == "" {
		targetDate = time.Now().Format("01/02/2006")
	}

	dateParts := strings.Split(targetDate, "/")
	if len(dateParts) == 3 && len(dateParts[2]) == 2 {
		targetDate = fmt.Sprintf("%02s/%02s/20%s", dateParts[0], dateParts[1], dateParts[2])
	} else if len(dateParts) == 2 && len(dateParts[1]) == 2 {
		targetDate = fmt.Sprintf("%02s/20%s", dateParts[0], dateParts[1])
	}
	dateParts = strings.Split(targetDate, "/")

	isMonthQuery := len(dateParts) == 2
	
	var dateCondition string
	var dateArg interface{}
	
	if isMonthQuery {
		dateCondition = "h.sale_date LIKE ?"
		dateArg = dateParts[0] + "/%/" + dateParts[1]
	} else {
		dateCondition = "h.sale_date = ?"
		dateArg = targetDate
	}

	query := `
		SELECT 
			h.invoice_number, 
			h.sale_date,
			i.part_number, 
			i.description, 
			i.quantity, 
			IFNULL(p.cost, 0.0), 
			i.sell_price, 
			i.line_total, 
			COALESCE(NULLIF(TRIM(c.name), ''), NULLIF(TRIM(h.customer_id), ''), 'CASH SALE')
		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))
		LEFT JOIN customers c ON TRIM(UPPER(h.customer_id)) = TRIM(UPPER(c.customer_id))
		WHERE ` + dateCondition + ` 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')
		ORDER BY h.sale_date ASC, h.invoice_number ASC`

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

	var results []map[string]interface{}
	for rows.Next() {
		var inv, sdate, part, desc, cust sql.NullString
		var qty, cost, price, ext sql.NullFloat64

		if err := rows.Scan(&inv, &sdate, &part, &desc, &qty, &cost, &price, &ext, &cust); err == nil {
			results = append(results, map[string]interface{}{
				"invoice_number": strings.TrimSpace(inv.String),
				"sale_date":      strings.TrimSpace(sdate.String),
				"part_number":    strings.TrimSpace(part.String),
				"description":    strings.TrimSpace(desc.String),
				"qty":            qty.Float64,
				"cost":           cost.Float64,
				"price":          price.Float64,
				"extended":       ext.Float64,
				"customer_name":  strings.TrimSpace(cust.String),
			})
		}
	}
	
	if results == nil { results = []map[string]interface{}{} }
	json.NewEncoder(w).Encode(results)
}

func handleFinTotals(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")
	
	now := time.Now()
	currentMonth := fmt.Sprintf("%02d", now.Month())
	currentYear := fmt.Sprintf("%d", now.Year())

	query := `
		SELECT 
			CASE 
				WHEN i.part_number = 'SYS:LABOR' THEN '350.14'
				WHEN p.stock_type = 'WG' THEN '320.01'
				ELSE '341.03' 
			END as account_id,
			h.sale_date,
			SUM(i.line_total) as sales_amount,
			SUM(i.quantity * IFNULL(p.cost, 0.0)) as cost_amount
		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.invoice_number != 'MAINT' AND h.invoice_number NOT LIKE 'S-%' AND h.invoice_number NOT LIKE 'RCV-%'
		GROUP BY account_id, h.sale_date
	`

	rows, err := db.Query(query)
	if err != nil {
		log.Printf(">> SQLITE ENGINE ERROR (FIN TOTALS): %v", err)
		http.Error(w, "DATABASE QUERY FAILED", http.StatusInternalServerError)
		return
	}
	defer rows.Close()

	accs := map[string]map[string]float64{
		"320.01": {"mSales": 0, "mCost": 0, "ySales": 0, "yCost": 0},
		"350.14": {"mSales": 0, "mCost": 0, "ySales": 0, "yCost": 0},
		"341.03": {"mSales": 0, "mCost": 0, "ySales": 0, "yCost": 0},
	}

	for rows.Next() {
		var accountID, saleDate string
		var salesAmount, costAmount float64

		if err := rows.Scan(&accountID, &saleDate, &salesAmount, &costAmount); err == nil {
			saleDate = strings.TrimSpace(saleDate)
			
			if strings.HasSuffix(saleDate, currentYear) {
				accs[accountID]["ySales"] += salesAmount
				accs[accountID]["yCost"] += costAmount
				
				if strings.HasPrefix(saleDate, currentMonth+"/") {
					accs[accountID]["mSales"] += salesAmount
					accs[accountID]["mCost"] += costAmount
				}
			}
		}
	}

	payload := make(map[string]AccountTotals)

	for accID, data := range accs {
		mMargin := data["mSales"] - data["mCost"]
		yMargin := data["ySales"] - data["yCost"]

		mPct := 0.0
		if data["mSales"] > 0 {
			mPct = (mMargin / data["mSales"]) * 100
		}

		yPct := 0.0
		if data["ySales"] > 0 {
			yPct = (yMargin / data["ySales"]) * 100
		}

		payload[accID] = AccountTotals{
			Monthly: FinPeriodData{
				Sales:      fmt.Sprintf("%.2f", data["mSales"]),
				Cost:       fmt.Sprintf("%.2f", data["mCost"]),
				Margin:     fmt.Sprintf("%.2f", mMargin),
				Percentage: fmt.Sprintf("%.1f", mPct),
			},
			YTD: FinPeriodData{
				Sales:      fmt.Sprintf("%.2f", data["ySales"]),
				Cost:       fmt.Sprintf("%.2f", data["yCost"]),
				Margin:     fmt.Sprintf("%.2f", yMargin),
				Percentage: fmt.Sprintf("%.1f", yPct),
			},
		}
	}

	json.NewEncoder(w).Encode(payload)
}

func handleDFSReport(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")
	
	var res DFSReportResponse
	
	currentYear := fmt.Sprintf("%d", time.Now().Year())

	db.QueryRow("SELECT IFNULL(SUM(CAST(quantity_on_hand AS REAL) * CAST(cost AS REAL)), 0) FROM parts").Scan(&res.Assets.InventoryValuation)
	db.QueryRow("SELECT IFNULL(SUM(CAST(account_balance AS REAL)), 0) FROM customers").Scan(&res.Assets.ARCustomer)
	
	res.Assets.ARWorkorders = 0.0
	res.Assets.TotalAssets = res.Assets.InventoryValuation + res.Assets.ARCustomer + res.Assets.ARWorkorders

	query := `
		SELECT 
			h.sale_date,
			i.part_number,
			i.line_total,
			IFNULL(p.cost, 0.0),
			i.quantity,
			h.tax_amount,
			h.invoice_number
		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.invoice_number != 'MAINT' AND h.invoice_number NOT LIKE 'S-%' AND h.invoice_number NOT LIKE 'RCV-%'
	`

	rows, err := db.Query(query)
	if err != nil {
		log.Printf(">> DFS SQLITE EXCEPTION: %v", err)
		http.Error(w, "DATABASE QUERY FAILED", http.StatusInternalServerError)
		return
	}
	defer rows.Close()

	processedInvoices := make(map[string]bool)

	for rows.Next() {
		var saleDate, partNo, invNo string
		var lineTotal, unitCost, qty, taxAmount float64

		if err := rows.Scan(&saleDate, &partNo, &lineTotal, &unitCost, &qty, &taxAmount, &invNo); err == nil {
			saleDate = strings.TrimSpace(saleDate)
			
			if strings.HasSuffix(saleDate, currentYear) {
				monthStr := saleDate[:2]
				monthIdx, err := strconv.Atoi(monthStr)
				if err != nil || monthIdx < 1 || monthIdx > 12 {
					continue
				}
				idx := monthIdx - 1
				
				if partNo == "SYS:LABOR" {
					res.YTD.LaborRevenue += lineTotal
					res.Monthly[idx].LaborDelivery += lineTotal
				} else if partNo == "SYS:DELIVERY" {
					res.YTD.DeliveryRevenue += lineTotal
					res.Monthly[idx].LaborDelivery += lineTotal
				} else {
					res.YTD.PartsRevenue += lineTotal
					res.Monthly[idx].PartsRevenue += lineTotal
					
					cogs := qty * unitCost
					res.YTD.COGS += cogs
					res.Monthly[idx].COGS += cogs
				}

				if !processedInvoices[invNo] {
					res.YTD.TaxCollected += taxAmount
					processedInvoices[invNo] = true
				}
			}
		}
	}

	res.YTD.OperatingRevenue = res.YTD.PartsRevenue + res.YTD.LaborRevenue + res.YTD.DeliveryRevenue
	res.YTD.CashReceipts = res.YTD.OperatingRevenue + res.YTD.TaxCollected + res.YTD.ManualAdjustments
	res.YTD.GrossProfit = res.YTD.OperatingRevenue - res.YTD.COGS

	for i := 0; i < 12; i++ {
		res.Monthly[i].GrossProfit = (res.Monthly[i].PartsRevenue + res.Monthly[i].LaborDelivery) - res.Monthly[i].COGS
	}

	json.NewEncoder(w).Encode(res)
}

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

	now := time.Now()
	currentYearStr := fmt.Sprintf("%d", now.Year())
	currentMonthStr := fmt.Sprintf("%02d", now.Month())
	
	ytdMap := make(map[string]float64)
	mtdMap := make(map[string]float64)

	yearPattern := "%/" + currentYearStr
	salesQuery := `SELECT TRIM(UPPER(customer_id)), sale_date, final_total FROM sales_history WHERE invoice_number != 'MAINT' AND invoice_number NOT LIKE 'S-%' AND invoice_number NOT LIKE 'RCV-%' AND invoice_number != 'YTD-INIT' AND sale_date LIKE ?`
	
	salesRows, err := db.Query(salesQuery, yearPattern)
	if err == nil {
		for salesRows.Next() {
			var cid, sDate string
			var total float64
			if err := salesRows.Scan(&cid, &sDate, &total); err == nil {
				ytdMap[cid] += total
				if strings.HasPrefix(sDate, currentMonthStr+"/") {
					mtdMap[cid] += total
				}
			}
		}
		salesRows.Close()
	}

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

	rows, err := db.Query("SELECT * FROM customers")
	if err != nil {
		log.Printf(">> CSR QUERY ERROR: %v", err)
		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] }
			}

			custId := coalesceString(rowMap, "customer_id", "id", "cust_id")
			
			ytd := ytdMap[strings.ToUpper(custId)]
			mtd := mtdMap[strings.ToUpper(custId)]

			if ytd > 0.001 || mtd > 0.001 {
				name := coalesceString(rowMap, "name", "customer_name", "cust_name")
				if name == "" { name = "UNKNOWN" }
				
				phone := coalesceString(rowMap, "phone1", "phone", "cust_phone")
				if phone == "" { phone = "---" }

				results = append(results, map[string]interface{}{
					"customer_id":   custId,
					"customer_name": name,
					"phone":         phone,
					"mtd_sales":     mtd,
					"ytd_sales":     ytd,
				})
			}
		}
	}

	if results == nil {
		results = []map[string]interface{}{}
	}

	sort.Slice(results, func(i, j int) bool {
		return results[i]["ytd_sales"].(float64) > results[j]["ytd_sales"].(float64)
	})

	json.NewEncoder(w).Encode(results)
}

func handleFinancialReport(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")
	if r.Method != http.MethodGet {
		http.Error(w, "METHOD NOT ALLOWED", http.StatusMethodNotAllowed)
		return
	}

	var report FinancialReport

	db.QueryRow(`SELECT IFNULL(SUM(CAST(quantity_on_hand AS REAL) * CAST(cost AS REAL)), 0) FROM parts`).Scan(&report.InventoryTotal)
	db.QueryRow(`SELECT COALESCE(SUM(account_balance), 0) FROM customers`).Scan(&report.ARBalance)
	report.CashBalance = 1250.00

	currentYear := fmt.Sprintf("%d", time.Now().Year())
	currentMonth := fmt.Sprintf("%02d", time.Now().Month())

	query := `
		SELECT 
			h.sale_date,
			i.line_total,
			IFNULL(p.cost, 0.0),
			i.quantity,
			h.tax_amount,
			h.invoice_number
		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.invoice_number != 'MAINT' AND h.invoice_number NOT LIKE 'S-%' AND h.invoice_number NOT LIKE 'RCV-%'
	`
	rows, err := db.Query(query)
	if err != nil {
		log.Printf(">> FIN REPORT ERROR: %v", err)
		http.Error(w, "DATABASE QUERY FAILED", http.StatusInternalServerError)
		return
	}
	defer rows.Close()

	processedInvoices := make(map[string]bool)

	for rows.Next() {
		var saleDate, invNo string
		var lineTotal, unitCost, qty, taxAmount float64

		if err := rows.Scan(&saleDate, &lineTotal, &unitCost, &qty, &taxAmount, &invNo); err == nil {
			saleDate = strings.TrimSpace(saleDate)
			
			if strings.HasSuffix(saleDate, currentYear) {
				cogs := qty * unitCost
				isCurrentMonth := strings.HasPrefix(saleDate, currentMonth+"/")
				
				report.SalesYTD += lineTotal
				report.COGSYTD += cogs
				
				if isCurrentMonth {
					report.SalesMonth += lineTotal
					report.COGSMonth += cogs
				}

				if !processedInvoices[invNo] {
					report.TaxYTD += taxAmount
					if isCurrentMonth {
						report.TaxMonth += taxAmount
					}
					processedInvoices[invNo] = true
				}
			}
		}
	}

	json.NewEncoder(w).Encode(report)
}

func handlePSAReport(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")
	
	monthsParam := r.URL.Query().Get("months")
	sourceParam := strings.TrimSpace(strings.ToUpper(r.URL.Query().Get("source")))
	typeParam := strings.TrimSpace(strings.ToUpper(r.URL.Query().Get("type")))

	minMonths, err := strconv.Atoi(monthsParam)
	if err != nil { minMonths = 12 } 

	query := `
		SELECT 
			COALESCE(part_number, ''),
			COALESCE(description, ''),
			COALESCE(source, ''),
			CAST(COALESCE(NULLIF(quantity_on_hand, ''), 0.0) AS REAL),
			CAST(COALESCE(NULLIF(cost, ''), 0.0) AS REAL),
			COALESCE(NULLIF(TRIM(last_sale_date), ''), 'NEVER')
		FROM parts
		WHERE CAST(COALESCE(NULLIF(quantity_on_hand, ''), 0.0) AS REAL) > 0
	`
	
	var args []interface{}
	
	// Add dynamic Stock Type filter
	if typeParam != "" && typeParam != "ALL" {
		query += " AND COALESCE(NULLIF(stock_type, ''), 'ST') = ?"
		args = append(args, typeParam)
	}
	
	if sourceParam != "" && sourceParam != "ALL" {
		query += " AND TRIM(UPPER(source)) = ?"
		args = append(args, sourceParam)
	}
	
	query += " ORDER BY source, part_number"

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

	now := time.Now()
	var results []PSAReportRow

	for rows.Next() {
		var row PSAReportRow
		if err := rows.Scan(&row.PartNumber, &row.Description, &row.Source, &row.Qty, &row.Cost, &row.LastSaleDate); err == nil {
			row.Extended = row.Qty * row.Cost
			row.MonthsNoSale = 0
			
			if row.LastSaleDate == "NEVER" {
				row.MonthsNoSale = 999 
			} else if len(row.LastSaleDate) >= 10 {
				parts := strings.Split(row.LastSaleDate[:10], "/")
				if len(parts) == 3 {
					m, _ := strconv.Atoi(parts[0])
					y, _ := strconv.Atoi(parts[2])
					if y < 100 { y += 2000 } 
					mos := (now.Year() - y) * 12 + (int(now.Month()) - m)
					if mos > 0 { row.MonthsNoSale = mos }
				}
			}

			if row.MonthsNoSale >= minMonths {
				results = append(results, row)
			}
		}
	}

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

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

	// Query sorts by YTD Sales descending and limits to top 25
	query := `
		SELECT 
			COALESCE(part_number, ''),
			COALESCE(description, ''),
			COALESCE(source, ''),
			CAST(COALESCE(NULLIF(quantity_on_hand, ''), 0.0) AS REAL),
			CAST(COALESCE(NULLIF(cost, ''), 0.0) AS REAL),
			CAST(COALESCE(NULLIF(ytd_sales, ''), 0.0) AS REAL)
		FROM parts
		WHERE CAST(COALESCE(NULLIF(ytd_sales, ''), 0.0) AS REAL) > 0
	`
	
	var args []interface{}
	
	if sourceParam != "" && sourceParam != "ALL" {
		query += " AND TRIM(UPPER(source)) = ?"
		args = append(args, sourceParam)
	}
	
	query += " ORDER BY CAST(COALESCE(NULLIF(ytd_sales, ''), 0.0) AS REAL) DESC LIMIT 25"

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

	var results []map[string]interface{}

	for rows.Next() {
		var pn, desc, src string
		var qty, cost, ytd float64
		
		if err := rows.Scan(&pn, &desc, &src, &qty, &cost, &ytd); err == nil {
			results = append(results, map[string]interface{}{
				"part_number": pn,
				"description": desc,
				"source":      src,
				"qty":         qty,
				"cost":        cost,
				"ytd_sales":   ytd,
			})
		}
	}

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