package catalog

import (
	"context"
	"errors"
	"fmt"
	"strings"
	"time"
	"unicode"

	"github.com/TaruLab/keluargamart-backend/internal/auth"
	"github.com/TaruLab/keluargamart-backend/internal/db"
	"github.com/jackc/pgx/v5/pgtype"
	"github.com/shopspring/decimal"
)

// Sentinel domain errors mapped to HTTP in the handler.
var (
	ErrNotFound            = errors.New("not found")
	ErrDuplicateName       = errors.New("name already exists")
	ErrDuplicateCode       = errors.New("code already exists")
	ErrInUse               = errors.New("resource in use")
	ErrCategoryRequired    = errors.New("category is required")
	ErrUnitRequired        = errors.New("unit is required")
	ErrBrandRequired       = errors.New("brand does not exist")
	ErrDuplicateUnitConfig = errors.New("unit is already configured for this product")
	ErrDuplicateBarcode    = errors.New("barcode already exists")
	ErrInvalidConversion   = errors.New("conversion_factor must be greater than 0")
	ErrInvalidMinPurchase  = errors.New("min_purchase must be greater than 0")
	ErrInvalidUnitPrice    = errors.New("selling_price must not be negative")
	ErrBaseUnitConversion  = errors.New("base unit must have conversion_factor 1")
	ErrBarcodeRequired     = errors.New("barcode is required")
	ErrUnitProductMismatch = errors.New("product_unit does not belong to this product")
)

// Service implements catalog use cases.
type Service struct {
	store Store
	seq   Seq
	loc   *time.Location
}

// Seq allocates atomic counters (pkg/seq) — injected so tests fake it.
type Seq interface {
	// Sequence returns the next per-day or global sequence for a prefix.
	// tx is a pgx.Tx when inside a unit of work, nil otherwise.
	Sequence(ctx context.Context, prefix string, date time.Time, tx any) (int64, error)
}

func NewService(store Store, seq Seq, loc *time.Location) *Service {
	return &Service{store: store, seq: seq, loc: loc}
}

// normalizeProductSort maps the ERP table's requested column/direction onto
// the two values the SQL compares against. Whitelisted here (not just in the
// handler) because the result is interpolated into the query's CASE branches:
// an arbitrary string from the query string must never survive this function.
// An unknown direction keeps the column and lets SQL order it ascending.
func normalizeProductSort(sortBy, sortDir string) (string, string) {
	switch strings.ToLower(strings.TrimSpace(sortBy)) {
	case "name", "code", "selling_price", "purchase_price", "created_at", "stock":
	default:
		return "", ""
	}
	switch strings.ToLower(strings.TrimSpace(sortDir)) {
	case "asc", "desc":
		return strings.ToLower(strings.TrimSpace(sortBy)), strings.ToLower(strings.TrimSpace(sortDir))
	default:
		return strings.ToLower(strings.TrimSpace(sortBy)), ""
	}
}

// --- categories --------------------------------------------------------------

func (s *Service) ListCategories(ctx context.Context, f CatalogFilter) ([]CategoryView, int, error) {
	if f.Page < 1 {
		f.Page = 1
	}
	if f.PerPage < 1 || f.PerPage > 100 {
		f.PerPage = 20
	}
	rows, total, err := s.store.ListCategories(ctx, f)
	if err != nil {
		return nil, 0, err
	}
	out := make([]CategoryView, 0, len(rows))
	for _, r := range rows {
		out = append(out, CategoryView{
			ID:          r.ID,
			Name:        r.Name,
			Description: r.Description,
			IsActive:    r.IsActive,
		})
	}
	return out, total, nil
}

func (s *Service) GetCategory(ctx context.Context, id int64) (*CategoryView, error) {
	r, err := s.store.CategoryByID(ctx, id)
	if err != nil {
		return nil, err
	}
	return &CategoryView{
		ID:          r.ID,
		Name:        r.Name,
		Description: r.Description,
		IsActive:    r.IsActive,
	}, nil
}

