package xlstranslator

import (
	"fmt"
	"sort"
	"strings"

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

// SheetTemplate describes one sheet of a template file.
type SheetTemplate struct {
	Name           string   `json:"name"`
	Headers        []string `json:"headers"`
	HeaderRowIndex int      `json:"header_row_index"`
	DataStartRow   int      `json:"data_start_row"`
	Positional     bool     `json:"positional"`
}

// TemplateSpec describes an input template for one converter type.
// Kind is either "xlsx" or "xml".
type TemplateSpec struct {
	Name        string          `json:"name"`
	DisplayName string          `json:"display_name"`
	Kind        string          `json:"kind"`
	Filename    string          `json:"filename"`
	Description string          `json:"description"`
	Sheets      []SheetTemplate `json:"sheets"`
	XMLSkeleton string          `json:"-"`
}

// TemplateSpecs is the full registry of templates that the admin panel can
// generate and download. Headers and sheet names match the expectations of the
// converter code so that a filled template can be uploaded as-is.
var TemplateSpecs = []TemplateSpec{
	{
		Name:        "amort",
		DisplayName: "GRC Amortiguadores Pesado",
		Kind:        "xlsx",
		Filename:    "amort_template.xlsx",
		Description: "Columnas posicionales. La fila de encabezados es ignorada por el conversor; empieza a capturar datos desde la segunda fila.",
		Sheets: []SheetTemplate{
			{
				Name:           "GRC AMR pesado MIP 1707",
				Headers:        []string{"", "marca", "numero de parte", "modelo", "año", "Modelo base", "notas aplicacion", "Categoria", "producto", "longitud extendida", "longitud comprimida", "carrera", "Medida", "montaje superior", "montaje inferior", "Referencia", "posicion", "imagen"},
				HeaderRowIndex: 0,
				DataStartRow:   1,
				Positional:     true,
			},
		},
	},
	{
		Name:        "frenos",
		DisplayName: "GRC Frenos",
		Kind:        "xlsx",
		Filename:    "frenos_template.xlsx",
		Description: "Columnas posicionales. La fila de encabezados es ignorada por el conversor; empieza a capturar datos desde la segunda fila.",
		Sheets: []SheetTemplate{
			{
				Name:           "Frenos GRC MIP 1707",
				Headers:        []string{"", "marca", "modelo", "año", "numero de parte", "producto", "serie", "tier", "cantidad", "posicion", "referencia FMSI", "FMSI alt", "incluye herraje", "imagen", "Calificador", "", "Descripcion"},
				HeaderRowIndex: 0,
				DataStartRow:   1,
				Positional:     true,
			},
		},
	},
	{
		Name:        "ag",
		DisplayName: "AG (Shock / Strut / Leg)",
		Kind:        "xlsx",
		Filename:    "ag_template.xlsx",
		Description: "Usa exactamente estos nombres de hoja y encabezados (acentuación incluida).",
		Sheets: []SheetTemplate{
			{
				Name:           "Amortiguadores AG Shock",
				Headers:        []string{"Código", "kyb", "boge", "Marca", "Aplicación", "Desde", "Hasta", "Pos", "Link imagen"},
				HeaderRowIndex: 0,
				DataStartRow:   1,
				Positional:     false,
			},
			{
				Name:           "Bases Amortiguador AG Strut",
				Headers:        []string{"Código", "syd", "vazlo", "Marca", "Aplicación", "Desde", "Hasta", "Pos", "Link imagen"},
				HeaderRowIndex: 0,
				DataStartRow:   1,
				Positional:     false,
			},
			{
				Name:           "Piernas AG Proshock",
				Headers:        []string{"Código", "Marca", "Aplicación", "Desde", "Hasta", "Pos", "Link imagen"},
				HeaderRowIndex: 0,
				DataStartRow:   1,
				Positional:     false,
			},
		},
	},
	{
		Name:        "monroe",
		DisplayName: "Monroe",
		Kind:        "xlsx",
		Filename:    "monroe_template.xlsx",
		Description: "Tres hojas: Packages (opcional), Applications (obligatoria) y Descriptions (obligatoria). LD - DH en Packages determina LD/HD; en Applications usa 'position' o 'pos'. En Descriptions, languagecode debe ser ES y typename debe ser exactamente 'Product Description - Invoice' o 'Product Description - Short'.",
		Sheets: []SheetTemplate{
			{
				Name:           "Packages",
				Headers:        []string{"Part Number", "LD - DH"},
				HeaderRowIndex: 0,
				DataStartRow:   1,
				Positional:     false,
			},
			{
				Name:           "Applications",
				Headers:        []string{"partnumber", "qty", "parttype", "make", "model", "yearfrom", "yearto", "position", "img_url"},
				HeaderRowIndex: 0,
				DataStartRow:   1,
				Positional:     false,
			},
			{
				Name:           "Descriptions",
				Headers:        []string{"partnumber", "content", "typename", "languagecode"},
				HeaderRowIndex: 0,
				DataStartRow:   1,
				Positional:     false,
			},
		},
	},
	{
		Name:        "trw_sachs",
		DisplayName: "TRW Sachs",
		Kind:        "xlsx",
		Filename:    "trw_sachs_template.xlsx",
		Description: "Primera hoja del libro. Columnas posicionales; una fila que contenga 'NUM PARTE' en la columna A o B es ignorada.",
		Sheets: []SheetTemplate{
			{
				Name:           "TRW Sachs",
				Headers:        []string{"", "NUM PARTE", "APLICACIÓN", "POSICIÓN", "MARCA"},
				HeaderRowIndex: 0,
				DataStartRow:   1,
				Positional:     true,
			},
		},
	},
	{
		Name:        "cano",
		DisplayName: "Cano Discos",
		Kind:        "xlsx",
		Filename:    "cano_template.xlsx",
		Description: "Hoja 'Hoja1'. Encabezados en la tercera fila (filas 1-2 son apartado). Las aplicaciones se separan por '|'.",
		Sheets: []SheetTemplate{
			{
				Name:           "Hoja1",
				Headers:        []string{"Código", "Código MIP", "Nombre", "Descripción corta", "Tipo", "Categoría", "Posición", "Mínimo Espesor", "Diametro Original", "Máximo Diametro", "Altura", "Diametro Piloto", "Materiales", "Birlos", "Aplicaciones", "Peso Aprox", "Garantía", "Artículo"},
				HeaderRowIndex: 2,
				DataStartRow:   3,
				Positional:     false,
			},
		},
	},
	{
		Name:        "wagner",
		DisplayName: "Wagner Brake",
		Kind:        "xlsx",
		Filename:    "wagner_template.xlsx",
		Description: "Tres hojas: PartAttributes, Applications y Descriptions. Solo se importan partes de fricción (pastillas/zapatas).",
		Sheets: []SheetTemplate{
			{
				Name:           "PartAttributes",
				Headers:        []string{"partnumber", "attribute", "attributevalue"},
				HeaderRowIndex: 0,
				DataStartRow:   1,
				Positional:     false,
			},
			{
				Name:           "Applications",
				Headers:        []string{"partnumber", "qty", "parttype", "make", "model", "yearfrom", "yearto", "position"},
				HeaderRowIndex: 0,
				DataStartRow:   1,
				Positional:     false,
			},
			{
				Name:           "Descriptions",
				Headers:        []string{"partnumber", "content", "typename", "languagecode"},
				HeaderRowIndex: 0,
				DataStartRow:   1,
				Positional:     false,
			},
		},
	},
	{
		Name:        "grobb",
		DisplayName: "GROBB",
		Kind:        "xlsx",
		Filename:    "grobb_template.xlsx",
		Description: "Hoja 'Inventario'. Columnas posicionales: código (A), vacía (B), posición (C), aplicación (D).",
		Sheets: []SheetTemplate{
			{
				Name:           "Inventario",
				Headers:        []string{"Código", "", "Posición", "Aplicación"},
				HeaderRowIndex: 0,
				DataStartRow:   1,
				Positional:     true,
			},
		},
	},
	{
		Name:        "fritec",
		DisplayName: "FRITEC",
		Kind:        "xlsx",
		Filename:    "fritec_template.xlsx",
		Description: "Hoja 'Relación existencia'. Columnas posicionales; la columna de existencia (E) es ignorada.",
		Sheets: []SheetTemplate{
			{
				Name:           "Relación existencia",
				Headers:        []string{"Código", "Marca", "Tipo", "Aplicación", "Existencia (ignorado)", "Posicion"},
				HeaderRowIndex: 0,
				DataStartRow:   1,
				Positional:     true,
			},
		},
	},
	{
		Name:        "prestone",
		DisplayName: "Prestone",
		Kind:        "xlsx",
		Filename:    "prestone_template.xlsx",
		Description: "Puedes tener varias hojas; el nombre de cada hoja se convierte en la categoría del producto. Renombra 'Categoria' por la categoría real (ej. Anticongelantes).",
		Sheets: []SheetTemplate{
			{
				Name:           "Categoria",
				Headers:        []string{"Item", "No de parte", "Descripción", "Aplicaciones", "Tamaño", "Piezas", "Precio", "Codigo SAT"},
				HeaderRowIndex: 0,
				DataStartRow:   1,
				Positional:     false,
			},
		},
	},
	{
		Name:        "pagid",
		DisplayName: "Hella-Pagid",
		Kind:        "xlsx",
		Filename:    "pagid_template.xlsx",
		Description: "Tres hojas: balata y zapata, DISCO (encabezados en la segunda fila) y Relación existencia. Sensores: descripción con ≥4 partes separadas por coma.",
		Sheets: []SheetTemplate{
			{
				Name:           "balata y zapata",
				Headers:        []string{"partNumber", "ignorar", "product", "position", "fmsi", "aplicación"},
				HeaderRowIndex: 0,
				DataStartRow:   1,
				Positional:     true,
			},
			{
				Name:           "DISCO",
				Headers:        []string{"partNumber", "ignorar", "ignorar", "type", "position", "aplicación"},
				HeaderRowIndex: 1,
				DataStartRow:   2,
				Positional:     true,
			},
			{
				Name:           "Relación existencia",
				Headers:        []string{"partNumber", "descripcion (producto, marca, posicion, tamaño, ...)"},
				HeaderRowIndex: 0,
				DataStartRow:   1,
				Positional:     true,
			},
		},
	},
	{
		Name:        "brembo",
		DisplayName: "Brembo XML",
		Kind:        "xml",
		Filename:    "brembo_template.xml",
		Description: "Archivo XML con elementos <App>. Solo part numbers de la lista blanca generan filas en el CSV; el ejemplo usa P06033.",
		XMLSkeleton: bremboXMLTemplate,
	},
}

// bremboXMLTemplate is a minimal skeleton showing the expected XML shape.
const bremboXMLTemplate = `<?xml version="1.0" encoding="UTF-8"?>
<ACES version="3.2">
  <!-- Cada <App> representa una aplicación. El conversor lee todos los <App> sin importar el elemento raíz. -->
  <App>
    <!-- BaseVehicle: se exporta tal cual como la aplicación. Formato sugerido: Marca - Modelo - Año -->
    <BaseVehicle>Volkswagen - Jetta - 2018</BaseVehicle>
    <!-- SubModel, Qty: leídos pero no exportados -->
    <SubModel>GLI</SubModel>
    <Qty>1</Qty>
    <!-- PartTerminology -> producto (ej. Brake Pad Set) -->
    <PartTerminology>Brake Pad Set</PartTerminology>
    <!-- MfrLabel -> tipo -->
    <MfrLabel>Front Axle Set</MfrLabel>
    <!-- Position -> posicion (Front/Rear/etc.) -->
    <Position>Front</Position>
    <!-- PartNumber: obligatorio; debe estar en la lista blanca del conversor para generar fila -->
    <PartNumber>P06033</PartNumber>
  </App>
</ACES>
`

// ConverterTypeInfo is a lightweight entry for the conversion-type selector.
type ConverterTypeInfo struct {
	Name        string `json:"name"`
	DisplayName string `json:"display_name"`
}

// ConverterTypes returns the list of converters that the backend actually
// supports (derived from converterFileNames), paired with their display names
// from TemplateSpecs. This keeps the admin-panel selector in sync with the
// real dispatch logic in ConvertFile/convertByType.
func ConverterTypes() []ConverterTypeInfo {
	displayByName := make(map[string]string, len(TemplateSpecs))
	for _, spec := range TemplateSpecs {
		displayByName[spec.Name] = spec.DisplayName
	}

	types := make([]ConverterTypeInfo, 0, len(converterFileNames))
	for name := range converterFileNames {
		dn := displayByName[name]
		if dn == "" {
			dn = name
		}
		types = append(types, ConverterTypeInfo{
			Name:        name,
			DisplayName: dn,
		})
	}

	// Stable alphabetical order by raw converter name.
	sort.Slice(types, func(i, j int) bool {
		return types[i].Name < types[j].Name
	})

	return types
}

// GetTemplateSpec returns the spec for a given template name.
func GetTemplateSpec(name string) (TemplateSpec, bool) {
	for _, s := range TemplateSpecs {
		if s.Name == name {
			return s, true
		}
	}
	return TemplateSpec{}, false
}

// GenerateTemplate builds a template file in-memory for the given converter name.
func GenerateTemplate(name string) ([]byte, string, error) {
	spec, ok := GetTemplateSpec(name)
	if !ok {
		return nil, "", fmt.Errorf("unknown template: %s", name)
	}

	if spec.Kind == "xml" {
		return []byte(strings.TrimSpace(spec.XMLSkeleton)), "application/xml", nil
	}

	f := excelize.NewFile()
	defer f.Close()

	for _, sheet := range spec.Sheets {
		if _, err := f.NewSheet(sheet.Name); err != nil {
			return nil, "", fmt.Errorf("create sheet %q: %w", sheet.Name, err)
		}

		rowNum := sheet.HeaderRowIndex + 1
		cell := fmt.Sprintf("A%d", rowNum)

		values := make([]interface{}, len(sheet.Headers))
		for i, h := range sheet.Headers {
			values[i] = h
		}

		if err := f.SetSheetRow(sheet.Name, cell, &values); err != nil {
			return nil, "", fmt.Errorf("write headers in %q: %w", sheet.Name, err)
		}
	}

	// Remove the default sheet created by excelize.
	if err := f.DeleteSheet("Sheet1"); err != nil {
		return nil, "", fmt.Errorf("delete default sheet: %w", err)
	}

	buf, err := f.WriteToBuffer()
	if err != nil {
		return nil, "", fmt.Errorf("write xlsx: %w", err)
	}

	return buf.Bytes(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", nil
}