package server

import (
	"context"
	"crypto/tls"
	"encoding/json"
	"errors"
	"fmt"
	"html/template"
	"log"
	"mime"
	"net"
	"net/http"
	"net/mail"
	"net/smtp"
	"net/url"
	"os"
	"path/filepath"
	"regexp"
	"sort"
	"strconv"
	"strings"
	"time"

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

type TemplateRenderer struct {
	templates *template.Template
	queries   *catalogue.Queries
	db        catalogue.DBTX
}

func NewTemplateRenderer(db catalogue.DBTX, queries *catalogue.Queries, templateDir string) (*TemplateRenderer, error) {
	funcMap := template.FuncMap{
		"ucfirst": func(s string) string {
			if len(s) == 0 {
				return s
			}
			return strings.ToUpper(s[:1]) + s[1:]
		},
		"seq": func(count int) []int {
			s := make([]int, count)
			for i := 1; i <= count; i++ {
				s[i-1] = i
			}
			return s
		},
		"add": func(a, b int) int { return a + b },
		"sub": func(a, b int) int { return a - b },
		"contains": func(slice []string, val string) bool {
			for _, s := range slice {
				if s == val {
					return true
				}
			}
			return false
		},
		"hasPrefix": strings.HasPrefix,
		"stripProductSuffix": func(product, serie string) string {
			product = strings.TrimSpace(product)
			serie = strings.TrimSpace(serie)
			if serie == "" || product == "" {
				return serie
			}
			lp := strings.ToLower(product)
			ls := strings.ToLower(serie)
			if ls == lp {
				return ""
			}
			if strings.HasSuffix(ls, " "+lp) {
				return strings.TrimSpace(serie[:len(serie)-(len(product)+1)])
			}
			if strings.Contains(lp, ls) {
				return strings.ReplaceAll(ls, lp, "")
			}
			return serie
		},
	}

	tmpl, err := template.New("").Funcs(funcMap).ParseGlob(filepath.Join(templateDir, "*.tmpl"))
	if err != nil {
		return nil, fmt.Errorf("failed to parse templates: %w", err)
	}

	return &TemplateRenderer{
		db:        db,
		templates: tmpl,
		queries:   queries,
	}, nil
}

func SetupTemplateRoutes(r *gin.Engine, renderer *TemplateRenderer) {
	r.GET("/", renderer.IndexPage)
	//r.GET("/nosotros", renderer.NosotrosPage)
	r.GET("/contacto", renderer.ContactoPage)
	r.POST("/contacto", renderer.ContactoSubmit)
	r.GET("/resultados", renderer.ResultadosPage)
	r.GET("/product/:type/:part_number", renderer.ProductDetail)
	r.GET("/api/brands", renderer.GetBrands)
	r.GET("/api/models", renderer.GetModels)
	r.GET("/api/years", renderer.GetYears)
	r.GET("/api/product/price", renderer.GetProductPrice)
	r.GET("/api/product/price-tag", renderer.GetProductPriceTag)
	r.GET("/api/product/stock", renderer.GetProductStock)
	r.GET("/api/product/bodegas", renderer.GetProductBodegas)
	r.GET("/api/product/lookup", renderer.GetProductLookup)
	r.GET("/api/filtered-brands", renderer.GetFilteredBrands)
	r.GET("/api/filtered-models", renderer.GetFilteredModels)
	r.GET("/api/results/chunk", renderer.ResultsChunk)
}

type SlideHome struct {
	Subtitulo   string
	Titulo      string
	Descripcion string
	TextoBoton  string
	URLBoton    string
	ImagenURL   string
}

type Marca struct {
	Nombre  string
	Link    string
	LogoURL string
}

func strPtr(s *string) string {
	if s == nil {
		return ""
	}
	return *s
}

func (tr *TemplateRenderer) IndexPage(c *gin.Context) {
	var slides []SlideHome
	var marcas []Marca

	resp, err := http.Get("http://strapi_cms:1313/api/slide-homes?populate=*&sort=createdAt:asc")
	if err == nil {
		defer resp.Body.Close()
		var result struct {
			Data []struct {
				Subtitulo   *string `json:"subtitulo"`
				Titulo      *string `json:"titulo"`
				Descripcion *string `json:"descripcion"`
				TextoBoton  *string `json:"texto_boton"`
				URLBoton    *string `json:"url_boton"`
				Imagen      *struct {
					URL string `json:"url"`
				} `json:"imagen"`
			} `json:"data"`
		}
		if err := json.NewDecoder(resp.Body).Decode(&result); err == nil {
			for _, item := range result.Data {
				imgURL := ""
				if item.Imagen != nil {
					imgURL = "https://cms.mipdsa.com" + item.Imagen.URL
				}
				slides = append(slides, SlideHome{
					Subtitulo:   strPtr(item.Subtitulo),
					Titulo:      strPtr(item.Titulo),
					Descripcion: strPtr(item.Descripcion),
					TextoBoton:  strPtr(item.TextoBoton),
					URLBoton:    strPtr(item.URLBoton),
					ImagenURL:   imgURL,
				})
			}
		}
	}

	resp2, err := http.Get("http://strapi_cms:1313/api/marcas?populate=*")
	if err == nil {
		defer resp2.Body.Close()
		var result2 struct {
			Data []struct {
				Nombre *string `json:"nombre"`
				Link   *string `json:"link"`
				Logo   *struct {
					URL string `json:"url"`
				} `json:"logo"`
			} `json:"data"`
		}
		if err := json.NewDecoder(resp2.Body).Decode(&result2); err == nil {
			for _, item := range result2.Data {
				logoURL := ""
				if item.Logo != nil {
					logoURL = "https://cms.mipdsa.com" + item.Logo.URL
				}
				marcas = append(marcas, Marca{
					Nombre:  strPtr(item.Nombre),
					Link:    strPtr(item.Link),
					LogoURL: logoURL,
				})
			}
		}
	}

	c.Header("Content-Type", "text/html; charset=utf-8")
	tr.templates.ExecuteTemplate(c.Writer, "index.tmpl", map[string]interface{}{
		"Title":  "MIPDSA - Catálogo de Autopartes",
		"Slides": slides,
		"Marcas": marcas,
	})
}

func (tr *TemplateRenderer) NosotrosPage(c *gin.Context) {
	c.Header("Content-Type", "text/html; charset=utf-8")
	tr.templates.ExecuteTemplate(c.Writer, "nosotros.tmpl", map[string]interface{}{
		"Title": "Nosotros - MIPDSA",
	})
}

type ContactoForm struct {
	Firstname string
	Phone     string
	Email     string
	Subject   string
	Message   string
}

type ContactoData struct {
	Title string
	Sent  bool
	Error string
	Form  ContactoForm
}

func (tr *TemplateRenderer) ContactoPage(c *gin.Context) {
	c.Header("Content-Type", "text/html; charset=utf-8")
	data := ContactoData{Title: "Contacto - MIPDSA"}
	if c.Query("sent") == "1" {
		data.Sent = true
	}
	tr.templates.ExecuteTemplate(c.Writer, "contacto.tmpl", data)
}

func (tr *TemplateRenderer) ContactoSubmit(c *gin.Context) {
	c.Header("Content-Type", "text/html; charset=utf-8")
	form := ContactoForm{
		Firstname: strings.TrimSpace(c.PostForm("firstname")),
		Phone:     strings.TrimSpace(c.PostForm("phone")),
		Email:     strings.TrimSpace(c.PostForm("email")),
		Subject:   strings.TrimSpace(c.PostForm("subject")),
		Message:   strings.TrimSpace(c.PostForm("message")),
	}
	data := ContactoData{Title: "Contacto - MIPDSA", Form: form}

	if err := validateContactoForm(form); err != nil {
		data.Error = err.Error()
		tr.templates.ExecuteTemplate(c.Writer, "contacto.tmpl", data)
		return
	}

	if err := sendContactEmail(form); err != nil {
		log.Printf("failed to send contact email: %v", err)
		data.Error = "No se pudo enviar el mensaje. Inténtalo de nuevo más tarde."
		tr.templates.ExecuteTemplate(c.Writer, "contacto.tmpl", data)
		return
	}

	c.Redirect(http.StatusSeeOther, "/contacto?sent=1")
}

func validateContactoForm(f ContactoForm) error {
	if f.Firstname == "" || f.Phone == "" || f.Email == "" || f.Subject == "" || f.Message == "" {
		return errors.New("Todos los campos son obligatorios.")
	}
	if _, err := mail.ParseAddress(f.Email); err != nil {
		return errors.New("El correo electrónico no es válido.")
	}
	if len(f.Firstname) > 100 {
		return errors.New("El nombre es demasiado largo.")
	}
	if len(f.Phone) > 50 {
		return errors.New("El teléfono es demasiado largo.")
	}
	if len(f.Email) > 254 {
		return errors.New("El correo electrónico es demasiado largo.")
	}
	if len(f.Subject) > 200 {
		return errors.New("El asunto es demasiado largo.")
	}
	if len(f.Message) > 5000 {
		return errors.New("El mensaje es demasiado largo.")
	}
	return nil
}

func sendContactEmail(f ContactoForm) error {
	host := os.Getenv("SMTP_HOST")
	if host == "" {
		host = "mail.mipdsa.com"
	}
	port := os.Getenv("SMTP_PORT")
	if port == "" {
		port = "465"
	}
	user := os.Getenv("SMTP_USER")
	pass := os.Getenv("SMTP_PASS")
	to := os.Getenv("CONTACT_TO")
	if to == "" {
		to = user
	}
	if to == "" {
		return errors.New("CONTACT_TO not configured")
	}

	from := mail.Address{Name: "MIPDSA Web", Address: user}
	if from.Address == "" {
		from.Address = to
	}
	replyTo := mail.Address{Address: f.Email}

	subject := fmt.Sprintf("[Contacto web] %s", f.Subject)
	encodedSubject := mime.QEncoding.Encode("utf-8", subject)
	body := fmt.Sprintf(
		"Nombre: %s\nTeléfono: %s\nCorreo: %s\nAsunto: %s\n\nMensaje:\n%s\n",
		f.Firstname, f.Phone, f.Email, f.Subject, f.Message,
	)

	msg := []byte(fmt.Sprintf(
		"To: %s\r\nFrom: %s\r\nReply-To: %s\r\nSubject: %s\r\nContent-Type: text/plain; charset=UTF-8\r\n\r\n%s",
		to, from.String(), replyTo.String(), encodedSubject, body,
	))

	addr := fmt.Sprintf("%s:%s", host, port)

	var client *smtp.Client
	var err error

	if port == "465" {
		conn, err := tls.Dial("tcp", addr, &tls.Config{ServerName: host})
		if err != nil {
			return err
		}
		defer conn.Close()
		client, err = smtp.NewClient(conn, host)
		if err != nil {
			return err
		}
	} else {
		conn, err := net.Dial("tcp", addr)
		if err != nil {
			return err
		}
		defer conn.Close()
		client, err = smtp.NewClient(conn, host)
		if err != nil {
			return err
		}
		if ok, _ := client.Extension("STARTTLS"); ok {
			config := &tls.Config{ServerName: host}
			if err := client.StartTLS(config); err != nil {
				return err
			}
		}
	}
	defer client.Close()

	if user != "" && pass != "" {
		auth := smtp.PlainAuth("", user, pass, host)
		if err := client.Auth(auth); err != nil {
			return err
		}
	}

	if err := client.Mail(from.Address); err != nil {
		return err
	}
	if err := client.Rcpt(to); err != nil {
		return err
	}
	w, err := client.Data()
	if err != nil {
		return err
	}
	if _, err := w.Write(msg); err != nil {
		return err
	}
	if err := w.Close(); err != nil {
		return err
	}
	return client.Quit()
}

const (
	PageSize  = 20
	ChunkSize = 10
)

type SearchResultItem struct {
	Type           string   `json:"type"`
	ID             int32    `json:"id"`
	PartNumber     string   `json:"partNumber"`
	Product        string   `json:"product"`
	Serie          string   `json:"serie"`
	Position       string   `json:"position"`
	Tier           string   `json:"tier"`
	ImageProduct   string   `json:"imageProduct"`
	ImageFallbacks []string `json:"imageFallbacks"`
	BrandName      string   `json:"brandName"`
	VehicleType    string   `json:"vehicleType"`
}

type FilterCount struct {
	Value string
	Label string
	Count int
}

type SidebarFilters struct {
	ProductTypes  []FilterCount
	ProductBrands []FilterCount
}

type ResultadosData struct {
	Title               string
	Results             []SearchResultItem
	Summary             string
	TotalCount          int
	Page                int
	PageSize            int
	TotalPages          int
	BaseQuery           string
	SelectedMake        string
	SelectedModel       string
	SelectedYear        string
	SelectedVehicleType string
	SelectedTypes       []string
	SelectedBrands      []string
	Filters             SidebarFilters
	FiltersReady        bool
	Streaming           bool
	ChunkReq            string
	RenderID            string
	PartNumberMode      bool
	PartNumber          string
}

// grcBrakeDiagramURL returns the URL for a GRC brake specs diagram image if it
// exists on disk, otherwise an empty string. The backend has the same assets
// directory mounted that the /assets static route serves.
func grcBrakeDiagramURL(partNumber string) string {
	if partNumber == "" {
		return ""
	}
	rel := fmt.Sprintf("frontend/assets/img/product/grc/brakes/diagrams/%s.webp", partNumber)
	if _, err := os.Stat(rel); err != nil {
		return ""
	}
	return fmt.Sprintf("/assets/img/product/grc/brakes/diagrams/%s.webp", partNumber)
}

// productImageFallbacks returns the ordered list of local canonical image URLs
// to use as tier-2 sources inside the <object> fallback chain. Tier 1 is the
// DB imageProduct value; the final tier is the per-type placeholder SVG.
func productImageFallbacks(brandName, ptype, partNumber, position string) []string {
	if partNumber == "" {
		return nil
	}
	brand := strings.ToLower(strings.TrimSpace(brandName))

	// Cano uses <pn>_front.webp / <pn>_rear.webp ordered by position.
	if brand == "cano" && ptype == "disk" {
		base := "/assets/img/product/cano/" + partNumber
		front := base + "_front.webp"
		rear := base + "_rear.webp"
		pos := strings.ToLower(strings.TrimSpace(position))
		// Prefer the side that matches the position, but try the other side next.
		if strings.Contains(pos, "tras") || strings.Contains(pos, "rear") {
			return []string{rear, front}
		}
		if strings.Contains(pos, "del") || strings.Contains(pos, "front") {
			return []string{front, rear}
		}
		return []string{front, rear}
	}

	var baseDir string
	switch ptype {
	case "shock", "strut", "leg":
		if brand == "grc" {
			baseDir = "grc/shocks"
		}
	case "brake":
		switch brand {
		case "grc":
			baseDir = "grc/brakes"
		case "brembo":
			baseDir = "brembo/brakes"
		}
	case "disk":
		if brand == "brembo" {
			baseDir = "brembo/discs"
		}
	}
	if baseDir == "" {
		return nil
	}
	return []string{"/assets/img/product/" + baseDir + "/" + partNumber + ".webp"}
}

// newRenderID returns a short unique id identifying one results render so the
// frontend can discard late responses belonging to an older, replaced render.
func newRenderID() string {
	return strconv.FormatInt(time.Now().UnixNano(), 36)
}

func cleanFilters(vals []string) []string {
	var out []string
	for _, v := range vals {
		v = strings.TrimSpace(v)
		if v == "" || v == "todos" {
			continue
		}
		out = append(out, v)
	}
	return out
}

func (tr *TemplateRenderer) buildQueryString(c *gin.Context, overrides map[string]string) string {
	params := url.Values{}
	for k, vs := range c.Request.URL.Query() {
		for _, v := range vs {
			if v != "" {
				params.Add(k, v)
			}
		}
	}
	for k, v := range overrides {
		if v == "" {
			params.Del(k)
		} else {
			params.Set(k, v)
		}
	}
	return params.Encode()
}

func (tr *TemplateRenderer) renderResultados(c *gin.Context, data ResultadosData) {
	data.Title = "Resultados de Catálogo - MIPDSA"
	c.Header("Content-Type", "text/html; charset=utf-8")
	if c.GetHeader("HX-Request") != "" {
		if err := tr.templates.ExecuteTemplate(c.Writer, "results_partial.tmpl", data); err != nil {
			fmt.Printf("ERROR rendering results_partial.tmpl: %v\n", err)
		}
		return
	}
	if err := tr.templates.ExecuteTemplate(c.Writer, "resultados.tmpl", data); err != nil {
		fmt.Printf("ERROR rendering resultados.tmpl: %v\n", err)
	}
}

func (tr *TemplateRenderer) ResultadosPage(c *gin.Context) {
	makeName := c.Query("make")
	model := c.Query("model")
	year := c.Query("year")
	vehicleType := c.Query("vehicle_type")
	selectedTypes := expandTypeGroups(cleanFilters(c.QueryArray("type")))
	selectedBrands := cleanFilters(c.QueryArray("brand"))
	partNumber := strings.TrimSpace(c.Query("part_number"))

	page, _ := strconv.Atoi(c.DefaultQuery("page", "1"))
	if page < 1 {
		page = 1
	}

	if partNumber != "" {
		mode, term := parsePartNumberQuery(partNumber)
		tr.resultadosByPartNumber(c, partNumber, mode, term, page)
		return
	}

	if makeName == "" || model == "" {
		data := ResultadosData{
			Summary:             "Selecciona un vehículo para ver resultados.",
			SelectedVehicleType: vehicleType,
			SelectedMake:        makeName,
			SelectedModel:       model,
			SelectedYear:        year,
			SelectedTypes:       selectedTypes,
			SelectedBrands:      selectedBrands,
			FiltersReady:        true,
			Page:                1,
			PageSize:            PageSize,
			TotalPages:          1,
		}
		tr.renderResultados(c, data)
		return
	}

	if page == 1 {
		rid := newRenderID()
		chunkReq := tr.buildChunkURL(rid, makeName, model, year, vehicleType, selectedTypes, selectedBrands, 0, ChunkSize, 1)
		data := ResultadosData{
			Summary:             "Cargando resultados…",
			SelectedMake:        makeName,
			SelectedModel:       model,
			SelectedYear:        year,
			SelectedVehicleType: vehicleType,
			SelectedTypes:       selectedTypes,
			SelectedBrands:      selectedBrands,
			FiltersReady:        false,
			Streaming:           true,
			ChunkReq:            chunkReq,
			RenderID:            rid,
			Page:                1,
			PageSize:            PageSize,
			TotalPages:          1,
		}
		tr.renderResultados(c, data)
		return
	}

	ctx := c.Request.Context()
	offset := (page - 1) * PageSize
	rows, _ := tr.searchCatalogue(ctx, makeName, model, year, vehicleType, selectedTypes, selectedBrands, PageSize, offset)
	for i := range rows {
		rows[i].VehicleType = vehicleType
	}
	total, _ := tr.countCatalogue(ctx, makeName, model, year, vehicleType, selectedTypes, selectedBrands)
	totalPages := (total + PageSize - 1) / PageSize
	if totalPages < 1 {
		totalPages = 1
	}
	if page > totalPages {
		page = totalPages
	}

	isHX := c.GetHeader("HX-Request") != ""
	var filters SidebarFilters
	if !isHX {
		filters, _ = tr.computeSidebarFilters(ctx, makeName, model, year, selectedTypes, selectedBrands)
	}

	baseQuery := tr.buildQueryString(c, map[string]string{"page": ""})
	summary := fmt.Sprintf("%d resultados para %s %s", total, makeName, model)
	if year != "" {
		summary += " (" + year + ")"
	}
	data := ResultadosData{
		Results:             rows,
		Summary:             summary,
		TotalCount:          total,
		Page:                page,
		PageSize:            PageSize,
		TotalPages:          totalPages,
		BaseQuery:           baseQuery,
		SelectedMake:        makeName,
		SelectedModel:       model,
		SelectedYear:        year,
		SelectedVehicleType: vehicleType,
		SelectedTypes:       selectedTypes,
		SelectedBrands:      selectedBrands,
		Filters:             filters,
		FiltersReady:        !isHX,
		Streaming:           false,
	}
	tr.renderResultados(c, data)
}

func (tr *TemplateRenderer) resultadosByPartNumber(c *gin.Context, partNumber, mode, term string, page int) {
	ctx := c.Request.Context()
	pattern := "%" + term + "%"

	if page == 1 {
		rid := newRenderID()
		chunkReq := tr.buildPartNumberChunkURL(rid, partNumber, 0, ChunkSize, 1)
		data := ResultadosData{
			Summary:        "Cargando resultados…",
			PartNumber:     partNumber,
			PartNumberMode: true,
			Streaming:      true,
			ChunkReq:       chunkReq,
			RenderID:       rid,
			Page:           1,
			PageSize:       PageSize,
			TotalPages:     1,
		}
		tr.renderResultados(c, data)
		return
	}

	var total int
	switch mode {
	case "fmsi":
		total, _ = tr.countByFmsi(ctx, pattern)
	default:
		total, _ = tr.countByPartNumberRelated(ctx, pattern)
	}

	totalPages := (total + PageSize - 1) / PageSize
	if totalPages < 1 {
		totalPages = 1
	}
	if page > totalPages {
		page = totalPages
	}
	offset := (page - 1) * PageSize

	var rows []SearchResultItem
	switch mode {
	case "fmsi":
		rows, _ = tr.searchByFmsi(ctx, pattern, PageSize, offset)
	default:
		rows, _ = tr.searchByPartNumberRelated(ctx, pattern, PageSize, offset)
	}

	baseQ := url.Values{}
	baseQ.Set("part_number", partNumber)

	summary := fmt.Sprintf("%d resultados para la pieza «%s»", total, term)
	if mode == "fmsi" {
		summary = fmt.Sprintf("%d resultados para FMSI «%s»", total, term)
	}

	data := ResultadosData{
		Results:        rows,
		Summary:        summary,
		TotalCount:     total,
		Page:           page,
		PageSize:       PageSize,
		TotalPages:     totalPages,
		BaseQuery:      baseQ.Encode(),
		PartNumber:     partNumber,
		PartNumberMode: true,
		Filters:        SidebarFilters{},
		FiltersReady:   false,
		Streaming:      false,
	}
	tr.renderResultados(c, data)
}

// buildChunkURL assembles the next chunk request URL. rid identifies the
// render that owns the stream so stale responses can be discarded.
func (tr *TemplateRenderer) buildChunkURL(rid, make, model, year, vehicleType string, types, brands []string, offset, limit, page int) string {
	params := url.Values{}
	params.Set("make", make)
	params.Set("model", model)
	if year != "" {
		params.Set("year", year)
	}
	if vehicleType != "" {
		params.Set("vehicle_type", vehicleType)
	}
	for _, t := range types {
		params.Add("type", t)
	}
	for _, b := range brands {
		params.Add("brand", b)
	}
	params.Set("offset", strconv.Itoa(offset))
	params.Set("limit", strconv.Itoa(limit))
	params.Set("page", strconv.Itoa(page))
	params.Set("rid", rid)
	return "/api/results/chunk?" + params.Encode()
}

func (tr *TemplateRenderer) buildPartNumberChunkURL(rid, partNumber string, offset, limit, page int) string {
	params := url.Values{}
	params.Set("part_number", partNumber)
	params.Set("offset", strconv.Itoa(offset))
	params.Set("limit", strconv.Itoa(limit))
	params.Set("page", strconv.Itoa(page))
	params.Set("rid", rid)
	return "/api/results/chunk?" + params.Encode()
}

func (tr *TemplateRenderer) ResultsChunk(c *gin.Context) {
	makeName := c.Query("make")
	model := c.Query("model")
	year := c.Query("year")
	vehicleType := c.Query("vehicle_type")
	types := expandTypeGroups(cleanFilters(c.QueryArray("type")))
	brands := cleanFilters(c.QueryArray("brand"))
	partNumber := strings.TrimSpace(c.Query("part_number"))
	offset, _ := strconv.Atoi(c.DefaultQuery("offset", "0"))
	if offset < 0 {
		offset = 0
	}
	limit, _ := strconv.Atoi(c.DefaultQuery("limit", strconv.Itoa(ChunkSize)))
	if limit < 1 {
		limit = ChunkSize
	}
	page, _ := strconv.Atoi(c.DefaultQuery("page", "1"))
	if page < 1 {
		page = 1
	}

	if partNumber != "" {
		mode, term := parsePartNumberQuery(partNumber)
		tr.resultsChunkByPartNumber(c, partNumber, mode, term, limit, offset, page)
		return
	}

	if makeName == "" || model == "" {
		c.String(http.StatusOK, "")
		return
	}

	ctx := c.Request.Context()
	rows, err := tr.searchCatalogue(ctx, makeName, model, year, vehicleType, types, brands, limit, offset)
	if err != nil {
		rows = []SearchResultItem{}
	}
	for i := range rows {
		rows[i].VehicleType = vehicleType
	}

	c.Header("Content-Type", "text/html; charset=utf-8")
	for _, r := range rows {
		tr.templates.ExecuteTemplate(c.Writer, "result_card.tmpl", r)
	}

	nextOffset := offset + len(rows)
	pageBudget := page * PageSize

	if len(rows) == 0 || len(rows) < limit || nextOffset >= pageBudget {
		tr.writeFinalizeOOB(c, makeName, model, year, vehicleType, types, brands, page)
		return
	}

	nextURL := tr.buildChunkURL(c.Query("rid"), makeName, model, year, vehicleType, types, brands, nextOffset, limit, page)
	fmt.Fprintf(c.Writer, `<div hx-swap-oob="true" id="next-chunk" hx-get="%s" hx-trigger="load" hx-target="#results-stream" hx-swap="beforeend" hidden></div>`, nextURL)
}

func (tr *TemplateRenderer) resultsChunkByPartNumber(c *gin.Context, partNumber, mode, term string, limit, offset, page int) {
	ctx := c.Request.Context()
	pattern := "%" + term + "%"

	var rows []SearchResultItem
	var err error
	switch mode {
	case "fmsi":
		rows, err = tr.searchByFmsi(ctx, pattern, limit, offset)
	default:
		rows, err = tr.searchByPartNumberRelated(ctx, pattern, limit, offset)
	}
	if err != nil {
		rows = []SearchResultItem{}
	}

	c.Header("Content-Type", "text/html; charset=utf-8")
	for _, r := range rows {
		tr.templates.ExecuteTemplate(c.Writer, "result_card.tmpl", r)
	}

	nextOffset := offset + len(rows)
	pageBudget := page * PageSize

	if len(rows) == 0 || len(rows) < limit || nextOffset >= pageBudget {
		tr.writePartNumberFinalizeOOB(c, partNumber, mode, term, page)
		return
	}

	nextURL := tr.buildPartNumberChunkURL(c.Query("rid"), partNumber, nextOffset, limit, page)
	fmt.Fprintf(c.Writer, `<div hx-swap-oob="true" id="next-chunk" hx-get="%s" hx-trigger="load" hx-target="#results-stream" hx-swap="beforeend" hidden></div>`, nextURL)
}

func (tr *TemplateRenderer) writeFinalizeOOB(c *gin.Context, make, model, year, vehicleType string, types, brands []string, page int) {
	ctx := c.Request.Context()
	total, _ := tr.countCatalogue(ctx, make, model, year, vehicleType, types, brands)
	totalPages := (total + PageSize - 1) / PageSize
	if totalPages < 1 {
		totalPages = 1
	}
	if page > totalPages {
		page = totalPages
	}
	filters, _ := tr.computeSidebarFilters(ctx, make, model, year, types, brands)

	baseQ := url.Values{}
	baseQ.Set("make", make)
	baseQ.Set("model", model)
	if year != "" {
		baseQ.Set("year", year)
	}
	if vehicleType != "" {
		baseQ.Set("vehicle_type", vehicleType)
	}
	for _, t := range types {
		baseQ.Add("type", t)
	}
	for _, b := range brands {
		baseQ.Add("brand", b)
	}

	summary := fmt.Sprintf("%d resultados para %s %s", total, make, model)
	if year != "" {
		summary += " (" + year + ")"
	}

	data := ResultadosData{
		Summary:             summary,
		TotalCount:          total,
		Page:                page,
		PageSize:            PageSize,
		TotalPages:          totalPages,
		BaseQuery:           baseQ.Encode(),
		SelectedMake:        make,
		SelectedModel:       model,
		SelectedYear:        year,
		SelectedVehicleType: vehicleType,
		SelectedTypes:       types,
		SelectedBrands:      brands,
		Filters:             filters,
		FiltersReady:        true,
		Streaming:           false,
	}
	tr.templates.ExecuteTemplate(c.Writer, "finalize_oob.tmpl", data)
}

func (tr *TemplateRenderer) writePartNumberFinalizeOOB(c *gin.Context, partNumber, mode, term string, page int) {
	ctx := c.Request.Context()
	pattern := "%" + term + "%"

	var total int
	switch mode {
	case "fmsi":
		total, _ = tr.countByFmsi(ctx, pattern)
	default:
		total, _ = tr.countByPartNumberRelated(ctx, pattern)
	}

	totalPages := (total + PageSize - 1) / PageSize
	if totalPages < 1 {
		totalPages = 1
	}
	if page > totalPages {
		page = totalPages
	}

	baseQ := url.Values{}
	baseQ.Set("part_number", partNumber)

	summary := fmt.Sprintf("%d resultados para la pieza «%s»", total, term)
	if mode == "fmsi" {
		summary = fmt.Sprintf("%d resultados para FMSI «%s»", total, term)
	}

	data := ResultadosData{
		Summary:        summary,
		TotalCount:     total,
		Page:           page,
		PageSize:       PageSize,
		TotalPages:     totalPages,
		BaseQuery:      baseQ.Encode(),
		PartNumber:     partNumber,
		PartNumberMode: true,
		Filters:        SidebarFilters{},
		FiltersReady:   true,
		Streaming:      false,
	}
	tr.templates.ExecuteTemplate(c.Writer, "finalize_oob.tmpl", data)
}

func parsePartNumberQuery(raw string) (mode, term string) {
	raw = strings.TrimSpace(raw)
	if strings.HasPrefix(strings.ToLower(raw), "fmsi:") {
		return "fmsi", strings.TrimSpace(raw[len("fmsi:"):])
	}
	return "part", raw
}

// yearClause is the SQL fragment applied to each spec arm. It uses $1 for the
// nullable year parameter (text or int cast). Comma-separated values in
// years/yearsRange match only the exact years listed; hyphen-separated
// yyyy-yyyy values match every year in the series.
const yearClause = `($1::text IS NULL
    OR (s.years IS NOT NULL AND (
      EXISTS (
        SELECT 1 FROM unnest(string_to_array(btrim(s.years), ',')) AS p
        WHERE btrim(p) ~ '^\d{4}$' AND $1::int = btrim(p)::int
      )
      OR EXISTS (
        SELECT 1 FROM unnest(string_to_array(btrim(s.years), ',')) AS p
        WHERE btrim(p) ~ '^\d{4}\s*-\s*\d{4}$'
          AND $1::int BETWEEN
            LEAST(substring(btrim(p) from '^\d{4}')::int, substring(btrim(p) from '\d{4}$')::int)
            AND GREATEST(substring(btrim(p) from '^\d{4}')::int, substring(btrim(p) from '\d{4}$')::int)
      )
    ))
    OR (s."yearsRange" IS NOT NULL AND (
      EXISTS (
        SELECT 1 FROM unnest(string_to_array(btrim(s."yearsRange"), ',')) AS p
        WHERE btrim(p) ~ '^\d{4}$' AND $1::int = btrim(p)::int
      )
      OR EXISTS (
        SELECT 1 FROM unnest(string_to_array(btrim(s."yearsRange"), ',')) AS p
        WHERE btrim(p) ~ '^\d{4}\s*-\s*\d{4}$'
          AND $1::int BETWEEN
            LEAST(substring(btrim(p) from '^\d{4}')::int, substring(btrim(p) from '\d{4}$')::int)
            AND GREATEST(substring(btrim(p) from '^\d{4}')::int, substring(btrim(p) from '\d{4}$')::int)
      )
    ))
  )`

// matchingvmyClause is the make/model/type filter CTE fragment referencing
// $4 (make), $5 (model), and $6 (optional vehicle_type name).
const matchingvmyClause = `WITH matching_vmy AS (
  SELECT v.id
  FROM vehicle_model_year v
  JOIN vehicle_brands vb ON v."fk_idVehicleBrand" = vb.id
  WHERE LOWER(vb.brand) = LOWER($4)
    AND (LOWER(v.model) = LOWER($5) OR LOWER(v.model) LIKE LOWER($5) || ' %')
    AND ($6::text IS NULL OR v."fk_idVehicleType" = (
        SELECT id FROM vehicle_type WHERE LOWER(type) = LOWER($6::text)
    ))
)`

const searchCatalogueBase = matchingvmyClause + `
SELECT t.type, t.id, t."partNumber", t.product, t.serie, t.position, t.tier,
       t."imageProduct", t.brand_name
FROM (
  SELECT ROW_NUMBER() OVER (PARTITION BY u."partNumber" ORDER BY u.type, u.id) AS rn,
         u.type, u.id, u."partNumber", u.product, u.serie, u.position, u.tier,
         u."imageProduct", u.brand_name
  FROM (
    SELECT 'shock'::text AS type, s.id, s."partNumber", s.product, s.serie, s.position, s.tier,
           s."imageProduct", COALESCE(sb.name, '') AS brand_name
    FROM shock_brand_specs s JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
    LEFT JOIN shock_brands sb ON s."fk_idShockBrand" = sb.id
    WHERE ` + yearClause + `
    UNION ALL
    SELECT 'brake'::text AS type, s.id, s."partNumber", s.product, s.serie, s.position, s.tier,
           s."imageProduct", COALESCE(sb.name, '') AS brand_name
    FROM brake_brand_specs s JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
    LEFT JOIN brake_brands sb ON s."fk_idBrakeBrand" = sb.id
    WHERE ` + yearClause + `
    UNION ALL
    SELECT 'disk'::text AS type, s.id, s."partNumber", s.product, s.serie, s.position, s.tier,
           s."imageProduct", COALESCE(sb.name, '') AS brand_name
    FROM disk_brand_specs s JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
    LEFT JOIN disk_brands sb ON s."fk_idDiskBrand" = sb.id
    WHERE ` + yearClause + `
    UNION ALL
    SELECT 'strut'::text AS type, s.id, s."partNumber", s.product, s.serie, s.position, s.tier,
           s."imageProduct", COALESCE(sb.name, '') AS brand_name
    FROM strut_brand_specs s JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
    LEFT JOIN strut_brands sb ON s."fk_idStrutBrand" = sb.id
    WHERE ` + yearClause + `
    UNION ALL
    SELECT 'leg'::text AS type, s.id, s."partNumber", s.product, s.serie, s.position, s.tier,
           s."imageProduct", COALESCE(sb.name, '') AS brand_name
    FROM leg_brand_specs s JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
    LEFT JOIN leg_brands sb ON s."fk_idLegBrand" = sb.id
    WHERE ` + yearClause + `
    UNION ALL
    SELECT 'extra'::text AS type, e.id, e."type" AS "partNumber", e.name AS product,
           e.notes AS serie, ''::text AS position, ''::text AS tier,
           ''::text AS "imageProduct", COALESCE(eb.brand, 'Prestone') AS brand_name
    FROM extra_type_product e
    LEFT JOIN extra_type_brand eb ON e."id_fktypeBrand" = eb.id
    WHERE $2::text[] IS NOT NULL AND 'extra' = ANY($2::text[])
  ) u
  WHERE ($2::text[] IS NULL OR u.type = ANY($2::text[]))
    AND ($3::text[] IS NULL OR u.brand_name = ANY($3::text[]))
) t
WHERE t.rn = 1
ORDER BY t.type, t."partNumber"
LIMIT $7 OFFSET $8
`

const countCatalogueBase = matchingvmyClause + `
SELECT count(DISTINCT t."partNumber")::bigint FROM (
  SELECT 'shock'::text AS type, s."partNumber", COALESCE(sb.name, '') AS brand_name
  FROM shock_brand_specs s JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
  LEFT JOIN shock_brands sb ON s."fk_idShockBrand" = sb.id WHERE ` + yearClause + `
  UNION ALL
  SELECT 'brake'::text AS type, s."partNumber", COALESCE(sb.name, '') AS brand_name
  FROM brake_brand_specs s JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
  LEFT JOIN brake_brands sb ON s."fk_idBrakeBrand" = sb.id WHERE ` + yearClause + `
  UNION ALL
  SELECT 'disk'::text AS type, s."partNumber", COALESCE(sb.name, '') AS brand_name
  FROM disk_brand_specs s JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
  LEFT JOIN disk_brands sb ON s."fk_idDiskBrand" = sb.id WHERE ` + yearClause + `
  UNION ALL
  SELECT 'strut'::text AS type, s."partNumber", COALESCE(sb.name, '') AS brand_name
  FROM strut_brand_specs s JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
  LEFT JOIN strut_brands sb ON s."fk_idStrutBrand" = sb.id WHERE ` + yearClause + `
  UNION ALL
    SELECT 'leg'::text AS type, s."partNumber", COALESCE(sb.name, '') AS brand_name
    FROM leg_brand_specs s JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
    LEFT JOIN leg_brands sb ON s."fk_idLegBrand" = sb.id WHERE ` + yearClause + `
    UNION ALL
    SELECT 'extra'::text AS type, e."type" AS "partNumber", COALESCE(eb.brand, 'Prestone') AS brand_name
    FROM extra_type_product e
    LEFT JOIN extra_type_brand eb ON e."id_fktypeBrand" = eb.id
    WHERE $2::text[] IS NOT NULL AND 'extra' = ANY($2::text[])
) t
WHERE ($2::text[] IS NULL OR t.type = ANY($2::text[]))
  AND ($3::text[] IS NULL OR t.brand_name = ANY($3::text[]))
`

const searchByPartNumberBase = `
SELECT t.type, t.id, t."partNumber", t.product, t.serie, t.position, t.tier,
       t."imageProduct", t.brand_name, t.vehicle_type
FROM (
  SELECT ROW_NUMBER() OVER (PARTITION BY u."partNumber" ORDER BY u.type, u.id) AS rn,
         u.type, u.id, u."partNumber", u.product, u.serie, u.position, u.tier,
         u."imageProduct", u.brand_name, u.vehicle_type
  FROM (
    SELECT 'shock'::text AS type, s.id, s."partNumber", s.product, s.serie, s.position, s.tier,
           s."imageProduct", COALESCE(sb.name, '') AS brand_name,
           COALESCE(vt.type, '') AS vehicle_type
    FROM shock_brand_specs s
    LEFT JOIN vehicle_model_year v ON s."fk_idVehicleModelYear" = v.id
    LEFT JOIN vehicle_type vt ON v."fk_idVehicleType" = vt.id
    LEFT JOIN shock_brands sb ON s."fk_idShockBrand" = sb.id
    WHERE s."partNumber" ILIKE $1
    UNION ALL
    SELECT 'brake'::text AS type, s.id, s."partNumber", s.product, s.serie, s.position, s.tier,
           s."imageProduct", COALESCE(sb.name, '') AS brand_name,
           COALESCE(vt.type, '') AS vehicle_type
    FROM brake_brand_specs s
    LEFT JOIN vehicle_model_year v ON s."fk_idVehicleModelYear" = v.id
    LEFT JOIN vehicle_type vt ON v."fk_idVehicleType" = vt.id
    LEFT JOIN brake_brands sb ON s."fk_idBrakeBrand" = sb.id
    WHERE s."partNumber" ILIKE $1
    UNION ALL
    SELECT 'disk'::text AS type, s.id, s."partNumber", s.product, s.serie, s.position, s.tier,
           s."imageProduct", COALESCE(sb.name, '') AS brand_name,
           COALESCE(vt.type, '') AS vehicle_type
    FROM disk_brand_specs s
    LEFT JOIN vehicle_model_year v ON s."fk_idVehicleModelYear" = v.id
    LEFT JOIN vehicle_type vt ON v."fk_idVehicleType" = vt.id
    LEFT JOIN disk_brands sb ON s."fk_idDiskBrand" = sb.id
    WHERE s."partNumber" ILIKE $1
    UNION ALL
    SELECT 'strut'::text AS type, s.id, s."partNumber", s.product, s.serie, s.position, s.tier,
           s."imageProduct", COALESCE(sb.name, '') AS brand_name,
           COALESCE(vt.type, '') AS vehicle_type
    FROM strut_brand_specs s
    LEFT JOIN vehicle_model_year v ON s."fk_idVehicleModelYear" = v.id
    LEFT JOIN vehicle_type vt ON v."fk_idVehicleType" = vt.id
    LEFT JOIN strut_brands sb ON s."fk_idStrutBrand" = sb.id
    WHERE s."partNumber" ILIKE $1
    UNION ALL
    SELECT 'leg'::text AS type, s.id, s."partNumber", s.product, s.serie, s.position, s.tier,
           s."imageProduct", COALESCE(sb.name, '') AS brand_name,
           COALESCE(vt.type, '') AS vehicle_type
    FROM leg_brand_specs s
    LEFT JOIN vehicle_model_year v ON s."fk_idVehicleModelYear" = v.id
    LEFT JOIN vehicle_type vt ON v."fk_idVehicleType" = vt.id
    LEFT JOIN leg_brands sb ON s."fk_idLegBrand" = sb.id
    WHERE s."partNumber" ILIKE $1
    UNION ALL
    SELECT 'extra'::text AS type, e.id, e."type" AS "partNumber", e.name AS product,
           e.notes AS serie, ''::text AS position, ''::text AS tier,
           ''::text AS "imageProduct", COALESCE(eb.brand, 'Prestone') AS brand_name,
           ''::text AS vehicle_type
    FROM extra_type_product e
    LEFT JOIN extra_type_brand eb ON e."id_fktypeBrand" = eb.id
    WHERE e."type" ILIKE $1
  ) u
) t
WHERE t.rn = 1
ORDER BY t.type, t."partNumber"
LIMIT $2 OFFSET $3
`

const countByPartNumberBase = `
SELECT count(DISTINCT t."partNumber")::bigint FROM (
  SELECT 'shock'::text AS type, s."partNumber"
  FROM shock_brand_specs s WHERE s."partNumber" ILIKE $1
  UNION ALL
  SELECT 'brake'::text AS type, s."partNumber"
  FROM brake_brand_specs s WHERE s."partNumber" ILIKE $1
  UNION ALL
  SELECT 'disk'::text AS type, s."partNumber"
  FROM disk_brand_specs s WHERE s."partNumber" ILIKE $1
  UNION ALL
  SELECT 'strut'::text AS type, s."partNumber"
  FROM strut_brand_specs s WHERE s."partNumber" ILIKE $1
  UNION ALL
  SELECT 'leg'::text AS type, s."partNumber"
  FROM leg_brand_specs s WHERE s."partNumber" ILIKE $1
  UNION ALL
  SELECT 'extra'::text AS type, e."type" AS "partNumber"
  FROM extra_type_product e WHERE e."type" ILIKE $1
) t
`

const searchByFmsiBase = `
SELECT t.type, t.id, t."partNumber", t.product, t.serie, t.position, t.tier,
       t."imageProduct", t.brand_name, t.vehicle_type
FROM (
  SELECT ROW_NUMBER() OVER (PARTITION BY u."partNumber" ORDER BY u.type, u.id) AS rn,
         u.type, u.id, u."partNumber", u.product, u.serie, u.position, u.tier,
         u."imageProduct", u.brand_name, u.vehicle_type
  FROM (
    SELECT 'brake'::text AS type, s.id, s."partNumber", s.product, s.serie, s.position, s.tier,
           s."imageProduct", COALESCE(sb.name, '') AS brand_name,
           COALESCE(vt.type, '') AS vehicle_type
    FROM brake_brand_specs s
    LEFT JOIN vehicle_model_year v ON s."fk_idVehicleModelYear" = v.id
    LEFT JOIN vehicle_type vt ON v."fk_idVehicleType" = vt.id
    LEFT JOIN brake_brands sb ON s."fk_idBrakeBrand" = sb.id
    WHERE s.fmsi ILIKE $1
  ) u
) t
WHERE t.rn = 1
ORDER BY t.type, t."partNumber"
LIMIT $2 OFFSET $3
`

const countByFmsiBase = `
SELECT count(DISTINCT t."partNumber")::bigint FROM (
  SELECT 'brake'::text AS type, s."partNumber"
  FROM brake_brand_specs s WHERE s.fmsi ILIKE $1
) t
`

const posGroupCase = `CASE
  WHEN LOWER(TRIM(COALESCE(s.position, ''))) ~ 'del\s*/\s*tras' THEN 'both'
  WHEN LOWER(TRIM(COALESCE(s.position, ''))) ~ '^(del|front)' THEN 'front'
  WHEN LOWER(TRIM(COALESCE(s.position, ''))) ~ '^(tras|rear)' THEN 'rear'
  ELSE LOWER(TRIM(COALESCE(s.position, '')))
END`

const searchByPartNumberRelatedBaseRaw = `
WITH searched AS (
  SELECT 'shock'::text AS type, s.position, COALESCE(sb.name, '') AS brand_name, s."fk_idVehicleModelYear" AS vmy_id
  FROM shock_brand_specs s LEFT JOIN shock_brands sb ON s."fk_idShockBrand" = sb.id WHERE s."partNumber" ILIKE $1
  UNION ALL
  SELECT 'brake'::text AS type, s.position, COALESCE(sb.name, '') AS brand_name, s."fk_idVehicleModelYear"
  FROM brake_brand_specs s LEFT JOIN brake_brands sb ON s."fk_idBrakeBrand" = sb.id WHERE s."partNumber" ILIKE $1
  UNION ALL
  SELECT 'disk'::text AS type, s.position, COALESCE(sb.name, '') AS brand_name, s."fk_idVehicleModelYear"
  FROM disk_brand_specs s LEFT JOIN disk_brands sb ON s."fk_idDiskBrand" = sb.id WHERE s."partNumber" ILIKE $1
  UNION ALL
  SELECT 'strut'::text AS type, s.position, COALESCE(sb.name, '') AS brand_name, s."fk_idVehicleModelYear"
  FROM strut_brand_specs s LEFT JOIN strut_brands sb ON s."fk_idStrutBrand" = sb.id WHERE s."partNumber" ILIKE $1
  UNION ALL
  SELECT 'leg'::text AS type, s.position, COALESCE(sb.name, '') AS brand_name, s."fk_idVehicleModelYear"
  FROM leg_brand_specs s LEFT JOIN leg_brands sb ON s."fk_idLegBrand" = sb.id WHERE s."partNumber" ILIKE $1
  UNION ALL
  SELECT 'extra'::text AS type, ''::text AS position, COALESCE(eb.brand, 'Prestone') AS brand_name, NULL::integer AS vmy_id
  FROM extra_type_product e LEFT JOIN extra_type_brand eb ON e."id_fktypeBrand" = eb.id WHERE e."type" ILIKE $1
),
matching_vmy AS (
  SELECT DISTINCT vmy_id AS id FROM searched WHERE vmy_id IS NOT NULL
),
searched_types AS (
  SELECT DISTINCT type FROM searched
),
searched_pos AS (
  SELECT DISTINCT {{POS_GROUP}} AS pg FROM searched s
),
combined AS (
  SELECT 'shock'::text AS type, s.id, s."partNumber", s.product, s.serie, s.position, s.tier,
         s."imageProduct", COALESCE(sb.name, '') AS brand_name,
         COALESCE(vt.type, '') AS vehicle_type
  FROM shock_brand_specs s
  LEFT JOIN vehicle_model_year v ON s."fk_idVehicleModelYear" = v.id
  LEFT JOIN vehicle_type vt ON v."fk_idVehicleType" = vt.id
  LEFT JOIN shock_brands sb ON s."fk_idShockBrand" = sb.id
  WHERE s."partNumber" ILIKE $1
  UNION ALL
  SELECT 'brake'::text AS type, s.id, s."partNumber", s.product, s.serie, s.position, s.tier,
         s."imageProduct", COALESCE(sb.name, '') AS brand_name,
         COALESCE(vt.type, '') AS vehicle_type
  FROM brake_brand_specs s
  LEFT JOIN vehicle_model_year v ON s."fk_idVehicleModelYear" = v.id
  LEFT JOIN vehicle_type vt ON v."fk_idVehicleType" = vt.id
  LEFT JOIN brake_brands sb ON s."fk_idBrakeBrand" = sb.id
  WHERE s."partNumber" ILIKE $1
  UNION ALL
  SELECT 'disk'::text AS type, s.id, s."partNumber", s.product, s.serie, s.position, s.tier,
         s."imageProduct", COALESCE(sb.name, '') AS brand_name,
         COALESCE(vt.type, '') AS vehicle_type
  FROM disk_brand_specs s
  LEFT JOIN vehicle_model_year v ON s."fk_idVehicleModelYear" = v.id
  LEFT JOIN vehicle_type vt ON v."fk_idVehicleType" = vt.id
  LEFT JOIN disk_brands sb ON s."fk_idDiskBrand" = sb.id
  WHERE s."partNumber" ILIKE $1
  UNION ALL
  SELECT 'strut'::text AS type, s.id, s."partNumber", s.product, s.serie, s.position, s.tier,
         s."imageProduct", COALESCE(sb.name, '') AS brand_name,
         COALESCE(vt.type, '') AS vehicle_type
  FROM strut_brand_specs s
  LEFT JOIN vehicle_model_year v ON s."fk_idVehicleModelYear" = v.id
  LEFT JOIN vehicle_type vt ON v."fk_idVehicleType" = vt.id
  LEFT JOIN strut_brands sb ON s."fk_idStrutBrand" = sb.id
  WHERE s."partNumber" ILIKE $1
  UNION ALL
  SELECT 'leg'::text AS type, s.id, s."partNumber", s.product, s.serie, s.position, s.tier,
         s."imageProduct", COALESCE(sb.name, '') AS brand_name,
         COALESCE(vt.type, '') AS vehicle_type
  FROM leg_brand_specs s
  LEFT JOIN vehicle_model_year v ON s."fk_idVehicleModelYear" = v.id
  LEFT JOIN vehicle_type vt ON v."fk_idVehicleType" = vt.id
  LEFT JOIN leg_brands sb ON s."fk_idLegBrand" = sb.id
  WHERE s."partNumber" ILIKE $1
  UNION ALL
  SELECT 'extra'::text AS type, e.id, e."type" AS "partNumber", e.name AS product,
         e.notes AS serie, ''::text AS position, ''::text AS tier,
         ''::text AS "imageProduct", COALESCE(eb.brand, 'Prestone') AS brand_name,
         ''::text AS vehicle_type
  FROM extra_type_product e
  LEFT JOIN extra_type_brand eb ON e."id_fktypeBrand" = eb.id
  WHERE e."type" ILIKE $1
  UNION ALL
  SELECT 'shock'::text AS type, s.id, s."partNumber", s.product, s.serie, s.position, s.tier,
         s."imageProduct", COALESCE(sb.name, '') AS brand_name,
         COALESCE(vt.type, '') AS vehicle_type
  FROM shock_brand_specs s
  JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
  LEFT JOIN vehicle_model_year v ON s."fk_idVehicleModelYear" = v.id
  LEFT JOIN vehicle_type vt ON v."fk_idVehicleType" = vt.id
  LEFT JOIN shock_brands sb ON s."fk_idShockBrand" = sb.id
  WHERE EXISTS (SELECT 1 FROM searched_types WHERE type IN ('shock','strut','leg'))
    AND {{POS_GROUP}} IN (SELECT pg FROM searched_pos)
  UNION ALL
  SELECT 'brake'::text AS type, s.id, s."partNumber", s.product, s.serie, s.position, s.tier,
         s."imageProduct", COALESCE(sb.name, '') AS brand_name,
         COALESCE(vt.type, '') AS vehicle_type
  FROM brake_brand_specs s
  JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
  LEFT JOIN vehicle_model_year v ON s."fk_idVehicleModelYear" = v.id
  LEFT JOIN vehicle_type vt ON v."fk_idVehicleType" = vt.id
  LEFT JOIN brake_brands sb ON s."fk_idBrakeBrand" = sb.id
  WHERE EXISTS (SELECT 1 FROM searched_types WHERE type IN ('brake','disk'))
    AND {{POS_GROUP}} IN (SELECT pg FROM searched_pos)
  UNION ALL
  SELECT 'disk'::text AS type, s.id, s."partNumber", s.product, s.serie, s.position, s.tier,
         s."imageProduct", COALESCE(sb.name, '') AS brand_name,
         COALESCE(vt.type, '') AS vehicle_type
  FROM disk_brand_specs s
  JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
  LEFT JOIN vehicle_model_year v ON s."fk_idVehicleModelYear" = v.id
  LEFT JOIN vehicle_type vt ON v."fk_idVehicleType" = vt.id
  LEFT JOIN disk_brands sb ON s."fk_idDiskBrand" = sb.id
  WHERE EXISTS (SELECT 1 FROM searched_types WHERE type IN ('brake','disk'))
    AND {{POS_GROUP}} IN (SELECT pg FROM searched_pos)
  UNION ALL
  SELECT 'strut'::text AS type, s.id, s."partNumber", s.product, s.serie, s.position, s.tier,
         s."imageProduct", COALESCE(sb.name, '') AS brand_name,
         COALESCE(vt.type, '') AS vehicle_type
  FROM strut_brand_specs s
  JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
  LEFT JOIN vehicle_model_year v ON s."fk_idVehicleModelYear" = v.id
  LEFT JOIN vehicle_type vt ON v."fk_idVehicleType" = vt.id
  LEFT JOIN strut_brands sb ON s."fk_idStrutBrand" = sb.id
  WHERE EXISTS (SELECT 1 FROM searched_types WHERE type IN ('shock','strut','leg'))
    AND {{POS_GROUP}} IN (SELECT pg FROM searched_pos)
  UNION ALL
  SELECT 'leg'::text AS type, s.id, s."partNumber", s.product, s.serie, s.position, s.tier,
         s."imageProduct", COALESCE(sb.name, '') AS brand_name,
         COALESCE(vt.type, '') AS vehicle_type
  FROM leg_brand_specs s
  JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
  LEFT JOIN vehicle_model_year v ON s."fk_idVehicleModelYear" = v.id
  LEFT JOIN vehicle_type vt ON v."fk_idVehicleType" = vt.id
  LEFT JOIN leg_brands sb ON s."fk_idLegBrand" = sb.id
  WHERE EXISTS (SELECT 1 FROM searched_types WHERE type IN ('shock','strut','leg'))
    AND {{POS_GROUP}} IN (SELECT pg FROM searched_pos)
)
SELECT t.type, t.id, t."partNumber", t.product, t.serie, t.position, t.tier,
       t."imageProduct", t.brand_name, t.vehicle_type
FROM (
  SELECT ROW_NUMBER() OVER (PARTITION BY u."partNumber" ORDER BY u.type, u.id) AS rn,
         u.type, u.id, u."partNumber", u.product, u.serie, u.position, u.tier,
         u."imageProduct", u.brand_name, u.vehicle_type
  FROM combined u
) t
WHERE t.rn = 1
ORDER BY t.type, t."partNumber"
LIMIT $2 OFFSET $3
`

const countByPartNumberRelatedBaseRaw = `
WITH searched AS (
  SELECT 'shock'::text AS type, s.position, COALESCE(sb.name, '') AS brand_name, s."fk_idVehicleModelYear" AS vmy_id
  FROM shock_brand_specs s LEFT JOIN shock_brands sb ON s."fk_idShockBrand" = sb.id WHERE s."partNumber" ILIKE $1
  UNION ALL
  SELECT 'brake'::text AS type, s.position, COALESCE(sb.name, '') AS brand_name, s."fk_idVehicleModelYear"
  FROM brake_brand_specs s LEFT JOIN brake_brands sb ON s."fk_idBrakeBrand" = sb.id WHERE s."partNumber" ILIKE $1
  UNION ALL
  SELECT 'disk'::text AS type, s.position, COALESCE(sb.name, '') AS brand_name, s."fk_idVehicleModelYear"
  FROM disk_brand_specs s LEFT JOIN disk_brands sb ON s."fk_idDiskBrand" = sb.id WHERE s."partNumber" ILIKE $1
  UNION ALL
  SELECT 'strut'::text AS type, s.position, COALESCE(sb.name, '') AS brand_name, s."fk_idVehicleModelYear"
  FROM strut_brand_specs s LEFT JOIN strut_brands sb ON s."fk_idStrutBrand" = sb.id WHERE s."partNumber" ILIKE $1
  UNION ALL
  SELECT 'leg'::text AS type, s.position, COALESCE(sb.name, '') AS brand_name, s."fk_idVehicleModelYear"
  FROM leg_brand_specs s LEFT JOIN leg_brands sb ON s."fk_idLegBrand" = sb.id WHERE s."partNumber" ILIKE $1
  UNION ALL
  SELECT 'extra'::text AS type, ''::text AS position, COALESCE(eb.brand, 'Prestone') AS brand_name, NULL::integer AS vmy_id
  FROM extra_type_product e LEFT JOIN extra_type_brand eb ON e."id_fktypeBrand" = eb.id WHERE e."type" ILIKE $1
),
matching_vmy AS (
  SELECT DISTINCT vmy_id AS id FROM searched WHERE vmy_id IS NOT NULL
),
searched_types AS (
  SELECT DISTINCT type FROM searched
),
searched_pos AS (
  SELECT DISTINCT {{POS_GROUP}} AS pg FROM searched s
),
combined AS (
  SELECT s."partNumber" FROM shock_brand_specs s WHERE s."partNumber" ILIKE $1
  UNION ALL
  SELECT s."partNumber" FROM brake_brand_specs s WHERE s."partNumber" ILIKE $1
  UNION ALL
  SELECT s."partNumber" FROM disk_brand_specs s WHERE s."partNumber" ILIKE $1
  UNION ALL
  SELECT s."partNumber" FROM strut_brand_specs s WHERE s."partNumber" ILIKE $1
  UNION ALL
  SELECT s."partNumber" FROM leg_brand_specs s WHERE s."partNumber" ILIKE $1
  UNION ALL
  SELECT e."type" AS "partNumber" FROM extra_type_product e WHERE e."type" ILIKE $1
  UNION ALL
  SELECT s."partNumber" FROM shock_brand_specs s JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
  WHERE EXISTS (SELECT 1 FROM searched_types WHERE type IN ('shock','strut','leg'))
    AND {{POS_GROUP}} IN (SELECT pg FROM searched_pos)
  UNION ALL
  SELECT s."partNumber" FROM brake_brand_specs s JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
  WHERE EXISTS (SELECT 1 FROM searched_types WHERE type IN ('brake','disk'))
    AND {{POS_GROUP}} IN (SELECT pg FROM searched_pos)
  UNION ALL
  SELECT s."partNumber" FROM disk_brand_specs s JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
  WHERE EXISTS (SELECT 1 FROM searched_types WHERE type IN ('brake','disk'))
    AND {{POS_GROUP}} IN (SELECT pg FROM searched_pos)
  UNION ALL
  SELECT s."partNumber" FROM strut_brand_specs s JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
  WHERE EXISTS (SELECT 1 FROM searched_types WHERE type IN ('shock','strut','leg'))
    AND {{POS_GROUP}} IN (SELECT pg FROM searched_pos)
  UNION ALL
  SELECT s."partNumber" FROM leg_brand_specs s JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
  WHERE EXISTS (SELECT 1 FROM searched_types WHERE type IN ('shock','strut','leg'))
    AND {{POS_GROUP}} IN (SELECT pg FROM searched_pos)
)
SELECT count(DISTINCT "partNumber")::bigint FROM combined
`

var searchByPartNumberRelatedBase = strings.ReplaceAll(searchByPartNumberRelatedBaseRaw, "{{POS_GROUP}}", posGroupCase)
var countByPartNumberRelatedBase = strings.ReplaceAll(countByPartNumberRelatedBaseRaw, "{{POS_GROUP}}", posGroupCase)

type catalogueRow struct {
	Type         string
	ID           int32
	PartNumber   string
	Product      string
	Serie        string
	Position     string
	Tier         string
	ImageProduct string
	BrandName    string
	VehicleType  string
}

func (tr *TemplateRenderer) searchCatalogue(ctx context.Context, make, model, year, vehicleType string, types, brands []string, limit, offset int) ([]SearchResultItem, error) {
	args := []any{
		pgtype.Text{String: year, Valid: year != ""},
		toTextArray(expandTypeGroups(types)),
		toTextArray(brands),
		make,
		model,
		pgtype.Text{String: vehicleType, Valid: vehicleType != ""},
		int32(limit),
		int32(offset),
	}
	rows, err := tr.db.Query(ctx, searchCatalogueBase, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []SearchResultItem
	for rows.Next() {
		var r catalogueRow
		var pn, product, serie, pos, tier, img pgtype.Text
		if err := rows.Scan(&r.Type, &r.ID, &pn, &product, &serie, &pos, &tier, &img, &r.BrandName); err != nil {
			return nil, err
		}
		r.PartNumber = pn.String
		r.Product = product.String
		r.Serie = serie.String
		r.Position = pos.String
		r.Tier = tier.String
		r.ImageProduct = img.String
		fallbacks := productImageFallbacks(r.BrandName, r.Type, r.PartNumber, r.Position)
		items = append(items, SearchResultItem{
			Type: r.Type, ID: r.ID, PartNumber: r.PartNumber, Product: r.Product,
			Serie: r.Serie, Position: r.Position, Tier: r.Tier,
			ImageProduct: r.ImageProduct, ImageFallbacks: fallbacks, BrandName: r.BrandName,
		})
	}
	return items, rows.Err()
}

func (tr *TemplateRenderer) countCatalogue(ctx context.Context, make, model, year, vehicleType string, types, brands []string) (int, error) {
	args := []any{
		pgtype.Text{String: year, Valid: year != ""},
		toTextArray(expandTypeGroups(types)),
		toTextArray(brands),
		make,
		model,
		pgtype.Text{String: vehicleType, Valid: vehicleType != ""},
	}
	row := tr.db.QueryRow(ctx, countCatalogueBase, args...)
	var cnt int64
	if err := row.Scan(&cnt); err != nil {
		return 0, err
	}
	return int(cnt), nil
}

func (tr *TemplateRenderer) searchByPartNumber(ctx context.Context, pattern string, limit, offset int) ([]SearchResultItem, error) {
	args := []any{
		pattern,
		int32(limit),
		int32(offset),
	}
	rows, err := tr.db.Query(ctx, searchByPartNumberBase, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []SearchResultItem
	for rows.Next() {
		var r catalogueRow
		var pn, product, serie, pos, tier, img pgtype.Text
		if err := rows.Scan(&r.Type, &r.ID, &pn, &product, &serie, &pos, &tier, &img, &r.BrandName, &r.VehicleType); err != nil {
			return nil, err
		}
		r.PartNumber = pn.String
		r.Product = product.String
		r.Serie = serie.String
		r.Position = pos.String
		r.Tier = tier.String
		r.ImageProduct = img.String
		fallbacks := productImageFallbacks(r.BrandName, r.Type, r.PartNumber, r.Position)
		items = append(items, SearchResultItem{
			Type: r.Type, ID: r.ID, PartNumber: r.PartNumber, Product: r.Product,
			Serie: r.Serie, Position: r.Position, Tier: r.Tier,
			ImageProduct: r.ImageProduct, ImageFallbacks: fallbacks, BrandName: r.BrandName,
			VehicleType: r.VehicleType,
		})
	}
	return items, rows.Err()
}

func (tr *TemplateRenderer) countByPartNumber(ctx context.Context, pattern string) (int, error) {
	row := tr.db.QueryRow(ctx, countByPartNumberBase, pattern)
	var cnt int64
	if err := row.Scan(&cnt); err != nil {
		return 0, err
	}
	return int(cnt), nil
}

func (tr *TemplateRenderer) searchByFmsi(ctx context.Context, pattern string, limit, offset int) ([]SearchResultItem, error) {
	args := []any{
		pattern,
		int32(limit),
		int32(offset),
	}
	rows, err := tr.db.Query(ctx, searchByFmsiBase, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []SearchResultItem
	for rows.Next() {
		var r catalogueRow
		var pn, product, serie, pos, tier, img pgtype.Text
		if err := rows.Scan(&r.Type, &r.ID, &pn, &product, &serie, &pos, &tier, &img, &r.BrandName, &r.VehicleType); err != nil {
			return nil, err
		}
		r.PartNumber = pn.String
		r.Product = product.String
		r.Serie = serie.String
		r.Position = pos.String
		r.Tier = tier.String
		r.ImageProduct = img.String
		fallbacks := productImageFallbacks(r.BrandName, r.Type, r.PartNumber, r.Position)
		items = append(items, SearchResultItem{
			Type: r.Type, ID: r.ID, PartNumber: r.PartNumber, Product: r.Product,
			Serie: r.Serie, Position: r.Position, Tier: r.Tier,
			ImageProduct: r.ImageProduct, ImageFallbacks: fallbacks, BrandName: r.BrandName,
			VehicleType: r.VehicleType,
		})
	}
	return items, rows.Err()
}

func (tr *TemplateRenderer) countByFmsi(ctx context.Context, pattern string) (int, error) {
	row := tr.db.QueryRow(ctx, countByFmsiBase, pattern)
	var cnt int64
	if err := row.Scan(&cnt); err != nil {
		return 0, err
	}
	return int(cnt), nil
}

func (tr *TemplateRenderer) searchByPartNumberRelated(ctx context.Context, pattern string, limit, offset int) ([]SearchResultItem, error) {
	args := []any{
		pattern,
		int32(limit),
		int32(offset),
	}
	rows, err := tr.db.Query(ctx, searchByPartNumberRelatedBase, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []SearchResultItem
	for rows.Next() {
		var r catalogueRow
		var pn, product, serie, pos, tier, img pgtype.Text
		if err := rows.Scan(&r.Type, &r.ID, &pn, &product, &serie, &pos, &tier, &img, &r.BrandName, &r.VehicleType); err != nil {
			return nil, err
		}
		r.PartNumber = pn.String
		r.Product = product.String
		r.Serie = serie.String
		r.Position = pos.String
		r.Tier = tier.String
		r.ImageProduct = img.String
		fallbacks := productImageFallbacks(r.BrandName, r.Type, r.PartNumber, r.Position)
		items = append(items, SearchResultItem{
			Type: r.Type, ID: r.ID, PartNumber: r.PartNumber, Product: r.Product,
			Serie: r.Serie, Position: r.Position, Tier: r.Tier,
			ImageProduct: r.ImageProduct, ImageFallbacks: fallbacks, BrandName: r.BrandName,
			VehicleType: r.VehicleType,
		})
	}
	return items, rows.Err()
}

func (tr *TemplateRenderer) countByPartNumberRelated(ctx context.Context, pattern string) (int, error) {
	row := tr.db.QueryRow(ctx, countByPartNumberRelatedBase, pattern)
	var cnt int64
	if err := row.Scan(&cnt); err != nil {
		return 0, err
	}
	return int(cnt), nil
}

// matchingvmyClauseFacets is the make/model CTE fragment for the facets query,
// where $1 is the nullable year, $2 is make and $3 is model.
const matchingvmyClauseFacets = `WITH matching_vmy AS (
  SELECT v.id
  FROM vehicle_model_year v
  JOIN vehicle_brands vb ON v."fk_idVehicleBrand" = vb.id
  WHERE LOWER(vb.brand) = LOWER($2)
    AND (LOWER(v.model) = LOWER($3) OR LOWER(v.model) LIKE LOWER($3) || ' %')
)`

// typeFacetsQuery counts distinct part numbers per product family for the
// current make/model/year, optionally filtered by selected brands.
// Parameters: $1 year, $2 make, $3 model, $4 selected brands.
const typeFacetsQuery = matchingvmyClauseFacets + `
SELECT
  COUNT(DISTINCT u."partNumber") FILTER (WHERE u.type IN ('shock','strut','leg')) AS shock,
  COUNT(DISTINCT u."partNumber") FILTER (WHERE u.type IN ('brake','disk'))         AS brake,
  COUNT(DISTINCT u."partNumber") FILTER (WHERE u.type = 'extra')                   AS extra
FROM (
  SELECT 'shock'::text AS type, s."partNumber", COALESCE(sb.name, '') AS brand_name
  FROM shock_brand_specs s JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
  LEFT JOIN shock_brands sb ON s."fk_idShockBrand" = sb.id WHERE ` + yearClause + `
  UNION ALL
  SELECT 'strut'::text AS type, s."partNumber", COALESCE(sb.name, '') AS brand_name
  FROM strut_brand_specs s JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
  LEFT JOIN strut_brands sb ON s."fk_idStrutBrand" = sb.id WHERE ` + yearClause + `
  UNION ALL
  SELECT 'leg'::text AS type, s."partNumber", COALESCE(sb.name, '') AS brand_name
  FROM leg_brand_specs s JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
  LEFT JOIN leg_brands sb ON s."fk_idLegBrand" = sb.id WHERE ` + yearClause + `
  UNION ALL
  SELECT 'brake'::text AS type, s."partNumber", COALESCE(sb.name, '') AS brand_name
  FROM brake_brand_specs s JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
  LEFT JOIN brake_brands sb ON s."fk_idBrakeBrand" = sb.id WHERE ` + yearClause + `
  UNION ALL
  SELECT 'disk'::text AS type, s."partNumber", COALESCE(sb.name, '') AS brand_name
  FROM disk_brand_specs s JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
  LEFT JOIN disk_brands sb ON s."fk_idDiskBrand" = sb.id WHERE ` + yearClause + `
  UNION ALL
  SELECT 'extra'::text AS type, e."type" AS "partNumber", COALESCE(eb.brand, 'Prestone') AS brand_name
  FROM extra_type_product e
  LEFT JOIN extra_type_brand eb ON e."id_fktypeBrand" = eb.id
) u
WHERE ($4::text[] IS NULL OR u.brand_name = ANY($4::text[]))
`

// brandFacetsQuery counts distinct part numbers per brand for the current
// make/model/year, filtered to the selected internal types (already expanded).
// Parameters: $1 year, $2 make, $3 model, $4 selected internal types.
const brandFacetsQuery = matchingvmyClauseFacets + `
SELECT u.brand_name, COUNT(DISTINCT u."partNumber")::bigint AS cnt
FROM (
  SELECT 'shock'::text AS type, s."partNumber", COALESCE(sb.name, '') AS brand_name
  FROM shock_brand_specs s JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
  LEFT JOIN shock_brands sb ON s."fk_idShockBrand" = sb.id WHERE ` + yearClause + `
  UNION ALL
  SELECT 'strut'::text AS type, s."partNumber", COALESCE(sb.name, '') AS brand_name
  FROM strut_brand_specs s JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
  LEFT JOIN strut_brands sb ON s."fk_idStrutBrand" = sb.id WHERE ` + yearClause + `
  UNION ALL
  SELECT 'leg'::text AS type, s."partNumber", COALESCE(sb.name, '') AS brand_name
  FROM leg_brand_specs s JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
  LEFT JOIN leg_brands sb ON s."fk_idLegBrand" = sb.id WHERE ` + yearClause + `
  UNION ALL
  SELECT 'brake'::text AS type, s."partNumber", COALESCE(sb.name, '') AS brand_name
  FROM brake_brand_specs s JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
  LEFT JOIN brake_brands sb ON s."fk_idBrakeBrand" = sb.id WHERE ` + yearClause + `
  UNION ALL
  SELECT 'disk'::text AS type, s."partNumber", COALESCE(sb.name, '') AS brand_name
  FROM disk_brand_specs s JOIN matching_vmy m ON s."fk_idVehicleModelYear" = m.id
  LEFT JOIN disk_brands sb ON s."fk_idDiskBrand" = sb.id WHERE ` + yearClause + `
  UNION ALL
  SELECT 'extra'::text AS type, e."type" AS "partNumber", COALESCE(eb.brand, 'Prestone') AS brand_name
  FROM extra_type_product e
  LEFT JOIN extra_type_brand eb ON e."id_fktypeBrand" = eb.id
) u
WHERE ($4::text[] IS NULL OR u.type = ANY($4::text[]))
  AND u.brand_name != ''
GROUP BY u.brand_name
ORDER BY u.brand_name
`

func (tr *TemplateRenderer) computeSidebarFilters(ctx context.Context, make, model, year string, types, brands []string) (SidebarFilters, error) {
	typeKeyToLabel := map[string]string{
		"shock": "Amortiguadores",
		"brake": "Frenos",
		"extra": "Insumos Automotrices",
	}

	// 1. Product-family counts, filtered by the currently selected brands.
	typeArgs := []any{
		pgtype.Text{String: year, Valid: year != ""},
		make,
		model,
		toTextArray(brands),
	}
	row := tr.db.QueryRow(ctx, typeFacetsQuery, typeArgs...)
	var shockCnt, brakeCnt, extraCnt int64
	if err := row.Scan(&shockCnt, &brakeCnt, &extraCnt); err != nil {
		return SidebarFilters{}, err
	}
	counts := map[string]int{
		"shock": int(shockCnt),
		"brake": int(brakeCnt),
		"extra": int(extraCnt),
	}
	order := []string{"shock", "brake", "extra"}
	var productTypes []FilterCount
	for _, g := range order {
		productTypes = append(productTypes, FilterCount{Value: g, Label: typeKeyToLabel[g], Count: counts[g]})
	}

	// 2. Brand counts, filtered to the currently selected product families.
	brandArgs := []any{
		pgtype.Text{String: year, Valid: year != ""},
		make,
		model,
		toTextArray(expandTypeGroups(types)),
	}
	rows, err := tr.db.Query(ctx, brandFacetsQuery, brandArgs...)
	if err != nil {
		return SidebarFilters{}, err
	}
	defer rows.Close()

	var productBrands []FilterCount
	for rows.Next() {
		var name string
		var cnt int64
		if err := rows.Scan(&name, &cnt); err != nil {
			return SidebarFilters{}, err
		}
		productBrands = append(productBrands, FilterCount{Value: name, Label: name, Count: int(cnt)})
	}
	if err := rows.Err(); err != nil {
		return SidebarFilters{}, err
	}
	sort.Slice(productBrands, func(i, j int) bool {
		return productBrands[i].Label < productBrands[j].Label
	})

	return SidebarFilters{ProductTypes: productTypes, ProductBrands: productBrands}, nil
}

// toTextArray returns a Postgres text[] argument; nil when empty so the
// `IS NULL` branch of the filter clause matches.
func toTextArray(vals []string) []string {
	if len(vals) == 0 {
		return nil
	}
	return vals
}

// expandTypeGroups maps the 3 UI categories to the underlying spec tables.
// "Amortiguadores" (shock) covers suspension: shock + strut + leg.
// "Frenos" (brake) covers braking: brake + disk.
// "Insumos Automotrices" (extra) covers non-vehicle consumables: extra.
// Other values (disk, strut, leg, etc.) are passed through unchanged so the
// sidebar drill-down can still target a single spec table. Empty input is
// returned empty so the `IS NULL` branch matches all tables ("Todos").
func expandTypeGroups(types []string) []string {
	if len(types) == 0 {
		return nil
	}
	seen := make(map[string]struct{})
	var out []string
	add := func(t string) {
		if _, ok := seen[t]; !ok {
			seen[t] = struct{}{}
			out = append(out, t)
		}
	}
	for _, t := range types {
		switch t {
		case "shock":
			add("shock")
			add("strut")
			add("leg")
		case "brake":
			add("brake")
			add("disk")
		case "extra":
			add("extra")
		default:
			add(t)
		}
	}
	return out
}

type NormalizedProduct struct {
	ID             int32
	PartNumber     string
	Product        string
	Serie          string
	Quantity       string
	Tier           string
	Position       string
	ImageProduct   string
	ImageFallbacks []string
	DiagramImage   string
	VehicleAttrs   string
	Notes          string
	Years          string
	YearsRange     string
	BrandName      string
	ExtraSpecs     []SpecAttr
}

type SpecAttr struct {
	Label string
	Value string
}

type Application struct {
	Make       string
	Model      string
	Years      string
	YearsRange string
	Position   string
}

type ProductDetailData struct {
	Title        string
	Product      NormalizedProduct
	ProductType  string
	VehicleMake  string
	VehicleModel string
	VehicleType  string
	Applications []Application
}

func (tr *TemplateRenderer) ProductDetail(c *gin.Context) {
	productType := c.Param("type")
	partNumber := c.Param("part_number")

	ctx := c.Request.Context()
	product, applications, err := tr.fetchProductByPartNumber(ctx, productType, partNumber)
	if err != nil {
		c.JSON(http.StatusNotFound, gin.H{"error": "Producto no encontrado"})
		return
	}

	vehicleMake, vehicleModel := "", ""
	if len(applications) > 0 {
		vehicleMake = applications[0].Make
		vehicleModel = applications[0].Model
	}

	vehicleType := c.Query("vehicle_type")
	if vehicleType == "" {
		vehicleType = tr.resolveVehicleType(ctx, productType, partNumber)
	}

	data := ProductDetailData{
		Title:        fmt.Sprintf("%s - MIPDSA", product.PartNumber),
		Product:      product,
		ProductType:  productType,
		VehicleMake:  vehicleMake,
		VehicleModel: vehicleModel,
		VehicleType:  vehicleType,
		Applications: applications,
	}

	c.Header("Content-Type", "text/html; charset=utf-8")
	err = tr.templates.ExecuteTemplate(c.Writer, "product.tmpl", data)
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
	}
}

// resolveVehicleType returns the vehicle type name for the first matching spec
// row. It is used as a fallback in ProductDetail when the URL does not include
// a vehicle_type query parameter.
func (tr *TemplateRenderer) resolveVehicleType(ctx context.Context, productType, partNumber string) string {
	var table string
	switch productType {
	case "shock":
		table = "shock_brand_specs"
	case "brake":
		table = "brake_brand_specs"
	case "disk":
		table = "disk_brand_specs"
	case "strut":
		table = "strut_brand_specs"
	case "leg":
		table = "leg_brand_specs"
	default:
		return ""
	}

	query := fmt.Sprintf(`
		SELECT COALESCE(vt.type, '')
		FROM %s s
		JOIN vehicle_model_year v ON s."fk_idVehicleModelYear" = v.id
		JOIN vehicle_type vt ON v."fk_idVehicleType" = vt.id
		WHERE s."partNumber" = $1
		LIMIT 1
	`, table)

	var vt string
	if err := tr.db.QueryRow(ctx, query, partNumber).Scan(&vt); err != nil {
		return ""
	}
	return vt
}

func (tr *TemplateRenderer) GetBrands(c *gin.Context) {
	ctx := c.Request.Context()
	brands, err := tr.queries.GetVehicleBrands(ctx, catalogue.GetVehicleBrandsParams{Limit: 1000000000, Offset: 0})
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}

	if c.GetHeader("HX-Request") != "" {
		var html strings.Builder
		html.WriteString(`<option selected value="">Marca</option>`)
		for _, b := range brands {
			html.WriteString(fmt.Sprintf(`<option value="%s">%s</option>`, b.Brand, b.Brand))
		}
		c.Header("Content-Type", "text/html; charset=utf-8")
		c.String(http.StatusOK, html.String())
	} else {
		c.JSON(http.StatusOK, brands)
	}
}

func (tr *TemplateRenderer) GetModels(c *gin.Context) {
	brandName := c.Query("brand_name")
	brandIDStr := c.Query("brand_id")
	var brandID int32

	if brandName != "" {
		ctx := c.Request.Context()
		allBrands, err := tr.queries.GetVehicleBrands(ctx, catalogue.GetVehicleBrandsParams{Limit: 1000000000, Offset: 0})
		if err != nil {
			c.JSON(http.StatusOK, []catalogue.VehicleModelYear{})
			return
		}
		for _, b := range allBrands {
			if strings.EqualFold(b.Brand, brandName) {
				brandID = b.ID
				break
			}
		}
	} else if brandIDStr != "" {
		fmt.Sscanf(brandIDStr, "%d", &brandID)
	}

	if brandID <= 0 {
		c.JSON(http.StatusOK, []catalogue.VehicleModelYear{})
		return
	}

	ctx := c.Request.Context()
	models, err := tr.queries.GetVehicleModelYearsByBrand(ctx, pgtype.Int4{Int32: brandID, Valid: true})
	if err != nil {
		c.JSON(http.StatusOK, []catalogue.VehicleModelYear{})
		return
	}
	c.JSON(http.StatusOK, models)
}

// specTable returns the spec table name and FK column for a product type.
func specTable(productType string) (table string, fkColumn string) {
	switch productType {
	case "shock":
		return "shock_brand_specs", "fk_idShockBrand"
	case "brake":
		return "brake_brand_specs", "fk_idBrakeBrand"
	case "disk":
		return "disk_brand_specs", "fk_idDiskBrand"
	case "strut":
		return "strut_brand_specs", "fk_idStrutBrand"
	case "leg":
		return "leg_brand_specs", "fk_idLegBrand"
	default:
		return "", ""
	}
}

type filteredBrand struct {
	ID    int32  `json:"id"`
	Brand string `json:"brand"`
}

func (tr *TemplateRenderer) getFilteredBrandsQuery(vehicleType string, productType string) (string, []any) {
	baseSelect := `
		SELECT DISTINCT vb.id, vb.brand
		FROM vehicle_brands vb
		JOIN vehicle_model_year vmy ON vmy."fk_idVehicleBrand" = vb.id
		JOIN vehicle_type vt ON vmy."fk_idVehicleType" = vt.id
		JOIN %s s ON s."fk_idVehicleModelYear" = vmy.id
		WHERE LOWER(vt.type) = LOWER($1)`

	if productType == "" || productType == "todos" {
		tables := []string{"shock_brand_specs", "brake_brand_specs", "disk_brand_specs", "strut_brand_specs", "leg_brand_specs"}
		unionParts := make([]string, len(tables))
		for i, t := range tables {
			unionParts[i] = fmt.Sprintf(baseSelect, t)
		}
		q := "SELECT DISTINCT id, brand FROM (" + strings.Join(unionParts, " UNION ") + ") sub ORDER BY brand"
		return q, []any{vehicleType}
	}

	table, _ := specTable(productType)
	if table == "" {
		return "", nil
	}
	q := fmt.Sprintf(baseSelect+" ORDER BY vb.brand", table)
	return q, []any{vehicleType}
}

func (tr *TemplateRenderer) GetFilteredBrands(c *gin.Context) {
	vehicleType := c.Query("vehicle_type")
	productType := c.Query("product_type")
	if vehicleType == "" {
		c.JSON(http.StatusBadRequest, gin.H{"error": "vehicle_type is required"})
		return
	}

	q, args := tr.getFilteredBrandsQuery(vehicleType, productType)
	if q == "" {
		c.JSON(http.StatusBadRequest, gin.H{"error": "invalid product_type"})
		return
	}

	ctx := c.Request.Context()
	rows, err := tr.db.Query(ctx, q, args...)
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}
	defer rows.Close()

	var brands []filteredBrand
	for rows.Next() {
		var b filteredBrand
		if err := rows.Scan(&b.ID, &b.Brand); err != nil {
			continue
		}
		brands = append(brands, b)
	}

	if c.GetHeader("HX-Request") != "" {
		var html strings.Builder
		html.WriteString(`<option selected value="">Marca</option>`)
		for _, b := range brands {
			html.WriteString(fmt.Sprintf(`<option value="%s">%s</option>`, b.Brand, b.Brand))
		}
		c.Header("Content-Type", "text/html; charset=utf-8")
		c.String(http.StatusOK, html.String())
	} else {
		c.JSON(http.StatusOK, brands)
	}
}

type filteredModel struct {
	ID    int32  `json:"id"`
	Model string `json:"model"`
}

func (tr *TemplateRenderer) GetFilteredModels(c *gin.Context) {
	vehicleType := c.Query("vehicle_type")
	productType := c.Query("product_type")
	brandName := c.Query("brand")
	if vehicleType == "" || brandName == "" {
		c.JSON(http.StatusBadRequest, gin.H{"error": "vehicle_type and brand are required"})
		return
	}

	ctx := c.Request.Context()
	allBrands, err := tr.queries.GetVehicleBrands(ctx, catalogue.GetVehicleBrandsParams{Limit: 1000000000, Offset: 0})
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}
	var brandID int32
	for _, b := range allBrands {
		if strings.EqualFold(b.Brand, brandName) {
			brandID = b.ID
			break
		}
	}
	if brandID <= 0 {
		c.JSON(http.StatusOK, []filteredModel{})
		return
	}

	baseSelect := `
		SELECT DISTINCT vmy.id, vmy.model
		FROM vehicle_model_year vmy
		JOIN vehicle_type vt ON vmy."fk_idVehicleType" = vt.id
		JOIN %s s ON s."fk_idVehicleModelYear" = vmy.id
		WHERE LOWER(vt.type) = LOWER($1)
		AND vmy."fk_idVehicleBrand" = $2`

	var q string
	if productType == "" || productType == "todos" {
		tables := []string{"shock_brand_specs", "brake_brand_specs", "disk_brand_specs", "strut_brand_specs", "leg_brand_specs"}
		parts := make([]string, len(tables))
		for i, t := range tables {
			parts[i] = fmt.Sprintf(baseSelect, t)
		}
		q = "SELECT DISTINCT id, model FROM (" + strings.Join(parts, " UNION ") + ") sub ORDER BY model"
	} else {
		table, _ := specTable(productType)
		if table == "" {
			c.JSON(http.StatusBadRequest, gin.H{"error": "invalid product_type"})
			return
		}
		q = fmt.Sprintf(baseSelect+" ORDER BY vmy.model", table)
	}

	rows, err := tr.db.Query(ctx, q, vehicleType, brandID)
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}
	defer rows.Close()

	var models []filteredModel
	for rows.Next() {
		var m filteredModel
		if err := rows.Scan(&m.ID, &m.Model); err != nil {
			continue
		}
		models = append(models, m)
	}

	c.JSON(http.StatusOK, models)
}

