package csvimporter

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

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

func (imp *Importer) ImportTRWSachs(filename string) (ImportResult, error) {
	result := ImportResult{Table: "trw_sachs_import"}

	records, err := imp.readCSVSeparator(filename, '|')
	if err != nil {
		return result, fmt.Errorf("failed to read CSV: %w", err)
	}

	if len(records) < 2 {
		return result, fmt.Errorf("no data rows in CSV")
	}

	ctx := context.Background()

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

	header := records[0]
	var errorRows []ErrorRow

	for i, row := range records[1:] {
		rowNum := i + 2
		if len(row) < 4 {
			result.Errors++
			errorRows = append(errorRows, ErrorRow{Row: row, Error: "Row has fewer than 4 columns", RowNum: rowNum})
			continue
		}

		partnumber := row[0]
		aplicacion := row[1]
		pos := row[2]
		brand := row[3]

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

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

		shockBrandID, err := imp.findOrCreateShockBrand(ctx, brand)
		if err != nil {
			result.Errors++
			errorRows = append(errorRows, ErrorRow{Row: row, Error: fmt.Sprintf("Failed to find/create shock brand: %v", err), RowNum: rowNum})
			continue
		}

		apps := parseTrwSachsApplications(aplicacion)
		if len(apps) == 0 {
			result.Errors++
			errorRows = append(errorRows, ErrorRow{Row: row, Error: "No valid applications found", RowNum: rowNum})
			continue
		}

		for _, app := range apps {
			if app.Make == "" || app.Make == "MISSING" || app.Model == "" || app.Model == "MISSING" {
				continue
			}

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

			yearsStr := ""
			yearsRangeStr := ""
			if app.YearFrom > 0 {
				if app.YearTo > 0 && app.YearTo != app.YearFrom {
					yearsRangeStr = fmt.Sprintf("%d-%d", app.YearFrom, app.YearTo)
				} else {
					yearsStr = fmt.Sprintf("%d", app.YearFrom)
				}
			}

			notes := app.Notes
			if notes == "MISSING" {
				notes = ""
			}

			if err := imp.insertShockBrandSpec(ctx, catalogue.CreateShockBrandSpecParams{
				PartNumber:           pgtype.Text{String: partnumber, Valid: true},
				Product:              pgtype.Text{String: "Amortiguador", Valid: true},
				Serie:                pgtype.Text{Valid: false},
				Quantity:             pgtype.Int2{Int16: 1, Valid: true},
				Tier:                 pgtype.Text{Valid: false},
				Position:             pgtype.Text{String: pos, Valid: pos != ""},
				ImageProduct:         pgtype.Text{Valid: false},
				VehicleAttrs:         pgtype.Text{String: fmt.Sprintf("%d-%d", app.YearFrom, app.YearTo), Valid: true},
				Notes:                pgtype.Text{String: notes, Valid: notes != ""},
				FkIdVehicleModelYear: pgtype.Int4{Int32: modelYearID, Valid: true},
				FkIdShockBrand:       pgtype.Int4{Int32: shockBrandID, Valid: true},
				Years:                pgtype.Text{String: yearsStr, Valid: yearsStr != ""},
				YearsRange:           pgtype.Text{String: yearsRangeStr, Valid: yearsRangeStr != ""},
			}, &result); err != nil {
				result.Errors++
				errorRows = append(errorRows, ErrorRow{Row: row, Error: fmt.Sprintf("Database insert failed: %v", err), RowNum: rowNum})
				continue
			}
		}

		if i%100 == 0 {
			fmt.Printf("  TRW/SACHS: %d rows processed\n", i)
		}
	}

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

	return result, nil
}

type trwSachsApp struct {
	Make     string
	Model    string
	YearFrom int32
	YearTo   int32
	Notes    string
}

var trwSachsYearPattern = regexp.MustCompile(`\b(\d{4})(?:\s*-\s*(\d{2,4}))?\b`)

