package main

import (
	"context"
	"encoding/csv"
	"fmt"
	"log"
	"os"
	"sort"
	"strconv"
	"strings"

	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgxpool"
	"github.com/xuri/excelize/v2"
)

// Monroe xlsx Position values (lowercased) -> GRC-style normalized position.
// These must be kept in sync with the positions used in the database.
var positionNormalize = map[string]string{
	"tras":         "Tras",
	"del":          "Del",
	"del l":        "Del Izq",
	"del r":        "Del Der",
	"tras l":       "Tras Izq",
	"tras r":       "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",
}

// canonicalPositionTokens returns the canonical position token keys sorted by
// length descending so the longest (e.g. "del / tras") wins over shorter ones
// (e.g. "tras").
func canonicalPositionTokens() []string {
	var tokens []string
	for k := range positionNormalize {
		if k == "" || k == "n/a" {
			continue
		}
		tokens = append(tokens, k)
	}
	sort.Slice(tokens, func(i, j int) bool {
		if len(tokens[i]) != len(tokens[j]) {
			return len(tokens[i]) > len(tokens[j])
		}
		return tokens[i] < tokens[j]
	})
	return tokens
}

func normalizePosition(raw string) string {
	key := strings.ToLower(strings.TrimSpace(raw))
	key = strings.Join(strings.Fields(key), " ")
	if norm, ok := positionNormalize[key]; ok {
		return norm
	}
	// If the raw value is not in the map, keep it verbatim (trimmed) rather
	// than losing information. The list can be extended later.
	return strings.TrimSpace(raw)
}

// extractPositionFromNotes finds the longest canonical position token that
// appears as a trailing word sequence in notes. If found it returns the
// normalized position and the notes with that trailing token removed.
func extractPositionFromNotes(notes string) (string, string) {
	notes = strings.TrimSpace(notes)
	if notes == "" {
		return "", ""
	}
	lowerNotes := strings.ToLower(notes)
	for _, token := range canonicalPositionTokens() {
		if strings.HasSuffix(lowerNotes, " "+token) {
			stripped := strings.TrimSpace(notes[:len(notes)-(len(token)+1)])
			return positionNormalize[token], stripped
		}
		if lowerNotes == token {
			return positionNormalize[token], ""
		}
	}
	return "", notes
}

// loadMonroePositionMap reads the monroe.xlsx Applications sheet and returns
// partNumber -> set of raw position values observed in the xlsx.
func loadMonroePositionMap(xlsxPath string) (map[string]map[string]struct{}, error) {
	f, err := excelize.OpenFile(xlsxPath)
	if err != nil {
		return nil, fmt.Errorf("open xlsx: %w", err)
	}
	defer f.Close()

	rows, err := f.GetRows("Applications")
	if err != nil {
		return nil, fmt.Errorf("read Applications sheet: %w", err)
	}
	if len(rows) == 0 {
		return nil, fmt.Errorf("empty Applications sheet")
	}

	// Find the header row and the Position column index.
	headerMap := make(map[string]int)
	headerRowIdx := -1
	for rowIdx, row := range rows {
		if headerRowIdx != -1 {
			break
		}
		if len(row) == 0 {
			continue
		}
		for _, val := range row {
			if strings.EqualFold(strings.TrimSpace(val), "partnumber") {
				headerRowIdx = rowIdx
				for ci, v := range row {
					headerMap[strings.ToLower(strings.TrimSpace(v))] = ci
				}
				break
			}
		}
	}
	if headerRowIdx < 0 {
		return nil, fmt.Errorf("partnumber header not found in Applications sheet")
	}
	pnIdx, ok := headerMap["partnumber"]
	if !ok {
		return nil, fmt.Errorf("partnumber column index not found")
	}
	posIdx, ok := headerMap["position"]
	if !ok {
		posIdx, ok = headerMap["pos"]
		if !ok {
			return nil, fmt.Errorf("position column not found in Applications sheet")
		}
	}

	result := make(map[string]map[string]struct{})
	for _, row := range rows[headerRowIdx+1:] {
		if len(row) <= pnIdx {
			continue
		}
		pn := strings.TrimSpace(row[pnIdx])
		if pn == "" {
			continue
		}
		var pos string
		if posIdx < len(row) {
			pos = strings.TrimSpace(row[posIdx])
		}
		if pos == "" {
			continue
		}
		if result[pn] == nil {
			result[pn] = make(map[string]struct{})
		}
		result[pn][pos] = struct{}{}
	}
	return result, nil
}

type rowUpdate struct {
	ID       int32
	Position string
	Notes    string
}