var discreteYearPattern = regexp.MustCompile(`^\d{4}$`)
var yearRangePattern = regexp.MustCompile(`^(\d{4})\s*-\s*(\d{4})$`)

const maxYearRangeSpan = 100

// expandYearValue returns the years a raw years/yearsRange value represents:
// comma-separated pieces count as individual years, hyphen-separated
// yyyy-yyyy pieces expand to every year in the series, anything else is
// ignored.
func expandYearValue(raw string) []string {
	years := make(map[string]bool)
	for _, piece := range strings.Split(raw, ",") {
		piece = strings.TrimSpace(piece)
		if piece == "" {
			continue
		}
		if discreteYearPattern.MatchString(piece) {
			years[piece] = true
			continue
		}
		if endpoints := yearRangePattern.FindStringSubmatch(piece); endpoints != nil {
			start, startErr := strconv.Atoi(endpoints[1])
			end, endErr := strconv.Atoi(endpoints[2])
			if startErr != nil || endErr != nil {
				continue
			}
			lo, hi := start, end
			if lo > hi {
				lo, hi = hi, lo
			}
			if hi-lo > maxYearRangeSpan {
				continue
			}
			for year := lo; year <= hi; year++ {
				years[strconv.Itoa(year)] = true
			}
		}
	}

	result := make([]string, 0, len(years))
	for year := range years {
		result = append(result, year)
	}
	return result
}

