-- Agent Orchestrator Database Schema
-- Version: 1.0
--
-- This schema supports:
-- - Agent registration and status
-- - Task queue management
-- - Run execution history
-- - Health monitoring samples
-- - Approval queue for risky actions
-- - Daily usage aggregation
-- - MCP tool usage tracking

-- =============================================================================
-- Core Tables
-- =============================================================================

-- Agent registry
CREATE TABLE IF NOT EXISTS agents (
    id TEXT PRIMARY KEY,
    tool TEXT NOT NULL,  -- 'claude_code', 'gemini_cli', 'codex', 'claude_sdk', 'openai_agents'
    worktree_path TEXT,
    branch TEXT,
    status TEXT DEFAULT 'idle',  -- 'idle', 'running', 'stuck', 'paused', 'terminated'
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Task queue
CREATE TABLE IF NOT EXISTS tasks (
    id TEXT PRIMARY KEY,
    description TEXT NOT NULL,
    task_type TEXT NOT NULL,  -- matches TaskType enum
    priority INTEGER DEFAULT 0,
    status TEXT DEFAULT 'pending',  -- 'pending', 'assigned', 'running', 'completed', 'failed', 'blocked'
    assigned_agent_id TEXT REFERENCES agents(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    error_message TEXT,
    -- Run-until-done support
    run_until_done BOOLEAN DEFAULT FALSE,  -- Enable retry mode
    max_retries INTEGER DEFAULT 3,  -- Maximum retry attempts
    attempt_count INTEGER DEFAULT 0,  -- Current attempt number
    last_attempt_at TIMESTAMP,  -- Timestamp of last attempt
    success_criteria TEXT  -- JSON: {"tests_pass": true, "lint_clean": true, etc.}
);

-- Run execution history
CREATE TABLE IF NOT EXISTS runs (
    id TEXT PRIMARY KEY,
    agent_id TEXT NOT NULL REFERENCES agents(id),
    task_id TEXT REFERENCES tasks(id),
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ended_at TIMESTAMP,
    outcome TEXT,  -- 'success', 'failure', 'blocked', 'timeout', 'terminated'
    tokens_input INTEGER DEFAULT 0,
    tokens_output INTEGER DEFAULT 0,
    cost_usd REAL DEFAULT 0.0,
    error_message TEXT,
    status_packet_json TEXT  -- Full StatusPacket as JSON
);

-- =============================================================================
-- Health Monitoring
-- =============================================================================

-- Health check samples (time-series)
CREATE TABLE IF NOT EXISTS health_samples (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    agent_id TEXT NOT NULL REFERENCES agents(id),
    sampled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_stdout_at TIMESTAMP,
    last_file_change_at TIMESTAMP,
    token_burn_rate REAL,  -- tokens/min
    error_count INTEGER DEFAULT 0,
    consecutive_same_error INTEGER DEFAULT 0,
    pending_permission_prompt BOOLEAN DEFAULT FALSE,
    edit_revert_cycles INTEGER DEFAULT 0,
    is_stuck BOOLEAN DEFAULT FALSE,
    stuck_reason TEXT  -- 'idle_but_burning_tokens', 'repeated_error_loop', 'edit_oscillation', 'awaiting_human_decision'
);

-- =============================================================================
-- Approval Queue
-- =============================================================================

-- Approval requests for risky actions
CREATE TABLE IF NOT EXISTS approvals (
    id TEXT PRIMARY KEY,
    agent_id TEXT NOT NULL REFERENCES agents(id),
    run_id TEXT REFERENCES runs(id),
    action_type TEXT NOT NULL,  -- 'file_edit', 'command', 'merge', 'deploy'
    target TEXT NOT NULL,  -- file path or command
    risk_level TEXT NOT NULL,  -- 'low', 'medium', 'high', 'critical'
    status TEXT DEFAULT 'pending',  -- 'pending', 'approved', 'rejected', 'timeout', 'skipped'
    requested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    decided_at TIMESTAMP,
    decided_by TEXT,  -- 'user', 'auto', 'timeout', 'auto_critical'
    decision_notes TEXT
);

-- =============================================================================
-- Usage Tracking
-- =============================================================================

-- Daily usage aggregates
CREATE TABLE IF NOT EXISTS usage_daily (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    date DATE NOT NULL,
    agent_id TEXT NOT NULL REFERENCES agents(id),
    tokens_input INTEGER DEFAULT 0,
    tokens_output INTEGER DEFAULT 0,
    cost_usd REAL DEFAULT 0.0,
    requests_count INTEGER DEFAULT 0,
    errors_count INTEGER DEFAULT 0,
    UNIQUE(date, agent_id)
);

-- MCP tool usage tracking
CREATE TABLE IF NOT EXISTS mcp_tool_usage (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    agent_id TEXT NOT NULL REFERENCES agents(id),
    run_id TEXT REFERENCES runs(id),
    tool_name TEXT NOT NULL,
    mcp_server TEXT NOT NULL,
    called_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    tokens_used INTEGER DEFAULT 0,
    duration_ms INTEGER,
    success BOOLEAN DEFAULT TRUE,
    error_message TEXT
);

-- =============================================================================
-- Decision Log (for audit trail)
-- =============================================================================

-- Significant decisions made by agents
CREATE TABLE IF NOT EXISTS decisions (
    id TEXT PRIMARY KEY,
    agent_id TEXT NOT NULL REFERENCES agents(id),
    task_id TEXT REFERENCES tasks(id),
    decision TEXT NOT NULL,
    rationale TEXT,
    reversible BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =============================================================================
-- Indexes for Common Queries
-- =============================================================================

-- Agent queries
CREATE INDEX IF NOT EXISTS idx_agents_status ON agents(status);
CREATE INDEX IF NOT EXISTS idx_agents_tool ON agents(tool);

-- Task queries
CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks(status);
CREATE INDEX IF NOT EXISTS idx_tasks_priority ON tasks(priority DESC);
CREATE INDEX IF NOT EXISTS idx_tasks_assigned ON tasks(assigned_agent_id);

-- Run queries
CREATE INDEX IF NOT EXISTS idx_runs_agent ON runs(agent_id, started_at DESC);
CREATE INDEX IF NOT EXISTS idx_runs_task ON runs(task_id);
CREATE INDEX IF NOT EXISTS idx_runs_outcome ON runs(outcome);

-- Health sample queries
CREATE INDEX IF NOT EXISTS idx_health_agent ON health_samples(agent_id, sampled_at DESC);
CREATE INDEX IF NOT EXISTS idx_health_stuck ON health_samples(is_stuck) WHERE is_stuck = TRUE;

-- Approval queries
CREATE INDEX IF NOT EXISTS idx_approvals_pending ON approvals(status) WHERE status = 'pending';
CREATE INDEX IF NOT EXISTS idx_approvals_agent ON approvals(agent_id);

-- Usage queries
CREATE INDEX IF NOT EXISTS idx_usage_date ON usage_daily(date DESC, agent_id);

-- MCP usage queries
CREATE INDEX IF NOT EXISTS idx_mcp_agent ON mcp_tool_usage(agent_id, called_at DESC);
CREATE INDEX IF NOT EXISTS idx_mcp_server ON mcp_tool_usage(mcp_server);
CREATE INDEX IF NOT EXISTS idx_mcp_tool ON mcp_tool_usage(tool_name);

-- =============================================================================
-- Triggers
-- =============================================================================

-- Update agents.updated_at on any change
CREATE TRIGGER IF NOT EXISTS trigger_agents_updated
    AFTER UPDATE ON agents
    FOR EACH ROW
BEGIN
    UPDATE agents SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

-- Update task started_at when status changes to 'running'
CREATE TRIGGER IF NOT EXISTS trigger_tasks_started
    AFTER UPDATE OF status ON tasks
    WHEN NEW.status = 'running' AND OLD.status != 'running'
BEGIN
    UPDATE tasks SET started_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

-- Update task completed_at when status changes to terminal state
CREATE TRIGGER IF NOT EXISTS trigger_tasks_completed
    AFTER UPDATE OF status ON tasks
    WHEN NEW.status IN ('completed', 'failed', 'blocked')
         AND OLD.status NOT IN ('completed', 'failed', 'blocked')
BEGIN
    UPDATE tasks SET completed_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

-- =============================================================================
-- CLI Session State (for rate limit persistence)
-- =============================================================================

-- CLI session tracking
CREATE TABLE IF NOT EXISTS cli_sessions (
    id TEXT PRIMARY KEY,
    agent_id TEXT NOT NULL REFERENCES agents(id),
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ended_at TIMESTAMP,
    request_count INTEGER DEFAULT 0,
    total_tokens INTEGER DEFAULT 0,
    total_cost_usd REAL DEFAULT 0.0,
    is_active BOOLEAN DEFAULT TRUE
);

-- CLI rate limit state snapshots
CREATE TABLE IF NOT EXISTS cli_rate_limit_state (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    agent_id TEXT NOT NULL,
    session_id TEXT REFERENCES cli_sessions(id),
    saved_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    recent_request_timestamps TEXT,  -- JSON array of timestamps
    recent_token_usage TEXT,  -- JSON array of [timestamp, tokens] pairs
    cooldown_until TIMESTAMP,
    consecutive_limit_hits INTEGER DEFAULT 0,
    UNIQUE(agent_id, session_id)
);

-- CLI session indexes
CREATE INDEX IF NOT EXISTS idx_cli_sessions_agent ON cli_sessions(agent_id, started_at DESC);
CREATE INDEX IF NOT EXISTS idx_cli_sessions_active ON cli_sessions(is_active) WHERE is_active = TRUE;
CREATE INDEX IF NOT EXISTS idx_cli_rate_state_agent ON cli_rate_limit_state(agent_id, saved_at DESC);

-- =============================================================================
-- Voting/Consensus System
-- =============================================================================

-- Voting sessions track a decision requiring multi-agent consensus
CREATE TABLE IF NOT EXISTS voting_sessions (
    id TEXT PRIMARY KEY,
    task_id TEXT REFERENCES tasks(id),
    topic TEXT NOT NULL,  -- What is being voted on
    description TEXT,  -- Detailed description for voters
    created_by TEXT NOT NULL,  -- Agent ID or 'orchestrator'
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deadline_at TIMESTAMP,  -- When voting closes
    status TEXT DEFAULT 'open',  -- 'open', 'closed', 'cancelled'
    closed_at TIMESTAMP,
    -- Quorum settings
    required_voters INTEGER DEFAULT 3,  -- Minimum voters for valid result
    quorum_type TEXT DEFAULT 'simple_majority',  -- 'simple_majority', 'supermajority', 'unanimous'
    supermajority_threshold REAL DEFAULT 0.67,  -- For supermajority type
    -- Result
    winning_option TEXT,  -- The winning choice after voting closes
    result_rationale TEXT  -- Explanation of result
);

-- Individual votes from agents
CREATE TABLE IF NOT EXISTS votes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id TEXT NOT NULL REFERENCES voting_sessions(id),
    agent_id TEXT NOT NULL REFERENCES agents(id),
    choice TEXT NOT NULL,  -- The option voted for
    confidence REAL DEFAULT 1.0,  -- 0.0-1.0 confidence in vote
    rationale TEXT,  -- Why the agent voted this way
    voted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(session_id, agent_id)  -- One vote per agent per session
);

-- Voting session indexes
CREATE INDEX IF NOT EXISTS idx_voting_sessions_status ON voting_sessions(status);
CREATE INDEX IF NOT EXISTS idx_voting_sessions_task ON voting_sessions(task_id);
CREATE INDEX IF NOT EXISTS idx_votes_session ON votes(session_id);
CREATE INDEX IF NOT EXISTS idx_votes_agent ON votes(agent_id);

-- =============================================================================
-- Project Management
-- =============================================================================

-- Projects track multi-session work with associated agents
CREATE TABLE IF NOT EXISTS projects (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    domain TEXT NOT NULL,  -- Primary TaskDomain value
    secondary_domains TEXT,  -- JSON array of secondary domains
    workspace_path TEXT,  -- Path to project workspace directory
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_accessed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status TEXT DEFAULT 'active',  -- 'active', 'paused', 'completed', 'archived'
    -- Planning state
    planning_complete BOOLEAN DEFAULT FALSE,
    planning_notes TEXT,  -- JSON with planning decisions
    -- Progress tracking
    total_tasks INTEGER DEFAULT 0,
    completed_tasks INTEGER DEFAULT 0
);

-- Project agents track which agents are assigned to a project
CREATE TABLE IF NOT EXISTS project_agents (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    agent_template_id TEXT NOT NULL,  -- Template ID from registry
    agent_instance_id TEXT REFERENCES agents(id),  -- Actual running agent if spawned
    role TEXT DEFAULT 'specialist',  -- 'lead', 'specialist', 'reviewer', 'support'
    status TEXT DEFAULT 'planned',  -- 'planned', 'active', 'paused', 'completed', 'removed'
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_active_at TIMESTAMP,
    -- Performance tracking
    tasks_completed INTEGER DEFAULT 0,
    tokens_used INTEGER DEFAULT 0,
    UNIQUE(project_id, agent_template_id)
);

-- Project context stores key-value pairs for project state
CREATE TABLE IF NOT EXISTS project_context (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    context_key TEXT NOT NULL,  -- e.g., 'conversation_summary', 'current_task', 'pending_decisions'
    context_value TEXT NOT NULL,  -- JSON or text value
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(project_id, context_key)
);

-- Project files track important files in a project
CREATE TABLE IF NOT EXISTS project_files (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    file_path TEXT NOT NULL,
    file_type TEXT,  -- 'source', 'config', 'output', 'resource'
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_modified_at TIMESTAMP,
    UNIQUE(project_id, file_path)
);

-- Project history tracks significant events
CREATE TABLE IF NOT EXISTS project_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    event_type TEXT NOT NULL,  -- 'created', 'agent_added', 'task_completed', 'milestone', 'resumed'
    event_data TEXT,  -- JSON with event details
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Project indexes
CREATE INDEX IF NOT EXISTS idx_projects_status ON projects(status);
CREATE INDEX IF NOT EXISTS idx_projects_domain ON projects(domain);
CREATE INDEX IF NOT EXISTS idx_projects_accessed ON projects(last_accessed_at DESC);
CREATE INDEX IF NOT EXISTS idx_project_agents_project ON project_agents(project_id);
CREATE INDEX IF NOT EXISTS idx_project_agents_template ON project_agents(agent_template_id);
CREATE INDEX IF NOT EXISTS idx_project_context_project ON project_context(project_id);
CREATE INDEX IF NOT EXISTS idx_project_files_project ON project_files(project_id);
CREATE INDEX IF NOT EXISTS idx_project_history_project ON project_history(project_id, created_at DESC);
