package csvimporter

import (
	"bufio"
	"context"
	"fmt"
	"os"
	"path/filepath"
	"regexp"
	"strconv"
	"strings"

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

const batchSize = 2000

var trailingYearRe = regexp.MustCompile(`\s*-\s*\d{2,4}\s*$`)

type Importer struct {
	queries               *catalogue.Queries
	vehicleModelYearCache map[string]int32
	shockSpecKeys         map[string]bool
	brakeSpecKeys         map[string]bool
	diskSpecKeys          map[string]bool
	strutSpecKeys         map[string]bool
	legSpecKeys           map[string]bool
}

func NewImporter(queries *catalogue.Queries) *Importer {
	return &Importer{
		queries:               queries,
		vehicleModelYearCache: make(map[string]int32),
	}
}

type ImportResult struct {
	Table      string
	Inserted   int
	Duplicates int
	Errors     int
}

func (imp *Importer) ImportAll(csvDir string) ([]ImportResult, error) {
	var results []ImportResult

	importIfExists := func(fn func(string) (ImportResult, error), file string) error {
		path := filepath.Join(csvDir, file)
		if _, err := os.Stat(path); os.IsNotExist(err) {
			fmt.Printf("  Skipping %s (file not found)\n", file)
			return nil
		}
		// Skip header-only CSVs (no data rows). A CSV with at least two
		// non-empty lines is considered to have data.
		if hasDataRows := func(p string) bool {
			f, err := os.Open(p)
			if err != nil {
				return false
			}
			defer f.Close()
			scanner := bufio.NewScanner(f)
			lines := 0
			for scanner.Scan() {
				if strings.TrimSpace(scanner.Text()) != "" {
					lines++
					if lines >= 2 {
						return true
					}
				}
			}
			return false
		}(path); !hasDataRows {
			fmt.Printf("  Skipping %s (header only)\n", file)
			return nil
		}
		result, err := fn(path)
		if err != nil {
			return err
		}
		results = append(results, result)
		return nil
	}

	if err := importIfExists(imp.ImportAGShock, "AG_shock.csv"); err != nil {
		return nil, err
	}
	if err := importIfExists(imp.ImportAGStrut, "AG_strut.csv"); err != nil {
		return nil, err
	}
	if err := importIfExists(imp.ImportAGLeg, "AG_leg.csv"); err != nil {
		return nil, err
	}
	if err := importIfExists(imp.ImportMonroeLD, "monroe_ld.csv"); err != nil {
		return nil, err
	}
	if err := importIfExists(imp.ImportMonroeHD, "monroe_hd.csv"); err != nil {
		return nil, err
	}
	if err := importIfExists(imp.ImportTRWSachs, "trw_sachs.csv"); err != nil {
		return nil, err
	}
	if err := importIfExists(imp.ImportGRCBrakes, "frenos-deseable.csv"); err != nil {
		return nil, err
	}
	if err := importIfExists(imp.ImportGRCShocksHeavy, "amort-deseable.csv"); err != nil {
		return nil, err
	}
	if err := importIfExists(imp.ImportGRCShocks, "grc-ligro-amort-deseable.csv"); err != nil {
		return nil, err
	}
	if err := importIfExists(imp.ImportCanoDisk, "cano_disk.csv"); err != nil {
		return nil, err
	}
	if err := importIfExists(imp.ImportWagner, "wagner_brake.csv"); err != nil {
		return nil, err
	}
	if err := importIfExists(imp.ImportGrobb, "grobb_shocks.csv"); err != nil {
		return nil, err
	}
	if err := importIfExists(imp.ImportFritec, "fritec_discs.csv"); err != nil {
		return nil, err
	}
	if err := importIfExists(imp.ImportBrembo, "brembo.csv"); err != nil {
		return nil, err
	}
	if err := importIfExists(imp.ImportPrestone, "prestone.csv"); err != nil {
		return nil, err
	}
	if err := importIfExists(imp.ImportPagidBrake, "pagid_brakes.csv"); err != nil {
		return nil, err
	}
	if err := importIfExists(imp.ImportPagidDisk, "pagid_discs.csv"); err != nil {
		return nil, err
	}
	if err := importIfExists(imp.ImportPagidSensor, "pagid_sensors.csv"); err != nil {
		return nil, err
	}

	return results, nil
}

func (imp *Importer) readCSV(filename string) ([][]string, error) {
	file, err := os.Open(filename)
	if err != nil {
		return nil, err
	}
	defer file.Close()

	scanner := bufio.NewScanner(file)
	var records [][]string
	for scanner.Scan() {
		line := scanner.Text()
		fields := strings.Split(line, "|")
		records = append(records, fields)
	}
	if err := scanner.Err(); err != nil {
		return nil, err
	}

	return records, nil
}

func (imp *Importer) findOrCreateVehicleBrand(ctx context.Context, brandName string) (int32, error) {
	brandName = strings.TrimLeft(brandName, ",.\\-;:/ \t\r\n")
	if brandName == "" || brandName == "MISSING" {
		brandName = "Unknown"
	}

	brands, err := imp.queries.GetVehicleBrands(ctx, catalogue.GetVehicleBrandsParams{Limit: 1000000000, Offset: 0})
	if err != nil {
		return 0, err
	}

	for _, b := range brands {
		if strings.EqualFold(b.Brand, brandName) {
			return b.ID, nil
		}
	}

	created, err := imp.queries.CreateVehicleBrand(ctx, brandName)
	if err != nil {
		return 0, err
	}
	return created.ID, nil
}

func (imp *Importer) findOrCreateVehicleType(ctx context.Context, typeName string) (int32, error) {
	if typeName == "" || typeName == "MISSING" {
		typeName = "ligeros"
	}

	switch strings.ToLower(typeName) {
	case "ligero", "ligeros":
		typeName = "Ligero"
	case "pesado", "pesados":
		typeName = "Pesado"
	default:
		typeName = "Ligero"
	}

	types, err := imp.queries.GetVehicleTypes(ctx, catalogue.GetVehicleTypesParams{Limit: 1000000000, Offset: 0})
	if err != nil {
		return 0, err
	}

	for _, t := range types {
		if strings.EqualFold(t.Type.String, typeName) {
			return t.ID, nil
		}
	}

	created, err := imp.queries.CreateVehicleType(ctx, pgtype.Text{String: typeName, Valid: true})
	if err != nil {
		return 0, err
	}
	return created.ID, nil
}

func (imp *Importer) findOrCreateVehicleModelYear(ctx context.Context, brandName, modelName string, vehicleTypeID int32) (int32, error) {
	if modelName == "" || modelName == "MISSING" {
		modelName = "Unknown"
	}

	modelName = strings.TrimSpace(modelName)
	cleanModel := strings.TrimSpace(trailingYearRe.ReplaceAllString(modelName, ""))

	brandID, err := imp.findOrCreateVehicleBrand(ctx, brandName)
	if err != nil {
		return 0, err
	}

	lookupNames := []string{modelName}
	if cleanModel != "" && cleanModel != modelName {
		lookupNames = append(lookupNames, cleanModel)
	}

	cacheKeyBase := strings.ToLower(brandName) + "|" + vehicleTypeIDKey(vehicleTypeID)

	for _, name := range lookupNames {
		cacheKey := cacheKeyBase + "|" + strings.ToLower(name)
		if id, ok := imp.vehicleModelYearCache[cacheKey]; ok {
			return id, nil
		}
	}

	models, err := imp.queries.GetVehicleModelYears(ctx, catalogue.GetVehicleModelYearsParams{Limit: 1000000000, Offset: 0})
	if err != nil {
		return 0, err
	}

	for _, name := range lookupNames {
		for _, m := range models {
			if strings.EqualFold(m.Model.String, name) &&
				m.FkIdVehicleBrand.Int32 == brandID &&
				m.FkIdVehicleType.Int32 == vehicleTypeID {
				cacheKey := cacheKeyBase + "|" + strings.ToLower(modelName)
				imp.vehicleModelYearCache[cacheKey] = m.ID
				return m.ID, nil
			}
		}
	}

	created, err := imp.queries.CreateVehicleModelYear(ctx, catalogue.CreateVehicleModelYearParams{
		Model:            pgtype.Text{String: modelName, Valid: true},
		FkIdVehicleBrand: pgtype.Int4{Int32: brandID, Valid: true},
		FkIdVehicleType:  pgtype.Int4{Int32: vehicleTypeID, Valid: true},
	})
	if err != nil {
		return 0, err
	}
	cacheKey := cacheKeyBase + "|" + strings.ToLower(modelName)
	imp.vehicleModelYearCache[cacheKey] = created.ID
	return created.ID, nil
}

func vehicleTypeIDKey(id int32) string {
	return strconv.Itoa(int(id))
}

func (imp *Importer) findOrCreateShockBrand(ctx context.Context, brandName string) (int32, error) {
	if brandName == "" || brandName == "MISSING" {
		brandName = "AG"
	}

	brands, err := imp.queries.GetShockBrands(ctx, catalogue.GetShockBrandsParams{Limit: 1000000000, Offset: 0})
	if err != nil {
		return 0, err
	}

	for _, b := range brands {
		if strings.EqualFold(b.Name, brandName) || strings.EqualFold(b.Brand, brandName) {
			return b.ID, nil
		}
	}

	created, err := imp.queries.CreateShockBrand(ctx, catalogue.CreateShockBrandParams{
		Name:  brandName,
		Brand: brandName,
	})
	if err != nil {
		return 0, err
	}
	return created.ID, nil
}

func (imp *Importer) findOrCreateBrakeBrand(ctx context.Context, brandName string) (int32, error) {
	if brandName == "" || brandName == "MISSING" {
		brandName = "GRC"
	}

	brands, err := imp.queries.GetBrakekBrands(ctx, catalogue.GetBrakekBrandsParams{Limit: 1000000000, Offset: 0})
	if err != nil {
		return 0, err
	}

	for _, b := range brands {
		if strings.EqualFold(b.Name, brandName) || strings.EqualFold(b.Brand, brandName) {
			return b.ID, nil
		}
	}

	created, err := imp.queries.CreateBrakeBrand(ctx, catalogue.CreateBrakeBrandParams{
		Name:  brandName,
		Brand: brandName,
	})
	if err != nil {
		return 0, err
	}
	return created.ID, nil
}

func (imp *Importer) findOrCreateStrutBrand(ctx context.Context, brandName string) (int32, error) {
	if brandName == "" || brandName == "MISSING" {
		brandName = "AG"
	}

	brands, err := imp.queries.GetStrutBrands(ctx, catalogue.GetStrutBrandsParams{Limit: 1000000000, Offset: 0})
	if err != nil {
		return 0, err
	}

	for _, b := range brands {
		if strings.EqualFold(b.Name, brandName) || strings.EqualFold(b.Brand, brandName) {
			return b.ID, nil
		}
	}

	created, err := imp.queries.CreateStrutBrand(ctx, catalogue.CreateStrutBrandParams{
		Name:  brandName,
		Brand: brandName,
	})
	if err != nil {
		return 0, err
	}
	return created.ID, nil
}

func (imp *Importer) findOrCreateLegBrand(ctx context.Context, brandName string) (int32, error) {
	if brandName == "" || brandName == "MISSING" {
		brandName = "AG"
	}

	brands, err := imp.queries.GetLegBrands(ctx, catalogue.GetLegBrandsParams{Limit: 1000000000, Offset: 0})
	if err != nil {
		return 0, err
	}

	for _, b := range brands {
		if strings.EqualFold(b.Name.String, brandName) || strings.EqualFold(b.Brand.String, brandName) {
			return b.ID, nil
		}
	}

	created, err := imp.queries.CreateLegBrand(ctx, catalogue.CreateLegBrandParams{
		Name:  pgtype.Text{String: brandName, Valid: true},
		Brand: pgtype.Text{String: brandName, Valid: true},
	})
	if err != nil {
		return 0, err
	}
	return created.ID, nil
}

func (imp *Importer) findOrCreateDiskBrand(ctx context.Context, brandName string) (int32, error) {
	if brandName == "" || brandName == "MISSING" {
		brandName = "Cano"
	}

	brands, err := imp.queries.GetDiskBrands(ctx, catalogue.GetDiskBrandsParams{Limit: 1000000000, Offset: 0})
	if err != nil {
		return 0, err
	}

	for _, b := range brands {
		if strings.EqualFold(b.Name, brandName) || strings.EqualFold(b.Brand, brandName) {
			return b.ID, nil
		}
	}

	created, err := imp.queries.CreateDiskBrand(ctx, catalogue.CreateDiskBrandParams{
		Name:  brandName,
		Brand: brandName,
	})
	if err != nil {
		return 0, err
	}
	return created.ID, nil
}

func (imp *Importer) parsePosition(pos string) string {
	if pos == "" || pos == "MISSING" {
		return ""
	}
	return pos
}

func (imp *Importer) parseYear(yearStr string) int32 {
	if yearStr == "" || yearStr == "MISSING" {
		return 0
	}
	year, err := strconv.Atoi(yearStr)
	if err != nil {
		return 0
	}
	return int32(year)
}

func normalizeHeaderKey(col string) string {
	s := strings.ToLower(col)
	s = strings.ReplaceAll(s, "á", "a")
	s = strings.ReplaceAll(s, "é", "e")
	s = strings.ReplaceAll(s, "í", "i")
	s = strings.ReplaceAll(s, "ó", "o")
	s = strings.ReplaceAll(s, "ú", "u")
	s = strings.ReplaceAll(s, "ñ", "n")
	s = strings.ReplaceAll(s, " ", "")
	s = strings.ReplaceAll(s, "_", "")
	return s
}

func specKey(partNumber string, vehicleModelYearID, brandID int32, position string) string {
	return fmt.Sprintf("%s|%d|%d|%s", partNumber, vehicleModelYearID, brandID, position)
}

func (imp *Importer) ensureShockSpecKeys(ctx context.Context, brandID int32) error {
	if imp.shockSpecKeys != nil {
		return nil
	}
	specs, err := imp.queries.GetShockBrandSpecsByBrand(ctx, pgtype.Int4{Int32: brandID, Valid: true})
	if err != nil {
		return err
	}
	imp.shockSpecKeys = make(map[string]bool, len(specs))
	for _, s := range specs {
		imp.shockSpecKeys[specKey(s.PartNumber.String, s.FkIdVehicleModelYear.Int32, s.FkIdShockBrand.Int32, s.Position.String)] = true
	}
	return nil
}

func (imp *Importer) ensureBrakeSpecKeys(ctx context.Context, brandID int32) error {
	if imp.brakeSpecKeys != nil {
		return nil
	}
	specs, err := imp.queries.GetBrakeBrandSpecsByBrand(ctx, pgtype.Int4{Int32: brandID, Valid: true})
	if err != nil {
		return err
	}
	imp.brakeSpecKeys = make(map[string]bool, len(specs))
	for _, s := range specs {
		imp.brakeSpecKeys[specKey(s.PartNumber.String, s.FkIdVehicleModelYear.Int32, s.FkIdBrakeBrand.Int32, s.Position.String)] = true
	}
	return nil
}

func (imp *Importer) ensureDiskSpecKeys(ctx context.Context, brandID int32) error {
	if imp.diskSpecKeys != nil {
		return nil
	}
	specs, err := imp.queries.GetDiskBrandSpecsByBrand(ctx, pgtype.Int4{Int32: brandID, Valid: true})
	if err != nil {
		return err
	}
	imp.diskSpecKeys = make(map[string]bool, len(specs))
	for _, s := range specs {
		imp.diskSpecKeys[specKey(s.PartNumber.String, s.FkIdVehicleModelYear.Int32, s.FkIdDiskBrand.Int32, s.Position.String)] = true
	}
	return nil
}

func (imp *Importer) ensureStrutSpecKeys(ctx context.Context, brandID int32) error {
	if imp.strutSpecKeys != nil {
		return nil
	}
	specs, err := imp.queries.GetStrutBrandSpecsByBrand(ctx, pgtype.Int4{Int32: brandID, Valid: true})
	if err != nil {
		return err
	}
	imp.strutSpecKeys = make(map[string]bool, len(specs))
	for _, s := range specs {
		imp.strutSpecKeys[specKey(s.PartNumber.String, s.FkIdVehicleModelYear.Int32, s.FkIdStrutBrand.Int32, s.Position.String)] = true
	}
	return nil
}

func (imp *Importer) ensureLegSpecKeys(ctx context.Context, brandID int32) error {
	if imp.legSpecKeys != nil {
		return nil
	}
	specs, err := imp.queries.GetLegBrandSpecsByBrand(ctx, pgtype.Int4{Int32: brandID, Valid: true})
	if err != nil {
		return err
	}
	imp.legSpecKeys = make(map[string]bool, len(specs))
	for _, s := range specs {
		imp.legSpecKeys[specKey(s.PartNumber.String, s.FkIdVehicleModelYear.Int32, s.FkIdLegBrand.Int32, s.Position.String)] = true
	}
	return nil
}

func (imp *Importer) insertShockBrandSpec(ctx context.Context, params catalogue.CreateShockBrandSpecParams, result *ImportResult) error {
	if err := imp.ensureShockSpecKeys(ctx, params.FkIdShockBrand.Int32); err != nil {
		return err
	}
	key := specKey(params.PartNumber.String, params.FkIdVehicleModelYear.Int32, params.FkIdShockBrand.Int32, params.Position.String)
	if imp.shockSpecKeys[key] {
		result.Duplicates++
		return nil
	}
	if _, err := imp.queries.CreateShockBrandSpec(ctx, params); err != nil {
		return err
	}
	imp.shockSpecKeys[key] = true
	result.Inserted++
	return nil
}

func (imp *Importer) insertBrakeBrandSpec(ctx context.Context, params catalogue.CreateBrakeBrandSpecParams, result *ImportResult) error {
	if err := imp.ensureBrakeSpecKeys(ctx, params.FkIdBrakeBrand.Int32); err != nil {
		return err
	}
	key := specKey(params.PartNumber.String, params.FkIdVehicleModelYear.Int32, params.FkIdBrakeBrand.Int32, params.Position.String)
	if imp.brakeSpecKeys[key] {
		result.Duplicates++
		return nil
	}
	if _, err := imp.queries.CreateBrakeBrandSpec(ctx, params); err != nil {
		return err
	}
	imp.brakeSpecKeys[key] = true
	result.Inserted++
	return nil
}

func (imp *Importer) insertDiskBrandSpec(ctx context.Context, params catalogue.CreateDiskBrandSpecParams, result *ImportResult) error {
	if err := imp.ensureDiskSpecKeys(ctx, params.FkIdDiskBrand.Int32); err != nil {
		return err
	}
	key := specKey(params.PartNumber.String, params.FkIdVehicleModelYear.Int32, params.FkIdDiskBrand.Int32, params.Position.String)
	if imp.diskSpecKeys[key] {
		result.Duplicates++
		return nil
	}
	if _, err := imp.queries.CreateDiskBrandSpec(ctx, params); err != nil {
		return err
	}
	imp.diskSpecKeys[key] = true
	result.Inserted++
	return nil
}

func (imp *Importer) insertStrutBrandSpec(ctx context.Context, params catalogue.CreateStrutBrandSpecParams, result *ImportResult) error {
	if err := imp.ensureStrutSpecKeys(ctx, params.FkIdStrutBrand.Int32); err != nil {
		return err
	}
	key := specKey(params.PartNumber.String, params.FkIdVehicleModelYear.Int32, params.FkIdStrutBrand.Int32, params.Position.String)
	if imp.strutSpecKeys[key] {
		result.Duplicates++
		return nil
	}
	if _, err := imp.queries.CreateStrutBrandSpec(ctx, params); err != nil {
		return err
	}
	imp.strutSpecKeys[key] = true
	result.Inserted++
	return nil
}

func (imp *Importer) insertLegBrandSpec(ctx context.Context, params catalogue.CreateLegBrandSpecParams, result *ImportResult) error {
	if err := imp.ensureLegSpecKeys(ctx, params.FkIdLegBrand.Int32); err != nil {
		return err
	}
	key := specKey(params.PartNumber.String, params.FkIdVehicleModelYear.Int32, params.FkIdLegBrand.Int32, params.Position.String)
	if imp.legSpecKeys[key] {
		result.Duplicates++
		return nil
	}
	if _, err := imp.queries.CreateLegBrandSpec(ctx, params); err != nil {
		return err
	}
	imp.legSpecKeys[key] = true
	result.Inserted++
	return nil
}

type parsedVehicleApplication struct {
	Make      string
	Model     string
	Year      string
	YearRange string
}

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

var knownVehicleMakes = []string{
	"Mercedes-Benz", "Alfa Romeo", "Aston Martin", "Land Rover", "Rolls-Royce",
	"Volkswagen", "Chevrolet", "International", "Mitsubishi", "Oldsmobile",
	"Mercedes", "Chrysler", "Cadillac", "Infiniti", "Plymouth", "Pontiac",
	"Porsche", "Renault", "Peugeot", "Citroen", "Hyundai", "Lincoln",
	"Mercury", "Subaru", "Suzuki", "Toyota", "Vauxhall", "Bentley",
	"Ferrari", "Maserati", "Acura", "Audi", "BMW", "Buick", "Dodge",
	"Eagle", "Fiat", "Ford", "GMC", "Honda", "Isuzu", "Jaguar", "Jeep",
	"Lexus", "Mazda", "Nissan", "Saab", "Seat", "Skoda", "Smart", "Volvo",
	"Mini", "Opel", "Kia", "VW", "Freightliner", "Chery",
}

func parseVehicleApplications(aplicacion string) []parsedVehicleApplication {
	if aplicacion == "" || aplicacion == "MISSING" {
		return nil
	}

	var vehicles []parsedVehicleApplication
	currentMake := ""
	for _, part := range strings.Split(aplicacion, ",") {
		part = strings.TrimSpace(part)
		if part == "" {
			continue
		}

		vehicle, makeForNext := parseVehicleApplication(part, currentMake)
		if vehicle.Make == "" || vehicle.Model == "" {
			continue
		}
		vehicles = append(vehicles, vehicle)
		if makeForNext != "" {
			currentMake = makeForNext
		}
	}

	return vehicles
}

func parseVehicleApplication(part, currentMake string) (parsedVehicleApplication, string) {
	year := ""
	yearRange := ""
	modelPart := part

	if loc := applicationYearPattern.FindStringSubmatchIndex(part); loc != nil {
		yearFrom := part[loc[2]:loc[3]]
		yearTo := ""
		if loc[4] >= 0 && loc[5] >= 0 {
			yearTo = normalizeYearEnd(yearFrom, part[loc[4]:loc[5]])
		}

		if yearTo != "" && yearTo != yearFrom {
			yearRange = yearFrom + "-" + yearTo
		} else {
			year = yearFrom
		}
		modelPart = strings.TrimSpace(part[:loc[0]])
	}

	make, model, foundMake := splitVehicleMakeModel(modelPart, currentMake)
	return parsedVehicleApplication{Make: make, Model: model, Year: year, YearRange: yearRange}, foundMake
}

func splitVehicleMakeModel(value, currentMake string) (string, string, string) {
	value = strings.TrimSpace(strings.Trim(value, "-"))
	if value == "" {
		return currentMake, "MISSING", currentMake
	}

	lowerValue := strings.ToLower(value)
	for _, make := range knownVehicleMakes {
		lowerMake := strings.ToLower(make)
		if lowerValue == lowerMake || strings.HasPrefix(lowerValue, lowerMake+" ") {
			model := strings.TrimSpace(strings.TrimLeft(value[len(make):], "- "))
			if model == "" {
				model = "MISSING"
			}
			return make, model, make
		}
	}

	if currentMake != "" {
		return currentMake, value, currentMake
	}

	parts := strings.Fields(value)
	if len(parts) == 0 {
		return "MISSING", "MISSING", ""
	}
	if len(parts) == 1 {
		return parts[0], "MISSING", parts[0]
	}
	return parts[0], strings.Join(parts[1:], " "), parts[0]
}

func normalizeYearEnd(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)
}