func (s *Service) CreateCategory(ctx context.Context, in CategoryInput) (*CategoryView, error) {
	name := strings.TrimSpace(in.Name)
	if name == "" {
		return nil, errors.New("name is required")
	}
	exists, err := s.store.CategoryNameExists(ctx, name, 0)
	if err != nil {
		return nil, err
	}
	if exists {
		return nil, ErrDuplicateName
	}
	id, err := s.store.CreateCategory(ctx, db.CreateCategoryParams{
		Name:        name,
		Description: pgtextPtr(in.Description),
		IsActive:    in.IsActive,
	})
	if err != nil {
		return nil, err
	}
	return s.GetCategory(ctx, id)
}

func (s *Service) UpdateCategory(ctx context.Context, id int64, in CategoryInput) (*CategoryView, error) {
	// Load first so a missing row surfaces as 404 (ErrNotFound) instead of a
	// silent no-op, and so we can preserve fields the client omits.
	existing, err := s.store.CategoryByID(ctx, id)
	if err != nil {
		return nil, err
	}
	name := strings.TrimSpace(in.Name)
	if name == "" {
		// Tolerate partial updates (e.g. toggling is_active) that omit the name:
		// keep the existing value instead of rejecting with a 400.
		name = existing.Name
	} else if name != existing.Name {
		exists, err := s.store.CategoryNameExists(ctx, name, id)
		if err != nil {
			return nil, err
		}
		if exists {
			return nil, ErrDuplicateName
		}
	}
	// Omit description to preserve the existing value; an explicit "" clears it.
	desc := existing.Description
	if in.Description != nil {
		desc = *in.Description
	}
	if err := s.store.UpdateCategory(ctx, db.UpdateCategoryParams{
		ID:          id,
		Name:        name,
		Description: pgtext(desc),
		IsActive:    in.IsActive,
	}); err != nil {
		return nil, err
	}
	return s.GetCategory(ctx, id)
}

func (s *Service) DeleteCategory(ctx context.Context, id int64) error {
	err := s.store.DeleteCategory(ctx, id)
	if errors.Is(err, ErrInUse) {
		return ErrInUse
	}
	return err
}

// --- units -------------------------------------------------------------------

func (s *Service) ListUnits(ctx context.Context, f CatalogFilter) ([]UnitView, int, error) {
	if f.Page < 1 {
		f.Page = 1
	}
	if f.PerPage < 1 || f.PerPage > 100 {
		f.PerPage = 20
	}
	rows, total, err := s.store.ListUnits(ctx, f)
	if err != nil {
		return nil, 0, err
	}
	out := make([]UnitView, 0, len(rows))
	for _, r := range rows {
		sym := ""
		if r.Symbol.Valid {
			sym = r.Symbol.String
		}
		out = append(out, UnitView{
			ID:          r.ID,
			Name:        r.Name,
			Symbol:      sym,
			Description: r.Description,
		})
	}
	return out, total, nil
}

func (s *Service) CreateUnit(ctx context.Context, in UnitInput) (*UnitView, error) {
	if strings.TrimSpace(in.Name) == "" {
		return nil, errors.New("name is required")
	}
	exists, err := s.store.UnitNameExists(ctx, in.Name, 0)
	if err != nil {
		return nil, err
	}
	if exists {
		return nil, ErrDuplicateName
	}
	id, err := s.store.CreateUnit(ctx, db.CreateUnitParams{
		Name:        strings.TrimSpace(in.Name),
		Symbol:      pgtext(in.Symbol),
		Description: pgtext(in.Description),
	})
	if err != nil {
		return nil, err
	}
	return &UnitView{
		ID:          id,
		Name:        strings.TrimSpace(in.Name),
		Symbol:      in.Symbol,
		Description: in.Description,
	}, nil
}

func (s *Service) UpdateUnit(ctx context.Context, id int64, in UnitInput) (*UnitView, error) {
	if strings.TrimSpace(in.Name) == "" {
		return nil, errors.New("name is required")
	}
	exists, err := s.store.UnitNameExists(ctx, in.Name, id)
	if err != nil {
		return nil, err
	}
	if exists {
		return nil, ErrDuplicateName
	}
	err = s.store.UpdateUnit(ctx, db.UpdateUnitParams{
		ID:          id,
		Name:        strings.TrimSpace(in.Name),
		Symbol:      pgtext(in.Symbol),
		Description: pgtext(in.Description),
	})
	if err != nil {
		return nil, err
	}
	return &UnitView{
		ID:          id,
		Name:        strings.TrimSpace(in.Name),
		Symbol:      in.Symbol,
		Description: in.Description,
	}, nil
}

