"""Garbage collection for inactive scopes and expired messages."""

from __future__ import annotations

import logging
from datetime import datetime, timedelta, timezone

logger = logging.getLogger(__name__)


def _rows_affected(status: str) -> int:
    parts = status.strip().split()
    if len(parts) >= 2 and parts[-1].isdigit():
        return int(parts[-1])
    return 0


async def gc_expired_messages(db_infra, *, ttl_days: int = 30) -> dict:
    aweb_db = db_infra.get_manager("aweb")
    cutoff = datetime.now(timezone.utc) - timedelta(days=ttl_days)

    chat_status = await aweb_db.execute(
        "DELETE FROM {{tables.chat_messages}} WHERE created_at < $1",
        cutoff,
    )
    chat_deleted = _rows_affected(chat_status)

    mail_status = await aweb_db.execute(
        "DELETE FROM {{tables.messages}} WHERE created_at < $1",
        cutoff,
    )
    mail_deleted = _rows_affected(mail_status)

    total = chat_deleted + mail_deleted
    logger.info("gc_expired_messages: deleted %d messages (cutoff=%s)", total, cutoff.isoformat())
    return {"messages_deleted": total, "chat_deleted": chat_deleted, "mail_deleted": mail_deleted}


async def gc_inactive_scopes(db_infra, *, ttl_days: int = 30) -> dict:
    aweb_db = db_infra.get_manager("aweb")
    cutoff = datetime.now(timezone.utc) - timedelta(days=ttl_days)

    inactive = await aweb_db.fetch_all(
        """
        SELECT t.team_id
        FROM {{tables.teams}} t
        WHERE t.created_at < $1
          AND NOT EXISTS (
              SELECT 1 FROM {{tables.messages}} m
              WHERE m.team_id = t.team_id
                AND m.created_at >= $1
          )
          AND NOT EXISTS (
              SELECT 1 FROM {{tables.chat_sessions}} cs
              JOIN {{tables.chat_messages}} cm ON cm.session_id = cs.session_id
              WHERE cs.team_id = t.team_id
                AND cm.created_at >= $1
          )
        """,
        cutoff,
    )

    deleted_count = 0
    for row in inactive:
        await _hard_delete_scope(aweb_db, team_id=row["team_id"])
        deleted_count += 1

    logger.info("gc_inactive_scopes: deleted %d scopes (cutoff=%s)", deleted_count, cutoff.isoformat())
    return {"scopes_deleted": deleted_count}


async def _hard_delete_scope(aweb_db, *, team_id) -> None:
    async with aweb_db.transaction() as tx:
        await tx.execute(
            """
            UPDATE {{tables.messages}}
            SET from_agent_id = NULL
            WHERE from_agent_id IN (
                SELECT agent_id FROM {{tables.agents}} WHERE team_id = $1
            )
            """,
            team_id,
        )
        await tx.execute(
            """
            UPDATE {{tables.messages}}
            SET to_agent_id = NULL
            WHERE to_agent_id IN (
                SELECT agent_id FROM {{tables.agents}} WHERE team_id = $1
            )
            """,
            team_id,
        )
        await tx.execute(
            """
            UPDATE {{tables.messages}}
            SET team_id = NULL
            WHERE team_id = $1
            """,
            team_id,
        )
        await tx.execute(
            """
            UPDATE {{tables.chat_read_receipts}}
            SET agent_id = NULL
            WHERE agent_id IN (
                SELECT agent_id FROM {{tables.agents}} WHERE team_id = $1
            )
            """,
            team_id,
        )
        await tx.execute(
            """
            UPDATE {{tables.chat_messages}}
            SET from_agent_id = NULL
            WHERE from_agent_id IN (
                SELECT agent_id FROM {{tables.agents}} WHERE team_id = $1
            )
            """,
            team_id,
        )
        await tx.execute(
            """
            UPDATE {{tables.chat_participants}}
            SET agent_id = NULL
            WHERE agent_id IN (
                SELECT agent_id FROM {{tables.agents}} WHERE team_id = $1
            )
            """,
            team_id,
        )
        await tx.execute(
            """
            UPDATE {{tables.chat_sessions}}
            SET team_id = NULL
            WHERE team_id = $1
            """,
            team_id,
        )
        await tx.execute(
            "DELETE FROM {{tables.control_signals}} WHERE team_id = $1",
            team_id,
        )
        await tx.execute(
            "DELETE FROM {{tables.task_claims}} WHERE team_id = $1",
            team_id,
        )
        await tx.execute(
            "DELETE FROM {{tables.task_comments}} WHERE team_id = $1",
            team_id,
        )
        await tx.execute(
            "DELETE FROM {{tables.task_dependencies}} WHERE team_id = $1",
            team_id,
        )
        await tx.execute(
            "DELETE FROM {{tables.tasks}} WHERE team_id = $1",
            team_id,
        )
        await tx.execute(
            "DELETE FROM {{tables.task_counters}} WHERE team_id = $1",
            team_id,
        )
        await tx.execute(
            "DELETE FROM {{tables.task_root_counters}} WHERE team_id = $1",
            team_id,
        )
        await tx.execute(
            "DELETE FROM {{tables.reservations}} WHERE team_id = $1",
            team_id,
        )
        await tx.execute(
            "DELETE FROM {{tables.workspaces}} WHERE team_id = $1",
            team_id,
        )
        await tx.execute(
            "DELETE FROM {{tables.repos}} WHERE team_id = $1",
            team_id,
        )
        await tx.execute(
            "DELETE FROM {{tables.team_roles}} WHERE team_id = $1",
            team_id,
        )
        await tx.execute(
            "DELETE FROM {{tables.team_instructions}} WHERE team_id = $1",
            team_id,
        )
        await tx.execute(
            "DELETE FROM {{tables.audit_log}} WHERE team_id = $1",
            team_id,
        )
        await tx.execute(
            "DELETE FROM {{tables.agents}} WHERE team_id = $1",
            team_id,
        )
        await tx.execute(
            "DELETE FROM {{tables.teams}} WHERE team_id = $1",
            team_id,
        )
