import asyncio
import os
import sqlite3
import sys
from pathlib import Path

sys.path.insert(0, str(Path(__file__).resolve().parents[1]))

from app.services.operational_assistant_service import (
    PlannedToolCall,
    SearchProductsArgs,
    _derive_home_thread_state,
    _extract_catalog_query,
    _build_surface_direct_tool_calls,
    _execute_tool_call,
    _home_requires_hard_direct_execution,
    _normalize_home_planned_tool_calls,
    _normalize_sql_readonly_query,
    _normalize_text,
    _search_products,
    _should_prefer_planner_for_home,
    _timeclock_period_from_message,
)
from app.services.tenant_store import get_tenant_store


def _assert(condition: bool, detail: str) -> None:
    if not condition:
        raise AssertionError(detail)


def _resolve_regression_tenant_id() -> str:
    configured = os.getenv("ASSISTANT_REGRESSION_TENANT_ID", "").strip()
    if configured:
        return configured

    registry_path = Path(os.getenv("TENANCY_REGISTRY_DATABASE", "/data/platform_registry.sqlite3"))
    with sqlite3.connect(registry_path) as connection:
        row = connection.execute("SELECT id FROM tenants WHERE slug = ?", ("ritual",)).fetchone()
    if row is None:
        raise RuntimeError("Tenant 'ritual' non trovato. Imposta ASSISTANT_REGRESSION_TENANT_ID.")
    return str(row[0])