func (s *Service) DeleteUnit(ctx context.Context, id int64) error {
	err := s.store.DeleteUnit(ctx, id)
	if errors.Is(err, ErrInUse) {
		return ErrInUse
	}
	return err
}

// --- products ----------------------------------------------------------------

// canSeeCost reports whether the caller may see (and therefore edit) HPP.
// Copied from internal/inventory.canCost: one grant, inventory.cost.view,
// gates the product's purchase_price exactly as it gates the stock line's
// purchase_price/hpp/value, so a caller cannot read cost from the catalog
// endpoint that the stock endpoint refuses. The repository context never
// carries the user; the decision belongs on the read path here, after the
// fetch — same split inventory and finance use.
//
// Admin and owner hold the platform bypass (auth.User.Can), so their stock
// screens keep working without this key being seeded to them.
func canSeeCost(ctx context.Context) bool {
	u := auth.UserFrom(ctx)
	return u != nil && u.Can("inventory.cost.view")
}

func (s *Service) ListProducts(ctx context.Context, f ProductFilter) ([]ProductView, int, error) {
	if f.Page < 1 {
		f.Page = 1
	}
	// Cap mirrors ListStock (1000), not the 100 of the small master lists:
	// the catalog page legitimately asks per_page=200, and a silent reset
	// to 20 truncated the list so late-sorting products never reached the
	// browser — the Depot Galon filter showed empty for exactly this reason.
	if f.PerPage < 1 || f.PerPage > 1000 {
		f.PerPage = 20
	}
	rows, total, err := s.store.ListProducts(ctx, f)
	if err != nil {
		return nil, 0, err
	}
	out := make([]ProductView, 0, len(rows))
	for _, r := range rows {
		pv, err := productView(ctx, r)
		if err != nil {
			return nil, 0, err
		}
		out = append(out, *pv)
	}
	return out, total, nil
}

func (s *Service) GetProduct(ctx context.Context, id int64) (*ProductView, error) {
	r, err := s.store.ProductByID(ctx, id)
	if err != nil {
		return nil, err
	}
	return productView(ctx, r)
}

// CreateProduct validates category/unit exist, then allocates the atomic code.
func (s *Service) CreateProduct(ctx context.Context, in ProductInput) (*ProductView, error) {
	if strings.TrimSpace(in.Name) == "" {
		return nil, errors.New("name is required")
	}
	if in.CategoryID == 0 {
		return nil, ErrCategoryRequired
	}
	if in.UnitID == 0 {
		return nil, ErrUnitRequired
	}
	cat, err := s.store.CategoryByID(ctx, in.CategoryID)
	if err != nil {
		if errors.Is(err, ErrNotFound) {
			return nil, ErrCategoryRequired
		}
		return nil, err
	}
	if _, err := s.store.UnitByID(ctx, in.UnitID); err != nil {
		if errors.Is(err, ErrNotFound) {
			return nil, ErrUnitRequired
		}
		return nil, err
	}
	code, err := s.nextProductCode(ctx, cat.Name)
	if err != nil {
		return nil, err
	}
	brandID, err := s.resolveBrand(ctx, in.BrandID)
	if err != nil {
		return nil, err
	}
	// is_featured defaults to false on create when omitted.
	var isFeatured bool
	if in.IsFeatured != nil {
		isFeatured = *in.IsFeatured
	}

	row, err := s.store.CreateProduct(ctx, db.CreateProductParams{
		Code:          code,
		Name:          strings.TrimSpace(in.Name),
		Description:   pgtextPtr(in.Description),
		CategoryID:    int8(in.CategoryID),
		UnitID:        int8(in.UnitID),
		BrandID:       brandID,
		PurchasePrice: in.PurchasePrice.Round(2),
		SellingPrice:  in.SellingPrice.Round(2),
		MinStock:      in.MinStock.Round(4),
		IsActive:      in.IsActive,
		IsFeatured:    isFeatured,
		Image:         pgtextPtr(in.Image),
	})
	if err != nil {
		if errors.Is(err, ErrDuplicateCode) {
			return nil, ErrDuplicateCode
		}
		return nil, err
	}
	return s.GetProduct(ctx, row.ID)
}

