"""Regression: legacy ``whatsapp_log`` tables self-heal at startup.

Producer/consumer for the CT115 defect (Mumuni relay #747): the live demo DB's
``whatsapp_log`` table still has the pre-webhook shape — ``command`` instead of
``message_text`` and no ``wa_message_id``/``ticket_number`` — so
``GET /api/whatsapp/mock-log`` 500'd with ``OperationalError: no such column:
whatsapp_log.message_text`` even for a valid admin token.

Producer: build the legacy-shaped table (as the live DB holds it) and seed it
with ``command`` rows.
Consumer: boot the app's startup self-heal (``ensure_legacy_schema``), then read
back through the authenticated mock-log endpoint, insert through the ORM write
path, and re-run the self-heal to prove idempotency.
"""

from __future__ import annotations

from datetime import datetime

import pytest
from sqlalchemy import text

from app.core.database import async_session_factory, engine
from app.main import ensure_legacy_schema

pytestmark = pytest.mark.asyncio

# Pre-webhook shape (commit 4afdc36 changed the model but no migration shipped):
# id, command (NOT NULL), from_number, ticket_id, received_at.
LEGACY_WHATSAPP_LOG_DDL = (
    "CREATE TABLE whatsapp_log ("
    "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "
    "command TEXT NOT NULL, "
    "from_number VARCHAR(50), "
    "ticket_id INTEGER, "
    "received_at DATETIME NOT NULL)"
)

EXPECTED_MODEL_COLUMNS = {
    "id",
    "from_number",
    "message_text",
    "wa_message_id",
    "ticket_id",
    "ticket_number",
    "received_at",
}


async def _login(client, email="wahab@denya.com", password="denya123") -> str:
    resp = await client.post("/api/auth/login", json={"email": email, "password": password})
    assert resp.status_code == 200, resp.text
    return resp.json()["access_token"]


def _auth(token: str) -> dict[str, str]:
    return {"Authorization": f"Bearer {token}"}


async def _replace_with_legacy_whatsapp_log() -> None:
    """Drop the model-shaped table and recreate the legacy shape with rows."""
    async with engine.begin() as conn:
        await conn.execute(text("DROP TABLE IF EXISTS whatsapp_log"))
        await conn.execute(text(LEGACY_WHATSAPP_LOG_DDL))
        await conn.execute(
            text(
                "INSERT INTO whatsapp_log (command, from_number, ticket_id, received_at) VALUES "
                "('legacy older message', '+233200000001', 1, '2026-09-08 08:00:00'), "
                "('legacy newest message', '+233200000002', 2, '2026-09-09 09:30:00')"
            )
        )


async def _columns() -> set[str]:
    async with engine.begin() as conn:
        result = await conn.execute(text("PRAGMA table_info(whatsapp_log)"))
        return {row[1] for row in result}


async def test_legacy_whatsapp_log_self_heals_and_mock_log_200(client):
    """Producer: legacy whatsapp_log (command shape).  Consumer: startup self-heal
    then authenticated mock-log — the exact 500 from the live instance."""
    token = await _login(client)
    await _replace_with_legacy_whatsapp_log()

    # Pre-fix reproduction: on the legacy table this endpoint fails exactly as
    # reported (production: HTTP 500; under ASGITransport the app never returns
    # a response so the sqlalchemy OperationalError propagates).
    import sqlalchemy.exc

    with pytest.raises(sqlalchemy.exc.OperationalError):
        await client.get("/api/whatsapp/mock-log", headers=_auth(token))

    # ── Boot the startup self-heal (what lifespan does each boot) ──
    async with engine.begin() as conn:
        await ensure_legacy_schema(conn)

    columns = await _columns()
    assert EXPECTED_MODEL_COLUMNS <= columns
    assert "command" not in columns  # obsolete model-dropped column is gone

    # Authenticated mock-log returns 200 and the backfilled rows are readable.
    resp = await client.get("/api/whatsapp/mock-log", headers=_auth(token))
    assert resp.status_code == 200, resp.text
    entries = resp.json()
    assert [e["message_text"] for e in entries] == [
        "legacy newest message",
        "legacy older message",
    ]  # order: received_at desc; bodies preserved from legacy `command`

    # ORM write path works on the healed table (the current app inserts
    # message_text/wa_message_id and never writes `command`).
    from app.models.whatsapp_log import WhatsAppLog

    async with async_session_factory() as session:
        session.add(
            WhatsAppLog(
                from_number="+233200000003",
                message_text="inbound after self-heal",
                wa_message_id="wamid.healed.1",
                ticket_id=None,
                ticket_number=None,
                received_at=datetime(2026, 9, 10, 10, 0, 0),
            )
        )
        await session.commit()

    resp = await client.get("/api/whatsapp/mock-log", headers=_auth(token))
    assert resp.status_code == 200, resp.text
    entries = resp.json()
    assert entries[0]["message_text"] == "inbound after self-heal"
    assert entries[0]["from_number"] == "+233200000003"
    assert len(entries) == 3

    # ── Idempotent on the next boot ──
    async with engine.begin() as conn:
        await ensure_legacy_schema(conn)
    columns = await _columns()
    assert EXPECTED_MODEL_COLUMNS <= columns
    assert "command" not in columns

    resp = await client.get("/api/whatsapp/mock-log", headers=_auth(token))
    assert resp.status_code == 200, resp.text
    assert len(resp.json()) == 3