sulaimaannaasif6866
916bdd9c7c
- 新增 category.archive_kind 与 kind_field 字段模板表（迁移 0010） - 种子 electronics 字段模板 + 3C 品类树（手机/笔记本/平板等） - 后端按档案模式动态计算完整度/合格：食品沿用 food_detail， 其它模式走 product.attributes + kind_field - 新增 GET /api/kind-fields?kind= 接口 - 后台编辑页按品类模式动态渲染规格参数表单 - 公开详情页/接口输出带标签的规格表 Co-Authored-By: Devin AI <158243242+devin-ai-integration[bot]@users.noreply.github.com>
65 lines
3.7 KiB
SQL
65 lines
3.7 KiB
SQL
-- Generic, extensible "archive kind" (档案模式) framework.
|
|
-- Instead of a dedicated detail table per domain (food/electronics/drug/...),
|
|
-- each category belongs to an archive_kind, and a metadata table (kind_field)
|
|
-- describes the editable spec fields for that kind. Non-food spec values live
|
|
-- in the existing product.attributes JSONB. Adding a new domain later (drug,
|
|
-- machinery, ...) is just: seed kind_field rows + a category subtree.
|
|
|
|
ALTER TABLE category ADD COLUMN archive_kind VARCHAR(24) NOT NULL DEFAULT 'generic';
|
|
|
|
-- The existing seeded tree is the food domain.
|
|
UPDATE category SET archive_kind = 'food' WHERE path <@ 'food';
|
|
|
|
-- Field template per archive kind: drives the dynamic admin form and the
|
|
-- kind-aware completeness/qualified computation.
|
|
CREATE TABLE kind_field (
|
|
kind VARCHAR(24) NOT NULL,
|
|
field_key VARCHAR(64) NOT NULL,
|
|
group_label TEXT NOT NULL DEFAULT '',
|
|
label_zh TEXT NOT NULL,
|
|
field_type VARCHAR(16) NOT NULL DEFAULT 'text',
|
|
unit TEXT,
|
|
options TEXT[] NOT NULL DEFAULT '{}',
|
|
placeholder TEXT,
|
|
sort_order INT NOT NULL DEFAULT 0,
|
|
qualified BOOLEAN NOT NULL DEFAULT false,
|
|
PRIMARY KEY (kind, field_key),
|
|
CONSTRAINT kind_field_type_chk CHECK (field_type IN ('text','number','textarea','select','list'))
|
|
);
|
|
|
|
-- Seed the electronics (3C) field template.
|
|
INSERT INTO kind_field (kind, field_key, group_label, label_zh, field_type, unit, sort_order, qualified) VALUES
|
|
('electronics','model_number', '基础规格','型号', 'text', NULL, 10, true),
|
|
('electronics','ccc_cert', '基础规格','3C认证号(CCC)','text', NULL, 20, true),
|
|
('electronics','color', '基础规格','颜色', 'text', NULL, 30, false),
|
|
('electronics','release_year', '基础规格','发布年份', 'number', NULL, 40, false),
|
|
('electronics','warranty_months','基础规格','保修期', 'number', '月', 50, false),
|
|
('electronics','dimensions', '外形','尺寸(长x宽x高)', 'text', 'mm', 60, false),
|
|
('electronics','weight', '外形','重量', 'number', 'g', 70, false),
|
|
('electronics','power', '外形','电源/功率', 'text', NULL, 80, false),
|
|
('electronics','os', '关键参数','操作系统', 'text', NULL, 90, false),
|
|
('electronics','cpu', '关键参数','处理器', 'text', NULL, 100, false),
|
|
('electronics','ram', '关键参数','内存', 'text', NULL, 110, false),
|
|
('electronics','storage', '关键参数','存储', 'text', NULL, 120, false),
|
|
('electronics','screen_size', '关键参数','屏幕尺寸', 'text', NULL, 130, true),
|
|
('electronics','battery', '关键参数','电池容量', 'text', NULL, 140, false),
|
|
('electronics','ports', '关键参数','接口', 'text', NULL, 150, false);
|
|
|
|
-- Seed the 3C category tree.
|
|
INSERT INTO category (name_zh, name_en, parent_id, path, level, archive_kind)
|
|
VALUES ('电子数码', 'Electronics', NULL, 'electronics', 0, 'electronics');
|
|
|
|
INSERT INTO category (name_zh, name_en, parent_id, path, level, archive_kind)
|
|
SELECT v.name_zh, v.name_en, c.id, v.path::ltree, 1, 'electronics'
|
|
FROM (VALUES
|
|
('手机', 'Smartphone', 'electronics.phone'),
|
|
('笔记本电脑', 'Laptop', 'electronics.laptop'),
|
|
('平板电脑', 'Tablet', 'electronics.tablet'),
|
|
('智能手表', 'Smartwatch', 'electronics.watch'),
|
|
('耳机', 'Headphone', 'electronics.headphone'),
|
|
('相机', 'Camera', 'electronics.camera'),
|
|
('电视', 'TV', 'electronics.tv'),
|
|
('家用电器', 'Home appliance', 'electronics.appliance')
|
|
) AS v(name_zh, name_en, path)
|
|
JOIN category c ON c.path = 'electronics';
|