import uuid
from datetime import datetime

from sqlalchemy import ForeignKey, String, Text, text
from sqlalchemy.dialects.postgresql import JSONB, TIMESTAMP, UUID as PGUUID
from sqlalchemy.orm import Mapped, mapped_column

from app.models.base import Base, TenantScopedMixin, TimestampMixin, UUIDPKMixin


class EntityTable(UUIDPKMixin, TenantScopedMixin, TimestampMixin, Base):
    """A datasource or form stored as a real table (the "Table" storage
    tier) — see app/crud/entity_storage.py. `columns` maps each field name
    to its column spec."""

    __tablename__ = "entity_tables"

    source_kind: Mapped[str] = mapped_column(String(10), nullable=False, server_default="master")  # master | form
    master_id: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("masters.id", ondelete="CASCADE"), unique=True)
    form_id: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), unique=True)
    # "building" while a large source's records are copied in (online
    # switching), "ready" once queries may read the table.
    state: Mapped[str] = mapped_column(String(10), nullable=False, server_default="ready")
    table_name: Mapped[str] = mapped_column(String(63), nullable=False, unique=True)
    columns: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default="{}")
    created_by: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"))

    @property
    def source_id(self) -> uuid.UUID:
        return self.master_id if self.source_kind == "master" else self.form_id


class EntityMigration(UUIDPKMixin, TenantScopedMixin, Base):
    """One storage change — promote, migrate or demote — with the DDL it ran.
    `master_id` holds the source's id: a datasource's or a form's."""

    __tablename__ = "entity_migrations"

    master_id: Mapped[uuid.UUID] = mapped_column(PGUUID(as_uuid=True), nullable=False)
    action: Mapped[str] = mapped_column(String(20), nullable=False)
    summary: Mapped[str] = mapped_column(Text, nullable=False)
    statements: Mapped[list] = mapped_column(JSONB, nullable=False, server_default="[]")
    applied_by: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"))
    applied_at: Mapped[datetime] = mapped_column(TIMESTAMP(timezone=True), server_default=text("now()"))