zhijiayun-pharmacy/zhijiayun-gateway/src/test/resources/test-schema.sql

-- ============================================
-- 测试用建表脚本（每次测试前重建所有表）
-- ============================================

-- 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_operation_audit_log;
-- 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_search_record;
-- DROP TABLE IF EXISTS t_platform_session;
-- 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_admin;
-- 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,
    membership_expire_at TIMESTAMP DEFAULT NULL COMMENT '会员过期时间',
    inviter_id        BIGINT        DEFAULT NULL COMMENT '邀请人ID（不可变）',
    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,
    token_version   BIGINT        DEFAULT 0,
    refresh_token_hash VARCHAR(64)  DEFAULT NULL,
    refresh_token_status VARCHAR(10) 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_admin (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    username        VARCHAR(64)   NOT NULL,
    password        VARCHAR(128)  NOT NULL DEFAULT '',
    phone           VARCHAR(20)   DEFAULT NULL,
    role            VARCHAR(20)   NOT NULL DEFAULT 'ADMIN',
    status          INT           NOT NULL DEFAULT 1,
    token_version   BIGINT        DEFAULT 0,
    refresh_token_hash   VARCHAR(64)  DEFAULT NULL,
    refresh_token_status VARCHAR(10) DEFAULT NULL,
    last_login_time DATETIME      DEFAULT NULL,
    last_login_ip   VARCHAR(45)   DEFAULT NULL,
    create_time     DATETIME      DEFAULT CURRENT_TIMESTAMP,
    update_time     DATETIME      DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uk_username UNIQUE (username),
    CONSTRAINT uk_phone_admin 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 'SYSTEM',
    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,
    -- 文案模板配置
    share_title_template    VARCHAR(256)  DEFAULT '{inviter} 邀请你加入{appName}',
    copy_text_template      TEXT          DEFAULT NULL,
    reward_description      VARCHAR(256)  DEFAULT '邀请人每成功邀请1家药店注册，赠送1个月高级会员时长',
    summary_text_template   VARCHAR(256)  DEFAULT '已邀请{totalInvited}家药店，{registeredCount}家注册，累计获得{totalReward}个月高级会员',
    -- 各渠道操作提示
    instruction_windows     VARCHAR(256)  DEFAULT NULL,
    instruction_app         VARCHAR(256)  DEFAULT NULL,
    instruction_miniapp     VARCHAR(256)  DEFAULT NULL,
    instruction_wechat      VARCHAR(256)  DEFAULT NULL,
    instruction_dingtalk    VARCHAR(256)  DEFAULT NULL,
    instruction_feishu      VARCHAR(256)  DEFAULT NULL,
    instruction_default     VARCHAR(256)  DEFAULT NULL,
    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_search_record (
    id                BIGINT        NOT NULL COMMENT '主键ID（雪花算法）',
    user_id           BIGINT        NOT NULL COMMENT '用户ID',
    drug_name         VARCHAR(200)  NOT NULL COMMENT '药品名称',
    searched_at       TIMESTAMP     DEFAULT CURRENT_TIMESTAMP COMMENT '搜索时间',
    status_text       VARCHAR(500)  DEFAULT NULL COMMENT '状态文案',
    platform_results  LONGTEXT      DEFAULT NULL COMMENT '平台搜索结果（JSON文本）',
    search_log        LONGTEXT      DEFAULT NULL COMMENT '搜索日志（JSON文本）',
    platform_count    INT           NOT NULL DEFAULT 0 COMMENT '涉及平台数量',
    quote_count       INT           NOT NULL DEFAULT 0 COMMENT '总报价条数',
    create_time       TIMESTAMP     DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (id),
    INDEX idx_user_id (user_id),
    INDEX idx_user_searched (user_id, searched_at)
);

-- 平台会话快照表
CREATE TABLE IF NOT EXISTS t_platform_session (
    id                  BIGINT        NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    user_id             BIGINT        NOT NULL COMMENT '用户ID',
    platform_code       VARCHAR(50)   NOT NULL COMMENT '平台代码',
    platform_name       VARCHAR(100)  DEFAULT NULL COMMENT '平台展示名称',
    login_url           VARCHAR(500)  DEFAULT NULL COMMENT '平台登录页地址',
    account_label       VARCHAR(200)  DEFAULT NULL COMMENT '账号标签',
    captured_at         TIMESTAMP     DEFAULT NULL COMMENT 'Cookie捕获时间',
    updated_at          TIMESTAMP     DEFAULT NULL COMMENT '绑定元数据更新时间',
    cookies_encrypted   LONGTEXT      DEFAULT NULL COMMENT '加密后的Cookie JSON数组',
    version             INT           DEFAULT 1 COMMENT '数据版本号',
    expires_at          TIMESTAMP     DEFAULT NULL COMMENT '最早过期的Cookie时间',
    create_time         TIMESTAMP     DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time         TIMESTAMP     DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (id),
    UNIQUE KEY uk_user_platform (user_id, platform_code),
    INDEX idx_user_id (user_id),
    INDEX idx_expires_at (expires_at)
);

-- 邀请码表
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);

-- 初始化管理员账号（用于运营后台接口测试，已迁至 t_admin）
INSERT IGNORE INTO t_admin (id, username, password, phone, role, status)
VALUES
    (100, 'testadmin', '', '13900000000', 'ADMIN', 1);

-- 管理员也保留 t_user 记录（用于 ID 关联，但不设 role 为 ADMIN）
INSERT IGNORE INTO t_user (id, phone, nickname, status, membership_level)
VALUES
    (100, '13900000000', '测试管理员', 1, 'PLUS')
ON DUPLICATE KEY UPDATE
    phone = VALUES(phone),
    nickname = VALUES(nickname),
    status = VALUES(status),
    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, 0, 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)
);

-- 初始化会员权益配置
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_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,
    order_type           VARCHAR(20)   NOT NULL DEFAULT 'NORMAL',
    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)
);

-- 初始化支付方案（PLUS 免费开放，不提供付费套餐）
INSERT IGNORE INTO t_payment_plan (plan_code, plan_name, membership_level, price, original_price, duration_days, sort_order, status, description) VALUES
    ('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, '连锁专用，多店铺管理，敬请期待');