from __future__ import annotations

from dataclasses import dataclass
from sqlite3 import Connection

from jarvis_finance.audit.log import record_audit_event
from jarvis_finance.imports.common import utc_now

REVIEW_TOKENS = ("review", "docx", "test", "demo", "synthetic", "dryrun", "dry run", "wizard-test", "wizard test")
REVIEW_SOURCE_TYPES = ("broker_import_reviewed_snapshot", "broker_import_dry_run", "docx_import", "wizard_test", "test", "demo", "synthetic", "dryrun")


@dataclass(frozen=True)
class CleanupPlan:
    account_ids: list[str]
    instrument_ids: list[str]
    transaction_ids: list[str]
    alert_ids: list[str]
    manual_position_count: int

    @property
    def artifact_count(self) -> int:
        return len(self.account_ids) + len(self.instrument_ids) + len(self.transaction_ids)


def _token_sql(alias: str, columns: list[str]) -> str:
    parts = []
    for col in columns:
        for token in REVIEW_TOKENS:
            parts.append(f"lower(coalesce({alias}.{col},'')) LIKE '%{token}%'")
    return " OR ".join(parts) or "0"


def identify_review_test_equity_artifacts(conn: Connection) -> CleanupPlan:
    """Find clearly non-manual review/test/DOCX equity artifacts without exposing values.

    Conservative by design: manual_dashboard/manual_position_add data is excluded unless the
    account itself is clearly a review/test account. Crypto tables are never touched.
    """
    account_where = _token_sql("a", ["account_name", "notes"])
    platform_where = _token_sql("p", ["name", "notes"])
    account_rows = conn.execute(
        f"""
        SELECT DISTINCT a.account_id
        FROM accounts a JOIN platforms p ON p.platform_id=a.platform_id
        WHERE ({account_where} OR {platform_where})
        """
    ).fetchall()
    account_ids = sorted({row["account_id"] for row in account_rows})

    source_placeholders = ",".join("?" for _ in REVIEW_SOURCE_TYPES)
    tx_rows = conn.execute(
        f"""
        SELECT DISTINCT t.transaction_id
        FROM transactions t
        JOIN instruments i ON i.instrument_id=t.instrument_id
        LEFT JOIN accounts a ON a.account_id=t.account_id
        WHERE lower(i.asset_class) IN ('stock','equity','etf')
          AND coalesce(t.is_voided,0)=0
          AND (
              t.source_type IN ({source_placeholders})
              OR lower(coalesce(t.source_type,'')) LIKE '%review%'
              OR lower(coalesce(t.source_id,'')) LIKE '%review%'
              OR lower(coalesce(t.notes,'')) LIKE '%review%'
              OR lower(coalesce(t.notes,'')) LIKE '%docx%'
              OR lower(coalesce(t.notes,'')) LIKE '%synthetic%'
              OR lower(coalesce(t.notes,'')) LIKE '%demo%'
              OR lower(coalesce(t.notes,'')) LIKE '%test%'
              OR a.account_id IN ({','.join('?' for _ in account_ids) if account_ids else "''"})
          )
          AND t.source_type NOT IN ('manual_dashboard')
        """,
        (*REVIEW_SOURCE_TYPES, *account_ids),
    ).fetchall()
    transaction_ids = sorted({row["transaction_id"] for row in tx_rows})

    # Instruments are removable only when all their open equity transactions are in the cleanup set.
    if transaction_ids:
        tx_ph = ",".join("?" for _ in transaction_ids)
        instrument_rows = conn.execute(
            f"""
            SELECT i.instrument_id
            FROM instruments i
            WHERE lower(i.asset_class) IN ('stock','equity','etf')
              AND EXISTS (SELECT 1 FROM transactions t WHERE t.instrument_id=i.instrument_id AND t.transaction_id IN ({tx_ph}))
              AND NOT EXISTS (
                  SELECT 1 FROM transactions t
                  WHERE t.instrument_id=i.instrument_id
                    AND coalesce(t.is_voided,0)=0
                    AND t.transaction_id NOT IN ({tx_ph})
              )
            """,
            (*transaction_ids, *transaction_ids),
        ).fetchall()
        instrument_ids = sorted({row["instrument_id"] for row in instrument_rows})
    else:
        instrument_ids = []

    # Deactivate cleanup accounts only when no non-cleanup active transactions remain.
    removable_accounts: list[str] = []
    for account_id in account_ids:
        remaining = conn.execute(
            f"""
            SELECT COUNT(*) AS c FROM transactions
            WHERE account_id=? AND coalesce(is_voided,0)=0
              {'AND transaction_id NOT IN (' + ','.join('?' for _ in transaction_ids) + ')' if transaction_ids else ''}
            """,
            (account_id, *transaction_ids),
        ).fetchone()["c"]
        if int(remaining or 0) == 0:
            removable_accounts.append(account_id)

    if instrument_ids:
        inst_ph = ",".join("?" for _ in instrument_ids)
        alert_rows = conn.execute(f"SELECT alert_id FROM alerts WHERE entity_id IN ({inst_ph})", tuple(instrument_ids)).fetchall()
        alert_ids = sorted({row["alert_id"] for row in alert_rows})
    else:
        alert_ids = []

    manual_position_count = int(conn.execute(
        """
        SELECT COUNT(*) AS c
        FROM transactions t JOIN instruments i ON i.instrument_id=t.instrument_id
        WHERE lower(i.asset_class) IN ('stock','equity','etf')
          AND coalesce(t.is_voided,0)=0
          AND t.source_type IN ('manual_dashboard','vue_manual_position')
        """
    ).fetchone()["c"] or 0)
    return CleanupPlan(removable_accounts, instrument_ids, transaction_ids, alert_ids, manual_position_count)


