# -*- coding: utf-8 -*-
"""
Django management command: migration_analyze (V2)

V2 = "diagnostic + prescriptions"

Objectif (multi-engine):
- Croiser:
  A) Django "Model State" (ce que Django pense devoir exister)
  B) Plan de migrations + opérations (ce que Django va appliquer)
  C) Schéma réel DB (tables/colonnes/indexes via INFORMATION_SCHEMA / pg_catalog / sqlite_master)
- Détecter les risques classiques AVANT crash:
  - MySQL/MariaDB: 1061 (duplicate key name), 1071 (key too long), index sur TEXT/BLOB sans prefix
  - Incohérences: colonnes manquantes, indexes divergents, tables orphelines
- Proposer des actions:
  - 1061: "FAKE migration" si index identique, sinon "DROP index"
  - 1071: suggestions concrètes (191, réduire index composite, prefix via RunSQL, etc.)
  - UNKNOWN: distingue "normal (table pas migrée)" vs "vrai risque (TEXT/BLOB)"

Options:
  --plan / --diff / --risks
  --recommend (default True) : imprime des actions recommandées
  --fix-1061 {none,drop,fake} : auto-fix 1061 (drop auto, fake = commande suggérée)
  --dry-run : pour fix, n'applique rien (mais affiche)
  --emit-sql : affiche le SQL proposé (DROP INDEX)
  --export-json path : export JSON (findings + recommendations)

Usage:
  python manage.py migration_analyze --risks --mysql-max-key-bytes 1000 --verbose
  python manage.py migration_analyze --risks --fix-1061 drop --dry-run --emit-sql
  python manage.py migration_analyze --risks --fix-1061 drop --emit-sql
"""

from __future__ import annotations

import json
from dataclasses import dataclass, asdict
from typing import Any, Dict, List, Optional, Sequence, Set, Tuple

from django.core.management.base import BaseCommand
from django.db import connections, DEFAULT_DB_ALIAS
from django.db.migrations.executor import MigrationExecutor
from django.db.migrations.state import ProjectState
from django.db.migrations.operations.models import (
    CreateModel,
    AddIndex,
    AlterIndexTogether,
    AlterUniqueTogether,
)
from django.db.migrations.operations.fields import AddField, AlterField


# -------------------------
# Data structures
# -------------------------

@dataclass(frozen=True)
class DBColumn:
    name: str
    data_type: str
    max_length: Optional[int]
    is_nullable: bool
    collation: Optional[str] = None


@dataclass(frozen=True)
class DBIndex:
    name: str
    columns: Tuple[str, ...]
    unique: bool


@dataclass(frozen=True)
class DBTable:
    name: str
    columns: Dict[str, DBColumn]
    indexes: Dict[str, DBIndex]


@dataclass(frozen=True)
class PlannedIndex:
    table: str
    name: str
    columns: Tuple[str, ...]
    unique: bool
    origin: str  # "app.migration: ..."


@dataclass(frozen=True)
class PlannedOp:
    app: str
    migration: str
    op: str
    model: Optional[str]
    details: str


@dataclass
class Finding:
    level: str  # INFO/WARN/ERROR
    code: str
    message: str
    details: Dict[str, Any]  # V2: inclut "recommendations": []


# -------------------------
# DB Introspection helpers
# -------------------------

def _mysql_fetch_tables(connection) -> Dict[str, DBTable]:
    tables: Dict[str, DBTable] = {}

    with connection.cursor() as cur:
        cur.execute(
            """
            SELECT TABLE_NAME
            FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_SCHEMA = DATABASE()
            """
        )
        for (tname,) in cur.fetchall():
            tables[tname] = DBTable(name=tname, columns={}, indexes={})

        cur.execute(
            """
            SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, COLLATION_NAME
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
            """
        )
        for t, c, dt, maxlen, isnull, coll in cur.fetchall():
            if t not in tables:
                continue
            tables[t].columns[c] = DBColumn(
                name=c,
                data_type=str(dt).lower(),
                max_length=maxlen if maxlen is None else int(maxlen),
                is_nullable=(str(isnull).upper() == "YES"),
                collation=coll,
            )

        cur.execute(
            """
            SELECT TABLE_NAME, INDEX_NAME, NON_UNIQUE, COLUMN_NAME, SEQ_IN_INDEX
            FROM INFORMATION_SCHEMA.STATISTICS
            WHERE TABLE_SCHEMA = DATABASE()
            ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX
            """
        )
        tmp: Dict[Tuple[str, str], Dict[str, Any]] = {}
        for t, idx, non_unique, col, seq in cur.fetchall():
            key = (t, idx)
            if key not in tmp:
                tmp[key] = {"cols": [], "unique": (int(non_unique) == 0)}
            tmp[key]["cols"].append(col)

        for (t, idx), v in tmp.items():
            if t in tables:
                tables[t].indexes[idx] = DBIndex(
                    name=idx,
                    columns=tuple(v["cols"]),
                    unique=bool(v["unique"]),
                )

    return tables


