-- World Cup activity tables for SQL Server.
-- Amount fields use integer cents (NumConfig::NUM_VALUE = 100).

IF OBJECT_ID('agent.dbo.world_cup_user_state', 'U') IS NULL
BEGIN
    -- 字段说明：
    -- user_id: QPAccountsDB.dbo.AccountsInfo.UserID，活动用户主键。
    -- first_bet_used: 是否已使用世界杯首注奖励，1=已使用，0=未使用。
    -- invite_code: 用户自己的邀请码，前端展示并用于手动补绑。
    -- referred_by_user_id: 邀请人的 UserID，未绑定时为空。
    -- referral_bind_at: 邀请关系绑定时间。
    -- referral_bind_type: 绑定方式，link=分享链接自动绑定，manual=24h 内手动输入邀请码。
    -- device_fp: 设备指纹，用于风控识别同设备自邀。
    -- pay_account_hash: 支付账号哈希，用于风控识别同支付账号。
    -- signup_ip: 注册 IP，用于风控识别同 IP/同段聚集。
    -- created_at: 记录创建时间。
    -- updated_at: 记录更新时间。
    CREATE TABLE agent.dbo.world_cup_user_state (
        user_id INT NOT NULL PRIMARY KEY,
        first_bet_used BIT NOT NULL DEFAULT 0,
        invite_code VARCHAR(32) NULL,
        referred_by_user_id INT NULL,
        referral_bind_at DATETIME NULL,
        referral_bind_type VARCHAR(16) NULL,
        device_fp VARCHAR(128) NULL,
        pay_account_hash VARCHAR(128) NULL,
        signup_ip VARCHAR(64) NULL,
        created_at DATETIME NOT NULL DEFAULT GETDATE(),
        updated_at DATETIME NOT NULL DEFAULT GETDATE()
    );

    CREATE UNIQUE INDEX idx_world_cup_user_state_invite_code
        ON agent.dbo.world_cup_user_state(invite_code)
        WHERE invite_code IS NOT NULL;
END;

IF OBJECT_ID('agent.dbo.world_cup_matches', 'U') IS NULL
BEGIN
    -- 字段说明：
    -- match_id: 比赛 ID，单场 1X2 盘口主键。
    -- match_no: FIFA 官方赛程 Match 编号。
    -- competition: 赛事名称，例如 World Cup。
    -- stage: 赛事阶段，group=小组赛，round_32=32 强，round_16=16 强，quarter_final=1/4 决赛，semi_final=半决赛，final=决赛。
    -- group_name: 小组名称，小组赛为 A-L，淘汰赛可为空。
    -- home_team: 主队名称。
    -- away_team: 客队名称。
    -- venue: 比赛场馆名称。
    -- kickoff_at: 开赛时间，UTC 时间戳；开赛前 1 小时停止投注。
    -- status: 比赛状态，scheduled=可维护/待开赛，closed=关闭投注，finished=已完赛。
    -- result: 比赛赛果，home/draw/away；未结算时为空。
    -- created_at: 记录创建时间。
    -- updated_at: 记录更新时间。
    CREATE TABLE agent.dbo.world_cup_matches (
        match_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        match_no INT NULL,
        competition VARCHAR(64) NOT NULL,
        stage VARCHAR(32) NOT NULL DEFAULT 'group',
        group_name VARCHAR(8) NULL,
        home_team VARCHAR(64) NOT NULL,
        away_team VARCHAR(64) NOT NULL,
        venue VARCHAR(128) NULL,
        kickoff_at DATETIME NOT NULL,
        status VARCHAR(16) NOT NULL DEFAULT 'scheduled',
        result VARCHAR(16) NULL,
        created_at DATETIME NOT NULL DEFAULT GETDATE(),
        updated_at DATETIME NOT NULL DEFAULT GETDATE()
    );

    CREATE INDEX idx_world_cup_matches_open
        ON agent.dbo.world_cup_matches(status, kickoff_at);
END;

IF OBJECT_ID('agent.dbo.world_cup_matches', 'U') IS NOT NULL
BEGIN
    IF COL_LENGTH('agent.dbo.world_cup_matches', 'match_no') IS NULL
        ALTER TABLE agent.dbo.world_cup_matches ADD match_no INT NULL;

    IF COL_LENGTH('agent.dbo.world_cup_matches', 'stage') IS NULL
        ALTER TABLE agent.dbo.world_cup_matches ADD stage VARCHAR(32) NOT NULL DEFAULT 'group';

    IF COL_LENGTH('agent.dbo.world_cup_matches', 'group_name') IS NULL
        ALTER TABLE agent.dbo.world_cup_matches ADD group_name VARCHAR(8) NULL;

    IF COL_LENGTH('agent.dbo.world_cup_matches', 'venue') IS NULL
        ALTER TABLE agent.dbo.world_cup_matches ADD venue VARCHAR(128) NULL;
END;
GO

IF OBJECT_ID('agent.dbo.world_cup_odds', 'U') IS NULL
BEGIN
    -- 字段说明：
    -- odds_id: 赔率记录 ID。
    -- match_id: 比赛 ID；Winner 夺冠盘为空，1X2 单场盘必填。
    -- market: 盘口类型，winner=夺冠盘，1x2=单场胜平负。
    -- selection: 投注选项；Winner 为球队名，1X2 为 home/draw/away。
    -- decimal_odds: 当前十进制赔率。
    -- previous_odds: 上一次赔率，用于前端展示涨跌箭头。
    -- is_active: 是否展示/可投注，1=启用，0=隐藏。
    -- locked_weight: 后台排序权重，数值越大越靠前。
    -- updated_at: 赔率更新时间。
    CREATE TABLE agent.dbo.world_cup_odds (
        odds_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        match_id INT NULL,
        market VARCHAR(16) NOT NULL,
        selection VARCHAR(64) NOT NULL,
        decimal_odds DECIMAL(10, 2) NOT NULL,
        previous_odds DECIMAL(10, 2) NULL,
        is_active BIT NOT NULL DEFAULT 1,
        locked_weight INT NOT NULL DEFAULT 0,
        updated_at DATETIME NOT NULL DEFAULT GETDATE()
    );

    CREATE INDEX idx_world_cup_odds_market
        ON agent.dbo.world_cup_odds(market, match_id, is_active);
END;

IF OBJECT_ID('agent.dbo.world_cup_match_favorites', 'U') IS NULL
BEGIN
    -- 字段说明：
    -- id: 收藏记录 ID。
    -- user_id: 收藏用户 UserID。
    -- match_id: 被收藏的单场比赛 ID，仅支持 1X2 比赛，不支持 Winner 盘。
    -- created_at: 收藏创建时间。
    CREATE TABLE agent.dbo.world_cup_match_favorites (
        id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        user_id INT NOT NULL,
        match_id INT NOT NULL,
        created_at DATETIME NOT NULL DEFAULT GETDATE()
    );

    CREATE UNIQUE INDEX idx_world_cup_match_favorites_user_match
        ON agent.dbo.world_cup_match_favorites(user_id, match_id);