func (tr *TemplateRenderer) GetYears(c *gin.Context) {
	makeName := c.Query("make")
	modelName := c.Query("model")
	if makeName == "" || modelName == "" {
		c.JSON(http.StatusOK, []string{})
		return
	}

	ctx := c.Request.Context()

	// Match the exact model OR any variant that begins with "<model> <suffix>".
	// This keeps "Versa I" isolated from "Versa II" while letting a bare
	// "Versa" search aggregate years across Versa, Versa I, Versa II, etc.
	matchingIDsQuery := `
		SELECT v.id
		FROM vehicle_model_year v
		JOIN vehicle_brands vb ON v."fk_idVehicleBrand" = vb.id
		WHERE LOWER(vb.brand) = LOWER($1)
		  AND (LOWER(v.model) = LOWER($2) OR LOWER(v.model) LIKE LOWER($2) || ' %')
	`
	rows, err := tr.db.Query(ctx, matchingIDsQuery, makeName, modelName)
	if err != nil {
		c.JSON(http.StatusOK, []string{})
		return
	}
	var modelIDs []int32
	for rows.Next() {
		var id int32
		if err := rows.Scan(&id); err == nil {
			modelIDs = append(modelIDs, id)
		}
	}
	rows.Close()

	if len(modelIDs) == 0 {
		c.JSON(http.StatusOK, []string{})
		return
	}

	yearSet := make(map[string]bool)

	specTables := []string{
		"shock_brand_specs",
		"brake_brand_specs",
		"disk_brand_specs",
		"strut_brand_specs",
		"leg_brand_specs",
	}

	for _, table := range specTables {
		for _, col := range []string{"years", "yearsRange"} {
			q := fmt.Sprintf(`SELECT DISTINCT "%s" FROM %s WHERE "fk_idVehicleModelYear" = ANY($1::int[]) AND "%s" IS NOT NULL AND "%s" != ''`, col, table, col, col)
			rows, err := tr.db.Query(ctx, q, modelIDs)
			if err != nil {
				continue
			}
			for rows.Next() {
				var val string
				if err := rows.Scan(&val); err == nil && val != "" {
					yearSet[val] = true
				}
			}
			rows.Close()
		}
	}

	expanded := make(map[string]bool)
	for raw := range yearSet {
		for _, year := range expandYearValue(raw) {
			expanded[year] = true
		}
	}

	unique := make([]string, 0, len(expanded))
	for year := range expanded {
		unique = append(unique, year)
	}

	sort.Slice(unique, func(i, j int) bool {
		return unique[i] > unique[j]
	})

	c.JSON(http.StatusOK, unique)
}

