package xlstranslator

import (
	"encoding/csv"
	"fmt"
	"os"
	"path/filepath"
	"regexp"
	"sort"
	"strconv"
	"strings"
	"unicode"

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

// Config holds input/output directories.
type Config struct {
	InputDir  string
	OutputDir string
}

// Converter orchestrates XLSX→CSV conversion with error logging.
type Converter struct {
	cfg    Config
	logger *ErrorLogger
}

func NewConverter(cfg Config) *Converter {
	return &Converter{
		cfg:    cfg,
		logger: NewErrorLogger(),
	}
}

// ------------------------------------------------------------------
// Public API
// ------------------------------------------------------------------

// converterFileNames maps converter names to their canonical source files
// relative to InputDir (the legacy docs/database layout used by the CLI).
var converterFileNames = map[string]string{
	"amort":     "grc_amortiguador_pesado.xlsx",
	"frenos":    "grc_frenos_ligero.xlsx",
	"ag":        "AG.xlsx",
	"monroe":    filepath.Join("latest", "monroe.xlsx"),
	"trw_sachs": "trw_sachs.xlsx",
	"cano":      "cano.xlsx",
	"grobb":     filepath.Join("latest", "GROBB_shocks.xlsx"),
	"fritec":    filepath.Join("latest", "FRITEC_DISCOS.xlsx"),
	"wagner":    filepath.Join("latest", "Wagner_Brake.xlsx"),
	"brembo":    "Brembo.xml",
	"prestone":  "PRESTONE.xlsx",
	"pagid":     filepath.Join("temp", "PAGID_CARGA_20AGOSTO.xlsx"),
}

// converterOutputs maps converter names to the CSV files they write into
// OutputDir, so callers (e.g. the admin panel) can report what was produced.
var converterOutputs = map[string][]string{
	"amort":     {"amort-deseable.csv"},
	"frenos":    {"frenos-deseable.csv"},
	"ag":        {"AG_shock.csv", "AG_strut.csv", "AG_leg.csv"},
	"monroe":    {"monroe_ld.csv", "monroe_hd.csv"},
	"trw_sachs": {"trw_sachs.csv"},
	"cano":      {"cano_disk.csv"},
	"grobb":     {"grobb_shocks.csv"},
	"fritec":    {"fritec_discs.csv"},
	"wagner":    {"wagner_brake.csv"},
	"brembo":    {"brembo.csv"},
	"prestone":  {"prestone.csv"},
	"pagid":     {"pagid_brakes.csv", "pagid_discs.csv", "pagid_sensors.csv"},
}

func ConverterOutputs(name string) []string {
	return converterOutputs[name]
}

// SourceFileName returns the canonical source filename (relative to InputDir)
// expected by the converter for a given converter name.
func SourceFileName(name string) (string, bool) {
	rel, ok := converterFileNames[name]
	return rel, ok
}

func (c *Converter) ConvertAll() error {
	if err := os.MkdirAll(c.cfg.OutputDir, 0755); err != nil {
		return err
	}

	for name, rel := range converterFileNames {
		fp := filepath.Join(c.cfg.InputDir, rel)
		fmt.Printf("Processing: %s\n", filepath.Base(fp))
		if err := c.run(name, fp); err != nil {
			fmt.Printf("  Error: %v\n", err)
		}
	}

	return nil
}

// ConvertSingle allows converting one file by name.
func (c *Converter) ConvertSingle(name string) error {
	rel, ok := converterFileNames[name]
	if !ok {
		return fmt.Errorf("unknown converter name: %s", name)
	}
	return c.run(name, filepath.Join(c.cfg.InputDir, rel))
}

// ConvertFile converts an explicit source file (e.g. an admin-panel upload)
// with the converter registered for name, whatever the file is named on disk.
func (c *Converter) ConvertFile(name, fp string) error {
	if _, ok := converterFileNames[name]; !ok {
		return fmt.Errorf("unknown converter name: %s", name)
	}
	return c.run(name, fp)
}

func (c *Converter) run(name, fp string) error {
	c.logger = NewErrorLogger() // fresh logger per file
	err := c.convertByType(name, fp)
	if err != nil {
		c.logger.Add(fp, "", 0, "", fmt.Sprintf("top-level error: %v", err))
	}
	_ = c.logger.WriteToFile(fp)
	return err
}

func (c *Converter) convertByType(name, fp string) error {
	switch name {
	case "amort":
		return c.processAmortiguadores(fp)
	case "frenos":
		return c.processFrenos(fp)
	case "ag":
		return c.processAG(fp)
	case "monroe":
		return c.processMonroe(fp)
	case "trw_sachs":
		return c.processTrwSachs(fp)
	case "cano":
		return c.processCano(fp)
	case "wagner":
		return c.processWagner(fp)
	case "grobb":
		return c.processGrobb(fp)
	case "fritec":
		return c.processFritec(fp)
	case "brembo":
		return c.processBremboXML(fp)
	case "prestone":
		return c.processPrestone(fp)
	case "pagid":
		return c.processPagid(fp)
	default:
		return fmt.Errorf("unknown converter name: %s", name)
	}
}

// sheetNotFoundErr reports a malformed source workbook: the expected sheet is
// missing, and the sheets that do exist are listed so the user can see why.
func sheetNotFoundErr(f *excelize.File, expected string) error {
	return fmt.Errorf("archivo malformado: la hoja %q no existe; hojas encontradas: %s", expected, strings.Join(f.GetSheetList(), ", "))
}

// ------------------------------------------------------------------
// Shared helpers
// ------------------------------------------------------------------

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

func (c *Converter) logError(sourceFile, sheet string, rowNum int, rawData, msg string) {
	c.logger.Add(sourceFile, sheet, rowNum, rawData, msg)
}

// ------------------------------------------------------------------
// amort-deseable (GRC shocks - Pesado)
// ------------------------------------------------------------------

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

	rows, err := f.GetRows("GRC AMR pesado MIP 1707")
	if err != nil {
		return sheetNotFoundErr(f, "GRC AMR pesado MIP 1707")
	}

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

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

	outHeaders := []string{
		"marca", "numeroDeParte", "modelo", "ano", "notasAplicacion",
		"producto", "longitudExtendida", "longitudComprimida", "carrera",
		"montajeSuperior", "montajeInferior", "posicion", "imagen",
		"atributosVehiculo", "serie", "tier", "cantidad", "accesorios",
	}
	writer.Write(outHeaders)

	val := func(row []string, idx int) string {
		if idx >= len(row) {
			return ""
		}
		return cleanStr(row[idx])
	}

	isEmpty := func(s string) bool {
		s = strings.ToLower(s)
		return s == "" || s == "n/a" || s == "no req." || s == "s/n" || s == "na"
	}

	rowCount := 0
	for i, row := range rows {
		if i == 0 {
			// source row 0 contains Column1..ColumnN - skip it
			continue
		}
		if len(row) < 3 {
			continue
		}

		marca := val(row, 1)
		numeroDeParte := val(row, 2)
		modelo := val(row, 3)
		ano := val(row, 4)
		notasAplicacion := val(row, 6)
		producto := val(row, 8)
		longitudExtendida := val(row, 9)
		longitudComprimida := val(row, 10)
		carrera := val(row, 11)
		montajeSuperior := val(row, 13)
		montajeInferior := val(row, 14)
		posicion := val(row, 16)
		imagen := val(row, 17)

		var attrs []string
		if v := val(row, 5); !isEmpty(v) {
			attrs = append(attrs, fmt.Sprintf("Modelo base: %s", v))
		}
		if v := val(row, 7); !isEmpty(v) {
			attrs = append(attrs, fmt.Sprintf("Categoria: %s", v))
		}
		if v := val(row, 12); !isEmpty(v) {
			attrs = append(attrs, fmt.Sprintf("Medida: %s", v))
		}
		if v := val(row, 15); !isEmpty(v) {
			attrs = append(attrs, fmt.Sprintf("Referencia: %s", v))
		}
		atributosVehiculo := strings.Join(attrs, " | ")

		writer.Write([]string{
			marca, numeroDeParte, modelo, ano, notasAplicacion,
			producto, longitudExtendida, longitudComprimida, carrera,
			montajeSuperior, montajeInferior, posicion, imagen,
			atributosVehiculo, "", "", "", "",
		})

		rowCount++
		if rowCount > 0 && rowCount%10000 == 0 {
			writer.Flush()
			fmt.Printf("  Amort: %d rows written...\n", rowCount)
		}
	}

	writer.Flush()
	fmt.Printf("Exported: amort-deseable.csv (%d rows)\n", rowCount)
	return nil
}

