"""Retarget ResearchStrategy.research_id FK to research_history.id.

Background
==========
Commit fcc5651 (June 2025) introduced ``ResearchStrategy.research_id`` as
``Integer FK research.id``, pointing at a dormant table that no production
path reads or writes. ``save_research_strategy`` always passes the
research_history UUID string. PRAGMA foreign_keys=OFF (the default before
v1.6.0) silently inserted orphan rows, so the bug was invisible. PR #3081
(v1.6.0) turned PRAGMA foreign_keys ON; from that release every
``save_research_strategy`` commit fails with::

    sqlcipher3.dbapi2.IntegrityError: FOREIGN KEY constraint failed

What this migration does
========================
Rebuild ``research_strategies`` with ``research_id`` as
``String(36) FK research_history.id ON DELETE CASCADE``. SQLite cannot
ALTER an existing FK in place, so the table is dropped and recreated.

Existing rows are guaranteed empty: the broken FK has rejected every
insert since 2025-06-07. A precondition check refuses the rebuild if any
row is found (would indicate an exotic install), and the operator can
inspect manually.

This migration deliberately does **not** drop the dormant ``research`` /
``research_tasks`` / ``reports`` / ``report_sections`` / ``search_queries``
/ ``search_results`` tables. That cleanup is independent and can ship in
a later release.

Revision ID: 0008
Revises: 0007
Create Date: 2026-04-29
"""

from __future__ import annotations

import sqlalchemy as sa
from alembic import op
from loguru import logger
from sqlalchemy import inspect, text
from sqlalchemy_utc import UtcDateTime, utcnow

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


def _table_row_count(bind, table_name: str) -> int:
    return (
        bind.execute(
            text(f"SELECT COUNT(*) FROM {table_name}")  # noqa: S608 — hardcoded name  # bearer:disable python_lang_sql_injection — table_name is always a hardcoded literal
        ).scalar()
        or 0
    )


def upgrade() -> None:
    bind = op.get_bind()
    inspector = inspect(bind)
    if not inspector.has_table("research_strategies"):
        logger.info(
            "0008: research_strategies table absent (fresh DB) — nothing to rebuild"
        )
        return

    row_count = _table_row_count(bind, "research_strategies")
    if row_count:
        logger.warning(
            f"0008: research_strategies has {row_count} row(s) — unexpected, "
            "since the broken FK has rejected every insert since 2025-06-07. "
            "Skipping rebuild; inspect manually before re-running."
        )
        return

    op.drop_table("research_strategies")
    op.create_table(
        "research_strategies",
        sa.Column("id", sa.Integer(), primary_key=True, index=True),
        sa.Column(
            "research_id",
            sa.String(36),
            sa.ForeignKey("research_history.id", ondelete="CASCADE"),
            nullable=False,
            unique=True,
            index=True,
        ),
        sa.Column("strategy_name", sa.String(100), nullable=False, index=True),
        sa.Column(
            "created_at",
            UtcDateTime(),
            server_default=utcnow(),
            nullable=False,
        ),
    )
    logger.info(
        "0008: rebuilt research_strategies with FK -> research_history.id"
    )


def downgrade() -> None:
    """Restore the prior (broken) schema for chain consistency.

    Note: the prior FK target was the dormant ``research`` table, so
    restoring it re-introduces the original ``FOREIGN KEY constraint
    failed`` bug on any subsequent ``save_research_strategy`` commit.
    Downgrading below 0008 is only sensible during testing or when paired
    with downgrading further to a release that predates the broken FK.
    """
    bind = op.get_bind()
    inspector = inspect(bind)
    if not inspector.has_table("research_strategies"):
        return

    row_count = _table_row_count(bind, "research_strategies")
    if row_count:
        logger.warning(
            f"0008 downgrade: research_strategies has {row_count} row(s); "
            "those rows reference research_history.id (String UUIDs) which "
            "the restored Integer FK column cannot represent. Skipping "
            "downgrade; inspect manually."
        )
        return

    op.drop_table("research_strategies")
    op.create_table(
        "research_strategies",
        sa.Column("id", sa.Integer(), primary_key=True, index=True),
        sa.Column(
            "research_id",
            sa.Integer(),
            sa.ForeignKey("research.id", ondelete="CASCADE"),
            nullable=False,
            unique=True,
            index=True,
        ),
        sa.Column("strategy_name", sa.String(100), nullable=False, index=True),
        sa.Column(
            "created_at",
            UtcDateTime(),
            server_default=utcnow(),
            nullable=False,
        ),
    )