type ExternalProductResponse struct {
	Count   int `json:"count"`
	Results []struct {
		Codigo        string  `json:"codigo"`
		Descripcion   string  `json:"descripcion"`
		Precio        float64 `json:"precio"`
		PrecioNeto    float64 `json:"precio_neto"`
		Moneda        string  `json:"moneda"`
		InfoAlmacenes []struct {
			Almacen struct {
				Nombre string `json:"nombre"`
			} `json:"almacen"`
			Disponible     float64 `json:"disponible"`
			PrecioUnitario float64 `json:"precio_unitario"`
		} `json:"info_almacenes"`
	} `json:"results"`
}

func transformCodigo(codigo, brand, ptype, vehicleType string) []string {
	if strings.EqualFold(brand, "Cano") {
		return []string{codigo + " -C"}
	}
	if strings.EqualFold(brand, "GRC") && ptype == "shock" {
		if strings.EqualFold(vehicleType, "Pesado") {
			// Try most-specific codes first to avoid false-positive matches.
			return []string{
				"ARV0" + codigo + "02I",
				"ARV0" + codigo + "02Y",
				"ARV0" + codigo,
				codigo,
			}
		}
		return []string{"ARV" + codigo}
	}
	return []string{codigo}
}

var (
	admintotalHTTPClient = &http.Client{
		Timeout: 5 * time.Second,
		Transport: &http.Transport{
			MaxIdleConns:        20,
			MaxIdleConnsPerHost: 10,
			IdleConnTimeout:     30 * time.Second,
		},
	}
	admintotalQueue = make(chan admintotalRequest, 64)
)

