-- T9: approved SSH execution MCP Gateway seed
-- 目的：讓 Telegram/Approval 已批准的 SSH 修復動作通過 AwoooP Gateway 五閘門。
-- 邊界：只授權 approval_executor；write/admin 仍需 Gate 5 短效 approval key。

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

WITH agent_body AS (
    SELECT jsonb_build_object(
        'schema_version', 'awooop_agent_contract_v1',
        'agent_id', 'approval_executor',
        'display_name', 'Approval Executor',
        'project_id', 'awoooi',
        'purpose', 'Approved SSH execution through AwoooP MCP Gateway',
        'allowed_scopes', jsonb_build_array('read', 'write', 'admin'),
        'requires_gate5_for_scopes', jsonb_build_array('write', 'admin'),
        'stage', 't9_ssh_approval_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',
        'approval_executor',
        1,
        0,
        'active',
        body_json,
        encode(digest(body_json::text, 'sha256'), 'hex'),
        'v1.0',
        'migration:t9_ssh_approval_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 = 'approval_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 'approval_executor_active_contracts', count(*) FROM upsert_pointer;

WITH gateway_tools(tool_name, description, required_scope) AS (
    VALUES
        ('ssh_diagnose', 'SSH host diagnosis read', 'read'),
        ('ssh_docker_restart', 'Approved Docker container restart over SSH', 'write'),
        ('ssh_docker_compose_restart', 'Approved Docker Compose service restart over SSH', 'write'),
        ('ssh_systemctl_restart', 'Approved systemd service restart over SSH', 'write'),
        ('ssh_clear_docker_logs', 'Approved Docker log truncation over SSH', 'write'),
        ('ssh_renew_ssl', 'Approved certbot renewal over SSH', 'write'),
        ('ssh_reload_nginx', 'Approved nginx config test and reload over SSH', 'write'),
        ('ssh_docker_prune', 'Approved Docker prune over SSH with provider disk guard', 'admin')
),
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,
        jsonb_build_array(required_scope),
        '{"env": "prod"}'::jsonb,
        TRUE,
        NOW()
    FROM gateway_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',
        'approval_executor',
        tool_id,
        'migration:t9_ssh_approval_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
    'approval_executor_ssh_gateway',
    (SELECT count(*) FROM upsert_tools) AS tool_rows,
    (SELECT count(*) FROM upsert_grants) AS grant_rows;
