-- T24: auto-repair executor Docker restart MCP Gateway grant
-- 目的：讓已由 PlayBook 標記為 requires_approval=false 的安全容器重啟，
--       透過 AwoooP MCP Gateway + Gate 5 policy projection 執行與稽核。
-- 邊界：僅授權 ssh_docker_restart/write；複雜 shell、systemctl、prune 仍不得自動執行。

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', 'Auto repair diagnostics and safe Docker container restart through AwoooP MCP Gateway',
        'allowed_scopes', jsonb_build_array('read', 'write'),
        'requires_gate5_for_scopes', jsonb_build_array('write'),
        'write_scope_constraints', jsonb_build_object(
            'allowed_tools', jsonb_build_array('ssh_docker_restart'),
            'required_playbook_requires_approval', false,
            'required_trust_score_min', 0.8,
            'forbidden_shell_patterns', jsonb_build_array('command_substitution', 'pipe', 'fallback_shell', 'systemd', 'prune')
        ),
        'stage', 't24_auto_repair_docker_restart_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,
        1,
        'active',
        body_json,
        encode(digest(body_json::text, 'sha256'), 'hex'),
        'v1.1',
        'migration:t24_auto_repair_docker_restart_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 = 1
      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
),
upsert_tool AS (
    INSERT INTO awooop_mcp_tool_registry (
        project_id,
        tool_name,
        tool_type,
        description,
        allowed_scopes,
        environment_tags,
        is_active,
        updated_at
    )
    VALUES (
        'awoooi',
        'ssh_docker_restart',
        'mcp_server',
        'Policy-approved Docker container restart over SSH for auto-repair',
        '["write"]'::jsonb,
        '{"env": "prod"}'::jsonb,
        TRUE,
        NOW()
    )
    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, allowed_scopes
),
upsert_grant 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:t24_auto_repair_docker_restart_gateway',
        allowed_scopes,
        NULL,
        FALSE,
        NULL,
        NULL
    FROM upsert_tool
    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_docker_restart_gateway',
    (SELECT count(*) FROM upsert_pointer) AS active_contract_rows,
    (SELECT count(*) FROM upsert_tool) AS tool_rows,
    (SELECT count(*) FROM upsert_grant) AS grant_rows;
