import uuid
from datetime import datetime

from sqlalchemy import Boolean, Computed, ForeignKey, ForeignKeyConstraint, Integer, String, Text, text
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

publish_status = PGEnum("draft", "published", name="publish_status", create_type=False)
workflow_instance_status = PGEnum(
    "in_progress", "completed", "cancelled", "escalated", name="workflow_instance_status", create_type=False
)


class Workflow(TenantScopedMixin, TimestampMixin, Base):
    __tablename__ = "workflows"

    workflow_id: Mapped[uuid.UUID] = mapped_column(
        PGUUID(as_uuid=True), primary_key=True, server_default=text("gen_random_uuid()")
    )
    version: Mapped[int] = mapped_column(Integer, primary_key=True, server_default="1")
    name: Mapped[str] = mapped_column(String(200), nullable=False)
    step_definitions: Mapped[list] = mapped_column(JSONB, nullable=False, server_default="[]")
    # Workflow-wide options — app/schemas/workflow.py::WorkflowSettings.
    settings: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default="{}")
    status: Mapped[str] = mapped_column(publish_status, nullable=False, server_default="draft")
    is_current: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default="true")
    created_by: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("users.id"))


class Form(TenantScopedMixin, TimestampMixin, Base):
    __tablename__ = "forms"

    form_id: Mapped[uuid.UUID] = mapped_column(
        PGUUID(as_uuid=True), primary_key=True, server_default=text("gen_random_uuid()")
    )
    version: Mapped[int] = mapped_column(Integer, primary_key=True, server_default="1")
    name: Mapped[str] = mapped_column(String(200), nullable=False)
    field_definitions: Mapped[list] = mapped_column(JSONB, nullable=False, server_default="[]")
    # Lifecycle hooks — OnSave/OnSubmit/OnCancel, each optionally wired to run
    # a Task, start a Workflow, navigate, or show a message. See
    # app/schemas/workflow.py::FormEventAction.
    events: Mapped[list] = mapped_column(JSONB, nullable=False, server_default="[]")
    # Optional: fetch one record from a Master or another Form's submissions,
    # filtered by a URL query parameter, and copy its fields into this Form's
    # own fields on load — wherever this Form renders (the Public Portal's
    # standalone runtime, or embedded/opened from a Page). Null (the default)
    # means no prefill configured, unchanged from every Form's behavior
    # before this existed. See app/schemas/workflow.py::FormPrefillConfig.
    prefill: Mapped[dict | None] = mapped_column(JSONB)
    settings: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default="{}")
    status: Mapped[str] = mapped_column(publish_status, nullable=False, server_default="draft")
    is_current: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default="true")
    created_by: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("users.id"))


class WorkflowStepForm(Base):
    __tablename__ = "workflow_step_forms"
    __table_args__ = (
        ForeignKeyConstraint(["workflow_id", "workflow_version"], ["workflows.workflow_id", "workflows.version"], ondelete="CASCADE"),
        ForeignKeyConstraint(["form_id", "form_version"], ["forms.form_id", "forms.version"]),
    )

    workflow_id: Mapped[uuid.UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True)
    workflow_version: Mapped[int] = mapped_column(Integer, primary_key=True)
    step_key: Mapped[str] = mapped_column(String(100), primary_key=True)
    form_id: Mapped[uuid.UUID] = mapped_column(PGUUID(as_uuid=True), nullable=False)
    form_version: Mapped[int] = mapped_column(Integer, nullable=False)


class WorkflowInstance(UUIDPKMixin, TenantScopedMixin, Base):
    __tablename__ = "workflow_instances"
    __table_args__ = (
        ForeignKeyConstraint(["workflow_id", "workflow_version"], ["workflows.workflow_id", "workflows.version"]),
    )

    vendor_id: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("vendors.id"))
    workflow_id: Mapped[uuid.UUID] = mapped_column(PGUUID(as_uuid=True), nullable=False)
    workflow_version: Mapped[int] = mapped_column(Integer, nullable=False)
    current_step: Mapped[str | None] = mapped_column(String(100))
    status: Mapped[str] = mapped_column(workflow_instance_status, nullable=False, server_default="in_progress")
    sla_due_at: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True))
    # sla_paused_at set = clock currently paused; sla_paused_seconds =
    # cumulative time already spent paused across every past pause/resume
    # cycle — see WorkflowInstanceRead.effective_sla_due_at.
    sla_paused_at: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True))
    sla_paused_seconds: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0")
    started_by: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("users.id"))
    started_at: Mapped[datetime] = mapped_column(TIMESTAMP(timezone=True), server_default=text("now()"))
    completed_at: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True))
    # Set only when this instance was spawned as a child of another (e.g. a
    # Transfer/Information-Request branch) — see routers/workflow_instances.py's
    # completion-cascade logic. Null (the default) means a normal top-level
    # instance, unchanged from before this existed.
    parent_instance_id: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("workflow_instances.id"))
    # Accumulates as the instance moves through steps — each step's declared
    # input_params/output_params are validated against this (app/crud/
    # params.py), the same runtime-enforced param contract Task nodes use.
    context: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default="{}")
    # The engine's state (app/crud/workflow_engine.py): one token per active
    # branch — where it is and what it waits for — and each join's arrivals.
    # current_step mirrors the first token's step, for readers that want one.
    tokens: Mapped[list] = mapped_column(JSONB, nullable=False, server_default="[]")
    join_state: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default="{}")
    # The earliest timer / step SLA / reminder due — what the scheduler looks for.
    next_due_at: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True))
    suspended_at: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True))
    cancel_reason: Mapped[str | None] = mapped_column(Text)
    # The parent's token waiting on this instance (a Sub-workflow step).
    parent_token_id: Mapped[str | None] = mapped_column(String(40))
    has_incident: Mapped[bool] = mapped_column(Boolean, Computed("""tokens @> '[{"state": "failed"}]'::jsonb""", persisted=True))
    has_escalation: Mapped[bool] = mapped_column(Boolean, Computed("""tokens @> '[{"escalated": true}]'::jsonb""", persisted=True))


