package main

import (
	"bytes"
	"encoding/csv"
	"encoding/json"
	"fmt"
	"log"
	"net/http"
	"os"
	"path/filepath"
	"regexp"
	"strconv"
	"strings"
	"time"

	"github.com/xuri/excelize/v2"
)

type Config struct {
	InputDir  string
	OutputDir string
}

type VehicleApp struct {
	Make      string
	Model    string
	YearFrom  int
	YearTo    int
}

func main() {
	cfg := Config{
		InputDir:  "../database",
		OutputDir: "../database/csv",
	}

	if err := os.MkdirAll(cfg.OutputDir, 0755); err != nil {
		log.Fatal(err)
	}

	if err := processAllFiles(cfg); err != nil {
		log.Fatal(err)
	}
}

func processAllFiles(cfg Config) error {
	files := map[string]string{
		"amort":    filepath.Join(cfg.InputDir, "amort-deseable.xlsx"),
		"frenos":   filepath.Join(cfg.InputDir, "frenos-deseable.xlsx"),
		"aces":     filepath.Join(cfg.InputDir, "aces.xlsx"),
		"ag":       filepath.Join(cfg.InputDir, "AG.xlsx"),
		"monroe":   filepath.Join(cfg.InputDir, "Monroe.xlsx"),
		"brembo":   filepath.Join(cfg.InputDir, "Brembo.xlsx"),
		"cano":     filepath.Join(cfg.InputDir, "Cano.xlsx"),
		"prestone": filepath.Join(cfg.InputDir, "PRESTONE 2026.xlsx"),
	}

	for name, fp := range files {
		fmt.Printf("Processing: %s\n", filepath.Base(fp))

		switch name {
		case "amort":
			if err := processAmortiguadores(fp, cfg.OutputDir); err != nil {
				log.Printf("Error: %v", err)
			}
		case "frenos":
			if err := processFrenos(fp, cfg.OutputDir); err != nil {
				log.Printf("Error: %v", err)
			}
		case "aces":
			if err := processAces(fp, cfg.OutputDir); err != nil {
				log.Printf("Error: %v", err)
			}
		case "ag":
			if err := processAG(fp, cfg.OutputDir); err != nil {
				log.Printf("Error: %v", err)
			}
		case "monroe":
			if err := processMonroe(fp, cfg.OutputDir); err != nil {
				log.Printf("Error: %v", err)
			}
		case "brembo":
			if err := processBrembo(fp, cfg.OutputDir); err != nil {
				log.Printf("Error: %v", err)
			}
		case "cano":
			if err := processCano(fp, cfg.OutputDir); err != nil {
				log.Printf("Error: %v", err)
			}
		case "prestone":
			if err := processPrestone(fp, cfg.OutputDir); err != nil {
				log.Printf("Error: %v", err)
			}
		}
	}
	return nil
}

func processAmortiguadores(fp, outDir string) error {
	f, err := excelize.OpenFile(fp)
	if err != nil {
		return err
	}
	defer f.Close()

	rows, err := f.GetRows("GRC Web Cat")
	if err != nil {
		return err
	}

	type appData struct {
		app     VehicleApp
		codigo  string
		imgUrl  string
	}
	apps := make(map[string]appData)

	for _, row := range rows {
		if len(row) < 17 {
			continue
		}

		marca := cleanStr(row[1])
		modelo := cleanStr(row[2])
		anoStr := cleanStr(row[3])
		codigo := cleanStr(row[4])
		imgUrl := cleanStr(row[16])

		if marca == "" || modelo == "" {
			continue
		}

		var year int
		if anoStr != "" {
			year, _ = strconv.Atoi(anoStr)
		}

		key := marca + "|" + modelo
		if existing, ok := apps[key]; ok {
			if year > 0 {
				if year > 0 && year < existing.app.YearFrom {
					existing.app.YearFrom = year
				}
				if year > existing.app.YearTo {
					existing.app.YearTo = year
				}
				if codigo != "" {
					existing.codigo = codigo
				}
				if imgUrl != "" && existing.imgUrl == "" {
					existing.imgUrl = imgUrl
				}
				apps[key] = existing
			}
		} else {
			from := year
			to := year
			if from == 0 {
				from = 1900
				to = 2025
			}
			apps[key] = appData{
				app:     VehicleApp{Make: marca, Model: modelo, YearFrom: from, YearTo: to},
				codigo:  codigo,
				imgUrl:  imgUrl,
			}
		}
	}

	file, err := os.Create(filepath.Join(outDir, "ag_shock.csv"))
	if err != nil {
		return err
	}
	defer file.Close()

	writer := csv.NewWriter(file)
	writer.Comma = '|'
	writer.Write([]string{"codigo", "kyb", "boge", "marca", "aplicacion", "desde", "hasta", "pos", "img_url"})
	writer.Flush()

	for _, data := range apps {
		aplicacion := data.app.Make + " " + data.app.Model
		writer.Write([]string{
			data.codigo, "", "", data.app.Make,
			aplicacion,
			strconv.Itoa(data.app.YearFrom), strconv.Itoa(data.app.YearTo),
			"", data.imgUrl,
		})
	}
	writer.Flush()
	fmt.Printf("Exported: ag_shock.csv (%d rows)\n", len(apps))

	return nil
}

