package main

import (
	"context"
	"fmt"
	"log"
	"os"
	"strings"

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

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)
	}

	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 partNumber -> DefaultMfrLabel from monroe.xlsx Items sheet.
	seriesMap, err := loadMonroeSeriesMap("/app/docs/database/monroe.xlsx")
	if err != nil {
		log.Fatalf("Failed to load monroe.xlsx Items sheet: %v\n", err)
	}
	fmt.Printf("Loaded %d partNumber -> DefaultMfrLabel mappings\n", len(seriesMap))

	var totalAffected int64
	for pn, serie := range seriesMap {
		cmdTag, err := pool.Exec(ctx, `
			UPDATE "shock_brand_specs"
			SET "serie" = $1
			WHERE "fk_idShockBrand" = $2 AND "partNumber" = $3 AND ("serie" IS NULL OR "serie" = '')
		`, serie, monroeID, pn)
		if err != nil {
			log.Fatalf("Update partNumber %s failed: %v\n", pn, err)
		}
		totalAffected += cmdTag.RowsAffected()
	}
	fmt.Printf("Rows updated with serie: %d\n", totalAffected)

	// Summary.
	var withSerie, withoutSerie, total int64
	err = pool.QueryRow(ctx, `
		SELECT
			COUNT(*) FILTER (WHERE "serie" IS NOT NULL AND "serie" <> ''),
			COUNT(*) FILTER (WHERE "serie" IS NULL OR "serie" = ''),
			COUNT(*)
		FROM "shock_brand_specs"
		WHERE "fk_idShockBrand" = $1
	`, monroeID).Scan(&withSerie, &withoutSerie, &total)
	if err != nil {
		log.Fatalf("Summary query failed: %v\n", err)
	}
	fmt.Printf("Monroe rows total=%d with serie=%d without serie=%d\n", total, withSerie, withoutSerie)
}

func loadMonroeSeriesMap(xlsxPath string) (map[string]string, error) {
	f, err := excelize.OpenFile(xlsxPath)
	if err != nil {
		return nil, fmt.Errorf("open xlsx: %w", err)
	}
	defer f.Close()

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

	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 colIdx, v := range row {
					headerMap[strings.ToLower(strings.TrimSpace(v))] = colIdx
				}
				break
			}
		}
	}
	if headerRowIdx < 0 {
		return nil, fmt.Errorf("header row not found in Items sheet")
	}

	pnIdx, ok := headerMap["partnumber"]
	if !ok {
		return nil, fmt.Errorf("partnumber column not found in Items sheet")
	}
	labelIdx, ok := headerMap["defaultmfrlabel"]
	if !ok {
		return nil, fmt.Errorf("DefaultMfrLabel column not found in Items sheet")
	}

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

// compile-time guard: keep imports if not otherwise used.
var _ = pgconn.CommandTag{}