-- =============================================
-- 四川路桥 Maas 算力平台 - 数据库初始化脚本
-- PostgreSQL 15+
-- =============================================

-- 创建 schema
CREATE SCHEMA IF NOT EXISTS aigcspace;

-- =============================================
-- 1. 用户表
-- =============================================
CREATE TABLE aigcspace.users (
    id VARCHAR(50) NOT NULL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    password_hash VARCHAR(255),
    nickname VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    email VARCHAR(255),
    avatar TEXT,
    real_name VARCHAR(100),
    id_card VARCHAR(18),
    is_verified VARCHAR(20) NOT NULL DEFAULT 'unverified',
    verified_at TIMESTAMP WITHOUT TIME ZONE,
    apikey VARCHAR(255),
    registration_date DATE,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL,
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL
);

CREATE INDEX idx_users_username ON aigcspace.users(username);
CREATE INDEX idx_users_phone ON aigcspace.users(phone);
CREATE INDEX idx_users_email ON aigcspace.users(email);
CREATE INDEX idx_users_status ON aigcspace.users(status);

-- =============================================
-- 2. 管理员表
-- =============================================
CREATE TABLE aigcspace.admin_users (
    id SERIAL NOT NULL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    nickname VARCHAR(100),
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    last_login_at TIMESTAMP WITHOUT TIME ZONE,
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now()
);

CREATE TABLE aigcspace.admin_login_attempt (
    id SERIAL NOT NULL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    success BOOLEAN NOT NULL,
    ip_address VARCHAR(50),
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now()
);

CREATE TABLE aigcspace.operation_log (
    id BIGSERIAL NOT NULL 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 JSON,
    ip_address VARCHAR(50),
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now()
);

CREATE INDEX idx_operation_log_admin_id ON aigcspace.operation_log(admin_id);
CREATE INDEX idx_operation_log_created_at ON aigcspace.operation_log(created_at);

-- =============================================
-- 3. 模型表
-- =============================================
CREATE TABLE aigcspace.models_new (
    id SERIAL NOT NULL PRIMARY KEY,
    model_code VARCHAR(200) NOT NULL UNIQUE,
    description TEXT,
    display_tags TEXT[],
    input_modalities TEXT[],
    output_modalities TEXT[],
    features JSONB,
    rate_limits JSONB,
    tool_call_prices JSONB,
    raw_prices JSONB,
    source_url TEXT,
    crawled_at TIMESTAMP WITHOUT TIME ZONE,
    categories INTEGER[] NOT NULL DEFAULT '{0}',
    display_name VARCHAR(255),
    img TEXT,
    supplier VARCHAR(100) NOT NULL DEFAULT '',
    tag1 VARCHAR(100),
    tag2 VARCHAR(100),
    keywords TEXT,
    custom_description TEXT,
    is_featured BOOLEAN NOT NULL DEFAULT false,
    is_show_enabled BOOLEAN NOT NULL DEFAULT true,
    is_api_enabled BOOLEAN NOT NULL DEFAULT false,
    is_search BOOLEAN NOT NULL DEFAULT false,
    is_thinking BOOLEAN NOT NULL DEFAULT false,
    source_keys TEXT[],
    normalized_keys TEXT[],
    group_name VARCHAR(100),
    encrypted_api_key TEXT,
    -- 本地模型字段
    is_local BOOLEAN NOT NULL DEFAULT false,
    user_id VARCHAR(50) REFERENCES aigcspace.users(id),
    base_url VARCHAR(500),
    local_api_key VARCHAR(500),
    visibility VARCHAR(20) DEFAULT 'user',
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL,
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL
);

CREATE INDEX idx_models_new_categories ON aigcspace.models_new USING GIN(categories);
CREATE INDEX idx_models_new_supplier ON aigcspace.models_new(supplier);
CREATE INDEX idx_models_new_is_local ON aigcspace.models_new(is_local);
CREATE INDEX idx_models_new_user_id ON aigcspace.models_new(user_id);
CREATE INDEX idx_models_new_is_show_enabled ON aigcspace.models_new(is_show_enabled);
CREATE INDEX idx_models_new_is_api_enabled ON aigcspace.models_new(is_api_enabled);

