# ❌ WRONG - Two parents
bigquery_agent = Agent(sub_agents=[alyac_family_agent, ...])
conversational_agent = Agent(sub_agents=[alyac_family_agent, ...])  # ERROR!

# ✅ CORRECT - Single parent
# bigquery_agent: Has domain expert sub_agents
bigquery_agent = Agent(
    name="general_orchestrator",
    sub_agents=[alyac_family_agent, security_agent, marketing_agent, conversion_agent]
)

# conversational_analytics_agent: No sub_agents (tools only)
conversational_agent = Agent(
    name="conversational_analyst",
    tools=[ask_data_insights, search_catalog, google_search],
    sub_agents=[]  # Empty - uses AI tools instead
)

def select_agent_for_request(
    agent_type: Optional[str],  # "sql" or "conversational"
    message: str,
    conversation_history: Iterable[Tuple[str, Optional[str]]]
) -> Tuple[AgentInfo, str, str]:
    """
    Routes requests based on:
    1. Explicit agent_type selection
    2. Keyword matching in message content
    """

// frontend/src/App.tsx:293-299
const stopStream = apiService.streamQuery({
  query: trimmed,
  user_id: 'demo-user',
  session_id: 'demo-session',
  agent_type: agentType  // ✅ Only agent_type is sent, NOT domain!
})

WHERE detection_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 MONTH)

400 POST: TIMESTAMP_SUB does not support the MONTH date part
when the argument is TIMESTAMP type

"## SQL 작성 지침\n"
"- 시간 필터: **반드시 DAY 단위만 사용**\n"
"  - ✅ 올바름: `TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)` (1개월)\n"
"  - ❌ 금지: `TIMESTAMP_SUB(..., INTERVAL 1 MONTH)` → TIMESTAMP 타입에서 MONTH 지원 안 됨\n"

# adk-backend/src/adk_backend/api/chat.py
from google.adk.plugins import ReflectAndRetryToolPlugin

runner = InMemoryRunner(
    app_name="ADK Chat Stream",
    agent=agent,
    plugins=[
        ReflectAndRetryToolPlugin(
            max_retries=2,  # Total 3 attempts
            throw_exception_if_retry_exceeded=True,
        ),
    ],
)

User Query: "최근 한 달간 스미싱 TOP5"
    ↓
[1st Defense] Instruction (90% prevention)
AI generates: INTERVAL 30 DAY ✅
    ↓
BigQuery executes successfully (no retry needed)
    ↓
User receives results (fast)

[1st Defense] Instruction missed
AI generates: INTERVAL 1 MONTH ❌
    ↓
BigQuery error
    ↓
[2nd Defense] ReflectAndRetryToolPlugin (80% recovery)
Plugin: "Error detected, feedback to AI..."
    ↓
AI corrects: INTERVAL 30 DAY ✅
    ↓
BigQuery retry succeeds
    ↓
User receives results (slightly slower)

# In bigquery_agent instruction
"""
## 도메인 에이전트 위임 규칙
- `alyac_family_analyst`: 악성 앱·피싱 메시지·ALYac Family 고유 지표
- `security_analyst`: 스미싱, FCM 보안 이벤트, 악성앱/위협 경보
- `marketing_analyst`: 캠페인, 세그먼트, 가족 초대, 구독 전환
- `conversion_analyst`: 구독/퍼널/리텐션/코호트 등 SaaS 핵심 지표

위임이 필요할 때는 반드시 `transfer_to_agent`를 사용해 해당 이름을 호출하고,
응답을 사용자에게 요약/보완하세요.
"""

User: "최근 악성 앱 Top 5는?"
    ↓
bigquery_agent detects keyword "악성 앱"
    ↓
transfer_to_agent("alyac_family_analyst")
    ↓
alyac_family_agent executes BigQuery tools
    ↓
Result → bigquery_agent
    ↓
bigquery_agent summarizes and returns to user

AGENT_REGISTRY = {
    "conversational": AgentInfo(
        key="conversational",
        display_name="대화형 데이터 분석가",
        agent=conversational_analytics_agent,
        focus="자연어 데이터 분석",
        keywords=["자연어", "대화", "인사이트", "AI분석", ...],
    ),
    "general": AgentInfo(
        key="general",
        display_name="범용 데이터 분석가",
        agent=bigquery_agent,
        focus="범용 데이터 분석",
        keywords=[],
    ),
    "alyac_family": AgentInfo(
        key="alyac_family",
        display_name="ALYac Family 보안 분석가",
        agent=alyac_family_agent,
        focus="악성 앱 및 피싱 분석",
        keywords=["악성", "malware", "phishing", ...],
    ),
    # ... security, marketing, conversion
}