def apply_review_test_equity_cleanup(conn: Connection, *, plan: CleanupPlan | None = None, note: str = "MVP Real UI Acceptance cleanup") -> str:
    plan = plan or identify_review_test_equity_artifacts(conn)
    now = utc_now()
    if plan.transaction_ids:
        ph = ",".join("?" for _ in plan.transaction_ids)
        conn.execute(
            f"UPDATE transactions SET is_voided=1, voided_at=?, void_reason=?, voided_by='dashboard_cleanup', updated_at=? WHERE transaction_id IN ({ph})",
            (now, note, now, *plan.transaction_ids),
        )
    if plan.alert_ids:
        ph = ",".join("?" for _ in plan.alert_ids)
        conn.execute(
            f"UPDATE alerts SET status='resolved', resolved_at=? WHERE alert_id IN ({ph})",
            (now, *plan.alert_ids),
        )
    if plan.instrument_ids:
        ph = ",".join("?" for _ in plan.instrument_ids)
        conn.execute(f"UPDATE instruments SET is_active=0, updated_at=? WHERE instrument_id IN ({ph})", (now, *plan.instrument_ids))
    if plan.account_ids:
        ph = ",".join("?" for _ in plan.account_ids)
        conn.execute(f"UPDATE accounts SET is_active=0, updated_at=? WHERE account_id IN ({ph})", (now, *plan.account_ids))
    audit_id = record_audit_event(
        conn,
        source="runtime_cleanup",
        action="archive_review_test_equity_artifacts",
        entity_type="runtime_db",
        entity_id="equity_review_test_artifacts",
        old_values={},
        new_values={
            "accounts_archived": len(plan.account_ids),
            "instruments_archived": len(plan.instrument_ids),
            "transactions_voided": len(plan.transaction_ids),
            "alerts_resolved": len(plan.alert_ids),
            "manual_positions_preserved": plan.manual_position_count,
        },
        user_text_note=note,
        confirmed=True,
        created_by="dashboard_cleanup",
    )
    conn.commit()
    return audit_id