"""
OrchestratorDB - SQLite database layer for the orchestrator.

Provides methods for storing and retrieving:
- Agents and their status
- Tasks and execution history
- Health monitoring samples
- Approval requests
- Usage tracking
"""

import json
import sqlite3
from contextlib import contextmanager
from datetime import datetime, date
from pathlib import Path
from typing import Any, Generator, Optional


# Register datetime/date adapters to avoid Python 3.12 deprecation warnings
def _adapt_datetime(dt: datetime) -> str:
    """Convert datetime to ISO format string."""
    return dt.isoformat()


def _adapt_date(d: date) -> str:
    """Convert date to ISO format string."""
    return d.isoformat()


def _convert_datetime(val: bytes) -> datetime:
    """Convert ISO format bytes to datetime."""
    return datetime.fromisoformat(val.decode())


def _convert_date(val: bytes) -> date:
    """Convert ISO format bytes to date."""
    return date.fromisoformat(val.decode())


# Register adapters and converters
sqlite3.register_adapter(datetime, _adapt_datetime)
sqlite3.register_adapter(date, _adapt_date)
sqlite3.register_converter("datetime", _convert_datetime)
sqlite3.register_converter("date", _convert_date)

from .models import (
    Agent,
    Task,
    Run,
    HealthSample,
    Approval,
    UsageDaily,
    MCPToolUsage,
    Decision,
    VotingSession,
    Vote,
)
from ..journal.status_packet import StatusPacket