func main() {
	ctx := context.Background()
	connStr := os.Getenv("MIP_DB")
	if connStr == "" {
		connStr = "postgres://user:password@postgres_db:5432/mydatabase?sslmode=disable"
	}

	pool, err := pgxpool.New(ctx, connStr)
	if err != nil {
		log.Fatalf("Unable to connect to database: %v\n", err)
	}
	defer pool.Close()

	if err := pool.Ping(ctx); err != nil {
		log.Fatalf("Unable to ping database: %v\n", err)
	}

	// Find Monroe brand id.
	var monroeID int32
	err = pool.QueryRow(ctx, `SELECT id FROM "shock_brands" WHERE LOWER("name") = LOWER('Monroe')`).Scan(&monroeID)
	if err != nil {
		log.Fatalf("Monroe brand not found in shock_brands: %v\n", err)
	}
	fmt.Printf("Monroe shock_brands.id = %d\n", monroeID)

	// Load xlsx position map for fallback.
	xlsxPath := "/app/docs/database/monroe.xlsx"
	partPositions, err := loadMonroePositionMap(xlsxPath)
	if err != nil {
		log.Fatalf("Failed to load monroe.xlsx position map: %v\n", err)
	}

	// Build partNumber -> single normalized position map.
	singlePosition := make(map[string]string)
	multiPosition := make(map[string][]string)
	for pn, set := range partPositions {
		if len(set) == 1 {
			for raw := range set {
				singlePosition[pn] = normalizePosition(raw)
				break
			}
		} else {
			var list []string
			for raw := range set {
				list = append(list, raw)
			}
			multiPosition[pn] = list
		}
	}
	fmt.Printf("xlsx partNumber -> single position: %d, multi-position: %d\n", len(singlePosition), len(multiPosition))

	// Phase A: backfill from notes trailing position token.
	phaseA, err := pool.Query(ctx, `
		SELECT id, "partNumber", notes
		FROM "shock_brand_specs"
		WHERE "fk_idShockBrand" = $1
		  AND ("position" IS NULL OR "position" = '')
		  AND notes IS NOT NULL AND notes <> ''
		ORDER BY id
	`, monroeID)
	if err != nil {
		log.Fatalf("Phase A query failed: %v\n", err)
	}

	var phaseAUpdates []rowUpdate
	var phaseAEmpty int
	for phaseA.Next() {
		var id int32
		var partNumber, notes string
		if err := phaseA.Scan(&id, &partNumber, &notes); err != nil {
			log.Fatalf("Phase A scan failed: %v\n", err)
		}
		pos, stripped := extractPositionFromNotes(notes)
		if pos != "" {
			phaseAUpdates = append(phaseAUpdates, rowUpdate{ID: id, Position: pos, Notes: stripped})
		} else {
			phaseAEmpty++
		}
	}
	phaseA.Close()
	fmt.Printf("Phase A rows scanned: %d | with position tail: %d | without: %d\n", len(phaseAUpdates)+phaseAEmpty, len(phaseAUpdates), phaseAEmpty)

	affectedA, err := applyUpdates(ctx, pool, phaseAUpdates)
	if err != nil {
		log.Fatalf("Phase A updates failed: %v\n", err)
	}
	fmt.Printf("Phase A rows updated: %d\n", affectedA)

	// Phase B: fallback to partNumber -> single xlsx position.
	phaseB, err := pool.Query(ctx, `
		SELECT id, "partNumber"
		FROM "shock_brand_specs"
		WHERE "fk_idShockBrand" = $1
		  AND ("position" IS NULL OR "position" = '')
		ORDER BY id
	`, monroeID)
	if err != nil {
		log.Fatalf("Phase B query failed: %v\n", err)
	}

	var phaseBUpdates []rowUpdate
	var phaseBSkipped []string
	var phaseBMultiPositionPNs []string
	for phaseB.Next() {
		var id int32
		var partNumber string
		if err := phaseB.Scan(&id, &partNumber); err != nil {
			log.Fatalf("Phase B scan failed: %v\n", err)
		}
		pos, ok := singlePosition[partNumber]
		if ok && pos != "" {
			phaseBUpdates = append(phaseBUpdates, rowUpdate{ID: id, Position: pos})
		} else if _, isMulti := multiPosition[partNumber]; isMulti {
			phaseBMultiPositionPNs = append(phaseBMultiPositionPNs, partNumber)
		} else {
			phaseBSkipped = append(phaseBSkipped, partNumber)
		}
	}
	phaseB.Close()

	// Deduplicate multi-position log entries.
	phaseBMultiPositionPNs = uniqueStrings(phaseBMultiPositionPNs)
	phaseBSkipped = uniqueStrings(phaseBSkipped)

	fmt.Printf("Phase B rows scanned: %d | single-position updates: %d | multi-position skipped: %d | no xlsx position: %d\n",
		len(phaseBUpdates)+len(phaseBMultiPositionPNs)+len(phaseBSkipped), len(phaseBUpdates), len(phaseBMultiPositionPNs), len(phaseBSkipped))

	affectedB, err := applyUpdates(ctx, pool, phaseBUpdates)
	if err != nil {
		log.Fatalf("Phase B updates failed: %v\n", err)
	}
	fmt.Printf("Phase B rows updated: %d\n", affectedB)

	// Phase C: match remaining empty-position rows against the regenerated
	// Monroe CSV by (partNumber, make, model, years). This resolves the
	// multi-position parts, because each fitment row carries its own position.
	affectedC, duplicateWarnings, err := phaseCBackfillFromCSV(ctx, pool, monroeID, "/app/docs/database/csv/monroe_ld.csv")
	if err != nil {
		log.Fatalf("Phase C failed: %v\n", err)
	}
	fmt.Printf("Phase C rows updated: %d\n", affectedC)
	if len(duplicateWarnings) > 0 {
		fmt.Printf("Phase C duplicate matches (one CSV row matched multiple DB rows) (%d):\n", len(duplicateWarnings))
		for _, w := range duplicateWarnings[:min(50, len(duplicateWarnings))] {
			fmt.Printf("  %s\n", w)
		}
		if len(duplicateWarnings) > 50 {
			fmt.Printf("  ... and %d more\n", len(duplicateWarnings)-50)
		}
	}

	// Final summary.
	var remainingEmpty int64
	err = pool.QueryRow(ctx, `
		SELECT COUNT(*) FROM "shock_brand_specs"
		WHERE "fk_idShockBrand" = $1 AND ("position" IS NULL OR "position" = '')
	`, monroeID).Scan(&remainingEmpty)
	if err != nil {
		log.Fatalf("Final summary query failed: %v\n", err)
	}
	fmt.Printf("Remaining Monroe shocks with empty position: %d\n", remainingEmpty)

	if len(phaseBMultiPositionPNs) > 0 {
		fmt.Printf("Multi-position part numbers requiring manual review (%d):\n", len(phaseBMultiPositionPNs))
		for _, pn := range phaseBMultiPositionPNs[:min(50, len(phaseBMultiPositionPNs))] {
			fmt.Printf("  %s -> %v\n", pn, multiPosition[pn])
		}
		if len(phaseBMultiPositionPNs) > 50 {
			fmt.Printf("  ... and %d more\n", len(phaseBMultiPositionPNs)-50)
		}
	}
	if len(phaseBSkipped) > 0 {
		fmt.Printf("Part numbers with no xlsx position and no notes tail (%d):\n", len(phaseBSkipped))
		for _, pn := range phaseBSkipped[:min(50, len(phaseBSkipped))] {
			fmt.Printf("  %s\n", pn)
		}
		if len(phaseBSkipped) > 50 {
			fmt.Printf("  ... and %d more\n", len(phaseBSkipped)-50)
		}
	}
}

