cloth-design/back-end/src/database/init.sql

-- 创建设计使用表
CREATE TABLE IF NOT EXISTS design_usage (
  id SERIAL PRIMARY KEY,
  part_id INTEGER NOT NULL CHECK (part_id BETWEEN 1 AND 4),
  color_code CHAR(2) NOT NULL CHECK (color_code ~ '^[0-9A-F]{2}$'),
  usage_count INTEGER NOT NULL DEFAULT 0,
  UNIQUE (part_id, color_code)
);

-- 插入初始数据
INSERT INTO design_usage (part_id, color_code, usage_count)
VALUES
  (1, '00', 150), (1, '01', 120), (1, '02', 95), (1, '03', 80),
  (2, '04', 65), (2, '05', 140), (2, '06', 60), (2, '07', 85),
  (3, '08', 110), (3, '09', 130), (3, '0A', 45), (3, '0B', 100),
  (4, '0C', 55), (4, '0D', 75), (4, '0E', 125), (4, '0F', 90)
ON CONFLICT (part_id, color_code) DO UPDATE
SET usage_count = EXCLUDED.usage_count;