END;

IF OBJECT_ID('agent.dbo.world_cup_bets', 'U') IS NULL
BEGIN
    -- 字段说明：
    -- bet_id: 世界杯注单 ID。
    -- user_id: 下注用户 UserID。
    -- game_id: 前端展示的 8 位 GameID，冗余保存便于查询。
    -- idempotency_key: 客户端幂等键，同一用户同一 key 不重复下单。
    -- market: 盘口类型，winner=夺冠盘，1x2=单场胜平负。
    -- match_id: 比赛 ID；Winner 夺冠盘为空。
    -- selection: 投注选项，球队名或 home/draw/away。
    -- stake: 下注额，整数分。
    -- odds: 下单时锁定的十进制赔率。
    -- is_first_bet: 是否为用户世界杯首注，1=首注。
    -- bonus_amount: 首注 +50% 额外奖励金额，整数分。
    -- potential_payout: 潜在派彩金额，整数分，stake * odds + bonus_amount。
    -- status: 注单状态，pending=待结算，won=赢，lost=输。
    -- settled_at: 结算时间。
    -- created_at: 下单时间。
    -- updated_at: 注单更新时间。
    CREATE TABLE agent.dbo.world_cup_bets (
        bet_id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        user_id INT NOT NULL,
        game_id INT NOT NULL,
        idempotency_key VARCHAR(64) NOT NULL,
        market VARCHAR(16) NOT NULL,
        match_id INT NULL,
        selection VARCHAR(64) NOT NULL,
        stake INT NOT NULL,
        odds DECIMAL(10, 2) NOT NULL,
        is_first_bet BIT NOT NULL DEFAULT 0,
        bonus_amount INT NOT NULL DEFAULT 0,
        potential_payout INT NOT NULL DEFAULT 0,
        status VARCHAR(16) NOT NULL DEFAULT 'pending',
        settled_at DATETIME NULL,
        created_at DATETIME NOT NULL DEFAULT GETDATE(),
        updated_at DATETIME NOT NULL DEFAULT GETDATE()
    );

    CREATE UNIQUE INDEX idx_world_cup_bets_user_idempotency
        ON agent.dbo.world_cup_bets(user_id, idempotency_key);

    CREATE INDEX idx_world_cup_bets_match_status
        ON agent.dbo.world_cup_bets(match_id, status);
END;

IF OBJECT_ID('agent.dbo.world_cup_referrals', 'U') IS NULL
BEGIN
    -- 字段说明：
    -- id: 邀请绑定记录 ID。
    -- referrer_id: 邀请人 UserID。
    -- invitee_id: 被邀请人 UserID；唯一，一个被邀请人只能绑定一次。
    -- bind_type: 绑定方式，link=分享链接自动绑定，manual=手动补填邀请码。
    -- bind_at: 绑定发生时间。
    -- created_at: 记录创建时间。
    CREATE TABLE agent.dbo.world_cup_referrals (
        id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        referrer_id INT NOT NULL,
        invitee_id INT NOT NULL,
        bind_type VARCHAR(16) NOT NULL,
        bind_at DATETIME NOT NULL DEFAULT GETDATE(),
        created_at DATETIME NOT NULL DEFAULT GETDATE()
    );

    CREATE UNIQUE INDEX idx_world_cup_referrals_invitee
        ON agent.dbo.world_cup_referrals(invitee_id);
END;

IF OBJECT_ID('agent.dbo.world_cup_referral_rewards', 'U') IS NULL
BEGIN
    -- 字段说明：
    -- reward_id: 邀请奖励单 ID，后台审核对象。
    -- referrer_id: 邀请人 UserID。
    -- invitee_id: 被邀请人 UserID；唯一，同一被邀请人首充只生成一次奖励单。
    -- first_deposit_order_sn: 触发奖励的首充订单号，用于事件监听幂等。
    -- first_deposit_amt: 触发奖励时的首充实际入账金额，整数分。
    -- reward_each: 双方各得奖励金额，整数分；规则为 min(first_deposit_amt * 50%, 10000)。
    -- total_liability: 本奖励单总赔付，整数分，reward_each * 2，最高 20000。
    -- risk_score: 风控评分，0-100。
    -- risk_level: 风险等级，low/medium/high。
    -- signals: 命中的风控信号 JSON 文本。
    -- status: 审核状态，reviewing/approved/rejected/on_hold/clawed_back。
    -- reason_code: 驳回或追回原因代码。
    -- review_by: 审核员标识。
    -- reviewed_at: 审核时间。
    -- submitted_at: 奖励单提交审核时间，用于计算 24h SLA。
    -- created_at: 记录创建时间。
    -- updated_at: 记录更新时间。
    CREATE TABLE agent.dbo.world_cup_referral_rewards (
        reward_id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        referrer_id INT NOT NULL,
        invitee_id INT NOT NULL,
        first_deposit_order_sn VARCHAR(64) NULL,
        first_deposit_amt INT NOT NULL,
        reward_each INT NOT NULL,
        total_liability INT NOT NULL,
        risk_score INT NOT NULL DEFAULT 0,
        risk_level VARCHAR(16) NOT NULL DEFAULT 'low',
        signals NVARCHAR(MAX) NULL,
        status VARCHAR(16) NOT NULL DEFAULT 'reviewing',
        reason_code VARCHAR(32) NULL,
        review_by VARCHAR(64) NULL,
        reviewed_at DATETIME NULL,
        submitted_at DATETIME NOT NULL DEFAULT GETDATE(),
        created_at DATETIME NOT NULL DEFAULT GETDATE(),
        updated_at DATETIME NOT NULL DEFAULT GETDATE()
    );

    CREATE UNIQUE INDEX idx_world_cup_referral_rewards_invitee
        ON agent.dbo.world_cup_referral_rewards(invitee_id);

    CREATE INDEX idx_world_cup_referral_rewards_queue
        ON agent.dbo.world_cup_referral_rewards(status, risk_level, submitted_at);
END;

IF OBJECT_ID('agent.dbo.world_cup_referral_rewards', 'U') IS NOT NULL
BEGIN
    IF COL_LENGTH('agent.dbo.world_cup_referral_rewards', 'first_deposit_order_sn') IS NULL
        ALTER TABLE agent.dbo.world_cup_referral_rewards ADD first_deposit_order_sn VARCHAR(64) NULL;
END;
GO

IF OBJECT_ID('agent.dbo.world_cup_referral_rewards', 'U') IS NOT NULL
    AND NOT EXISTS (
        SELECT 1
        FROM sys.indexes
        WHERE name = 'idx_world_cup_referral_rewards_order_sn'
            AND object_id = OBJECT_ID('agent.dbo.world_cup_referral_rewards')
    )
BEGIN
    CREATE UNIQUE INDEX idx_world_cup_referral_rewards_order_sn
        ON agent.dbo.world_cup_referral_rewards(first_deposit_order_sn)
        WHERE first_deposit_order_sn IS NOT NULL;
