import os
from dotenv import load_dotenv
from sqlalchemy import create_engine, text
from sqlalchemy.orm import declarative_base, sessionmaker
from typing import Generator, Generic, TypeVar, List
from datetime import datetime, timezone
from pydantic import BaseModel, ConfigDict

load_dotenv(override=True)

DB_HOST = os.environ.get("DB_HOST", "localhost")
DB_PORT = os.environ.get("DB_PORT", "5432")
DB_NAME = os.environ.get("DB_NAME", "")
DB_USER = os.environ.get("DB_USER", "")
DB_PASSWORD = os.environ.get("DB_PASSWORD", "")
DB_SCHEMA = os.environ.get("DB_SCHEMA", "")

DATABASE_URL = f"postgresql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"

connect_args: dict = {}
if DB_SCHEMA:
    connect_args["options"] = f"-csearch_path={DB_SCHEMA},public"

engine = create_engine(DATABASE_URL, connect_args=connect_args, pool_pre_ping=True, pool_recycle=3600)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()


def _verify_schema() -> None:
    """Raise at startup if DB_SCHEMA is set but does not exist in the database."""
    if not DB_SCHEMA:
        return
    try:
        with engine.connect() as conn:
            row = conn.execute(
                text("SELECT 1 FROM information_schema.schemata WHERE schema_name = :s"),
                {"s": DB_SCHEMA},
            ).fetchone()
        if row is None:
            raise RuntimeError(
                f"Database schema \"{DB_SCHEMA}\" does not exist. "
                f"Re-run the DDL deployment step (step-07) before starting the backend. "
                f"DB_HOST={DB_HOST} DB_NAME={DB_NAME}"
            )
    except RuntimeError:
        raise
    except Exception:
        pass


_verify_schema()


def get_db() -> Generator:
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


def utc_now() -> datetime:
    return datetime.now(timezone.utc)


T = TypeVar("T")


class PaginatedResponse(BaseModel, Generic[T]):
    items: List[T]
    total: int
    limit: int
    offset: int
    model_config = ConfigDict(from_attributes=True)