func (s *Service) UpdateProduct(ctx context.Context, id int64, in ProductInput) (*ProductView, error) {
	existing, err := s.store.ProductByID(ctx, id)
	if err != nil {
		return nil, err
	}
	if strings.TrimSpace(in.Name) == "" {
		return nil, errors.New("name is required")
	}
	code := existing.Code
	// category change allowed; maybe new code prefix if category name changed
	if in.CategoryID != existing.CategoryID.Int64 {
		cat, err := s.store.CategoryByID(ctx, in.CategoryID)
		if err != nil {
			if errors.Is(err, ErrNotFound) {
				return nil, ErrCategoryRequired
			}
			return nil, err
		}
		code, err = s.nextProductCode(ctx, cat.Name)
		if err != nil {
			return nil, err
		}
	}
	if _, err := s.store.UnitByID(ctx, in.UnitID); err != nil {
		if errors.Is(err, ErrNotFound) {
			return nil, ErrUnitRequired
		}
		return nil, err
	}
	brandID, err := s.resolveBrand(ctx, in.BrandID)
	if err != nil {
		return nil, err
	}
	// Omit is_featured to preserve the stored flag; explicit true/false sets it.
	isFeatured := existing.IsFeatured
	if in.IsFeatured != nil {
		isFeatured = *in.IsFeatured
	}
	// Same partial-update contract for optional text: nil = keep stored value,
	// explicit value (incl. "") = set. pgtextPtr(nil) is SQL NULL, so mapping a
	// missing key straight through used to wipe description/image on every
	// partial product save.
	description := existing.Description
	if in.Description != nil {
		description = pgtextPtr(in.Description)
	}
	image := existing.Image
	if in.Image != nil {
		image = pgtextPtr(in.Image)
	}
	// Write-protection mirrors read-protection (canSeeCost): a caller who
	// cannot see HPP cannot change it. The ERP product form sends
	// purchase_price only when the editor holds inventory.cost.view, so
	// without this every cost-blind partial save would store zero — the same
	// wipe the description/image preservation above guards against.
	purchasePrice := existing.PurchasePrice
	if canSeeCost(ctx) {
		purchasePrice = in.PurchasePrice
	}

	err = s.store.UpdateProduct(ctx, db.UpdateProductParams{
		ID:            existing.ID,
		Code:          code,
		Name:          strings.TrimSpace(in.Name),
		Description:   description,
		CategoryID:    int8(in.CategoryID),
		UnitID:        int8(in.UnitID),
		BrandID:       brandID,
		PurchasePrice: purchasePrice.Round(2),
		SellingPrice:  in.SellingPrice.Round(2),
		MinStock:      in.MinStock.Round(4),
		IsActive:      in.IsActive,
		IsFeatured:    isFeatured,
		Image:         image,
	})
	if err != nil {
		if errors.Is(err, ErrNotFound) {
			return nil, ErrNotFound
		}
		return nil, err
	}
	return s.GetProduct(ctx, id)
}

func (s *Service) DeleteProduct(ctx context.Context, id int64) error {
	err := s.store.DeleteProduct(ctx, id)
	if errors.Is(err, ErrNotFound) {
		return ErrNotFound
	}
	return err
}

// productRowFields mirrors the sqlc product row structs. ListProductsRow and
// GetProductByIDRow are structurally identical, so either converts directly.
type productRowFields struct {
	ID            int64              `json:"id"`
	Code          string             `json:"code"`
	Name          string             `json:"name"`
	Description   pgtype.Text        `json:"description"`
	CategoryID    pgtype.Int8        `json:"category_id"`
	UnitID        pgtype.Int8        `json:"unit_id"`
	PurchasePrice decimal.Decimal    `json:"purchase_price"`
	SellingPrice  decimal.Decimal    `json:"selling_price"`
	MinStock      decimal.Decimal    `json:"min_stock"`
	IsActive      bool               `json:"is_active"`
	IsFeatured    bool               `json:"is_featured"`
	Image         pgtype.Text        `json:"image"`
	DeletedAt     pgtype.Timestamptz `json:"deleted_at"`
	CreatedAt     pgtype.Timestamptz `json:"created_at"`
	UpdatedAt     pgtype.Timestamptz `json:"updated_at"`
	BrandID       pgtype.Int8        `json:"brand_id"`
	ID_2          int64              `json:"id_2"`
	Name_2        string             `json:"name_2"`
	Description_2 string             `json:"description_2"`
	ID_3          int64              `json:"id_3"`
	Name_3        string             `json:"name_3"`
	Symbol        pgtype.Text        `json:"symbol"`
	Description_3 string             `json:"description_3"`
	BrandName     pgtype.Text        `json:"brand_name"`
	TotalStock    decimal.Decimal    `json:"total_stock"`
}

