// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.27.0
// source: catalog.sql

package db

import (
	"context"

	"github.com/jackc/pgx/v5/pgtype"
	"github.com/shopspring/decimal"
)

const clearProductBaseUnit = `-- name: ClearProductBaseUnit :exec
UPDATE product_units SET is_base = false, updated_at = now()
WHERE product_id = $1 AND is_base = true AND id <> $2
`

type ClearProductBaseUnitParams struct {
	ProductID int64 `json:"product_id"`
	ID        int64 `json:"id"`
}

func (q *Queries) ClearProductBaseUnit(ctx context.Context, arg ClearProductBaseUnitParams) error {
	_, err := q.db.Exec(ctx, clearProductBaseUnit, arg.ProductID, arg.ID)
	return err
}

const clearProductPrimaryBarcode = `-- name: ClearProductPrimaryBarcode :exec
UPDATE product_barcodes SET is_primary = false, updated_at = now()
WHERE product_id = $1 AND is_primary = true AND id <> $2
`

type ClearProductPrimaryBarcodeParams struct {
	ProductID int64 `json:"product_id"`
	ID        int64 `json:"id"`
}

func (q *Queries) ClearProductPrimaryBarcode(ctx context.Context, arg ClearProductPrimaryBarcodeParams) error {
	_, err := q.db.Exec(ctx, clearProductPrimaryBarcode, arg.ProductID, arg.ID)
	return err
}

const countBrandsByName = `-- name: CountBrandsByName :one
SELECT count(*) FROM brands WHERE lower(name) = lower($1::text) AND id <> $2
`

type CountBrandsByNameParams struct {
	Name     string `json:"name"`
	ExceptID int64  `json:"except_id"`
}

func (q *Queries) CountBrandsByName(ctx context.Context, arg CountBrandsByNameParams) (int64, error) {
	row := q.db.QueryRow(ctx, countBrandsByName, arg.Name, arg.ExceptID)
	var count int64
	err := row.Scan(&count)
	return count, err
}

const countCategories = `-- name: CountCategories :one
SELECT count(*) FROM categories
WHERE ($1::boolean IS NULL OR ($1::boolean = true AND is_active = true))
  AND ($2::text IS NULL OR (name ILIKE '%' || $2 || '%' OR COALESCE(description, '') ILIKE '%' || $2 || '%'))
`

type CountCategoriesParams struct {
	OnlyActive pgtype.Bool `json:"only_active"`
	Search     pgtype.Text `json:"search"`
}

func (q *Queries) CountCategories(ctx context.Context, arg CountCategoriesParams) (int64, error) {
	row := q.db.QueryRow(ctx, countCategories, arg.OnlyActive, arg.Search)
	var count int64
	err := row.Scan(&count)
	return count, err
}

const countCategoriesByName = `-- name: CountCategoriesByName :one
SELECT count(*) FROM categories WHERE name = $1 AND id <> $2
`

type CountCategoriesByNameParams struct {
	Name string `json:"name"`
	ID   int64  `json:"id"`
}

func (q *Queries) CountCategoriesByName(ctx context.Context, arg CountCategoriesByNameParams) (int64, error) {
	row := q.db.QueryRow(ctx, countCategoriesByName, arg.Name, arg.ID)
	var count int64
	err := row.Scan(&count)
	return count, err
}

const countProducts = `-- name: CountProducts :one
SELECT count(*) FROM products p
WHERE p.deleted_at IS NULL
  AND ($1::text IS NULL OR (p.code ILIKE '%' || $1 || '%' OR p.name ILIKE '%' || $1 || '%'))
  AND ($2::bigint IS NULL OR p.category_id = $2)
  AND ($3::bigint IS NULL OR p.brand_id = $3)
  AND ($4::boolean IS NULL OR p.is_active = $4)
  AND ($5::boolean IS NULL OR $5 = false OR
       (SELECT COALESCE(SUM(GREATEST(0, st2.quantity - st2.reserved_quantity)), 0) FROM stocks st2 WHERE st2.product_id = p.id) > 0)
`

type CountProductsParams struct {
	Search     pgtype.Text `json:"search"`
	CategoryID pgtype.Int8 `json:"category_id"`
	BrandID    pgtype.Int8 `json:"brand_id"`
	IsActive   pgtype.Bool `json:"is_active"`
	InStock    pgtype.Bool `json:"in_stock"`
}

func (q *Queries) CountProducts(ctx context.Context, arg CountProductsParams) (int64, error) {
	row := q.db.QueryRow(ctx, countProducts,
		arg.Search,
		arg.CategoryID,
		arg.BrandID,
		arg.IsActive,
		arg.InStock,
	)
	var count int64
	err := row.Scan(&count)
	return count, err
}

const countProductsByBrand = `-- name: CountProductsByBrand :one
SELECT count(*) FROM products WHERE brand_id = $1 AND deleted_at IS NULL
`

func (q *Queries) CountProductsByBrand(ctx context.Context, brandID pgtype.Int8) (int64, error) {
	row := q.db.QueryRow(ctx, countProductsByBrand, brandID)
	var count int64
	err := row.Scan(&count)
	return count, err
}

