-- 创建平台API Key表
-- 用于存储用户的平台API Key，支持开放平台API调用认证
-- 需求: 7.7 - API Key使用SHA256哈希算法存储

CREATE TABLE IF NOT EXISTS aigcspace.platform_api_key (
    id SERIAL PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL REFERENCES aigcspace.users(id) ON DELETE CASCADE,
    api_key VARCHAR(100) NOT NULL,
    api_key_prefix VARCHAR(20) NOT NULL,
    name VARCHAR(100),
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    last_used_at TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 添加字段注释
COMMENT ON TABLE aigcspace.platform_api_key IS '平台API Key表';
COMMENT ON COLUMN aigcspace.platform_api_key.id IS '主键ID';
COMMENT ON COLUMN aigcspace.platform_api_key.user_id IS '用户ID';
COMMENT ON COLUMN aigcspace.platform_api_key.api_key IS 'API Key（SHA256哈希存储）';
COMMENT ON COLUMN aigcspace.platform_api_key.api_key_prefix IS 'API Key前缀用于显示（如sk-aigc-xxxx...xxxx）';
COMMENT ON COLUMN aigcspace.platform_api_key.name IS '备注名称';
COMMENT ON COLUMN aigcspace.platform_api_key.status IS '状态：active-启用，disabled-禁用';
COMMENT ON COLUMN aigcspace.platform_api_key.last_used_at IS '最后使用时间';
COMMENT ON COLUMN aigcspace.platform_api_key.created_at IS '创建时间';
COMMENT ON COLUMN aigcspace.platform_api_key.updated_at IS '更新时间';

-- 创建索引
CREATE INDEX idx_platform_api_key_user_id ON aigcspace.platform_api_key(user_id);
CREATE INDEX idx_platform_api_key_api_key ON aigcspace.platform_api_key(api_key);
CREATE INDEX idx_platform_api_key_status ON aigcspace.platform_api_key(status);