// ------------------------------------------------------------------
// frenos-deseable (GRC brakes - Ligero)
// ------------------------------------------------------------------

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

	rows, err := f.GetRows("Frenos GRC MIP 1707")
	if err != nil {
		return sheetNotFoundErr(f, "Frenos GRC MIP 1707")
	}

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

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

	outHeaders := []string{
		"marca", "modelo", "ano", "numeroDeParte", "producto",
		"serie", "tier", "cantidad", "posicion", "referencia fmsi",
		"incluye herraje", "imagen", "atributosDelVehiculo",
	}
	writer.Write(outHeaders)

	val := func(row []string, idx int) string {
		if idx >= len(row) {
			return ""
		}
		return cleanStr(row[idx])
	}

	isEmpty := func(s string) bool {
		s = strings.ToLower(s)
		return s == "" || s == "n/a" || s == "no req." || s == "s/n" || s == "na"
	}

	rowCount := 0
	for i, row := range rows {
		if i == 0 {
			// source row 0 contains Column1..ColumnN - skip it
			continue
		}
		if len(row) < 4 {
			continue
		}

		marca := val(row, 1)
		modelo := val(row, 2)
		ano := val(row, 3)
		numeroDeParte := val(row, 4)
		producto := val(row, 5)
		serie := val(row, 6)
		tier := val(row, 7)
		cantidad := val(row, 8)
		posicion := val(row, 9)
		fmsi := val(row, 10)
		incluyeHerraje := val(row, 12)
		imagen := val(row, 13)

		var attrs []string
		if v := val(row, 11); !isEmpty(v) {
			attrs = append(attrs, fmt.Sprintf("FMSI alt: %s", v))
		}
		if v := val(row, 14); !isEmpty(v) {
			attrs = append(attrs, fmt.Sprintf("Calificador: %s", v))
		}
		if v := val(row, 16); !isEmpty(v) {
			attrs = append(attrs, fmt.Sprintf("Descripcion: %s", v))
		}
		atributosDelVehiculo := strings.Join(attrs, " | ")

		writer.Write([]string{
			marca, modelo, ano, numeroDeParte, producto,
			serie, tier, cantidad, posicion, fmsi,
			incluyeHerraje, imagen, atributosDelVehiculo,
		})

		rowCount++
		if rowCount > 0 && rowCount%10000 == 0 {
			writer.Flush()
			fmt.Printf("  Frenos: %d rows written...\n", rowCount)
		}
	}

	writer.Flush()
	fmt.Printf("Exported: frenos-deseable.csv (%d rows)\n", rowCount)
	return nil
}

// ------------------------------------------------------------------
// aces
// ------------------------------------------------------------------

func (c *Converter) processAces(fp string) error {
	outDir := c.cfg.OutputDir
	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 {
			c.logError(fp, sheet, 0, "", fmt.Sprintf("failed to read sheet: %v", err))
			continue
		}

		outFile := filepath.Join(outDir, strings.ReplaceAll(sheet, " ", "_")+".csv")
		file, err := os.Create(outFile)
		if err != nil {
			c.logError(fp, sheet, 0, "", fmt.Sprintf("failed to create output file: %v", err))
			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
}

// ------------------------------------------------------------------
// AG
// ------------------------------------------------------------------

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

	sheetsConfig := []struct {
		sheetName     string
		producto      string
		outputFile    string
		delimiter     rune
		excelHeaders  []string
		outputHeaders []string
	}{
		{
			sheetName:     "Amortiguadores AG Shock",
			producto:      "Amortiguador",
			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", "producto"},
		},
		{
			sheetName:     "Bases Amortiguador AG Strut",
			producto:      "Base de amortiguador | 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", "producto"},
		},
		{
			sheetName:     "Piernas AG Proshock",
			producto:      "Pierna de suspensión 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", "producto"},
		},
	}

	for _, cfg := range sheetsConfig {
		if err := c.processAGSheet(f, fp, cfg.sheetName, cfg.producto, cfg.outputFile, cfg.delimiter, cfg.excelHeaders, cfg.outputHeaders); err != nil {
			fmt.Printf("  Error processing sheet %s: %v\n", cfg.sheetName, err)
			c.logError(fp, cfg.sheetName, 0, "", fmt.Sprintf("sheet processing error: %v", err))
		}
	}

	return nil
}

func (c *Converter) processAGSheet(f *excelize.File, sourceFile, sheetName, producto string, outputFile string, delimiter rune, excelHeaders, outputHeaders []string) error {
	rows, err := f.GetRows(sheetName)
	if err != nil {
		return sheetNotFoundErr(f, sheetName)
	}

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

	file, err := os.Create(filepath.Join(c.cfg.OutputDir, 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
				}
			}
			values = append(values, producto)
			writer.Write(values)
		}
	}

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

	return nil
}