type admintotalRequest struct {
	codigo string
	result chan admintotalResult
}

type admintotalResult struct {
	resp *ExternalProductResponse
	err  error
}

func init() {
	go admintotalWorker()
}

func admintotalRequestInterval() time.Duration {
	if v := os.Getenv("ADMINTOTAL_REQUEST_INTERVAL_SECONDS"); v != "" {
		if n, err := strconv.Atoi(v); err == nil && n > 0 {
			return time.Duration(n) * time.Second
		}
	}
	return 1 * time.Second
}

func admintotalWorker() {
	interval := admintotalRequestInterval()
	var lastCall time.Time
	for req := range admintotalQueue {
		if elapsed := time.Since(lastCall); elapsed < interval {
			time.Sleep(interval - elapsed)
		}
		resp, err := fetchAdmintotalProductHTTP(context.Background(), req.codigo)
		lastCall = time.Now()
		req.result <- admintotalResult{resp: resp, err: err}
	}
}

func fetchAdmintotalProductHTTP(ctx context.Context, codigo string) (*ExternalProductResponse, error) {
	req, _ := http.NewRequestWithContext(ctx, "GET", "https://geitqueretaro.admintotal.com/api/v2/productos/", nil)
	q := req.URL.Query()
	q.Add("codigo", codigo)
	req.URL.RawQuery = q.Encode()
	req.Header.Set("Api-key", "QXU2DZWRAUER58QHTS872LWTG57BYYFFPWU")
	req.Header.Set("User-Agent", "MIPDSA/1.0")

	resp, err := admintotalHTTPClient.Do(req)
	if err != nil {
		return nil, err
	}
	if resp.StatusCode != http.StatusOK {
		return nil, fmt.Errorf("admintotal returned status %d", resp.StatusCode)
	}
	defer resp.Body.Close()

	var extResp ExternalProductResponse
	if err := json.NewDecoder(resp.Body).Decode(&extResp); err != nil || extResp.Count == 0 || len(extResp.Results) == 0 {
		return nil, fmt.Errorf("no results")
	}
	return &extResp, nil
}

