import uuid
from datetime import datetime

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

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

report_source = PGEnum(
    "workflow_instances", "form_submissions", "approval_log", "sla_compliance", name="report_source", create_type=False
)
report_frequency = PGEnum("one_time", "daily", "weekly", "monthly", name="report_frequency", create_type=False)
connector_type = PGEnum(
    "email_smtp", "email_imap", "realtime_notification", "webhook", "sms", "payment_gateway", "rpa", "database", "file_transfer", "google", "firebase",
    name="connector_type", create_type=False,
)
connector_status = PGEnum("enabled", "disabled", name="connector_status", create_type=False)
dataset_source_type = PGEnum("master", "form", "query", name="dataset_source_type", create_type=False)
dataset_sort_dir = PGEnum("asc", "desc", name="dataset_sort_dir", create_type=False)
dataset_aggregate_fn = PGEnum("count", "sum", "avg", "min", "max", name="dataset_aggregate_fn", create_type=False)


class Report(UUIDPKMixin, TenantScopedMixin, TimestampMixin, Base):
    __tablename__ = "reports"

    name: Mapped[str] = mapped_column(String(200), nullable=False)
    source: Mapped[str] = mapped_column(report_source, nullable=False)
    columns: Mapped[list] = mapped_column(JSONB, nullable=False, server_default="[]")
    filters: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default="{}")
    schedule_frequency: Mapped[str | None] = mapped_column(report_frequency)
    schedule_recipients: Mapped[list | None] = mapped_column(JSONB)
    created_by: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("users.id"))
    last_run_at: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True))


class Connector(UUIDPKMixin, TenantScopedMixin, TimestampMixin, Base):
    __tablename__ = "connectors"

    name: Mapped[str] = mapped_column(String(200), nullable=False)
    type: Mapped[str] = mapped_column(connector_type, nullable=False)
    configuration: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default="{}")
    status: Mapped[str] = mapped_column(connector_status, nullable=False, server_default="disabled")
    last_tested_at: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True))
    last_test_result: Mapped[str | None] = mapped_column(String(500))


class PaymentTransaction(UUIDPKMixin, TenantScopedMixin, TimestampMixin, Base):
    """One payment attempt through a payment_gateway Connector (see
    app/crud/payments.py). `reference` is FlowFabric's own order id — sent to
    the gateway, safe for every gateway's order-id rules — while `label` keeps
    the caller's human reference (e.g. "KSRTC/RTI/00012"). The gateway calls
    back to /payments/callback/{id}; `return_url` is where the browser goes
    after that, with the verified status appended."""

    __tablename__ = "payment_transactions"

    connector_id: Mapped[uuid.UUID] = mapped_column(PGUUID(as_uuid=True), ForeignKey("connectors.id", ondelete="CASCADE"), nullable=False)
    provider: Mapped[str] = mapped_column(String(30), nullable=False)
    reference: Mapped[str] = mapped_column(String(50), nullable=False)
    label: Mapped[str | None] = mapped_column(String(200))
    gateway_reference: Mapped[str | None] = mapped_column(String(200))
    amount: Mapped[int] = mapped_column(nullable=False)  # minor units (paise/cents)
    currency: Mapped[str] = mapped_column(String(3), nullable=False, server_default="INR")
    status: Mapped[str] = mapped_column(String(20), nullable=False, server_default="created")  # created|pending|paid|failed
    provider_status: Mapped[str | None] = mapped_column(String(100))
    return_url: Mapped[str | None] = mapped_column(String(1000))
    checkout: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default="{}")  # how to hand the browser to the gateway
    raw: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default="{}")  # last gateway response/callback, for support
    created_by: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"))


class Dataset(UUIDPKMixin, TenantScopedMixin, TimestampMixin, Base):
    """A saved query over a Master's records or a Form's submissions — built
    in the Data Explorer, then referenceable from a dashboard widget as
    data_source "dataset:<id>" alongside the existing "master:<id>" /
    "form:<id>" bindings. No materialized rows are stored here: only the
    query definition, resolved fresh on read (see app/crud/dataset_query.py)."""

    __tablename__ = "datasets"

    name: Mapped[str] = mapped_column(String(200), nullable=False)
    description: Mapped[str | None] = mapped_column(String(500))
    source_type: Mapped[str] = mapped_column(dataset_source_type, nullable=False)
    source_id: Mapped[uuid.UUID] = mapped_column(PGUUID(as_uuid=True), nullable=False)
    columns: Mapped[list] = mapped_column(JSONB, nullable=False, server_default="[]")
    filters: Mapped[list] = mapped_column(JSONB, nullable=False, server_default="[]")
    sort_field: Mapped[str | None] = mapped_column(String(200))
    sort_dir: Mapped[str] = mapped_column(dataset_sort_dir, nullable=False, server_default="asc")
    # Optional group-by-and-count/sum/avg/min/max — when set, resolve_rows
    # returns one row per distinct group_by value instead of raw records
    # (see app/crud/dataset_query.py's _aggregate_rows), which is what makes
    # a dataset like "count of packages by country" a sensible bar/pie
    # widget source rather than a raw row dump.
    group_by: Mapped[str | None] = mapped_column(String(200))
    aggregate_field: Mapped[str | None] = mapped_column(String(200))
    aggregate_fn: Mapped[str | None] = mapped_column(dataset_aggregate_fn)
    # Calculated columns — [{name, expression}], see DatasetComputedColumn.
    computed: Mapped[list] = mapped_column(JSONB, nullable=False, server_default="[]")
    # Other sources joined onto each row — [DatasetJoin], see app/schemas/reporting.py.
    joins: Mapped[list] = mapped_column(JSONB, nullable=False, server_default="[]")
    created_by: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("users.id"))