// parseTrwSachsApplications splits the APLICACIÓN cell into individual vehicle
// applications. It handles bracketed ranges [YYYY-YYYY], bare single years (Mercedes
// rows), multi-model brackets (Q2 Q3 TT [YYYY-YYYY]), and engine-detail suffixes.
func parseTrwSachsApplications(aplicacion string) []trwSachsApp {
	if aplicacion == "" || aplicacion == "MISSING" {
		return nil
	}

	// Normalize internal spaces in year ranges (2011-20 21 -> 2011-2021).
	aplicacion = fixYearRangeSpaces(aplicacion)

	// Split on commas and newlines; both separate applications in this file.
	var rawSegments []string
	for _, s := range strings.Split(aplicacion, ",") {
		for _, t := range strings.Split(s, "\n") {
			t = strings.TrimSpace(t)
			if t != "" {
				rawSegments = append(rawSegments, t)
			}
		}
	}

	var apps []trwSachsApp
	currentMake := ""
	for _, seg := range rawSegments {
		app, makeForNext := parseTrwSachsApplication(seg, currentMake)
		if makeForNext != "" {
			currentMake = makeForNext
		}
		if app == nil {
			continue
		}
		apps = append(apps, app...)
	}

	return apps
}

// fixYearRangeSpaces collapses spaces inside year ranges like "2011-20 21" -> "2011-2021".
func fixYearRangeSpaces(s string) string {
	// First fix a space inserted between the two digit pairs of the year-to value.
	re := regexp.MustCompile(`(\d{4})-(\d{2})\s+(\d{2})`)
	s = re.ReplaceAllString(s, "${1}-${2}${3}")
	// Then normalize any remaining spaces around the hyphen.
	re2 := regexp.MustCompile(`(\d{4})\s*-\s*(\d{2,4})`)
	return re2.ReplaceAllString(s, "${1}-${2}")
}

// parseTrwSachsApplication parses one comma/newline segment. It may return multiple
// apps when several models share a single year bracket.
func parseTrwSachsApplication(seg, currentMake string) ([]trwSachsApp, string) {
	seg = strings.TrimSpace(seg)
	if seg == "" {
		return nil, currentMake
	}

	// 1) Try bracketed year first.
	bracketRe := regexp.MustCompile(`\[(\d{4})(?:\s*-\s*(\d{2,4}))?\]`)
	loc := bracketRe.FindStringSubmatchIndex(seg)

	var yearFrom, yearTo int32
	var textBefore, textAfter string
	foundYear := false

	if loc != nil {
		yf := seg[loc[2]:loc[3]]
		yt := ""
		if loc[4] >= 0 && loc[5] >= 0 {
			yt = seg[loc[4]:loc[5]]
		}
		yearFrom = parseYear(yf)
		yearTo = parseYear(normalizeYearEnd(yf, yt))
		textBefore = strings.TrimSpace(seg[:loc[0]])
		textAfter = strings.TrimSpace(seg[loc[1]:])
		foundYear = true
	} else {
		// 2) Bare year range or single year.
		m := trwSachsYearPattern.FindStringSubmatchIndex(seg)
		if m == nil {
			return nil, currentMake
		}
		yf := seg[m[2]:m[3]]
		yt := ""
		if m[4] >= 0 && m[5] >= 0 {
			yt = seg[m[4]:m[5]]
		}
		yearFrom = parseYear(yf)
		yearTo = parseYear(normalizeYearEnd(yf, yt))
		textBefore = strings.TrimSpace(seg[:m[0]])
		textAfter = strings.TrimSpace(seg[m[1]:])
		foundYear = true
	}

	if !foundYear || yearFrom == 0 {
		return nil, currentMake
	}

	// Engine detail / parenthetical notes live after the year token.
	notes := textAfter

	// Determine make and model tokens from textBefore.
	make, modelTokens, foundMake := splitTrwSachsMakeModel(textBefore, currentMake)
	if foundMake != "" {
		currentMake = foundMake
	}

	if make == "" || len(modelTokens) == 0 {
		// No make or no model: skip (e.g. empty-model brackets like "[2018-2022]").
		return nil, currentMake
	}

	// Expand only when every model token is a short UPPERCASE model code (Q2, Q3, TT, A3, A4).
	// Mixed-case tokens like "Benz" or body-style words like "Hatchback" keep the whole string as one model.
	expand := len(modelTokens) > 1
	for _, t := range modelTokens {
		if len(t) > 3 || !isUpperAlphaNumeric(t) {
			expand = false
			break
		}
	}

	var models []string
	if expand {
		for _, t := range modelTokens {
			models = append(models, t)
		}
	} else {
		models = append(models, strings.Join(modelTokens, " "))
	}

	var apps []trwSachsApp
	for _, model := range models {
		apps = append(apps, trwSachsApp{
			Make:     make,
			Model:    model,
			YearFrom: yearFrom,
			YearTo:   yearTo,
			Notes:    notes,
		})
	}

	return apps, currentMake
}

