from contextlib import asynccontextmanager
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
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, suspended_order  # noqa: F401
from app.models.order_batch import OrderBatch
from app.models.order_item import OrderItem
from app.models.product import Product


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",
    }
    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)
    yield


settings = get_settings()
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)