// ------------------------------------------------------------------
// Cano
// ------------------------------------------------------------------

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

	rows, err := f.GetRows("Hoja1")
	if err != nil {
		return sheetNotFoundErr(f, "Hoja1")
	}

	if len(rows) < 4 {
		return fmt.Errorf("archivo malformado: not enough rows in sheet (expected header at row 3, data from row 4)")
	}

	headerMap := make(map[string]int)
	for colIdx, val := range rows[2] {
		headerMap[strings.ToLower(cleanStr(val))] = colIdx
	}
	fmt.Printf("  Cano headers: %v\n", headerMap)

	getCol := func(row []string, key string) string {
		idx, ok := headerMap[strings.ToLower(key)]
		if !ok || idx >= len(row) {
			return ""
		}
		return cleanStr(row[idx])
	}

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

	writer := csv.NewWriter(file)
	writer.Comma = '|'
	writer.Write([]string{
		"partNumber", "mipPartNumber", "product", "description", "type",
		"position", "minThikness", "ogDiameter", "maxDiameter", "height",
		"piDiameter", "materials", "lugNuts", "vehicleAttrs", "Categoría",
		"make", "model", "year", "yearRange",
	})
	writer.Flush()

	rowCount := 0
	for rowIdx, row := range rows[3:] {
		codigo := getCol(row, "Código")
		codigoMip := getCol(row, "Código MIP")
		nombre := getCol(row, "Nombre")
		descripcion := getCol(row, "Descripción corta")
		tipo := getCol(row, "Tipo")
		categoria := getCol(row, "Categoría")
		posicion := getCol(row, "Posición")
		minEsp := getCol(row, "Mínimo Espesor")
		diamOrig := getCol(row, "Diametro Original")
		maxDiam := getCol(row, "Máximo Diametro")
		altura := getCol(row, "Altura")
		diamPiloto := getCol(row, "Diametro Piloto")
		materiales := getCol(row, "Materiales")
		birlos := getCol(row, "Birlos")
		aplicaciones := getCol(row, "Aplicaciones")
		pesoAprox := getCol(row, "Peso Aprox")
		garantia := getCol(row, "Garantía")
		articulo := getCol(row, "Artículo")

		if codigo == "" || aplicaciones == "" {
			c.logError(fp, "Hoja1", rowIdx+4, strings.Join(row, "|"), "missing código or aplicaciones")
			continue
		}

		if categoria == "" {
			categoria = "Ligero"
		}

		apps := strings.Split(aplicaciones, "|")
		for _, app := range apps {
			app = strings.TrimSpace(app)
			if app == "" {
				continue
			}

			make, model, year, yearRange := parseCanoApplication(app)
			if make == "" || model == "" {
				c.logError(fp, "Hoja1", rowIdx+4, app, "unable to parse make/model from application")
				continue
			}

			var attrs []string
			attrs = append(attrs, fmt.Sprintf("Aplicaciones: %s", app))
			if categoria != "" {
				attrs = append(attrs, fmt.Sprintf("Categoría: %s", categoria))
			}
			if pesoAprox != "" {
				attrs = append(attrs, fmt.Sprintf("Peso Aprox: %s", pesoAprox))
			}
			if garantia != "" {
				attrs = append(attrs, fmt.Sprintf("Garantía: %s", garantia))
			}
			if articulo != "" {
				attrs = append(attrs, fmt.Sprintf("Artículo: %s", articulo))
			}
			vehicleAttrs := strings.Join(attrs, "; ")

			writer.Write([]string{
				codigo,
				codigoMip,
				nombre,
				descripcion,
				tipo,
				posicion,
				minEsp,
				diamOrig,
				maxDiam,
				altura,
				diamPiloto,
				materiales,
				birlos,
				vehicleAttrs,
				categoria,
				make,
				model,
				year,
				yearRange,
			})
			rowCount++
		}
	}

	writer.Flush()
	fmt.Printf("Exported: cano_disk.csv (%d rows)\n", rowCount)
	return nil
}

func parseCanoApplication(app string) (make, model, year, yearRange string) {
	app = strings.TrimLeft(app, ",.\\-;:/ \t\r\n")
	yearRangeRe := regexp.MustCompile(`(\d{4})\s*-\s*(\d{2,4})`)
	yearSingleRe := regexp.MustCompile(`\b(\d{4})\b`)

	if m := yearRangeRe.FindStringSubmatch(app); m != nil {
		yearRange = m[1] + "-" + normalizeCanoYearEnd(m[1], m[2])
		app = yearRangeRe.ReplaceAllString(app, "")
	} else if m := yearSingleRe.FindStringSubmatch(app); m != nil {
		year = m[1]
		app = yearSingleRe.ReplaceAllString(app, "")
	}

	app = strings.TrimSpace(app)
	if app == "" {
		return "MISSING", "MISSING", year, yearRange
	}

	parts := strings.Fields(app)
	make = parts[0]
	if len(parts) > 1 {
		model = strings.Join(parts[1:], " ")
	} else {
		model = "MISSING"
	}

	remYearRe := regexp.MustCompile(`\s*-\s*\d{2,4}\s*$`)
	model = strings.TrimSpace(remYearRe.ReplaceAllString(model, ""))

	return
}

// normalizeCanoYearEnd expands a 2-digit year end into the same century as the
// start year, adding 100 years if the simple expansion rolls back before the
// start year. This matches the behaviour in the csv importer so Cano CSVs emit
// consistent 4-digit year ranges such as "2018-2020".
func normalizeCanoYearEnd(yearFrom, yearTo string) string {
	if len(yearTo) == 4 {
		return yearTo
	}
	if len(yearTo) != 2 || len(yearFrom) != 4 {
		return yearTo
	}

	from, err := strconv.Atoi(yearFrom)
	if err != nil {
		return yearTo
	}
	to, err := strconv.Atoi(yearTo)
	if err != nil {
		return yearTo
	}

	century := (from / 100) * 100
	fullYear := century + to
	if fullYear < from {
		fullYear += 100
	}
	return strconv.Itoa(fullYear)
}

// normalizeAplicacion prepares a free-text application string for parsing:
// semicolons are treated as application separators (merged into commas) and
// runs of whitespace are collapsed so the importer's comma-based splitter and
// make/model extractor behave predictably.
// digitHyphenSpaceRe collapses whitespace around hyphens that sit between two
// digits: "1995 -2010" -> "1995-2010", "1995  -2010" -> "1995-2010".
var digitHyphenSpaceRe = regexp.MustCompile(`(\d)\s*-\s*(\d)`)

// threeDigitYearRangeRe expands 3-digit year shorthands in a range, e.g.
// "203-2005" -> "2003-2005" (PAGID DISCO sheet typo). The leading
// ([^0-9]|^) boundary ensures we don't match a 3-digit run that is actually the
// tail of a 4-digit year (e.g. "994-1987" inside "1994-1987").
var threeDigitYearRangeRe = regexp.MustCompile(`([^0-9]|^)(\d{3})-(\d{4})`)

func normalizeAplicacion(a string) string {
	// Normalize en/em-dashes (e.g. "1997–2002") to ASCII hyphen so year
	// ranges parse correctly.
	a = strings.ReplaceAll(a, "–", "-")
	a = strings.ReplaceAll(a, "—", "-")
	// Collapse spaces around hyphens between digits ("1995 -2010" -> "1995-2010").
	a = digitHyphenSpaceRe.ReplaceAllString(a, "$1-$2")
	// Expand 3-digit year shorthands in ranges ("203-2005" -> "2003-2005").
	a = threeDigitYearRangeRe.ReplaceAllStringFunc(a, func(m string) string {
		if sub := threeDigitYearRangeRe.FindStringSubmatch(m); sub != nil {
			return sub[1] + "20" + sub[2][1:] + "-" + sub[3]
		}
		return m
	})
	// Source typo "Chirey" -> "Chery".
	a = strings.ReplaceAll(a, "Chirey", "Chery")
	a = strings.ReplaceAll(a, ";", ",")
	a = strings.ReplaceAll(a, "|", "")
	a = regexp.MustCompile(`\s+`).ReplaceAllString(a, " ")
	return strings.TrimSpace(a)
}

// ------------------------------------------------------------------
// GROBB shocks (simple inventory export)
// ------------------------------------------------------------------

func (c *Converter) processGrobb(fp string) error {
	outDir := c.cfg.OutputDir
	f, err := excelize.OpenFile(fp)
	if err != nil {
		return err
	}
	defer f.Close()

	sheet := "Inventario"
	rows, err := f.GetRows(sheet)
	if err != nil {
		return sheetNotFoundErr(f, sheet)
	}

	if len(rows) < 2 {
		return fmt.Errorf("archivo malformado: not enough rows in sheet %s", sheet)
	}

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

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

	rowCount := 0
	for rowIdx, row := range rows {
		if rowIdx == 0 {
			continue // header
		}
		get := func(idx int) string {
			if idx >= len(row) {
				return ""
			}
			return cleanStr(row[idx])
		}
		partNumber := get(0)
		position := get(2)
		aplicacion := normalizeAplicacion(get(3))
		if partNumber == "" || aplicacion == "" {
			c.logError(fp, sheet, rowIdx+1, strings.Join(row, "|"), "missing código or aplicación")
			continue
		}
		writer.Write([]string{partNumber, position, aplicacion})
		rowCount++
	}

	writer.Flush()
	fmt.Printf("Exported: grobb_shocks.csv (%d rows)\n", rowCount)
	return nil
}