func processFrenos(fp, outDir string) error {
	f, err := excelize.OpenFile(fp)
	if err != nil {
		return err
	}
	defer f.Close()

	rows, err := f.GetRows("GRC Ultra & Prime Database")
	if err != nil {
		return err
	}

	type brakeData struct {
		app      VehicleApp
		parte    string
		producto string
		fmsi     string
	}
	apps := make(map[string]brakeData)

	for _, row := range rows {
		if len(row) < 8 {
			continue
		}

		marca := cleanStr(row[0])
		modelo := cleanStr(row[1])
		anoStr := cleanStr(row[2])
		parte := cleanStr(row[3])
		producto := cleanStr(row[5])
		fmsi := cleanStr(row[7])

		if marca == "" || modelo == "" {
			continue
		}

		var year int
		if anoStr != "" {
			year, _ = strconv.Atoi(anoStr)
		}

		key := marca + "|" + modelo
		if existing, ok := apps[key]; ok {
			if year > 0 {
				if year > 0 && year < existing.app.YearFrom {
					existing.app.YearFrom = year
				}
				if year > existing.app.YearTo {
					existing.app.YearTo = year
				}
				apps[key] = existing
			}
		} else {
			from := year
			to := year
			if from == 0 {
				from = 1900
				to = 2025
			}
			apps[key] = brakeData{
				app:      VehicleApp{Make: marca, Model: modelo, YearFrom: from, YearTo: to},
				parte:    parte,
				producto: producto,
				fmsi:     fmsi,
			}
		}
	}

	file, err := os.Create(filepath.Join(outDir, "brembo_mx0301.csv"))
	if err != nil {
		return err
	}
	defer file.Close()

	writer := csv.NewWriter(file)
	writer.Comma = '|'
	writer.Write([]string{"no_parte", "producto", "aplicacion", "fmsi"})
	writer.Flush()

	for _, data := range apps {
		aplicacion := data.app.Make + " " + data.app.Model + " " + strconv.Itoa(data.app.YearFrom) + "-" + strconv.Itoa(data.app.YearTo)
		writer.Write([]string{data.parte, data.producto, aplicacion, data.fmsi})
	}
	writer.Flush()
	fmt.Printf("Exported: brembo_mx0301.csv (%d rows)\n", len(apps))

	return nil
}

func processAces(fp, outDir string) error {
	f, err := excelize.OpenFile(fp)
	if err != nil {
		return err
	}
	defer f.Close()

	sheets := f.GetSheetList()
	fmt.Printf("  Sheets: %v\n", sheets)

	for _, sheet := range sheets {
		rows, err := f.GetRows(sheet)
		if err != nil {
			continue
		}

		outFile := filepath.Join(outDir, strings.ReplaceAll(sheet, " ", "_") + ".csv")
		file, err := os.Create(outFile)
		if err != nil {
			continue
		}
		defer file.Close()

		writer := csv.NewWriter(file)
		writer.Comma = '|'

		for i, row := range rows {
			if i == 0 {
				writer.Write(row)
			} else if len(row) > 0 {
				writer.Write(row)
			}
		}
		writer.Flush()
		fmt.Printf("Exported: %s (%d rows)\n", filepath.Base(outFile), len(rows))
	}

	return nil
}