END;

IF OBJECT_ID('agent.dbo.world_cup_odds', 'U') IS NOT NULL
    AND NOT EXISTS (
        SELECT 1
        FROM sys.indexes
        WHERE name = 'idx_world_cup_odds_unique_market'
            AND object_id = OBJECT_ID('agent.dbo.world_cup_odds')
    )
BEGIN
    CREATE UNIQUE INDEX idx_world_cup_odds_unique_market
        ON agent.dbo.world_cup_odds(market, match_id, selection);
END;

IF OBJECT_ID('agent.dbo.world_cup_risk_signals', 'U') IS NULL
BEGIN
    -- 字段说明：
    -- code: 风控信号代码。
    -- label: 后台展示名称。
    -- weight: 风控权重，命中后累加到 risk_score。
    -- is_active: 是否启用，1=启用，0=停用。
    -- updated_at: 配置更新时间。
    CREATE TABLE agent.dbo.world_cup_risk_signals (
        code VARCHAR(32) NOT NULL PRIMARY KEY,
        label VARCHAR(128) NOT NULL,
        weight INT NOT NULL,
        is_active BIT NOT NULL DEFAULT 1,
        updated_at DATETIME NOT NULL DEFAULT GETDATE()
    );

    INSERT INTO agent.dbo.world_cup_risk_signals (code, label, weight)
    VALUES
        ('same_device', 'Same device', 40),
        ('same_payment', 'Same payment account', 40),
        ('same_ip', 'Same IP/subnet', 20),
        ('reg_velocity', 'Registration velocity', 25),
        ('fast_deposit', 'Fast first deposit', 15),
        ('deposit_reversed', 'Deposit reversed', 50),
        ('multi_account_ring', 'Multi-account ring', 40),
        ('threshold_clustering', 'Threshold clustering', 20);
END;

IF OBJECT_ID('agent.dbo.world_cup_audit_log', 'U') IS NULL
BEGIN
    -- 字段说明：
    -- id: 审计日志 ID。
    -- reward_id: 关联的邀请奖励单 ID，可为空。
    -- actor: 操作人，后台审核员或系统任务。
    -- action: 操作动作，例如 approve/reject/hold/clawback/auto_score。
    -- reason_code: 操作原因代码。
    -- before_status: 操作前状态。
    -- after_status: 操作后状态。
    -- payload: 操作上下文 JSON 文本。
    -- created_at: 日志创建时间。
    CREATE TABLE agent.dbo.world_cup_audit_log (
        id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        reward_id BIGINT NULL,
        actor VARCHAR(64) NOT NULL,
        action VARCHAR(32) NOT NULL,
        reason_code VARCHAR(32) NULL,
        before_status VARCHAR(16) NULL,
        after_status VARCHAR(16) NULL,
        payload NVARCHAR(MAX) NULL,
        created_at DATETIME NOT NULL DEFAULT GETDATE()
    );

    CREATE INDEX idx_world_cup_audit_log_reward
        ON agent.dbo.world_cup_audit_log(reward_id, created_at);
END;