class WorkflowDelegation(UUIDPKMixin, TenantScopedMixin, Base):
    """While `user_id` is away (starts_at–ends_at), `delegate_id` sees and
    acts on the workflow tasks waiting on them — for one workflow, or all."""

    __tablename__ = "workflow_delegations"

    user_id: Mapped[uuid.UUID] = mapped_column(PGUUID(as_uuid=True), ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    delegate_id: Mapped[uuid.UUID] = mapped_column(PGUUID(as_uuid=True), ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    starts_at: Mapped[datetime] = mapped_column(TIMESTAMP(timezone=True), nullable=False)
    ends_at: Mapped[datetime] = mapped_column(TIMESTAMP(timezone=True), nullable=False)
    workflow_id: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True))
    reason: Mapped[str | None] = mapped_column(String(300))
    created_by: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"))
    created_at: Mapped[datetime] = mapped_column(TIMESTAMP(timezone=True), server_default=text("now()"))


class StepHistory(UUIDPKMixin, TenantScopedMixin, Base):
    __tablename__ = "step_history"

    instance_id: Mapped[uuid.UUID] = mapped_column(
        PGUUID(as_uuid=True), ForeignKey("workflow_instances.id", ondelete="CASCADE"), nullable=False
    )
    step: Mapped[str] = mapped_column(String(100), nullable=False)
    actor_id: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("users.id"))
    action: Mapped[str] = mapped_column(String(100), nullable=False)
    comment: Mapped[str | None] = mapped_column(String)
    occurred_at: Mapped[datetime] = mapped_column(TIMESTAMP(timezone=True), server_default=text("now()"))


class FormSubmission(UUIDPKMixin, TenantScopedMixin, Base):
    __tablename__ = "form_submissions"
    __table_args__ = (
        ForeignKeyConstraint(["form_id", "form_version"], ["forms.form_id", "forms.version"]),
    )

    vendor_id: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("vendors.id"))
    # Part of the key because the table is partitioned by it (db/schema.sql).
    form_id: Mapped[uuid.UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True)
    form_version: Mapped[int] = mapped_column(Integer, nullable=False)
    workflow_instance_id: Mapped[uuid.UUID | None] = mapped_column(
        PGUUID(as_uuid=True), ForeignKey("workflow_instances.id")
    )
    submitted_by: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("users.id"))
    updated_by: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("users.id"))
    field_values: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default="{}")
    submitted_at: Mapped[datetime] = mapped_column(TIMESTAMP(timezone=True), server_default=text("now()"))
    created_at: Mapped[datetime] = mapped_column(TIMESTAMP(timezone=True), server_default=text("now()"))
    updated_at: Mapped[datetime] = mapped_column(TIMESTAMP(timezone=True), server_default=text("now()"))
    # Soft-delete flag — see db/schema.sql's form_submissions.is_deleted comment.
    is_deleted: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default="false")
    # "draft" | "submitted" — see db/schema.sql.
    status: Mapped[str] = mapped_column(String(10), nullable=False, server_default="submitted")
    # A public form's "continue later" link to this draft (app/routers/
    # public_forms.py): a SHA-256 of its token, and when it stops working.
    resume_token_hash: Mapped[str | None] = mapped_column(String(64))
    resume_expires_at: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True))
    # Officers' review (app/crud/application_review.py) — None = submitted.
    review_status: Mapped[str | None] = mapped_column(String(20))
    review_note: Mapped[str | None] = mapped_column(Text)
    reviewed_at: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True))
    correction_fields: Mapped[list | None] = mapped_column(JSONB)
    correction_until: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True))


class FormSubmissionReview(UUIDPKMixin, TenantScopedMixin, Base):
    """One review status change of a submission. No FK to form_submissions on
    purpose — the history outlives a deleted submission."""

    __tablename__ = "form_submission_reviews"

    form_id: Mapped[uuid.UUID] = mapped_column(PGUUID(as_uuid=True), nullable=False)
    submission_id: Mapped[uuid.UUID] = mapped_column(PGUUID(as_uuid=True), nullable=False)
    from_status: Mapped[str | None] = mapped_column(String(20))
    to_status: Mapped[str] = mapped_column(String(20), nullable=False)
    note: Mapped[str | None] = mapped_column(Text)
    actor_id: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"))
    occurred_at: Mapped[datetime] = mapped_column(TIMESTAMP(timezone=True), server_default=text("now()"))