func processAG(fp, outDir string) error {
	f, err := excelize.OpenFile(fp)
	if err != nil {
		return err
	}
	defer f.Close()

	sheetsConfig := []struct {
		sheetName     string
		outputFile    string
		delimiter     rune
		excelHeaders  []string
		outputHeaders []string
	}{
		{
			sheetName:     "Amortiguadores AG Shock",
			outputFile:    "AG_shock.csv",
			delimiter:     '|',
			excelHeaders:  []string{"código", "kyb", "boge", "marca", "aplicación", "desde", "hasta", "pos", "link imagen"},
			outputHeaders: []string{"codigo", "kyb", "boge", "marca", "aplicacion", "desde", "hasta", "pos", "img_url"},
		},
		{
			sheetName:     "Bases Amortiguador AG Strut",
			outputFile:    "AG_strut.csv",
			delimiter:     '|',
			excelHeaders:  []string{"código", "syd", "vazlo", "marca", "aplicación", "desde", "hasta", "pos", "link imagen"},
			outputHeaders: []string{"codigo", "syd", "vazlo", "marca", "aplicacion", "desde", "hasta", "pos", "img_url"},
		},
		{
			sheetName:     "Piernas AG Proshock",
			outputFile:    "AG_leg.csv",
			delimiter:     '|',
			excelHeaders:  []string{"código", "marca", "aplicación", "desde", "hasta", "pos", "link imagen"},
			outputHeaders: []string{"codigo", "marca", "aplicacion", "desde", "hasta", "pos", "img_url"},
		},
	}

	for _, cfg := range sheetsConfig {
		if err := processAGSheet(f, cfg.sheetName, outDir, cfg.outputFile, cfg.delimiter, cfg.excelHeaders, cfg.outputHeaders); err != nil {
			log.Printf("Error processing sheet %s: %v", cfg.sheetName, err)
		}
	}

	return nil
}

func processMonroePackages(f *excelize.File) map[string]string {
	result := make(map[string]string)
	rows, err := f.GetRows("Packages")
	if err != nil {
		fmt.Printf("  Warning: Packages sheet not found: %v\n", err)
		return result
	}

	for i, row := range rows {
		if i == 0 || len(row) < 9 {
			continue
		}
		partNumber := cleanStr(row[0])
		category := cleanStr(row[8])
		if partNumber != "" && category != "" && (strings.Contains(strings.ToLower(category), "ld") || strings.Contains(strings.ToLower(category), "hd")) {
			result[partNumber] = strings.ToUpper(category)
		}
	}
	fmt.Printf("  Packages: extracted %d part numbers with LD/HD info\n", len(result))
	return result
}