func applyUpdates(ctx context.Context, pool *pgxpool.Pool, updates []rowUpdate) (int64, error) {
	if len(updates) == 0 {
		return 0, nil
	}
	var affected int64
	for _, u := range updates {
			var cmdTag pgconn.CommandTag
		var err error
		if u.Notes == "" {
			cmdTag, err = pool.Exec(ctx, `
				UPDATE "shock_brand_specs"
				SET "position" = $1
				WHERE id = $2
			`, u.Position, u.ID)
		} else {
			cmdTag, err = pool.Exec(ctx, `
				UPDATE "shock_brand_specs"
				SET "position" = $1, notes = $2
				WHERE id = $3
			`, u.Position, u.Notes, u.ID)
		}
		if err != nil {
			return affected, fmt.Errorf("update id %d failed: %w", u.ID, err)
		}
		affected += cmdTag.RowsAffected()
	}
	return affected, nil
}

type csvRow struct {
	PartNumber string
	Make       string
	Model      string
	YearFrom   string
	YearTo     string
	Position   string
}

// parseYear mirrors the importer's parseYear helper.
func parseYear(yearStr string) int32 {
	yearStr = strings.TrimSpace(yearStr)
	if yearStr == "" || strings.EqualFold(yearStr, "MISSING") {
		return 0
	}
	n, err := strconv.Atoi(yearStr)
	if err != nil {
		return 0
	}
	return int32(n)
}