const countProductsByCategory = `-- name: CountProductsByCategory :one
SELECT count(*) FROM products WHERE category_id = $1
`

func (q *Queries) CountProductsByCategory(ctx context.Context, categoryID pgtype.Int8) (int64, error) {
	row := q.db.QueryRow(ctx, countProductsByCategory, categoryID)
	var count int64
	err := row.Scan(&count)
	return count, err
}

const countUnits = `-- name: CountUnits :one
SELECT count(*) FROM units
WHERE ($1::text IS NULL OR (name ILIKE '%' || $1 || '%' OR symbol ILIKE '%' || $1 || '%'))
`

func (q *Queries) CountUnits(ctx context.Context, search pgtype.Text) (int64, error) {
	row := q.db.QueryRow(ctx, countUnits, search)
	var count int64
	err := row.Scan(&count)
	return count, err
}

const countUnitsByName = `-- name: CountUnitsByName :one
SELECT count(*) FROM units WHERE name = $1 AND id <> $2
`

type CountUnitsByNameParams struct {
	Name string `json:"name"`
	ID   int64  `json:"id"`
}

func (q *Queries) CountUnitsByName(ctx context.Context, arg CountUnitsByNameParams) (int64, error) {
	row := q.db.QueryRow(ctx, countUnitsByName, arg.Name, arg.ID)
	var count int64
	err := row.Scan(&count)
	return count, err
}

const createBrand = `-- name: CreateBrand :one
INSERT INTO brands (name, description, is_active)
VALUES ($1, $2, $3)
RETURNING id, name, description, is_active, created_at, updated_at
`

type CreateBrandParams struct {
	Name        string `json:"name"`
	Description string `json:"description"`
	IsActive    bool   `json:"is_active"`
}

func (q *Queries) CreateBrand(ctx context.Context, arg CreateBrandParams) (Brand, error) {
	row := q.db.QueryRow(ctx, createBrand, arg.Name, arg.Description, arg.IsActive)
	var i Brand
	err := row.Scan(
		&i.ID,
		&i.Name,
		&i.Description,
		&i.IsActive,
		&i.CreatedAt,
		&i.UpdatedAt,
	)
	return i, err
}

const createCategory = `-- name: CreateCategory :one
INSERT INTO categories (name, description, is_active) VALUES ($1, $2, $3) RETURNING id
`

type CreateCategoryParams struct {
	Name        string      `json:"name"`
	Description pgtype.Text `json:"description"`
	IsActive    bool        `json:"is_active"`
}

func (q *Queries) CreateCategory(ctx context.Context, arg CreateCategoryParams) (int64, error) {
	row := q.db.QueryRow(ctx, createCategory, arg.Name, arg.Description, arg.IsActive)
	var id int64
	err := row.Scan(&id)
	return id, err
}

const createProduct = `-- name: CreateProduct :one
INSERT INTO products (code, name, description, category_id, unit_id,
       purchase_price, selling_price, min_stock, is_active, is_featured, image, brand_id)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
RETURNING id, created_at, updated_at
`

type CreateProductParams struct {
	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"`
	BrandID       pgtype.Int8     `json:"brand_id"`
}

type CreateProductRow struct {
	ID        int64              `json:"id"`
	CreatedAt pgtype.Timestamptz `json:"created_at"`
	UpdatedAt pgtype.Timestamptz `json:"updated_at"`
}

func (q *Queries) CreateProduct(ctx context.Context, arg CreateProductParams) (CreateProductRow, error) {
	row := q.db.QueryRow(ctx, createProduct,
		arg.Code,
		arg.Name,
		arg.Description,
		arg.CategoryID,
		arg.UnitID,
		arg.PurchasePrice,
		arg.SellingPrice,
		arg.MinStock,
		arg.IsActive,
		arg.IsFeatured,
		arg.Image,
		arg.BrandID,
	)
	var i CreateProductRow
	err := row.Scan(&i.ID, &i.CreatedAt, &i.UpdatedAt)
	return i, err
}

const createProductBarcode = `-- name: CreateProductBarcode :one
INSERT INTO product_barcodes (product_id, product_unit_id, barcode, is_primary)
VALUES ($1, $2, $3, $4)
RETURNING id, product_id, product_unit_id, barcode, is_primary, created_at, updated_at
`

type CreateProductBarcodeParams struct {
	ProductID     int64       `json:"product_id"`
	ProductUnitID pgtype.Int8 `json:"product_unit_id"`
	Barcode       string      `json:"barcode"`
	IsPrimary     bool        `json:"is_primary"`
}

func (q *Queries) CreateProductBarcode(ctx context.Context, arg CreateProductBarcodeParams) (ProductBarcode, error) {
	row := q.db.QueryRow(ctx, createProductBarcode,
		arg.ProductID,
		arg.ProductUnitID,
		arg.Barcode,
		arg.IsPrimary,
	)
	var i ProductBarcode
	err := row.Scan(
		&i.ID,
		&i.ProductID,
		&i.ProductUnitID,
		&i.Barcode,
		&i.IsPrimary,
		&i.CreatedAt,
		&i.UpdatedAt,
	)
	return i, err
}