async def main() -> None:
    store = get_tenant_store()
    session = store.build_service_session(_resolve_regression_tenant_id())
    if session is None:
        raise RuntimeError("Sessione di servizio non disponibile per il tenant di regressione.")

    catalog_message = "Trovi rum Gosling nel catalogo dei nostri fornitori?"
    normalized_catalog_message = _normalize_text(catalog_message)
    catalog_direct_calls = _build_surface_direct_tool_calls("home", catalog_message, [], {})
    _assert(
        catalog_direct_calls and catalog_direct_calls[0].tool == "run_tenant_query",
        f"I cataloghi fornitori devono usare il query layer, non {catalog_direct_calls}.",
    )
    _assert(
        _should_prefer_planner_for_home(
            catalog_message,
            normalized_catalog_message,
            [],
            contextual_tool_calls=[],
            direct_tool_calls=catalog_direct_calls,
        ),
        "Le letture catalogo devono preferire il planner LLM.",
    )

    plural_supplier_catalog_message = "Trovi del gosling nei cataloghi fornitori?"
    _assert(
        _extract_catalog_query(plural_supplier_catalog_message) == "gosling",
        "La query catalogo non deve includere la preposizione 'nei'.",
    )
    supplier_any_catalog_message = "Trovi Gosling nel catalogo di qualche fornitore?"
    _assert(
        _extract_catalog_query(supplier_any_catalog_message) == "gosling",
        "La query catalogo non deve diventare 'qualche' quando l'utente dice 'di qualche fornitore'.",
    )
    plural_supplier_catalog_calls = _build_surface_direct_tool_calls("home", plural_supplier_catalog_message, [], {})
    _assert(
        plural_supplier_catalog_calls and plural_supplier_catalog_calls[0].tool == "run_tenant_query",
        "La richiesta 'cataloghi fornitori' non deve finire nello storico acquisti.",
    )
    supplier_any_catalog_calls = _build_surface_direct_tool_calls("home", supplier_any_catalog_message, [], {})
    _assert(
        supplier_any_catalog_calls and supplier_any_catalog_calls[0].tool == "run_tenant_query",
        "La richiesta 'catalogo di qualche fornitore' deve interrogare i cataloghi, non rispondere a vuoto.",
    )
    _assert(
        "gosling" in str(supplier_any_catalog_calls[0].arguments.get("sql") or "").lower()
        and "qualche" not in str(supplier_any_catalog_calls[0].arguments.get("sql") or "").lower(),
        f"La SQL cataloghi deve cercare Gosling, non 'qualche': {supplier_any_catalog_calls[0].arguments}",
    )

    planned_search = [PlannedToolCall(tool="search_products", arguments={"query": "rum", "limit": 10})]
    normalized_search = _normalize_home_planned_tool_calls(catalog_message, planned_search)
    _assert(
        normalized_search[0].arguments.get("query") == "rum gosling",
        "Il normalizzatore deve reinserire il termine distintivo Gosling.",
    )

    normalized_sql = _normalize_sql_readonly_query(
        "SELECT * FROM supplier_catalog_items WHERE lower(product_name) LIKE '%gosling%';"
    )
    _assert("lower(source_name)" in normalized_sql, "Le query supplier_catalog_items devono usare source_name.")

    search_result = _search_products(session, SearchProductsArgs(query="rum gosling", limit=5000))
    product_names = [str(item.get("product_name") or "") for item in search_result.get("items", [])]
    _assert(product_names == ["GOSLING 1L"], f"Atteso solo GOSLING 1L, trovati: {product_names}")

    first_order_message = "Mostrami il primo ordine del 2025"
    first_order_calls = _build_surface_direct_tool_calls("home", first_order_message, [], {})
    _assert(
        first_order_calls and first_order_calls[0].tool == "get_purchase_batches",
        f"'Primo ordine' deve usare get_purchase_batches, trovati: {first_order_calls}",
    )
    first_order_args = first_order_calls[0].arguments
    _assert(
        first_order_args.get("sort_order") == "earliest"
        and first_order_args.get("limit") == 1
        and first_order_args.get("year") == 2025,
        f"'Primo ordine del 2025' deve essere earliest/limit 1/year 2025, args: {first_order_args}",
    )
    planner_first_order_calls = _normalize_home_planned_tool_calls(
        first_order_message,
        [PlannedToolCall(tool="get_purchase_history", arguments={"year": 2025, "limit": 1})],
    )
    _assert(
        planner_first_order_calls
        and planner_first_order_calls[0].tool == "get_purchase_batches"
        and planner_first_order_calls[0].arguments.get("sort_order") == "earliest"
        and planner_first_order_calls[0].arguments.get("limit") == 1,
        f"Il normalizzatore non deve trasformare 'primo ordine' in lista completa: {planner_first_order_calls}",
    )
    first_order_result = await _execute_tool_call(session, first_order_calls[0], prior_tool_results=[])
    first_order_batches = first_order_result.get("result", {}).get("batches", [])
    _assert(
        len(first_order_batches) == 1,
        f"'Primo ordine del 2025' deve restituire un solo ordine, trovati: {len(first_order_batches)}",
    )
    second_order_message = "Mostrami il secondo ordine del 2025"
    second_order_calls = _build_surface_direct_tool_calls(
        "home",
        second_order_message,
        [],
        {"purchase_query": "timbrature", "purchase_view": "get_purchase_batches", "last_tool": "get_purchase_batches"},
    )
    _assert(
        second_order_calls
        and second_order_calls[0].tool == "get_purchase_batches"
        and second_order_calls[0].arguments.get("query") == ""
        and second_order_calls[0].arguments.get("sort_order") == "earliest"
        and second_order_calls[0].arguments.get("limit") == 2
        and second_order_calls[0].arguments.get("year") == 2025,
        f"'Secondo ordine del 2025' non deve ereditare query sporche e deve usare earliest/limit 2: {second_order_calls}",
    )
    planner_second_order_calls = _normalize_home_planned_tool_calls(
        second_order_message,
        [PlannedToolCall(tool="get_purchase_history", arguments={"query": "secondo", "year": 2025, "limit": 1})],
    )
    _assert(
        planner_second_order_calls
        and planner_second_order_calls[0].tool == "get_purchase_batches"
        and planner_second_order_calls[0].arguments.get("query") == ""
        and planner_second_order_calls[0].arguments.get("sort_order") == "earliest"
        and planner_second_order_calls[0].arguments.get("limit") == 2,
        f"Il normalizzatore deve correggere 'secondo ordine' in secondo ordine cronologico: {planner_second_order_calls}",
    )
    first_laconi_message = "mostrami il primo ordine di laconi del 2025"
    first_laconi_calls = _build_surface_direct_tool_calls("home", first_laconi_message, [], {})
    _assert(
        first_laconi_calls
        and first_laconi_calls[0].tool == "get_purchase_batches"
        and first_laconi_calls[0].arguments.get("query") == "laconi"
        and first_laconi_calls[0].arguments.get("sort_order") == "earliest"
        and first_laconi_calls[0].arguments.get("limit") == 1,
        f"'Primo ordine di laconi' deve mantenere il filtro prodotto: {first_laconi_calls}",
    )
    planner_first_laconi_calls = _normalize_home_planned_tool_calls(
        first_laconi_message,
        [PlannedToolCall(tool="get_purchase_history", arguments={"query": "laconi", "year": 2025, "limit": 1})],
    )
    _assert(
        planner_first_laconi_calls
        and planner_first_laconi_calls[0].tool == "get_purchase_batches"
        and planner_first_laconi_calls[0].arguments.get("query") == "laconi"
        and planner_first_laconi_calls[0].arguments.get("sort_order") == "earliest"
        and planner_first_laconi_calls[0].arguments.get("limit") == 1,
        f"Il normalizzatore non deve perdere 'laconi': {planner_first_laconi_calls}",
    )

    reservation_message = "Crea una prenotazione per Mario domani alle 20 per 4 persone"
    reservation_direct_calls = _build_surface_direct_tool_calls("home", reservation_message, [], {})
    _assert(
        _home_requires_hard_direct_execution(
            reservation_message,
            _normalize_text(reservation_message),
            reservation_direct_calls,
        ),
        "Le scritture prenotazione devono restare hard-direct.",
    )

    timeclock_message = "quanto ha lavorato Ale Cioeta?"
    timeclock_direct_calls = _build_surface_direct_tool_calls("home", timeclock_message, [], {})
    _assert(
        timeclock_direct_calls and timeclock_direct_calls[0].tool == "get_timeclock_summary",
        "Le domande sulle ore lavorate devono usare get_timeclock_summary.",
    )
    _assert(
        not _should_prefer_planner_for_home(
            timeclock_message,
            _normalize_text(timeclock_message),
            [],
            contextual_tool_calls=[],
            direct_tool_calls=timeclock_direct_calls,
        ),
        "Le domande semplici sui turni non devono passare dal planner SQL.",
    )
    team_month_timeclock_message = "quante ore hanno fatto i ragazzi questo mese?"
    team_month_timeclock_calls = _build_surface_direct_tool_calls("home", team_month_timeclock_message, [], {})
    _assert(
        team_month_timeclock_calls
        and team_month_timeclock_calls[0].tool == "get_timeclock_summary"
        and team_month_timeclock_calls[0].arguments.get("start_date")
        and team_month_timeclock_calls[0].arguments.get("end_date")
        and team_month_timeclock_calls[0].arguments.get("limit") is not None,
        f"'quante ore ... ragazzi questo mese' deve essere una lettura Turni mensile valida: {team_month_timeclock_calls}",
    )
    planner_team_month_calls = _normalize_home_planned_tool_calls(
        team_month_timeclock_message,
        [
            PlannedToolCall(
                tool="get_timeclock_summary",
                arguments={"query_text": "ragazzi", "scope": "month", "target_date": "2026-04-29", "limit": None},
            )
        ],
    )
    _assert(
        planner_team_month_calls
        and planner_team_month_calls[0].tool == "get_timeclock_summary"
        and planner_team_month_calls[0].arguments.get("scope") == "all"
        and planner_team_month_calls[0].arguments.get("start_date")
        and planner_team_month_calls[0].arguments.get("end_date")
        and planner_team_month_calls[0].arguments.get("limit") is not None,
        f"Il planner Turni con scope month/null limit deve essere normalizzato prima della validazione: {planner_team_month_calls}",
    )
    for temporal_message in (
        "quante ore hanno fatto i ragazzi dopodomani?",
        "quante ore hanno fatto i ragazzi settimana prossima?",
        "quante ore hanno fatto i ragazzi mese prossimo?",
    ):
        normalized_temporal_message = _normalize_text(temporal_message)
        scope, target_date, start_date, end_date = _timeclock_period_from_message(temporal_message, normalized_temporal_message)
        _assert(
            bool(target_date) or (bool(start_date) and bool(end_date)) or scope == "active",
            f"Il riferimento temporale non e' stato risolto per '{temporal_message}': {(scope, target_date, start_date, end_date)}",
        )
    timeclock_year_message = "quanto ha lavorato ale cioeta nel 2026?"
    timeclock_year_calls = _build_surface_direct_tool_calls("home", timeclock_year_message, [], {})
    _assert(
        timeclock_year_calls and timeclock_year_calls[0].tool == "get_timeclock_summary",
        "Le domande dirette sui turni con anno devono usare get_timeclock_summary.",
    )
    timeclock_year_args = timeclock_year_calls[0].arguments
    _assert(
        timeclock_year_args.get("scope") == "all"
        and timeclock_year_args.get("start_date") == "2026-01-01"
        and timeclock_year_args.get("end_date") == "2026-12-31",
        f"La domanda diretta sui turni nel 2026 non deve interrogare solo oggi, args: {timeclock_year_args}",
    )
    timeclock_state = _derive_home_thread_state(
        previous_state={},
        message=timeclock_message,
        executed_tool_calls=timeclock_direct_calls,
        tool_results=[
            {
                "tool": "get_timeclock_summary",
                "result": {
                    "resolved_user": {
                        "user_id": "user_ale",
                        "name": "Ale Cioeta",
                        "username": "ale",
                        "email": "ale@example.test",
                    }
                },
            }
        ],
        route="test",
        conversation=[],
    )
    timeclock_followup_calls = _build_surface_direct_tool_calls(
        "home",
        "e in generale nel 2026?",
        [
            {"role": "user", "content": timeclock_message},
            {"role": "assistant", "content": "Non vedo timbrature per oggi."},
        ],
        timeclock_state,
    )
    _assert(
        timeclock_followup_calls and timeclock_followup_calls[0].tool == "get_timeclock_summary",
        f"Il follow-up sui turni deve mantenere il contesto, trovati: {timeclock_followup_calls}",
    )
    timeclock_followup_args = timeclock_followup_calls[0].arguments
    _assert(
        timeclock_followup_args.get("query_text") == timeclock_message,
        "Il follow-up sul 2026 deve riusare la persona della domanda precedente.",
    )
    _assert(
        timeclock_followup_args.get("start_date") == "2026-01-01"
        and timeclock_followup_args.get("end_date") == "2026-12-31",
        f"Il follow-up sul 2026 deve interrogare tutto l'anno, args: {timeclock_followup_args}",
    )
    dirty_timeclock_followup_calls = _build_surface_direct_tool_calls(
        "home",
        "e in generale nel 2026?",
        [
            {"role": "user", "content": "Trovi del gosling nei cataloghi fornitori?"},
            {"role": "assistant", "content": "Ho trovato questi dati acquisti reali..."},
            {"role": "user", "content": timeclock_message},
            {"role": "assistant", "content": "Non vedo timbrature per oggi."},
            {"role": "user", "content": "e in generale nel 2026?"},
            {"role": "assistant", "content": "Non riesco ancora a leggere questo dato operativo."},
        ],
        {"purchase_query": "gosling nei", "purchase_view": "get_purchase_overview", "last_tool": "get_purchase_overview"},
    )
    _assert(
        dirty_timeclock_followup_calls and dirty_timeclock_followup_calls[0].tool == "get_timeclock_summary",
        "Anche un thread gia sporco deve recuperare l'ultima domanda reale sui turni.",
    )
    _assert(
        dirty_timeclock_followup_calls[0].arguments.get("query_text") == timeclock_message,
        f"Il thread sporco deve recuperare Ale Cioeta, args: {dirty_timeclock_followup_calls[0].arguments}",
    )
    reservation_after_timeclock_calls = _build_surface_direct_tool_calls(
        "home",
        "abbiamo prenotazioni per oggi?",
        [
            {"role": "user", "content": timeclock_year_message},
            {"role": "assistant", "content": "Nel periodo richiesto Ale Cioeta ha lavorato 0h 01m."},
            {"role": "user", "content": "e oggi?"},
            {"role": "assistant", "content": "Non vedo timbrature per oggi."},
        ],
        {
            "last_tool": "get_timeclock_summary",
            "timeclock_query_text": timeclock_year_message,
            "timeclock_scope": "today",
            "timeclock_target_date": "2026-04-26",
        },
    )
    _assert(
        reservation_after_timeclock_calls and reservation_after_timeclock_calls[0].tool == "get_reservations_snapshot",
        f"Un cambio contesto esplicito sulle prenotazioni non deve restare sui turni: {reservation_after_timeclock_calls}",
    )
    _assert(
        not _should_prefer_planner_for_home(
            "abbiamo prenotazioni per oggi?",
            _normalize_text("abbiamo prenotazioni per oggi?"),
            [],
            contextual_tool_calls=[],
            direct_tool_calls=reservation_after_timeclock_calls,
        ),
        "Le letture semplici prenotazioni devono essere eseguite direttamente, non dal planner LLM.",
    )
    reservation_state = _derive_home_thread_state(
        previous_state={
            "last_tool": "get_timeclock_summary",
            "timeclock_query_text": timeclock_year_message,
        },
        message="abbiamo prenotazioni per oggi?",
        executed_tool_calls=reservation_after_timeclock_calls,
        tool_results=[],
        route="test",
        conversation=[],
    )
    _assert(
        reservation_state.get("last_tool") == "get_reservations_snapshot",
        f"Dopo una lettura prenotazioni lo stato non deve restare sui turni: {reservation_state}",
    )
    reservation_followup_calls = _build_surface_direct_tool_calls(
        "home",
        "e domani?",
        [
            {"role": "user", "content": timeclock_year_message},
            {"role": "assistant", "content": "Nel periodo richiesto Ale Cioeta ha lavorato 0h 01m."},
            {"role": "user", "content": "abbiamo prenotazioni per oggi?"},
            {"role": "assistant", "content": "Per oggi risultano alcune prenotazioni."},
        ],
        reservation_state,
    )
    _assert(
        reservation_followup_calls and reservation_followup_calls[0].tool == "get_reservations_snapshot",
        f"Il follow-up dopo prenotazioni non deve tornare ai turni: {reservation_followup_calls}",
    )
    dirty_reservation_followup_calls = _build_surface_direct_tool_calls(
        "home",
        "e domani?",
        [
            {"role": "user", "content": timeclock_year_message},
            {"role": "assistant", "content": "Nel periodo richiesto Ale Cioeta ha lavorato 0h 01m."},
            {"role": "user", "content": "abbiamo prenotazioni per oggi?"},
            {"role": "assistant", "content": "Non vedo timbrature per oggi."},
        ],
        {
            "last_tool": "get_timeclock_summary",
            "timeclock_query_text": timeclock_year_message,
            "timeclock_scope": "today",
        },
    )
    _assert(
        dirty_reservation_followup_calls and dirty_reservation_followup_calls[0].tool == "get_reservations_snapshot",
        f"Anche un thread gia sporco deve rispettare il cambio contesto prenotazioni: {dirty_reservation_followup_calls}",
    )
    newer_purchase_followup_calls = _build_surface_direct_tool_calls(
        "home",
        "e nel 2026?",
        [
            {"role": "user", "content": timeclock_message},
            {"role": "assistant", "content": "Non vedo timbrature per oggi."},
            {"role": "user", "content": "Quanta don julio ordinata nel 2025?"},
            {"role": "assistant", "content": "Nel 2025 risulta ordinata una quantita..."},
        ],
        {"purchase_query": "don julio", "purchase_view": "get_purchase_overview", "last_tool": "get_purchase_overview"},
    )
    _assert(
        not any(tool_call.tool == "get_timeclock_summary" for tool_call in newer_purchase_followup_calls),
        f"Un follow-up acquisti non deve ereditare il vecchio contesto turni: {newer_purchase_followup_calls}",
    )

    direct_result = await _execute_tool_call(session, plural_supplier_catalog_calls[0], prior_tool_results=[])
    direct_rows = direct_result.get("result", {}).get("rows", [])
    direct_names = [str(row.get("display_name") or "") for row in direct_rows if isinstance(row, dict)]
    _assert(
        direct_names == ["GOSLING 1L"],
        f"Il fallback diretto sui cataloghi deve trovare solo GOSLING 1L dal catalogo locale, trovati: {direct_names}",
    )
    supplier_any_result = await _execute_tool_call(session, supplier_any_catalog_calls[0], prior_tool_results=[])
    supplier_any_rows = supplier_any_result.get("result", {}).get("rows", [])
    supplier_any_names = [str(row.get("display_name") or "") for row in supplier_any_rows if isinstance(row, dict)]
    _assert(
        supplier_any_names == ["GOSLING 1L"],
        f"La frase 'catalogo di qualche fornitore' deve trovare GOSLING 1L, trovati: {supplier_any_names}",
    )

    print("assistant regressions: ok")


if __name__ == "__main__":
    asyncio.run(main())