"""Backfill model-declared indexes that were never emitted by the
encrypted-DB create-tables loop, and repair download_tracker FK targets.

Background
==========
``encrypted_db.create_user_database()`` previously emitted only
``CreateTable(...)`` for each model in ``Base.metadata.sorted_tables``,
never ``CreateIndex(...)``. As a result, every model-declared index
(``index=True``, ``unique=True``, ``Index(...)`` in ``__table_args__``)
was missing from per-user encrypted databases.

This was harmless until v1.6.0 enabled ``PRAGMA foreign_keys = ON``,
which then started raising ``foreign key mismatch`` whenever
``download_attempts`` / ``download_duplicates`` cascade deletes ran —
because their FK target ``download_tracker.url_hash`` had no UNIQUE
backing visible to SQLCipher.

What this migration does
========================
1. Removes duplicate ``url_hash`` rows in ``download_tracker`` so the
   subsequent UNIQUE INDEX creation does not fail. Survivor: smallest
   ``id`` per ``url_hash``.
2. Removes orphan child rows in ``download_attempts`` /
   ``download_duplicates`` whose ``url_hash`` no longer exists in
   ``download_tracker`` (cascade was inert before v1.6.0, so orphans
   may have accumulated).
3. Creates the canonical UNIQUE index on ``download_tracker.url_hash``
   (named to match the new ``UniqueConstraint`` in the model).
4. Iterates every table in ``Base.metadata`` and creates any model-
   declared index that is missing in this database. Idempotent:
   indexes that already exist (created by earlier migrations or by
   the now-fixed encrypted_db loop) are skipped.

Revision ID: 0007
Revises: 0006
Create Date: 2026-04-27
"""

from alembic import op
from loguru import logger
from sqlalchemy import inspect, text

revision = "0007"
down_revision = "0006"
branch_labels = None
depends_on = None


_DOWNLOAD_TRACKER_UNIQUE_INDEX = "uq_download_tracker_url_hash"


def _scrub_download_tracker_duplicates(bind) -> None:
    """Keep the smallest-id row per url_hash; drop the rest."""
    if not inspect(bind).has_table("download_tracker"):
        return

    duplicates = bind.execute(
        text(
            "SELECT url_hash, COUNT(*) AS c FROM download_tracker "
            "GROUP BY url_hash HAVING c > 1"
        )
    ).all()
    if not duplicates:
        return

    total_removed = 0
    for url_hash, _ in duplicates:
        result = bind.execute(
            text(
                "DELETE FROM download_tracker "
                "WHERE url_hash = :h AND id NOT IN ("
                "  SELECT MIN(id) FROM download_tracker WHERE url_hash = :h"
                ")"
            ),
            {"h": url_hash},
        )
        total_removed += result.rowcount or 0
    logger.warning(
        f"0007: removed {total_removed} duplicate download_tracker row(s) "
        f"across {len(duplicates)} url_hash group(s) before adding UNIQUE index"
    )


_ORPHAN_SCRUB_QUERIES = {
    "download_attempts": text(
        "DELETE FROM download_attempts "
        "WHERE url_hash NOT IN (SELECT url_hash FROM download_tracker)"
    ),
    "download_duplicates": text(
        "DELETE FROM download_duplicates "
        "WHERE url_hash NOT IN (SELECT url_hash FROM download_tracker)"
    ),
}


def _scrub_orphan_children(bind) -> None:
    """Drop child rows referencing url_hash values that no longer exist."""
    inspector = inspect(bind)
    for child_table, stmt in _ORPHAN_SCRUB_QUERIES.items():
        if not inspector.has_table(child_table):
            continue
        result = bind.execute(stmt)
        if result.rowcount:
            logger.warning(
                f"0007: removed {result.rowcount} orphan row(s) from {child_table}"
            )


def _index_exists(inspector, index_name: str, table_name: str) -> bool:
    if not inspector.has_table(table_name):
        return False
    return any(
        idx["name"] == index_name for idx in inspector.get_indexes(table_name)
    )


def _backfill_model_indexes(bind) -> None:
    """Create any model-declared index that's missing in this database."""
    from local_deep_research.database.models import Base

    inspector = inspect(bind)
    created = 0
    for table in Base.metadata.sorted_tables:
        if table.name == "users":
            continue
        if not inspector.has_table(table.name):
            continue
        for index in table.indexes:
            if not index.name:
                continue
            if _index_exists(inspector, index.name, table.name):
                continue
            try:
                op.create_index(
                    index.name,
                    table.name,
                    [c.name for c in index.columns],
                    unique=index.unique,
                    if_not_exists=True,
                )
                created += 1
            except Exception:
                logger.exception(
                    f"0007: failed to create index {index.name} on {table.name}"
                )
    if created:
        logger.info(f"0007: created {created} missing model-declared index(es)")


def upgrade() -> None:
    bind = op.get_bind()

    # SQLite raises ``foreign key mismatch`` on any DML touching a table whose
    # FK target column lacks a UNIQUE backing — which is exactly the pre-fix
    # state of every existing user DB this migration is meant to repair.
    #
    # The actual FK toggle that takes effect is in
    # ``alembic_runner._disable_fk_for_migration``, run BEFORE the migration
    # transaction opens — SQLite silently ignores ``PRAGMA foreign_keys``
    # once any transaction is active, and earlier migrations in this same
    # upgrade chain may have already issued DML, auto-beginning the driver
    # transaction. Issuing the PRAGMA again here is a defensive belt-and-
    # suspenders no-op when the runner has already disabled FK; it preserves
    # the migration's contract for any caller that runs it outside the
    # standard ``run_migrations`` path. The runner re-enables FK on the
    # same connection after the upgrade commits, before returning the
    # connection to the pool.
    bind.execute(text("PRAGMA foreign_keys = OFF"))

    _scrub_download_tracker_duplicates(bind)
    _scrub_orphan_children(bind)

    inspector = inspect(bind)
    if inspector.has_table("download_tracker") and not _index_exists(
        inspector, _DOWNLOAD_TRACKER_UNIQUE_INDEX, "download_tracker"
    ):
        op.create_index(
            _DOWNLOAD_TRACKER_UNIQUE_INDEX,
            "download_tracker",
            ["url_hash"],
            unique=True,
            if_not_exists=True,
        )

    _backfill_model_indexes(bind)


def downgrade() -> None:
    """Drop only the canonical url_hash index this migration introduced.

    The defensive backfill of model-declared indexes is forward-only —
    those indexes are part of the model definitions and would be re-emitted
    on the next user-DB open via the fixed encrypted_db.create_user_database
    code path.
    """
    bind = op.get_bind()
    inspector = inspect(bind)
    if _index_exists(
        inspector, _DOWNLOAD_TRACKER_UNIQUE_INDEX, "download_tracker"
    ):
        op.drop_index(
            _DOWNLOAD_TRACKER_UNIQUE_INDEX,
            table_name="download_tracker",
        )