const createProductUnit = `-- name: CreateProductUnit :one
INSERT INTO product_units (product_id, unit_id, conversion_factor, selling_price,
       min_purchase, is_base, is_active)
VALUES ($1, $2, $3, $4, $5, $6, $7)
RETURNING id
`

type CreateProductUnitParams struct {
	ProductID        int64               `json:"product_id"`
	UnitID           int64               `json:"unit_id"`
	ConversionFactor decimal.Decimal     `json:"conversion_factor"`
	SellingPrice     decimal.NullDecimal `json:"selling_price"`
	MinPurchase      decimal.Decimal     `json:"min_purchase"`
	IsBase           bool                `json:"is_base"`
	IsActive         bool                `json:"is_active"`
}

func (q *Queries) CreateProductUnit(ctx context.Context, arg CreateProductUnitParams) (int64, error) {
	row := q.db.QueryRow(ctx, createProductUnit,
		arg.ProductID,
		arg.UnitID,
		arg.ConversionFactor,
		arg.SellingPrice,
		arg.MinPurchase,
		arg.IsBase,
		arg.IsActive,
	)
	var id int64
	err := row.Scan(&id)
	return id, err
}

const createUnit = `-- name: CreateUnit :one
INSERT INTO units (name, symbol, description) VALUES ($1, $2, $3) RETURNING id
`

type CreateUnitParams struct {
	Name        string      `json:"name"`
	Symbol      pgtype.Text `json:"symbol"`
	Description pgtype.Text `json:"description"`
}

func (q *Queries) CreateUnit(ctx context.Context, arg CreateUnitParams) (int64, error) {
	row := q.db.QueryRow(ctx, createUnit, arg.Name, arg.Symbol, arg.Description)
	var id int64
	err := row.Scan(&id)
	return id, err
}

const deleteBrand = `-- name: DeleteBrand :execrows
DELETE FROM brands WHERE id = $1
`