func fetchAdmintotalProduct(ctx context.Context, codigo string) (*ExternalProductResponse, error) {
	req := admintotalRequest{
		codigo: codigo,
		result: make(chan admintotalResult, 1),
	}
	select {
	case admintotalQueue <- req:
	case <-ctx.Done():
		return nil, ctx.Err()
	}
	select {
	case res := <-req.result:
		return res.resp, res.err
	case <-ctx.Done():
		return nil, ctx.Err()
	}
}

// fetchAdmintotalProductVariants tries each transformed code in order and
// returns the first admintotal response that has at least one result.
func fetchAdmintotalProductVariants(codigos []string) (*ExternalProductResponse, error) {
	if len(codigos) == 0 {
		return nil, fmt.Errorf("no codes to lookup")
	}

	ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
	defer cancel()

	var lastErr error
	for _, c := range codigos {
		r, err := fetchAdmintotalProduct(ctx, c)
		if err == nil && r != nil && len(r.Results) > 0 {
			return r, nil
		}
		if err != nil {
			lastErr = err
		}
	}
	if lastErr != nil {
		return nil, lastErr
	}
	return nil, fmt.Errorf("no results")
}

func (tr *TemplateRenderer) GetProductPrice(c *gin.Context) {
	tr.fetchExternalProductField(c, "price")
}