def _pgsql_fetch_tables(connection) -> Dict[str, DBTable]:
    tables: Dict[str, DBTable] = {}

    with connection.cursor() as cur:
        cur.execute(
            """
            SELECT tablename
            FROM pg_catalog.pg_tables
            WHERE schemaname = 'public'
            """
        )
        for (tname,) in cur.fetchall():
            tables[tname] = DBTable(name=tname, columns={}, indexes={})

        cur.execute(
            """
            SELECT c.table_name, c.column_name, c.data_type, c.character_maximum_length, c.is_nullable
            FROM information_schema.columns c
            WHERE c.table_schema = 'public'
            """
        )
        for t, c, dt, maxlen, isnull in cur.fetchall():
            if t not in tables:
                continue
            tables[t].columns[c] = DBColumn(
                name=c,
                data_type=str(dt).lower(),
                max_length=maxlen if maxlen is None else int(maxlen),
                is_nullable=(str(isnull).upper() == "YES"),
                collation=None,
            )

        cur.execute(
            """
            SELECT
              t.relname AS table_name,
              i.relname AS index_name,
              ix.indisunique AS is_unique,
              array_to_string(array_agg(a.attname ORDER BY x.n), ',') AS columns
            FROM pg_class t
            JOIN pg_index ix ON t.oid = ix.indrelid
            JOIN pg_class i ON i.oid = ix.indexrelid
            JOIN LATERAL unnest(ix.indkey) WITH ORDINALITY AS x(attnum, n) ON true
            JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = x.attnum
            WHERE t.relkind = 'r'
              AND pg_catalog.pg_table_is_visible(t.oid)
            GROUP BY t.relname, i.relname, ix.indisunique
            """
        )
        for t, idx, is_unique, cols in cur.fetchall():
            if t not in tables:
                continue
            col_tuple = tuple([c.strip() for c in str(cols).split(",") if c.strip()])
            tables[t].indexes[idx] = DBIndex(
                name=idx,
                columns=col_tuple,
                unique=bool(is_unique),
            )

    return tables


def _sqlite_fetch_tables(connection) -> Dict[str, DBTable]:
    tables: Dict[str, DBTable] = {}
    with connection.cursor() as cur:
        cur.execute("SELECT name FROM sqlite_master WHERE type='table'")
        for (tname,) in cur.fetchall():
            tables[tname] = DBTable(name=tname, columns={}, indexes={})

        for tname in list(tables.keys()):
            cur.execute(f"PRAGMA table_info('{tname}')")
            for cid, name, ctype, notnull, dflt, pk in cur.fetchall():
                tables[tname].columns[name] = DBColumn(
                    name=name,
                    data_type=str(ctype).lower(),
                    max_length=None,
                    is_nullable=(int(notnull) == 0),
                    collation=None,
                )

            cur.execute(f"PRAGMA index_list('{tname}')")
            for seq, idxname, unique, origin, partial in cur.fetchall():
                cur.execute(f"PRAGMA index_info('{idxname}')")
                cols = []
                for seqno, cid, cname in cur.fetchall():
                    cols.append(cname)
                tables[tname].indexes[idxname] = DBIndex(
                    name=idxname,
                    columns=tuple(cols),
                    unique=bool(unique),
                )
    return tables


def fetch_db_schema(connection) -> Dict[str, DBTable]:
    if connection.vendor == "mysql":
        return _mysql_fetch_tables(connection)
    if connection.vendor == "postgresql":
        return _pgsql_fetch_tables(connection)
    if connection.vendor == "sqlite":
        return _sqlite_fetch_tables(connection)

    tables: Dict[str, DBTable] = {}
    with connection.cursor() as cur:
        for tname in connection.introspection.table_names(cur):
            tables[tname] = DBTable(name=tname, columns={}, indexes={})
    return tables