func (q *Queries) DeleteBrand(ctx context.Context, id int64) (int64, error) {
	result, err := q.db.Exec(ctx, deleteBrand, id)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const deleteCategory = `-- name: DeleteCategory :execrows
DELETE FROM categories WHERE id = $1
`

func (q *Queries) DeleteCategory(ctx context.Context, id int64) (int64, error) {
	result, err := q.db.Exec(ctx, deleteCategory, id)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const deleteProductBarcode = `-- name: DeleteProductBarcode :execrows
DELETE FROM product_barcodes WHERE id = $1
`

func (q *Queries) DeleteProductBarcode(ctx context.Context, id int64) (int64, error) {
	result, err := q.db.Exec(ctx, deleteProductBarcode, id)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const deleteProductUnit = `-- name: DeleteProductUnit :execrows
DELETE FROM product_units WHERE id = $1
`

func (q *Queries) DeleteProductUnit(ctx context.Context, id int64) (int64, error) {
	result, err := q.db.Exec(ctx, deleteProductUnit, id)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const deleteUnit = `-- name: DeleteUnit :execrows
DELETE FROM units WHERE id = $1
`

func (q *Queries) DeleteUnit(ctx context.Context, id int64) (int64, error) {
	result, err := q.db.Exec(ctx, deleteUnit, id)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const getBrandByID = `-- name: GetBrandByID :one

SELECT id, name, description, is_active, created_at, updated_at
FROM brands WHERE id = $1
`

// business-rules.md §12: brands
func (q *Queries) GetBrandByID(ctx context.Context, id int64) (Brand, error) {
	row := q.db.QueryRow(ctx, getBrandByID, id)
	var i Brand
	err := row.Scan(
		&i.ID,
		&i.Name,
		&i.Description,
		&i.IsActive,
		&i.CreatedAt,
		&i.UpdatedAt,
	)
	return i, err
}

const getCategoryByID = `-- name: GetCategoryByID :one
SELECT id, name, COALESCE(description, '') AS description, is_active FROM categories WHERE id = $1
`

type GetCategoryByIDRow struct {
	ID          int64  `json:"id"`
	Name        string `json:"name"`
	Description string `json:"description"`
	IsActive    bool   `json:"is_active"`
}

func (q *Queries) GetCategoryByID(ctx context.Context, id int64) (GetCategoryByIDRow, error) {
	row := q.db.QueryRow(ctx, getCategoryByID, id)
	var i GetCategoryByIDRow
	err := row.Scan(
		&i.ID,
		&i.Name,
		&i.Description,
		&i.IsActive,
	)
	return i, err
}

const getProductBarcodeByID = `-- name: GetProductBarcodeByID :one
SELECT id, product_id, product_unit_id, barcode, is_primary, created_at, updated_at
FROM product_barcodes WHERE id = $1
`

func (q *Queries) GetProductBarcodeByID(ctx context.Context, id int64) (ProductBarcode, error) {
	row := q.db.QueryRow(ctx, getProductBarcodeByID, id)
	var i ProductBarcode
	err := row.Scan(
		&i.ID,
		&i.ProductID,
		&i.ProductUnitID,
		&i.Barcode,
		&i.IsPrimary,
		&i.CreatedAt,
		&i.UpdatedAt,
	)
	return i, err
}

const getProductByCode = `-- name: GetProductByCode :one
SELECT p.id, p.code, p.name, p.description, p.category_id, p.unit_id,
       p.purchase_price, p.selling_price, p.min_stock, p.is_active, p.is_featured, p.image, p.deleted_at,
       p.created_at, p.updated_at, p.brand_id,
       c.id, c.name, COALESCE(c.description, '') AS description,
       u.id, u.name, u.symbol, COALESCE(u.description, '') AS description,
       b.name AS brand_name,
       (SELECT COALESCE(SUM(GREATEST(0, st.quantity - st.reserved_quantity)), 0)::numeric FROM stocks st WHERE st.product_id = p.id) AS total_stock
FROM products p
JOIN categories c ON c.id = p.category_id
JOIN units u ON u.id = p.unit_id
LEFT JOIN brands b ON b.id = p.brand_id
WHERE p.code = $1
`

type GetProductByCodeRow 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"`
}

func (q *Queries) GetProductByCode(ctx context.Context, code string) (GetProductByCodeRow, error) {
	row := q.db.QueryRow(ctx, getProductByCode, code)
	var i GetProductByCodeRow
	err := row.Scan(
		&i.ID,
		&i.Code,
		&i.Name,
		&i.Description,
		&i.CategoryID,
		&i.UnitID,
		&i.PurchasePrice,
		&i.SellingPrice,
		&i.MinStock,
		&i.IsActive,
		&i.IsFeatured,
		&i.Image,
		&i.DeletedAt,
		&i.CreatedAt,
		&i.UpdatedAt,
		&i.BrandID,
		&i.ID_2,
		&i.Name_2,
		&i.Description_2,
		&i.ID_3,
		&i.Name_3,
		&i.Symbol,
		&i.Description_3,
		&i.BrandName,
		&i.TotalStock,
	)
	return i, err
}

const getProductByID = `-- name: GetProductByID :one
SELECT p.id, p.code, p.name, p.description, p.category_id, p.unit_id,
       p.purchase_price, p.selling_price, p.min_stock, p.is_active, p.is_featured, p.image, p.deleted_at,
       p.created_at, p.updated_at, p.brand_id,
       c.id, c.name, COALESCE(c.description, '') AS description,
       u.id, u.name, u.symbol, COALESCE(u.description, '') AS description,
       b.name AS brand_name,
       (SELECT COALESCE(SUM(GREATEST(0, st.quantity - st.reserved_quantity)), 0)::numeric FROM stocks st WHERE st.product_id = p.id) AS total_stock
FROM products p
JOIN categories c ON c.id = p.category_id
JOIN units u ON u.id = p.unit_id
LEFT JOIN brands b ON b.id = p.brand_id
WHERE p.id = $1 AND p.deleted_at IS NULL
`

type GetProductByIDRow 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"`
}

func (q *Queries) GetProductByID(ctx context.Context, id int64) (GetProductByIDRow, error) {
	row := q.db.QueryRow(ctx, getProductByID, id)
	var i GetProductByIDRow
	err := row.Scan(
		&i.ID,
		&i.Code,
		&i.Name,
		&i.Description,
		&i.CategoryID,
		&i.UnitID,
		&i.PurchasePrice,
		&i.SellingPrice,
		&i.MinStock,
		&i.IsActive,
		&i.IsFeatured,
		&i.Image,
		&i.DeletedAt,
		&i.CreatedAt,
		&i.UpdatedAt,
		&i.BrandID,
		&i.ID_2,
		&i.Name_2,
		&i.Description_2,
		&i.ID_3,
		&i.Name_3,
		&i.Symbol,
		&i.Description_3,
		&i.BrandName,
		&i.TotalStock,
	)
	return i, err
}

const getProductUnitByID = `-- name: GetProductUnitByID :one
SELECT pu.id, pu.product_id, pu.unit_id, pu.conversion_factor, pu.selling_price,
       pu.min_purchase, pu.is_base, pu.is_active, pu.created_at, pu.updated_at,
       u.name AS unit_name, u.symbol AS unit_symbol
FROM product_units pu
JOIN units u ON u.id = pu.unit_id
WHERE pu.id = $1
`

type GetProductUnitByIDRow struct {
	ID               int64               `json:"id"`
	ProductID        int64               `json:"product_id"`
	UnitID           int64               `json:"unit_id"`
	ConversionFactor decimal.Decimal     `json:"conversion_factor"`
	SellingPrice     decimal.NullDecimal `json:"selling_price"`
	MinPurchase      decimal.Decimal     `json:"min_purchase"`
	IsBase           bool                `json:"is_base"`
	IsActive         bool                `json:"is_active"`
	CreatedAt        pgtype.Timestamptz  `json:"created_at"`
	UpdatedAt        pgtype.Timestamptz  `json:"updated_at"`
	UnitName         string              `json:"unit_name"`
	UnitSymbol       pgtype.Text         `json:"unit_symbol"`
}

func (q *Queries) GetProductUnitByID(ctx context.Context, id int64) (GetProductUnitByIDRow, error) {
	row := q.db.QueryRow(ctx, getProductUnitByID, id)
	var i GetProductUnitByIDRow
	err := row.Scan(
		&i.ID,
		&i.ProductID,
		&i.UnitID,
		&i.ConversionFactor,
		&i.SellingPrice,
		&i.MinPurchase,
		&i.IsBase,
		&i.IsActive,
		&i.CreatedAt,
		&i.UpdatedAt,
		&i.UnitName,
		&i.UnitSymbol,
	)
	return i, err
}

const getUnitByID = `-- name: GetUnitByID :one
SELECT id, name, symbol, COALESCE(description, '') AS description FROM units WHERE id = $1
`

type GetUnitByIDRow struct {
	ID          int64       `json:"id"`
	Name        string      `json:"name"`
	Symbol      pgtype.Text `json:"symbol"`
	Description string      `json:"description"`
}

func (q *Queries) GetUnitByID(ctx context.Context, id int64) (GetUnitByIDRow, error) {
	row := q.db.QueryRow(ctx, getUnitByID, id)
	var i GetUnitByIDRow
	err := row.Scan(
		&i.ID,
		&i.Name,
		&i.Symbol,
		&i.Description,
	)
	return i, err
}

const listBrands = `-- name: ListBrands :many
SELECT id, name, description, is_active, created_at, updated_at
FROM brands
WHERE $1::boolean IS NULL
   OR ($1::boolean = true AND is_active = true)
ORDER BY name
`

func (q *Queries) ListBrands(ctx context.Context, onlyActive pgtype.Bool) ([]Brand, error) {
	rows, err := q.db.Query(ctx, listBrands, onlyActive)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []Brand
	for rows.Next() {
		var i Brand
		if err := rows.Scan(
			&i.ID,
			&i.Name,
			&i.Description,
			&i.IsActive,
			&i.CreatedAt,
			&i.UpdatedAt,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const listCategories = `-- name: ListCategories :many
SELECT id, name, COALESCE(description, '') AS description, is_active FROM categories
WHERE ($3::boolean IS NULL OR ($3::boolean = true AND is_active = true))
  AND ($4::text IS NULL OR (name ILIKE '%' || $4 || '%' OR COALESCE(description, '') ILIKE '%' || $4 || '%'))
ORDER BY name
LIMIT $1 OFFSET $2
`

type ListCategoriesParams struct {
	Limit      int32       `json:"limit"`
	Offset     int32       `json:"offset"`
	OnlyActive pgtype.Bool `json:"only_active"`
	Search     pgtype.Text `json:"search"`
}

type ListCategoriesRow struct {
	ID          int64  `json:"id"`
	Name        string `json:"name"`
	Description string `json:"description"`
	IsActive    bool   `json:"is_active"`
}

func (q *Queries) ListCategories(ctx context.Context, arg ListCategoriesParams) ([]ListCategoriesRow, error) {
	rows, err := q.db.Query(ctx, listCategories,
		arg.Limit,
		arg.Offset,
		arg.OnlyActive,
		arg.Search,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListCategoriesRow
	for rows.Next() {
		var i ListCategoriesRow
		if err := rows.Scan(
			&i.ID,
			&i.Name,
			&i.Description,
			&i.IsActive,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const listProductBarcodes = `-- name: ListProductBarcodes :many

SELECT id, product_id, product_unit_id, barcode, is_primary, created_at, updated_at
FROM product_barcodes
WHERE product_id = $1
ORDER BY is_primary DESC, barcode
`

// business-rules.md §12: product barcodes
func (q *Queries) ListProductBarcodes(ctx context.Context, productID int64) ([]ProductBarcode, error) {
	rows, err := q.db.Query(ctx, listProductBarcodes, productID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ProductBarcode
	for rows.Next() {
		var i ProductBarcode
		if err := rows.Scan(
			&i.ID,
			&i.ProductID,
			&i.ProductUnitID,
			&i.Barcode,
			&i.IsPrimary,
			&i.CreatedAt,
			&i.UpdatedAt,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const listProductUnits = `-- name: ListProductUnits :many

SELECT pu.id, pu.product_id, pu.unit_id, pu.conversion_factor, pu.selling_price,
       pu.min_purchase, pu.is_base, pu.is_active, pu.created_at, pu.updated_at,
       u.name AS unit_name, u.symbol AS unit_symbol
FROM product_units pu
JOIN units u ON u.id = pu.unit_id
WHERE pu.product_id = $1
  AND ($2::boolean IS NULL
       OR ($2::boolean = true AND pu.is_active = true))
ORDER BY pu.is_base DESC, pu.conversion_factor, u.name
`

type ListProductUnitsParams struct {
	ProductID  int64       `json:"product_id"`
	OnlyActive pgtype.Bool `json:"only_active"`
}

type ListProductUnitsRow struct {
	ID               int64               `json:"id"`
	ProductID        int64               `json:"product_id"`
	UnitID           int64               `json:"unit_id"`
	ConversionFactor decimal.Decimal     `json:"conversion_factor"`
	SellingPrice     decimal.NullDecimal `json:"selling_price"`
	MinPurchase      decimal.Decimal     `json:"min_purchase"`
	IsBase           bool                `json:"is_base"`
	IsActive         bool                `json:"is_active"`
	CreatedAt        pgtype.Timestamptz  `json:"created_at"`
	UpdatedAt        pgtype.Timestamptz  `json:"updated_at"`
	UnitName         string              `json:"unit_name"`
	UnitSymbol       pgtype.Text         `json:"unit_symbol"`
}

// business-rules.md §12: configured product units
func (q *Queries) ListProductUnits(ctx context.Context, arg ListProductUnitsParams) ([]ListProductUnitsRow, error) {
	rows, err := q.db.Query(ctx, listProductUnits, arg.ProductID, arg.OnlyActive)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListProductUnitsRow
	for rows.Next() {
		var i ListProductUnitsRow
		if err := rows.Scan(
			&i.ID,
			&i.ProductID,
			&i.UnitID,
			&i.ConversionFactor,
			&i.SellingPrice,
			&i.MinPurchase,
			&i.IsBase,
			&i.IsActive,
			&i.CreatedAt,
			&i.UpdatedAt,
			&i.UnitName,
			&i.UnitSymbol,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const listProducts = `-- name: ListProducts :many
SELECT p.id, p.code, p.name, p.description, p.category_id, p.unit_id,
       p.purchase_price, p.selling_price, p.min_stock, p.is_active, p.is_featured, p.image, p.deleted_at,
       p.created_at, p.updated_at, p.brand_id,
       c.id, c.name, COALESCE(c.description, '') AS description,
       u.id, u.name, u.symbol, COALESCE(u.description, '') AS description,
       b.name AS brand_name,
       (SELECT COALESCE(SUM(GREATEST(0, st.quantity - st.reserved_quantity)), 0)::numeric FROM stocks st WHERE st.product_id = p.id) AS total_stock
FROM products p
JOIN categories c ON c.id = p.category_id
JOIN units u ON u.id = p.unit_id
LEFT JOIN brands b ON b.id = p.brand_id
WHERE p.deleted_at IS NULL
  AND ($3::text IS NULL OR (p.code ILIKE '%' || $3 || '%' OR p.name ILIKE '%' || $3 || '%'))
  AND ($4::bigint IS NULL OR p.category_id = $4)
  AND ($5::bigint IS NULL OR p.brand_id = $5)
  AND ($6::boolean IS NULL OR p.is_active = $6)
  AND ($7::boolean IS NULL OR $7 = false OR
       (SELECT COALESCE(SUM(GREATEST(0, st2.quantity - st2.reserved_quantity)), 0) FROM stocks st2 WHERE st2.product_id = p.id) > 0)
ORDER BY
  -- Each pair is dir-gated: the bare column key must only match its own
  -- direction, otherwise the un-gated ASC clause dominates the DESC clause
  -- below it and every "desc" click silently returned ascending order
  -- (flow audit 3 Nov: products name-desc returned A→Z).
  CASE WHEN $8::text = 'name' AND $9::text <> 'desc' THEN p.name END ASC,
  CASE WHEN $8::text = 'name' AND $9::text = 'desc' THEN p.name END DESC,
  CASE WHEN $8::text = 'code' AND $9::text = 'desc' THEN p.code END DESC,
  CASE WHEN $8::text = 'code' THEN p.code END ASC,
  CASE WHEN $8::text = 'selling_price' AND $9::text = 'desc' THEN p.selling_price END DESC NULLS LAST,
  CASE WHEN $8::text = 'selling_price' THEN p.selling_price END ASC NULLS LAST,
  CASE WHEN $8::text = 'purchase_price' AND $9::text = 'desc' THEN p.purchase_price END DESC NULLS LAST,
  CASE WHEN $8::text = 'purchase_price' THEN p.purchase_price END ASC NULLS LAST,
  CASE WHEN $8::text = 'created_at' AND $9::text = 'desc' THEN p.created_at END DESC NULLS LAST,
  CASE WHEN $8::text = 'created_at' THEN p.created_at END ASC NULLS LAST,
  -- Stock: the same expression the row returns, so what the header sorts is
  -- what the column shows (sum of available per warehouse).
  CASE WHEN $8::text = 'stock' AND $9::text = 'desc'
       THEN (SELECT COALESCE(SUM(GREATEST(0, st3.quantity - st3.reserved_quantity)), 0) FROM stocks st3 WHERE st3.product_id = p.id) END DESC,
  CASE WHEN $8::text = 'stock'
       THEN (SELECT COALESCE(SUM(GREATEST(0, st3.quantity - st3.reserved_quantity)), 0) FROM stocks st3 WHERE st3.product_id = p.id) END ASC,
  CASE WHEN $10::text = 'name' THEN p.name END ASC,
  CASE WHEN $10::text = 'price_asc' THEN p.selling_price END ASC,
  CASE WHEN $10::text = 'price_desc' THEN p.selling_price END DESC NULLS LAST,
  CASE WHEN $10::text = 'newest' THEN p.created_at END DESC NULLS LAST,
  CASE WHEN $10::text = 'popularity' THEN p.is_featured::int END DESC NULLS LAST,
  p.code ASC
LIMIT $1 OFFSET $2
`

type ListProductsParams struct {
	Limit      int32       `json:"limit"`
	Offset     int32       `json:"offset"`
	Search     pgtype.Text `json:"search"`
	CategoryID pgtype.Int8 `json:"category_id"`
	BrandID    pgtype.Int8 `json:"brand_id"`
	IsActive   pgtype.Bool `json:"is_active"`
	InStock    pgtype.Bool `json:"in_stock"`
	SortBy     pgtype.Text `json:"sort_by"`
	SortDir    pgtype.Text `json:"sort_dir"`
	Sort       pgtype.Text `json:"sort"`
}

type ListProductsRow 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"`
}

// ERP column sort (sort_by + sort_dir) wins when present: the table headers
// are per-column and used to be ignored, so every click returned the same
// order. Whitelisted columns only — a sort key from the query string must
// never reach SQL as an identifier.
// A null price/stock stays at the bottom in BOTH directions, and the code
// tiebreak makes paging stable.
func (q *Queries) ListProducts(ctx context.Context, arg ListProductsParams) ([]ListProductsRow, error) {
	rows, err := q.db.Query(ctx, listProducts,
		arg.Limit,
		arg.Offset,
		arg.Search,
		arg.CategoryID,
		arg.BrandID,
		arg.IsActive,
		arg.InStock,
		arg.SortBy,
		arg.SortDir,
		arg.Sort,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListProductsRow
	for rows.Next() {
		var i ListProductsRow
		if err := rows.Scan(
			&i.ID,
			&i.Code,
			&i.Name,
			&i.Description,
			&i.CategoryID,
			&i.UnitID,
			&i.PurchasePrice,
			&i.SellingPrice,
			&i.MinStock,
			&i.IsActive,
			&i.IsFeatured,
			&i.Image,
			&i.DeletedAt,
			&i.CreatedAt,
			&i.UpdatedAt,
			&i.BrandID,
			&i.ID_2,
			&i.Name_2,
			&i.Description_2,
			&i.ID_3,
			&i.Name_3,
			&i.Symbol,
			&i.Description_3,
			&i.BrandName,
			&i.TotalStock,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const listUnits = `-- name: ListUnits :many
SELECT id, name, symbol, COALESCE(description, '') AS description FROM units
WHERE ($3::text IS NULL OR (name ILIKE '%' || $3 || '%' OR symbol ILIKE '%' || $3 || '%'))
ORDER BY name
LIMIT $1 OFFSET $2
`

type ListUnitsParams struct {
	Limit  int32       `json:"limit"`
	Offset int32       `json:"offset"`
	Search pgtype.Text `json:"search"`
}

type ListUnitsRow struct {
	ID          int64       `json:"id"`
	Name        string      `json:"name"`
	Symbol      pgtype.Text `json:"symbol"`
	Description string      `json:"description"`
}

func (q *Queries) ListUnits(ctx context.Context, arg ListUnitsParams) ([]ListUnitsRow, error) {
	rows, err := q.db.Query(ctx, listUnits, arg.Limit, arg.Offset, arg.Search)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ListUnitsRow
	for rows.Next() {
		var i ListUnitsRow
		if err := rows.Scan(
			&i.ID,
			&i.Name,
			&i.Symbol,
			&i.Description,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const lookupProductByBarcode = `-- name: LookupProductByBarcode :one
SELECT pb.id AS barcode_id, pb.barcode, pb.is_primary, pb.product_unit_id,
       p.id AS product_id, p.code AS product_code, p.name AS product_name,
       p.selling_price, p.is_active,
       pu.unit_id, pu.conversion_factor, pu.selling_price AS unit_selling_price,
       pu.min_purchase, pu.is_base AS unit_is_base, pu.is_active AS unit_is_active,
       u.name AS unit_name, u.symbol AS unit_symbol
FROM product_barcodes pb
JOIN products p ON p.id = pb.product_id
LEFT JOIN product_units pu ON pu.id = pb.product_unit_id
LEFT JOIN units u ON u.id = pu.unit_id
WHERE pb.barcode = $1 AND p.deleted_at IS NULL
`

type LookupProductByBarcodeRow struct {
	BarcodeID        int64               `json:"barcode_id"`
	Barcode          string              `json:"barcode"`
	IsPrimary        bool                `json:"is_primary"`
	ProductUnitID    pgtype.Int8         `json:"product_unit_id"`
	ProductID        int64               `json:"product_id"`
	ProductCode      string              `json:"product_code"`
	ProductName      string              `json:"product_name"`
	SellingPrice     decimal.Decimal     `json:"selling_price"`
	IsActive         bool                `json:"is_active"`
	UnitID           pgtype.Int8         `json:"unit_id"`
	ConversionFactor decimal.NullDecimal `json:"conversion_factor"`
	UnitSellingPrice decimal.NullDecimal `json:"unit_selling_price"`
	MinPurchase      decimal.NullDecimal `json:"min_purchase"`
	UnitIsBase       pgtype.Bool         `json:"unit_is_base"`
	UnitIsActive     pgtype.Bool         `json:"unit_is_active"`
	UnitName         pgtype.Text         `json:"unit_name"`
	UnitSymbol       pgtype.Text         `json:"unit_symbol"`
}

func (q *Queries) LookupProductByBarcode(ctx context.Context, barcode string) (LookupProductByBarcodeRow, error) {
	row := q.db.QueryRow(ctx, lookupProductByBarcode, barcode)
	var i LookupProductByBarcodeRow
	err := row.Scan(
		&i.BarcodeID,
		&i.Barcode,
		&i.IsPrimary,
		&i.ProductUnitID,
		&i.ProductID,
		&i.ProductCode,
		&i.ProductName,
		&i.SellingPrice,
		&i.IsActive,
		&i.UnitID,
		&i.ConversionFactor,
		&i.UnitSellingPrice,
		&i.MinPurchase,
		&i.UnitIsBase,
		&i.UnitIsActive,
		&i.UnitName,
		&i.UnitSymbol,
	)
	return i, err
}

const softDeleteProduct = `-- name: SoftDeleteProduct :execrows
UPDATE products SET deleted_at = now() WHERE id = $1 AND deleted_at IS NULL
`

func (q *Queries) SoftDeleteProduct(ctx context.Context, id int64) (int64, error) {
	result, err := q.db.Exec(ctx, softDeleteProduct, id)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const updateBrand = `-- name: UpdateBrand :execrows
UPDATE brands SET name = $2, description = $3, is_active = $4, updated_at = now()
WHERE id = $1
`

type UpdateBrandParams struct {
	ID          int64  `json:"id"`
	Name        string `json:"name"`
	Description string `json:"description"`
	IsActive    bool   `json:"is_active"`
}

func (q *Queries) UpdateBrand(ctx context.Context, arg UpdateBrandParams) (int64, error) {
	result, err := q.db.Exec(ctx, updateBrand,
		arg.ID,
		arg.Name,
		arg.Description,
		arg.IsActive,
	)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const updateCategory = `-- name: UpdateCategory :execrows
UPDATE categories SET name = $2, description = $3, is_active = $4 WHERE id = $1
`

type UpdateCategoryParams struct {
	ID          int64       `json:"id"`
	Name        string      `json:"name"`
	Description pgtype.Text `json:"description"`
	IsActive    bool        `json:"is_active"`
}

func (q *Queries) UpdateCategory(ctx context.Context, arg UpdateCategoryParams) (int64, error) {
	result, err := q.db.Exec(ctx, updateCategory,
		arg.ID,
		arg.Name,
		arg.Description,
		arg.IsActive,
	)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const updateProduct = `-- name: UpdateProduct :execrows
UPDATE products SET name = $2, description = $3, category_id = $4, unit_id = $5,
       purchase_price = $6, selling_price = $7, min_stock = $8, is_active = $9,
       is_featured = $10, image = $11, code = $12, brand_id = $13, updated_at = now()
WHERE id = $1 AND deleted_at IS NULL
`

type UpdateProductParams struct {
	ID            int64           `json:"id"`
	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"`
	Code          string          `json:"code"`
	BrandID       pgtype.Int8     `json:"brand_id"`
}

func (q *Queries) UpdateProduct(ctx context.Context, arg UpdateProductParams) (int64, error) {
	result, err := q.db.Exec(ctx, updateProduct,
		arg.ID,
		arg.Name,
		arg.Description,
		arg.CategoryID,
		arg.UnitID,
		arg.PurchasePrice,
		arg.SellingPrice,
		arg.MinStock,
		arg.IsActive,
		arg.IsFeatured,
		arg.Image,
		arg.Code,
		arg.BrandID,
	)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const updateProductUnit = `-- name: UpdateProductUnit :execrows
UPDATE product_units SET unit_id = $2, conversion_factor = $3, selling_price = $4,
       min_purchase = $5, is_base = $6, is_active = $7, updated_at = now()
WHERE id = $1
`

type UpdateProductUnitParams struct {
	ID               int64               `json:"id"`
	UnitID           int64               `json:"unit_id"`
	ConversionFactor decimal.Decimal     `json:"conversion_factor"`
	SellingPrice     decimal.NullDecimal `json:"selling_price"`
	MinPurchase      decimal.Decimal     `json:"min_purchase"`
	IsBase           bool                `json:"is_base"`
	IsActive         bool                `json:"is_active"`
}

func (q *Queries) UpdateProductUnit(ctx context.Context, arg UpdateProductUnitParams) (int64, error) {
	result, err := q.db.Exec(ctx, updateProductUnit,
		arg.ID,
		arg.UnitID,
		arg.ConversionFactor,
		arg.SellingPrice,
		arg.MinPurchase,
		arg.IsBase,
		arg.IsActive,
	)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}

const updateUnit = `-- name: UpdateUnit :execrows
UPDATE units SET name = $2, symbol = $3, description = $4 WHERE id = $1
`

type UpdateUnitParams struct {
	ID          int64       `json:"id"`
	Name        string      `json:"name"`
	Symbol      pgtype.Text `json:"symbol"`
	Description pgtype.Text `json:"description"`
}

func (q *Queries) UpdateUnit(ctx context.Context, arg UpdateUnitParams) (int64, error) {
	result, err := q.db.Exec(ctx, updateUnit,
		arg.ID,
		arg.Name,
		arg.Symbol,
		arg.Description,
	)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected(), nil
}