package csvimporter

import (
	"context"
	"fmt"
	"regexp"
	"strconv"
	"strings"

	"catalogue.midpdsa.com/src/db/catalogue"
	"github.com/jackc/pgx/v5/pgtype"
	"github.com/xuri/excelize/v2"
)

// ImportBremboXLSX reads the temporary Brembo Excel file and imports its three
// data sheets (FRENOS, sensor, disco) into brake_brand_specs / disk_brand_specs.
// It imports every row regardless of the XML allowlist and leaves tier empty.
func (imp *Importer) ImportBremboXLSX(filename string) (ImportResult, error) {
	result := ImportResult{Table: "brembo_brand_specs"}

	f, err := excelize.OpenFile(filename)
	if err != nil {
		return result, fmt.Errorf("failed to open xlsx: %w", err)
	}
	defer f.Close()

	ctx := context.Background()

	vehicleTypeID, err := imp.findOrCreateVehicleType(ctx, "Ligero")
	if err != nil {
		return result, fmt.Errorf("failed to get vehicle type: %w", err)
	}

	brakeBrandID, err := imp.findOrCreateBrakeBrand(ctx, "Brembo")
	if err != nil {
		return result, fmt.Errorf("failed to get brake brand: %w", err)
	}

	diskBrandID, err := imp.findOrCreateDiskBrand(ctx, "Brembo")
	if err != nil {
		return result, fmt.Errorf("failed to get disk brand: %w", err)
	}

	sheetConfigs := []struct {
		name   string
		isDisk bool
	}{
		{name: "FRENOS", isDisk: false},
		{name: "sensor", isDisk: false},
		{name: "disco", isDisk: true},
	}

	header := []string{"numero_de_parte", "producto", "tipo", "aplicacion", "fmsi", "posicion"}
	var errorRows []ErrorRow

	for _, cfg := range sheetConfigs {
		rows, err := f.GetRows(cfg.name)
		if err != nil {
			return result, fmt.Errorf("sheet %s: %w", cfg.name, err)
		}

		headerMap, headerIdx := buildXLSXHeaderMap(rows)
		if headerIdx < 0 {
			fmt.Printf("  Warning: sheet %s has no 'Número de parte' header, skipping\n", cfg.name)
			continue
		}

		fmt.Printf("  Sheet %s: header row %d\n", cfg.name, headerIdx+1)

		sheetResult, sheetErrors := imp.processBremboXLSXSheet(
			ctx, cfg.name, rows[headerIdx+1:], headerMap, header,
			vehicleTypeID, brakeBrandID, diskBrandID, cfg.isDisk,
		)
		result.Inserted += sheetResult.Inserted
		result.Errors += sheetResult.Errors
		errorRows = append(errorRows, sheetErrors...)
	}

	if len(errorRows) > 0 {
		if err := imp.writeErrorCSV("brembo_xlsx_errors.csv", header, errorRows); err != nil {
			fmt.Printf("  Warning: failed to write error CSV: %v\n", err)
		}
	}

	return result, nil
}

func buildXLSXHeaderMap(rows [][]string) (map[string]int, int) {
	for i, row := range rows {
		m := make(map[string]int)
		foundPart := false
		for j, cell := range row {
			key := normalizeHeaderKey(cell)
			if key == "" {
				continue
			}
			m[key] = j
			if key == "numerodeparte" || strings.Contains(key, "numerodeparte") {
				foundPart = true
			}
		}
		if foundPart {
			return m, i
		}
	}
	return nil, -1
}

