xiaoxia-saas/migrations/008_membership_points.sql

-- 会员 + 积分系统 (#1895)
-- users 表新增字段 + 4 张新表
-- 创建时间: 2026-09-11

-- 1. users 表新增字段
ALTER TABLE users ADD COLUMN IF NOT EXISTS is_member BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE users ADD COLUMN IF NOT EXISTS member_type VARCHAR(20);
ALTER TABLE users ADD COLUMN IF NOT EXISTS member_expires_at TIMESTAMP;
ALTER TABLE users ADD COLUMN IF NOT EXISTS points_balance INTEGER NOT NULL DEFAULT 0;

-- 2. points_accounts 积分账户表
CREATE TABLE IF NOT EXISTS points_accounts (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
    balance INTEGER NOT NULL DEFAULT 0,
    total_earned INTEGER NOT NULL DEFAULT 0,
    total_spent INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- 3. points_transactions 积分流水表
CREATE TABLE IF NOT EXISTS points_transactions (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    account_id VARCHAR(36) NOT NULL REFERENCES points_accounts(id) ON DELETE CASCADE,
    type VARCHAR(20) NOT NULL,
    source VARCHAR(50) NOT NULL,
    amount INTEGER NOT NULL,
    balance_after INTEGER NOT NULL,
    description VARCHAR(255) DEFAULT '',
    ref_id VARCHAR(100) DEFAULT '',
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_points_tx_user ON points_transactions(user_id);
CREATE INDEX IF NOT EXISTS idx_points_tx_type ON points_transactions(type);
CREATE INDEX IF NOT EXISTS idx_points_tx_source ON points_transactions(source);
CREATE INDEX IF NOT EXISTS idx_points_tx_created ON points_transactions(created_at);

-- 4. points_orders 积分/会员订单表
CREATE TABLE IF NOT EXISTS points_orders (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    order_type VARCHAR(20) NOT NULL,
    product_code VARCHAR(50) NOT NULL,
    amount_cents INTEGER NOT NULL,
    original_amount_cents INTEGER NOT NULL DEFAULT 0,
    discount REAL NOT NULL DEFAULT 1.0,
    points_amount INTEGER NOT NULL DEFAULT 0,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    payment_method VARCHAR(50),
    payment_id VARCHAR(100),
    paid_at TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_points_orders_user ON points_orders(user_id);
CREATE INDEX IF NOT EXISTS idx_points_orders_status ON points_orders(status);

-- 5. daily_usage_records 每日免费混剪计数
CREATE TABLE IF NOT EXISTS daily_usage_records (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    usage_date DATE NOT NULL,
    usage_type VARCHAR(50) NOT NULL DEFAULT 'free_clip',
    count INTEGER NOT NULL DEFAULT 0,
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    UNIQUE(user_id, usage_date, usage_type)
);

CREATE INDEX IF NOT EXISTS idx_daily_usage_user_date ON daily_usage_records(user_id, usage_date);