package app

import (
	"context"
	"time"

	"spread/internal/spread"
)

// CatalogEntry is one admin-managed supplier price.
type CatalogEntry struct {
	PartNumber  string
	Description string
	SpreadPrice float64
	UpdatedAt   time.Time
}

func (a *App) listCatalog(ctx context.Context) ([]CatalogEntry, error) {
	rows, err := a.pool.Query(ctx,
		`SELECT part_number, description, spread_price, updated_at
		   FROM catalog ORDER BY part_number`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []CatalogEntry
	for rows.Next() {
		var e CatalogEntry
		if err := rows.Scan(&e.PartNumber, &e.Description, &e.SpreadPrice, &e.UpdatedAt); err != nil {
			return nil, err
		}
		out = append(out, e)
	}
	return out, rows.Err()
}

func (a *App) getCatalogEntry(ctx context.Context, pn string) (CatalogEntry, error) {
	var e CatalogEntry
	err := a.pool.QueryRow(ctx,
		`SELECT part_number, description, spread_price, updated_at
		   FROM catalog WHERE part_number = $1`, spread.NormalizePN(pn),
	).Scan(&e.PartNumber, &e.Description, &e.SpreadPrice, &e.UpdatedAt)
	return e, err
}

func (a *App) upsertCatalog(ctx context.Context, pn, desc string, price float64) error {
	_, err := a.pool.Exec(ctx,
		`INSERT INTO catalog (part_number, description, spread_price, updated_at)
		 VALUES ($1, $2, $3, now())
		 ON CONFLICT (part_number)
		 DO UPDATE SET description = EXCLUDED.description,
		               spread_price = EXCLUDED.spread_price,
		               updated_at = now()`,
		spread.NormalizePN(pn), desc, price)
	return err
}

func (a *App) deleteCatalog(ctx context.Context, pn string) error {
	_, err := a.pool.Exec(ctx, `DELETE FROM catalog WHERE part_number = $1`, spread.NormalizePN(pn))
	return err
}

func (a *App) catalogCount(ctx context.Context) (int, error) {
	var n int
	err := a.pool.QueryRow(ctx, `SELECT count(*) FROM catalog`).Scan(&n)
	return n, err
}

// catalogSource loads the whole catalog into memory as a spread.Catalog so a
// BOM can be re-quoted with a single query instead of one per line.
func (a *App) catalogSource(ctx context.Context) (spread.Catalog, error) {
	rows, err := a.pool.Query(ctx, `SELECT part_number, spread_price FROM catalog`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	m := mapCatalog{}
	for rows.Next() {
		var pn string
		var price float64
		if err := rows.Scan(&pn, &price); err != nil {
			return nil, err
		}
		m[pn] = price
	}
	return m, rows.Err()
}

type mapCatalog map[string]float64

func (m mapCatalog) Lookup(pn string) (float64, bool) {
	p, ok := m[spread.NormalizePN(pn)]
	return p, ok
}

// demoCatalog mirrors the five parts on the landing page so a fresh admin can
// seed a working catalog with one click.
var demoCatalog = []CatalogEntry{
	{"GRM188R71H104KA93D", "0603 0.1µF Capacitor", 0.0142, time.Time{}},
	{"DIN 912", "M3×10 Hex Cap Screw, A2 SS", 0.0335, time.Time{}},
	{"UL1007", "22 AWG Hookup Wire, 3m", 0.9450, time.Time{}},
	{"STM32F103C8T6", "STM32F103C8T6 MCU LQFP-48", 2.9200, time.Time{}},
	{"RC0805FR-0710KL", "0805 10kΩ Resistor, 1%", 0.0086, time.Time{}},
}

func (a *App) seedDemoCatalog(ctx context.Context) error {
	for _, e := range demoCatalog {
		if err := a.upsertCatalog(ctx, e.PartNumber, e.Description, e.SpreadPrice); err != nil {
			return err
		}
	}
	return nil
}