import asyncio
from contextlib import asynccontextmanager
import logging
from pathlib import Path
import sqlite3

from fastapi import FastAPI
from fastapi.middleware.cors import CORSMiddleware
from sqlalchemy import inspect, text

from app.api.routes import router, run_safety_stock_notification_scan
from app.core.config import get_settings
from app.core.database import prepare_database
from app.models import order_batch, order_item, product, seasonal_goal, shared_note, supplier_catalog, suspended_order  # noqa: F401
from app.models.order_batch import OrderBatch
from app.models.order_item import OrderItem
from app.models.product import Product


settings = get_settings()
settings.validate_runtime()
logger = logging.getLogger(__name__)


def _safety_stock_poll_interval_seconds() -> int:
    return max(300, settings.safety_stock_notification_poll_seconds)


async def _safety_stock_notification_poll_loop() -> None:
    await asyncio.sleep(min(60, _safety_stock_poll_interval_seconds()))
    while True:
        try:
            await asyncio.to_thread(run_safety_stock_notification_scan, _tenant_database_urls())
        except Exception:
            logger.exception("Safety stock notification scan failed")
        await asyncio.sleep(_safety_stock_poll_interval_seconds())


def _upgrade_product_schema(database_url: str) -> None:
    engine = prepare_database(database_url)
    existing_columns = {column["name"] for column in inspect(engine).get_columns(Product.__tablename__)}
    column_specs = {
        "product_code": "VARCHAR(120)",
        "final_price_vat": "FLOAT",
        "vat_rate": "FLOAT",
        "weight_kg": "FLOAT",
        "unit_price_per_kg": "FLOAT",
        "category": "VARCHAR(120)",
        "notes": "TEXT",
        "units_per_pack": "FLOAT",
        "liters_per_unit": "FLOAT",
    }

    with engine.begin() as connection:
        for column_name, column_type in column_specs.items():
            if column_name in existing_columns:
                continue
            connection.execute(text(f"ALTER TABLE {Product.__tablename__} ADD COLUMN {column_name} {column_type}"))


def _upgrade_order_schema(database_url: str) -> None:
    engine = prepare_database(database_url)
    batch_columns = {column["name"] for column in inspect(engine).get_columns(OrderBatch.__tablename__)}
    item_columns = {column["name"] for column in inspect(engine).get_columns(OrderItem.__tablename__)}

    batch_column_specs = {
        "total_estimated_amount": "FLOAT",
        "fiscal_document_id": "VARCHAR(80)",
        "fiscal_document_name": "VARCHAR(240)",
        "fiscal_document_type": "VARCHAR(40)",
        "fiscal_document_matched_at": "VARCHAR(40)",
    }
    item_column_specs = {
        "final_price_vat_snapshot": "FLOAT",
        "estimated_line_total": "FLOAT",
    }

    with engine.begin() as connection:
        for column_name, column_type in batch_column_specs.items():
            if column_name in batch_columns:
                continue
            connection.execute(text(f"ALTER TABLE {OrderBatch.__tablename__} ADD COLUMN {column_name} {column_type}"))
        for column_name, column_type in item_column_specs.items():
            if column_name in item_columns:
                continue
            connection.execute(text(f"ALTER TABLE {OrderItem.__tablename__} ADD COLUMN {column_name} {column_type}"))


def _tenant_database_urls() -> list[str]:
    registry_path = Path(get_settings().tenancy_registry_database)
    if not registry_path.exists():
        return []

    connection = sqlite3.connect(registry_path)
    try:
        rows = connection.execute("SELECT database_path FROM tenants").fetchall()
    finally:
        connection.close()

    urls: list[str] = []
    seen: set[str] = set()
    for (database_path,) in rows:
        if not database_path:
            continue
        target = f"sqlite:///{Path(database_path)}"
        if target in seen:
            continue
        seen.add(target)
        urls.append(target)
    return urls


@asynccontextmanager
async def lifespan(_: FastAPI):
    settings = get_settings()
    _upgrade_product_schema(settings.database_url)
    _upgrade_order_schema(settings.database_url)
    for database_url in _tenant_database_urls():
        _upgrade_product_schema(database_url)
        _upgrade_order_schema(database_url)
    safety_stock_task = asyncio.create_task(_safety_stock_notification_poll_loop())
    try:
        yield
    finally:
        safety_stock_task.cancel()
        try:
            await safety_stock_task
        except asyncio.CancelledError:
            pass

app = FastAPI(title=settings.app_name, version="0.1.0", lifespan=lifespan)

app.add_middleware(
    CORSMiddleware,
    allow_origins=settings.cors_origins_list,
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

app.include_router(router)