"""MessageTemplate, ReminderSchedule and MessageJob (PRD §4.2, §7.3).

MessageJob is the queue itself (design D3) — there is no broker. The sender claims due
rows with FOR UPDATE SKIP LOCKED, so `(status, scheduled_for)` is indexed for that query.
"""

import uuid
from datetime import datetime, time

from sqlalchemy import (
    ARRAY,
    Boolean,
    ForeignKey,
    Index,
    Integer,
    String,
    Text,
    Time,
    UniqueConstraint,
    Uuid,
)
from sqlalchemy.orm import Mapped, mapped_column

from app.models.base import Base, TimestampTZ, created_at_col, pg_enum, updated_at_col, uuid_pk
from app.models.enums import (
    Channel,
    Locale,
    MessageJobStatus,
    ReminderAudience,
    TemplatePurpose,
)


class MessageTemplate(Base):
    __tablename__ = "message_template"
    __table_args__ = (
        UniqueConstraint("channel", "purpose", "locale", name="uq_template_channel_purpose_locale"),
    )

    id: Mapped[uuid.UUID] = uuid_pk()
    channel: Mapped[Channel] = mapped_column(pg_enum(Channel, "channel"), nullable=False)
    purpose: Mapped[TemplatePurpose] = mapped_column(
        pg_enum(TemplatePurpose, "template_purpose"), nullable=False
    )
    locale: Mapped[Locale] = mapped_column(pg_enum(Locale, "locale"), nullable=False)

    subject: Mapped[str | None] = mapped_column(Text)
    body: Mapped[str] = mapped_column(Text, nullable=False)
    body_text: Mapped[str | None] = mapped_column(Text)
    provider_template_id: Mapped[str | None] = mapped_column(String(120))
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)

    created_at: Mapped[datetime] = created_at_col()
    updated_at: Mapped[datetime] = updated_at_col()


class ReminderSchedule(Base):
    """T-15 / T-7 / T-2 per event. offset_days is configurable, not hardcoded."""

    __tablename__ = "reminder_schedule"
    __table_args__ = (UniqueConstraint("event_id", "offset_days", name="uq_schedule_event_offset"),)

    id: Mapped[uuid.UUID] = uuid_pk()
    event_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("event.id", ondelete="CASCADE"), nullable=False, index=True
    )
    offset_days: Mapped[int] = mapped_column(Integer, nullable=False)
    send_at_local_time: Mapped[time] = mapped_column(Time, nullable=False)
    channels: Mapped[list[str]] = mapped_column(
        ARRAY(Text), default=lambda: ["email"], server_default="{email}", nullable=False
    )
    audience: Mapped[ReminderAudience] = mapped_column(
        pg_enum(ReminderAudience, "reminder_audience"),
        default=ReminderAudience.ACCEPTED,
        nullable=False,
    )
    is_enabled: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)

    created_at: Mapped[datetime] = created_at_col()
    updated_at: Mapped[datetime] = updated_at_col()


class MessageJob(Base):
    """One row per (invitation x schedule x channel). This table is the queue.

    idempotency_key is the single cheapest piece of insurance in the system: without it a
    server restart or a duplicated planner run would message every guest twice (PRD §7.3).
    """

    __tablename__ = "message_job"
    __table_args__ = (
        UniqueConstraint("idempotency_key", name="uq_message_job_idempotency"),
        # Supports the sender's claim query: WHERE status='queued' AND scheduled_for <= now()
        Index("ix_message_job_claim", "status", "scheduled_for"),
        Index("ix_message_job_provider", "provider_message_id"),
        # Every batch read is "this batch's rows": the dispatch pass and the progress poll.
        Index("ix_message_job_batch", "batch_id"),
    )

    id: Mapped[uuid.UUID] = uuid_pk()
    invitation_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("invitation.id", ondelete="CASCADE"), nullable=False, index=True
    )
    template_id: Mapped[uuid.UUID | None] = mapped_column(
        ForeignKey("message_template.id", ondelete="SET NULL")
    )
    schedule_id: Mapped[uuid.UUID | None] = mapped_column(
        ForeignKey("reminder_schedule.id", ondelete="SET NULL")
    )

    channel: Mapped[Channel] = mapped_column(pg_enum(Channel, "channel"), nullable=False)
    scheduled_for: Mapped[datetime] = mapped_column(TimestampTZ, nullable=False)
    status: Mapped[MessageJobStatus] = mapped_column(
        pg_enum(MessageJobStatus, "message_job_status"),
        default=MessageJobStatus.QUEUED,
        nullable=False,
    )
    attempts: Mapped[int] = mapped_column(Integer, default=0, nullable=False)

    provider_message_id: Mapped[str | None] = mapped_column(String(200))
    error_code: Mapped[str | None] = mapped_column(String(80))
    error_message: Mapped[str | None] = mapped_column(Text)
    # Why a job was skipped, e.g. "no email address" — surfaced in the admin message log.
    skip_reason: Mapped[str | None] = mapped_column(String(200))

    #: A manual send's own content, hand-edited by an admin for one guest
    #: (add-guest-invitation-send D3). Null means "render from `template_id` at send time",
    #: which is every scheduled and bulk job — those two paths are untouched.
    #:
    #: This is the one place a job holds guest-visible personal text: the guest's name and
    #: their token URL are inside `body_text`. It must not be rendered into the message log,
    #: which is a list screen, or the log becomes a PII surface.
    subject: Mapped[str | None] = mapped_column(Text)
    body_text: Mapped[str | None] = mapped_column(Text)

    #: Set only by an admin who confirmed a send inside quiet hours (D5). Automated waves
    #: never set it, so FR-6.9 holds for every planned reminder exactly as before.
    override_quiet_hours: Mapped[bool] = mapped_column(
        Boolean, default=False, server_default="false", nullable=False
    )

    #: Who pressed Send. Recorded at insert because it cannot be reconstructed afterwards —
    #: the audit log knows an admin sent *something*, not which job it became.
    sent_by_admin_id: Mapped[uuid.UUID | None] = mapped_column(
        ForeignKey("admin_user.id", ondelete="SET NULL")
    )

    #: The one admin action that produced this job, when it was a batched send from the guest
    #: list (add-bulk-invitation-send D5). It groups the jobs for the progress poll and for
    #: the single audit row, and it rides inside `idempotency_key`, which is what makes a
    #: double-clicked Send insert nothing twice while a deliberate second batch still sends.
    #:
    #: Nullable, and no foreign key: there is no batch table. A batch is a set of jobs sharing
    #: this value and nothing else, so every job that predates this column — and every
    #: scheduled job ever — reads correctly as "not part of a batch".
    batch_id: Mapped[uuid.UUID | None] = mapped_column(Uuid)

    # invitation_id:schedule_id:channel — see design D5.
    idempotency_key: Mapped[str] = mapped_column(String(200), nullable=False)

    sent_at: Mapped[datetime | None] = mapped_column(TimestampTZ)
    delivered_at: Mapped[datetime | None] = mapped_column(TimestampTZ)

    created_at: Mapped[datetime] = created_at_col()
    updated_at: Mapped[datetime] = updated_at_col()