Maas-Model-platform/backend/migrations/037_admin_backend_models.sql

-- 后台管理系统数据库模型扩展
-- Requirements: 13.1, 13.2, 13.3, 13.4

-- 1. 创建 admin_users 表（管理员用户）
CREATE TABLE IF NOT EXISTS aigcspace.admin_users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    nickname VARCHAR(100) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_admin_users_username ON aigcspace.admin_users(username);
CREATE INDEX IF NOT EXISTS idx_admin_users_status ON aigcspace.admin_users(status);

COMMENT ON TABLE aigcspace.admin_users IS '管理员用户表';
COMMENT ON COLUMN aigcspace.admin_users.id IS '主键ID';
COMMENT ON COLUMN aigcspace.admin_users.username IS '用户名';
COMMENT ON COLUMN aigcspace.admin_users.password_hash IS '密码哈希';
COMMENT ON COLUMN aigcspace.admin_users.nickname IS '昵称';
COMMENT ON COLUMN aigcspace.admin_users.status IS '状态：active/disabled';
COMMENT ON COLUMN aigcspace.admin_users.created_at IS '创建时间';
COMMENT ON COLUMN aigcspace.admin_users.updated_at IS '更新时间';

-- 2. 创建 operation_log 表（操作日志）
CREATE TABLE IF NOT EXISTS aigcspace.operation_log (
    id BIGSERIAL PRIMARY KEY,
    admin_id INTEGER NOT NULL REFERENCES aigcspace.admin_users(id),
    operation_type VARCHAR(50) NOT NULL,
    module VARCHAR(50) NOT NULL,
    target_id VARCHAR(100),
    detail TEXT,
    ip_address VARCHAR(50),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_operation_log_admin_id ON aigcspace.operation_log(admin_id);
CREATE INDEX IF NOT EXISTS idx_operation_log_operation_type ON aigcspace.operation_log(operation_type);
CREATE INDEX IF NOT EXISTS idx_operation_log_module ON aigcspace.operation_log(module);
CREATE INDEX IF NOT EXISTS idx_operation_log_created_at ON aigcspace.operation_log(created_at);

COMMENT ON TABLE aigcspace.operation_log IS '管理员操作日志表';
COMMENT ON COLUMN aigcspace.operation_log.id IS '主键ID';
COMMENT ON COLUMN aigcspace.operation_log.admin_id IS '管理员ID';
COMMENT ON COLUMN aigcspace.operation_log.operation_type IS '操作类型：create/update/delete/login';
COMMENT ON COLUMN aigcspace.operation_log.module IS '操作模块：user/model/config';
COMMENT ON COLUMN aigcspace.operation_log.target_id IS '操作对象ID';
COMMENT ON COLUMN aigcspace.operation_log.detail IS '操作详情（JSON格式）';
COMMENT ON COLUMN aigcspace.operation_log.ip_address IS 'IP地址';
COMMENT ON COLUMN aigcspace.operation_log.created_at IS '创建时间';

-- 3. 创建 admin_login_attempt 表（登录尝试记录）
CREATE TABLE IF NOT EXISTS aigcspace.admin_login_attempt (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    success BOOLEAN NOT NULL,
    ip_address VARCHAR(50),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_admin_login_attempt_username ON aigcspace.admin_login_attempt(username);
CREATE INDEX IF NOT EXISTS idx_admin_login_attempt_created_at ON aigcspace.admin_login_attempt(created_at);

COMMENT ON TABLE aigcspace.admin_login_attempt IS '管理员登录尝试记录表';
COMMENT ON COLUMN aigcspace.admin_login_attempt.id IS '主键ID';
COMMENT ON COLUMN aigcspace.admin_login_attempt.username IS '用户名';
COMMENT ON COLUMN aigcspace.admin_login_attempt.success IS '是否成功';
COMMENT ON COLUMN aigcspace.admin_login_attempt.ip_address IS 'IP地址';
COMMENT ON COLUMN aigcspace.admin_login_attempt.created_at IS '创建时间';

-- 4. 在 users 表新增 status 字段
ALTER TABLE aigcspace.users 
ADD COLUMN IF NOT EXISTS status VARCHAR(20) NOT NULL DEFAULT 'active';

CREATE INDEX IF NOT EXISTS idx_users_status ON aigcspace.users(status);

COMMENT ON COLUMN aigcspace.users.status IS '账户状态：active/disabled';

-- 5. 在 balance_log 表新增 remark 和 admin_id 字段
ALTER TABLE aigcspace.balance_log 
ADD COLUMN IF NOT EXISTS remark TEXT;

ALTER TABLE aigcspace.balance_log 
ADD COLUMN IF NOT EXISTS admin_id VARCHAR(50);

COMMENT ON COLUMN aigcspace.balance_log.remark IS '调整原因（admin_adjust类型使用）';
COMMENT ON COLUMN aigcspace.balance_log.admin_id IS '操作管理员ID（admin_adjust类型使用）';