func processMonroe(fp, outDir string) error {
	f, err := excelize.OpenFile(fp)
	if err != nil {
		return err
	}
	defer f.Close()

	fmt.Println("  Sheets:", f.GetSheetList())

	packages := processMonroePackages(f)
	fmt.Printf("  Packages: %d parts mapped\n", len(packages))

	applications := processMonroeApplicationsAll(f)
	fmt.Printf("  Applications: %d rows extracted\n", len(applications))

	descriptions := processMonroeDescriptionsAll(f)
	fmt.Printf("  Descriptions: %d rows extracted\n", len(descriptions))

	ldFile, err := os.Create(filepath.Join(outDir, "monroe_ld.csv"))
	if err != nil {
		return err
	}
	defer ldFile.Close()

	hdFile, err := os.Create(filepath.Join(outDir, "monroe_hd.csv"))
	if err != nil {
		return err
	}
	defer hdFile.Close()

	ldWriter := csv.NewWriter(ldFile)
	ldWriter.Comma = '|'
	ldWriter.Write([]string{"partnumber", "qty", "parttype", "make", "model", "yearfrom", "yearto", "pos", "img_url", "content"})
	ldWriter.Flush()

	hdWriter := csv.NewWriter(hdFile)
	hdWriter.Comma = '|'
	hdWriter.Write([]string{"partnumber", "qty", "parttype", "make", "model", "yearfrom", "yearto", "pos", "img_url", "content"})
	hdWriter.Flush()

	ldCount := 0
	hdCount := 0

	for _, appRow := range applications {
		pn := appRow[0]
		row := make([]string, 10)
		copy(row, appRow)

		if descRows, ok := descriptions[pn]; ok && len(descRows) > 0 {
			row[9] = descRows[0]
		} else {
			row[9] = "MISSING"
		}

		category := "HD"
		if pkgCat, ok := packages[pn]; ok {
			if strings.Contains(pkgCat, "LD") {
				category = "LD"
			}
		}

		if category == "LD" {
			ldWriter.Write(row)
			ldCount++
		} else {
			hdWriter.Write(row)
			hdCount++
		}
	}

	ldWriter.Flush()
	hdWriter.Flush()
	fmt.Printf("Exported: monroe_ld.csv (%d rows), monroe_hd.csv (%d rows)\n", ldCount, hdCount)

	return nil
}

func processMonroeApplicationsAll(f *excelize.File) [][]string {
	rows, err := f.GetRows("Applications")
	if err != nil {
		fmt.Printf("  Warning: Applications sheet not found: %v\n", err)
		return nil
	}

	headerMap := make(map[string]int)
	headerRowIdx := -1
	appHeaders := []string{"partnumber", "qty", "parttype", "make", "model", "yearfrom", "yearto", "pos", "img_url"}
	var result [][]string

	for ri, row := range rows {
		if headerRowIdx == -1 && len(row) > 0 {
			for ci, v := range row {
				headerMap[strings.ToLower(cleanStr(v))] = ci
			}
			headerRowIdx = ri
			continue
		}
		if headerRowIdx != -1 && ri > headerRowIdx && len(row) > 0 {
			pn := ""
			vals := make([]string, len(appHeaders))
			for i, h := range appHeaders {
				ci, ok := headerMap[strings.ToLower(h)]
				if !ok || ci >= len(row) || cleanStr(row[ci]) == "" {
					vals[i] = "MISSING"
				} else {
					vals[i] = cleanStr(row[ci])
					if h == "partnumber" {
						pn = vals[i]
					}
				}
			}
			if pn != "" {
				result = append(result, vals)
			}
		}
	}
	return result
}

func processMonroeDescriptionsAll(f *excelize.File) map[string][]string {
	result := make(map[string][]string)
	rows, err := f.GetRows("Descriptions")
	if err != nil {
		return result
	}

	headerMap := make(map[string]int)
	headerRowIdx := -1

	for ri, row := range rows {
		if headerRowIdx == -1 && len(row) > 0 {
			for ci, v := range row {
				headerMap[strings.ToLower(cleanStr(v))] = ci
			}
			headerRowIdx = ri
			continue
		}
		if headerRowIdx != -1 && ri > headerRowIdx && len(row) > 0 {
			pn := ""
			var content string
			if ci, ok := headerMap["partnumber"]; ok && ci < len(row) {
				pn = cleanStr(row[ci])
			}
			if ci, ok := headerMap["content"]; ok && ci < len(row) {
				content = cleanStr(row[ci])
			}
			if pn != "" && content != "" {
				if _, exists := result[pn]; !exists {
					result[pn] = []string{pn, content}
				}
			}
		}
	}
	return result
}

