-- T23: auto-repair executor read-only MCP Gateway seed
-- 目的：讓 YAML_RULE/PlayBook 的只讀 SSH 診斷步驟經過 AwoooP MCP Gateway。
-- 邊界：只授權 read scope；write/admin SSH 工具仍必須走 approval_executor + Gate 5。

SELECT set_config('app.project_id', 'awoooi', FALSE);

WITH agent_body AS (
    SELECT jsonb_build_object(
        'schema_version', 'awooop_agent_contract_v1',
        'agent_id', 'auto_repair_executor',
        'display_name', 'Auto Repair Executor',
        'project_id', 'awoooi',
        'purpose', 'Read-only auto-repair diagnostics through AwoooP MCP Gateway',
        'allowed_scopes', jsonb_build_array('read'),
        'forbidden_scopes', jsonb_build_array('write', 'admin'),
        'stage', 't23_auto_repair_diagnostic_gateway'
    ) AS body_json
),
inserted_revision AS (
    INSERT INTO awooop_contract_revisions (
        project_id,
        contract_family,
        contract_id,
        version_major,
        version_minor,
        lifecycle_status,
        body_json,
        body_hash,
        body_schema_version,
        publisher_id,
        published_at
    )
    SELECT
        'awoooi',
        'agent',
        'auto_repair_executor',
        1,
        0,
        'active',
        body_json,
        encode(digest(body_json::text, 'sha256'), 'hex'),
        'v1.0',
        'migration:t23_auto_repair_executor_read_gateway',
        NOW()
    FROM agent_body
    ON CONFLICT (project_id, contract_family, contract_id, version_major, version_minor)
    DO NOTHING
    RETURNING revision_id, project_id, contract_family, contract_id
),
chosen_revision AS (
    SELECT revision_id, project_id, contract_family, contract_id
    FROM inserted_revision
    UNION ALL
    SELECT revision_id, project_id, contract_family, contract_id
    FROM awooop_contract_revisions
    WHERE project_id = 'awoooi'
      AND contract_family = 'agent'
      AND contract_id = 'auto_repair_executor'
      AND version_major = 1
      AND version_minor = 0
      AND lifecycle_status = 'active'
),
upsert_pointer AS (
    INSERT INTO awooop_active_revisions (
        project_id,
        contract_family,
        contract_id,
        active_revision_id,
        updated_at
    )
    SELECT DISTINCT ON (project_id, contract_family, contract_id)
        project_id,
        contract_family,
        contract_id,
        revision_id,
        NOW()
    FROM chosen_revision
    ORDER BY project_id, contract_family, contract_id, revision_id
    ON CONFLICT (project_id, contract_family, contract_id)
    DO UPDATE SET
        active_revision_id = EXCLUDED.active_revision_id,
        updated_at = NOW()
    RETURNING contract_id
)
SELECT 'auto_repair_executor_active_contracts', count(*) FROM upsert_pointer;

WITH read_tools(tool_name, description) AS (
    VALUES
        ('ssh_diagnose', 'SSH host/container diagnosis read'),
        ('ssh_get_top_processes', 'SSH top processes read'),
        ('ssh_get_disk_usage', 'SSH disk usage read'),
        ('ssh_get_memory_info', 'SSH memory info read'),
        ('ssh_get_container_logs', 'SSH container logs read'),
        ('ssh_get_container_status', 'SSH container status read'),
        ('ssh_get_service_status', 'SSH service status read'),
        ('ssh_check_port', 'SSH port check read'),
        ('ssh_get_nginx_error_log', 'SSH nginx error log read'),
        ('ssh_get_swap_info', 'SSH swap info read')
),
upsert_tools AS (
    INSERT INTO awooop_mcp_tool_registry (
        project_id,
        tool_name,
        tool_type,
        description,
        allowed_scopes,
        environment_tags,
        is_active,
        updated_at
    )
    SELECT
        'awoooi',
        tool_name,
        'mcp_server',
        description,
        '["read"]'::jsonb,
        '{"env": "prod"}'::jsonb,
        TRUE,
        NOW()
    FROM read_tools
    ON CONFLICT (project_id, tool_name)
    DO UPDATE SET
        description = EXCLUDED.description,
        allowed_scopes = EXCLUDED.allowed_scopes,
        environment_tags = EXCLUDED.environment_tags,
        is_active = TRUE,
        updated_at = NOW()
    RETURNING tool_id, tool_name, allowed_scopes
),
upsert_grants AS (
    INSERT INTO awooop_mcp_grants (
        project_id,
        agent_id,
        tool_id,
        granted_by,
        granted_scopes,
        expires_at,
        is_revoked,
        revoked_at,
        revoked_by
    )
    SELECT
        'awoooi',
        'auto_repair_executor',
        tool_id,
        'migration:t23_auto_repair_executor_read_gateway',
        allowed_scopes,
        NULL,
        FALSE,
        NULL,
        NULL
    FROM upsert_tools
    ON CONFLICT (project_id, agent_id, tool_id)
    DO UPDATE SET
        granted_by = EXCLUDED.granted_by,
        granted_scopes = EXCLUDED.granted_scopes,
        expires_at = NULL,
        is_revoked = FALSE,
        revoked_at = NULL,
        revoked_by = NULL
    RETURNING grant_id
)
SELECT
    'auto_repair_executor_read_gateway',
    (SELECT count(*) FROM upsert_tools) AS tool_rows,
    (SELECT count(*) FROM upsert_grants) AS grant_rows;