class OrchestratorDB:
    """
    SQLite database manager for the orchestrator.

    Handles all database operations including schema initialization,
    CRUD operations for all entity types, and complex queries.
    """

    def __init__(self, db_path: Path | str) -> None:
        """
        Initialize the database.

        Args:
            db_path: Path to the SQLite database file (Path or string)
        """
        self.db_path = Path(db_path) if isinstance(db_path, str) else db_path
        self._ensure_directory()
        self._init_schema()

    def _ensure_directory(self) -> None:
        """Ensure the database directory exists."""
        self.db_path.parent.mkdir(parents=True, exist_ok=True)

    @contextmanager
    def connection(self) -> Generator[sqlite3.Connection, None, None]:
        """
        Get a database connection with automatic commit/rollback.

        Usage:
            with db.connection() as conn:
                conn.execute(...)
        """
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row
        try:
            yield conn
            conn.commit()
        except Exception:
            conn.rollback()
            raise
        finally:
            conn.close()

    def _init_schema(self) -> None:
        """Initialize the database schema."""
        schema_path = Path(__file__).parent / "schema.sql"
        with open(schema_path) as f:
            schema_sql = f.read()

        with self.connection() as conn:
            conn.executescript(schema_sql)

        # Run migrations for existing databases
        self._run_migrations()

    def _run_migrations(self) -> None:
        """Run migrations to update existing databases with new columns."""
        with self.connection() as conn:
            # Check if tasks table needs run_until_done columns
            cursor = conn.execute("PRAGMA table_info(tasks)")
            columns = {row[1] for row in cursor.fetchall()}

            # Migration: Add run_until_done columns to tasks table
            if "run_until_done" not in columns:
                conn.execute(
                    "ALTER TABLE tasks ADD COLUMN run_until_done BOOLEAN DEFAULT FALSE"
                )
            if "max_retries" not in columns:
                conn.execute(
                    "ALTER TABLE tasks ADD COLUMN max_retries INTEGER DEFAULT 3"
                )
            if "attempt_count" not in columns:
                conn.execute(
                    "ALTER TABLE tasks ADD COLUMN attempt_count INTEGER DEFAULT 0"
                )
            if "last_attempt_at" not in columns:
                conn.execute(
                    "ALTER TABLE tasks ADD COLUMN last_attempt_at TIMESTAMP"
                )
            if "success_criteria" not in columns:
                conn.execute(
                    "ALTER TABLE tasks ADD COLUMN success_criteria TEXT"
                )

    # =========================================================================
    # Agent Operations
    # =========================================================================

    def create_agent(self, agent: Agent) -> None:
        """Create a new agent."""
        with self.connection() as conn:
            conn.execute(
                """
                INSERT INTO agents (id, tool, worktree_path, branch, status)
                VALUES (?, ?, ?, ?, ?)
                """,
                (
                    agent.id,
                    agent.tool,
                    agent.worktree_path,
                    agent.branch,
                    agent.status,
                ),
            )

    def get_agent(self, agent_id: str) -> Optional[Agent]:
        """Get an agent by ID."""
        with self.connection() as conn:
            row = conn.execute(
                "SELECT * FROM agents WHERE id = ?", (agent_id,)
            ).fetchone()
            return Agent.from_row(dict(row)) if row else None

    def get_agents_by_status(self, status: str) -> list[Agent]:
        """Get all agents with a specific status."""
        with self.connection() as conn:
            rows = conn.execute(
                "SELECT * FROM agents WHERE status = ?", (status,)
            ).fetchall()
            return [Agent.from_row(dict(row)) for row in rows]

    def get_all_agents(self) -> list[Agent]:
        """Get all agents."""
        with self.connection() as conn:
            rows = conn.execute("SELECT * FROM agents").fetchall()
            return [Agent.from_row(dict(row)) for row in rows]

    def update_agent_status(self, agent_id: str, status: str) -> None:
        """Update an agent's status."""
        with self.connection() as conn:
            conn.execute(
                "UPDATE agents SET status = ? WHERE id = ?",
                (status, agent_id),
            )

    def delete_agent(self, agent_id: str) -> None:
        """Delete an agent."""
        with self.connection() as conn:
            conn.execute("DELETE FROM agents WHERE id = ?", (agent_id,))

    # =========================================================================
    # Task Operations
    # =========================================================================

    def create_task(self, task: Task) -> None:
        """Create a new task."""
        with self.connection() as conn:
            conn.execute(
                """
                INSERT INTO tasks (
                    id, description, task_type, priority, status, assigned_agent_id,
                    run_until_done, max_retries, attempt_count, success_criteria
                )
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """,
                (
                    task.id,
                    task.description,
                    task.task_type,
                    task.priority,
                    task.status,
                    task.assigned_agent_id,
                    task.run_until_done,
                    task.max_retries,
                    task.attempt_count,
                    task.success_criteria,
                ),
            )

    def get_task(self, task_id: str) -> Optional[Task]:
        """Get a task by ID."""
        with self.connection() as conn:
            row = conn.execute(
                "SELECT * FROM tasks WHERE id = ?", (task_id,)
            ).fetchone()
            return Task.from_row(dict(row)) if row else None

    def get_pending_tasks(self, limit: int = 10) -> list[Task]:
        """Get pending tasks ordered by priority."""
        with self.connection() as conn:
            rows = conn.execute(
                """
                SELECT * FROM tasks
                WHERE status = 'pending'
                ORDER BY priority DESC, created_at ASC
                LIMIT ?
                """,
                (limit,),
            ).fetchall()
            return [Task.from_row(dict(row)) for row in rows]

    def assign_task(self, task_id: str, agent_id: str) -> None:
        """Assign a task to an agent."""
        with self.connection() as conn:
            conn.execute(
                """
                UPDATE tasks SET
                    assigned_agent_id = ?,
                    status = 'assigned'
                WHERE id = ?
                """,
                (agent_id, task_id),
            )

    def update_task_status(
        self, task_id: str, status: str, error_message: Optional[str] = None
    ) -> None:
        """Update a task's status."""
        with self.connection() as conn:
            conn.execute(
                """
                UPDATE tasks SET
                    status = ?,
                    error_message = ?
                WHERE id = ?
                """,
                (status, error_message, task_id),
            )

    def record_task_attempt(self, task_id: str) -> int:
        """
        Record a task execution attempt.

        Increments attempt_count and updates last_attempt_at.

        Args:
            task_id: Task identifier

        Returns:
            The new attempt count
        """
        with self.connection() as conn:
            conn.execute(
                """
                UPDATE tasks SET
                    attempt_count = attempt_count + 1,
                    last_attempt_at = CURRENT_TIMESTAMP
                WHERE id = ?
                """,
                (task_id,),
            )
            row = conn.execute(
                "SELECT attempt_count FROM tasks WHERE id = ?",
                (task_id,),
            ).fetchone()
            return row["attempt_count"] if row else 0

    def get_retryable_tasks(self, limit: int = 10) -> list[Task]:
        """
        Get failed tasks that are eligible for retry.

        Returns tasks where:
        - run_until_done is True
        - status is 'failed'
        - attempt_count < max_retries

        Args:
            limit: Maximum number of tasks to return

        Returns:
            List of retryable tasks, ordered by priority
        """
        with self.connection() as conn:
            rows = conn.execute(
                """
                SELECT * FROM tasks
                WHERE run_until_done = TRUE
                  AND status = 'failed'
                  AND attempt_count < max_retries
                ORDER BY priority DESC, last_attempt_at ASC
                LIMIT ?
                """,
                (limit,),
            ).fetchall()
            return [Task.from_row(dict(row)) for row in rows]

    def reset_task_for_retry(self, task_id: str) -> None:
        """
        Reset a task's status to 'pending' for retry.

        Clears error_message and assigned_agent_id to allow reassignment.

        Args:
            task_id: Task identifier
        """
        with self.connection() as conn:
            conn.execute(
                """
                UPDATE tasks SET
                    status = 'pending',
                    error_message = NULL,
                    assigned_agent_id = NULL
                WHERE id = ?
                """,
                (task_id,),
            )

    def mark_task_exhausted(self, task_id: str, final_error: str) -> None:
        """
        Mark a task as exhausted (all retries failed).

        Sets status to 'failed' and records the final error.

        Args:
            task_id: Task identifier
            final_error: Final error message after all retries
        """
        with self.connection() as conn:
            conn.execute(
                """
                UPDATE tasks SET
                    status = 'failed',
                    error_message = ?
                WHERE id = ?
                """,
                (f"[Exhausted after max retries] {final_error}", task_id),
            )

    # =========================================================================
    # Run Operations
    # =========================================================================

    def start_run(self, run_id: str, agent_id: str, task_id: Optional[str] = None) -> str:
        """Start a new run and return its ID."""
        with self.connection() as conn:
            conn.execute(
                """
                INSERT INTO runs (id, agent_id, task_id)
                VALUES (?, ?, ?)
                """,
                (run_id, agent_id, task_id),
            )
        return run_id

    def complete_run(
        self,
        run_id: str,
        outcome: str,
        packet: StatusPacket,
        error_message: Optional[str] = None,
    ) -> None:
        """Complete a run with status packet."""
        with self.connection() as conn:
            conn.execute(
                """
                UPDATE runs SET
                    ended_at = ?,
                    outcome = ?,
                    tokens_input = ?,
                    tokens_output = ?,
                    cost_usd = ?,
                    error_message = ?,
                    status_packet_json = ?
                WHERE id = ?
                """,
                (
                    datetime.now(),
                    outcome,
                    packet.artifacts.tokens_input,
                    packet.artifacts.tokens_output,
                    packet.artifacts.cost_usd,
                    error_message,
                    packet.to_json(),
                    run_id,
                ),
            )

    def get_run(self, run_id: str) -> Optional[Run]:
        """Get a run by ID."""
        with self.connection() as conn:
            row = conn.execute(
                "SELECT * FROM runs WHERE id = ?", (run_id,)
            ).fetchone()
            return Run.from_row(dict(row)) if row else None

    def get_agent_runs(
        self, agent_id: str, limit: int = 10
    ) -> list[Run]:
        """Get recent runs for an agent."""
        with self.connection() as conn:
            rows = conn.execute(
                """
                SELECT * FROM runs
                WHERE agent_id = ?
                ORDER BY started_at DESC
                LIMIT ?
                """,
                (agent_id, limit),
            ).fetchall()
            return [Run.from_row(dict(row)) for row in rows]

    def get_latest_run(self, agent_id: str) -> Optional[Run]:
        """Get the most recent run for an agent."""
        runs = self.get_agent_runs(agent_id, limit=1)
        return runs[0] if runs else None

    # =========================================================================
    # Health Sample Operations
    # =========================================================================

    def record_health_sample(self, sample: HealthSample) -> None:
        """Record a health check sample."""
        with self.connection() as conn:
            conn.execute(
                """
                INSERT INTO health_samples (
                    agent_id, last_stdout_at, last_file_change_at,
                    token_burn_rate, error_count, consecutive_same_error,
                    pending_permission_prompt, edit_revert_cycles,
                    is_stuck, stuck_reason
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """,
                (
                    sample.agent_id,
                    sample.last_stdout_at,
                    sample.last_file_change_at,
                    sample.token_burn_rate,
                    sample.error_count,
                    sample.consecutive_same_error,
                    sample.pending_permission_prompt,
                    sample.edit_revert_cycles,
                    sample.is_stuck,
                    sample.stuck_reason,
                ),
            )

    def get_latest_health_sample(self, agent_id: str) -> Optional[HealthSample]:
        """Get the most recent health sample for an agent."""
        with self.connection() as conn:
            row = conn.execute(
                """
                SELECT * FROM health_samples
                WHERE agent_id = ?
                ORDER BY sampled_at DESC
                LIMIT 1
                """,
                (agent_id,),
            ).fetchone()
            return HealthSample.from_row(dict(row)) if row else None

    def get_stuck_agents(self) -> list[HealthSample]:
        """Get the latest health samples for stuck agents."""
        with self.connection() as conn:
            rows = conn.execute(
                """
                SELECT hs.*
                FROM health_samples hs
                INNER JOIN (
                    SELECT agent_id, MAX(sampled_at) as max_sampled
                    FROM health_samples
                    GROUP BY agent_id
                ) latest ON hs.agent_id = latest.agent_id
                    AND hs.sampled_at = latest.max_sampled
                WHERE hs.is_stuck = TRUE
                """
            ).fetchall()
            return [HealthSample.from_row(dict(row)) for row in rows]

    # =========================================================================
    # Approval Operations
    # =========================================================================

    def create_approval(self, approval: Approval) -> None:
        """Create a new approval request."""
        with self.connection() as conn:
            conn.execute(
                """
                INSERT INTO approvals (
                    id, agent_id, run_id, action_type, target,
                    risk_level, status
                ) VALUES (?, ?, ?, ?, ?, ?, ?)
                """,
                (
                    approval.id,
                    approval.agent_id,
                    approval.run_id,
                    approval.action_type,
                    approval.target,
                    approval.risk_level,
                    approval.status,
                ),
            )

    def get_pending_approvals(self) -> list[Approval]:
        """Get all pending approval requests."""
        with self.connection() as conn:
            rows = conn.execute(
                """
                SELECT * FROM approvals
                WHERE status = 'pending'
                ORDER BY requested_at
                """
            ).fetchall()
            return [Approval.from_row(dict(row)) for row in rows]

    def get_agent_pending_approvals(self, agent_id: str) -> list[Approval]:
        """Get pending approvals for a specific agent."""
        with self.connection() as conn:
            rows = conn.execute(
                """
                SELECT * FROM approvals
                WHERE agent_id = ? AND status = 'pending'
                ORDER BY requested_at
                """,
                (agent_id,),
            ).fetchall()
            return [Approval.from_row(dict(row)) for row in rows]

    def update_approval(
        self,
        approval_id: str,
        status: str,
        decided_by: str,
        decision_notes: Optional[str] = None,
    ) -> None:
        """Update an approval decision."""
        with self.connection() as conn:
            conn.execute(
                """
                UPDATE approvals SET
                    status = ?,
                    decided_at = ?,
                    decided_by = ?,
                    decision_notes = ?
                WHERE id = ?
                """,
                (status, datetime.now(), decided_by, decision_notes, approval_id),
            )

    # =========================================================================
    # Usage Operations
    # =========================================================================

    def get_daily_usage(
        self, agent_id: str, target_date: Optional[date] = None
    ) -> dict[str, Any]:
        """Get usage for a specific day."""
        target_date = target_date or date.today()
        with self.connection() as conn:
            row = conn.execute(
                """
                SELECT * FROM usage_daily
                WHERE agent_id = ? AND date = ?
                """,
                (agent_id, target_date),
            ).fetchone()

            if row:
                return dict(row)
            return {
                "tokens_input": 0,
                "tokens_output": 0,
                "cost_usd": 0.0,
                "requests_count": 0,
                "errors_count": 0,
            }

    def update_daily_usage(
        self,
        agent_id: str,
        tokens_input: int,
        tokens_output: int,
        cost_usd: float,
        is_error: bool = False,
    ) -> None:
        """Update daily usage for an agent (upsert)."""
        today = date.today()
        with self.connection() as conn:
            conn.execute(
                """
                INSERT INTO usage_daily (
                    date, agent_id, tokens_input, tokens_output,
                    cost_usd, requests_count, errors_count
                ) VALUES (?, ?, ?, ?, ?, 1, ?)
                ON CONFLICT (date, agent_id) DO UPDATE SET
                    tokens_input = tokens_input + excluded.tokens_input,
                    tokens_output = tokens_output + excluded.tokens_output,
                    cost_usd = cost_usd + excluded.cost_usd,
                    requests_count = requests_count + 1,
                    errors_count = errors_count + excluded.errors_count
                """,
                (today, agent_id, tokens_input, tokens_output, cost_usd, 1 if is_error else 0),
            )

    def get_usage_summary(
        self, start_date: date, end_date: date
    ) -> list[dict[str, Any]]:
        """Get usage summary for a date range."""
        with self.connection() as conn:
            rows = conn.execute(
                """
                SELECT
                    agent_id,
                    SUM(tokens_input) as total_tokens_input,
                    SUM(tokens_output) as total_tokens_output,
                    SUM(cost_usd) as total_cost,
                    SUM(requests_count) as total_requests,
                    SUM(errors_count) as total_errors
                FROM usage_daily
                WHERE date BETWEEN ? AND ?
                GROUP BY agent_id
                """,
                (start_date, end_date),
            ).fetchall()
            return [dict(row) for row in rows]

    # =========================================================================
    # MCP Tool Usage Operations
    # =========================================================================

    def record_mcp_tool_call(
        self,
        agent_id: str,
        run_id: Optional[str],
        tool_name: str,
        mcp_server: str,
        tokens_used: int,
        duration_ms: Optional[int],
        success: bool,
        error_message: Optional[str] = None,
    ) -> None:
        """Record an MCP tool call."""
        with self.connection() as conn:
            conn.execute(
                """
                INSERT INTO mcp_tool_usage (
                    agent_id, run_id, tool_name, mcp_server,
                    tokens_used, duration_ms, success, error_message
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                """,
                (
                    agent_id,
                    run_id,
                    tool_name,
                    mcp_server,
                    tokens_used,
                    duration_ms,
                    success,
                    error_message,
                ),
            )

    def get_mcp_usage_today(
        self, mcp_server: Optional[str] = None, tool_name: Optional[str] = None
    ) -> int:
        """Get today's MCP token usage, optionally filtered by server/tool."""
        with self.connection() as conn:
            # Use DATE('now') to match CURRENT_TIMESTAMP timezone (UTC)
            query = """
                SELECT COALESCE(SUM(tokens_used), 0) as total
                FROM mcp_tool_usage
                WHERE DATE(called_at) = DATE('now')
            """
            params: list[Any] = []

            if mcp_server:
                query += " AND mcp_server = ?"
                params.append(mcp_server)
            if tool_name:
                query += " AND tool_name = ?"
                params.append(tool_name)

            row = conn.execute(query, params).fetchone()
            return row["total"] if row else 0

    # =========================================================================
    # Decision Operations
    # =========================================================================

    def record_decision(self, decision: Decision) -> None:
        """Record a significant decision."""
        with self.connection() as conn:
            conn.execute(
                """
                INSERT INTO decisions (
                    id, agent_id, task_id, decision, rationale, reversible
                ) VALUES (?, ?, ?, ?, ?, ?)
                """,
                (
                    decision.id,
                    decision.agent_id,
                    decision.task_id,
                    decision.decision,
                    decision.rationale,
                    decision.reversible,
                ),
            )

    def get_recent_decisions(self, limit: int = 10) -> list[Decision]:
        """Get recent decisions."""
        with self.connection() as conn:
            rows = conn.execute(
                """
                SELECT * FROM decisions
                ORDER BY created_at DESC
                LIMIT ?
                """,
                (limit,),
            ).fetchall()
            return [Decision.from_row(dict(row)) for row in rows]

    # =========================================================================
    # Utility Methods
    # =========================================================================

    def generate_run_id(self, agent_id: str) -> str:
        """Generate a unique run ID."""
        timestamp = datetime.now().strftime("%Y%m%d%H%M%S")
        return f"run-{timestamp}-{agent_id}"

    def generate_approval_id(self) -> str:
        """Generate a unique approval ID."""
        timestamp = datetime.now().strftime("%Y%m%d%H%M%S%f")
        return f"approval-{timestamp}"

    def generate_decision_id(self) -> str:
        """Generate a unique decision ID."""
        timestamp = datetime.now().strftime("%Y%m%d%H%M%S%f")
        return f"dec-{timestamp}"

    # =========================================================================
    # CLI Session Operations
    # =========================================================================

    def create_cli_session(
        self,
        session_id: str,
        agent_id: str,
    ) -> None:
        """Create a new CLI session."""
        with self.connection() as conn:
            conn.execute(
                """
                INSERT INTO cli_sessions (id, agent_id)
                VALUES (?, ?)
                """,
                (session_id, agent_id),
            )

    def end_cli_session(
        self,
        session_id: str,
        request_count: int = 0,
        total_tokens: int = 0,
        total_cost_usd: float = 0.0,
    ) -> None:
        """Mark a CLI session as ended."""
        with self.connection() as conn:
            conn.execute(
                """
                UPDATE cli_sessions
                SET ended_at = CURRENT_TIMESTAMP,
                    is_active = FALSE,
                    request_count = ?,
                    total_tokens = ?,
                    total_cost_usd = ?
                WHERE id = ?
                """,
                (request_count, total_tokens, total_cost_usd, session_id),
            )

    def get_active_cli_session(
        self,
        agent_id: str,
    ) -> Optional[dict[str, Any]]:
        """Get active CLI session for an agent."""
        with self.connection() as conn:
            row = conn.execute(
                """
                SELECT * FROM cli_sessions
                WHERE agent_id = ? AND is_active = TRUE
                ORDER BY started_at DESC
                LIMIT 1
                """,
                (agent_id,),
            ).fetchone()
            return dict(row) if row else None

    def save_rate_limit_state(
        self,
        agent_id: str,
        session_id: Optional[str],
        recent_request_timestamps: str,  # JSON
        recent_token_usage: str,  # JSON
        cooldown_until: Optional[datetime],
        consecutive_limit_hits: int,
    ) -> None:
        """Save rate limit state snapshot."""
        with self.connection() as conn:
            conn.execute(
                """
                INSERT OR REPLACE INTO cli_rate_limit_state (
                    agent_id, session_id, recent_request_timestamps,
                    recent_token_usage, cooldown_until, consecutive_limit_hits
                ) VALUES (?, ?, ?, ?, ?, ?)
                """,
                (
                    agent_id,
                    session_id,
                    recent_request_timestamps,
                    recent_token_usage,
                    cooldown_until,
                    consecutive_limit_hits,
                ),
            )

    def load_rate_limit_state(
        self,
        agent_id: str,
        session_id: Optional[str] = None,
    ) -> Optional[dict[str, Any]]:
        """Load most recent rate limit state for an agent."""
        with self.connection() as conn:
            if session_id:
                row = conn.execute(
                    """
                    SELECT * FROM cli_rate_limit_state
                    WHERE agent_id = ? AND session_id = ?
                    ORDER BY saved_at DESC
                    LIMIT 1
                    """,
                    (agent_id, session_id),
                ).fetchone()
            else:
                row = conn.execute(
                    """
                    SELECT * FROM cli_rate_limit_state
                    WHERE agent_id = ?
                    ORDER BY saved_at DESC
                    LIMIT 1
                    """,
                    (agent_id,),
                ).fetchone()
            return dict(row) if row else None

    def get_cli_session_history(
        self,
        agent_id: str,
        limit: int = 10,
    ) -> list[dict[str, Any]]:
        """Get recent CLI sessions for an agent."""
        with self.connection() as conn:
            rows = conn.execute(
                """
                SELECT * FROM cli_sessions
                WHERE agent_id = ?
                ORDER BY started_at DESC
                LIMIT ?
                """,
                (agent_id, limit),
            ).fetchall()
            return [dict(row) for row in rows]

    def generate_session_id(self, agent_id: str) -> str:
        """Generate a unique session ID."""
        timestamp = datetime.now().strftime("%Y%m%d%H%M%S%f")
        return f"session-{agent_id}-{timestamp}"

    # =========================================================================
    # Voting/Consensus Operations
    # =========================================================================

    def create_voting_session(self, session: VotingSession) -> str:
        """Create a new voting session."""
        with self.connection() as conn:
            conn.execute(
                """
                INSERT INTO voting_sessions (
                    id, task_id, topic, description, created_by,
                    deadline_at, status, required_voters, quorum_type,
                    supermajority_threshold
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """,
                (
                    session.id,
                    session.task_id,
                    session.topic,
                    session.description,
                    session.created_by,
                    session.deadline_at,
                    session.status,
                    session.required_voters,
                    session.quorum_type,
                    session.supermajority_threshold,
                ),
            )
        return session.id

    def get_voting_session(self, session_id: str) -> Optional[VotingSession]:
        """Get a voting session by ID."""
        with self.connection() as conn:
            row = conn.execute(
                "SELECT * FROM voting_sessions WHERE id = ?",
                (session_id,),
            ).fetchone()
            return VotingSession.from_row(dict(row)) if row else None

    def get_open_voting_sessions(
        self, task_id: Optional[str] = None
    ) -> list[VotingSession]:
        """Get all open voting sessions, optionally filtered by task."""
        with self.connection() as conn:
            if task_id:
                rows = conn.execute(
                    """
                    SELECT * FROM voting_sessions
                    WHERE status = 'open' AND task_id = ?
                    ORDER BY created_at DESC
                    """,
                    (task_id,),
                ).fetchall()
            else:
                rows = conn.execute(
                    """
                    SELECT * FROM voting_sessions
                    WHERE status = 'open'
                    ORDER BY created_at DESC
                    """,
                ).fetchall()
            return [VotingSession.from_row(dict(row)) for row in rows]

    def cast_vote(self, vote: Vote) -> None:
        """Cast a vote in a voting session."""
        with self.connection() as conn:
            # Use INSERT OR REPLACE to allow vote changes
            conn.execute(
                """
                INSERT OR REPLACE INTO votes (
                    session_id, agent_id, choice, confidence, rationale
                ) VALUES (?, ?, ?, ?, ?)
                """,
                (
                    vote.session_id,
                    vote.agent_id,
                    vote.choice,
                    vote.confidence,
                    vote.rationale,
                ),
            )

    def get_votes(self, session_id: str) -> list[Vote]:
        """Get all votes for a voting session."""
        with self.connection() as conn:
            rows = conn.execute(
                """
                SELECT * FROM votes
                WHERE session_id = ?
                ORDER BY voted_at ASC
                """,
                (session_id,),
            ).fetchall()
            return [Vote.from_row(dict(row)) for row in rows]

    def get_vote_counts(self, session_id: str) -> dict[str, int]:
        """Get vote counts per choice for a session."""
        with self.connection() as conn:
            rows = conn.execute(
                """
                SELECT choice, COUNT(*) as count
                FROM votes
                WHERE session_id = ?
                GROUP BY choice
                ORDER BY count DESC
                """,
                (session_id,),
            ).fetchall()
            return {row["choice"]: row["count"] for row in rows}

    def get_weighted_vote_counts(self, session_id: str) -> dict[str, float]:
        """Get confidence-weighted vote counts per choice."""
        with self.connection() as conn:
            rows = conn.execute(
                """
                SELECT choice, SUM(confidence) as weighted_count
                FROM votes
                WHERE session_id = ?
                GROUP BY choice
                ORDER BY weighted_count DESC
                """,
                (session_id,),
            ).fetchall()
            return {row["choice"]: row["weighted_count"] for row in rows}

    def close_voting_session(
        self,
        session_id: str,
        winning_option: Optional[str],
        result_rationale: str,
    ) -> None:
        """Close a voting session with results."""
        with self.connection() as conn:
            conn.execute(
                """
                UPDATE voting_sessions
                SET status = 'closed',
                    closed_at = CURRENT_TIMESTAMP,
                    winning_option = ?,
                    result_rationale = ?
                WHERE id = ?
                """,
                (winning_option, result_rationale, session_id),
            )

    def cancel_voting_session(self, session_id: str, reason: str) -> None:
        """Cancel a voting session."""
        with self.connection() as conn:
            conn.execute(
                """
                UPDATE voting_sessions
                SET status = 'cancelled',
                    closed_at = CURRENT_TIMESTAMP,
                    result_rationale = ?
                WHERE id = ?
                """,
                (reason, session_id),
            )

    def has_agent_voted(self, session_id: str, agent_id: str) -> bool:
        """Check if an agent has voted in a session."""
        with self.connection() as conn:
            row = conn.execute(
                """
                SELECT COUNT(*) as count FROM votes
                WHERE session_id = ? AND agent_id = ?
                """,
                (session_id, agent_id),
            ).fetchone()
            return row["count"] > 0 if row else False

    def get_agent_vote(self, session_id: str, agent_id: str) -> Optional[Vote]:
        """Get an agent's vote in a session."""
        with self.connection() as conn:
            row = conn.execute(
                """
                SELECT * FROM votes
                WHERE session_id = ? AND agent_id = ?
                """,
                (session_id, agent_id),
            ).fetchone()
            return Vote.from_row(dict(row)) if row else None

    def generate_voting_session_id(self) -> str:
        """Generate a unique voting session ID."""
        timestamp = datetime.now().strftime("%Y%m%d%H%M%S%f")
        return f"vote-{timestamp}"

    # =========================================================================
    # Project Management Operations
    # =========================================================================

    def create_project(
        self,
        project_id: str,
        name: str,
        domain: str,
        description: Optional[str] = None,
        secondary_domains: Optional[list[str]] = None,
        workspace_path: Optional[str] = None,
    ) -> str:
        """Create a new project."""
        with self.connection() as conn:
            conn.execute(
                """
                INSERT INTO projects (
                    id, name, description, domain, secondary_domains, workspace_path
                ) VALUES (?, ?, ?, ?, ?, ?)
                """,
                (
                    project_id,
                    name,
                    description,
                    domain,
                    json.dumps(secondary_domains) if secondary_domains else None,
                    workspace_path,
                ),
            )
            # Record creation event
            conn.execute(
                """
                INSERT INTO project_history (project_id, event_type, event_data)
                VALUES (?, 'created', ?)
                """,
                (project_id, json.dumps({"name": name, "domain": domain})),
            )
        return project_id

    def get_project(self, project_id: str) -> Optional[dict[str, Any]]:
        """Get a project by ID."""
        with self.connection() as conn:
            row = conn.execute(
                "SELECT * FROM projects WHERE id = ?",
                (project_id,),
            ).fetchone()
            if row:
                result = dict(row)
                if result.get("secondary_domains"):
                    result["secondary_domains"] = json.loads(result["secondary_domains"])
                if result.get("planning_notes"):
                    result["planning_notes"] = json.loads(result["planning_notes"])
                return result
            return None

    def get_recent_projects(self, limit: int = 10) -> list[dict[str, Any]]:
        """Get recently accessed projects."""
        with self.connection() as conn:
            rows = conn.execute(
                """
                SELECT * FROM projects
                WHERE status != 'archived'
                ORDER BY last_accessed_at DESC
                LIMIT ?
                """,
                (limit,),
            ).fetchall()
            results = []
            for row in rows:
                result = dict(row)
                if result.get("secondary_domains"):
                    result["secondary_domains"] = json.loads(result["secondary_domains"])
                results.append(result)
            return results

    def get_active_projects(self) -> list[dict[str, Any]]:
        """Get all active projects."""
        with self.connection() as conn:
            rows = conn.execute(
                """
                SELECT * FROM projects
                WHERE status = 'active'
                ORDER BY last_accessed_at DESC
                """
            ).fetchall()
            results = []
            for row in rows:
                result = dict(row)
                if result.get("secondary_domains"):
                    result["secondary_domains"] = json.loads(result["secondary_domains"])
                results.append(result)
            return results

    def update_project_accessed(self, project_id: str) -> None:
        """Update the last_accessed_at timestamp for a project."""
        with self.connection() as conn:
            conn.execute(
                """
                UPDATE projects
                SET last_accessed_at = CURRENT_TIMESTAMP
                WHERE id = ?
                """,
                (project_id,),
            )

    def update_project_status(self, project_id: str, status: str) -> None:
        """Update a project's status."""
        with self.connection() as conn:
            conn.execute(
                "UPDATE projects SET status = ? WHERE id = ?",
                (status, project_id),
            )

    def update_project_planning(
        self,
        project_id: str,
        planning_complete: bool,
        planning_notes: Optional[dict[str, Any]] = None,
    ) -> None:
        """Update a project's planning state."""
        with self.connection() as conn:
            conn.execute(
                """
                UPDATE projects
                SET planning_complete = ?, planning_notes = ?
                WHERE id = ?
                """,
                (
                    planning_complete,
                    json.dumps(planning_notes) if planning_notes else None,
                    project_id,
                ),
            )

    def update_project_progress(
        self,
        project_id: str,
        total_tasks: int,
        completed_tasks: int,
    ) -> None:
        """Update a project's progress counts."""
        with self.connection() as conn:
            conn.execute(
                """
                UPDATE projects
                SET total_tasks = ?, completed_tasks = ?
                WHERE id = ?
                """,
                (total_tasks, completed_tasks, project_id),
            )

    def delete_project(self, project_id: str) -> None:
        """Delete a project and all associated data (cascades)."""
        with self.connection() as conn:
            conn.execute("DELETE FROM projects WHERE id = ?", (project_id,))

    # Project Agents

    def add_project_agent(
        self,
        project_id: str,
        agent_template_id: str,
        role: str = "specialist",
        agent_instance_id: Optional[str] = None,
    ) -> str:
        """Add an agent to a project."""
        agent_id = f"pa-{project_id[:8]}-{agent_template_id}"
        with self.connection() as conn:
            conn.execute(
                """
                INSERT INTO project_agents (
                    id, project_id, agent_template_id, agent_instance_id, role
                ) VALUES (?, ?, ?, ?, ?)
                ON CONFLICT (project_id, agent_template_id) DO UPDATE SET
                    role = excluded.role,
                    agent_instance_id = excluded.agent_instance_id
                """,
                (agent_id, project_id, agent_template_id, agent_instance_id, role),
            )
            # Record event
            conn.execute(
                """
                INSERT INTO project_history (project_id, event_type, event_data)
                VALUES (?, 'agent_added', ?)
                """,
                (project_id, json.dumps({"template_id": agent_template_id, "role": role})),
            )
        return agent_id

    def get_project_agents(self, project_id: str) -> list[dict[str, Any]]:
        """Get all agents assigned to a project."""
        with self.connection() as conn:
            rows = conn.execute(
                """
                SELECT * FROM project_agents
                WHERE project_id = ?
                ORDER BY role, created_at
                """,
                (project_id,),
            ).fetchall()
            return [dict(row) for row in rows]

    def update_project_agent_status(
        self,
        project_id: str,
        agent_template_id: str,
        status: str,
    ) -> None:
        """Update a project agent's status."""
        with self.connection() as conn:
            conn.execute(
                """
                UPDATE project_agents
                SET status = ?, last_active_at = CURRENT_TIMESTAMP
                WHERE project_id = ? AND agent_template_id = ?
                """,
                (status, project_id, agent_template_id),
            )

    def link_project_agent_instance(
        self,
        project_id: str,
        agent_template_id: str,
        agent_instance_id: str,
    ) -> None:
        """Link an actual agent instance to a project agent slot."""
        with self.connection() as conn:
            conn.execute(
                """
                UPDATE project_agents
                SET agent_instance_id = ?, status = 'active'
                WHERE project_id = ? AND agent_template_id = ?
                """,
                (agent_instance_id, project_id, agent_template_id),
            )

    def remove_project_agent(
        self,
        project_id: str,
        agent_template_id: str,
    ) -> None:
        """Remove an agent from a project."""
        with self.connection() as conn:
            conn.execute(
                """
                DELETE FROM project_agents
                WHERE project_id = ? AND agent_template_id = ?
                """,
                (project_id, agent_template_id),
            )

    # Project Context

    def set_project_context(
        self,
        project_id: str,
        key: str,
        value: Any,
    ) -> None:
        """Set a context value for a project."""
        context_id = f"ctx-{project_id[:8]}-{key}"
        json_value = json.dumps(value) if not isinstance(value, str) else value
        with self.connection() as conn:
            conn.execute(
                """
                INSERT INTO project_context (id, project_id, context_key, context_value)
                VALUES (?, ?, ?, ?)
                ON CONFLICT (project_id, context_key) DO UPDATE SET
                    context_value = excluded.context_value,
                    updated_at = CURRENT_TIMESTAMP
                """,
                (context_id, project_id, key, json_value),
            )

    def get_project_context(
        self,
        project_id: str,
        key: str,
    ) -> Optional[Any]:
        """Get a context value for a project."""
        with self.connection() as conn:
            row = conn.execute(
                """
                SELECT context_value FROM project_context
                WHERE project_id = ? AND context_key = ?
                """,
                (project_id, key),
            ).fetchone()
            if row:
                try:
                    return json.loads(row["context_value"])
                except (json.JSONDecodeError, TypeError):
                    return row["context_value"]
            return None

    def get_all_project_context(self, project_id: str) -> dict[str, Any]:
        """Get all context values for a project."""
        with self.connection() as conn:
            rows = conn.execute(
                """
                SELECT context_key, context_value FROM project_context
                WHERE project_id = ?
                """,
                (project_id,),
            ).fetchall()
            result = {}
            for row in rows:
                try:
                    result[row["context_key"]] = json.loads(row["context_value"])
                except (json.JSONDecodeError, TypeError):
                    result[row["context_key"]] = row["context_value"]
            return result

    def delete_project_context(self, project_id: str, key: str) -> None:
        """Delete a context value for a project."""
        with self.connection() as conn:
            conn.execute(
                """
                DELETE FROM project_context
                WHERE project_id = ? AND context_key = ?
                """,
                (project_id, key),
            )

    # Project Files

    def add_project_file(
        self,
        project_id: str,
        file_path: str,
        file_type: str = "source",
        description: Optional[str] = None,
    ) -> str:
        """Track a file as part of a project."""
        file_id = f"pf-{project_id[:8]}-{hash(file_path) % 10000:04d}"
        with self.connection() as conn:
            conn.execute(
                """
                INSERT INTO project_files (id, project_id, file_path, file_type, description)
                VALUES (?, ?, ?, ?, ?)
                ON CONFLICT (project_id, file_path) DO UPDATE SET
                    file_type = excluded.file_type,
                    description = excluded.description,
                    last_modified_at = CURRENT_TIMESTAMP
                """,
                (file_id, project_id, file_path, file_type, description),
            )
        return file_id

    def get_project_files(
        self,
        project_id: str,
        file_type: Optional[str] = None,
    ) -> list[dict[str, Any]]:
        """Get files tracked in a project."""
        with self.connection() as conn:
            if file_type:
                rows = conn.execute(
                    """
                    SELECT * FROM project_files
                    WHERE project_id = ? AND file_type = ?
                    ORDER BY file_path
                    """,
                    (project_id, file_type),
                ).fetchall()
            else:
                rows = conn.execute(
                    """
                    SELECT * FROM project_files
                    WHERE project_id = ?
                    ORDER BY file_type, file_path
                    """,
                    (project_id,),
                ).fetchall()
            return [dict(row) for row in rows]

    def remove_project_file(self, project_id: str, file_path: str) -> None:
        """Remove a file from project tracking."""
        with self.connection() as conn:
            conn.execute(
                """
                DELETE FROM project_files
                WHERE project_id = ? AND file_path = ?
                """,
                (project_id, file_path),
            )

    # Project History

    def add_project_event(
        self,
        project_id: str,
        event_type: str,
        event_data: Optional[dict[str, Any]] = None,
    ) -> None:
        """Add an event to project history."""
        with self.connection() as conn:
            conn.execute(
                """
                INSERT INTO project_history (project_id, event_type, event_data)
                VALUES (?, ?, ?)
                """,
                (project_id, event_type, json.dumps(event_data) if event_data else None),
            )

    def get_project_history(
        self,
        project_id: str,
        limit: int = 50,
    ) -> list[dict[str, Any]]:
        """Get project history events."""
        with self.connection() as conn:
            rows = conn.execute(
                """
                SELECT * FROM project_history
                WHERE project_id = ?
                ORDER BY created_at DESC
                LIMIT ?
                """,
                (project_id, limit),
            ).fetchall()
            results = []
            for row in rows:
                result = dict(row)
                if result.get("event_data"):
                    try:
                        result["event_data"] = json.loads(result["event_data"])
                    except (json.JSONDecodeError, TypeError):
                        pass
                results.append(result)
            return results

    def generate_project_id(self, name: str) -> str:
        """Generate a unique project ID."""
        import hashlib
        timestamp = datetime.now().strftime("%Y%m%d%H%M%S")
        name_hash = hashlib.md5(name.encode()).hexdigest()[:6]
        return f"proj-{timestamp}-{name_hash}"