IF OBJECT_ID('agent.dbo.world_cup_matches', 'U') IS NOT NULL
BEGIN
    SET IDENTITY_INSERT agent.dbo.world_cup_matches ON;

    MERGE agent.dbo.world_cup_matches AS target
    USING (VALUES
        (1, 1, 'World Cup', 'group', 'A', 'Mexico', 'South Africa', 'Mexico City', '2026-06-11 19:00:00', 'scheduled'),
        (2, 2, 'World Cup', 'group', 'A', 'South Korea', 'Czechia', 'Guadalajara', '2026-06-12 02:00:00', 'scheduled'),
        (3, 3, 'World Cup', 'group', 'B', 'Canada', 'Bosnia & Herzegovina', 'Toronto', '2026-06-12 19:00:00', 'scheduled'),
        (4, 4, 'World Cup', 'group', 'B', 'Qatar', 'Switzerland', 'Santa Clara', '2026-06-13 19:00:00', 'scheduled'),
        (5, 5, 'World Cup', 'group', 'C', 'Brazil', 'Morocco', 'East Rutherford', '2026-06-13 22:00:00', 'scheduled'),
        (6, 6, 'World Cup', 'group', 'C', 'Haiti', 'Scotland', 'Foxborough', '2026-06-14 01:00:00', 'scheduled'),
        (7, 7, 'World Cup', 'group', 'D', 'United States', 'Paraguay', 'Inglewood', '2026-06-13 01:00:00', 'scheduled'),
        (8, 8, 'World Cup', 'group', 'D', 'Australia', 'Turkiye', 'Vancouver', '2026-06-14 16:00:00', 'scheduled'),
        (9, 9, 'World Cup', 'group', 'E', 'Germany', 'Curacao', 'Houston', '2026-06-14 17:00:00', 'scheduled'),
        (10, 10, 'World Cup', 'group', 'E', 'Ivory Coast', 'Ecuador', 'Philadelphia', '2026-06-14 23:00:00', 'scheduled'),
        (11, 11, 'World Cup', 'group', 'F', 'Netherlands', 'Japan', 'Arlington', '2026-06-14 20:00:00', 'scheduled'),
        (12, 12, 'World Cup', 'group', 'F', 'Sweden', 'Tunisia', 'Monterrey', '2026-06-15 02:00:00', 'scheduled'),
        (13, 13, 'World Cup', 'group', 'G', 'Belgium', 'Egypt', 'Seattle', '2026-06-15 19:00:00', 'scheduled'),
        (14, 14, 'World Cup', 'group', 'G', 'Iran', 'New Zealand', 'Inglewood', '2026-06-16 01:00:00', 'scheduled'),
        (15, 15, 'World Cup', 'group', 'H', 'Spain', 'Cape Verde', 'Atlanta', '2026-06-15 16:00:00', 'scheduled'),
        (16, 16, 'World Cup', 'group', 'H', 'Saudi Arabia', 'Uruguay', 'Miami Gardens', '2026-06-15 22:00:00', 'scheduled'),
        (17, 17, 'World Cup', 'group', 'I', 'France', 'Senegal', 'East Rutherford', '2026-06-16 19:00:00', 'scheduled'),
        (18, 18, 'World Cup', 'group', 'I', 'Iraq', 'Norway', 'Foxborough', '2026-06-16 22:00:00', 'scheduled'),
        (19, 19, 'World Cup', 'group', 'J', 'Argentina', 'Algeria', 'Kansas City', '2026-06-17 01:00:00', 'scheduled'),
        (20, 20, 'World Cup', 'group', 'J', 'Austria', 'Jordan', 'Santa Clara', '2026-06-17 04:00:00', 'scheduled'),
        (21, 21, 'World Cup', 'group', 'K', 'Portugal', 'DR Congo', 'Houston', '2026-06-17 17:00:00', 'scheduled'),
        (22, 22, 'World Cup', 'group', 'K', 'Uzbekistan', 'Colombia', 'Mexico City', '2026-06-18 02:00:00', 'scheduled'),
        (23, 23, 'World Cup', 'group', 'L', 'England', 'Croatia', 'Arlington', '2026-06-17 20:00:00', 'scheduled'),
        (24, 24, 'World Cup', 'group', 'L', 'Ghana', 'Panama', 'Toronto', '2026-06-17 23:00:00', 'scheduled'),
        (25, 25, 'World Cup', 'group', 'A', 'Czechia', 'South Africa', 'Atlanta', '2026-06-18 16:00:00', 'scheduled'),
        (26, 26, 'World Cup', 'group', 'A', 'Mexico', 'South Korea', 'Guadalajara', '2026-06-19 01:00:00', 'scheduled'),
        (27, 27, 'World Cup', 'group', 'B', 'Switzerland', 'Bosnia & Herzegovina', 'Inglewood', '2026-06-18 19:00:00', 'scheduled'),
        (28, 28, 'World Cup', 'group', 'B', 'Canada', 'Qatar', 'Vancouver', '2026-06-18 22:00:00', 'scheduled'),
        (29, 29, 'World Cup', 'group', 'C', 'Scotland', 'Morocco', 'Foxborough', '2026-06-19 22:00:00', 'scheduled'),
        (30, 30, 'World Cup', 'group', 'C', 'Brazil', 'Haiti', 'Philadelphia', '2026-06-20 00:30:00', 'scheduled'),
        (31, 31, 'World Cup', 'group', 'D', 'United States', 'Australia', 'Seattle', '2026-06-19 19:00:00', 'scheduled'),
        (32, 32, 'World Cup', 'group', 'D', 'Turkiye', 'Paraguay', 'Santa Clara', '2026-06-20 03:00:00', 'scheduled'),
        (33, 33, 'World Cup', 'group', 'E', 'Germany', 'Ivory Coast', 'Toronto', '2026-06-20 20:00:00', 'scheduled'),
        (34, 34, 'World Cup', 'group', 'E', 'Ecuador', 'Curacao', 'Kansas City', '2026-06-21 00:00:00', 'scheduled'),
        (35, 35, 'World Cup', 'group', 'F', 'Netherlands', 'Sweden', 'Houston', '2026-06-20 17:00:00', 'scheduled'),
        (36, 36, 'World Cup', 'group', 'F', 'Tunisia', 'Japan', 'Monterrey', '2026-06-21 04:00:00', 'scheduled'),
        (37, 37, 'World Cup', 'group', 'G', 'Belgium', 'Iran', 'Inglewood', '2026-06-21 19:00:00', 'scheduled'),
        (38, 38, 'World Cup', 'group', 'G', 'New Zealand', 'Egypt', 'Vancouver', '2026-06-22 01:00:00', 'scheduled'),
        (39, 39, 'World Cup', 'group', 'H', 'Spain', 'Saudi Arabia', 'Atlanta', '2026-06-21 16:00:00', 'scheduled'),
        (40, 40, 'World Cup', 'group', 'H', 'Uruguay', 'Cape Verde', 'Miami Gardens', '2026-06-21 22:00:00', 'scheduled'),
        (41, 41, 'World Cup', 'group', 'I', 'France', 'Iraq', 'Philadelphia', '2026-06-22 21:00:00', 'scheduled'),
        (42, 42, 'World Cup', 'group', 'I', 'Norway', 'Senegal', 'East Rutherford', '2026-06-23 00:00:00', 'scheduled'),
        (43, 43, 'World Cup', 'group', 'J', 'Argentina', 'Austria', 'Arlington', '2026-06-22 17:00:00', 'scheduled'),
        (44, 44, 'World Cup', 'group', 'J', 'Jordan', 'Algeria', 'Santa Clara', '2026-06-23 03:00:00', 'scheduled'),
        (45, 45, 'World Cup', 'group', 'K', 'Portugal', 'Uzbekistan', 'Houston', '2026-06-23 17:00:00', 'scheduled'),
        (46, 46, 'World Cup', 'group', 'K', 'Colombia', 'DR Congo', 'Guadalajara', '2026-06-24 02:00:00', 'scheduled'),
        (47, 47, 'World Cup', 'group', 'L', 'England', 'Ghana', 'Foxborough', '2026-06-23 20:00:00', 'scheduled'),
        (48, 48, 'World Cup', 'group', 'L', 'Panama', 'Croatia', 'Toronto', '2026-06-23 23:00:00', 'scheduled'),
        (49, 49, 'World Cup', 'group', 'A', 'Czechia', 'Mexico', 'Mexico City', '2026-06-25 01:00:00', 'scheduled'),
        (50, 50, 'World Cup', 'group', 'A', 'South Africa', 'South Korea', 'Monterrey', '2026-06-25 01:00:00', 'scheduled'),
        (51, 51, 'World Cup', 'group', 'B', 'Switzerland', 'Canada', 'Vancouver', '2026-06-24 19:00:00', 'scheduled'),
        (52, 52, 'World Cup', 'group', 'B', 'Bosnia & Herzegovina', 'Qatar', 'Seattle', '2026-06-24 19:00:00', 'scheduled'),
        (53, 53, 'World Cup', 'group', 'C', 'Scotland', 'Brazil', 'Miami Gardens', '2026-06-24 22:00:00', 'scheduled'),
        (54, 54, 'World Cup', 'group', 'C', 'Morocco', 'Haiti', 'Atlanta', '2026-06-24 22:00:00', 'scheduled'),
        (55, 55, 'World Cup', 'group', 'D', 'Turkiye', 'United States', 'Inglewood', '2026-06-26 02:00:00', 'scheduled'),
        (56, 56, 'World Cup', 'group', 'D', 'Paraguay', 'Australia', 'Santa Clara', '2026-06-26 02:00:00', 'scheduled'),
        (57, 57, 'World Cup', 'group', 'E', 'Curacao', 'Ivory Coast', 'Philadelphia', '2026-06-25 20:00:00', 'scheduled'),
        (58, 58, 'World Cup', 'group', 'E', 'Ecuador', 'Germany', 'East Rutherford', '2026-06-25 20:00:00', 'scheduled'),
        (59, 59, 'World Cup', 'group', 'F', 'Japan', 'Sweden', 'Arlington', '2026-06-25 23:00:00', 'scheduled'),
        (60, 60, 'World Cup', 'group', 'F', 'Tunisia', 'Netherlands', 'Kansas City', '2026-06-25 23:00:00', 'scheduled'),
        (61, 61, 'World Cup', 'group', 'G', 'Egypt', 'Iran', 'Seattle', '2026-06-27 03:00:00', 'scheduled'),
        (62, 62, 'World Cup', 'group', 'G', 'New Zealand', 'Belgium', 'Vancouver', '2026-06-27 03:00:00', 'scheduled'),
        (63, 63, 'World Cup', 'group', 'H', 'Cape Verde', 'Saudi Arabia', 'Houston', '2026-06-27 00:00:00', 'scheduled'),
        (64, 64, 'World Cup', 'group', 'H', 'Uruguay', 'Spain', 'Guadalajara', '2026-06-27 00:00:00', 'scheduled'),
        (65, 65, 'World Cup', 'group', 'I', 'Norway', 'France', 'Foxborough', '2026-06-26 19:00:00', 'scheduled'),
        (66, 66, 'World Cup', 'group', 'I', 'Senegal', 'Iraq', 'Toronto', '2026-06-26 19:00:00', 'scheduled'),
        (67, 67, 'World Cup', 'group', 'J', 'Algeria', 'Austria', 'Kansas City', '2026-06-28 02:00:00', 'scheduled'),
        (68, 68, 'World Cup', 'group', 'J', 'Jordan', 'Argentina', 'Arlington', '2026-06-28 02:00:00', 'scheduled'),
        (69, 69, 'World Cup', 'group', 'K', 'Colombia', 'Portugal', 'Miami Gardens', '2026-06-27 23:30:00', 'scheduled'),
        (70, 70, 'World Cup', 'group', 'K', 'DR Congo', 'Uzbekistan', 'Atlanta', '2026-06-27 23:30:00', 'scheduled'),
        (71, 71, 'World Cup', 'group', 'L', 'Panama', 'England', 'East Rutherford', '2026-06-27 21:00:00', 'scheduled'),
        (72, 72, 'World Cup', 'group', 'L', 'Croatia', 'Ghana', 'Philadelphia', '2026-06-27 21:00:00', 'scheduled'),
        (73, 73, 'World Cup', 'round_32', NULL, 'Winner 73 A', 'Winner 73 B', 'Inglewood', '2026-06-28 19:00:00', 'closed'),
        (74, 74, 'World Cup', 'round_32', NULL, 'Winner 74 A', 'Winner 74 B', 'Houston', '2026-06-29 17:00:00', 'closed'),
        (75, 75, 'World Cup', 'round_32', NULL, 'Winner 75 A', 'Winner 75 B', 'Foxborough', '2026-06-29 20:30:00', 'closed'),
        (76, 76, 'World Cup', 'round_32', NULL, 'Winner 76 A', 'Winner 76 B', 'Monterrey', '2026-06-30 01:00:00', 'closed'),
        (77, 77, 'World Cup', 'round_32', NULL, 'Winner 77 A', 'Winner 77 B', 'Arlington', '2026-06-30 17:00:00', 'closed'),
        (78, 78, 'World Cup', 'round_32', NULL, 'Winner 78 A', 'Winner 78 B', 'East Rutherford', '2026-06-30 21:00:00', 'closed'),
        (79, 79, 'World Cup', 'round_32', NULL, 'Winner 79 A', 'Winner 79 B', 'Mexico City', '2026-07-01 01:00:00', 'closed'),
        (80, 80, 'World Cup', 'round_32', NULL, 'Winner 80 A', 'Winner 80 B', 'Atlanta', '2026-07-01 16:00:00', 'closed'),
        (81, 81, 'World Cup', 'round_32', NULL, 'Winner 81 A', 'Winner 81 B', 'Seattle', '2026-07-01 20:00:00', 'closed'),
        (82, 82, 'World Cup', 'round_32', NULL, 'Winner 82 A', 'Winner 82 B', 'Santa Clara', '2026-07-02 00:00:00', 'closed'),
        (83, 83, 'World Cup', 'round_32', NULL, 'Winner 83 A', 'Winner 83 B', 'Inglewood', '2026-07-02 19:00:00', 'closed'),
        (84, 84, 'World Cup', 'round_32', NULL, 'Winner 84 A', 'Winner 84 B', 'Toronto', '2026-07-02 23:00:00', 'closed'),
        (85, 85, 'World Cup', 'round_32', NULL, 'Winner 85 A', 'Winner 85 B', 'Vancouver', '2026-07-03 03:00:00', 'closed'),
        (86, 86, 'World Cup', 'round_32', NULL, 'Winner 86 A', 'Winner 86 B', 'Arlington', '2026-07-03 18:00:00', 'closed'),
        (87, 87, 'World Cup', 'round_32', NULL, 'Winner 87 A', 'Winner 87 B', 'Miami Gardens', '2026-07-03 22:00:00', 'closed'),
        (88, 88, 'World Cup', 'round_32', NULL, 'Winner 88 A', 'Winner 88 B', 'Kansas City', '2026-07-04 01:30:00', 'closed'),
        (89, 89, 'World Cup', 'round_16', NULL, 'Winner 89 A', 'Winner 89 B', 'Houston', '2026-07-04 17:00:00', 'closed'),
        (90, 90, 'World Cup', 'round_16', NULL, 'Winner 90 A', 'Winner 90 B', 'Philadelphia', '2026-07-04 21:00:00', 'closed'),
        (91, 91, 'World Cup', 'round_16', NULL, 'Winner 91 A', 'Winner 91 B', 'East Rutherford', '2026-07-05 20:00:00', 'closed'),
        (92, 92, 'World Cup', 'round_16', NULL, 'Winner 92 A', 'Winner 92 B', 'Mexico City', '2026-07-06 00:00:00', 'closed'),
        (93, 93, 'World Cup', 'round_16', NULL, 'Winner 93 A', 'Winner 93 B', 'Arlington', '2026-07-06 19:00:00', 'closed'),
        (94, 94, 'World Cup', 'round_16', NULL, 'Winner 94 A', 'Winner 94 B', 'Seattle', '2026-07-07 00:00:00', 'closed'),
        (95, 95, 'World Cup', 'round_16', NULL, 'Winner 95 A', 'Winner 95 B', 'Atlanta', '2026-07-07 16:00:00', 'closed'),
        (96, 96, 'World Cup', 'round_16', NULL, 'Winner 96 A', 'Winner 96 B', 'Vancouver', '2026-07-07 20:00:00', 'closed'),
        (97, 97, 'World Cup', 'quarter_final', NULL, 'Winner 97 A', 'Winner 97 B', 'Foxborough', '2026-07-09 20:00:00', 'closed'),
        (98, 98, 'World Cup', 'quarter_final', NULL, 'Winner 98 A', 'Winner 98 B', 'Inglewood', '2026-07-10 19:00:00', 'closed'),
        (99, 99, 'World Cup', 'quarter_final', NULL, 'Winner 99 A', 'Winner 99 B', 'Miami Gardens', '2026-07-11 21:00:00', 'closed'),
        (100, 100, 'World Cup', 'quarter_final', NULL, 'Winner 100 A', 'Winner 100 B', 'Kansas City', '2026-07-12 01:00:00', 'closed'),
        (101, 101, 'World Cup', 'semi_final', NULL, 'Winner 101 A', 'Winner 101 B', 'Arlington', '2026-07-14 19:00:00', 'closed'),
        (102, 102, 'World Cup', 'semi_final', NULL, 'Winner 102 A', 'Winner 102 B', 'Atlanta', '2026-07-15 19:00:00', 'closed'),
        (103, 103, 'World Cup', 'third_place', NULL, 'Winner 103 A', 'Winner 103 B', 'Miami Gardens', '2026-07-18 21:00:00', 'closed'),
        (104, 104, 'World Cup', 'final', NULL, 'Winner 104 A', 'Winner 104 B', 'East Rutherford', '2026-07-19 19:00:00', 'closed')
    ) AS source (
        match_id,
        match_no,
        competition,
        stage,
        group_name,
        home_team,
        away_team,
        venue,
        kickoff_at,
        status
    )
    ON target.match_id = source.match_id
    WHEN MATCHED THEN
        UPDATE SET
            match_no = source.match_no,
            competition = source.competition,
            stage = source.stage,
            group_name = source.group_name,
            home_team = source.home_team,
            away_team = source.away_team,
            venue = source.venue,
            kickoff_at = source.kickoff_at,
            status = source.status,
            updated_at = GETDATE()
    WHEN NOT MATCHED THEN
        INSERT (match_id, match_no, competition, stage, group_name, home_team, away_team, venue, kickoff_at, status)
        VALUES (
            source.match_id,
            source.match_no,
            source.competition,
            source.stage,
            source.group_name,
            source.home_team,
            source.away_team,
            source.venue,
            source.kickoff_at,
            source.status
        );

    SET IDENTITY_INSERT agent.dbo.world_cup_matches OFF;