func (tr *TemplateRenderer) GetProductPriceTag(c *gin.Context) {
	codigo := c.Query("codigo")
	brand := c.Query("brand")
	ptype := c.Query("type")
	vehicleType := c.Query("vehicle_type")
	if codigo == "" {
		c.JSON(http.StatusOK, gin.H{"found": false})
		return
	}
	codigos := transformCodigo(codigo, brand, ptype, vehicleType)

	extResp, err := fetchAdmintotalProductVariants(codigos)
	if err != nil {
		c.JSON(http.StatusOK, gin.H{"found": false})
		return
	}
	if extResp == nil || len(extResp.Results) == 0 {
		c.JSON(http.StatusOK, gin.H{"found": false})
		return
	}

	result := extResp.Results[0]
	var price float64
	if len(result.InfoAlmacenes) > 0 && result.InfoAlmacenes[0].PrecioUnitario > 0 {
		price = result.InfoAlmacenes[0].PrecioUnitario
	} else if result.Precio > 0 {
		price = result.Precio
	}
	if price <= 0 {
		c.JSON(http.StatusOK, gin.H{"found": false})
		return
	}
	c.JSON(http.StatusOK, gin.H{
		"found": true,
		"price": fmt.Sprintf("$%.2f %s", price, result.Moneda),
	})
}

func (tr *TemplateRenderer) GetProductStock(c *gin.Context) {
	tr.fetchExternalProductField(c, "stock")
}

func (tr *TemplateRenderer) GetProductBodegas(c *gin.Context) {
	codigo := c.Query("codigo")
	brand := c.Query("brand")
	ptype := c.Query("type")
	vehicleType := c.Query("vehicle_type")
	if codigo == "" {
		c.String(http.StatusOK, "Sin información")
		return
	}
	codigos := transformCodigo(codigo, brand, ptype, vehicleType)

	extResp, err := fetchAdmintotalProductVariants(codigos)
	if err != nil {
		c.String(http.StatusOK, "Sin información")
		return
	}
	if extResp == nil || len(extResp.Results) == 0 {
		c.String(http.StatusOK, "Sin información")
		return
	}

	result := extResp.Results[0]
	var html strings.Builder
	html.WriteString(`<table class="table" style="width:100%;border-collapse:collapse;margin-top:8px;"><thead><tr style="border-bottom:2px solid #e5e7eb;"><th style="text-align:left;padding:8px;">Almacén</th><th style="text-align:left;padding:8px;">Stock</th><th style="text-align:left;padding:8px;">Precio</th></tr></thead><tbody>`)
	if len(result.InfoAlmacenes) == 0 {
		html.WriteString(`<tr><td colspan="3" style="padding:8px;">Sin información de almacenes</td></tr>`)
	} else {
		for _, alm := range result.InfoAlmacenes {
			price := "N/A"
			if alm.PrecioUnitario > 0 {
				price = fmt.Sprintf("$%.2f %s", alm.PrecioUnitario, result.Moneda)
			}
			stock := fmt.Sprintf("%.0f", alm.Disponible)
			html.WriteString(fmt.Sprintf(`<tr style="border-bottom:1px solid #e5e7eb;"><td style="padding:8px;">%s</td><td style="padding:8px;">%s</td><td style="padding:8px;">%s</td></tr>`, template.HTMLEscapeString(alm.Almacen.Nombre), stock, price))
		}
	}
	html.WriteString(`</tbody></table>`)
	c.Header("Content-Type", "text/html; charset=utf-8")
	c.String(http.StatusOK, html.String())
}

type ProductLookupResponse struct {
	Found      bool   `json:"found"`
	Transient  bool   `json:"transient"`
	StockText  string `json:"stockText"`
	StockState string `json:"stockState"` // "in", "out", "unknown"
	PriceText  string `json:"priceText"`
}

func isTransientAdmintotalError(err error) bool {
	if err == nil {
		return false
	}
	if errors.Is(err, context.DeadlineExceeded) || errors.Is(err, context.Canceled) {
		return true
	}
	msg := err.Error()
	return strings.Contains(msg, "429") || strings.Contains(msg, "502") ||
		strings.Contains(msg, "503") || strings.Contains(msg, "504")
}

func (tr *TemplateRenderer) GetProductLookup(c *gin.Context) {
	codigo := c.Query("codigo")
	brand := c.Query("brand")
	ptype := c.Query("type")
	vehicleType := c.Query("vehicle_type")
	if codigo == "" {
		c.JSON(http.StatusOK, ProductLookupResponse{Found: false, StockState: "unknown"})
		return
	}
	codigos := transformCodigo(codigo, brand, ptype, vehicleType)

	extResp, err := fetchAdmintotalProductVariants(codigos)
	if err != nil || extResp == nil || len(extResp.Results) == 0 {
		c.JSON(http.StatusOK, ProductLookupResponse{
			Found:      false,
			Transient:  isTransientAdmintotalError(err),
			StockState: "unknown",
		})
		return
	}

	result := extResp.Results[0]

	var price float64
	if len(result.InfoAlmacenes) > 0 && result.InfoAlmacenes[0].PrecioUnitario > 0 {
		price = result.InfoAlmacenes[0].PrecioUnitario
	} else if result.Precio > 0 {
		price = result.Precio
	}

	var totalStock float64
	for _, alm := range result.InfoAlmacenes {
		totalStock += alm.Disponible
	}

	resp := ProductLookupResponse{Found: true}
	if totalStock > 0 {
		resp.StockText = fmt.Sprintf("%.0f unidades", totalStock)
		resp.StockState = "in"
	} else {
		resp.StockText = "Sin stock"
		resp.StockState = "out"
	}
	if price > 0 {
		resp.PriceText = fmt.Sprintf("$%.2f %s", price, result.Moneda)
	}

	c.JSON(http.StatusOK, resp)
}