func processBrembo(fp, outDir string) error {
	f, err := excelize.OpenFile(fp)
	if err != nil {
		return err
	}
	defer f.Close()

	sheets := f.GetSheetList()
	fmt.Println("  Sheets:", sheets)

	sheetName := sheets[0]
		rows, err := f.GetRows(sheetName)
		if err != nil {
			return err
		}

		headerMap := make(map[string]int)
		headerRowIdx := -1

		file, err := os.Create(filepath.Join(outDir, "brembo.csv"))

		writer := csv.NewWriter(file)
		writer.Comma = '|'

		outputHeaders := []string{"numero_de_parte", "producto", "tipo", "aplicacion", "fmsi"}
		writer.Write(outputHeaders)
		writer.Flush()

		rowCount := 0
		for rowIdx, row := range rows {
			if headerRowIdx == -1 && len(row) > 0 {
				cleanedRow := make([]string, len(row))
				for i, val := range row {
					cleanedRow[i] = strings.ToLower(cleanStr(val))
				}

				expectedHeaders := []string{"número de parte", "producto", "tipo", "aplicación", "fmsi"}
				hasExpectedHeader := false
				for _, expected := range expectedHeaders {
					for _, val := range cleanedRow {
						if val == expected {
							hasExpectedHeader = true
							break
						}
					}
					if hasExpectedHeader {
						break
					}
				}

				if hasExpectedHeader {
					headerRowIdx = rowIdx
					for colIdx, val := range row {
						headerMap[strings.ToLower(cleanStr(val))] = colIdx
					}
					fmt.Printf("  Sheet: %s, Header row: %d, Headers: %v\n", sheetName, headerRowIdx, headerMap)
					continue
				}

				if rowIdx <= 10 {
					func(a ...interface{}) {}(row[:5])
				}
			}

			if headerRowIdx != -1 && rowIdx > headerRowIdx && len(row) > 0 {
				numeroParte := ""
				producto := ""
				tipo := ""
				aplicacion := ""
				fmsi := ""

				for colIdx, val := range row {
					colName := ""
					for k, v := range headerMap {
						if v == colIdx {
							colName = k
							break
						}
					}

					cleanVal := cleanStr(val)
					switch colName {
					case "número de parte":
						numeroParte = cleanVal
					case "producto":
						producto = cleanVal
					case "tipo":
						tipo = cleanVal
					case "aplicación":
						aplicacion = cleanVal
					case "fmsi":
						fmsi = cleanVal
					}
				}

				writer.Write([]string{numeroParte, producto, tipo, aplicacion, fmsi})
				rowCount++
			}
		}

	writer.Flush()
	fmt.Printf("Exported: brembo.csv (%d rows)\n", rowCount)

	return nil
}

func processCano(fp, outDir string) error {
	f, err := excelize.OpenFile(fp)
	if err != nil {
		return err
	}
	defer f.Close()

	sheets := f.GetSheetList()
	fmt.Println("  Sheets:", sheets)

	for _, sheetName := range sheets {
		if sheetName != "Hoja1" {
			continue
		}

		rows, err := f.GetRows(sheetName)
		if err != nil {
			continue
		}

		headerMap := make(map[string]int)
		headerRowIdx := -1

		file, err := os.Create(filepath.Join(outDir, "cano_"+strings.ReplaceAll(sheetName, " ", "_")+".csv"))
		if err != nil {
			continue
		}
		defer file.Close()

		writer := csv.NewWriter(file)
		writer.Comma = '|'

		outputHeaders := []string{"codigo", "aplicacion", "posicion", "birlos"}
		writer.Write(outputHeaders)
		writer.Flush()

		rowCount := 0
		for rowIdx, row := range rows {
			if headerRowIdx == -1 && len(row) > 0 {
				cleanedRow := make([]string, len(row))
				for i, val := range row {
					cleanedRow[i] = strings.ToLower(cleanStr(val))
				}

				expectedHeaders := []string{"codigo", "aplicación", "posición", "birlos"}
				hasExpectedHeader := false
				for _, expected := range expectedHeaders {
					for _, val := range cleanedRow {
						if val == expected {
							hasExpectedHeader = true
							break
						}
					}
					if hasExpectedHeader {
						break
					}
				}

				if hasExpectedHeader {
					headerRowIdx = rowIdx
					for colIdx, val := range row {
						headerMap[strings.ToLower(cleanStr(val))] = colIdx
					}
					fmt.Printf("  Sheet: %s, Header row: %d, Headers: %v\n", sheetName, headerRowIdx, headerMap)
					continue
				}
			}

			if headerRowIdx != -1 && rowIdx > headerRowIdx && len(row) > 0 {
				codigo := ""
				aplicacion := ""
				posicion := ""
				birlos := ""

				for colIdx, val := range row {
					colName := ""
					for k, v := range headerMap {
						if v == colIdx {
							colName = k
							break
						}
					}

					cleanVal := cleanStr(val)
					switch colName {
					case "codigo":
						codigo = cleanVal
					case "aplicación":
						aplicacion = cleanVal
					case "posición":
						posicion = cleanVal
					case "birlos":
						birlos = cleanVal
					}
				}

				if codigo == "" {
					codigo = "MISSING"
				}
				if aplicacion == "" {
					aplicacion = "MISSING"
				}
				if posicion == "" {
					posicion = "MISSING"
				}
				if birlos == "" {
					birlos = "MISSING"
				}

				writer.Write([]string{codigo, aplicacion, posicion, birlos})
				rowCount++
			}
		}

		writer.Flush()
		fmt.Printf("Exported: %s.csv (%d rows)\n", "cano_"+strings.ReplaceAll(sheetName, " ", "_"), rowCount)
	}

	return nil
}

