-- 交易模块数据库表创建脚本
|
-- 创建时间: 2024-08-07
|
-- 数据库: PostgreSQL
|
|
-- 1. 产品定价表 (product_pricing)
|
CREATE TABLE product_pricing (
|
id BIGSERIAL PRIMARY KEY,
|
suite_name VARCHAR(100) NOT NULL COMMENT '产品套件名称',
|
sales_form VARCHAR(20) NOT NULL CHECK (sales_form IN ('买断', '租赁', '私有增包量', '公有增包量', 'OTA服务', '资源包', '个人')) COMMENT '销售形式',
|
customer_type VARCHAR(10) NOT NULL CHECK (customer_type IN ('企业', '个人', '项目部')) COMMENT '客户对象',
|
account_limit VARCHAR(20) DEFAULT '不限' COMMENT '账户数量',
|
concurrent_nodes VARCHAR(20) DEFAULT '不限' COMMENT '并发节点数',
|
price_type VARCHAR(10) NOT NULL CHECK (price_type IN ('积分', '协议', '货币', '免费')) COMMENT '价格设置',
|
price_unit VARCHAR(20) NOT NULL CHECK (price_unit IN ('套', '套/年', '年', '个', '次')) COMMENT '价格单位',
|
price NUMERIC(15,2) NOT NULL COMMENT '价格值',
|
is_active BOOLEAN DEFAULT TRUE COMMENT '启用状态',
|
product_id BIGINT NOT NULL COMMENT '关联产品ID',
|
product_name VARCHAR(100) COMMENT '产品名称',
|
provider_id BIGINT COMMENT '产品提供者ID',
|
provider_name VARCHAR(100) COMMENT '产品提供者名称',
|
description TEXT COMMENT '产品描述',
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
created_by BIGINT COMMENT '创建人ID',
|
updated_by BIGINT COMMENT '更新人ID',
|
deleted INTEGER DEFAULT 0 COMMENT '逻辑删除：1-已删除，0-未删除'
|
);
|
|
-- 2. 购物车表 (shopping_cart)
|
CREATE TABLE shopping_cart (
|
id BIGSERIAL PRIMARY KEY,
|
user_id BIGINT NOT NULL COMMENT '用户ID',
|
unit_id BIGINT COMMENT '单位ID',
|
suite_name VARCHAR(100) NOT NULL COMMENT '产品套件名称',
|
sales_form VARCHAR(20) NOT NULL CHECK (sales_form IN ('买断', '租赁', '私有增包量', '公有增包量', 'OTA服务', '资源包', '个人')) COMMENT '销售形式',
|
customer_type VARCHAR(10) NOT NULL CHECK (customer_type IN ('企业', '个人', '项目部')) COMMENT '客户对象',
|
account_limit VARCHAR(20) DEFAULT '不限' COMMENT '账户数量',
|
concurrent_nodes VARCHAR(20) DEFAULT '不限' COMMENT '并发节点数',
|
price_type VARCHAR(10) NOT NULL CHECK (price_type IN ('积分', '协议', '货币', '免费')) COMMENT '价格类型',
|
price_unit VARCHAR(20) NOT NULL CHECK (price_unit IN ('套', '套/年', '年', '个', '次')) COMMENT '价格单位',
|
unit_price NUMERIC(15,2) NOT NULL COMMENT '单价',
|
quantity INTEGER NOT NULL DEFAULT 1 COMMENT '数量',
|
duration INTEGER COMMENT '期限(年)',
|
product_id BIGINT NOT NULL COMMENT '产品ID',
|
product_name VARCHAR(100) COMMENT '产品名称',
|
provider_id BIGINT COMMENT '产品提供者ID',
|
provider_name VARCHAR(100) COMMENT '产品提供者名称',
|
total_amount NUMERIC(15,2) COMMENT '总金额',
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
deleted INTEGER DEFAULT 0 COMMENT '逻辑删除：1-已删除，0-未删除'
|
);
|
|
-- 购物车表
|
CREATE TABLE IF NOT EXISTS cart (
|
id BIGSERIAL PRIMARY KEY,
|
user_id BIGINT NOT NULL COMMENT '用户ID',
|
unit_id BIGINT NOT NULL COMMENT '单位ID',
|
pricing_id BIGINT NOT NULL COMMENT '商品定价ID',
|
product_id BIGINT NOT NULL COMMENT '商品ID',
|
product_name VARCHAR(255) COMMENT '商品名称',
|
suite_name VARCHAR(255) COMMENT '商品套件名称',
|
sales_form VARCHAR(100) COMMENT '销售形式',
|
customer_type VARCHAR(100) COMMENT '客户对象',
|
account_limit VARCHAR(100) COMMENT '账户数量',
|
concurrent_nodes VARCHAR(100) COMMENT '并发节点数',
|
price_type VARCHAR(100) COMMENT '价格类型',
|
price_unit VARCHAR(100) COMMENT '价格单位',
|
unit_price DECIMAL(15,2) NOT NULL COMMENT '单价',
|
quantity INTEGER NOT NULL DEFAULT 1 COMMENT '数量',
|
duration INTEGER COMMENT '年限',
|
total_price DECIMAL(15,2) COMMENT '小计金额',
|
provider_id BIGINT COMMENT '产品提供者ID',
|
provider_name VARCHAR(255) COMMENT '产品提供者名称',
|
remarks TEXT COMMENT '备注',
|
add_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
|
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
deleted INTEGER DEFAULT 0 COMMENT '逻辑删除：1-已删除，0-未删除',
|
UNIQUE KEY uk_user_unit_pricing (user_id, unit_id, pricing_id, deleted)
|
);
|
|
-- 3. 订单表 (order_info)
|
CREATE TABLE order_info (
|
order_id VARCHAR(50) PRIMARY KEY COMMENT '订单编号',
|
product_id BIGINT NOT NULL COMMENT '产品ID',
|
user_id BIGINT NOT NULL COMMENT '用户ID',
|
unit_id BIGINT COMMENT '单位ID',
|
product_name VARCHAR(100) NOT NULL COMMENT '产品名称',
|
provider_name VARCHAR(100) NOT NULL COMMENT '产品提供者名称',
|
provider_id BIGINT NOT NULL COMMENT '提供者ID',
|
apply_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '申请时间',
|
order_status VARCHAR(20) NOT NULL DEFAULT '待审批' CHECK (order_status IN ('待审批', '待审批授权', '待授权', '待上传文件', '待交易确认', '已完成', '已取消', '已驳回')) COMMENT '订单状态',
|
total_amount NUMERIC(15,2) NOT NULL COMMENT '订单总金额',
|
payment_type VARCHAR(20) COMMENT '支付方式(积分/货币/协议)',
|
payment_status VARCHAR(20) DEFAULT '未支付' CHECK (payment_status IN ('未支付', '已支付', '支付失败', '已退款')) COMMENT '支付状态',
|
workflow_id VARCHAR(50) COMMENT '工作流ID',
|
current_step VARCHAR(50) COMMENT '当前审批步骤',
|
approval_flow TEXT COMMENT '审批流程配置',
|
buyer_remarks TEXT COMMENT '买家备注',
|
seller_remarks TEXT COMMENT '卖家备注',
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
deleted INTEGER DEFAULT 0 COMMENT '逻辑删除：1-已删除，0-未删除'
|
);
|
|
-- 4. 订单详情表 (order_detail)
|
CREATE TABLE order_detail (
|
id BIGSERIAL PRIMARY KEY,
|
order_id VARCHAR(50) NOT NULL COMMENT '关联订单ID',
|
pricing_id BIGINT NOT NULL COMMENT '关联产品定价ID',
|
product_id BIGINT NOT NULL COMMENT '产品ID',
|
suite_name VARCHAR(100) NOT NULL COMMENT '产品套件名称',
|
sales_form VARCHAR(20) NOT NULL COMMENT '销售形式',
|
customer_type VARCHAR(10) NOT NULL COMMENT '客户对象',
|
account_limit VARCHAR(20) COMMENT '账户数量',
|
concurrent_nodes VARCHAR(20) COMMENT '并发节点数',
|
price_type VARCHAR(10) NOT NULL COMMENT '价格类型',
|
price_unit VARCHAR(20) NOT NULL COMMENT '价格单位',
|
unit_price NUMERIC(15,2) NOT NULL COMMENT '单价',
|
quantity INTEGER NOT NULL COMMENT '数量',
|
duration INTEGER COMMENT '年限',
|
total_price NUMERIC(15,2) NOT NULL COMMENT '小计金额',
|
provider_id BIGINT NOT NULL COMMENT '产品提供者ID',
|
provider_name VARCHAR(100) COMMENT '产品提供者名称',
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
remarks TEXT COMMENT '备注',
|
deleted INTEGER DEFAULT 0 COMMENT '逻辑删除：1-已删除，0-未删除'
|
);
|
|
-- 5. 订单附件表 (order_attachment)
|
CREATE TABLE order_attachment (
|
id BIGSERIAL PRIMARY KEY,
|
order_id VARCHAR(50) NOT NULL COMMENT '关联订单ID',
|
file_name VARCHAR(255) NOT NULL COMMENT '附件名称',
|
original_name VARCHAR(255) COMMENT '原始文件名',
|
file_type VARCHAR(50) NOT NULL COMMENT '附件类型',
|
file_size BIGINT NOT NULL COMMENT '附件大小(字节)',
|
file_url VARCHAR(500) NOT NULL COMMENT '附件地址',
|
file_path VARCHAR(500) COMMENT '文件存储路径',
|
bucket_name VARCHAR(100) COMMENT 'MinIO存储桶名称',
|
object_name VARCHAR(500) COMMENT 'MinIO对象名称',
|
upload_user_id BIGINT COMMENT '上传用户ID',
|
upload_user_name VARCHAR(100) COMMENT '上传用户名',
|
attachment_type VARCHAR(50) COMMENT '附件类型(合同/发票/其他)',
|
description TEXT COMMENT '附件描述',
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
deleted INTEGER DEFAULT 0 COMMENT '逻辑删除：1-已删除，0-未删除'
|
);
|
|
-- 6. 订单审核表 (order_approval)
|
CREATE TABLE order_approval (
|
id BIGSERIAL PRIMARY KEY,
|
order_id VARCHAR(50) NOT NULL COMMENT '关联订单ID',
|
approval_step VARCHAR(50) NOT NULL COMMENT '审批步骤',
|
approval_type VARCHAR(20) NOT NULL CHECK (approval_type IN ('审批', '授权')) COMMENT '审批类型',
|
approval_opinion TEXT COMMENT '审核意见',
|
approval_result VARCHAR(20) NOT NULL CHECK (approval_result IN ('通过', '驳回', '待处理')) COMMENT '审批结果',
|
approver_id BIGINT NOT NULL COMMENT '审核人ID',
|
approver_name VARCHAR(100) NOT NULL COMMENT '审核人姓名',
|
approver_role VARCHAR(100) COMMENT '审核人角色',
|
approval_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '审核时间',
|
next_approver_id BIGINT COMMENT '下一审批人ID',
|
next_approver_name VARCHAR(100) COMMENT '下一审批人姓名',
|
workflow_status VARCHAR(20) COMMENT '工作流状态',
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
deleted INTEGER DEFAULT 0 COMMENT '逻辑删除：1-已删除，0-未删除'
|
);
|
|
-- 7. 订单评价表 (order_evaluation)
|
CREATE TABLE order_evaluation (
|
id BIGSERIAL PRIMARY KEY,
|
order_id VARCHAR(50) NOT NULL COMMENT '关联订单ID',
|
evaluator_id BIGINT NOT NULL COMMENT '评价人ID',
|
evaluator_name VARCHAR(100) NOT NULL COMMENT '评价人姓名',
|
evaluator_type VARCHAR(20) NOT NULL CHECK (evaluator_type IN ('买家', '卖家')) COMMENT '评价人类型',
|
content TEXT NOT NULL COMMENT '评价内容',
|
rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5) COMMENT '评分(1-5)',
|
service_rating INTEGER CHECK (service_rating >= 1 AND service_rating <= 5) COMMENT '服务评分',
|
quality_rating INTEGER CHECK (quality_rating >= 1 AND quality_rating <= 5) COMMENT '质量评分',
|
delivery_rating INTEGER CHECK (delivery_rating >= 1 AND delivery_rating <= 5) COMMENT '交付评分',
|
is_anonymous BOOLEAN DEFAULT FALSE COMMENT '是否匿名评价',
|
reply_content TEXT COMMENT '回复内容',
|
reply_user_id BIGINT COMMENT '回复用户ID',
|
reply_time TIMESTAMP COMMENT '回复时间',
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
deleted INTEGER DEFAULT 0 COMMENT '逻辑删除：1-已删除，0-未删除'
|
);
|
|
-- 8. 积分表 (points)
|
CREATE TABLE points (
|
id BIGSERIAL PRIMARY KEY,
|
points_name VARCHAR(100) NOT NULL COMMENT '积分名称',
|
points_code VARCHAR(50) NOT NULL UNIQUE COMMENT '积分编码',
|
points_type VARCHAR(20) NOT NULL CHECK (points_type IN ('个人积分', '单位积分', '产品积分')) COMMENT '积分类型',
|
effective_start DATE NOT NULL COMMENT '生效开始时间',
|
effective_end DATE COMMENT '生效结束时间',
|
modifier_id BIGINT NOT NULL COMMENT '修改人ID',
|
modifier_name VARCHAR(100) NOT NULL COMMENT '修改人姓名',
|
version VARCHAR(20) NOT NULL COMMENT '版本号',
|
description TEXT COMMENT '积分描述',
|
status VARCHAR(10) DEFAULT '启用' CHECK (status IN ('启用', '禁用')) COMMENT '状态',
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
deleted INTEGER DEFAULT 0 COMMENT '逻辑删除：1-已删除，0-未删除'
|
);
|
|
-- 9. 积分规则表 (points_rule) - 更新现有表结构
|
ALTER TABLE points_rule ADD COLUMN IF NOT EXISTS points_id BIGINT COMMENT '关联积分ID';
|
ALTER TABLE points_rule ADD COLUMN IF NOT EXISTS rule_code VARCHAR(50) COMMENT '规则编码';
|
ALTER TABLE points_rule ADD COLUMN IF NOT EXISTS effective_start DATE COMMENT '生效开始时间';
|
ALTER TABLE points_rule ADD COLUMN IF NOT EXISTS effective_end DATE COMMENT '生效结束时间';
|
ALTER TABLE points_rule ADD COLUMN IF NOT EXISTS priority INTEGER DEFAULT 0 COMMENT '优先级';
|
|
-- 10. 积分规则详情表 (points_rule_detail)
|
CREATE TABLE points_rule_detail (
|
id BIGSERIAL PRIMARY KEY,
|
rule_id BIGINT NOT NULL COMMENT '关联积分规则ID',
|
points_id BIGINT NOT NULL COMMENT '关联积分ID',
|
points_value INTEGER NOT NULL COMMENT '积分值',
|
daily_limit INTEGER COMMENT '每日积分上限值',
|
monthly_limit INTEGER COMMENT '每月积分上限值',
|
yearly_limit INTEGER COMMENT '每年积分上限值',
|
min_value INTEGER DEFAULT 0 COMMENT '最小值',
|
max_value INTEGER COMMENT '最大值',
|
conversion_rate NUMERIC(10,4) DEFAULT 1.0 COMMENT '转换比率',
|
effective_start DATE COMMENT '生效开始时间',
|
effective_end DATE COMMENT '生效结束时间',
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
deleted INTEGER DEFAULT 0 COMMENT '逻辑删除：1-已删除，0-未删除'
|
);
|
|
-- 11. 积分流水表 (points_transaction) - 新增字段
|
ALTER TABLE points_flow ADD COLUMN IF NOT EXISTS transaction_type VARCHAR(20) COMMENT '交易类型(获取/消耗/转换)';
|
ALTER TABLE points_flow ADD COLUMN IF NOT EXISTS related_id VARCHAR(50) COMMENT '关联业务ID';
|
ALTER TABLE points_flow ADD COLUMN IF NOT EXISTS related_type VARCHAR(50) COMMENT '关联业务类型';
|
ALTER TABLE points_flow ADD COLUMN IF NOT EXISTS balance_after INTEGER COMMENT '变动后余额';
|
ALTER TABLE points_flow ADD COLUMN IF NOT EXISTS operator_id BIGINT COMMENT '操作人ID';
|
ALTER TABLE points_flow ADD COLUMN IF NOT EXISTS operator_name VARCHAR(100) COMMENT '操作人姓名';
|
|
-- 12. 积分账户表 (points_account) - 更新现有表结构
|
ALTER TABLE user_points ADD COLUMN IF NOT EXISTS account_type VARCHAR(20) DEFAULT '个人积分' COMMENT '账户类型(个人积分/单位积分)';
|
ALTER TABLE user_points ADD COLUMN IF NOT EXISTS account_status VARCHAR(20) DEFAULT '正常' COMMENT '账户状态(正常/冻结/注销)';
|
ALTER TABLE user_points ADD COLUMN IF NOT EXISTS last_transaction_time TIMESTAMP COMMENT '最后交易时间';
|
|
-- 13. 产品表 (product) - 新增产品基础表
|
CREATE TABLE product (
|
id BIGSERIAL PRIMARY KEY,
|
product_name VARCHAR(100) NOT NULL COMMENT '产品名称',
|
product_code VARCHAR(50) NOT NULL UNIQUE COMMENT '产品编码',
|
product_type VARCHAR(50) COMMENT '产品类型',
|
category VARCHAR(100) COMMENT '产品分类',
|
description TEXT COMMENT '产品描述',
|
provider_id BIGINT NOT NULL COMMENT '提供者ID',
|
provider_name VARCHAR(100) NOT NULL COMMENT '提供者名称',
|
provider_type VARCHAR(20) COMMENT '提供者类型(企业/个人)',
|
status VARCHAR(20) DEFAULT '上架' CHECK (status IN ('上架', '下架', '审核中', '已下架')) COMMENT '产品状态',
|
audit_status VARCHAR(20) DEFAULT '待审核' CHECK (audit_status IN ('待审核', '审核通过', '审核驳回')) COMMENT '审核状态',
|
tags TEXT COMMENT '产品标签(JSON格式)',
|
cover_image VARCHAR(500) COMMENT '封面图片',
|
demo_url VARCHAR(500) COMMENT '演示地址',
|
doc_url VARCHAR(500) COMMENT '文档地址',
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
created_by BIGINT COMMENT '创建人ID',
|
updated_by BIGINT COMMENT '更新人ID',
|
deleted INTEGER DEFAULT 0 COMMENT '逻辑删除：1-已删除，0-未删除'
|
);
|
|
-- 14. 用户表 (user) - 新增用户基础表
|
CREATE TABLE user (
|
id BIGSERIAL PRIMARY KEY,
|
username VARCHAR(100) NOT NULL UNIQUE COMMENT '用户名',
|
real_name VARCHAR(100) COMMENT '真实姓名',
|
email VARCHAR(100) COMMENT '邮箱',
|
phone VARCHAR(20) COMMENT '手机号',
|
avatar VARCHAR(500) COMMENT '头像',
|
user_type VARCHAR(20) DEFAULT '个人用户' CHECK (user_type IN ('个人用户', '单位用户')) COMMENT '用户类型',
|
unit_id BIGINT COMMENT '单位ID',
|
unit_name VARCHAR(100) COMMENT '单位名称',
|
status VARCHAR(20) DEFAULT '正常' CHECK (status IN ('正常', '冻结', '注销')) COMMENT '用户状态',
|
last_login_time TIMESTAMP COMMENT '最后登录时间',
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
deleted INTEGER DEFAULT 0 COMMENT '逻辑删除：1-已删除，0-未删除'
|
);
|
|
-- 15. 单位表 (unit) - 新增单位基础表
|
CREATE TABLE unit (
|
id BIGSERIAL PRIMARY KEY,
|
unit_name VARCHAR(100) NOT NULL COMMENT '单位名称',
|
unit_code VARCHAR(50) UNIQUE COMMENT '单位编码',
|
unit_type VARCHAR(50) COMMENT '单位类型',
|
industry VARCHAR(100) COMMENT '所属行业',
|
address TEXT COMMENT '单位地址',
|
contact_person VARCHAR(100) COMMENT '联系人',
|
contact_phone VARCHAR(20) COMMENT '联系电话',
|
contact_email VARCHAR(100) COMMENT '联系邮箱',
|
business_license VARCHAR(500) COMMENT '营业执照',
|
legal_person VARCHAR(100) COMMENT '法人代表',
|
registered_capital NUMERIC(15,2) COMMENT '注册资本',
|
establishment_date DATE COMMENT '成立日期',
|
status VARCHAR(20) DEFAULT '正常' CHECK (status IN ('正常', '冻结', '注销')) COMMENT '单位状态',
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
deleted INTEGER DEFAULT 0 COMMENT '逻辑删除：1-已删除，0-未删除'
|
);
|
|
-- 创建索引
|
CREATE INDEX idx_product_pricing_product_id ON product_pricing(product_id);
|
CREATE INDEX idx_product_pricing_status ON product_pricing(is_active);
|
CREATE INDEX idx_shopping_cart_user_id ON shopping_cart(user_id);
|
CREATE INDEX idx_shopping_cart_product_id ON shopping_cart(product_id);
|
CREATE INDEX idx_order_info_user_id ON order_info(user_id);
|
CREATE INDEX idx_order_info_provider_id ON order_info(provider_id);
|
CREATE INDEX idx_order_info_status ON order_info(order_status);
|
CREATE INDEX idx_order_info_apply_time ON order_info(apply_time);
|
CREATE INDEX idx_order_detail_order_id ON order_detail(order_id);
|
CREATE INDEX idx_order_attachment_order_id ON order_attachment(order_id);
|
CREATE INDEX idx_order_approval_order_id ON order_approval(order_id);
|
CREATE INDEX idx_order_approval_approver_id ON order_approval(approver_id);
|
CREATE INDEX idx_order_evaluation_order_id ON order_evaluation(order_id);
|
CREATE INDEX idx_order_evaluation_evaluator_id ON order_evaluation(evaluator_id);
|
CREATE INDEX idx_points_rule_points_id ON points_rule(points_id);
|
CREATE INDEX idx_points_rule_detail_rule_id ON points_rule_detail(rule_id);
|
CREATE INDEX idx_product_provider_id ON product(provider_id);
|
CREATE INDEX idx_product_status ON product(status);
|
CREATE INDEX idx_user_unit_id ON user(unit_id);
|
CREATE INDEX idx_user_status ON user(status);
|
|
-- 购物车表索引
|
CREATE INDEX idx_cart_user_unit ON cart(user_id, unit_id, deleted);
|
CREATE INDEX idx_cart_pricing ON cart(pricing_id, deleted);
|
CREATE INDEX idx_cart_product ON cart(product_id, deleted);
|
CREATE INDEX idx_cart_provider ON cart(provider_id, deleted);
|
CREATE INDEX idx_cart_add_time ON cart(add_time, deleted);
|
|
-- 添加外键约束
|
ALTER TABLE product_pricing ADD CONSTRAINT fk_product_pricing_product_id FOREIGN KEY (product_id) REFERENCES product(id);
|
ALTER TABLE shopping_cart ADD CONSTRAINT fk_shopping_cart_user_id FOREIGN KEY (user_id) REFERENCES user(id);
|
ALTER TABLE shopping_cart ADD CONSTRAINT fk_shopping_cart_product_id FOREIGN KEY (product_id) REFERENCES product(id);
|
ALTER TABLE order_info ADD CONSTRAINT fk_order_info_product_id FOREIGN KEY (product_id) REFERENCES product(id);
|
ALTER TABLE order_info ADD CONSTRAINT fk_order_info_user_id FOREIGN KEY (user_id) REFERENCES user(id);
|
ALTER TABLE order_info ADD CONSTRAINT fk_order_info_provider_id FOREIGN KEY (provider_id) REFERENCES user(id);
|
ALTER TABLE order_detail ADD CONSTRAINT fk_order_detail_order_id FOREIGN KEY (order_id) REFERENCES order_info(order_id);
|
ALTER TABLE order_detail ADD CONSTRAINT fk_order_detail_pricing_id FOREIGN KEY (pricing_id) REFERENCES product_pricing(id);
|
ALTER TABLE order_attachment ADD CONSTRAINT fk_order_attachment_order_id FOREIGN KEY (order_id) REFERENCES order_info(order_id);
|
ALTER TABLE order_approval ADD CONSTRAINT fk_order_approval_order_id FOREIGN KEY (order_id) REFERENCES order_info(order_id);
|
ALTER TABLE order_evaluation ADD CONSTRAINT fk_order_evaluation_order_id FOREIGN KEY (order_id) REFERENCES order_info(order_id);
|
ALTER TABLE points_rule ADD CONSTRAINT fk_points_rule_points_id FOREIGN KEY (points_id) REFERENCES points(id);
|
ALTER TABLE points_rule_detail ADD CONSTRAINT fk_points_rule_detail_rule_id FOREIGN KEY (points_rule_id) REFERENCES points_rule(id);
|
ALTER TABLE points_rule_detail ADD CONSTRAINT fk_points_rule_detail_points_id FOREIGN KEY (points_id) REFERENCES points(id);
|
ALTER TABLE product ADD CONSTRAINT fk_product_provider_id FOREIGN KEY (provider_id) REFERENCES user(id);
|
ALTER TABLE user ADD CONSTRAINT fk_user_unit_id FOREIGN KEY (unit_id) REFERENCES unit(id);
|
|
-- 添加表注释
|
COMMENT ON TABLE product_pricing IS '产品定价表';
|
COMMENT ON TABLE shopping_cart IS '购物车表';
|
COMMENT ON TABLE order_info IS '订单表';
|
COMMENT ON TABLE order_detail IS '订单详情表';
|
COMMENT ON TABLE order_attachment IS '订单附件表';
|
COMMENT ON TABLE order_approval IS '订单审核表';
|
COMMENT ON TABLE order_evaluation IS '订单评价表';
|
COMMENT ON TABLE points IS '积分表';
|
COMMENT ON TABLE points_rule_detail IS '积分规则详情表';
|
COMMENT ON TABLE product IS '产品表';
|
COMMENT ON TABLE user IS '用户表';
|
COMMENT ON TABLE unit IS '单位表';
|
|
-- 插入初始数据
|
INSERT INTO points (points_name, points_code, points_type, effective_start, modifier_id, modifier_name, version, description, status) VALUES
|
('数字化标杆个人积分', 'BENCHMARK_PERSONAL', '个人积分', '2024-01-01', 1, '系统管理员', '1.0.0', '数字化标杆个人积分体系', '启用'),
|
('数字化标杆单位积分', 'BENCHMARK_UNIT', '单位积分', '2024-01-01', 1, '系统管理员', '1.0.0', '数字化标杆单位积分体系', '启用'),
|
('数字化知识个人积分', 'KNOWLEDGE_PERSONAL', '个人积分', '2024-01-01', 1, '系统管理员', '1.0.0', '数字化知识个人积分体系', '启用'),
|
('数字化知识单位积分', 'KNOWLEDGE_UNIT', '单位积分', '2024-01-01', 1, '系统管理员', '1.0.0', '数字化知识单位积分体系', '启用'),
|
('数字化产品个人积分', 'PRODUCT_PERSONAL', '个人积分', '2024-01-01', 1, '系统管理员', '1.0.0', '数字化产品个人积分体系', '启用'),
|
('数字化产品单位积分', 'PRODUCT_UNIT', '单位积分', '2024-01-01', 1, '系统管理员', '1.0.0', '数字化产品单位积分体系', '启用');
|
|
-- 插入测试单位数据
|
INSERT INTO unit (unit_name, unit_code, unit_type, industry, status) VALUES
|
('测试企业', 'TEST_ENTERPRISE', '企业', '信息技术', '正常'),
|
('测试单位', 'TEST_UNIT', '事业单位', '教育', '正常');
|
|
-- 插入测试用户数据
|
INSERT INTO user (username, real_name, user_type, unit_id, status) VALUES
|
('admin', '系统管理员', '个人用户', NULL, '正常'),
|
('test_user', '测试用户', '个人用户', 1, '正常'),
|
('test_provider', '测试提供者', '单位用户', 1, '正常');
|
|
-- 插入测试产品数据
|
INSERT INTO product (product_name, product_code, product_type, category, description, provider_id, provider_name, status, audit_status) VALUES
|
('测试产品1', 'TEST_PRODUCT_001', '软件产品', '企业管理', '这是一个测试产品', 3, '测试提供者', '上架', '审核通过'),
|
('测试产品2', 'TEST_PRODUCT_002', '服务产品', '技术服务', '这是另一个测试产品', 3, '测试提供者', '上架', '审核通过');
|
|
-- 插入测试产品定价数据
|
INSERT INTO product_pricing (suite_name, sales_form, customer_type, price_type, price_unit, price, product_id, product_name, provider_id, provider_name, is_active) VALUES
|
('基础版', '买断', '企业', '积分', '套', 1000, 1, '测试产品1', 3, '测试提供者', true),
|
('专业版', '租赁', '企业', '积分', '套/年', 500, 1, '测试产品1', 3, '测试提供者', true),
|
('企业版', '买断', '企业', '货币', '套', 10000, 2, '测试产品2', 3, '测试提供者', true);
|