// productView converts a flattened product row into the nested API view.
// PurchasePrice is attached only for a caller holding inventory.cost.view;
// everyone else gets the field omitted (see ProductView.PurchasePrice).
func productView(ctx context.Context, r interface{}) (*ProductView, error) {
	var row productRowFields
	switch v := r.(type) {
	case db.ListProductsRow:
		row = productRowFields(v)
	case db.GetProductByIDRow:
		row = productRowFields(v)
	default:
		return nil, fmt.Errorf("catalog: unexpected product row type %T", r)
	}
	v := &ProductView{
		ID:          row.ID,
		Code:        row.Code,
		Name:        row.Name,
		Description: textPtr(row.Description),
		CategoryID:  row.CategoryID.Int64,
		UnitID:      row.UnitID.Int64,
		BrandID:     int8Ptr(row.BrandID),
		BrandName:   textPtr(row.BrandName),
		Category: &CategoryView{
			ID:          row.ID_2,
			Name:        row.Name_2,
			Description: row.Description_2,
		},
		Unit: &UnitView{
			ID:          row.ID_3,
			Name:        row.Name_3,
			Symbol:      row.Symbol.String,
			Description: row.Description_3,
		},
		SellingPrice: row.SellingPrice,
		MinStock:     row.MinStock,
		TotalStock:   row.TotalStock,
		IsActive:     row.IsActive,
		IsFeatured:   row.IsFeatured,
		Image:        textPtr(row.Image),
		ImageUrl:     textPtr(row.Image),
		DeletedAt:    tsPtr(row.DeletedAt),
		CreatedAt:    row.CreatedAt.Time,
		UpdatedAt:    row.UpdatedAt.Time,
	}
	if canSeeCost(ctx) {
		pp := row.PurchasePrice
		v.PurchasePrice = &pp
	}
	return v, nil
}

// --- brands ------------------------------------------------------------------

func (s *Service) ListBrands(ctx context.Context, onlyActive bool) ([]BrandView, error) {
	rows, err := s.store.ListBrands(ctx, onlyActive)
	if err != nil {
		return nil, err
	}
	out := make([]BrandView, 0, len(rows))
	for _, r := range rows {
		out = append(out, brandView(r))
	}
	return out, nil
}

func (s *Service) GetBrand(ctx context.Context, id int64) (*BrandView, error) {
	row, err := s.store.BrandByID(ctx, id)
	if err != nil {
		return nil, err
	}
	v := brandView(row)
	return &v, nil
}

func (s *Service) CreateBrand(ctx context.Context, in BrandInput) (*BrandView, error) {
	name := strings.TrimSpace(in.Name)
	if name == "" {
		return nil, errors.New("name is required")
	}
	exists, err := s.store.BrandNameExists(ctx, name, 0)
	if err != nil {
		return nil, err
	}
	if exists {
		return nil, ErrDuplicateName
	}
	row, err := s.store.CreateBrand(ctx, db.CreateBrandParams{
		Name:        name,
		Description: strings.TrimSpace(in.Description),
		IsActive:    in.IsActive,
	})
	if err != nil {
		return nil, err
	}
	v := brandView(row)
	return &v, nil
}

func (s *Service) UpdateBrand(ctx context.Context, id int64, in BrandInput) (*BrandView, error) {
	name := strings.TrimSpace(in.Name)
	if name == "" {
		return nil, errors.New("name is required")
	}
	exists, err := s.store.BrandNameExists(ctx, name, id)
	if err != nil {
		return nil, err
	}
	if exists {
		return nil, ErrDuplicateName
	}
	if err := s.store.UpdateBrand(ctx, db.UpdateBrandParams{
		ID:          id,
		Name:        name,
		Description: strings.TrimSpace(in.Description),
		IsActive:    in.IsActive,
	}); err != nil {
		return nil, err
	}
	return s.GetBrand(ctx, id)
}