func processAGSheet(f *excelize.File, sheetName, outDir, outputFile string, delimiter rune, excelHeaders, outputHeaders []string) error {
	rows, err := f.GetRows(sheetName)
	if err != nil {
		return fmt.Errorf("sheet not found: %s", sheetName)
	}

	rowCount := 0
	headerMap := make(map[string]int)
	headerRowIdx := -1

	file, err := os.Create(filepath.Join(outDir, outputFile))
	if err != nil {
		return err
	}
	defer file.Close()

	writer := csv.NewWriter(file)
	writer.Comma = delimiter

	writer.Write(outputHeaders)

	for rowIdx, row := range rows {
		if headerRowIdx == -1 && len(row) > 0 {
			cleanedRow := make([]string, len(row))
			for i, val := range row {
				cleanedRow[i] = strings.ToLower(cleanStr(val))
			}

			hasExpectedHeader := false
			for _, expected := range excelHeaders {
				for _, val := range cleanedRow {
					if val == expected {
						hasExpectedHeader = true
						break
					}
				}
				if hasExpectedHeader {
					break
				}
			}

			if hasExpectedHeader {
				headerRowIdx = rowIdx
				for colIdx, val := range row {
					headerMap[strings.ToLower(cleanStr(val))] = colIdx
				}
				fmt.Printf("  Sheet: %s, Header row: %d\n", sheetName, headerRowIdx)
				continue
			}
		}

		if headerRowIdx != -1 && rowIdx > headerRowIdx && len(row) > 0 {
			rowCount++
			values := make([]string, len(excelHeaders))
			for i, expected := range excelHeaders {
				colIdx, ok := headerMap[strings.ToLower(expected)]
				if !ok || colIdx >= len(row) || cleanStr(row[colIdx]) == "" {
					values[i] = "MISSING"
				} else {
					val := cleanStr(row[colIdx])
					if strings.ToLower(expected) == "pos" {
						val = strings.ReplaceAll(val, `"`, "")
						val = strings.ReplaceAll(val, `"`, "")
					}
					values[i] = val
				}
			}
			writer.Write(values)
		}
	}

	writer.Flush()
	fmt.Printf("Exported: %s (%d rows)\n", outputFile, rowCount)

	return nil
}

func cleanStr(s string) string {
	s = strings.TrimSpace(s)
	s = regexp.MustCompile(`\s+`).ReplaceAllString(s, " ")
	return s
}

type LLMParsedApp struct {
	Make     string
	Model    string
	YearFrom string
	YearTo   string
}

// Cache for LLM parsed aplicacion
var aplicacionCache = make(map[string][]LLMParsedApp)

