import uuid
from datetime import datetime

from sqlalchemy import BigInteger, Boolean, ForeignKey, Integer, String, Text, UniqueConstraint
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
from app.models.workflow import publish_status


class SequenceCounter(UUIDPKMixin, TenantScopedMixin, TimestampMixin, Base):
    """One durable, atomically-incremented counter per (tenant, key) — the
    backing store for a Task's generate_sequence node. `key` already
    includes any reset-period suffix (e.g. "RTI-APPLICATION:2026") the node
    computed, so this table itself doesn't need to know about reset
    periods — it just counts up per distinct key, forever."""

    __tablename__ = "sequence_counters"
    __table_args__ = (UniqueConstraint("tenant_id", "key", name="uq_sequence_counters_tenant_key"),)

    key: Mapped[str] = mapped_column(String(200), nullable=False)
    current_value: Mapped[int] = mapped_column(BigInteger, nullable=False, server_default="0")


class Task(UUIDPKMixin, TenantScopedMixin, TimestampMixin, Base):
    """Task Designer Studio: a node-graph automation pipeline — the same
    canvas/transition/condition model as Workflow's step_definitions, but
    executed autonomously in one synchronous pass (no human-in-the-loop
    steps) via POST /tasks/{id}/run. Reusable and triggerable from a Form's
    OnSave/OnSubmit/OnCancel events (Form.events). nodes' shape is
    per-node-type — see app/schemas/automation.py::TaskNodeDef and the
    execution engine in app/crud/tasks.py."""

    __tablename__ = "tasks"

    name: Mapped[str] = mapped_column(String(200), nullable=False)
    nodes: Mapped[list] = mapped_column(JSONB, nullable=False, server_default="[]")
    # Task Designer › Variables — set when a run starts and readable by every
    # node (app/schemas/automation.py::TaskVariableDef, app/crud/tasks.py).
    variables: Mapped[list] = mapped_column(JSONB, nullable=False, server_default="[]")
    status: Mapped[str] = mapped_column(publish_status, nullable=False, server_default="draft")
    created_by: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("users.id"))


class TaskRun(Base):
    """One run of a Task (app/crud/task_history.py). task_id and started_by
    aren't foreign keys: this is written on its own connection while the run's
    transaction is still open, and must not wait on what that has locked."""

    __tablename__ = "task_runs"

    id: Mapped[uuid.UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True)
    tenant_id: Mapped[uuid.UUID] = mapped_column(PGUUID(as_uuid=True), ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False)
    task_id: Mapped[uuid.UUID] = mapped_column(PGUUID(as_uuid=True), nullable=False)
    task_name: Mapped[str] = mapped_column(Text, nullable=False)
    task_status: Mapped[str] = mapped_column(Text, nullable=False)
    task_updated_at: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True))
    trigger: Mapped[str] = mapped_column(Text, nullable=False)
    trigger_ref: Mapped[str | None] = mapped_column(Text)
    trigger_name: Mapped[str | None] = mapped_column(Text)
    started_by: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True))
    status: Mapped[str] = mapped_column(Text, nullable=False)
    detail: Mapped[str | None] = mapped_column(Text)
    input: Mapped[dict | None] = mapped_column(JSONB(none_as_null=True))
    output: Mapped[dict | None] = mapped_column(JSONB(none_as_null=True))
    step_count: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0")
    failed_steps: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0")
    lost_steps: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0")
    started_at: Mapped[datetime] = mapped_column(TIMESTAMP(timezone=True), nullable=False)
    finished_at: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True))
    updated_at: Mapped[datetime] = mapped_column(TIMESTAMP(timezone=True), nullable=False)


class TaskRunStep(Base):
    """One step of a TaskRun, in the order it ran."""

    __tablename__ = "task_run_steps"

    run_id: Mapped[uuid.UUID] = mapped_column(PGUUID(as_uuid=True), ForeignKey("task_runs.id", ondelete="CASCADE"), primary_key=True)
    seq: Mapped[int] = mapped_column(Integer, primary_key=True)
    tenant_id: Mapped[uuid.UUID] = mapped_column(PGUUID(as_uuid=True), ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False)
    node_key: Mapped[str] = mapped_column(Text, nullable=False)
    node_label: Mapped[str | None] = mapped_column(Text)
    node_type: Mapped[str] = mapped_column(Text, nullable=False)
    iteration: Mapped[str | None] = mapped_column(Text)
    ok: Mapped[bool] = mapped_column(Boolean, nullable=False)
    detail: Mapped[str] = mapped_column(Text, nullable=False)
    inputs: Mapped[dict | None] = mapped_column(JSONB(none_as_null=True))
    outputs: Mapped[object | None] = mapped_column(JSONB(none_as_null=True))
    saved: Mapped[dict | None] = mapped_column(JSONB(none_as_null=True))
    truncated: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default="false")
    started_at: Mapped[datetime] = mapped_column(TIMESTAMP(timezone=True), nullable=False)
    duration_ms: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0")