-- MCP 服务与能力表。

CREATE TABLE IF NOT EXISTS mcp_server (
    id BIGSERIAL PRIMARY KEY,
    server_code VARCHAR(100) NOT NULL,
    server_name VARCHAR(200) NOT NULL,
    import_type VARCHAR(50) NOT NULL,
    endpoint_url VARCHAR(500),
    package_name VARCHAR(200),
    manifest_json JSONB NOT NULL DEFAULT '{}'::jsonb,
    auth_type VARCHAR(50),
    secret_ref VARCHAR(200),
    health_status VARCHAR(50) NOT NULL DEFAULT 'UNKNOWN',
    enabled BOOLEAN NOT NULL DEFAULT TRUE,
    version INTEGER NOT NULL DEFAULT 1,
    create_time TIMESTAMP,
    update_time TIMESTAMP,
    remark VARCHAR(500) DEFAULT '',
    create_by VARCHAR(64),
    update_by VARCHAR(64),
    CONSTRAINT uk_mcp_server_code UNIQUE (server_code)
);

CREATE TABLE IF NOT EXISTS mcp_capability (
    id BIGSERIAL PRIMARY KEY,
    server_id BIGINT NOT NULL,
    capability_code VARCHAR(150) NOT NULL,
    capability_name VARCHAR(200) NOT NULL,
    capability_type VARCHAR(50) NOT NULL,
    schema_json JSONB NOT NULL DEFAULT '{}'::jsonb,
    enabled BOOLEAN NOT NULL DEFAULT TRUE,
    version INTEGER NOT NULL DEFAULT 1,
    create_time TIMESTAMP,
    update_time TIMESTAMP,
    remark VARCHAR(500) DEFAULT '',
    create_by VARCHAR(64),
    update_by VARCHAR(64),
    CONSTRAINT uk_mcp_capability_code UNIQUE (server_id, capability_code),
    CONSTRAINT fk_mcp_capability_server_id FOREIGN KEY (server_id) REFERENCES mcp_server (id)
);

CREATE INDEX IF NOT EXISTS idx_mcp_server_import_type ON mcp_server (import_type);
CREATE INDEX IF NOT EXISTS idx_mcp_server_health_status ON mcp_server (health_status);
CREATE INDEX IF NOT EXISTS idx_mcp_capability_server_id ON mcp_capability (server_id);
CREATE INDEX IF NOT EXISTS idx_mcp_capability_type ON mcp_capability (capability_type);

COMMENT ON TABLE mcp_server IS 'MCP服务表';
COMMENT ON COLUMN mcp_server.id IS 'ID';
COMMENT ON COLUMN mcp_server.server_code IS '服务编码';
COMMENT ON COLUMN mcp_server.server_name IS '服务名称';
COMMENT ON COLUMN mcp_server.import_type IS '导入方式';
COMMENT ON COLUMN mcp_server.endpoint_url IS '服务端点地址';
COMMENT ON COLUMN mcp_server.package_name IS '安装包名称';
COMMENT ON COLUMN mcp_server.manifest_json IS 'Manifest声明JSON';
COMMENT ON COLUMN mcp_server.auth_type IS '鉴权类型';
COMMENT ON COLUMN mcp_server.secret_ref IS '密钥引用';
COMMENT ON COLUMN mcp_server.health_status IS '健康状态';
COMMENT ON COLUMN mcp_server.enabled IS '是否启用';
COMMENT ON COLUMN mcp_server.version IS '版本';
COMMENT ON COLUMN mcp_server.create_time IS '创建时间';
COMMENT ON COLUMN mcp_server.update_time IS '更新时间';
COMMENT ON COLUMN mcp_server.remark IS '备注';
COMMENT ON COLUMN mcp_server.create_by IS '创建者';
COMMENT ON COLUMN mcp_server.update_by IS '更新者';
COMMENT ON TABLE mcp_capability IS 'MCP能力表';
COMMENT ON COLUMN mcp_capability.id IS 'ID';
COMMENT ON COLUMN mcp_capability.server_id IS '所属服务ID';
COMMENT ON COLUMN mcp_capability.capability_code IS '能力编码';
COMMENT ON COLUMN mcp_capability.capability_name IS '能力名称';
COMMENT ON COLUMN mcp_capability.capability_type IS '能力类型';
COMMENT ON COLUMN mcp_capability.schema_json IS '能力Schema JSON';
COMMENT ON COLUMN mcp_capability.enabled IS '是否启用';
COMMENT ON COLUMN mcp_capability.version IS '版本';
COMMENT ON COLUMN mcp_capability.create_time IS '创建时间';
COMMENT ON COLUMN mcp_capability.update_time IS '更新时间';
COMMENT ON COLUMN mcp_capability.remark IS '备注';
COMMENT ON COLUMN mcp_capability.create_by IS '创建者';
COMMENT ON COLUMN mcp_capability.update_by IS '更新者';