# -------------------------
# MySQL helpers (fix)
# -------------------------

def mysql_drop_index(connection, table: str, index_name: str) -> None:
    # MariaDB/MySQL: DROP INDEX <name> ON <table>
    with connection.cursor() as cur:
        cur.execute(f"DROP INDEX `{index_name}` ON `{table}`")


# -------------------------
# Planning helpers
# -------------------------

def _safe_table_name(app_label: str, model_name: str, state: ProjectState) -> str:
    key = (app_label, model_name.lower())
    if key in state.models:
        ms = state.models[key]
        return ms.options.get("db_table") or f"{app_label}_{model_name.lower()}"
    return f"{app_label}_{model_name.lower()}"


def _clean_cols(cols) -> Tuple[str, ...]:
    return tuple([c for c in (cols or []) if c])


def _estimate_mysql_index_bytes(
    table: str,
    columns: Sequence[str],
    db_schema: Dict[str, DBTable],
    assume_utf8mb4_bytes_per_char: int = 4,
) -> Tuple[Optional[int], List[str], str]:
    """
    Retourne (bytes_estimes|None, notes, classification)
    classification ∈ {"OK","TABLE_MISSING","COLUMN_MISSING","TEXT_BLOB","UNKNOWN_TYPE"}
    """
    notes: List[str] = []
    if table not in db_schema:
        return None, [f"Table '{table}' not present in DB schema snapshot."], "TABLE_MISSING"

    total = 0
    for col in columns:
        c = db_schema[table].columns.get(col)
        if not c:
            return None, [f"Column '{col}' not present in DB schema snapshot for table '{table}'."], "COLUMN_MISSING"

        dt = c.data_type.lower()

        if dt in ("varchar", "char", "nvarchar", "nchar"):
            if c.max_length is None:
                return None, [f"Column '{table}.{col}' has unknown max_length."], "UNKNOWN_TYPE"
            bpc = assume_utf8mb4_bytes_per_char
            if c.collation and "utf8" in c.collation.lower() and "mb4" not in c.collation.lower():
                bpc = 3
            total += int(c.max_length) * bpc

        elif "text" in dt or "blob" in dt:
            notes.append(f"Column '{table}.{col}' type '{dt}' is TEXT/BLOB-like; MySQL index may require prefix.")
            return None, notes, "TEXT_BLOB"

        elif dt in ("int", "integer", "bigint", "smallint", "tinyint", "mediumint"):
            total += 8
        elif dt in ("datetime", "timestamp", "date", "time"):
            total += 8
        elif dt in ("decimal", "numeric", "float", "double"):
            total += 16
        elif dt in ("uuid",):
            total += 16
        else:
            notes.append(f"Unknown/unsupported type for size estimate: '{table}.{col}' dt='{dt}'")
            return None, notes, "UNKNOWN_TYPE"

    return total, notes, "OK"


def collect_migration_plan(executor: MigrationExecutor, restrict_apps: Set[str]) -> List[PlannedOp]:
    targets = executor.loader.graph.leaf_nodes()
    plan = executor.migration_plan(targets)

    ops: List[PlannedOp] = []
    for migration, backwards in plan:
        if backwards:
            continue
        if restrict_apps and migration.app_label not in restrict_apps:
            continue
        for op in migration.operations:
            model = getattr(op, "model_name", None) or getattr(op, "name", None)
            ops.append(
                PlannedOp(
                    app=migration.app_label,
                    migration=migration.name,
                    op=op.__class__.__name__,
                    model=model,
                    details=str(op),
                )
            )
    return ops


