import uuid

from sqlalchemy import Boolean, ForeignKey, Integer, String, Text
from sqlalchemy.dialects.postgresql import ENUM as PGEnum, JSONB, UUID as PGUUID
from sqlalchemy.orm import Mapped, mapped_column

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

menu_status = PGEnum("active", "inactive", name="menu_status", create_type=False)
menu_link_type = PGEnum("existing_feature", "new_feature", name="menu_link_type", create_type=False)
menu_feature_status = PGEnum("coming_soon", "external_link", name="menu_feature_status", create_type=False)
widget_type = PGEnum(
    "kpi_card", "bar_chart", "line_chart", "pie_chart", "table", "filter",
    "area_chart", "combo_chart", "funnel_chart", "radar_chart", "treemap", "gauge", "leaderboard", "text",
    name="widget_type", create_type=False,
)
widget_size = PGEnum("small", "medium", "large", name="widget_size", create_type=False)
refresh_interval = PGEnum("real_time", "every_15_min", "hourly", name="refresh_interval", create_type=False)


class Menu(UUIDPKMixin, TenantScopedMixin, TimestampMixin, Base):
    __tablename__ = "menus"

    label: Mapped[str] = mapped_column(String(100), nullable=False)
    icon: Mapped[str] = mapped_column(String(100), nullable=False)
    # Short blurb shown under the label in the Public Portal's top-bar mega
    # menu (PortalMenu.tsx's TopGroup) — optional, purely presentational.
    description: Mapped[str | None] = mapped_column(String(300))
    link_type: Mapped[str] = mapped_column(menu_link_type, nullable=False)
    linked_screen: Mapped[str | None] = mapped_column(String(200))
    target_type: Mapped[str | None] = mapped_column(String(20))  # "master" | "form" — links to one specific record
    target_id: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True))
    group_label: Mapped[str | None] = mapped_column(String(100))  # items sharing a label render under one sidebar section
    feature_name: Mapped[str | None] = mapped_column(String(200))
    feature_status: Mapped[str | None] = mapped_column(menu_feature_status)
    external_link_url: Mapped[str | None] = mapped_column(String(500))
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0")
    status: Mapped[str] = mapped_column(menu_status, nullable=False, server_default="active")
    # Pillar 7: which side a menu item shows on — the internal Studio nav,
    # the vendor-facing Public Portal, or both. Studio design endpoints stay
    # off-limits to a vendor token regardless (see deny_vendor in
    # app/core/permissions.py), so "vendor"/"both" only ever surfaces
    # Pages/Forms/Masters-record-view style targets in practice.
    audience: Mapped[str] = mapped_column(String(20), nullable=False, server_default="internal")


class MenuRole(Base):
    __tablename__ = "menu_roles"

    menu_id: Mapped[uuid.UUID] = mapped_column(PGUUID(as_uuid=True), ForeignKey("menus.id", ondelete="CASCADE"), primary_key=True)
    role_id: Mapped[uuid.UUID] = mapped_column(PGUUID(as_uuid=True), ForeignKey("roles.id", ondelete="CASCADE"), primary_key=True)
    visible: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default="false")


class Dashboard(UUIDPKMixin, TenantScopedMixin, TimestampMixin, Base):
    __tablename__ = "dashboards"

    name: Mapped[str] = mapped_column(String(200), nullable=False)
    status: Mapped[str] = mapped_column(publish_status, nullable=False, server_default="draft")
    # Named values the dashboard's own filter widgets are wired to — see
    # app/schemas/navigation.py::DashboardParameterDef. Currently a
    # forward-compatible placeholder: a filter widget's Widget.config.targets
    # wires it directly to the widget ids it controls (see WidgetConfig), so
    # this list isn't read at query time yet.
    parameters: Mapped[list] = mapped_column(JSONB, nullable=False, server_default="[]")
    created_by: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("users.id"))


class DashboardRole(Base):
    __tablename__ = "dashboard_roles"

    dashboard_id: Mapped[uuid.UUID] = mapped_column(
        PGUUID(as_uuid=True), ForeignKey("dashboards.id", ondelete="CASCADE"), primary_key=True
    )
    role_id: Mapped[uuid.UUID] = mapped_column(PGUUID(as_uuid=True), ForeignKey("roles.id", ondelete="CASCADE"), primary_key=True)


