-- 交易模块数据库表创建脚本 (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);