def collect_planned_indexes(
    connection,
    executor: MigrationExecutor,
    restrict_apps: Set[str],
) -> List[PlannedIndex]:
    """
    Reconstruit les index que Django va créer à partir des migrations:
    - Meta.indexes (CreateModel)
    - index_together (CreateModel & AlterIndexTogether)
    - unique_together (CreateModel & AlterUniqueTogether) => unique index
    - AddIndex
    - db_index=True (AddField/AlterField) => index mono-colonne auto
    """
    state = executor._create_project_state(with_applied_migrations=True)
    targets = executor.loader.graph.leaf_nodes()
    plan = executor.migration_plan(targets)

    planned: List[PlannedIndex] = []

    with connection.schema_editor() as schema_editor:
        for migration, backwards in plan:
            if backwards:
                continue
            if restrict_apps and migration.app_label not in restrict_apps:
                continue

            for op in migration.operations:
                if isinstance(op, CreateModel):
                    table = op.options.get("db_table") or f"{migration.app_label}_{op.name.lower()}"

                    for idx in (op.options.get("indexes") or []):
                        cols = _clean_cols(getattr(idx, "fields", None))
                        name = getattr(idx, "name", None)
                        unique = bool(getattr(idx, "unique", False))
                        if not cols and not name:
                            continue
                        if not name:
                            name = schema_editor._create_index_name(table, list(cols), suffix="_idx")
                        planned.append(
                            PlannedIndex(
                                table=table,
                                name=name,
                                columns=cols,
                                unique=unique,
                                origin=f"{migration.app_label}.{migration.name}: CreateModel({op.name}) Meta.indexes",
                            )
                        )

                    for cols in (op.options.get("index_together") or []):
                        cols = _clean_cols(cols)
                        if not cols:
                            continue
                        name = schema_editor._create_index_name(table, list(cols), suffix="_idx")
                        planned.append(
                            PlannedIndex(
                                table=table,
                                name=name,
                                columns=cols,
                                unique=False,
                                origin=f"{migration.app_label}.{migration.name}: CreateModel({op.name}) index_together={cols}",
                            )
                        )

                    for cols in (op.options.get("unique_together") or []):
                        cols = _clean_cols(cols)
                        if not cols:
                            continue
                        name = schema_editor._create_index_name(table, list(cols), suffix="_uniq")
                        planned.append(
                            PlannedIndex(
                                table=table,
                                name=name,
                                columns=cols,
                                unique=True,
                                origin=f"{migration.app_label}.{migration.name}: CreateModel({op.name}) unique_together={cols}",
                            )
                        )

                if isinstance(op, AddIndex):
                    table = _safe_table_name(migration.app_label, op.model_name, state)
                    idx = op.index
                    cols = _clean_cols(getattr(idx, "fields", None))
                    name = getattr(idx, "name", None)
                    unique = bool(getattr(idx, "unique", False))
                    if not cols and not name:
                        continue
                    if not name:
                        name = schema_editor._create_index_name(table, list(cols), suffix="_idx")
                    planned.append(
                        PlannedIndex(
                            table=table,
                            name=name,
                            columns=cols,
                            unique=unique,
                            origin=f"{migration.app_label}.{migration.name}: AddIndex({op.model_name})",
                        )
                    )

                if isinstance(op, AlterIndexTogether):
                    table = _safe_table_name(migration.app_label, op.name, state)
                    for cols in (op.index_together or []):
                        cols = _clean_cols(cols)
                        if not cols:
                            continue
                        name = schema_editor._create_index_name(table, list(cols), suffix="_idx")
                        planned.append(
                            PlannedIndex(
                                table=table,
                                name=name,
                                columns=cols,
                                unique=False,
                                origin=f"{migration.app_label}.{migration.name}: AlterIndexTogether({op.name}) index_together={cols}",
                            )
                        )

                if isinstance(op, AlterUniqueTogether):
                    table = _safe_table_name(migration.app_label, op.name, state)
                    for cols in (op.unique_together or []):
                        cols = _clean_cols(cols)
                        if not cols:
                            continue
                        name = schema_editor._create_index_name(table, list(cols), suffix="_uniq")
                        planned.append(
                            PlannedIndex(
                                table=table,
                                name=name,
                                columns=cols,
                                unique=True,
                                origin=f"{migration.app_label}.{migration.name}: AlterUniqueTogether({op.name}) unique_together={cols}",
                            )
                        )

                if isinstance(op, (AddField, AlterField)):
                    field = op.field
                    if getattr(field, "db_index", False):
                        table = _safe_table_name(migration.app_label, op.model_name, state)
                        col = (
                            getattr(op, "name", None)
                            or getattr(op, "field_name", None)
                            or getattr(field, "attname", None)
                        )
                        if col:
                            name = schema_editor._create_index_name(table, [col], suffix="_idx")
                            planned.append(
                                PlannedIndex(
                                    table=table,
                                    name=name,
                                    columns=(col,),
                                    unique=False,
                                    origin=f"{migration.app_label}.{migration.name}: {op.__class__.__name__}({op.model_name}.{col}) db_index=True",
                                )
                            )

                op.state_forwards(migration.app_label, state)

    seen: Set[Tuple[str, str]] = set()
    uniq: List[PlannedIndex] = []
    for p in planned:
        k = (p.table, p.name)
        if k not in seen:
            seen.add(k)
            uniq.append(p)
    return uniq