// phaseCBackfillFromCSV reads the regenerated Monroe CSV and fills position on
// remaining empty-position Monroe rows by matching partNumber + make + model +
// years/yearsRange. It returns the number of DB rows updated and a list of
// warnings for CSV rows that matched multiple DB rows.
func phaseCBackfillFromCSV(ctx context.Context, pool *pgxpool.Pool, monroeID int32, csvPath string) (int64, []string, error) {
	f, err := os.Open(csvPath)
	if err != nil {
		return 0, nil, fmt.Errorf("open CSV: %w", err)
	}
	defer f.Close()

	reader := csv.NewReader(f)
	reader.Comma = '|'
	records, err := reader.ReadAll()
	if err != nil {
		return 0, nil, fmt.Errorf("read CSV: %w", err)
	}
	if len(records) < 2 {
		return 0, nil, nil
	}

	var rows []csvRow
	for _, rec := range records[1:] {
		if len(rec) < 11 {
			continue
		}
		pos := strings.TrimSpace(rec[7])
		if pos == "" || strings.EqualFold(pos, "MISSING") {
			continue
		}
		make := strings.TrimSpace(rec[3])
		model := strings.TrimSpace(rec[4])
		if make == "" || strings.EqualFold(make, "MISSING") || model == "" || strings.EqualFold(model, "MISSING") {
			continue
		}
		rows = append(rows, csvRow{
			PartNumber: strings.TrimSpace(rec[0]),
			Make:       make,
			Model:      model,
			YearFrom:   strings.TrimSpace(rec[5]),
			YearTo:     strings.TrimSpace(rec[6]),
			Position:   pos,
		})
	}

	var affected int64
	var duplicateWarnings []string
	seenCSVRows := make(map[string]bool)

	const updateSQL = `
		UPDATE "shock_brand_specs" s
		SET "position" = $1
		FROM "vehicle_model_year" vmy, "vehicle_brands" vb
		WHERE s."fk_idVehicleModelYear" = vmy.id
		  AND vmy."fk_idVehicleBrand" = vb.id
		  AND s."fk_idShockBrand" = $2
		  AND s."partNumber" = $3
		  AND vb.brand = $4
		  AND vmy.model = $5
		  AND (
		    (s.years IS NOT NULL AND s.years <> '' AND s.years = $6)
		    OR (s."yearsRange" IS NOT NULL AND s."yearsRange" <> '' AND s."yearsRange" = $7)
		  )
		  AND (s."position" IS NULL OR s."position" = '')
	`

	for _, r := range rows {
		yearFrom := parseYear(r.YearFrom)
		yearTo := parseYear(r.YearTo)
		var yearsStr, yearsRangeStr string
		if yearFrom > 0 {
			if yearTo > 0 && yearTo != yearFrom {
				yearsRangeStr = fmt.Sprintf("%d,%d", yearFrom, yearTo)
			} else {
				yearsStr = fmt.Sprintf("%d", yearFrom)
			}
		}

		csvKey := fmt.Sprintf("%s|%s|%s|%s|%s", r.PartNumber, r.Make, r.Model, yearsStr, yearsRangeStr)
		if seenCSVRows[csvKey] {
			// Exact duplicate CSV row; skip to avoid double-counting.
			continue
		}
		seenCSVRows[csvKey] = true

		cmdTag, err := pool.Exec(ctx, updateSQL,
			normalizePosition(r.Position), monroeID, r.PartNumber, r.Make, r.Model, yearsStr, yearsRangeStr,
		)
		if err != nil {
			return affected, duplicateWarnings, fmt.Errorf("update %s failed: %w", r.PartNumber, err)
		}
		rowsAffected := cmdTag.RowsAffected()
		affected += rowsAffected
		if rowsAffected > 1 {
			duplicateWarnings = append(duplicateWarnings,
				fmt.Sprintf("%s (%s %s %s-%s) matched %d DB rows", r.PartNumber, r.Make, r.Model, r.YearFrom, r.YearTo, rowsAffected),
			)
		}
	}

	return affected, duplicateWarnings, nil
}

func uniqueStrings(in []string) []string {
	seen := make(map[string]struct{})
	var out []string
	for _, s := range in {
		if _, ok := seen[s]; ok {
			continue
		}
		seen[s] = struct{}{}
		out = append(out, s)
	}
	sort.Strings(out)
	return out
}

func min(a, b int) int {
	if a < b {
		return a
	}
	return b
}