Maas-Model-platform/backend/migrations/039_create_config_review_audit_tables.sql

-- 039: 创建系统配置、内容审核、日志审计相关表

-- 1. 系统配置表
CREATE TABLE IF NOT EXISTS aigcspace.system_config (
    id SERIAL PRIMARY KEY,
    config_key VARCHAR(100) UNIQUE NOT NULL,
    config_value TEXT NOT NULL,
    config_type VARCHAR(20) NOT NULL,  -- string/number/boolean/json
    category VARCHAR(50) NOT NULL,     -- basic/feature/limit/third_party
    description VARCHAR(500),
    updated_by INTEGER REFERENCES admin_users(id),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. 配置修改历史表
CREATE TABLE IF NOT EXISTS aigcspace.config_history (
    id SERIAL PRIMARY KEY,
    config_key VARCHAR(100) NOT NULL,
    old_value TEXT,
    new_value TEXT NOT NULL,
    updated_by INTEGER REFERENCES admin_users(id),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 3. 登录日志表
CREATE TABLE IF NOT EXISTS aigcspace.login_log (
    id SERIAL PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL,
    username VARCHAR(100),
    user_type VARCHAR(20) NOT NULL,  -- admin/user
    login_result VARCHAR(20) NOT NULL,  -- success/failed
    fail_reason VARCHAR(200),
    ip_address VARCHAR(50),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 4. API调用日志表
CREATE TABLE IF NOT EXISTS aigcspace.api_log (
    id SERIAL PRIMARY KEY,
    user_id VARCHAR(50),
    username VARCHAR(100),
    api_path VARCHAR(500) NOT NULL,
    request_method VARCHAR(10) NOT NULL,
    request_params JSONB,
    response_status INTEGER,
    response_time INTEGER,  -- 毫秒
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 5. 为ai_picture表添加审核字段
ALTER TABLE aigcspace.ai_picture ADD COLUMN IF NOT EXISTS review_status VARCHAR(20) DEFAULT 'pending';
ALTER TABLE aigcspace.ai_picture ADD COLUMN IF NOT EXISTS reviewed_by INTEGER REFERENCES aigcspace.admin_users(id);
ALTER TABLE aigcspace.ai_picture ADD COLUMN IF NOT EXISTS reviewed_at TIMESTAMP;
ALTER TABLE aigcspace.ai_picture ADD COLUMN IF NOT EXISTS reject_reason VARCHAR(500);

-- 6. 为ai_video表添加审核字段
ALTER TABLE aigcspace.ai_video ADD COLUMN IF NOT EXISTS review_status VARCHAR(20) DEFAULT 'pending';
ALTER TABLE aigcspace.ai_video ADD COLUMN IF NOT EXISTS reviewed_by INTEGER REFERENCES aigcspace.admin_users(id);
ALTER TABLE aigcspace.ai_video ADD COLUMN IF NOT EXISTS reviewed_at TIMESTAMP;
ALTER TABLE aigcspace.ai_video ADD COLUMN IF NOT EXISTS reject_reason VARCHAR(500);

-- 创建索引
CREATE INDEX IF NOT EXISTS idx_config_category ON aigcspace.system_config(category);
CREATE INDEX IF NOT EXISTS idx_config_key ON aigcspace.system_config(config_key);
CREATE INDEX IF NOT EXISTS idx_history_config_key ON aigcspace.config_history(config_key);
CREATE INDEX IF NOT EXISTS idx_history_updated_at ON aigcspace.config_history(updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_login_user_id ON aigcspace.login_log(user_id);
CREATE INDEX IF NOT EXISTS idx_login_created_at ON aigcspace.login_log(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_login_result ON aigcspace.login_log(login_result);
CREATE INDEX IF NOT EXISTS idx_api_user_id ON aigcspace.api_log(user_id);
CREATE INDEX IF NOT EXISTS idx_api_created_at ON aigcspace.api_log(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_api_path ON aigcspace.api_log(api_path);
CREATE INDEX IF NOT EXISTS idx_picture_review_status ON aigcspace.ai_picture(review_status);
CREATE INDEX IF NOT EXISTS idx_video_review_status ON aigcspace.ai_video(review_status);