# -------------------------
# Recommendation engine (V2)
# -------------------------

def _parse_origin_app_migration(origin: str) -> Tuple[Optional[str], Optional[str]]:
    """
    origin format expected: "<app>.<migration>: ..."
    """
    try:
        head = origin.split(":", 1)[0].strip()
        if "." not in head:
            return None, None
        app, mig = head.split(".", 1)
        app = app.strip() or None
        mig = mig.strip() or None
        return app, mig
    except Exception:
        return None, None


def _rec(action: str, rationale: str, sql: Optional[str] = None, command: Optional[str] = None) -> Dict[str, Any]:
    d = {"action": action, "rationale": rationale}
    if sql:
        d["sql"] = sql
    if command:
        d["command"] = command
    return d


def recommend_for_1061(
    connection_vendor: str,
    db_schema: Dict[str, DBTable],
    planned: PlannedIndex,
) -> List[Dict[str, Any]]:
    recs: List[Dict[str, Any]] = []
    if planned.table not in db_schema:
        recs.append(_rec(
            "IGNORE_FOR_NOW",
            "Table not present in DB snapshot; likely not migrated yet. Re-run after initial migrations.",
        ))
        return recs

    existing = db_schema[planned.table].indexes.get(planned.name)
    if not existing:
        return recs

    same_cols = tuple(existing.columns) == tuple(planned.columns)
    same_unique = bool(existing.unique) == bool(planned.unique)

    app, mig = _parse_origin_app_migration(planned.origin)

    if same_cols and same_unique:
        # Best practice: fake the migration that would recreate the same index
        if app and mig:
            recs.append(_rec(
                "FAKE_MIGRATION",
                "DB already has the exact same index (same columns & uniqueness). "
                "Avoid drop/recreate; just align Django migration history with --fake.",
                command=f"python manage.py migrate {app} {mig} --fake",
            ))
        else:
            recs.append(_rec(
                "FAKE_MIGRATION",
                "DB already has the exact same index (same columns & uniqueness). "
                "Fake the migration that creates it (need app + migration name).",
            ))
    else:
        # Mismatch: drop stale index so Django can recreate correct one
        if connection_vendor == "mysql":
            sql = f"DROP INDEX `{planned.name}` ON `{planned.table}`;"
            recs.append(_rec(
                "DROP_STALE_INDEX",
                "Index name exists but definition differs (columns/unique mismatch). "
                "Drop it so Django can recreate the expected index.",
                sql=sql,
            ))
        else:
            recs.append(_rec(
                "DROP_STALE_INDEX",
                "Index name exists but definition differs. Drop it so Django can recreate the expected index.",
            ))

    return recs


def recommend_for_1071(planned: PlannedIndex, estimated_bytes: int, limit: int) -> List[Dict[str, Any]]:
    cols = ", ".join(planned.columns)
    recs: List[Dict[str, Any]] = []
    recs.append(_rec(
        "REDUCE_INDEXED_CHARFIELD_LENGTH",
        f"Estimated index size {estimated_bytes} bytes exceeds limit {limit}. "
        f"Typical fix on MySQL/MariaDB utf8mb4: reduce max_length for indexed CharFields to 191 "
        f"(or reduce composite index columns). Index columns: {cols}.",
    ))
    recs.append(_rec(
        "SIMPLIFY_COMPOSITE_INDEX",
        f"Remove one or more columns from the composite index '{planned.name}' on '{planned.table}'. "
        f"Keep only the most selective columns; re-run migrations.",
    ))
    recs.append(_rec(
        "PREFIX_INDEX_VIA_RUNSQL",
        "If you must index TEXT/VARCHAR large fields, consider prefix indexes via RunSQL "
        "(MySQL-specific), e.g. CREATE INDEX idx ON table(col(191)). Django doesn't support this natively in Index(fields=...).",
    ))
    return recs


def recommend_for_unknown(classification: str, planned: PlannedIndex, notes: List[str]) -> List[Dict[str, Any]]:
    if classification in ("TABLE_MISSING", "COLUMN_MISSING"):
        return [_rec(
            "IGNORE_FOR_NOW",
            "DB snapshot can't see the table/column yet (normal if migrations not applied). Re-run after creating tables.",
        )]
    if classification == "TEXT_BLOB":
        return [
            _rec(
                "AVOID_TEXT_BLOB_INDEX",
                "MySQL/MariaDB cannot index TEXT/BLOB fully without a prefix. Consider changing to CharField with bounded length "
                "or add a prefix index via RunSQL.",
            )
        ]
    return [_rec(
        "INVESTIGATE_COLUMN_TYPES",
        "Cannot estimate index size due to unknown types. Inspect actual column types/collation and adjust fields/index accordingly.",
    )]