END;

IF OBJECT_ID('agent.dbo.world_cup_odds', 'U') IS NOT NULL
BEGIN
    MERGE agent.dbo.world_cup_odds AS target
    USING (VALUES
        (1, '1x2', 'home', 1.40, 0),
        (1, '1x2', 'draw', 4.60, 0),
        (1, '1x2', 'away', 8.00, 0),
        (2, '1x2', 'home', 2.62, 0),
        (2, '1x2', 'draw', 3.10, 0),
        (2, '1x2', 'away', 2.75, 0),
        (7, '1x2', 'home', 2.05, 0),
        (7, '1x2', 'draw', 3.30, 0),
        (7, '1x2', 'away', 3.90, 0),
        (5, '1x2', 'home', 1.61, 0),
        (5, '1x2', 'draw', 3.90, 0),
        (5, '1x2', 'away', 5.50, 0),
        (6, '1x2', 'home', 6.25, 0),
        (6, '1x2', 'draw', 4.33, 0),
        (6, '1x2', 'away', 1.50, 0),
        (8, '1x2', 'home', 4.40, 0),
        (8, '1x2', 'draw', 3.50, 0),
        (8, '1x2', 'away', 1.80, 0),
        (9, '1x2', 'home', 1.04, 0),
        (9, '1x2', 'draw', 16.00, 0),
        (9, '1x2', 'away', 46.00, 0),
        (11, '1x2', 'home', 2.00, 0),
        (11, '1x2', 'draw', 3.50, 0),
        (11, '1x2', 'away', 3.50, 0),
        (10, '1x2', 'home', 3.50, 0),
        (10, '1x2', 'draw', 2.88, 0),
        (10, '1x2', 'away', 2.30, 0),
        (12, '1x2', 'home', 1.91, 0),
        (12, '1x2', 'draw', 3.30, 0),
        (12, '1x2', 'away', 4.00, 0),
        (15, '1x2', 'home', 1.11, 0),
        (15, '1x2', 'draw', 10.00, 0),
        (15, '1x2', 'away', 23.00, 0),
        (NULL, 'winner', 'Spain', 5.50, 999),
        (NULL, 'winner', 'France', 5.80, 998),
        (NULL, 'winner', 'England', 7.50, 997),
        (NULL, 'winner', 'Brazil', 10.00, 996),
        (NULL, 'winner', 'Portugal', 9.50, 995),
        (NULL, 'winner', 'Argentina', 10.50, 994),
        (NULL, 'winner', 'Germany', 14.00, 993),
        (NULL, 'winner', 'Netherlands', 18.00, 992),
        (NULL, 'winner', 'Belgium', 23.00, 991),
        (NULL, 'winner', 'Norway', 34.00, 990),
        (NULL, 'winner', 'Colombia', 41.00, 989),
        (NULL, 'winner', 'Japan', 46.00, 988),
        (NULL, 'winner', 'Morocco', 56.00, 987),
        (NULL, 'winner', 'United States', 56.00, 986),
        (NULL, 'winner', 'Uruguay', 61.00, 985),
        (NULL, 'winner', 'Mexico', 61.00, 984),
        (NULL, 'winner', 'Switzerland', 76.00, 983),
        (NULL, 'winner', 'Croatia', 81.00, 982),
        (NULL, 'winner', 'Ecuador', 101.00, 981),
        (NULL, 'winner', 'Austria', 101.00, 980),
        (NULL, 'winner', 'Turkiye', 81.00, 979),
        (NULL, 'winner', 'Senegal', 151.00, 978),
        (NULL, 'winner', 'Ivory Coast', 151.00, 977),
        (NULL, 'winner', 'Sweden', 176.00, 976),
        (NULL, 'winner', 'Canada', 201.00, 975),
        (NULL, 'winner', 'Paraguay', 226.00, 974),
        (NULL, 'winner', 'Bosnia & Herzegovina', 226.00, 973),
        (NULL, 'winner', 'Algeria', 251.00, 972),
        (NULL, 'winner', 'Scotland', 301.00, 971),
        (NULL, 'winner', 'Egypt', 351.00, 970),
        (NULL, 'winner', 'South Korea', 401.00, 969),
        (NULL, 'winner', 'Ghana', 401.00, 968),
        (NULL, 'winner', 'Czechia', 401.00, 967),
        (NULL, 'winner', 'Iran', 751.00, 966),
        (NULL, 'winner', 'Tunisia', 2001.00, 965),
        (NULL, 'winner', 'Australia', 2501.00, 964),
        (NULL, 'winner', 'DR Congo', 2501.00, 963),
        (NULL, 'winner', 'Cape Verde', 2501.00, 962),
        (NULL, 'winner', 'Uzbekistan', 2501.00, 961),
        (NULL, 'winner', 'Panama', 2501.00, 960),
        (NULL, 'winner', 'Curacao', 2501.00, 959),
        (NULL, 'winner', 'Haiti', 1501.00, 958),
        (NULL, 'winner', 'Qatar', 2501.00, 957),
        (NULL, 'winner', 'Saudi Arabia', 2501.00, 956),
        (NULL, 'winner', 'Iraq', 2501.00, 955),
        (NULL, 'winner', 'New Zealand', 2501.00, 954),
        (NULL, 'winner', 'Jordan', 2501.00, 953),
        (NULL, 'winner', 'South Africa', 2501.00, 952)
    ) AS source (match_id, market, selection, decimal_odds, locked_weight)
    ON target.market = source.market
        AND (
            target.match_id = source.match_id
            OR (target.match_id IS NULL AND source.match_id IS NULL)
        )
        AND target.selection = source.selection
    WHEN MATCHED THEN
        UPDATE SET
            decimal_odds = source.decimal_odds,
            previous_odds = target.decimal_odds,
            is_active = 1,
            locked_weight = source.locked_weight,
            updated_at = GETDATE()
    WHEN NOT MATCHED THEN
        INSERT (match_id, market, selection, decimal_odds, previous_odds, is_active, locked_weight, updated_at)
        VALUES (
            source.match_id,
            source.market,
            source.selection,
            source.decimal_odds,
            NULL,
            1,
            source.locked_weight,
            GETDATE()
        );