func parseAplicacionWithLLM(aplicacion string) ([]LLMParsedApp, error) {
	if aplicacion == "" {
		return nil, nil
	}

	// Check cache first
	if cached, ok := aplicacionCache[aplicacion]; ok {
		return cached, nil
	}

	// Split by comma to handle multiple vehicles
	vehicles := strings.Split(aplicacion, ",")
	var results []LLMParsedApp

	// Process each vehicle sequentially
	for _, vehicle := range vehicles {
		vehicle = strings.TrimSpace(vehicle)
		if vehicle == "" {
			continue
		}

		// Call LLM for each vehicle
		parsed, err := parseSingleVehicleWithLLM(vehicle)
		if err != nil {
			// Fallback to regex parsing for this vehicle
			parsed = parseSingleVehicleWithRegex(vehicle)
		}
		if parsed != nil {
			results = append(results, *parsed)
		}
	}

	// Cache the result
	if len(results) > 0 {
		aplicacionCache[aplicacion] = results
	}

	return results, nil
}

func parseSingleVehicleWithLLM(vehicle string) (*LLMParsedApp, error) {
	prompt := fmt.Sprintf(`Parse this vehicle application string and return JSON with fields: make, model, year_from, year_to. Do NOT use thinking or reasoning tokens - output JSON only.
Input: "%s"
Output JSON only, no other text. Example: {"make":"Toyota","model":"Corolla","year_from":"2020","year_to":"2022"}`, vehicle)

	payload := map[string]interface{}{
		"model":   "gemma-4-26B-A4B-it-Q8_0.gguf",
		"prompt":  prompt,
		"stream":  false,
		"n_keep":   0,
		"max_tokens": 100,
	}

	jsonData, err := json.Marshal(payload)
	if err != nil {
		return nil, err
	}

	req, err := http.NewRequest("POST", "http://localhost:8000/v1/completions", bytes.NewBuffer(jsonData))
	if err != nil {
		return nil, err
	}
	req.Header.Set("Content-Type", "application/json")

	client := &http.Client{Timeout: 30 * time.Second}
	resp, err := client.Do(req)
	if err != nil {
		return nil, err
	}
	defer resp.Body.Close()

	if resp.StatusCode != 200 {
		return nil, fmt.Errorf("LLM returned status %d", resp.StatusCode)
	}

	var result struct {
		Content string `json:"content"`
		Choices []struct {
			Text string `json:"text"`
		} `json:"choices"`
	}

	if err := json.NewDecoder(resp.Body).Decode(&result); err != nil {
		return nil, err
	}

	responseText := ""
	if len(result.Choices) > 0 {
		responseText = result.Choices[0].Text
	} else {
		responseText = result.Content
	}

	// Parse JSON from response
	responseText = strings.TrimSpace(responseText)
	// Extract JSON from response (in case there's extra text)
	start := strings.Index(responseText, "{")
	end := strings.LastIndex(responseText, "}")
	if start >= 0 && end >= start {
		responseText = responseText[start : end+1]
	}

	var parsed struct {
		Make     string `json:"make"`
		Model    string `json:"model"`
		YearFrom string `json:"year_from"`
		YearTo   string `json:"year_to"`
	}

	if err := json.Unmarshal([]byte(responseText), &parsed); err != nil {
		return nil, err
	}

	if parsed.Make == "" {
		return nil, fmt.Errorf("empty make")
	}

	return &LLMParsedApp{
		Make:     parsed.Make,
		Model:    parsed.Model,
		YearFrom: parsed.YearFrom,
		YearTo:   parsed.YearTo,
	}, nil
}

func parseSingleVehicleWithRegex(vehicle string) *LLMParsedApp {
	// Try "Brand Model Year-Year" format
	yearRange := regexp.MustCompile(`(\d{4})-(\d{4})$`)
	match := yearRange.FindStringSubmatch(vehicle)
	if match != nil {
		yearFrom := match[1]
		yearTo := match[2]
		vehicle = yearRange.ReplaceAllString(vehicle, "")
		vehicle = strings.TrimSpace(vehicle)

		parts := strings.Fields(vehicle)
		if len(parts) >= 1 {
			make := parts[0]
			model := ""
			if len(parts) > 1 {
				model = strings.Join(parts[1:], " ")
			}
			return &LLMParsedApp{Make: make, Model: model, YearFrom: yearFrom, YearTo: yearTo}
		}
	}

	// Try "Brand Model Year" format
	yearSingle := regexp.MustCompile(`(\d{4})$`)
	match = yearSingle.FindStringSubmatch(vehicle)
	if match != nil {
		yearFrom := match[1]
		vehicle = yearSingle.ReplaceAllString(vehicle, "")
		vehicle = strings.TrimSpace(vehicle)

		parts := strings.Fields(vehicle)
		if len(parts) >= 1 {
			make := parts[0]
			model := ""
			if len(parts) > 1 {
				model = strings.Join(parts[1:], " ")
			}
			return &LLMParsedApp{Make: make, Model: model, YearFrom: yearFrom, YearTo: yearFrom}
		}
	}

	return nil
}