// ------------------------------------------------------------------
// FRITEC discs (simple inventory export)
// ------------------------------------------------------------------

func (c *Converter) processFritec(fp string) error {
	outDir := c.cfg.OutputDir
	f, err := excelize.OpenFile(fp)
	if err != nil {
		return err
	}
	defer f.Close()

	sheet := "Relación existencia"
	rows, err := f.GetRows(sheet)
	if err != nil {
		return sheetNotFoundErr(f, sheet)
	}

	if len(rows) < 2 {
		return fmt.Errorf("archivo malformado: not enough rows in sheet %s", sheet)
	}

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

	writer := csv.NewWriter(file)
	writer.Comma = '|'
	writer.Write([]string{"brand", "partNumber", "position", "type", "aplicacion"})
	writer.Flush()

	rowCount := 0
	for rowIdx, row := range rows {
		if rowIdx == 0 {
			continue // header
		}
		get := func(idx int) string {
			if idx >= len(row) {
				return ""
			}
			return cleanStr(row[idx])
		}
		brand := get(1)     // marca
		partNumber := get(0) // Código
		prodType := get(2)  // Tipo (Disco)
		position := get(5)  // Posicion
		aplicacion := normalizeAplicacion(get(3)) // aplicación
		// existencia (get(4)) intentionally ignored — stock is managed via admin UI
		if brand == "" || partNumber == "" || aplicacion == "" {
			c.logError(fp, sheet, rowIdx+1, strings.Join(row, "|"), "missing marca/código/aplicación")
			continue
		}
		writer.Write([]string{brand, partNumber, position, prodType, aplicacion})
		rowCount++
	}

	writer.Flush()
	fmt.Printf("Exported: fritec_discs.csv (%d rows)\n", rowCount)
	return nil
}

// ------------------------------------------------------------------
// PAGID (Hella-Pagid) — brake pads/shoes, discs and wear sensors
// ------------------------------------------------------------------

func (c *Converter) processPagid(fp string) error {
	outDir := c.cfg.OutputDir
	f, err := excelize.OpenFile(fp)
	if err != nil {
		return err
	}
	defer f.Close()

	brand := "Hella-Pagid"

	// --- Sheet 1: balata y zapata -> brake_brand_specs ---
	brakeFile, err := os.Create(filepath.Join(outDir, "pagid_brakes.csv"))
	if err != nil {
		return err
	}
	defer brakeFile.Close()
	brakeW := csv.NewWriter(brakeFile)
	brakeW.Comma = '|'
	brakeW.Write([]string{"brand", "partNumber", "product", "position", "fmsi", "aplicacion"})

	balata, err := f.GetRows("balata y zapata")
	if err != nil {
		return sheetNotFoundErr(f, "balata y zapata")
	}
	brakeCount := 0
	for rowIdx, row := range balata {
		if rowIdx == 0 {
			continue // header
		}
		get := func(idx int) string {
			if idx >= len(row) {
				return ""
			}
			return cleanStr(row[idx])
		}
		partNumber := get(0)
		product := normalizePagidBrakeProduct(get(2))
		position := get(3)
		fmsi := get(4)
		aplicacion := normalizeAplicacion(get(5))
		if partNumber == "" || aplicacion == "" {
			if partNumber != "" {
				c.logError(fp, "balata y zapata", rowIdx+1, strings.Join(row, "|"), "missing aplicación")
			}
			continue
		}
		brakeW.Write([]string{brand, partNumber, product, position, fmsi, aplicacion})
		brakeCount++
	}
	brakeW.Flush()
	fmt.Printf("Exported: pagid_brakes.csv (%d rows)\n", brakeCount)

	// --- Sheet 2: DISCO -> disk_brand_specs ---
	discFile, err := os.Create(filepath.Join(outDir, "pagid_discs.csv"))
	if err != nil {
		return err
	}
	defer discFile.Close()
	discW := csv.NewWriter(discFile)
	discW.Comma = '|'
	discW.Write([]string{"brand", "partNumber", "type", "position", "aplicacion"})

	disco, err := f.GetRows("DISCO")
	if err != nil {
		return sheetNotFoundErr(f, "DISCO")
	}
	discCount := 0
	for rowIdx, row := range disco {
		if rowIdx < 2 { // row 0 empty, row 1 header
			continue
		}
		get := func(idx int) string {
			if idx >= len(row) {
				return ""
			}
			return cleanStr(row[idx])
		}
		partNumber := get(0)
		prodType := get(3)
		position := get(4)
		aplicacion := normalizeAplicacion(get(5))
		if partNumber == "" || aplicacion == "" {
			if partNumber != "" {
				c.logError(fp, "DISCO", rowIdx+1, strings.Join(row, "|"), "missing aplicación")
			}
			continue
		}
		discW.Write([]string{brand, partNumber, prodType, position, aplicacion})
		discCount++
	}
	discW.Flush()
	fmt.Printf("Exported: pagid_discs.csv (%d rows)\n", discCount)

	// --- Sheet 3: Relación existencia -> brake_brand_specs (wear sensors) ---
	sensorFile, err := os.Create(filepath.Join(outDir, "pagid_sensors.csv"))
	if err != nil {
		return err
	}
	defer sensorFile.Close()
	sensorW := csv.NewWriter(sensorFile)
	sensorW.Comma = '|'
	sensorW.Write([]string{"brand", "partNumber", "make", "position", "product", "size"})

	sensorRows, err := f.GetRows("Relación existencia")
	if err != nil {
		return sheetNotFoundErr(f, "Relación existencia")
	}
	sensorCount := 0
	for rowIdx, row := range sensorRows {
		if rowIdx == 0 {
			continue // header
		}
		get := func(idx int) string {
			if idx >= len(row) {
				return ""
			}
			return cleanStr(row[idx])
		}
		partNumber := get(0)
		desc := get(1)
		if partNumber == "" || desc == "" {
			continue
		}
		mk, pos, product, size, ok := parsePagidSensor(desc)
		if !ok {
			c.logError(fp, "Relación existencia", rowIdx+1, desc, "unparsable sensor description")
			continue
		}
		sensorW.Write([]string{brand, partNumber, mk, pos, product, size})
		sensorCount++
	}
	sensorW.Flush()
	fmt.Printf("Exported: pagid_sensors.csv (%d rows)\n", sensorCount)

	return nil
}

func normalizePagidBrakeProduct(p string) string {
	switch {
	case strings.Contains(p, "Tambor"):
		return "Zapata"
	case strings.Contains(p, "Freno Disco"):
		return "Pastilla de Freno"
	default:
		return p
	}
}

func parsePagidSensor(desc string) (make, position, product, size string, ok bool) {
	parts := strings.Split(desc, ",")
	if len(parts) < 4 {
		return "", "", "", "", false
	}
	makeRaw := strings.TrimSpace(parts[1])
	posRaw := strings.TrimSpace(parts[2])
	size = strings.TrimSpace(parts[3])
	product = "Sensor de Desgaste"
	if strings.EqualFold(makeRaw, "Vw") {
		makeRaw = "Volkswagen"
	}
	switch strings.ToUpper(posRaw) {
	case "DELANTERO", "DELANETRO":
		posRaw = "Del"
	case "TRASERO":
		posRaw = "Tras"
	case "DELANTERO/TRASERO":
		posRaw = "Del / Tras"
	}
	return makeRaw, posRaw, product, size, true
}

// ------------------------------------------------------------------
// Prestone
// ------------------------------------------------------------------