func (tr *TemplateRenderer) fetchExternalProductField(c *gin.Context, field string) {
	codigo := c.Query("codigo")
	brand := c.Query("brand")
	ptype := c.Query("type")
	vehicleType := c.Query("vehicle_type")
	if codigo == "" {
		c.String(http.StatusOK, "N/A")
		return
	}
	codigos := transformCodigo(codigo, brand, ptype, vehicleType)

	extResp, err := fetchAdmintotalProductVariants(codigos)
	if err != nil || extResp == nil || len(extResp.Results) == 0 {
		c.String(http.StatusOK, "N/A")
		return
	}

	result := extResp.Results[0]
	if field == "price" {
		var price float64
		if len(result.InfoAlmacenes) > 0 && result.InfoAlmacenes[0].PrecioUnitario > 0 {
			price = result.InfoAlmacenes[0].PrecioUnitario
		} else if result.Precio > 0 {
			price = result.Precio
		}
		if price > 0 {
			c.String(http.StatusOK, fmt.Sprintf("$%.2f %s", price, result.Moneda))
		} else {
			c.String(http.StatusOK, "N/A")
		}
	} else {
		var totalStock float64
		for _, alm := range result.InfoAlmacenes {
			totalStock += alm.Disponible
		}
		if totalStock > 0 {
			c.String(http.StatusOK, fmt.Sprintf("%.0f unidades", totalStock))
		} else {
			c.String(http.StatusOK, "Sin stock")
		}
	}
}

func (tr *TemplateRenderer) resolveMakeModel(ctx context.Context, modelYearID int32) (string, string, error) {
	allModels, err := tr.queries.GetVehicleModelYears(ctx, catalogue.GetVehicleModelYearsParams{Limit: 1000000000, Offset: 0})
	if err != nil {
		return "", "", err
	}
	var targetModel catalogue.VehicleModelYear
	for _, m := range allModels {
		if m.ID == modelYearID {
			targetModel = m
			break
		}
	}
	if targetModel.ID == 0 {
		return "", "", fmt.Errorf("model not found")
	}

	brands, _ := tr.queries.GetVehicleBrands(ctx, catalogue.GetVehicleBrandsParams{Limit: 1000000000, Offset: 0})
	for _, b := range brands {
		if b.ID == targetModel.FkIdVehicleBrand.Int32 {
			return b.Brand, targetModel.Model.String, nil
		}
	}
	return "", targetModel.Model.String, nil
}

func (tr *TemplateRenderer) findVehicleMakeModel(modelYearID int32) (string, string) {
	make, model, _ := tr.resolveMakeModel(context.Background(), modelYearID)
	return make, model
}

func notFound(partNumber string) error {
	return fmt.Errorf("producto %s no encontrado", partNumber)
}

func (tr *TemplateRenderer) fetchProductByPartNumber(ctx context.Context, productType, partNumber string) (NormalizedProduct, []Application, error) {
	pn := pgtype.Text{String: partNumber, Valid: partNumber != ""}
	switch productType {
	case "shock":
		rows, err := tr.queries.GetAllShockBrandSpecsByPartNumber(ctx, pn)
		if err != nil || len(rows) == 0 {
			return NormalizedProduct{}, nil, notFound(partNumber)
		}
		return normalizeShockProduct(rows[0]), buildShockApplications(rows), nil
	case "brake":
		rows, err := tr.queries.GetAllBrakeBrandSpecsByPartNumber(ctx, pn)
		if err != nil || len(rows) == 0 {
			return NormalizedProduct{}, nil, notFound(partNumber)
		}
		return normalizeBrakeProduct(rows[0]), buildBrakeApplications(rows), nil
	case "disk":
		rows, err := tr.queries.GetAllDiskBrandSpecsByPartNumber(ctx, pn)
		if err != nil || len(rows) == 0 {
			return NormalizedProduct{}, nil, notFound(partNumber)
		}
		return normalizeDiskProduct(rows[0]), buildDiskApplications(rows), nil
	case "strut":
		rows, err := tr.queries.GetAllStrutBrandSpecsByPartNumber(ctx, pn)
		if err != nil || len(rows) == 0 {
			return NormalizedProduct{}, nil, notFound(partNumber)
		}
		return normalizeStrutProduct(rows[0]), buildStrutApplications(rows), nil
	case "leg":
		rows, err := tr.queries.GetAllLegBrandSpecsByPartNumber(ctx, pn)
		if err != nil || len(rows) == 0 {
			return NormalizedProduct{}, nil, notFound(partNumber)
		}
		return normalizeLegProduct(rows[0]), buildLegApplications(rows), nil
	case "extra":
		s, err := tr.queries.GetExtraTypeProductByType(ctx, pn)
		if err != nil {
			return NormalizedProduct{}, nil, err
		}
		return NormalizedProduct{
			ID: s.ID, PartNumber: s.Type.String, Product: s.Name.String,
			Serie: s.Notes.String, VehicleAttrs: s.Application.String,
		}, nil, nil
	default:
		return NormalizedProduct{}, nil, fmt.Errorf("tipo de producto desconocido")
	}
}

func applicationFromVehicleRow(make, model, years, yearsRange, position string) Application {
	return Application{
		Make:       make,
		Model:      model,
		Years:      years,
		YearsRange: yearsRange,
		Position:   position,
	}
}

func hasSpecValue(s string) bool {
	if s == "" {
		return false
	}
	u := strings.ToUpper(s)
	return u != "MISSING" && u != "N/A" && u != "NA" && u != "S/N" && u != "NO REQ."
}

func textSpec(label string, v pgtype.Text) (SpecAttr, bool) {
	if !v.Valid || !hasSpecValue(v.String) {
		return SpecAttr{}, false
	}
	return SpecAttr{Label: label, Value: v.String}, true
}

func intSpec(label string, v pgtype.Int4) (SpecAttr, bool) {
	if !v.Valid || v.Int32 <= 0 {
		return SpecAttr{}, false
	}
	return SpecAttr{Label: label, Value: fmt.Sprintf("%d", v.Int32)}, true
}

func appendSpec(specs []SpecAttr, label string, v pgtype.Text) []SpecAttr {
	if s, ok := textSpec(label, v); ok {
		return append(specs, s)
	}
	return specs
}

func appendIntSpec(specs []SpecAttr, label string, v pgtype.Int4) []SpecAttr {
	if s, ok := intSpec(label, v); ok {
		return append(specs, s)
	}
	return specs
}

func normalizeShockProduct(s catalogue.GetAllShockBrandSpecsByPartNumberRow) NormalizedProduct {
	extra := []SpecAttr{}
	extra = appendSpec(extra, "Accesorios", s.Accesories)
	extra = appendIntSpec(extra, "Longitud extendida (mm)", s.ExtendedLong)
	extra = appendIntSpec(extra, "Longitud comprimida (mm)", s.CompressedLong)
	extra = appendIntSpec(extra, "Carrera (mm)", s.Extension)
	extra = appendSpec(extra, "Montaje superior", s.SuperiorMount)
	extra = appendSpec(extra, "Montaje inferior", s.InferiorMount)
	extra = appendSpec(extra, "Ficha técnica", s.TechnicalSheet)

	return NormalizedProduct{
		ID: s.ID, PartNumber: s.PartNumber.String, Product: s.Product.String,
		Serie: s.Serie.String, Quantity: fmt.Sprintf("%d", s.Quantity.Int16),
		Tier: s.Tier.String, Position: s.Position.String,
		ImageProduct: s.ImageProduct.String, ImageFallbacks: productImageFallbacks(s.BrandName, "shock", s.PartNumber.String, s.Position.String),
		VehicleAttrs: s.VehicleAttrs.String,
		Notes:        s.Notes.String, Years: s.Years.String, YearsRange: s.YearsRange.String,
		BrandName: s.BrandName, ExtraSpecs: extra,
	}
}

func buildShockApplications(rows []catalogue.GetAllShockBrandSpecsByPartNumberRow) []Application {
	apps := make([]Application, len(rows))
	for i, r := range rows {
		apps[i] = applicationFromVehicleRow(r.VehicleMake, r.VehicleModel.String, r.Years.String, r.YearsRange.String, r.Position.String)
	}
	return apps
}

func normalizeBrakeProduct(s catalogue.GetAllBrakeBrandSpecsByPartNumberRow) NormalizedProduct {
	extra := []SpecAttr{}
	extra = appendSpec(extra, "FMSI", s.Fmsi)
	extra = appendSpec(extra, "Incluye herraje", s.IncludesHardware)
	extra = appendSpec(extra, "Dimensiones de imagen", s.ImageProductDimentions)
	extra = appendSpec(extra, "Ficha técnica", s.TechnicalSheet)

	var diagram string
	if strings.EqualFold(s.BrandName, "GRC") {
		diagram = grcBrakeDiagramURL(s.PartNumber.String)
	}

	return NormalizedProduct{
		ID: s.ID, PartNumber: s.PartNumber.String, Product: s.Product.String,
		Serie: s.Serie.String, Quantity: fmt.Sprintf("%d", s.Quantity.Int32),
		Tier: s.Tier.String, Position: s.Position.String,
		ImageProduct: s.ImageProduct.String, ImageFallbacks: productImageFallbacks(s.BrandName, "brake", s.PartNumber.String, s.Position.String),
		DiagramImage: diagram,
		VehicleAttrs: s.VehicleAttrs.String,
		Years:        s.Years.String, YearsRange: s.YearsRange.String,
		BrandName: s.BrandName, ExtraSpecs: extra,
	}
}

func buildBrakeApplications(rows []catalogue.GetAllBrakeBrandSpecsByPartNumberRow) []Application {
	apps := make([]Application, len(rows))
	for i, r := range rows {
		apps[i] = applicationFromVehicleRow(r.VehicleMake, r.VehicleModel.String, r.Years.String, r.YearsRange.String, r.Position.String)
	}
	return apps
}

func normalizeDiskProduct(s catalogue.GetAllDiskBrandSpecsByPartNumberRow) NormalizedProduct {
	extra := []SpecAttr{}
	extra = appendSpec(extra, "Número de parte MIP", s.MipPartNumber)
	extra = appendSpec(extra, "Descripción", s.Description)
	extra = appendSpec(extra, "Tipo de disco", s.Type)
	extra = appendSpec(extra, "Espesor mínimo", s.MinThikness)
	extra = appendSpec(extra, "Birlos", s.LugNuts)
	extra = appendSpec(extra, "Diámetro original", s.OgDiameter)
	extra = appendSpec(extra, "Diámetro máximo", s.MaxDiameter)
	extra = appendSpec(extra, "Materiales", s.Materials)
	extra = appendSpec(extra, "Diámetro piloto", s.PiDiameter)
	extra = appendSpec(extra, "Altura", s.Height)
	extra = appendSpec(extra, "Incluye herraje", s.IncludesHardware)
	extra = appendSpec(extra, "Dimensiones de imagen", s.ImageProductDimentions)
	extra = appendSpec(extra, "Ficha técnica", s.TechnicalSheet)

	return NormalizedProduct{
		ID: s.ID, PartNumber: s.PartNumber.String, Product: s.Product.String,
		Serie: s.Serie.String, Quantity: fmt.Sprintf("%d", s.Quantity.Int32),
		Tier: s.Tier.String, Position: s.Position.String,
		ImageProduct: s.ImageProduct.String, ImageFallbacks: productImageFallbacks(s.BrandName, "disk", s.PartNumber.String, s.Position.String),
		VehicleAttrs: s.VehicleAttrs.String,
		Years:        s.Years.String, YearsRange: s.YearsRange.String,
		BrandName: s.BrandName, ExtraSpecs: extra,
	}
}

func buildDiskApplications(rows []catalogue.GetAllDiskBrandSpecsByPartNumberRow) []Application {
	apps := make([]Application, len(rows))
	for i, r := range rows {
		apps[i] = applicationFromVehicleRow(r.VehicleMake, r.VehicleModel.String, r.Years.String, r.YearsRange.String, r.Position.String)
	}
	return apps
}

func normalizeStrutProduct(s catalogue.GetAllStrutBrandSpecsByPartNumberRow) NormalizedProduct {
	extra := []SpecAttr{}
	extra = appendSpec(extra, "Accesorios", s.Accesories)
	extra = appendSpec(extra, "Ficha técnica", s.TechnicalSheet)

	return NormalizedProduct{
		ID: s.ID, PartNumber: s.PartNumber.String, Product: s.Product.String,
		Serie: s.Serie.String, Quantity: fmt.Sprintf("%d", s.Quantity.Int16),
		Tier: s.Tier.String, Position: s.Position.String,
		ImageProduct: s.ImageProduct.String, ImageFallbacks: productImageFallbacks(s.BrandName, "strut", s.PartNumber.String, s.Position.String),
		VehicleAttrs: s.VehicleAttrs.String,
		Notes:        s.Notes.String, Years: s.Years.String, YearsRange: s.YearsRange.String,
		BrandName: s.BrandName, ExtraSpecs: extra,
	}
}

func buildStrutApplications(rows []catalogue.GetAllStrutBrandSpecsByPartNumberRow) []Application {
	apps := make([]Application, len(rows))
	for i, r := range rows {
		apps[i] = applicationFromVehicleRow(r.VehicleMake, r.VehicleModel.String, r.Years.String, r.YearsRange.String, r.Position.String)
	}
	return apps
}

func normalizeLegProduct(s catalogue.GetAllLegBrandSpecsByPartNumberRow) NormalizedProduct {
	extra := []SpecAttr{}
	extra = appendSpec(extra, "Accesorios", s.Accesories)
	extra = appendSpec(extra, "Ficha técnica", s.TechnicalSheet)

	return NormalizedProduct{
		ID: s.ID, PartNumber: s.PartNumber.String, Product: s.Product.String,
		Serie: s.Serie.String, Quantity: fmt.Sprintf("%d", s.Quantity.Int16),
		Tier: s.Tier.String, Position: s.Position.String,
		ImageProduct: s.ImageProduct.String, ImageFallbacks: productImageFallbacks(s.BrandName, "leg", s.PartNumber.String, s.Position.String),
		VehicleAttrs: s.VehicleAttrs.String,
		Notes:        s.Notes.String, Years: s.Years.String, YearsRange: s.YearsRange.String,
		BrandName: s.BrandName, ExtraSpecs: extra,
	}
}

func buildLegApplications(rows []catalogue.GetAllLegBrandSpecsByPartNumberRow) []Application {
	apps := make([]Application, len(rows))
	for i, r := range rows {
		apps[i] = applicationFromVehicleRow(r.VehicleMake, r.VehicleModel.String, r.Years.String, r.YearsRange.String, r.Position.String)
	}
	return apps
}