-- 000005: 模型配额、提供商、知识库、审计日志

-- 模型提供商表
CREATE TABLE model_providers (
    id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name              VARCHAR(100) NOT NULL,
    base_url          TEXT NOT NULL,
    api_key_encrypted TEXT NOT NULL,
    models            JSONB NOT NULL DEFAULT '[]',
    is_active         BOOLEAN NOT NULL DEFAULT true,
    priority          INTEGER NOT NULL DEFAULT 0,
    config            JSONB NOT NULL DEFAULT '{}',
    created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TRIGGER update_model_providers_updated_at
    BEFORE UPDATE ON model_providers
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- 模型配额表
CREATE TABLE model_quotas (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    target_type         VARCHAR(20) NOT NULL
                        CHECK (target_type IN ('global', 'department', 'user')),
    target_id           UUID,
    model_name          VARCHAR(100),
    daily_token_limit   BIGINT,
    monthly_token_limit BIGINT,
    daily_request_limit INTEGER,
    is_active           BOOLEAN NOT NULL DEFAULT true,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_quotas_target ON model_quotas(target_type, target_id);

CREATE TRIGGER update_model_quotas_updated_at
    BEFORE UPDATE ON model_quotas
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- 知识库表
CREATE TABLE knowledge_bases (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name            VARCHAR(100) NOT NULL,
    description     TEXT,
    owner_id        UUID NOT NULL REFERENCES users(id),
    dept_id         UUID REFERENCES departments(id),
    dify_dataset_id VARCHAR(255),
    visibility      VARCHAR(20) NOT NULL DEFAULT 'private'
                    CHECK (visibility IN ('private', 'department', 'public')),
    doc_count       INTEGER NOT NULL DEFAULT 0,
    total_chars     BIGINT NOT NULL DEFAULT 0,
    status          VARCHAR(20) NOT NULL DEFAULT 'active'
                    CHECK (status IN ('active', 'inactive')),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_kb_owner ON knowledge_bases(owner_id);
CREATE INDEX idx_kb_dept ON knowledge_bases(dept_id);

CREATE TRIGGER update_knowledge_bases_updated_at
    BEFORE UPDATE ON knowledge_bases
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- 知识库文档表
CREATE TABLE knowledge_documents (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    kb_id           UUID NOT NULL REFERENCES knowledge_bases(id) ON DELETE CASCADE,
    name            VARCHAR(255) NOT NULL,
    file_url        TEXT,
    file_type       VARCHAR(20)
                    CHECK (file_type IN ('pdf', 'docx', 'txt', 'md', 'csv', 'xlsx')),
    file_size       BIGINT NOT NULL DEFAULT 0,
    char_count      BIGINT NOT NULL DEFAULT 0,
    dify_doc_id     VARCHAR(255),
    indexing_status VARCHAR(20) NOT NULL DEFAULT 'pending'
                    CHECK (indexing_status IN ('pending', 'indexing', 'completed', 'failed')),
    uploader_id     UUID NOT NULL REFERENCES users(id),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_docs_kb ON knowledge_documents(kb_id);
CREATE INDEX idx_docs_status ON knowledge_documents(indexing_status);

CREATE TRIGGER update_knowledge_documents_updated_at
    BEFORE UPDATE ON knowledge_documents
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- 审计日志表
CREATE TABLE audit_logs (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID REFERENCES users(id),
    action          VARCHAR(50) NOT NULL,
    resource_type   VARCHAR(50) NOT NULL,
    resource_id     UUID,
    details         JSONB NOT NULL DEFAULT '{}',
    ip_address      INET,
    user_agent      TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_audit_user ON audit_logs(user_id);
CREATE INDEX idx_audit_action ON audit_logs(action);
CREATE INDEX idx_audit_resource ON audit_logs(resource_type, resource_id);
CREATE INDEX idx_audit_created ON audit_logs(created_at);