CREATE TABLE aigcspace.model_price_new (
    id SERIAL NOT NULL PRIMARY KEY,
    model_code VARCHAR(200) NOT NULL,
    label VARCHAR(200) NOT NULL DEFAULT 'default',
    tier_min NUMERIC(20, 2),
    tier_max NUMERIC(20, 2),
    tier_unit VARCHAR(50),
    input_price_original NUMERIC(20, 8) NOT NULL DEFAULT 0,
    output_price_original NUMERIC(20, 8) NOT NULL DEFAULT 0,
    discount_rate NUMERIC(5, 4) NOT NULL DEFAULT 1.0,
    discount_label VARCHAR(20),
    input_price_discounted NUMERIC(20, 8) NOT NULL DEFAULT 0,
    output_price_discounted NUMERIC(20, 8) NOT NULL DEFAULT 0,
    currency VARCHAR(10) NOT NULL DEFAULT 'CNY',
    unit VARCHAR(100) NOT NULL DEFAULT '千tokens',
    display_multiplier INTEGER NOT NULL DEFAULT 1,
    source_url TEXT,
    crawled_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT now(),
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL,
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL
);

CREATE INDEX idx_model_price_new_model_code ON aigcspace.model_price_new(model_code);

CREATE TABLE aigcspace.model_category (
    id SERIAL NOT NULL PRIMARY KEY,
    code INTEGER NOT NULL UNIQUE,
    name VARCHAR(50) NOT NULL,
    description VARCHAR(200),
    sort_order INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL
);

-- 初始化模型分类
INSERT INTO aigcspace.model_category (code, name, sort_order) VALUES
(0, '语言模型', 0),
(1, '多模态', 1),
(2, 'TTS', 2),
(3, 'STT', 3),
(4, '文生图', 4),
(5, '生视频', 5),
(6, '图生图', 6),
(7, 'Embedding', 7),
(8, 'Rerank', 8);

-- =============================================
-- 4. API 密钥表
-- =============================================
CREATE TABLE aigcspace.platform_api_key (
    id SERIAL NOT NULL 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',
    key_type VARCHAR(20) NOT NULL DEFAULT 'public',
    last_used_at TIMESTAMP WITHOUT TIME ZONE,
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL,
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL
);

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);
CREATE INDEX idx_platform_api_key_key_type ON aigcspace.platform_api_key(key_type);

-- =============================================
-- 5. API 调用日志表
-- =============================================
CREATE TABLE aigcspace.api_call_log (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL REFERENCES aigcspace.users(id) ON DELETE CASCADE,
    api_key_id INTEGER REFERENCES aigcspace.platform_api_key(id) ON DELETE SET NULL,
    model_id INTEGER,
    model_name VARCHAR(255) NOT NULL,
    is_local BOOLEAN NOT NULL DEFAULT false,
    input_tokens INTEGER NOT NULL DEFAULT 0,
    output_tokens INTEGER NOT NULL DEFAULT 0,
    bill NUMERIC(12, 4) NOT NULL DEFAULT 0,
    status VARCHAR(20) NOT NULL DEFAULT 'success',
    error_message TEXT,
    request_ip VARCHAR(50),
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL
);

CREATE INDEX idx_api_call_log_user_id ON aigcspace.api_call_log(user_id);
CREATE INDEX idx_api_call_log_api_key_id ON aigcspace.api_call_log(api_key_id);
CREATE INDEX idx_api_call_log_model_id ON aigcspace.api_call_log(model_id);
CREATE INDEX idx_api_call_log_created_at ON aigcspace.api_call_log(created_at);
CREATE INDEX idx_api_call_log_is_local ON aigcspace.api_call_log(is_local);
CREATE INDEX idx_api_call_log_model_name ON aigcspace.api_call_log(model_name);

