상품 카탈로그: 카테고리/태그/변형

-- Adjacency List + Materialized Path
CREATE TABLE categories (
  id UUID PRIMARY KEY,
  parent_id UUID REFERENCES categories(id),
  name VARCHAR NOT NULL,
  slug VARCHAR UNIQUE NOT NULL,
  path LTREE,  -- '식품.음료.커피'
  level INT NOT NULL,
  sort_order INT DEFAULT 0
);

CREATE INDEX idx_categories_path ON categories USING GIST (path);


-- 트리거로 path 자동 갱신
CREATE FUNCTION update_category_path() RETURNS trigger AS $$
DECLARE
  parent_path LTREE;
BEGIN
  IF NEW.parent_id IS NULL THEN
    NEW.path := NEW.slug::ltree;
    NEW.level := 1;
  ELSE
    SELECT path INTO parent_path FROM categories WHERE id = NEW.parent_id;
    NEW.path := parent_path || NEW.slug::ltree;
    NEW.level := nlevel(NEW.path);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 쿼리
-- 식품 카테고리의 모든 하위 (재귀)
SELECT * FROM categories WHERE path <@ '식품';

-- 음료의 직계 자식만
SELECT * FROM categories
WHERE parent_id = (SELECT id FROM categories WHERE slug = '음료');

-- 경로 탐색 (breadcrumb)
SELECT * FROM categories
WHERE path @> (SELECT path FROM categories WHERE id = ?)
ORDER BY level;

-- 옵션 정의
CREATE TABLE option_types (
  id UUID PRIMARY KEY,
  name VARCHAR UNIQUE NOT NULL  -- color, size, material
);

CREATE TABLE option_values (
  id UUID PRIMARY KEY,
  option_type_id UUID REFERENCES option_types(id),
  value VARCHAR NOT NULL  -- 'red', 'L'
);


-- 상품 → 옵션 연결
CREATE TABLE product_options (
  product_id UUID,
  option_type_id UUID,
  PRIMARY KEY (product_id, option_type_id)
);


-- 변형 (모든 조합)
CREATE TABLE product_variants (
  id UUID PRIMARY KEY,
  product_id UUID,
  sku VARCHAR UNIQUE NOT NULL,
  name VARCHAR NOT NULL,  -- "Red - L"
  attributes JSONB,        -- { color: 'red', size: 'L' }
  price DECIMAL(10,0),
  stock_quantity INT DEFAULT 0,
  image_url VARCHAR
);


-- 변형의 옵션 값
CREATE TABLE variant_option_values (
  variant_id UUID REFERENCES product_variants(id),
  option_value_id UUID REFERENCES option_values(id),
  PRIMARY KEY (variant_id, option_value_id)
);

async function generateVariants(productId: string, options: Record<string, string[]>) {
  // options = { color: ['red', 'blue'], size: ['S', 'M', 'L'] }
  // → 6개 조합 (red-S, red-M, red-L, blue-S, blue-M, blue-L)

  const combinations = cartesianProduct(Object.values(options));

  return Promise.all(combinations.map(async (combo) => {
    const attrs = Object.keys(options).reduce((acc, key, i) => {
      acc[key] = combo[i];
      return acc;
    }, {} as any);

    const sku = `${productId.slice(0, 8)}-${combo.join('-')}`.toUpperCase();
    const name = combo.join(' - ');

    return db.productVariant.create({
      data: { productId, sku, name, attributes: attrs, price: 0, stockQuantity: 0 },
    });
  }));
}

function cartesianProduct(arrays: any[][]): any[][] {
  return arrays.reduce<any[][]>((acc, curr) =>
    acc.flatMap(c => curr.map(v => [...c, v])),
    [[]]
  );
}

-- EAV (Entity-Attribute-Value) 안티패턴 → JSONB 사용
ALTER TABLE products ADD COLUMN attributes JSONB DEFAULT '{}';

-- 예시
UPDATE products SET attributes = '{
  "brand": "나이키",
  "material": "면 100%",
  "origin": "베트남",
  "weight_g": 250,
  "tags": ["여름", "캐주얼"]
}'::jsonb WHERE id = ?;


-- GIN 인덱스
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);


-- 필터 검색
SELECT * FROM products
WHERE attributes @> '{"brand": "나이키"}'
  AND attributes ? 'tags'
  AND attributes->'tags' @> '["여름"]';

app.get('/api/products', async (req, res) => {
  const {
    q, category, brand, color, size,
    minPrice, maxPrice,
    sort = 'created_desc',
    page = 1, limit = 24,
  } = req.query;

  const where: any = { status: 'active' };

  if (category) {
    // category 트리 모두
    const cat = await db.category.findUnique({ where: { slug: category as string } });
    where.category = { path: { startsWith: cat!.path } };
  }

  if (brand) where.attributes = { ...where.attributes, path: ['brand'], equals: brand };
  if (q) where.OR = [
    { name: { contains: q, mode: 'insensitive' } },
    { description: { contains: q, mode: 'insensitive' } },
  ];
  if (minPrice) where.basePrice = { ...where.basePrice, gte: Number(minPrice) };
  if (maxPrice) where.basePrice = { ...where.basePrice, lte: Number(maxPrice) };

  const orderBy = {
    'price_asc': { basePrice: 'asc' },
    'price_desc': { basePrice: 'desc' },
    'popular': { ordersCount: 'desc' },
    'created_desc': { createdAt: 'desc' },
  }[sort as string] ?? { createdAt: 'desc' };

  const [items, total] = await Promise.all([
    db.product.findMany({
      where,
      orderBy,
      skip: (Number(page) - 1) * Number(limit),
      take: Number(limit),
      include: { variants: { where: { stockQuantity: { gt: 0 } }, take: 1 } },
    }),
    db.product.count({ where }),
  ]);

  res.json({ items, total, page: Number(page) });
});