func (s *Service) DeleteBrand(ctx context.Context, id int64) error {
	return s.store.DeleteBrand(ctx, id)
}

func brandView(b db.Brand) BrandView {
	return BrandView{ID: b.ID, Name: b.Name, Description: b.Description, IsActive: b.IsActive}
}

// resolveBrand validates an optional brand reference; nil clears the brand.
func (s *Service) resolveBrand(ctx context.Context, brandID *int64) (pgtype.Int8, error) {
	if brandID == nil || *brandID == 0 {
		return pgtype.Int8{}, nil
	}
	if _, err := s.store.BrandByID(ctx, *brandID); err != nil {
		if errors.Is(err, ErrNotFound) {
			return pgtype.Int8{}, ErrBrandRequired
		}
		return pgtype.Int8{}, err
	}
	return int8(*brandID), nil
}

// --- configured product units ------------------------------------------------

func (s *Service) ListProductUnits(ctx context.Context, productID int64, onlyActive bool) ([]ProductUnitView, error) {
	if _, err := s.store.ProductByID(ctx, productID); err != nil {
		return nil, err
	}
	rows, err := s.store.ListProductUnits(ctx, productID, onlyActive)
	if err != nil {
		return nil, err
	}
	out := make([]ProductUnitView, 0, len(rows))
	for _, r := range rows {
		uv, err := productUnitView(r)
		if err != nil {
			return nil, err
		}
		out = append(out, uv)
	}
	return out, nil
}

func (s *Service) CreateProductUnit(ctx context.Context, productID int64, in ProductUnitInput) (*ProductUnitView, error) {
	if _, err := s.store.ProductByID(ctx, productID); err != nil {
		return nil, err
	}
	if err := s.validateUnitConfig(ctx, in); err != nil {
		return nil, err
	}
	id, err := s.store.CreateProductUnit(ctx, db.CreateProductUnitParams{
		ProductID:        productID,
		UnitID:           in.UnitID,
		ConversionFactor: in.ConversionFactor.Round(4),
		SellingPrice:     nullDecimal(in.SellingPrice),
		MinPurchase:      in.MinPurchase.Round(4),
		IsBase:           in.IsBase,
		IsActive:         in.IsActive,
	})
	if err != nil {
		return nil, err
	}
	return s.getProductUnit(ctx, id)
}

func (s *Service) UpdateProductUnit(ctx context.Context, productID, id int64, in ProductUnitInput) (*ProductUnitView, error) {
	existing, err := s.store.ProductUnitByID(ctx, id)
	if err != nil {
		return nil, err
	}
	if existing.ProductID != productID {
		return nil, ErrUnitProductMismatch
	}
	if err := s.validateUnitConfig(ctx, in); err != nil {
		return nil, err
	}
	if err := s.store.UpdateProductUnit(ctx, db.UpdateProductUnitParams{
		ID:               id,
		UnitID:           in.UnitID,
		ConversionFactor: in.ConversionFactor.Round(4),
		SellingPrice:     nullDecimal(in.SellingPrice),
		MinPurchase:      in.MinPurchase.Round(4),
		IsBase:           in.IsBase,
		IsActive:         in.IsActive,
	}); err != nil {
		return nil, err
	}
	return s.getProductUnit(ctx, id)
}

func (s *Service) DeleteProductUnit(ctx context.Context, productID, id int64) error {
	existing, err := s.store.ProductUnitByID(ctx, id)
	if err != nil {
		return err
	}
	if existing.ProductID != productID {
		return ErrUnitProductMismatch
	}
	return s.store.DeleteProductUnit(ctx, id)
}

func (s *Service) getProductUnit(ctx context.Context, id int64) (*ProductUnitView, error) {
	row, err := s.store.ProductUnitByID(ctx, id)
	if err != nil {
		return nil, err
	}
	v, err := productUnitView(row)
	if err != nil {
		return nil, err
	}
	return &v, nil
}

