-- T7: awoooi read-only MCP Gateway seed
-- 目的：讓決策前感官 MCP 能通過 AwoooP Gateway Gate 2/3，產生 first-class audit。
-- 邊界：只授權 read scope；不授權 restart/delete/scale/apply/rollback 等 write/admin 工具。

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

WITH agent_seed(agent_id, display_name) AS (
    VALUES
        ('pre_decision_investigator', 'Pre-decision Investigator'),
        ('post_execution_verifier', 'Post-execution Verifier')
),
agent_body AS (
    SELECT
        agent_id,
        jsonb_build_object(
            'schema_version', 'awooop_agent_contract_v1',
            'agent_id', agent_id,
            'display_name', display_name,
            'project_id', 'awoooi',
            'purpose', 'Read-only MCP sensing through AwoooP Gateway',
            'allowed_scopes', jsonb_build_array('read'),
            'forbidden_scopes', jsonb_build_array('write', 'admin'),
            'stage', 't7_mcp_gateway_read_sense'
        ) AS body_json
    FROM agent_seed
),
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',
        agent_id,
        1,
        0,
        'active',
        body_json,
        encode(digest(body_json::text, 'sha256'), 'hex'),
        'v1.0',
        'migration:t7_mcp_gateway_read_seed',
        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 IN (SELECT agent_id FROM agent_seed)
      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 'active_agent_contracts', count(*) FROM upsert_pointer;

WITH read_tools(tool_name, description) AS (
    VALUES
        ('k8s_get_pod_logs', 'Kubernetes pod logs read'),
        ('k8s_get_events', 'Kubernetes events read'),
        ('k8s_describe_pod', 'Kubernetes pod describe read'),
        ('k8s_get_hpa_status', 'Kubernetes HPA status read'),
        ('k8s_get_node_conditions', 'Kubernetes node conditions read'),
        ('ssh_diagnose', 'SSH host 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'),
        ('prometheus_query', 'Prometheus instant query read'),
        ('prometheus_query_range', 'Prometheus range query read'),
        ('prometheus_get_alert_history', 'Prometheus alert history read'),
        ('gold_metrics', 'SigNoz gold metrics read'),
        ('trace_url', 'SigNoz trace URL read'),
        ('system_metrics', 'SigNoz system metrics read'),
        ('query_logs', 'SigNoz logs read'),
        ('error_logs_summary', 'SigNoz error logs summary read'),
        ('list_approvals', 'Approval records read'),
        ('get_approval', 'Approval detail read'),
        ('list_incidents', 'Incident records read'),
        ('list_timeline', 'Timeline records read'),
        ('read_file', 'Filesystem allowlisted file read'),
        ('list_directory', 'Filesystem allowlisted directory read'),
        ('search_in_file', 'Filesystem allowlisted file search'),
        ('list_dashboards', 'Grafana dashboards read'),
        ('get_dashboard', 'Grafana dashboard read'),
        ('get_panel_data', 'Grafana panel data read'),
        ('generate_dashboard_url', 'Grafana dashboard URL read'),
        ('search_runbook', 'Runbook semantic search read'),
        ('get_index_stats', 'Runbook index stats read'),
        ('argocd_list_apps', 'ArgoCD apps read'),
        ('argocd_get_app_status', 'ArgoCD app status read'),
        ('argocd_get_sync_history', 'ArgoCD sync history read'),
        ('sentry_list_issues', 'Sentry issues read'),
        ('sentry_get_issue', 'Sentry issue detail read'),
        ('sentry_search_issues', 'Sentry issue search 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
),
grant_agents(agent_id) AS (
    VALUES
        ('pre_decision_investigator'),
        ('post_execution_verifier')
),
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',
        grant_agents.agent_id,
        upsert_tools.tool_id,
        'migration:t7_mcp_gateway_read_seed',
        '["read"]'::jsonb,
        NULL,
        FALSE,
        NULL,
        NULL
    FROM upsert_tools
    CROSS JOIN grant_agents
    ON CONFLICT (project_id, agent_id, tool_id)
    DO UPDATE SET
        granted_scopes = EXCLUDED.granted_scopes,
        expires_at = NULL,
        is_revoked = FALSE,
        revoked_at = NULL,
        revoked_by = NULL
    RETURNING grant_id
)
SELECT
    'awoooi_read_tools',
    (SELECT count(*) FROM upsert_tools) AS tool_rows,
    (SELECT count(*) FROM upsert_grants) AS grant_rows;