func (c *Converter) processPrestone(fp string) error {
	outDir := c.cfg.OutputDir
	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 {
			c.logError(fp, sheetName, 0, "", fmt.Sprintf("failed to read sheet: %v", 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 == "" {
					c.logError(fp, sheetName, rowIdx+1, strings.Join(row, "|"), "missing item (no de parte)")
					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
}

// ------------------------------------------------------------------
// Monroe  (placeholder for LD/HD split — Batch 2)
// ------------------------------------------------------------------

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

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

	// Step 1: read Packages sheet to build PartNumber -> LD/HD map.
	// If the Packages sheet is missing the LD - DH column (as in newer exports),
	// default every part number to LD.
	ldhdMap := make(map[string]string)
	defaultLD := false
	pkgRows, err := f.GetRows("Packages")
	if err != nil {
		fmt.Printf("  Warning: Packages sheet not found: %v\n", err)
		defaultLD = true
	} else if len(pkgRows) == 0 || len(pkgRows[0]) == 0 {
		fmt.Printf("  Warning: Packages sheet is empty; defaulting all to LD\n")
		defaultLD = true
	} else {
		ldhdIdx := -1
		for i, h := range pkgRows[0] {
			if strings.TrimSpace(h) == "LD - DH" {
				ldhdIdx = i
				break
			}
		}
		if ldhdIdx < 0 {
			fmt.Printf("  Warning: LD - DH column not found in Packages sheet; defaulting all to LD\n")
			defaultLD = true
		} else {
			for rowIdx, row := range pkgRows[1:] {
				if len(row) > ldhdIdx {
					pn := strings.TrimSpace(row[0])
					val := strings.TrimSpace(row[ldhdIdx])
					if pn != "" {
						ldhdMap[pn] = val
					}
				}
				_ = rowIdx
			}
		}
	}

	// Step 2: read Applications (one row per vehicle fitment, NOT deduplicated)
	applications, err := c.processMonroeApplications(f)
	if err != nil {
		fmt.Printf("Error processing Applications: %v\n", err)
	}

	// Step 3: read Descriptions for ES product/series content. We intentionally
	// do not use the Items sheet DefaultMfrLabel any more; it is always English
	// and conflicts with the Spanish product labels shown in result/product views.
	invoiceDesc, shortDesc, err := c.processMonroeDescriptions(f)
	if err != nil {
		fmt.Printf("Error processing Descriptions: %v\n", err)
	}

	merged := c.mergeMonroeData(applications, invoiceDesc, shortDesc)

	// Step 3: split into LD and HD files
	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", "short_content", "serie"})
	ldWriter.Flush()

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

	ldCount, hdCount, skipCount := 0, 0, 0
	for _, row := range merged {
		if len(row) == 0 {
			continue
		}
		pn := strings.TrimSpace(row[0])

		// monroeok.xlsx provides LD/HD per fitment row inside the Applications
		// sheet (stored at row[12]). Prefer it; fall back to the Packages sheet
		// map for legacy MonroeLDHD.xlsx files.
		ldhdVal := strings.ToUpper(strings.TrimSpace(row[12]))
		if ldhdVal == "" {
			if defaultLD {
				ldhdVal = "LD"
			} else {
				ldhdVal = strings.ToUpper(strings.TrimSpace(ldhdMap[pn]))
			}
		}

		// Write only the first 12 columns (row[12] is internal split metadata).
		out := row[:12]
		switch ldhdVal {
		case "LD":
			ldWriter.Write(out)
			ldCount++
		case "HD", "DH":
			hdWriter.Write(out)
			hdCount++
		default:
			// Unknown / #N/A value: default to LD so the part is not lost.
			fmt.Printf("  Unknown LD-HD value '%s' for PartNumber %s; defaulting to LD\n", ldhdVal, pn)
			ldWriter.Write(out)
			ldCount++
		}
	}

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

	return nil
}

// ------------------------------------------------------------------
// TRW / SACHS ligero shocks
// ------------------------------------------------------------------

func (c *Converter) processTrwSachs(fp string) error {
	outDir := c.cfg.OutputDir
	f, err := excelize.OpenFile(fp)
	if err != nil {
		return err
	}
	defer f.Close()

	sheets := f.GetSheetList()
	if len(sheets) == 0 {
		return fmt.Errorf("archivo malformado: no sheets found in TRW/SACHS file")
	}

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

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

	writer := csv.NewWriter(outFile)
	writer.Comma = '|'
	writer.Write([]string{"partnumber", "aplicacion", "pos", "brand"})

	written := 0
	for i, row := range rows {
		if i == 0 {
			// header row
			continue
		}
		if len(row) < 4 {
			continue
		}

		// Excel data starts at column B, so indices depend on whether leading
		// empty columns were read. Handle both B=0 and B=1 positions.
		var partnumber, aplicacion, pos, brand string
		if strings.TrimSpace(row[0]) == "NUM PARTE" {
			continue
		}
		if len(row) >= 5 && strings.TrimSpace(row[1]) == "NUM PARTE" {
			continue
		}

		if len(row) >= 5 {
			// columns A-E; B=1 is NUM PARTE
			partnumber = strings.TrimSpace(row[1])
			aplicacion = strings.TrimSpace(row[2])
			pos = strings.TrimSpace(row[3])
			brand = strings.TrimSpace(row[4])
		} else {
			// columns B-E; B=0
			partnumber = strings.TrimSpace(row[0])
			aplicacion = strings.TrimSpace(row[1])
			pos = strings.TrimSpace(row[2])
			brand = strings.TrimSpace(row[3])
		}

		if partnumber == "" {
			continue
		}

		// Normalize part number: remove spaces (319 733 -> 319733)
		partnumber = strings.ReplaceAll(partnumber, " ", "")
		partnumber = strings.ToUpper(partnumber)

		// Normalize position casing
		pos = normalizeTrwSachsPosition(pos)
		brand = strings.ToUpper(brand)

		writer.Write([]string{partnumber, aplicacion, pos, brand})
		written++
	}

	writer.Flush()
	fmt.Printf("Exported: trw_sachs.csv (%d rows)\n", written)
	return nil
}

func normalizeTrwSachsPosition(pos string) string {
	pos = strings.TrimSpace(pos)
	if pos == "" {
		return ""
	}
	// Title-case each word so lowercase "trasero" becomes "Trasero".
	words := strings.Fields(pos)
	for i, w := range words {
		if len(w) == 0 {
			continue
		}
		words[i] = strings.ToUpper(w[:1]) + strings.ToLower(w[1:])
	}
	return strings.Join(words, " ")
}

// ------------------------------------------------------------------
// Wagner  (brake pads + shoes; ACES export with FMSI in PartAttributes)
// ------------------------------------------------------------------

// isFrictionPartType reports whether a Wagner/Monroe PartType value belongs
// in the brake_brand_specs friction table. Both English and Spanish ACES
// values are accepted; other PartTypes (rotors, master cylinders, drums,
// fluid, clutch cylinders) are intentionally skipped.
func isFrictionPartType(pt string) bool {
	pt = strings.ToLower(strings.TrimSpace(pt))
	if pt == "" {
		return false
	}
	english := map[string]bool{
		"disc brake pad set": true,
		"drum brake shoe":    true,
	}
	if english[pt] {
		return true
	}
	// Spanish ACES values seen in recent exports: "Pastillas de Freno Del/Tras"
	// and "Zapatas de Freno ..."
	if strings.Contains(pt, "pastilla") || strings.Contains(pt, "zapata") || strings.Contains(pt, "balata") {
		return true
	}
	return false
}

func (c *Converter) processWagner(fp string) error {
	outDir := c.cfg.OutputDir
	f, err := excelize.OpenFile(fp)
	if err != nil {
		return err
	}
	defer f.Close()

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

	fmsiMap := c.processWagnerFMSI(f)
	fmt.Printf("  FMSI: extracted %d part numbers\n", len(fmsiMap))

	materialMap := c.processWagnerMaterial(f)
	fmt.Printf("  Material: extracted %d part numbers\n", len(materialMap))

	applications := c.processWagnerApplications(f)
	fmt.Printf("  Applications: %d fitment rows (pads+shoes)\n", len(applications))

	invoiceDesc, shortDesc, err := c.processMonroeDescriptions(f)
	if err != nil {
		fmt.Printf("  Warning: Descriptions: %v\n", err)
	}

	merged := c.mergeWagnerData(applications, invoiceDesc, shortDesc, fmsiMap, materialMap)

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

	writer := csv.NewWriter(outFile)
	writer.Comma = '|'
	writer.Write([]string{
		"partnumber", "qty", "parttype", "make", "model",
		"yearfrom", "yearto", "pos", "img_url",
		"content", "short_content", "fmsi", "material",
	})
	for _, row := range merged {
		writer.Write(row)
	}
	writer.Flush()
	fmt.Printf("Exported: wagner_brake.csv (%d rows)\n", len(merged))

	return nil
}

// processWagnerFMSI reads the PartAttributes sheet and builds a partNumber ->
// FMSI map by collecting rows whose Attribute == "FMSI Number".
func (c *Converter) processWagnerFMSI(f *excelize.File) map[string]string {
	result := make(map[string]string)
	rows, err := f.GetRows("PartAttributes")
	if err != nil {
		fmt.Printf("  Warning: PartAttributes sheet not found: %v\n", err)
		return result
	}

	headerMap, headerRowIdx := headerMapByHeaders(rows, "partnumber", "attribute")
	if headerRowIdx < 0 {
		fmt.Printf("  Warning: header row not found in PartAttributes sheet\n")
		return result
	}

	pnIdx, pnOk := headerMap["partnumber"]
	attrIdx, attrOk := headerMap["attribute"]
	valIdx, valOk := headerMap["attributevalue"]
	if !pnOk || !attrOk || !valOk {
		return result
	}

	for _, row := range rows[headerRowIdx+1:] {
		if len(row) <= pnIdx || len(row) <= valIdx {
			continue
		}
		pn := strings.TrimSpace(row[pnIdx])
		attr := strings.TrimSpace(row[attrIdx])
		val := strings.TrimSpace(row[valIdx])
		if pn == "" || val == "" {
			continue
		}
		if strings.EqualFold(attr, "FMSI Number") {
			if _, exists := result[pn]; !exists {
				result[pn] = val
			}
		}
	}
	return result
}

// processWagnerApplications reads the Applications sheet, keeps only friction
// PartTypes (pads + shoes), and returns one row per vehicle fitment with the
// 9 columns the Wagner importer expects:
//   partnumber, qty, parttype, make, model, yearfrom, yearto, pos, img_url
// (img_url is always empty — Wagner has no image column in Applications.)
func (c *Converter) processWagnerApplications(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, headerRowIdx := headerMapByHeaders(rows, "partnumber", "position")
	if headerRowIdx < 0 {
		// Fallback: position column may be absent; accept partnumber-only header.
		headerMap, headerRowIdx = headerMapByHeaders(rows, "partnumber")
	}
	if headerRowIdx < 0 {
		fmt.Printf("  Warning: header row not found in Applications sheet\n")
		return nil
	}

	outHeaders := []string{"partnumber", "qty", "parttype", "make", "model", "yearfrom", "yearto", "pos", "img_url"}
	pull := []string{"partnumber", "qty", "parttype", "make", "model", "yearfrom", "yearto", "position"}

	var result [][]string
	seen := make(map[string]bool)
	skipped := 0
	for _, row := range rows[headerRowIdx+1:] {
		if len(row) == 0 {
			continue
		}
		ptIdx, ok := headerMap["parttype"]
		if !ok || ptIdx >= len(row) {
			continue
		}
		pt := strings.ToLower(strings.TrimSpace(row[ptIdx]))
		if !isFrictionPartType(pt) {
			continue
		}

		values := make([]string, len(outHeaders))
		for i, h := range pull {
			ci, ok := headerMap[h]
			if !ok || ci >= len(row) || cleanStr(row[ci]) == "" {
				values[i] = "MISSING"
			} else {
				values[i] = cleanStr(row[ci])
			}
		}
		values[8] = "" // img_url not present in Wagner Applications

		if values[0] == "" || values[0] == "MISSING" {
			continue
		}
		// Drop exact-duplicate fitment rows: ACES emits many qualifier rows
		// per part+vehicle that collapse to identical output rows here.
		key := strings.Join(values, "\x00")
		if seen[key] {
			skipped++
			continue
		}
		seen[key] = true
		result = append(result, values)
	}
	if skipped > 0 {
		fmt.Printf("  Applications: dropped %d exact-duplicate fitment rows\n", skipped)
	}
	return result
}

// processWagnerMaterial reads the PartAttributes sheet and builds a
// partNumber -> Friction Material Composition map. Only rows whose Attribute
// is exactly "Friction Material Composition" are collected.
func (c *Converter) processWagnerMaterial(f *excelize.File) map[string]string {
	result := make(map[string]string)
	rows, err := f.GetRows("PartAttributes")
	if err != nil {
		fmt.Printf("  Warning: PartAttributes sheet not found: %v\n", err)
		return result
	}

	headerMap, headerRowIdx := headerMapByHeaders(rows, "partnumber", "attribute")
	if headerRowIdx < 0 {
		fmt.Printf("  Warning: header row not found in PartAttributes sheet\n")
		return result
	}

	pnIdx, pnOk := headerMap["partnumber"]
	attrIdx, attrOk := headerMap["attribute"]
	valIdx, valOk := headerMap["attributevalue"]
	if !pnOk || !attrOk || !valOk {
		return result
	}

	for _, row := range rows[headerRowIdx+1:] {
		if len(row) <= pnIdx || len(row) <= valIdx {
			continue
		}
		pn := strings.TrimSpace(row[pnIdx])
		attr := strings.TrimSpace(row[attrIdx])
		val := strings.TrimSpace(row[valIdx])
		if pn == "" || val == "" {
			continue
		}
		if strings.EqualFold(attr, "Friction Material Composition") {
			if _, exists := result[pn]; !exists {
				result[pn] = val
			}
		}
	}
	return result
}

// wagnerMaterialLabel normalizes a raw Friction Material Composition value
// into its Spanish display label. Returns "" for unknown compositions.
func wagnerMaterialLabel(composition string) string {
	switch strings.ToLower(strings.TrimSpace(composition)) {
	case "ceramic", "carbon fiber ceramic":
		return "Cerámica"
	case "semi-metallic":
		return "Semi-metálica"
	case "organic":
		return "Orgánica"
	default:
		return ""
	}
}

// wagnerMaterialFor resolves the display material for one fitment row.
// Drum shoes ("Zapata/Tambor...") are always labeled Zapata; pads fall back
// to their Friction Material Composition label, or blank when unknown.
func wagnerMaterialFor(partType string, materialMap map[string]string, partNumber string) string {
	pt := strings.ToLower(strings.TrimSpace(partType))
	if strings.Contains(pt, "zapata") || strings.Contains(pt, "shoe") {
		return "Zapata"
	}
	return wagnerMaterialLabel(materialMap[partNumber])
}

func (c *Converter) mergeWagnerData(applications [][]string, invoiceDesc, shortDesc, fmsiMap, materialMap map[string]string) [][]string {
	var merged [][]string
	for _, appRow := range applications {
		row := make([]string, 13)
		copy(row, appRow)

		pn := strings.TrimSpace(appRow[0])
		if content, ok := invoiceDesc[pn]; ok {
			row[9] = content
		} else {
			row[9] = "MISSING"
		}
		if short, ok := shortDesc[pn]; ok {
			row[10] = short
		} else {
			row[10] = "MISSING"
		}
		if fmsi, ok := fmsiMap[pn]; ok {
			row[11] = fmsi
		} else {
			row[11] = "MISSING"
		}
		row[12] = wagnerMaterialFor(appRow[2], materialMap, pn)
		merged = append(merged, row)
	}
	return merged
}

// headerMapByHeaders scans rows for the first row containing all the given
// (lowercased, cleaned) header names and returns the column index map plus
// that row's index. Returns -1 index if not found.
func headerMapByHeaders(rows [][]string, headers ...string) (map[string]int, int) {
	headerMap := make(map[string]int)
	for ri, row := range rows {
		if len(row) == 0 {
			continue
		}
		cleaned := make([]string, len(row))
		for i, v := range row {
			cleaned[i] = strings.ToLower(cleanStr(v))
		}
		allPresent := true
		for _, h := range headers {
			found := false
			for _, cv := range cleaned {
				if cv == h {
					found = true
					break
				}
			}
			if !found {
				allPresent = false
				break
			}
		}
		if !allPresent {
			continue
		}
		for ci, v := range row {
			headerMap[strings.ToLower(cleanStr(v))] = ci
		}
		return headerMap, ri
	}
	return headerMap, -1
}

func (c *Converter) processMonroeSheet(f *excelize.File, sourceFile, sheetName string, excelHeaders []string) (map[string][]string, error) {
	rows, err := f.GetRows(sheetName)
	if err != nil {
		return nil, sheetNotFoundErr(f, sheetName)
	}

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

	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 {
			partNumber := ""
			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 {
					values[i] = cleanStr(row[colIdx])
					if expected == "partnumber" {
						partNumber = values[i]
					}
				}
			}
			if partNumber != "" {
				result[partNumber] = values
			}
		}
	}

	fmt.Printf("  Sheet: %s, extracted %d part numbers\n", sheetName, len(result))
	return result, nil
}

func (c *Converter) mergeMonroeData(applications [][]string, invoiceDesc, shortDesc map[string]string) [][]string {
	var merged [][]string

	for _, appRow := range applications {
		// Schema: 0-8 = application fields, 9 = content, 10 = short_content,
		// 11 = serie, 12 = per-row LD/HD (metadata used during split only).
		row := make([]string, 13)
		copy(row, appRow)

		pn := strings.TrimSpace(appRow[0])
		pos := strings.TrimSpace(appRow[7])

		if content, ok := invoiceDesc[pn]; ok {
			row[9] = stripTrailingPosition(content, pos)
		} else {
			row[9] = "MISSING"
		}
		if short, ok := shortDesc[pn]; ok {
			row[10] = short
		} else {
			row[10] = "MISSING"
		}

		// In monroeok.xlsx the Spanish series label lives in the Applications
		// MfrLabel column (passed through in appRow[10]). Prefer it; fall back
		// to the Descriptions ES Invoice content for legacy files.
		if len(appRow) > 10 && strings.TrimSpace(appRow[10]) != "" {
			row[11] = strings.TrimSpace(appRow[10])
		} else if content, ok := invoiceDesc[pn]; ok {
			row[11] = stripTrailingPosition(content, pos)
		} else {
			row[11] = "MISSING"
		}

		// Per-row LD/HD is passed through in appRow[9] when present.
		if len(appRow) > 9 {
			row[12] = strings.TrimSpace(appRow[9])
		}

		merged = append(merged, row)
	}

	return merged
}

// monroePositionMap normalizes both legacy Spanish and current English
// raw position strings from the Monroe Applications sheet into a single
// canonical Spanish vocabulary used throughout the site.
var monroePositionMap = map[string]string{
	// Legacy Spanish sources (old monroe.xlsx)
	"tras":            "Tras",
	"del":             "Del",
	"del l":           "Del Izq",
	"del r":           "Del Der",
	"del izq":         "Del Izq",
	"del der":         "Del Der",
	"tras l":          "Tras Izq",
	"tras r":          "Tras Der",
	"tras izq":        "Tras Izq",
	"tras der":        "Tras Der",
	"del/tras":        "Del / Tras",
	"del / tras":      "Del / Tras",
	"cab":             "Cab",
	"tag axle":        "Eje Tarjeta",
	"del inferior":    "Del Inferior",
	"del superior":    "Del Superior",
	"del interior":    "Del Interior",
	"del adelante":    "Del Adelante",
	"del atrás":       "Del Atrás",
	"del atras":       "Del Atrás",
	"exterior del":    "Exterior Del",
	"n/a":             "N/A",
	// Current English sources (monroeok.xlsx)
	"front":                "Del",
	"rear":                 "Tras",
	"front left":           "Del Izq",
	"front right":          "Del Der",
	"rear left":            "Tras Izq",
	"rear right":           "Tras Der",
	"front lower":          "Del Inferior",
	"front upper":          "Del Superior",
	"front inner":          "Del Interior",
	"front forward":        "Del Adelante",
	"front rearward":       "Del Atrás",
	"front and rear":       "Del / Tras",
	"front outer":          "Exterior Del",
	"front left lower":     "Del Izq Inferior",
	"front right lower":    "Del Der Inferior",
	"rear left lower":      "Tras Izq Inferior",
	"rear right lower":     "Tras Der Inferior",
	"front lower at shock": "Del Inferior",
	"rear lower":           "Tras Inferior",
	"rear upper":           "Tras Superior",
}

// normalizeMonroePosition maps a raw position string to canonical Spanish.
// It tries the longest token sequences first so compounds like
// "front left lower" match before "front left".
func normalizeMonroePosition(raw string) string {
	raw = strings.ToLower(strings.TrimSpace(raw))
	if raw == "" {
		return ""
	}
	// Direct lookup first.
	if canonical, ok := monroePositionMap[raw]; ok {
		return canonical
	}
	// Longest-prefix scan across multi-word keys.
	var keys []string
	for k := range monroePositionMap {
		keys = append(keys, k)
	}
	sort.Slice(keys, func(i, j int) bool {
		return len(keys[i]) > len(keys[j])
	})
	for _, k := range keys {
		if strings.HasPrefix(raw, k) {
			return monroePositionMap[k]
		}
	}
	// No mapping: return the original trimmed value, title-cased for display.
	return simpleTitle(raw)
}

// simpleTitle returns a title-cased version of s without depending on the
// deprecated strings.Title.
func simpleTitle(s string) string {
	if s == "" {
		return ""
	}
	words := strings.Fields(s)
	for i, w := range words {
		if len(w) > 0 {
			r := []rune(w)
			r[0] = unicode.ToUpper(r[0])
			for j := 1; j < len(r); j++ {
				r[j] = unicode.ToLower(r[j])
			}
			words[i] = string(r)
		}
	}
	return strings.Join(words, " ")
}

// stripTrailingPosition removes the position word at the end of the invoice
// content if it is present. This keeps the position value in its own CSV
// column (pos) and prevents it from being duplicated inside the notes/content.
func stripTrailingPosition(content, pos string) string {
	content = strings.TrimSpace(content)
	pos = strings.TrimSpace(pos)
	if content == "" || pos == "" || pos == "MISSING" {
		return content
	}
	pos = strings.ToLower(pos)
	for _, token := range []string{pos, strings.TrimSpace(strings.ReplaceAll(pos, "/", " / "))} {
		if token == "" {
			continue
		}
		lowerContent := strings.ToLower(content)
		if strings.HasSuffix(lowerContent, " "+token) {
			content = strings.TrimSpace(content[:len(content)-(len(token)+1)])
		} else if strings.EqualFold(content, token) {
			content = ""
		}
	}
	return content
}

// processMonroeApplications reads the Applications sheet and returns one row
// per vehicle fitment (NOT deduplicated by part number — a single part can
// fit many vehicles, each with its own make/model/year).
func (c *Converter) processMonroeApplications(f *excelize.File) ([][]string, error) {
	rows, err := f.GetRows("Applications")
	if err != nil {
		return nil, sheetNotFoundErr(f, "Applications")
	}

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

	for rowIdx, row := range rows {
		if headerRowIdx == -1 && len(row) > 0 {
			for _, val := range row {
				hl := strings.ToLower(cleanStr(val))
				for _, expected := range excelHeaders {
					if hl == expected {
						headerRowIdx = rowIdx
						break
					}
				}
			}
			if headerRowIdx == rowIdx {
				for colIdx, val := range row {
					headerMap[strings.ToLower(cleanStr(val))] = colIdx
				}
				continue
			}
		}
	}

	if headerRowIdx < 0 {
		return nil, fmt.Errorf("archivo malformado: header row not found in Applications sheet")
	}

	csvHeaders := []string{"partnumber", "qty", "parttype", "make", "model", "yearfrom", "yearto", "pos", "img_url"}
	// The Monroe ACES sheet names the column "Position", not "pos".
	// Use "position" as the primary source, falling back to "pos".
	posSources := []string{"position", "pos"}
	var posColIdx *int
	for _, src := range posSources {
		if idx, ok := headerMap[src]; ok {
			posColIdx = &idx
			break
		}
	}

	// monroeok.xlsx carries LD/HD and the Spanish series label inside the
	// Applications sheet. Detect them once here so each fitment row can keep
	// its own metadata. If absent (legacy MonroeLDHD.xlsx layout), the values
	// stay empty and the older Packages-sheet / Descriptions fallbacks take over.
	ldhdColIdx := -1
	for _, candidate := range []string{"ld / hd", "ld/hd", "ld - dh", "ld-dh"} {
		if idx, ok := headerMap[candidate]; ok {
			ldhdColIdx = idx
			break
		}
	}
	mfrLabelColIdx := -1
	if idx, ok := headerMap["mfrlabel"]; ok {
		mfrLabelColIdx = idx
	}

	var result [][]string
	for rowIdx, row := range rows {
		if rowIdx <= headerRowIdx || len(row) == 0 {
			continue
		}
		values := make([]string, 11)
		for i, expected := range csvHeaders {
			colIdx, ok := headerMap[strings.ToLower(expected)]
			if !ok || colIdx >= len(row) || cleanStr(row[colIdx]) == "" {
				values[i] = "MISSING"
			} else {
				values[i] = cleanStr(row[colIdx])
			}
		}
		// Overwrite the position column with the source "Position" column,
		// normalizing legacy Spanish / current English values to canonical Spanish.
		if posColIdx != nil && *posColIdx < len(row) {
			values[7] = normalizeMonroePosition(cleanStr(row[*posColIdx]))
		}
		if ldhdColIdx >= 0 && ldhdColIdx < len(row) {
			values[9] = cleanStr(row[ldhdColIdx])
		}
		if mfrLabelColIdx >= 0 && mfrLabelColIdx < len(row) {
			values[10] = cleanStr(row[mfrLabelColIdx])
		}
		if values[0] == "" || values[0] == "MISSING" {
			continue
		}
		result = append(result, values)
	}

	fmt.Printf("  Sheet: Applications, extracted %d fitment rows\n", len(result))
	return result, nil
}

// processMonroeDescriptions reads the Descriptions sheet of the Monroe XLSX
// and returns two maps of partNumber -> Spanish content text:
//   - invoiceDesc: ES "Product Description - Invoice" content (product line
//     in Spanish, e.g. "Gas-Magnum Amortiguador" — used for notes and tier
//     detection; falls back to any other ES row if Invoice is absent).
//   - shortDesc: ES "Product Description - Short" content (the generic
//     product label in Spanish, e.g. "Amortiguador", "Ensamble Strut" —
//     used as the product field on result cards).
//
// Only rows with LanguageCode == "ES" are used. Any FR/EN row is skipped.
func (c *Converter) processMonroeDescriptions(f *excelize.File) (map[string]string, map[string]string, error) {
	rows, err := f.GetRows("Descriptions")
	if err != nil {
		return nil, nil, sheetNotFoundErr(f, "Descriptions")
	}

	headerMap := make(map[string]int)
	headerRowIdx := -1
	for rowIdx, row := range rows {
		if len(row) == 0 {
			continue
		}
		cleaned := make([]string, len(row))
		for i, v := range row {
			cleaned[i] = strings.ToLower(cleanStr(v))
		}
		hasPart := false
		hasLang := false
		for _, v := range cleaned {
			if v == "partnumber" {
				hasPart = true
			}
			if v == "languagecode" {
				hasLang = true
			}
		}
		if hasPart && hasLang {
			headerRowIdx = rowIdx
			for colIdx, v := range row {
				headerMap[strings.ToLower(cleanStr(v))] = colIdx
			}
			break
		}
	}

	if headerRowIdx < 0 {
		return nil, nil, fmt.Errorf("archivo malformado: header row not found in Descriptions sheet")
	}

	pnIdx := headerMap["partnumber"]
	contentIdx := headerMap["content"]
	typeNameIdx := headerMap["typename"]
	langIdx := headerMap["languagecode"]

	const (
		typeNameInvoice = "Product Description - Invoice"
		typeNameShort   = "Product Description - Short"
	)

	invoiceDesc := make(map[string]string) // preferred invoice ES content; fallback any ES
	shortDesc := make(map[string]string)   // short ES content → product label
	invoiceSet := make(map[string]bool)

	for _, row := range rows[headerRowIdx+1:] {
		if len(row) <= pnIdx || len(row) <= contentIdx || len(row) <= langIdx {
			continue
		}
		pn := strings.TrimSpace(row[pnIdx])
		if pn == "" {
			continue
		}
		lang := strings.TrimSpace(row[langIdx])
		if lang != "ES" { // ES ONLY — skip FR, EN, anything else
			continue
		}
		content := strings.TrimSpace(row[contentIdx])
		if content == "" {
			continue
		}
		tn := ""
		if typeNameIdx >= 0 && typeNameIdx < len(row) {
			tn = strings.TrimSpace(row[typeNameIdx])
		}

		switch tn {
		case typeNameInvoice:
			invoiceDesc[pn] = content
			invoiceSet[pn] = true
		case typeNameShort:
			shortDesc[pn] = content
		default:
			// keep as fallback for invoice if no invoice row exists yet
			if !invoiceSet[pn] {
				if _, exists := invoiceDesc[pn]; !exists {
					invoiceDesc[pn] = content
				}
			}
		}
	}

	fmt.Printf("  Sheet: Descriptions, extracted %d ES invoice + %d ES short descriptions\n", len(invoiceDesc), len(shortDesc))
	return invoiceDesc, shortDesc, nil
}

// ------------------------------------------------------------------
// VehicleApp
// ------------------------------------------------------------------

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