# -------------------------
# Main command
# -------------------------

class Command(BaseCommand):
    help = "Migration analyzer & debugger (models vs migrations vs DB schema), multi-engine (V2)."

    def add_arguments(self, parser):
        parser.add_argument("--database", default=DEFAULT_DB_ALIAS)
        parser.add_argument("--apps", nargs="*", default=None, help="Restrict to these app labels.")
        parser.add_argument("--plan", action="store_true", help="Print migration plan summary.")
        parser.add_argument("--diff", action="store_true", help="Compare DB schema vs Django planned state (basic).")
        parser.add_argument("--risks", action="store_true", help="Run risk detectors (1061/1071 etc).")
        parser.add_argument("--recommend", action="store_true", help="Print recommended actions (default on).")
        parser.add_argument("--no-recommend", action="store_true", help="Disable recommended actions.")
        parser.add_argument("--verbose", action="store_true", help="More details in output.")
        parser.add_argument("--export-json", default=None, help="Write report to JSON file path.")
        parser.add_argument("--mysql-max-key-bytes", type=int, default=1000,
                            help="Override MySQL/MariaDB max key length bytes (e.g. 1000).")
        parser.add_argument("--fix-1061", choices=["none", "drop", "fake"], default="none",
                            help="Auto-fix strategy for 1061 risks. drop=DROP INDEX, fake=print --fake commands.")
        parser.add_argument("--dry-run", action="store_true", help="Do not apply fixes; only show what would happen.")
        parser.add_argument("--emit-sql", action="store_true", help="Print SQL statements for recommended fixes (MySQL).")

    def handle(self, *args, **opts):
        db_alias: str = opts["database"]
        restrict_apps: Set[str] = set(opts["apps"] or [])
        show_plan: bool = opts["plan"]
        do_diff: bool = opts["diff"]
        do_risks: bool = opts["risks"]
        verbose: bool = opts["verbose"]
        export_json: Optional[str] = opts["export_json"]
        mysql_max_key_bytes: Optional[int] = opts["mysql_max_key_bytes"]

        fix_1061: str = opts["fix_1061"]
        dry_run: bool = opts["dry_run"]
        emit_sql: bool = opts["emit_sql"]

        # recommend default ON unless --no-recommend
        recommend_on: bool = True
        if opts["no_recommend"]:
            recommend_on = False
        if opts["recommend"]:
            recommend_on = True

        connection = connections[db_alias]
        executor = MigrationExecutor(connection)

        report: Dict[str, Any] = {
            "database": db_alias,
            "vendor": connection.vendor,
            "restricted_apps": sorted(list(restrict_apps)) if restrict_apps else None,
            "findings": [],
            "plan_ops": [],
            "planned_indexes": [],
            "db_schema_summary": {},
        }
        findings: List[Finding] = []

        # ---- Fetch DB schema snapshot
        db_schema = fetch_db_schema(connection)
        report["db_schema_summary"] = {
            "tables": len(db_schema),
            "indexes_total": sum(len(t.indexes) for t in db_schema.values()),
            "columns_total": sum(len(t.columns) for t in db_schema.values()),
        }

        # ---- Plan ops (for context)
        plan_ops = collect_migration_plan(executor, restrict_apps)
        report["plan_ops"] = [asdict(o) for o in plan_ops]

        # ---- Planned indexes
        planned_indexes = collect_planned_indexes(connection, executor, restrict_apps)
        report["planned_indexes"] = [asdict(p) for p in planned_indexes]

        if show_plan or (not do_diff and not do_risks and not show_plan):
            self.stdout.write(self.style.MIGRATE_HEADING("Migration plan (forward ops):"))
            for o in plan_ops[:400]:
                self.stdout.write(f"- {o.app}.{o.migration}: {o.op} model={o.model or '-'}")
            if len(plan_ops) > 400:
                self.stdout.write(f"... ({len(plan_ops)-400} more ops)")

        # ---- Diff (basic)
        if do_diff:
            self.stdout.write(self.style.MIGRATE_HEADING("DB diff (basic):"))
            planned_tables = set([p.table for p in planned_indexes])
            orphan_tables = sorted([t for t in db_schema.keys() if t not in planned_tables and t != "django_migrations"])
            if orphan_tables:
                findings.append(Finding(
                    level="WARN",
                    code="DB_ORPHAN_TABLES",
                    message="Tables exist in DB but not referenced by planned indexes (heuristic).",
                    details={
                        "tables": orphan_tables[:200],
                        "count": len(orphan_tables),
                        "recommendations": [
                            _rec(
                                "REVIEW_ORPHAN_TABLES",
                                "If these tables are remnants from old apps/migrations, consider dropping them (dev) "
                                "or creating a migration to manage them explicitly (prod).",
                            )
                        ],
                    },
                ))
                self.stdout.write(self.style.WARNING(f"- Orphan-ish tables detected: {len(orphan_tables)} (see JSON for list)"))
            else:
                self.stdout.write(self.style.SUCCESS("- No orphan-ish tables detected (heuristic)."))

            missing_tables = sorted([t for t in planned_tables if t not in db_schema])
            if missing_tables:
                findings.append(Finding(
                    level="INFO",
                    code="DB_MISSING_PLANNED_TABLES",
                    message="Some planned tables are not in DB yet (normal if not migrated).",
                    details={
                        "tables": missing_tables[:200],
                        "count": len(missing_tables),
                        "recommendations": [
                            _rec("RUN_MIGRATIONS", "Run python manage.py migrate to create missing tables.")
                        ],
                    },
                ))
                self.stdout.write(f"- Planned tables not in DB (yet): {len(missing_tables)}")
            else:
                self.stdout.write("- All planned tables exist in DB (at least for planned indexes).")

        # ---- Risks
        if do_risks:
            self.stdout.write(self.style.MIGRATE_HEADING("Risk analysis:"))

            # 1061 risk
            dup_names = 0
            for p in planned_indexes:
                if p.table in db_schema and p.name in db_schema[p.table].indexes:
                    dup_names += 1
                    existing = db_schema[p.table].indexes[p.name]
                    recs = recommend_for_1061(connection.vendor, db_schema, p)
                    findings.append(Finding(
                        level="WARN",
                        code="MYSQL_1061_RISK",
                        message="Planned index name already exists on table (may trigger 1061 if Django tries to create it).",
                        details={
                            "table": p.table,
                            "index": p.name,
                            "planned_columns": p.columns,
                            "db_columns": existing.columns,
                            "planned_unique": p.unique,
                            "db_unique": existing.unique,
                            "origin": p.origin,
                            "recommendations": recs,
                        },
                    ))
            if dup_names:
                self.stdout.write(self.style.WARNING(f"- 1061 risk: {dup_names} planned index name(s) already exist in DB."))
            else:
                self.stdout.write(self.style.SUCCESS("- 1061 risk: none detected from planned indexes."))

            # 1071 risk (MySQL only)
            if connection.vendor == "mysql":
                limit = mysql_max_key_bytes if mysql_max_key_bytes is not None else 1000
                risky_1071 = 0

                for p in planned_indexes:
                    if not p.columns:
                        continue

                    est, notes, classification = _estimate_mysql_index_bytes(p.table, p.columns, db_schema)

                    if est is None:
                        # V2: classification-aware severity + recommendations
                        level = "INFO" if classification in ("TABLE_MISSING", "COLUMN_MISSING") else "WARN"
                        recs = recommend_for_unknown(classification, p, notes)
                        findings.append(Finding(
                            level=level,
                            code="MYSQL_INDEX_SIZE_UNKNOWN",
                            message="Cannot estimate MySQL index size.",
                            details={
                                "table": p.table,
                                "index": p.name,
                                "columns": p.columns,
                                "origin": p.origin,
                                "notes": notes,
                                "classification": classification,
                                "recommendations": recs,
                            },
                        ))
                        continue

                    if est > limit:
                        risky_1071 += 1
                        recs = recommend_for_1071(p, est, limit)
                        findings.append(Finding(
                            level="ERROR",
                            code="MYSQL_1071_RISK",
                            message=f"MySQL/MariaDB key too long risk: estimated {est} bytes > limit {limit}.",
                            details={
                                "table": p.table,
                                "index": p.name,
                                "columns": p.columns,
                                "estimated_bytes": est,
                                "limit_bytes": limit,
                                "origin": p.origin,
                                "recommendations": recs,
                            },
                        ))

                if risky_1071:
                    self.stdout.write(self.style.ERROR(f"- 1071 risk: {risky_1071} planned index(es) likely exceed {limit} bytes."))
                else:
                    self.stdout.write(self.style.SUCCESS(f"- 1071 risk: none detected (limit={limit} bytes)."))
            else:
                self.stdout.write("- 1071 risk: skipped (not MySQL/MariaDB).")

        # ---- Auto-fix (V2) for 1061
        fixes_applied = []
        if do_risks and fix_1061 != "none":
            if connection.vendor != "mysql":
                self.stdout.write(self.style.WARNING("Auto-fix 1061 is implemented for MySQL/MariaDB only."))
            else:
                # collect 1061 findings
                f1061 = [f for f in findings if f.code == "MYSQL_1061_RISK"]
                if f1061:
                    self.stdout.write(self.style.MIGRATE_HEADING(f"Auto-fix 1061 ({fix_1061})"))
                for f in f1061:
                    table = f.details.get("table")
                    idx = f.details.get("index")
                    recs = f.details.get("recommendations") or []

                    if fix_1061 == "drop":
                        # only do if a DROP recommendation exists
                        drop_sql = None
                        for r in recs:
                            if r.get("action") == "DROP_STALE_INDEX" and r.get("sql"):
                                drop_sql = r["sql"]
                                break
                        # If index exists and we want drop anyway, allow drop even if "FAKE" recommended:
                        if drop_sql is None and table and idx:
                            drop_sql = f"DROP INDEX `{idx}` ON `{table}`;"

                        if drop_sql and emit_sql:
                            self.stdout.write(drop_sql)

                        if dry_run:
                            self.stdout.write(self.style.WARNING(f"DRY-RUN: would drop {table}.{idx}"))
                            fixes_applied.append({"action": "DROP_INDEX", "table": table, "index": idx, "dry_run": True})
                        else:
                            try:
                                mysql_drop_index(connection, table, idx)
                                self.stdout.write(self.style.SUCCESS(f"[DROP] {table}.{idx}"))
                                fixes_applied.append({"action": "DROP_INDEX", "table": table, "index": idx, "dry_run": False})
                            except Exception as e:
                                self.stdout.write(self.style.ERROR(f"[ERR] drop {table}.{idx}: {e}"))

                    elif fix_1061 == "fake":
                        # We don't apply; we print the command(s)
                        for r in recs:
                            if r.get("action") == "FAKE_MIGRATION" and r.get("command"):
                                cmd = r["command"]
                                self.stdout.write(self.style.WARNING(f"SUGGESTED: {cmd}"))
                                fixes_applied.append({"action": "FAKE_MIGRATION", "command": cmd})
                                break

        # ---- Print findings + recommended actions
        if findings:
            self.stdout.write(self.style.MIGRATE_HEADING("Findings summary:"))
            order = {"ERROR": 0, "WARN": 1, "INFO": 2}
            findings_sorted = sorted(findings, key=lambda f: (order.get(f.level, 9), f.code))
            for f in findings_sorted[:80]:
                sty = self.style.ERROR if f.level == "ERROR" else (self.style.WARNING if f.level == "WARN" else self.style.NOTICE)
                self.stdout.write(sty(f"[{f.level}] {f.code}: {f.message}"))
                if verbose:
                    self.stdout.write(f"  details={json.dumps(f.details, ensure_ascii=False)[:1400]}")

                if recommend_on:
                    recs = f.details.get("recommendations") or []
                    for r in recs[:5]:
                        line = f"  -> {r.get('action')}: {r.get('rationale')}"
                        if r.get("command"):
                            line += f" | cmd: {r['command']}"
                        if r.get("sql"):
                            line += f" | sql: {r['sql']}"
                        self.stdout.write(line)

            if len(findings_sorted) > 80:
                self.stdout.write(f"... ({len(findings_sorted)-80} more findings in JSON)")

        else:
            self.stdout.write(self.style.SUCCESS("No findings."))

        report["findings"] = [asdict(f) for f in findings]
        report["fixes_applied"] = fixes_applied

        if export_json:
            with open(export_json, "w", encoding="utf-8") as fp:
                json.dump(report, fp, ensure_ascii=False, indent=2)
            self.stdout.write(self.style.SUCCESS(f"JSON report written to: {export_json}"))