// validateUnitConfig enforces configuration-only conversion: a factor must be
// positive, the base unit is always factor 1, and minimum purchase is positive.
func (s *Service) validateUnitConfig(ctx context.Context, in ProductUnitInput) error {
	if in.UnitID == 0 {
		return ErrUnitRequired
	}
	if _, err := s.store.UnitByID(ctx, in.UnitID); err != nil {
		if errors.Is(err, ErrNotFound) {
			return ErrUnitRequired
		}
		return err
	}
	if in.ConversionFactor.LessThanOrEqual(decimal.Zero) {
		return ErrInvalidConversion
	}
	if in.IsBase && !in.ConversionFactor.Equal(decimal.NewFromInt(1)) {
		return ErrBaseUnitConversion
	}
	if in.MinPurchase.LessThanOrEqual(decimal.Zero) {
		return ErrInvalidMinPurchase
	}
	if in.SellingPrice != nil && in.SellingPrice.IsNegative() {
		return ErrInvalidUnitPrice
	}
	return nil
}

type productUnitRowFields struct {
	ID               int64
	ProductID        int64
	UnitID           int64
	ConversionFactor decimal.Decimal
	SellingPrice     decimal.NullDecimal
	MinPurchase      decimal.Decimal
	IsBase           bool
	IsActive         bool
	CreatedAt        pgtype.Timestamptz
	UpdatedAt        pgtype.Timestamptz
	UnitName         string
	UnitSymbol       pgtype.Text
}

func productUnitView(r interface{}) (ProductUnitView, error) {
	var row productUnitRowFields
	switch v := r.(type) {
	case db.ListProductUnitsRow:
		row = productUnitRowFields(v)
	case db.GetProductUnitByIDRow:
		row = productUnitRowFields(v)
	default:
		return ProductUnitView{}, fmt.Errorf("catalog: unexpected product unit row type %T", r)
	}
	return ProductUnitView{
		ID:               row.ID,
		ProductID:        row.ProductID,
		UnitID:           row.UnitID,
		UnitName:         row.UnitName,
		UnitSymbol:       row.UnitSymbol.String,
		ConversionFactor: row.ConversionFactor,
		SellingPrice:     decimalPtr(row.SellingPrice),
		MinPurchase:      row.MinPurchase,
		IsBase:           row.IsBase,
		IsActive:         row.IsActive,
	}, nil
}

// --- product barcodes --------------------------------------------------------

func (s *Service) ListProductBarcodes(ctx context.Context, productID int64) ([]ProductBarcodeView, error) {
	if _, err := s.store.ProductByID(ctx, productID); err != nil {
		return nil, err
	}
	rows, err := s.store.ListProductBarcodes(ctx, productID)
	if err != nil {
		return nil, err
	}
	out := make([]ProductBarcodeView, 0, len(rows))
	for _, r := range rows {
		out = append(out, barcodeView(r))
	}
	return out, nil
}

func (s *Service) CreateProductBarcode(ctx context.Context, productID int64, in ProductBarcodeInput) (*ProductBarcodeView, error) {
	if _, err := s.store.ProductByID(ctx, productID); err != nil {
		return nil, err
	}
	barcode := strings.TrimSpace(in.Barcode)
	if barcode == "" {
		return nil, ErrBarcodeRequired
	}
	var unitID pgtype.Int8
	if in.ProductUnitID != nil && *in.ProductUnitID != 0 {
		pu, err := s.store.ProductUnitByID(ctx, *in.ProductUnitID)
		if err != nil {
			return nil, err
		}
		if pu.ProductID != productID {
			return nil, ErrUnitProductMismatch
		}
		unitID = int8(*in.ProductUnitID)
	}
	row, err := s.store.CreateProductBarcode(ctx, db.CreateProductBarcodeParams{
		ProductID:     productID,
		ProductUnitID: unitID,
		Barcode:       barcode,
		IsPrimary:     in.IsPrimary,
	})
	if err != nil {
		return nil, err
	}
	v := barcodeView(row)
	return &v, nil
}

func (s *Service) DeleteProductBarcode(ctx context.Context, productID, id int64) error {
	existing, err := s.store.ProductBarcodeByID(ctx, id)
	if err != nil {
		return err
	}
	if existing.ProductID != productID {
		return ErrUnitProductMismatch
	}
	return s.store.DeleteProductBarcode(ctx, id)
}