class Widget(UUIDPKMixin, TenantScopedMixin, TimestampMixin, Base):
    __tablename__ = "widgets"

    dashboard_id: Mapped[uuid.UUID] = mapped_column(
        PGUUID(as_uuid=True), ForeignKey("dashboards.id", ondelete="CASCADE"), nullable=False
    )
    type: Mapped[str] = mapped_column(widget_type, nullable=False)
    data_source: Mapped[str] = mapped_column(String(200), nullable=False)
    size: Mapped[str] = mapped_column(widget_size, nullable=False, server_default="medium")
    color: Mapped[str] = mapped_column(String(20), nullable=False)
    refresh_interval: Mapped[str] = mapped_column(refresh_interval, nullable=False, server_default="hourly")
    connector_id: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("connectors.id"))
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0")
    # Everything type-specific that isn't one of the flat columns above — axis
    # fields, series/aggregate/expression definitions, layout grid position,
    # KPI icon, filter-widget mode/target/wiring, click action. All-optional
    # shape (see app/schemas/navigation.py::WidgetConfig): a widget saved
    # before this column existed has config == {}, and every reader treats
    # each field's absence as "use the legacy inferred behavior" so old
    # widgets keep rendering unchanged.
    config: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default="{}")


class Page(UUIDPKMixin, TenantScopedMixin, TimestampMixin, Base):
    __tablename__ = "pages"

    name: Mapped[str] = mapped_column(String(200), nullable=False)
    # Each block's own free-form-canvas position (PageBlockDef.layout) lives
    # inside this JSONB column, not a separate top-level field — same
    # convention as every other per-block field added over time (filters,
    # row_actions, ...). See app/schemas/navigation.py::PageBlockDef.
    blocks: Mapped[list] = mapped_column(JSONB, nullable=False, server_default="[]")
    # Named values this page reads from its own URL query string — see
    # app/schemas/navigation.py::PageParameterDef for how a block's filters
    # or a row action's param_mappings reference these by name.
    parameters: Mapped[list] = mapped_column(JSONB, nullable=False, server_default="[]")
    # Lifecycle hooks — currently just "on_load" — see
    # app/schemas/navigation.py::PageEventAction (mirrors forms.events).
    events: Mapped[list] = mapped_column(JSONB, nullable=False, server_default="[]")
    # Page variables (app/schemas/actions.py::PageVariableDef).
    variables: Mapped[list] = mapped_column(JSONB, nullable=False, server_default="[]")
    # Authored pixel width of the page root's free-form canvas — see
    # PageBlockDef.layout's docstring and PageRunner.tsx's responsive scaling.
    canvas_width: Mapped[int] = mapped_column(Integer, nullable=False, server_default="1200")
    # "scale" | "fluid" — how the page fits a narrower screen (see PageFitMode).
    fit_mode: Mapped[str] = mapped_column(String(10), nullable=False, server_default="scale")
    # The columns above are the designer's working copy; visitors see the
    # PageVersion of this number (app/models/access.py). None = never published.
    published_version: Mapped[int | None] = mapped_column(Integer)
    status: Mapped[str] = mapped_column(publish_status, nullable=False, server_default="draft")
    # The public website (app/routers/public_site.py): this page's address
    # there, /site/<tenant site slug>/<slug>, and whether it's on the site at
    # all. Both apply at once — only the published version is ever shown.
    slug: Mapped[str | None] = mapped_column(String(120))
    is_public: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default="false")
    # "blocks" (the designer's canvas) or "html": the page is one whole HTML
    # document, sanitized on save (app/core/html_sanitize.py). Versioned.
    content_mode: Mapped[str] = mapped_column(String(10), nullable=False, server_default="blocks")
    html_document: Mapped[str | None] = mapped_column(Text)
    # Search-engine and social-card metadata (app/schemas/public_site.py::PageSeo). Versioned.
    seo: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default="{}")
    created_by: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("users.id"))