def get_active_agents() -> List[AgentInfo]:
    """Returns all agents with active=True"""
    return [info for info in AGENT_REGISTRY.values() if info.active]

# 1. Create domain expert agent
new_domain_agent = Agent(
    name="new_domain_analyst",
    description="Domain-specific analysis",
    tools=[bigquery_list_templates, bigquery_render_template,
           bigquery_dry_run, bigquery_execute],
    # NO sub_agents (domain experts are leaf nodes)
)

# 2. Add to bigquery_agent sub_agents
bigquery_agent = Agent(
    name="general_orchestrator",
    sub_agents=[
        alyac_family_agent,
        security_agent,
        marketing_agent,
        conversion_agent,
        new_domain_agent,  # ✅ Add here
    ]
)

# 3. Register in registry
AGENT_REGISTRY["new_domain"] = AgentInfo(
    key="new_domain",
    agent=new_domain_agent,
    keywords=["keyword1", "keyword2"],
)

# 4. Update agent_selector routing logic

from google.adk.plugins import ReflectAndRetryToolPlugin

class BigQueryErrorRecoveryPlugin(ReflectAndRetryToolPlugin):
    """Custom error detection for BigQuery warnings"""

    async def extract_error_from_result(self, *, tool, tool_args,
                                       tool_context, result):
        if isinstance(result, dict):
            # Detect warnings in successful responses
            if result.get('warnings'):
                return {
                    'error': 'BigQuery Warning',
                    'warnings': result['warnings'],
                    'suggestion': '경고를 해결하여 최적화된 쿼리를 생성하세요.'
                }
        return None  # No error

# Apply to runner
runner = InMemoryRunner(
    agent=agent,
    plugins=[BigQueryErrorRecoveryPlugin(max_retries=3)]
)

User Query
    ↓
conversational_analytics_agent (orchestrator)
    ↓
transfer_to_agent("general_orchestrator")
    ↓
bigquery_agent (receives delegation)
    ↓
transfer_to_agent("alyac_family_analyst")
    ↓
alyac_family_agent (executes)
    ↓
Result → bigquery_agent → conversational_agent → User

# Check for multiple parent assignments
grep -r "sub_agents=\[.*alyac_family" adk-backend/src/adk_backend/agents/
# Should find ONLY in bigquery_agent.py

# Test keyword matching
echo '{"agent_type":"sql","message":"악성 앱 탐지"}' | \
  python -c "from adk_backend.services.agent_selector import select_agent_for_request; \
             import json, sys; \
             data=json.load(sys.stdin); \
             info, reason, _ = select_agent_for_request(
                agent_type=data['agent_type'],
                message=data['message'],
                conversation_history=[]
             ); \
             print(f'Selected: {info.key}, Reason: {reason}')"

# Manually trigger SQL error
cd adk-backend
python -c "
from src.adk_backend.tools.bigquery import bigquery_dry_run
try:
    bigquery_dry_run('SELECT * FROM table WHERE time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 MONTH)')
except Exception as e:
    print(f'Expected error: {e}')
"

adk-backend/src/adk_backend/
├── agents/
│   ├── base/
│   │   ├── bigquery_agent.py (SQL orchestrator, has sub_agents)
│   │   └── conversational_analytics_agent.py (AI orchestrator, no sub_agents)
│   ├── alyac_family_domain_expert.py
│   ├── security_domain_expert.py
│   ├── marketing_domain_expert.py
│   ├── conversion_domain_expert.py
│   └── registry.py (AgentInfo registration)
├── services/
│   └── agent_selector.py (routing logic)
├── tools/
│   ├── bigquery.py (SQL tools)
│   └── conversational_analytics.py (AI analysis tools)
└── api/
    └── chat.py (SSE streaming, ReflectAndRetryToolPlugin)

Need to handle agent request?
    ↓
Q: User mentioned specific domain keywords?
    ├─ YES → Route to domain expert agent
    │         └─ Use agent_selector.py keyword matching
    │
    └─ NO → Default to conversational_analytics_agent
            └─ Let AI decide delegation

Q: Agent architecture change needed?
    ↓
First check: Does it violate Single Parent Rule?
    ├─ YES → Redesign to use tools or indirect delegation
    └─ NO → Proceed with implementation

Q: SQL errors occurring?
    ↓
Layer 1: Enhanced instructions (preventive)
    ├─ Success → Fast response
    └─ Fail → Layer 2: ReflectAndRetryToolPlugin (reactive)
              └─ Auto-retry with AI correction