func processPrestone(fp, outDir string) error {
	f, err := excelize.OpenFile(fp)
	if err != nil {
		return err
	}
	defer f.Close()

	sheets := f.GetSheetList()
	fmt.Println("  Sheets:", sheets)

	file, err := os.Create(filepath.Join(outDir, "prestone.csv"))
	if err != nil {
		return err
	}
	defer file.Close()

	writer := csv.NewWriter(file)
	writer.Comma = '|'
	writer.Write([]string{"categoria", "item", "descripcion", "aplicaciones", "tamano", "piezas_por_caja", "precio", "codigo_sat"})
	writer.Flush()

	totalRows := 0

	for _, sheetName := range sheets {
		rows, err := f.GetRows(sheetName)
		if err != nil {
			log.Printf("Error reading sheet %s: %v", sheetName, err)
			continue
		}

		headerMap := make(map[string]int)
		headerRowIdx := -1
		sheetRows := 0

		for rowIdx, row := range rows {
			if headerRowIdx == -1 && len(row) > 0 {
				cleanedRow := make([]string, len(row))
				for i, val := range row {
					cleanedRow[i] = strings.ToLower(cleanStr(val))
				}

				expectedHeaders := []string{"item", "no de parte", "descripción", "descripcion", "aplicaciones", "tamaño", "piezas", "precio", "codigo sat"}
				hasExpectedHeader := false
				for _, expected := range expectedHeaders {
					for _, val := range cleanedRow {
						if val == expected {
							hasExpectedHeader = true
							break
						}
					}
					if hasExpectedHeader {
						break
					}
				}

				if hasExpectedHeader {
					headerRowIdx = rowIdx
					for colIdx, val := range row {
						headerMap[strings.ToLower(cleanStr(val))] = colIdx
					}
					fmt.Printf("  Sheet: %s, Header row: %d, Headers: %v\n", sheetName, headerRowIdx, headerMap)
					continue
				}
			}

			if headerRowIdx != -1 && rowIdx > headerRowIdx && len(row) > 0 {
				col0 := cleanStr(row[0])
				if col0 == "" {
					continue
				}

				item := ""
				descripcion := ""
				aplicaciones := ""
				tamano := ""
				piezas := ""
				precio := ""
				codigoSat := ""

				for colIdx, val := range row {
					colName := ""
					for k, v := range headerMap {
						if v == colIdx {
							colName = k
							break
						}
					}

					cleanVal := cleanStr(val)
					switch colName {
					case "item", "no de parte":
						item = cleanVal
					case "descripción", "descripcion":
						descripcion = cleanVal
					case "aplicaciones":
						aplicaciones = cleanVal
					case "tamaño", "tamano":
						tamano = cleanVal
					case "piezas \npor caja", "piezas por caja", "piezas":
						piezas = cleanVal
					case "precio de lista s/iva 18 de mayo 2026", "precio":
						precio = cleanVal
					case "codigo sat":
						codigoSat = cleanVal
					}
				}

				if item == "" {
					continue
				}

				writer.Write([]string{
					sheetName,
					item,
					descripcion,
					aplicaciones,
					tamano,
					piezas,
					precio,
					codigoSat,
				})
				sheetRows++
				totalRows++
			}
		}

		fmt.Printf("  Sheet: %s, extracted %d rows\n", sheetName, sheetRows)
	}

	writer.Flush()
	fmt.Printf("Exported: prestone.csv (%d total rows)\n", totalRows)

	return nil
}