END;

USE [agent]
GO

DECLARE @WorldCupDescriptions TABLE (
    table_name SYSNAME NOT NULL,
    column_name SYSNAME NULL,
    description NVARCHAR(4000) NOT NULL
);

INSERT INTO @WorldCupDescriptions (table_name, column_name, description)
VALUES
    (N'world_cup_user_state', NULL, N'世界杯活动用户状态表'),
    (N'world_cup_user_state', N'user_id', N'活动用户 UserID，对应 QPAccountsDB.dbo.AccountsInfo.UserID'),
    (N'world_cup_user_state', N'first_bet_used', N'是否已使用世界杯首注奖励，1=已使用，0=未使用'),
    (N'world_cup_user_state', N'invite_code', N'用户自己的邀请码，前端展示并用于手动绑定'),
    (N'world_cup_user_state', N'referred_by_user_id', N'邀请人的 UserID，未绑定时为空'),
    (N'world_cup_user_state', N'referral_bind_at', N'邀请关系绑定时间'),
    (N'world_cup_user_state', N'referral_bind_type', N'绑定方式，link=分享链接自动绑定，manual=24h 内手动输入邀请码'),
    (N'world_cup_user_state', N'device_fp', N'设备指纹，用于风控识别同设备自邀'),
    (N'world_cup_user_state', N'pay_account_hash', N'支付账号哈希，用于风控识别同支付账号'),
    (N'world_cup_user_state', N'signup_ip', N'注册 IP，用于风控识别同 IP 或同网段聚集'),
    (N'world_cup_user_state', N'created_at', N'记录创建时间'),
    (N'world_cup_user_state', N'updated_at', N'记录更新时间'),

    (N'world_cup_matches', NULL, N'世界杯单场比赛表'),
    (N'world_cup_matches', N'match_id', N'比赛 ID，单场 1X2 盘口主键'),
    (N'world_cup_matches', N'match_no', N'FIFA 官方赛程 Match 编号'),
    (N'world_cup_matches', N'competition', N'赛事名称，例如 World Cup'),
    (N'world_cup_matches', N'stage', N'赛事阶段，group=小组赛，round_32=32 强，round_16=16 强，quarter_final=1/4 决赛，semi_final=半决赛，third_place=季军赛，final=决赛'),
    (N'world_cup_matches', N'group_name', N'小组名称，小组赛为 A-L，淘汰赛为空'),
    (N'world_cup_matches', N'home_team', N'主队名称'),
    (N'world_cup_matches', N'away_team', N'客队名称'),
    (N'world_cup_matches', N'venue', N'比赛城市或场馆名称'),
    (N'world_cup_matches', N'kickoff_at', N'开赛时间；开赛前 1 小时停止投注'),
    (N'world_cup_matches', N'status', N'比赛状态，scheduled=待开赛可投注，closed=关闭投注，finished=已完赛'),
    (N'world_cup_matches', N'result', N'比赛结果，home/draw/away；未结算时为空'),
    (N'world_cup_matches', N'created_at', N'记录创建时间'),
    (N'world_cup_matches', N'updated_at', N'记录更新时间'),

    (N'world_cup_odds', NULL, N'世界杯盘口赔率表'),
    (N'world_cup_odds', N'odds_id', N'赔率记录 ID'),
    (N'world_cup_odds', N'match_id', N'比赛 ID；Winner 夺冠盘为空，1X2 单场盘必填'),
    (N'world_cup_odds', N'market', N'盘口类型，winner=夺冠盘，1x2=单场胜平负'),
    (N'world_cup_odds', N'selection', N'投注选项；Winner 为球队名，1X2 为 home/draw/away'),
    (N'world_cup_odds', N'decimal_odds', N'当前十进制赔率'),
    (N'world_cup_odds', N'previous_odds', N'上一版赔率，用于前端展示涨跌箭头'),
    (N'world_cup_odds', N'is_active', N'是否展示并允许投注，1=启用，0=隐藏'),
    (N'world_cup_odds', N'locked_weight', N'后台排序权重，数值越大越靠前'),
    (N'world_cup_odds', N'updated_at', N'赔率更新时间'),

    (N'world_cup_match_favorites', NULL, N'世界杯比赛收藏表'),
    (N'world_cup_match_favorites', N'id', N'收藏记录 ID'),
    (N'world_cup_match_favorites', N'user_id', N'收藏用户 UserID'),
    (N'world_cup_match_favorites', N'match_id', N'被收藏的单场比赛 ID，仅支持 1X2 比赛，不支持 Winner 盘'),
    (N'world_cup_match_favorites', N'created_at', N'收藏创建时间'),

    (N'world_cup_bets', NULL, N'世界杯投注订单表'),
    (N'world_cup_bets', N'bet_id', N'世界杯注单 ID'),
    (N'world_cup_bets', N'user_id', N'下注用户 UserID'),
    (N'world_cup_bets', N'game_id', N'前端展示的 8 位 GameID，冗余保存便于查询'),
    (N'world_cup_bets', N'idempotency_key', N'客户端幂等键，同一用户同一 key 不重复下单'),
    (N'world_cup_bets', N'market', N'盘口类型，winner=夺冠盘，1x2=单场胜平负'),
    (N'world_cup_bets', N'match_id', N'比赛 ID；Winner 夺冠盘为空'),
    (N'world_cup_bets', N'selection', N'投注选项，球队名或 home/draw/away'),
    (N'world_cup_bets', N'stake', N'下注金额，整数分'),
    (N'world_cup_bets', N'odds', N'下单时锁定的十进制赔率'),
    (N'world_cup_bets', N'is_first_bet', N'是否为用户世界杯首注，1=首注'),
    (N'world_cup_bets', N'bonus_amount', N'首注 +50% 额外奖励金额，整数分'),
    (N'world_cup_bets', N'potential_payout', N'潜在派彩金额，整数分，stake * odds + bonus_amount'),
    (N'world_cup_bets', N'status', N'注单状态，pending=待结算，won=赢，lost=输'),
    (N'world_cup_bets', N'settled_at', N'结算时间'),
    (N'world_cup_bets', N'created_at', N'下单时间'),
    (N'world_cup_bets', N'updated_at', N'注单更新时间'),

    (N'world_cup_referrals', NULL, N'世界杯邀请绑定关系表'),
    (N'world_cup_referrals', N'id', N'邀请绑定记录 ID'),
    (N'world_cup_referrals', N'referrer_id', N'邀请人 UserID'),
    (N'world_cup_referrals', N'invitee_id', N'被邀请人 UserID；唯一，一个被邀请人只能绑定一次'),
    (N'world_cup_referrals', N'bind_type', N'绑定方式，link=分享链接自动绑定，manual=手动补填邀请码'),
    (N'world_cup_referrals', N'bind_at', N'绑定发生时间'),
    (N'world_cup_referrals', N'created_at', N'记录创建时间'),

    (N'world_cup_referral_rewards', NULL, N'世界杯邀请奖励审核表'),
    (N'world_cup_referral_rewards', N'reward_id', N'邀请奖励单 ID，后台审核对象'),
    (N'world_cup_referral_rewards', N'referrer_id', N'邀请人 UserID'),
    (N'world_cup_referral_rewards', N'invitee_id', N'被邀请人 UserID；同一被邀请人首充只生成一次奖励单'),
    (N'world_cup_referral_rewards', N'first_deposit_order_sn', N'触发邀请奖励的首充订单号，用于 OrderPaid 事件监听幂等'),
    (N'world_cup_referral_rewards', N'first_deposit_amt', N'触发奖励时的首充实际入账金额，整数分'),
    (N'world_cup_referral_rewards', N'reward_each', N'双方各得奖励金额，整数分；规则为 min(first_deposit_amt * 50%, 10000)'),
    (N'world_cup_referral_rewards', N'total_liability', N'本奖励单总赔付，整数分，reward_each * 2，最高 20000'),
    (N'world_cup_referral_rewards', N'risk_score', N'风控评分，0-100'),
    (N'world_cup_referral_rewards', N'risk_level', N'风险等级，low/medium/high'),
    (N'world_cup_referral_rewards', N'signals', N'命中的风控信号 JSON 文本'),
    (N'world_cup_referral_rewards', N'status', N'审核状态，reviewing/approved/rejected/on_hold/clawed_back'),
    (N'world_cup_referral_rewards', N'reason_code', N'驳回或追回原因代码'),
    (N'world_cup_referral_rewards', N'review_by', N'审核员标识'),
    (N'world_cup_referral_rewards', N'reviewed_at', N'审核时间'),
    (N'world_cup_referral_rewards', N'submitted_at', N'奖励单提交审核时间，用于计算 24h SLA'),
    (N'world_cup_referral_rewards', N'created_at', N'记录创建时间'),
    (N'world_cup_referral_rewards', N'updated_at', N'记录更新时间'),

    (N'world_cup_risk_signals', NULL, N'世界杯风控信号配置表'),
    (N'world_cup_risk_signals', N'code', N'风控信号代码'),
    (N'world_cup_risk_signals', N'label', N'后台展示名称'),
    (N'world_cup_risk_signals', N'weight', N'风控权重，命中后累加到 risk_score'),
    (N'world_cup_risk_signals', N'is_active', N'是否启用，1=启用，0=停用'),
    (N'world_cup_risk_signals', N'updated_at', N'配置更新时间'),

    (N'world_cup_audit_log', NULL, N'世界杯审核操作日志表'),
    (N'world_cup_audit_log', N'id', N'审核日志 ID'),
    (N'world_cup_audit_log', N'reward_id', N'关联的邀请奖励单 ID，可为空'),
    (N'world_cup_audit_log', N'actor', N'操作人，后台审核员或系统任务'),
    (N'world_cup_audit_log', N'action', N'操作动作，例如 approve/reject/hold/clawback/auto_score'),
    (N'world_cup_audit_log', N'reason_code', N'操作原因代码'),
    (N'world_cup_audit_log', N'before_status', N'操作前状态'),
    (N'world_cup_audit_log', N'after_status', N'操作后状态'),
    (N'world_cup_audit_log', N'payload', N'操作上下文 JSON 文本'),
    (N'world_cup_audit_log', N'created_at', N'日志创建时间');