func (imp *Importer) processBremboXLSXSheet(
	ctx context.Context,
	sheetName string,
	rows [][]string,
	headerMap map[string]int,
	header []string,
	vehicleTypeID, brakeBrandID, diskBrandID int32,
	isDisk bool,
) (ImportResult, []ErrorRow) {
	result := ImportResult{Table: sheetName}
	var errorRows []ErrorRow

	for i, row := range rows {
		rowNum := i + 1 // relative to first data row

		noParte := getColumnValue(row, headerMap, "número de parte")
		producto := getColumnValue(row, headerMap, "producto")
		posicion := getColumnValue(row, headerMap, "posición")
		fmsi := getColumnValue(row, headerMap, "fmsi")
		aplicacion := getColumnValue(row, headerMap, "aplicación")

		// Normalized CSV row aligned with the error CSV header.
		errRow := []string{noParte, producto, "", aplicacion, fmsi, posicion}

		// Skip completely blank trailing rows.
		if noParte == "" && producto == "" && aplicacion == "" {
			continue
		}

		if noParte == "" || noParte == "MISSING" {
			result.Errors++
			errorRows = append(errorRows, ErrorRow{Row: errRow, Error: "Missing noParte", RowNum: rowNum})
			continue
		}

		if aplicacion == "" || aplicacion == "MISSING" {
			result.Errors++
			errorRows = append(errorRows, ErrorRow{Row: errRow, Error: "Missing aplicacion", RowNum: rowNum})
			continue
		}

		normalizedApp := normalizeXLSXApplication(aplicacion)
		vehicles := parseVehicleApplications(normalizedApp)
		if len(vehicles) == 0 {
			result.Errors++
			errorRows = append(errorRows, ErrorRow{Row: errRow, Error: "No valid vehicles parsed", RowNum: rowNum})
			continue
		}

		position := imp.parsePosition(posicion)

		for _, vehicle := range vehicles {
			modelYearID, err := imp.findOrCreateVehicleModelYear(ctx, vehicle.Make, vehicle.Model, vehicleTypeID)
			if err != nil {
				result.Errors++
				errorRows = append(errorRows, ErrorRow{Row: errRow, Error: fmt.Sprintf("Failed to find/create vehicle model year: %v", err), RowNum: rowNum})
				continue
			}

			if isDisk {
				if err := imp.insertDiskBrandSpec(ctx, catalogue.CreateDiskBrandSpecParams{
					PartNumber:           pgtype.Text{String: noParte, Valid: true},
					Product:              pgtype.Text{String: producto, Valid: producto != "" && producto != "MISSING"},
					Serie:                pgtype.Text{Valid: false},
					Tier:                 pgtype.Text{Valid: false},
					Quantity:             pgtype.Int4{Int32: 1, Valid: true},
					Position:             pgtype.Text{String: position, Valid: position != ""},
					IncludesHardware:     pgtype.Text{String: "No", Valid: true},
					VehicleAttrs:         pgtype.Text{String: aplicacion, Valid: true},
					FkIdVehicleModelYear: pgtype.Int4{Int32: modelYearID, Valid: true},
					FkIdDiskBrand:        pgtype.Int4{Int32: diskBrandID, Valid: true},
					Years:                pgtype.Text{String: vehicle.Year, Valid: vehicle.Year != ""},
					YearsRange:           pgtype.Text{String: vehicle.YearRange, Valid: vehicle.YearRange != ""},
				}, &result); err != nil {
					result.Errors++
					errorRows = append(errorRows, ErrorRow{Row: errRow, Error: fmt.Sprintf("Database insert failed: %v", err), RowNum: rowNum})
					continue
				}
			} else {
				if err := imp.insertBrakeBrandSpec(ctx, catalogue.CreateBrakeBrandSpecParams{
					PartNumber:           pgtype.Text{String: noParte, Valid: true},
					Product:              pgtype.Text{String: producto, Valid: producto != "" && producto != "MISSING"},
					Serie:                pgtype.Text{Valid: false},
					Tier:                 pgtype.Text{Valid: false},
					Quantity:             pgtype.Int4{Int32: 1, Valid: true},
					Position:             pgtype.Text{String: position, Valid: position != ""},
					Fmsi:                 pgtype.Text{String: fmsi, Valid: fmsi != "" && fmsi != "MISSING"},
					IncludesHardware:     pgtype.Text{String: "No", Valid: true},
					VehicleAttrs:         pgtype.Text{String: aplicacion, Valid: true},
					FkIdVehicleModelYear: pgtype.Int4{Int32: modelYearID, Valid: true},
					FkIdBrakeBrand:       pgtype.Int4{Int32: brakeBrandID, Valid: true},
					Years:                pgtype.Text{String: vehicle.Year, Valid: vehicle.Year != ""},
					YearsRange:           pgtype.Text{String: vehicle.YearRange, Valid: vehicle.YearRange != ""},
				}, &result); err != nil {
					result.Errors++
					errorRows = append(errorRows, ErrorRow{Row: errRow, Error: fmt.Sprintf("Database insert failed: %v", err), RowNum: rowNum})
					continue
				}
			}
		}

		if (i+1)%50 == 0 {
			fmt.Printf("    %s: %d rows processed (inserted: %d, errors: %d)\n", sheetName, i+1, result.Inserted, result.Errors)
		}
	}

	fmt.Printf("  %s: finished (inserted: %d, errors: %d)\n", sheetName, result.Inserted, result.Errors)
	return result, errorRows
}

var reversedYearRangeRe = regexp.MustCompile(`\b(\d{4})-(\d{4})\b`)

func normalizeXLSXApplication(s string) string {
	// The xlsx sometimes uses "Make - Model" separators; normalize to spaces.
	s = strings.ReplaceAll(s, " - ", " ")
	// Collapse repeated whitespace.
	s = strings.Join(strings.Fields(s), " ")

	segments := strings.Split(s, ",")
	var normalized []string
	for _, seg := range segments {
		seg = strings.TrimSpace(seg)
		if seg == "" {
			continue
		}

		matches := applicationYearPattern.FindAllStringIndex(seg, -1)
		if len(matches) == 0 {
			normalized = append(normalized, seg)
			continue
		}

		if len(matches) == 1 {
			normalized = append(normalized, swapReversedYearRanges(seg))
			continue
		}

		// Multiple year patterns in one comma segment means multiple models were
		// written without commas (e.g. "Mercedes-Benz A200 2018-2020 Cla200 2019-2020").
		var buf strings.Builder
		lastEnd := 0
		for i, m := range matches {
			buf.WriteString(seg[lastEnd:m[1]])
			if i < len(matches)-1 {
				buf.WriteString(",")
			}
			lastEnd = m[1]
		}
		if lastEnd < len(seg) {
			buf.WriteString(seg[lastEnd:])
		}
		normalized = append(normalized, swapReversedYearRanges(buf.String()))
	}

	return strings.Join(normalized, ", ")
}

func swapReversedYearRanges(s string) string {
	return reversedYearRangeRe.ReplaceAllStringFunc(s, func(match string) string {
		parts := strings.Split(match, "-")
		if len(parts) != 2 {
			return match
		}
		a, err1 := strconv.Atoi(parts[0])
		b, err2 := strconv.Atoi(parts[1])
		if err1 != nil || err2 != nil {
			return match
		}
		if a > b {
			return fmt.Sprintf("%d-%d", b, a)
		}
		return match
	})
}