import uuid
from typing import Any, Generic, TypeVar

from fastapi import HTTPException
from pydantic import BaseModel
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

ModelT = TypeVar("ModelT")
CreateSchemaT = TypeVar("CreateSchemaT", bound=BaseModel)
UpdateSchemaT = TypeVar("UpdateSchemaT", bound=BaseModel)


class CRUDBase(Generic[ModelT, CreateSchemaT, UpdateSchemaT]):
    """
    Generic list/get/create/update/delete for tables with a single UUID `id`
    primary key and a `tenant_id` column. Every query is additionally scoped
    by tenant_id here even though Postgres RLS (db/schema.sql) already
    enforces it — defense in depth per NFR-SEC-003 ("not only in the UI").

    Tables with composite primary keys (forms, workflows) or without a
    tenant_id of their own (junction tables) are handled by dedicated router
    code instead of this generic class.
    """

    def __init__(self, model: type[ModelT]):
        self.model = model

    async def list(
        self,
        db: AsyncSession,
        tenant_id: uuid.UUID,
        *,
        skip: int = 0,
        limit: int = 100,
        **equality_filters: Any,
    ) -> list[ModelT]:
        stmt = select(self.model).where(self.model.tenant_id == tenant_id)
        for field, value in equality_filters.items():
            if value is not None:
                stmt = stmt.where(getattr(self.model, field) == value)
        stmt = stmt.order_by(self.model.id).offset(skip).limit(limit)
        result = await db.execute(stmt)
        return list(result.scalars().all())

    async def get(self, db: AsyncSession, tenant_id: uuid.UUID, id_: uuid.UUID) -> ModelT:
        stmt = select(self.model).where(self.model.tenant_id == tenant_id, self.model.id == id_)
        result = await db.execute(stmt)
        obj = result.scalar_one_or_none()
        if obj is None:
            raise HTTPException(status_code=404, detail=f"{self.model.__name__} not found")
        return obj

    async def create(
        self, db: AsyncSession, tenant_id: uuid.UUID, obj_in: CreateSchemaT, **extra: Any
    ) -> ModelT:
        data = obj_in.model_dump(exclude_unset=True, mode="json")
        data["tenant_id"] = tenant_id
        data.update(extra)
        obj = self.model(**data)
        db.add(obj)
        await db.flush()
        await db.refresh(obj)
        return obj

    async def update(
        self, db: AsyncSession, tenant_id: uuid.UUID, id_: uuid.UUID, obj_in: UpdateSchemaT, **extra: Any
    ) -> ModelT:
        obj = await self.get(db, tenant_id, id_)
        data = obj_in.model_dump(exclude_unset=True, mode="json")
        # Optimistic concurrency: an update schema may carry the updated_at
        # its caller last read (see app/crud/change_log.py::check_fresh).
        if "expected_updated_at" in data:
            from app.crud.change_log import check_fresh

            check_fresh(obj, getattr(obj_in, "expected_updated_at", None))
            data.pop("expected_updated_at")
        for field, value in data.items():
            setattr(obj, field, value)
        for field, value in extra.items():
            setattr(obj, field, value)
        await db.flush()
        await db.refresh(obj)
        return obj

    async def delete(self, db: AsyncSession, tenant_id: uuid.UUID, id_: uuid.UUID) -> None:
        obj = await self.get(db, tenant_id, id_)
        await db.delete(obj)
        await db.flush()

    async def soft_delete(self, db: AsyncSession, tenant_id: uuid.UUID, id_: uuid.UUID, **extra: Any) -> ModelT:
        """Marks `is_deleted` instead of removing the row — for models that
        opt into the system-managed created_at/updated_at/created_by/
        updated_by/is_deleted audit columns (MasterRecord, FormSubmission).
        `extra` is typically `updated_by=ctx.user_id`."""
        obj = await self.get(db, tenant_id, id_)
        obj.is_deleted = True
        for field, value in extra.items():
            setattr(obj, field, value)
        await db.flush()
        return obj