DECLARE
    @tableName SYSNAME,
    @columnName SYSNAME,
    @description NVARCHAR(4000),
    @objectName NVARCHAR(512);

DECLARE world_cup_description_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT table_name, column_name, description
FROM @WorldCupDescriptions;

OPEN world_cup_description_cursor;

FETCH NEXT FROM world_cup_description_cursor INTO @tableName, @columnName, @description;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @objectName = N'dbo.' + @tableName;

    IF OBJECT_ID(@objectName, 'U') IS NOT NULL
        AND (@columnName IS NULL OR COL_LENGTH(@objectName, @columnName) IS NOT NULL)
    BEGIN
        IF @columnName IS NULL
        BEGIN
            IF EXISTS (
                SELECT 1
                FROM sys.extended_properties ep
                INNER JOIN sys.tables t ON ep.major_id = t.object_id
                INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
                WHERE ep.name = N'MS_Description'
                    AND ep.minor_id = 0
                    AND s.name = N'dbo'
                    AND t.name = @tableName
            )
            BEGIN
                EXEC sys.sp_updateextendedproperty
                    @name = N'MS_Description',
                    @value = @description,
                    @level0type = N'SCHEMA',
                    @level0name = N'dbo',
                    @level1type = N'TABLE',
                    @level1name = @tableName;
            END
            ELSE
            BEGIN
                EXEC sys.sp_addextendedproperty
                    @name = N'MS_Description',
                    @value = @description,
                    @level0type = N'SCHEMA',
                    @level0name = N'dbo',
                    @level1type = N'TABLE',
                    @level1name = @tableName;
            END;
        END
        ELSE
        BEGIN
            IF EXISTS (
                SELECT 1
                FROM sys.extended_properties ep
                INNER JOIN sys.tables t ON ep.major_id = t.object_id
                INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
                INNER JOIN sys.columns c ON ep.major_id = c.object_id
                    AND ep.minor_id = c.column_id
                WHERE ep.name = N'MS_Description'
                    AND s.name = N'dbo'
                    AND t.name = @tableName
                    AND c.name = @columnName
            )
            BEGIN
                EXEC sys.sp_updateextendedproperty
                    @name = N'MS_Description',
                    @value = @description,
                    @level0type = N'SCHEMA',
                    @level0name = N'dbo',
                    @level1type = N'TABLE',
                    @level1name = @tableName,
                    @level2type = N'COLUMN',
                    @level2name = @columnName;
            END
            ELSE
            BEGIN
                EXEC sys.sp_addextendedproperty
                    @name = N'MS_Description',
                    @value = @description,
                    @level0type = N'SCHEMA',
                    @level0name = N'dbo',
                    @level1type = N'TABLE',
                    @level1name = @tableName,
                    @level2type = N'COLUMN',
                    @level2name = @columnName;
            END;
        END;
    END;

    FETCH NEXT FROM world_cup_description_cursor INTO @tableName, @columnName, @description;
END;

CLOSE world_cup_description_cursor;
DEALLOCATE world_cup_description_cursor;
GO
