-- 交易模块数据库表创建脚本 (PostgreSQL 版本)
|
-- 创建时间: 2024-08-07
|
|
-- 创建顺序调整: 先创建基础表 (unit, users, product)，再创建依赖它们的表
|
|
-- 15. 单位表 (unit)
|
CREATE TABLE unit (
|
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
unit_name TEXT NOT NULL,
|
unit_code TEXT UNIQUE,
|
unit_type TEXT,
|
industry TEXT,
|
address TEXT,
|
contact_person TEXT,
|
contact_phone TEXT,
|
contact_email TEXT,
|
business_license TEXT,
|
legal_person TEXT,
|
registered_capital NUMERIC(15,2),
|
establishment_date DATE,
|
status TEXT DEFAULT '正常' CHECK (status IN ('正常', '冻结', '注销')),
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
deleted INTEGER DEFAULT 0 CHECK (deleted IN (0,1))
|
);
|
|
COMMENT ON TABLE unit IS '单位表';
|
|
-- 14. 用户表 (users) - 避免关键字冲突
|
CREATE TABLE users (
|
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
username TEXT NOT NULL UNIQUE,
|
real_name TEXT,
|
email TEXT,
|
phone TEXT,
|
avatar TEXT,
|
user_type TEXT DEFAULT '个人用户' CHECK (user_type IN ('个人用户', '单位用户')),
|
unit_id BIGINT REFERENCES unit(id),
|
unit_name TEXT,
|
status TEXT DEFAULT '正常' CHECK (status IN ('正常', '冻结', '注销')),
|
last_login_time TIMESTAMP,
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
deleted INTEGER DEFAULT 0 CHECK (deleted IN (0,1))
|
);
|
|
COMMENT ON TABLE users IS '用户表';
|
|
-- 13. 产品表 (product)
|
CREATE TABLE product (
|
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
product_name TEXT NOT NULL,
|
product_code TEXT NOT NULL UNIQUE,
|
product_type TEXT,
|
category TEXT,
|
description TEXT,
|
provider_id BIGINT NOT NULL REFERENCES users(id),
|
provider_name TEXT NOT NULL,
|
provider_type TEXT CHECK (provider_type IN ('企业', '个人')),
|
status TEXT DEFAULT '上架' CHECK (status IN ('上架', '下架', '审核中', '已下架')),
|
audit_status TEXT DEFAULT '待审核' CHECK (audit_status IN ('待审核', '审核通过', '审核驳回')),
|
tags TEXT,
|
cover_image TEXT,
|
demo_url TEXT,
|
doc_url TEXT,
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
created_by BIGINT REFERENCES users(id),
|
updated_by BIGINT REFERENCES users(id),
|
deleted INTEGER DEFAULT 0 CHECK (deleted IN (0,1))
|
);
|
|
COMMENT ON TABLE product IS '产品表';
|
|
-- 1. 产品定价表 (product_pricing)
|
CREATE TABLE product_pricing (
|
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
suite_name TEXT NOT NULL,
|
sales_form TEXT NOT NULL CHECK (sales_form IN ('买断', '租赁', '私有增包量', '公有增包量', 'OTA服务', '资源包', '个人')),
|
customer_type TEXT NOT NULL CHECK (customer_type IN ('企业', '个人', '项目部')),
|
account_limit TEXT DEFAULT '不限',
|
concurrent_nodes TEXT DEFAULT '不限',
|
price_type TEXT NOT NULL CHECK (price_type IN ('积分', '协议', '货币', '免费')),
|
price_unit TEXT NOT NULL CHECK (price_unit IN ('套', '套/年', '年', '个', '次')),
|
price NUMERIC(15,2) NOT NULL,
|
is_active BOOLEAN DEFAULT TRUE,
|
product_id BIGINT NOT NULL REFERENCES product(id),
|
product_name TEXT,
|
provider_id BIGINT REFERENCES users(id),
|
provider_name TEXT,
|
description TEXT,
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
created_by BIGINT REFERENCES users(id),
|
updated_by BIGINT REFERENCES users(id),
|
deleted INTEGER DEFAULT 0 CHECK (deleted IN (0,1))
|
);
|
|
COMMENT ON TABLE product_pricing IS '产品定价表';
|
|
-- 积分表 (points) - 需在积分规则前创建
|
-- 积分表 (points) - 主表
|
CREATE TABLE points (
|
id BIGSERIAL PRIMARY KEY,
|
points_name TEXT NOT NULL,
|
effective_start DATE NOT NULL,
|
modifier_id BIGINT NOT NULL,
|
modifier_name TEXT NOT NULL,
|
version TEXT NOT NULL,
|
description TEXT,
|
status TEXT DEFAULT '启用',
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
deleted SMALLINT DEFAULT 0
|
);
|
|
COMMENT ON TABLE points IS '积分主表';
|
COMMENT ON COLUMN points.points_name IS '积分名称';
|
COMMENT ON COLUMN points.effective_start IS '生效开始时间';
|
COMMENT ON COLUMN points.modifier_id IS '修改人ID';
|
COMMENT ON COLUMN points.modifier_name IS '修改人姓名';
|
COMMENT ON COLUMN points.version IS '版本号';
|
COMMENT ON COLUMN points.description IS '积分描述';
|
COMMENT ON COLUMN points.status IS '状态';
|
COMMENT ON COLUMN points.created_at IS '创建时间';
|
COMMENT ON COLUMN points.updated_at IS '更新时间';
|
COMMENT ON COLUMN points.deleted IS '逻辑删除';
|
ALTER TABLE points ADD CONSTRAINT chk_status CHECK (status IN ('启用', '禁用'));
|
ALTER TABLE points ADD CONSTRAINT chk_deleted CHECK (deleted IN (0,1));
|
|
-- 积分规则表 (points_rule)
|
CREATE TABLE points_rule (
|
id BIGSERIAL PRIMARY KEY,
|
points_id BIGINT NOT NULL,
|
rule_type TEXT NOT NULL,
|
category TEXT NOT NULL,
|
rule_name TEXT NOT NULL,
|
rule_description TEXT,
|
status TEXT DEFAULT '启用',
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
deleted SMALLINT DEFAULT 0
|
);
|
|
COMMENT ON TABLE points_rule IS '积分规则表';
|
COMMENT ON COLUMN points_rule.points_id IS '关联积分ID';
|
COMMENT ON COLUMN points_rule.rule_type IS '积分规则类型';
|
COMMENT ON COLUMN points_rule.category IS '积分类别';
|
COMMENT ON COLUMN points_rule.rule_name IS '规则名称';
|
COMMENT ON COLUMN points_rule.rule_description IS '规则描述';
|
COMMENT ON COLUMN points_rule.status IS '规则状态';
|
COMMENT ON COLUMN points_rule.created_at IS '创建时间';
|
COMMENT ON COLUMN points_rule.updated_at IS '更新时间';
|
COMMENT ON COLUMN points_rule.deleted IS '逻辑删除';
|
ALTER TABLE points_rule ADD CONSTRAINT fk_points FOREIGN KEY (points_id) REFERENCES points(id);
|
ALTER TABLE points_rule ADD CONSTRAINT chk_rule_type CHECK (rule_type IN ('获取', '消耗'));
|
ALTER TABLE points_rule ADD CONSTRAINT chk_category CHECK (category IN ('资源贡献', '资源传播', '资源交易', '交流社区互动'));
|
ALTER TABLE points_rule ADD CONSTRAINT chk_rule_status CHECK (status IN ('启用', '禁用'));
|
ALTER TABLE points_rule ADD CONSTRAINT chk_rule_deleted CHECK (deleted IN (0,1));
|
|
-- 积分规则详情表 (points_rule_detail)
|
CREATE TABLE points_rule_detail (
|
id BIGSERIAL PRIMARY KEY,
|
rule_id BIGINT NOT NULL,
|
points_id BIGINT NOT NULL,
|
points_value INTEGER NOT NULL,
|
daily_limit INTEGER,
|
monthly_limit INTEGER,
|
yearly_limit INTEGER,
|
min_value INTEGER DEFAULT 0,
|
max_value INTEGER,
|
conversion_rate NUMERIC(10,4) DEFAULT 1.0,
|
effective_start DATE,
|
effective_end DATE,
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
deleted SMALLINT DEFAULT 0
|
);
|
|
COMMENT ON TABLE points_rule_detail IS '积分规则详情表';
|
COMMENT ON COLUMN points_rule_detail.rule_id IS '关联积分规则ID';
|
COMMENT ON COLUMN points_rule_detail.points_id IS '关联积分ID';
|
COMMENT ON COLUMN points_rule_detail.points_value IS '积分值';
|
COMMENT ON COLUMN points_rule_detail.daily_limit IS '每日积分上限值';
|
COMMENT ON COLUMN points_rule_detail.monthly_limit IS '每月积分上限值';
|
COMMENT ON COLUMN points_rule_detail.yearly_limit IS '每年积分上限值';
|
COMMENT ON COLUMN points_rule_detail.min_value IS '最小值';
|
COMMENT ON COLUMN points_rule_detail.max_value IS '最大值';
|
COMMENT ON COLUMN points_rule_detail.conversion_rate IS '转换比率';
|
COMMENT ON COLUMN points_rule_detail.effective_start IS '生效开始时间';
|
COMMENT ON COLUMN points_rule_detail.effective_end IS '生效结束时间';
|
COMMENT ON COLUMN points_rule_detail.created_at IS '创建时间';
|
COMMENT ON COLUMN points_rule_detail.updated_at IS '更新时间';
|
COMMENT ON COLUMN points_rule_detail.deleted IS '逻辑删除';
|
ALTER TABLE points_rule_detail ADD CONSTRAINT fk_rule FOREIGN KEY (rule_id) REFERENCES points_rule(id);
|
ALTER TABLE points_rule_detail ADD CONSTRAINT fk_detail_points FOREIGN KEY (points_id) REFERENCES points(id);
|
ALTER TABLE points_rule_detail ADD CONSTRAINT chk_detail_deleted CHECK (deleted IN (0,1));
|
|
-- 积分流水表 (points_transaction)
|
CREATE TABLE points_transaction (
|
id BIGSERIAL PRIMARY KEY,
|
data_category TEXT NOT NULL,
|
transaction_name TEXT NOT NULL,
|
transaction_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
points_change INTEGER NOT NULL,
|
rule_type TEXT NOT NULL,
|
user_id BIGINT,
|
unit_id BIGINT,
|
user_type TEXT NOT NULL,
|
rule_id BIGINT,
|
detail_id BIGINT,
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
deleted SMALLINT DEFAULT 0
|
);
|
|
COMMENT ON TABLE points_transaction IS '积分流水表';
|
COMMENT ON COLUMN points_transaction.data_category IS '数据类目';
|
COMMENT ON COLUMN points_transaction.transaction_name IS '名称';
|
COMMENT ON COLUMN points_transaction.transaction_time IS '时间';
|
COMMENT ON COLUMN points_transaction.points_change IS '积分变动值';
|
COMMENT ON COLUMN points_transaction.rule_type IS '积分规则类型';
|
COMMENT ON COLUMN points_transaction.user_id IS '用户ID';
|
COMMENT ON COLUMN points_transaction.unit_id IS '企业ID';
|
COMMENT ON COLUMN points_transaction.user_type IS '用户类型';
|
COMMENT ON COLUMN points_transaction.rule_id IS '关联规则ID';
|
COMMENT ON COLUMN points_transaction.detail_id IS '关联规则详情ID';
|
COMMENT ON COLUMN points_transaction.created_at IS '创建时间';
|
COMMENT ON COLUMN points_transaction.deleted IS '逻辑删除';
|
ALTER TABLE points_transaction ADD CONSTRAINT chk_data_category CHECK (data_category IN ('用户参与', '其他'));
|
ALTER TABLE points_transaction ADD CONSTRAINT chk_trans_rule_type CHECK (rule_type IN ('获取', '消耗'));
|
ALTER TABLE points_transaction ADD CONSTRAINT chk_user_type CHECK (user_type IN ('单位用户', '个人用户'));
|
ALTER TABLE points_transaction ADD CONSTRAINT fk_rule_id FOREIGN KEY (rule_id) REFERENCES points_rule(id);
|
ALTER TABLE points_transaction ADD CONSTRAINT fk_detail_id FOREIGN KEY (detail_id) REFERENCES points_rule_detail(id);
|
ALTER TABLE points_transaction ADD CONSTRAINT chk_trans_deleted CHECK (deleted IN (0,1));
|
|
-- 积分账户表 (points_account)
|
CREATE TABLE points_account (
|
id BIGSERIAL PRIMARY KEY,
|
user_id BIGINT NOT NULL,
|
points_balance INTEGER NOT NULL DEFAULT 0,
|
total_earned INTEGER NOT NULL DEFAULT 0,
|
total_consumed INTEGER NOT NULL DEFAULT 0,
|
account_type TEXT NOT NULL,
|
account_status TEXT DEFAULT '正常',
|
last_transaction_time TIMESTAMPTZ,
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
deleted SMALLINT DEFAULT 0
|
);
|
|
COMMENT ON TABLE points_account IS '积分账户表';
|
COMMENT ON COLUMN points_account.user_id IS '用户ID';
|
COMMENT ON COLUMN points_account.points_balance IS '积分余额';
|
COMMENT ON COLUMN points_account.total_earned IS '累计获取积分';
|
COMMENT ON COLUMN points_account.total_consumed IS '累计消耗积分';
|
COMMENT ON COLUMN points_account.account_type IS '账户类型';
|
COMMENT ON COLUMN points_account.account_status IS '账户状态';
|
COMMENT ON COLUMN points_account.last_transaction_time IS '最后交易时间';
|
COMMENT ON COLUMN points_account.created_at IS '创建时间';
|
COMMENT ON COLUMN points_account.updated_at IS '更新时间';
|
COMMENT ON COLUMN points_account.deleted IS '逻辑删除';
|
ALTER TABLE points_account ADD CONSTRAINT chk_account_type CHECK (account_type IN ('个人积分', '单位积分'));
|
ALTER TABLE points_account ADD CONSTRAINT chk_account_status CHECK (account_status IN ('正常', '冻结', '注销'));
|
ALTER TABLE points_account ADD CONSTRAINT chk_account_deleted CHECK (deleted IN (0,1));
|
|
-- 创建索引
|
CREATE INDEX idx_points_rule_points ON points_rule(points_id);
|
CREATE INDEX idx_points_rule_type ON points_rule(rule_type);
|
CREATE INDEX idx_points_detail_rule ON points_rule_detail(rule_id);
|
CREATE INDEX idx_transaction_user ON points_transaction(user_id);
|
CREATE INDEX idx_transaction_time ON points_transaction(transaction_time);
|
CREATE INDEX idx_account_user ON points_account(user_id);
|
|
-- 2. 购物车表 (shopping_cart)
|
-- CREATE TABLE shopping_cart (
|
-- id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
-- user_id BIGINT NOT NULL REFERENCES users(id),
|
-- unit_id BIGINT REFERENCES unit(id),
|
-- suite_name TEXT NOT NULL,
|
-- sales_form TEXT NOT NULL CHECK (sales_form IN ('买断', '租赁', '私有增包量', '公有增包量', 'OTA服务', '资源包', '个人')),
|
-- customer_type TEXT NOT NULL CHECK (customer_type IN ('企业', '个人', '项目部')),
|
-- account_limit TEXT DEFAULT '不限',
|
-- concurrent_nodes TEXT DEFAULT '不限',
|
-- price_type TEXT NOT NULL CHECK (price_type IN ('积分', '协议', '货币', '免费')),
|
-- price_unit TEXT NOT NULL CHECK (price_unit IN ('套', '套/年', '年', '个', '次')),
|
-- unit_price NUMERIC(15,2) NOT NULL,
|
-- quantity INTEGER NOT NULL DEFAULT 1,
|
-- duration INTEGER,
|
-- product_id BIGINT NOT NULL REFERENCES product(id),
|
-- product_name TEXT,
|
-- provider_id BIGINT REFERENCES users(id),
|
-- provider_name TEXT,
|
-- total_amount NUMERIC(15,2),
|
-- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
-- updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
-- deleted INTEGER DEFAULT 0 CHECK (deleted IN (0,1))
|
-- );
|
--
|
-- COMMENT ON TABLE shopping_cart IS '购物车表';
|
|
-- 购物车表 (cart)
|
CREATE TABLE cart (
|
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
user_id BIGINT NOT NULL REFERENCES users(id),
|
unit_id BIGINT NOT NULL REFERENCES unit(id),
|
pricing_id BIGINT NOT NULL REFERENCES product_pricing(id),
|
product_id BIGINT NOT NULL REFERENCES product(id),
|
product_name TEXT,
|
suite_name TEXT,
|
sales_form TEXT,
|
customer_type TEXT,
|
account_limit TEXT,
|
concurrent_nodes TEXT,
|
price_type TEXT,
|
price_unit TEXT,
|
unit_price DECIMAL(15,2) NOT NULL,
|
quantity INTEGER NOT NULL DEFAULT 1,
|
duration INTEGER,
|
total_price DECIMAL(15,2),
|
provider_id BIGINT REFERENCES users(id),
|
provider_name TEXT,
|
remarks TEXT,
|
add_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
deleted INTEGER DEFAULT 0 CHECK (deleted IN (0,1)),
|
UNIQUE (user_id, unit_id, pricing_id, deleted)
|
);
|
|
COMMENT ON TABLE cart IS '购物车表';
|
|
-- 3. 订单表 (order_info)
|
CREATE TABLE order_info (
|
order_id TEXT PRIMARY KEY,
|
product_id BIGINT NOT NULL REFERENCES product(id),
|
user_id BIGINT NOT NULL REFERENCES users(id),
|
unit_id BIGINT REFERENCES unit(id),
|
product_name TEXT NOT NULL,
|
provider_name TEXT NOT NULL,
|
provider_id BIGINT NOT NULL REFERENCES users(id),
|
apply_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
order_status TEXT NOT NULL DEFAULT '待审批' CHECK (order_status IN ('待审批', '待审批授权', '待授权', '待上传文件', '待交易确认', '已完成', '已取消', '已驳回')),
|
total_amount NUMERIC(15,2) NOT NULL,
|
payment_type TEXT CHECK (payment_type IN ('积分', '货币', '协议')),
|
payment_status TEXT DEFAULT '未支付' CHECK (payment_status IN ('未支付', '已支付', '支付失败', '已退款')),
|
workflow_id TEXT,
|
current_step TEXT,
|
approval_flow TEXT,
|
buyer_remarks TEXT,
|
seller_remarks TEXT,
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
deleted INTEGER DEFAULT 0 CHECK (deleted IN (0,1))
|
);
|
|
COMMENT ON TABLE order_info IS '订单表';
|
|
-- 4. 订单详情表 (order_detail)
|
CREATE TABLE order_detail (
|
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
order_id TEXT NOT NULL REFERENCES order_info(order_id),
|
pricing_id BIGINT NOT NULL REFERENCES product_pricing(id),
|
product_id BIGINT NOT NULL REFERENCES product(id),
|
suite_name TEXT NOT NULL,
|
sales_form TEXT NOT NULL,
|
customer_type TEXT NOT NULL,
|
account_limit TEXT,
|
concurrent_nodes TEXT,
|
price_type TEXT NOT NULL,
|
price_unit TEXT NOT NULL,
|
unit_price NUMERIC(15,2) NOT NULL,
|
quantity INTEGER NOT NULL,
|
duration INTEGER,
|
total_price NUMERIC(15,2) NOT NULL,
|
provider_id BIGINT NOT NULL REFERENCES users(id),
|
provider_name TEXT,
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
remarks TEXT,
|
deleted INTEGER DEFAULT 0 CHECK (deleted IN (0,1))
|
);
|
|
COMMENT ON TABLE order_detail IS '订单详情表';
|
|
-- 5. 订单附件表 (order_attachment)
|
CREATE TABLE order_attachment (
|
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
order_id TEXT NOT NULL REFERENCES order_info(order_id),
|
file_name TEXT NOT NULL,
|
original_name TEXT,
|
file_type TEXT NOT NULL,
|
file_size BIGINT NOT NULL,
|
file_url TEXT NOT NULL,
|
file_path TEXT,
|
bucket_name TEXT,
|
object_name TEXT,
|
upload_user_id BIGINT REFERENCES users(id),
|
upload_user_name TEXT,
|
attachment_type TEXT CHECK (attachment_type IN ('合同', '发票', '其他')),
|
description TEXT,
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
deleted INTEGER DEFAULT 0 CHECK (deleted IN (0,1))
|
);
|
|
COMMENT ON TABLE order_attachment IS '订单附件表';
|
|
-- 6. 订单审核表 (order_approval)
|
CREATE TABLE order_approval (
|
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
order_id TEXT NOT NULL REFERENCES order_info(order_id),
|
approval_step TEXT NOT NULL,
|
approval_type TEXT NOT NULL CHECK (approval_type IN ('审批', '授权')),
|
approval_opinion TEXT,
|
approval_result TEXT NOT NULL CHECK (approval_result IN ('通过', '驳回', '待处理')),
|
approver_id BIGINT NOT NULL REFERENCES users(id),
|
approver_name TEXT NOT NULL,
|
approver_role TEXT,
|
approval_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
next_approver_id BIGINT REFERENCES users(id),
|
next_approver_name TEXT,
|
workflow_status TEXT,
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
deleted INTEGER DEFAULT 0 CHECK (deleted IN (0,1))
|
);
|
|
COMMENT ON TABLE order_approval IS '订单审核表';
|
|
-- 7. 订单评价表 (order_evaluation)
|
CREATE TABLE order_evaluation (
|
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
order_id TEXT NOT NULL REFERENCES order_info(order_id),
|
evaluator_id BIGINT NOT NULL REFERENCES users(id),
|
evaluator_name TEXT NOT NULL,
|
evaluator_type TEXT NOT NULL CHECK (evaluator_type IN ('买家', '卖家')),
|
content TEXT NOT NULL,
|
rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
|
service_rating INTEGER CHECK (service_rating >= 1 AND service_rating <= 5),
|
quality_rating INTEGER CHECK (quality_rating >= 1 AND quality_rating <= 5),
|
delivery_rating INTEGER CHECK (delivery_rating >= 1 AND delivery_rating <= 5),
|
is_anonymous BOOLEAN DEFAULT FALSE,
|
reply_content TEXT,
|
reply_user_id BIGINT REFERENCES users(id),
|
reply_time TIMESTAMP,
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
deleted INTEGER DEFAULT 0 CHECK (deleted IN (0,1))
|
);
|
|
COMMENT ON TABLE order_evaluation IS '订单评价表';
|
|
-- 创建索引 (PostgreSQL 语法)
|
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_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_users_unit_id ON users(unit_id);
|
CREATE INDEX idx_users_status ON users(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);
|
|
-- 添加列注释 (PostgreSQL 方式)
|
COMMENT ON COLUMN product_pricing.suite_name IS '产品套件名称';
|
COMMENT ON COLUMN product_pricing.sales_form IS '销售形式';
|
-- 其他列注释按相同格式补充...
|
|
|
INSERT INTO unit (unit_name, unit_code, unit_type, industry, status) VALUES
|
('测试企业', 'TEST_ENTERPRISE', '企业', '信息技术', '正常'),
|
('测试单位', 'TEST_UNIT', '事业单位', '教育', '正常');
|
|
INSERT INTO users (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);
|