-- =============================================
-- 6. 用户本地模型权限表
-- =============================================
CREATE TABLE aigcspace.user_local_model_permission (
    id SERIAL NOT NULL PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL REFERENCES aigcspace.users(id) ON DELETE CASCADE,
    model_id INTEGER NOT NULL REFERENCES aigcspace.models_new(id) ON DELETE CASCADE,
    has_access BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL,
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL,
    UNIQUE(user_id, model_id)
);

-- =============================================
-- 7. 系统配置表
-- =============================================
CREATE TABLE aigcspace.system_config (
    id SERIAL NOT NULL PRIMARY KEY,
    config_key VARCHAR(100) NOT NULL UNIQUE,
    config_value TEXT NOT NULL DEFAULT '',
    config_type VARCHAR(20) NOT NULL DEFAULT 'string',
    category VARCHAR(50) NOT NULL DEFAULT 'basic',
    description VARCHAR(500),
    updated_by INTEGER REFERENCES aigcspace.admin_users(id),
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now()
);

CREATE TABLE aigcspace.config_history (
    id SERIAL NOT NULL PRIMARY KEY,
    config_key VARCHAR(100) NOT NULL,
    old_value TEXT,
    new_value TEXT NOT NULL,
    updated_by INTEGER REFERENCES aigcspace.admin_users(id),
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now()
);

-- =============================================
-- 8. 日志表
-- =============================================
CREATE TABLE aigcspace.login_log (
    id SERIAL NOT NULL PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL,
    username VARCHAR(100),
    user_type VARCHAR(20) NOT NULL DEFAULT 'user',
    login_result VARCHAR(20) NOT NULL,
    fail_reason VARCHAR(200),
    ip_address VARCHAR(50),
    user_agent TEXT,
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now()
);

CREATE INDEX idx_login_log_user_id ON aigcspace.login_log(user_id);
CREATE INDEX idx_login_log_created_at ON aigcspace.login_log(created_at);

CREATE TABLE aigcspace.api_log (
    id SERIAL NOT NULL PRIMARY KEY,
    user_id VARCHAR(50),
    username VARCHAR(100),
    phone VARCHAR(20),
    module VARCHAR(50),
    api_path VARCHAR(500) NOT NULL,
    request_method VARCHAR(10) NOT NULL,
    request_params JSON,
    response_status INTEGER,
    response_time INTEGER,
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now()
);

CREATE INDEX idx_api_log_user_id ON aigcspace.api_log(user_id);
CREATE INDEX idx_api_log_created_at ON aigcspace.api_log(created_at);
CREATE INDEX idx_api_log_module ON aigcspace.api_log(module);

-- =============================================
-- 9. 初始化默认管理员（密码: admin123 的 SHA256）
-- =============================================
INSERT INTO aigcspace.admin_users (username, password_hash, nickname, status)
VALUES ('admin', '240be518fabd2724ddb6f04eeb1da5967448d7e831c08c8fa822809f74c720a9', '超级管理员', 'active')
ON CONFLICT (username) DO NOTHING;

-- =============================================
-- 10. 初始化系统配置
-- =============================================
INSERT INTO aigcspace.system_config (config_key, config_value, config_type, category, description) VALUES
('system_name', '四川路桥Maas算力平台', 'string', 'basic', '平台名称'),
('system_logo', '', 'string', 'basic', '平台Logo URL'),
('icp_number', '', 'string', 'basic', 'ICP备案号'),
('contact_email', '', 'string', 'basic', '联系邮箱'),
('enable_local_models', 'true', 'boolean', 'feature', '是否启用私域模型功能'),
('enable_registration', 'true', 'boolean', 'feature', '是否开放注册'),
('max_api_keys_per_user', '5', 'number', 'limit', '每用户最大API Key数量')
ON CONFLICT (config_key) DO NOTHING;