package csvimporter

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

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

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

	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()
	header := records[0]
	var errorRows []ErrorRow

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

		categoria := row[0]
		item := row[1]
		descripcion := row[2]
		aplicaciones := row[3]
		tamano := row[4]
		piezas := row[5]
		precio := ""
		codigoSat := ""
		if len(row) > 6 {
			precio = row[6]
		}
		if len(row) > 7 {
			codigoSat = row[7]
		}

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

		notes := ""
		if tamano != "" {
			notes += "Tamano: " + tamano
		}
		if codigoSat != "" {
			if notes != "" {
				notes += " | "
			}
			notes += "SAT: " + codigoSat
		}
		if precio != "" {
			if notes != "" {
				notes += " | "
			}
			notes += "Precio: " + precio
		}
		if categoria != "" {
			if notes != "" {
				notes = "Categoria: " + categoria + " | " + notes
			} else {
				notes = "Categoria: " + categoria
			}
		}

		application := strings.TrimSpace(aplicaciones)

		product, err := imp.findOrCreateExtraProduct(ctx, item, descripcion, notes, application)
		if err != nil {
			result.Errors++
			errorRows = append(errorRows, ErrorRow{Row: row, Error: fmt.Sprintf("Failed to find/create product: %v", err), RowNum: rowNum})
			continue
		}

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

		piezasQty, _ := strconv.Atoi(piezas)
		if piezasQty > 0 {
			_, err = imp.queries.CreateExtraProductStock(ctx, catalogue.CreateExtraProductStockParams{
				Quantity:         pgtype.Int4{Int32: int32(piezasQty), Valid: true},
				FkIdExtraProduct: pgtype.Int4{Int32: product.ID, Valid: true},
			})
			if err != nil {
				result.Errors++
				errorRows = append(errorRows, ErrorRow{Row: row, Error: fmt.Sprintf("Failed to create stock: %v", err), RowNum: rowNum})
				continue
			}
		}

		result.Inserted++
		if i%50 == 0 {
			fmt.Printf("  Prestone: %d rows processed\n", i)
		}
	}

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

	return result, nil
}

func (imp *Importer) findOrCreateExtraProduct(ctx context.Context, itemType, name, notes, application string) (catalogue.ExtraTypeProduct, error) {
	if name != "" {
		product, err := imp.queries.GetExtraTypeProductByName(ctx, pgtype.Text{String: name, Valid: true})
		if err == nil {
			return product, nil
		}
	}

	product, err := imp.queries.CreateExtraTypeProduct(ctx, catalogue.CreateExtraTypeProductParams{
		Type:          pgtype.Text{String: itemType, Valid: itemType != ""},
		Notes:         pgtype.Text{String: notes, Valid: notes != ""},
		Name:          pgtype.Text{String: name, Valid: name != ""},
		Application:   pgtype.Text{String: application, Valid: application != ""},
		IDFktypeBrand: pgtype.Int4{Int32: 0, Valid: false},
	})
	if err != nil {
		return catalogue.ExtraTypeProduct{}, err
	}
	return product, nil
}

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

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

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

	created, err := imp.queries.CreateExtraTypeBrand(ctx, catalogue.CreateExtraTypeBrandParams{
		Brand:               pgtype.Text{String: brandName, Valid: true},
		Image:               pgtype.Text{String: "", Valid: false},
		IDFkProductCategory: pgtype.Int4{Int32: productID, Valid: true},
	})
	if err != nil {
		return 0, err
	}
	return created.ID, nil
}