-- ============================================ -- 测试用建表脚本（每次测试前重建所有表） -- ============================================ DROP TABLE IF EXISTS t_payment_order; DROP TABLE IF EXISTS t_payment_plan; DROP TABLE IF EXISTS t_watchlist; DROP TABLE IF EXISTS t_search_history; DROP TABLE IF EXISTS t_operation_audit_log; DROP TABLE IF EXISTS t_trial_quota_config; DROP TABLE IF EXISTS t_business_license; DROP TABLE IF EXISTS t_platform_account; DROP TABLE IF EXISTS t_platform_config; DROP TABLE IF EXISTS t_crawler_quota_grant; DROP TABLE IF EXISTS t_crawler_usage_log; DROP TABLE IF EXISTS t_invite_relation; DROP TABLE IF EXISTS t_invite_code; DROP TABLE IF EXISTS t_invite_config; DROP TABLE IF EXISTS t_user_coupon; DROP TABLE IF EXISTS t_coupon; DROP TABLE IF EXISTS t_membership_benefit; DROP TABLE IF EXISTS t_user_membership; DROP TABLE IF EXISTS t_user; CREATE TABLE IF NOT EXISTS t_user ( id BIGINT AUTO_INCREMENT PRIMARY KEY, phone VARCHAR(20) DEFAULT NULL, nickname VARCHAR(64) DEFAULT NULL, avatar VARCHAR(512) DEFAULT NULL, password VARCHAR(128) DEFAULT NULL, wechat_open_id VARCHAR(64) DEFAULT NULL, wechat_union_id VARCHAR(64) DEFAULT NULL, wechat_nickname VARCHAR(128) DEFAULT NULL, pharmacy_name VARCHAR(200) DEFAULT NULL, province VARCHAR(50) DEFAULT NULL, city VARCHAR(50) DEFAULT NULL, district VARCHAR(50) DEFAULT NULL, status INT NOT NULL DEFAULT 1, role VARCHAR(20) NOT NULL DEFAULT 'USER', membership_level VARCHAR(20) DEFAULT NULL, last_login_time TIMESTAMP DEFAULT NULL, last_login_ip VARCHAR(45) DEFAULT NULL, last_login_device VARCHAR(100) DEFAULT NULL, login_source VARCHAR(20) DEFAULT NULL, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT uk_phone UNIQUE (phone) ); CREATE TABLE IF NOT EXISTS t_user_membership ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL, level VARCHAR(20) NOT NULL, source VARCHAR(20) NOT NULL DEFAULT 'ADMIN_GRANT', effective_from TIMESTAMP DEFAULT CURRENT_TIMESTAMP, effective_to TIMESTAMP DEFAULT NULL, status INT NOT NULL DEFAULT 1, remark VARCHAR(256) DEFAULT NULL, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_user_id (user_id), INDEX idx_level (level), INDEX idx_status (status) ); CREATE TABLE IF NOT EXISTS t_membership_benefit ( id BIGINT AUTO_INCREMENT PRIMARY KEY, level VARCHAR(20) NOT NULL, benefit_type VARCHAR(32) NOT NULL, benefit_value INT NOT NULL DEFAULT 0, description VARCHAR(256) DEFAULT NULL, status INT NOT NULL DEFAULT 1, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_level (level), INDEX idx_status (status) ); -- 会员权益表唯一索引（允许 INSERT ON DUPLICATE KEY UPDATE） -- 先清理重复数据，再创建唯一索引 DELETE t1 FROM t_membership_benefit t1 INNER JOIN t_membership_benefit t2 WHERE t1.id > t2.id AND t1.level = t2.level AND t1.benefit_type = t2.benefit_type; SET @idx_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 't_membership_benefit' AND INDEX_NAME = 'uk_level_type'); SET @sql = IF(@idx_exists = 0, 'ALTER TABLE t_membership_benefit ADD UNIQUE INDEX uk_level_type (level, benefit_type)', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; CREATE TABLE IF NOT EXISTS t_coupon ( id BIGINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(64) NOT NULL, type VARCHAR(16) NOT NULL, value INT NOT NULL, total_count INT NOT NULL DEFAULT (-1), issued_count INT NOT NULL DEFAULT 0, valid_type INT NOT NULL DEFAULT 1, valid_days INT DEFAULT NULL, valid_start TIMESTAMP DEFAULT NULL, valid_end TIMESTAMP DEFAULT NULL, min_level_code VARCHAR(16) DEFAULT NULL, status INT NOT NULL DEFAULT 1, description VARCHAR(256) DEFAULT NULL, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS t_user_coupon ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL, coupon_id BIGINT NOT NULL, status INT NOT NULL DEFAULT 0, used_time TIMESTAMP DEFAULT NULL, expire_time TIMESTAMP NOT NULL, source VARCHAR(32) NOT NULL DEFAULT 'ISSUE', create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS t_crawler_usage_log ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL, usage_date DATE NOT NULL, usage_count INT NOT NULL DEFAULT 1, platform VARCHAR(32) DEFAULT NULL, source VARCHAR(32) NOT NULL DEFAULT 'QUOTA', remark VARCHAR(128) DEFAULT NULL, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_user_date (user_id, usage_date), INDEX idx_user_id (user_id), INDEX idx_platform (platform) ); -- 邀请配置表 CREATE TABLE IF NOT EXISTS t_invite_config ( id BIGINT AUTO_INCREMENT PRIMARY KEY, reward_crawler_count INT NOT NULL DEFAULT 5, reward_type VARCHAR(16) NOT NULL DEFAULT 'MEMBERSHIP', reward_months INT NOT NULL DEFAULT 1, max_daily_reward INT NOT NULL DEFAULT 50, invite_code_expire_days INT NOT NULL DEFAULT 30, max_invite_per_day INT NOT NULL DEFAULT 10, max_total_invites INT NOT NULL DEFAULT 30, -- 多渠道配置 app_download_url VARCHAR(512) DEFAULT NULL, miniapp_path VARCHAR(256) DEFAULT NULL, miniapp_app_id VARCHAR(64) DEFAULT NULL, wechat_redirect_url VARCHAR(512) DEFAULT NULL, dingtalk_app_id VARCHAR(64) DEFAULT NULL, feishu_app_id VARCHAR(64) DEFAULT NULL, -- 落地页配置 landing_title VARCHAR(128) DEFAULT '邀请你加入智价云(药店端)', landing_desc VARCHAR(512) DEFAULT NULL, app_name VARCHAR(64) DEFAULT '智价云(药店端)', status INT NOT NULL DEFAULT 1, remark VARCHAR(256) DEFAULT NULL, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- 兼容升级：为已有表添加缺失列 SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 't_invite_config' AND COLUMN_NAME = 'reward_months'); SET @sql = IF(@col_exists = 0, 'ALTER TABLE t_invite_config ADD COLUMN reward_months INT NOT NULL DEFAULT 1 AFTER reward_type', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 't_invite_config' AND COLUMN_NAME = 'max_total_invites'); SET @sql = IF(@col_exists = 0, 'ALTER TABLE t_invite_config ADD COLUMN max_total_invites INT NOT NULL DEFAULT 30 AFTER max_invite_per_day', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- 邀请码表 CREATE TABLE IF NOT EXISTS t_invite_code ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL, code VARCHAR(16) NOT NULL, max_uses INT NOT NULL DEFAULT (-1), used_count INT NOT NULL DEFAULT 0, click_count INT NOT NULL DEFAULT 0, expire_time TIMESTAMP DEFAULT NULL, status INT NOT NULL DEFAULT 1, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT uk_code UNIQUE (code), INDEX idx_user_id (user_id) ); -- 邀请关系表 CREATE TABLE IF NOT EXISTS t_invite_relation ( id BIGINT AUTO_INCREMENT PRIMARY KEY, inviter_id BIGINT NOT NULL, invitee_id BIGINT NOT NULL, invite_code_id BIGINT NOT NULL, registered INT NOT NULL DEFAULT 0, reward_granted INT NOT NULL DEFAULT 0, status INT NOT NULL DEFAULT 1, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT uk_invitee UNIQUE (invitee_id) ); -- 爬虫额外配额发放表（邀请奖励 + 运营手动发放 + 购买会员奖励） CREATE TABLE IF NOT EXISTS t_crawler_quota_grant ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL, grant_type VARCHAR(16) NOT NULL, quota_count INT NOT NULL, used_count INT NOT NULL DEFAULT 0, expire_time TIMESTAMP DEFAULT NULL, source_user_id BIGINT DEFAULT NULL, remark VARCHAR(256) DEFAULT NULL, status INT NOT NULL DEFAULT 1, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_user_id (user_id), INDEX idx_grant_type (grant_type) ); -- 平台账号表 CREATE TABLE IF NOT EXISTS t_platform_account ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL, platform_code VARCHAR(50) NOT NULL, platform_name VARCHAR(100) NOT NULL, account_encrypted VARCHAR(512) NOT NULL, password_encrypted VARCHAR(512) NOT NULL, enabled TINYINT(1) NOT NULL DEFAULT 1, verify_status VARCHAR(16) NOT NULL DEFAULT 'PENDING', last_verify_time TIMESTAMP DEFAULT NULL, fail_reason VARCHAR(256) DEFAULT NULL, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_user_id (user_id), CONSTRAINT uk_user_platform UNIQUE (user_id, platform_code) ); -- 平台配置表 CREATE TABLE IF NOT EXISTS t_platform_config ( id BIGINT AUTO_INCREMENT PRIMARY KEY, platform_code VARCHAR(50) NOT NULL, platform_name VARCHAR(100) NOT NULL, enabled TINYINT(1) NOT NULL DEFAULT 1, requires_recharge TINYINT(1) NOT NULL DEFAULT 0, query_cost INT NOT NULL DEFAULT 1, priority INT NOT NULL DEFAULT 0, under_maintenance TINYINT(1) NOT NULL DEFAULT 0, maintenance_notice VARCHAR(500) DEFAULT NULL, description VARCHAR(500) DEFAULT NULL, official_url VARCHAR(500) DEFAULT NULL, binding_instructions TEXT DEFAULT NULL, sort_order INT DEFAULT 0, created_by BIGINT DEFAULT NULL, updated_by BIGINT DEFAULT NULL, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT uk_platform_code UNIQUE (platform_code), INDEX idx_enabled (enabled) ); -- 初始化平台配置数据 INSERT IGNORE INTO t_platform_config (platform_code, platform_name, enabled, query_cost, priority, binding_instructions, description, official_url, sort_order) VALUES ('yaoshibang', '药师帮', 1, 1, 1, '请输入您在药师帮的手机号和密码', '国内最大B2B医药批发平台', 'https://www.yaoshibang.com', 1), ('yaobangmang', '药帮忙', 1, 1, 2, '请输入您在药帮忙的手机号和密码', '专业医药B2B采购平台', 'https://www.yaobangmang.com', 2), ('yiyaocheng', '1药城', 1, 1, 3, '请输入您在1药城的手机号和密码', '1药网旗下B2B医药平台', 'https://www.1yaocheng.com', 3); -- 初始化测试用户（id=1，普通会员 PLUS） INSERT IGNORE INTO t_user (id, phone, nickname, status, membership_level) VALUES (1, '13800138000', '测试用户', 1, 'PLUS') ON DUPLICATE KEY UPDATE phone = VALUES(phone), nickname = VALUES(nickname), status = VALUES(status), membership_level = VALUES(membership_level); -- 搜索测试用户（id=2，高级会员 PRO，日配额20，避免配额耗尽影响其他测试） INSERT IGNORE INTO t_user (id, phone, nickname, status, membership_level) VALUES (2, '13900139000', '搜索测试', 1, 'PRO') ON DUPLICATE KEY UPDATE phone = VALUES(phone), nickname = VALUES(nickname), status = VALUES(status), membership_level = VALUES(membership_level); -- 初始化管理员账号（用于运营后台接口测试） INSERT IGNORE INTO t_user (id, phone, nickname, status, role, membership_level) VALUES (100, '13900000000', '测试管理员', 1, 'ADMIN', 'PLUS') ON DUPLICATE KEY UPDATE phone = VALUES(phone), nickname = VALUES(nickname), status = VALUES(status), role = VALUES(role), membership_level = VALUES(membership_level); -- 初始化邀请配置 INSERT IGNORE INTO t_invite_config (id, reward_type, reward_months, reward_crawler_count, max_daily_reward, invite_code_expire_days, max_invite_per_day, max_total_invites, landing_title, landing_desc, app_name, status) VALUES (1, 'MEMBERSHIP', 1, 5, 50, 30, 10, 30, '邀请你加入智价云(药店端)', '登录智价云(药店端)平台，邀请好友各得好礼', '智价云(药店端)', 1) ON DUPLICATE KEY UPDATE reward_type = VALUES(reward_type), reward_months = VALUES(reward_months), reward_crawler_count = VALUES(reward_crawler_count), max_daily_reward = VALUES(max_daily_reward), invite_code_expire_days = VALUES(invite_code_expire_days), max_invite_per_day = VALUES(max_invite_per_day), max_total_invites = VALUES(max_total_invites), landing_title = VALUES(landing_title), landing_desc = VALUES(landing_desc), app_name = VALUES(app_name), status = VALUES(status); -- 营业执照表 CREATE TABLE IF NOT EXISTS t_business_license ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL, license_image_url VARCHAR(500) DEFAULT NULL, credit_code VARCHAR(50) DEFAULT NULL, pharmacy_name VARCHAR(200) DEFAULT NULL, legal_person VARCHAR(50) DEFAULT NULL, registered_address VARCHAR(500) DEFAULT NULL, business_scope TEXT DEFAULT NULL, valid_from TIMESTAMP DEFAULT NULL, valid_to TIMESTAMP DEFAULT NULL, remark VARCHAR(500) DEFAULT NULL, review_status VARCHAR(20) NOT NULL DEFAULT 'PENDING', reviewer_id BIGINT DEFAULT NULL, review_time TIMESTAMP DEFAULT NULL, reject_reason VARCHAR(500) DEFAULT NULL, show_verified_badge TINYINT(1) NOT NULL DEFAULT 0, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_user_id (user_id), CONSTRAINT uk_user_id UNIQUE (user_id) ); -- 操作审计日志表 CREATE TABLE IF NOT EXISTS t_operation_audit_log ( id BIGINT AUTO_INCREMENT PRIMARY KEY, operator_id BIGINT DEFAULT NULL, operator_name VARCHAR(64) DEFAULT NULL, operator_role VARCHAR(32) DEFAULT NULL, operation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, ip_address VARCHAR(45) DEFAULT NULL, user_agent VARCHAR(512) DEFAULT NULL, module VARCHAR(32) DEFAULT NULL, operation_type VARCHAR(64) DEFAULT NULL, description VARCHAR(256) DEFAULT NULL, target_type VARCHAR(32) DEFAULT NULL, target_id BIGINT DEFAULT NULL, target_identifier VARCHAR(128) DEFAULT NULL, before_data TEXT DEFAULT NULL, after_data TEXT DEFAULT NULL, result VARCHAR(16) NOT NULL DEFAULT 'SUCCESS', error_message VARCHAR(512) DEFAULT NULL, request_url VARCHAR(512) DEFAULT NULL, request_method VARCHAR(8) DEFAULT NULL, request_params TEXT DEFAULT NULL, response_code INT DEFAULT NULL, duration BIGINT DEFAULT NULL, remark VARCHAR(256) DEFAULT NULL, is_sensitive TINYINT(1) NOT NULL DEFAULT 0, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_operator_id (operator_id), INDEX idx_operation_time (operation_time) ); -- 体验配额配置表 CREATE TABLE IF NOT EXISTS t_trial_quota_config ( id BIGINT AUTO_INCREMENT PRIMARY KEY, config_name VARCHAR(64) NOT NULL, trial_days INT NOT NULL DEFAULT 15, daily_query_limit INT NOT NULL DEFAULT 20, monthly_watchlist_limit INT NOT NULL DEFAULT 199, enabled TINYINT(1) NOT NULL DEFAULT 1, config_type VARCHAR(16) NOT NULL DEFAULT 'DEFAULT', applicable_user_type VARCHAR(16) NOT NULL DEFAULT 'NEW_USER', effective_from TIMESTAMP DEFAULT NULL, effective_to TIMESTAMP DEFAULT NULL, max_users INT DEFAULT NULL, current_users INT NOT NULL DEFAULT 0, description VARCHAR(256) DEFAULT NULL, remark VARCHAR(256) DEFAULT NULL, created_by BIGINT DEFAULT NULL, updated_by BIGINT DEFAULT NULL, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- 初始化会员权益配置 INSERT IGNORE INTO t_membership_benefit (level, benefit_type, benefit_value, description, status) VALUES ('PLUS', 'DAILY_QUOTA', 10, '普通会员：每日有效查询10次', 1), ('PLUS', 'MONTHLY_QUOTA', 300, '普通会员：每月查询300次', 1), ('PLUS', 'YEARLY_QUOTA', 3650, '普通会员：每年查询3650次（10*365）', 1), ('PRO', 'DAILY_QUOTA', 999, '高级会员：每日不限量', 1), ('PRO', 'MONTHLY_QUOTA', 29970, '高级会员：每月查询29970次（999*30）', 1), ('PRO', 'YEARLY_QUOTA', 364635, '高级会员：每年查询364635次（999*365）', 1), ('PRO', 'MAX_CONCURRENT', 5, '高级会员最大并发数', 1), ('ULTRA', 'DAILY_QUOTA', 999, '超级会员：每日不限量', 1), ('ULTRA', 'MONTHLY_QUOTA', 29970, '超级会员：每月查询29970次（999*30）', 1), ('ULTRA', 'YEARLY_QUOTA', 364635, '超级会员：每年查询364635次（999*365）', 1), ('ULTRA', 'MAX_CONCURRENT', 10, '超级会员最大并发数', 1); -- 搜索历史表 CREATE TABLE IF NOT EXISTS t_search_history ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL, keyword VARCHAR(200) NOT NULL, platform VARCHAR(32) DEFAULT NULL, result_count INT DEFAULT 0, search_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_user_id (user_id), INDEX idx_user_time (user_id, search_time) ); -- 关注/收藏表 CREATE TABLE IF NOT EXISTS t_watchlist ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL, drug_name VARCHAR(200) NOT NULL, spec VARCHAR(100) DEFAULT NULL, manufacturer VARCHAR(200) DEFAULT NULL, min_price DECIMAL(10,2) DEFAULT NULL, min_price_platform VARCHAR(32) DEFAULT NULL, last_price DECIMAL(10,2) DEFAULT NULL, price_change DECIMAL(10,2) DEFAULT NULL, last_check_time TIMESTAMP DEFAULT NULL, status INT NOT NULL DEFAULT 1, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_user_id (user_id) ); -- 支付方案表 CREATE TABLE IF NOT EXISTS t_payment_plan ( id BIGINT AUTO_INCREMENT PRIMARY KEY, plan_name VARCHAR(64) NOT NULL, plan_code VARCHAR(20) NOT NULL, membership_level VARCHAR(20) NOT NULL, price DECIMAL(10,2) NOT NULL, original_price DECIMAL(10,2) DEFAULT NULL, duration_days INT NOT NULL, sort_order INT DEFAULT 0, status INT NOT NULL DEFAULT 1, description VARCHAR(256) DEFAULT NULL, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT uk_plan_code UNIQUE (plan_code), INDEX idx_status (status) ); -- 支付订单表 CREATE TABLE IF NOT EXISTS t_payment_order ( id BIGINT AUTO_INCREMENT PRIMARY KEY, order_no VARCHAR(32) NOT NULL, user_id BIGINT NOT NULL, plan_id BIGINT NOT NULL, amount DECIMAL(10,2) NOT NULL, channel VARCHAR(20) NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'PENDING', qr_code_url VARCHAR(512) DEFAULT NULL, paid_channel VARCHAR(20) DEFAULT NULL, trade_no VARCHAR(64) DEFAULT NULL, paid_time TIMESTAMP DEFAULT NULL, expire_time TIMESTAMP NOT NULL, refund_time TIMESTAMP DEFAULT NULL, refund_amount DECIMAL(10,2) DEFAULT NULL, membership_record_id BIGINT DEFAULT NULL, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT uk_order_no UNIQUE (order_no), INDEX idx_user_id (user_id), INDEX idx_status (status), INDEX idx_expire_time (expire_time) ); -- 支付订单重复支付记录表 CREATE TABLE IF NOT EXISTS t_payment_order_extra_payment ( id BIGINT AUTO_INCREMENT PRIMARY KEY, order_no VARCHAR(32) NOT NULL, channel VARCHAR(20) NOT NULL, trade_no VARCHAR(64) NOT NULL, amount DECIMAL(10,2) NOT NULL, paid_time TIMESTAMP NOT NULL, refund_status VARCHAR(20) DEFAULT 'PENDING', refund_trade_no VARCHAR(64) DEFAULT NULL, refund_time TIMESTAMP DEFAULT NULL, refund_fail_reason VARCHAR(500) DEFAULT NULL, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_order_no (order_no) ); -- 初始化支付方案（含原价） INSERT IGNORE INTO t_payment_plan (plan_code, plan_name, membership_level, price, original_price, duration_days, sort_order, status, description) VALUES ('MONTHLY_PLUS', '普通会员·月卡', 'PLUS', 9.90, 19.90, 30, 1, 1, '每天有效查询10次 · 1个月9.9（原价19.9）'), ('YEARLY_PLUS', '普通会员·年卡', 'PLUS', 99.00, 199.90, 365, 2, 1, '每天有效查询10次 · 1年99（原价199.9）'), ('MONTHLY_PRO', '高级会员·月卡', 'PRO', 29.90, 59.90, 30, 3, 1, '每天不限量· 1个月29.9（原价59.9）'), ('YEARLY_PRO', '高级会员·年卡', 'PRO', 299.90,599.90, 365, 4, 1, '每天不限量· 1年299.9（原价599.9）'), ('ULTRA', '超级会员·连锁专享','ULTRA',0, NULL, 0, 5, 0, '连锁专用，多店铺管理，敬请期待');