-- 迁移文件: 004_create_users_table.sql
-- 描述: 创建用户信息表
-- 基于个人中心页面需求生成

-- ============================================
-- 正向迁移: 创建表和索引
-- ============================================

-- 创建users表
CREATE TABLE IF NOT EXISTS users (
    id VARCHAR(50) PRIMARY KEY,
    nickname VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    email VARCHAR(255),
    avatar TEXT,
    apikey VARCHAR(255),
    registration_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

-- 添加表注释
COMMENT ON TABLE users IS '用户信息表';
COMMENT ON COLUMN users.id IS '账号ID（主键）';
COMMENT ON COLUMN users.nickname IS '昵称';
COMMENT ON COLUMN users.phone IS '手机号';
COMMENT ON COLUMN users.email IS '邮箱';
COMMENT ON COLUMN users.avatar IS '头像URL';
COMMENT ON COLUMN users.apikey IS 'API密钥，用于API调用认证';
COMMENT ON COLUMN users.registration_date IS '注册时间';
COMMENT ON COLUMN users.created_at IS '创建时间';
COMMENT ON COLUMN users.updated_at IS '更新时间';

-- 创建索引（优化查询性能）
CREATE INDEX IF NOT EXISTS idx_users_phone ON users(phone);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_apikey ON users(apikey);

-- ============================================
-- 回滚迁移: 删除表和索引
-- ============================================
-- DROP INDEX IF EXISTS idx_users_apikey;
-- DROP INDEX IF EXISTS idx_users_email;
-- DROP INDEX IF EXISTS idx_users_phone;
-- DROP TABLE IF EXISTS users;