func parseYear(s string) int32 {
	if s == "" {
		return 0
	}
	y, err := strconv.Atoi(s)
	if err != nil {
		return 0
	}
	return int32(y)
}

// splitTrwSachsMakeModel identifies the make (from knownVehicleMakes with longest-match)
// and returns the remaining model tokens. It falls back to currentMake if no make is found.
var bmwModelPattern = regexp.MustCompile(`^(?i)(\d{3}[iI]|M\d+|X\d+|I\d+|Z\d+)$`)

func splitTrwSachsMakeModel(value, currentMake string) (make string, modelTokens []string, foundMake string) {
	value = strings.TrimSpace(strings.Trim(value, "-"))
	if value == "" {
		if currentMake != "" {
			return currentMake, nil, currentMake
		}
		return "", nil, ""
	}

	lowerValue := strings.ToLower(value)
	var matchedMake string
	var matchedLen int
	for _, candidate := range knownVehicleMakes {
		lowerMake := strings.ToLower(candidate)
		if lowerValue == lowerMake || strings.HasPrefix(lowerValue+" ", lowerMake+" ") {
			if len(candidate) > matchedLen {
				matchedMake = candidate
				matchedLen = len(candidate)
			}
		}
	}

	if matchedMake != "" {
		rest := strings.TrimSpace(strings.TrimLeft(value[len(matchedMake):], "- "))
		if rest == "" {
			// Make only with no model; inherit will happen on next token if any.
			return matchedMake, nil, matchedMake
		}
		return normalizeMercedesMake(matchedMake, strings.Fields(rest))
	}

	if currentMake != "" {
		return normalizeMercedesMake(currentMake, strings.Fields(value))
	}

	// No known make and no inherited make. Try BMW orphan model codes (118i, M3, X1, etc.).
	parts := strings.Fields(value)
	if len(parts) > 0 && bmwModelPattern.MatchString(parts[0]) {
		if len(parts) == 1 {
			return "BMW", []string{parts[0]}, "BMW"
		}
		return "BMW", parts, "BMW"
	}

	// Fallback: treat first word as make, but do NOT carry it over to later segments.
	if len(parts) == 0 {
		return "", nil, ""
	}
	if len(parts) == 1 {
		return parts[0], nil, ""
	}
	return parts[0], parts[1:], ""
}

// normalizeMercedesMake coerces "Mercedes" + model starting with "Benz" into
// "Mercedes-Benz" to avoid a fragmented vehicle brand.
func normalizeMercedesMake(make string, modelTokens []string) (string, []string, string) {
	if strings.EqualFold(make, "Mercedes") && len(modelTokens) > 0 && strings.EqualFold(modelTokens[0], "Benz") {
		return "Mercedes-Benz", modelTokens[1:], "Mercedes-Benz"
	}
	return make, modelTokens, make
}

func isUpperAlphaNumeric(s string) bool {
	for _, r := range s {
		if !((r >= 'A' && r <= 'Z') || (r >= '0' && r <= '9')) {
			return false
		}
	}
	return s != ""
}