func (s *Service) LookupBarcode(ctx context.Context, barcode string) (*BarcodeLookupView, error) {
	barcode = strings.TrimSpace(barcode)
	if barcode == "" {
		return nil, ErrBarcodeRequired
	}
	row, err := s.store.LookupProductByBarcode(ctx, barcode)
	if err != nil {
		return nil, err
	}
	out := &BarcodeLookupView{
		Barcode:      row.Barcode,
		ProductID:    row.ProductID,
		ProductCode:  row.ProductCode,
		ProductName:  row.ProductName,
		IsActive:     row.IsActive,
		SellingPrice: row.SellingPrice,
	}
	if row.ProductUnitID.Valid {
		out.ProductUnit = &ProductUnitView{
			ID:               row.ProductUnitID.Int64,
			ProductID:        row.ProductID,
			UnitID:           row.UnitID.Int64,
			UnitName:         row.UnitName.String,
			UnitSymbol:       row.UnitSymbol.String,
			ConversionFactor: row.ConversionFactor.Decimal,
			SellingPrice:     decimalPtr(row.UnitSellingPrice),
			MinPurchase:      row.MinPurchase.Decimal,
			IsBase:           row.UnitIsBase.Bool,
			IsActive:         row.UnitIsActive.Bool,
		}
	}
	return out, nil
}

func barcodeView(b db.ProductBarcode) ProductBarcodeView {
	return ProductBarcodeView{
		ID:            b.ID,
		ProductID:     b.ProductID,
		ProductUnitID: int8Ptr(b.ProductUnitID),
		Barcode:       b.Barcode,
		IsPrimary:     b.IsPrimary,
	}
}

// --- pgtype helpers ----------------------------------------------------------

// pgtext returns an always-valid pgtype.Text ("" keeps JSON parity).
func pgtext(s string) pgtype.Text {
	return pgtype.Text{String: s, Valid: true}
}

// pgtextPtr maps a *string to a nullable pgtype.Text (nil → SQL NULL).
func pgtextPtr(s *string) pgtype.Text {
	if s == nil {
		return pgtype.Text{}
	}
	return pgtype.Text{String: *s, Valid: true}
}

// int8 returns a valid pgtype.Int8.
func int8(i int64) pgtype.Int8 {
	return pgtype.Int8{Int64: i, Valid: true}
}

// textPtr maps a nullable pgtype.Text to a *string.
func textPtr(t pgtype.Text) *string {
	if !t.Valid {
		return nil
	}
	v := t.String
	return &v
}

// int8Ptr maps a nullable pgtype.Int8 to a *int64.
func int8Ptr(i pgtype.Int8) *int64 {
	if !i.Valid {
		return nil
	}
	v := i.Int64
	return &v
}

// nullDecimal maps an optional price to a nullable numeric (nil → SQL NULL).
func nullDecimal(d *decimal.Decimal) decimal.NullDecimal {
	if d == nil {
		return decimal.NullDecimal{}
	}
	return decimal.NullDecimal{Decimal: d.Round(2), Valid: true}
}

// decimalPtr maps a nullable numeric to an optional price.
func decimalPtr(d decimal.NullDecimal) *decimal.Decimal {
	if !d.Valid {
		return nil
	}
	v := d.Decimal
	return &v
}

// tsPtr maps a nullable pgtype.Timestamptz to a *time.Time.
func tsPtr(t pgtype.Timestamptz) *time.Time {
	if !t.Valid {
		return nil
	}
	v := t.Time
	return &v
}

// nextProductCode builds `<CAT3>-YYYYMMDD-<seq3>`.
// CAT3 = first 3 uppercase letters of the category name; default PRD.
// seq uses count incl. trashed (Laravel parity) + atomic counter. We still use
// the sequence table so concurrent creates never collide; the count() path is
// only a fallback when no sequence backend is wired (tests).
func (s *Service) nextProductCode(ctx context.Context, categoryName string) (string, error) {
	prefix := categoryPrefix(categoryName)
	date := time.Now().In(s.loc)
	seqNum, err := s.seq.Sequence(ctx, "prd:"+prefix, date, nil)
	if err != nil {
		return "", fmt.Errorf("product code sequence: %w", err)
	}
	return fmt.Sprintf("%s-%s-%03d", prefix, date.Format("20060102"), seqNum), nil
}

func categoryPrefix(name string) string {
	var letters []rune
	for _, r := range strings.ToUpper(name) {
		if unicode.IsLetter(r) {
			letters = append(letters, r)
		}
	}
	if len(letters) >= 3 {
		return string(letters[:3])
	}
	if len(letters) > 0 {
		return string(letters)
	}
	return "PRD"
}