import uuid
from datetime import datetime

from sqlalchemy import ForeignKey, String, 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

scheduled_job_type = PGEnum("run_task", "sla_overdue_scan", name="scheduled_job_type", create_type=False)
scheduled_job_frequency = PGEnum("hourly", "daily", "weekly", name="scheduled_job_frequency", create_type=False)
scheduled_job_status = PGEnum("active", "paused", name="scheduled_job_status", create_type=False)


class ScheduledJob(UUIDPKMixin, TenantScopedMixin, TimestampMixin, Base):
    """Fires a Task (job_type "run_task") or scans in_progress
    WorkflowInstances for a newly-overdue SLA (job_type "sla_overdue_scan")
    on a recurring cadence — see app/core/scheduler.py, the in-process tick
    loop that actually executes these; this table just holds what to run and
    when. Nothing here reaches into a specific Workflow's steps: an
    sla_overdue_scan job is tenant-wide, covering every in_progress instance
    at once, matching how the SLA fields themselves are already
    instance-level, not step-level (see WorkflowInstance.sla_due_at)."""

    __tablename__ = "scheduled_jobs"

    name: Mapped[str] = mapped_column(String(200), nullable=False)
    job_type: Mapped[str] = mapped_column(scheduled_job_type, nullable=False)
    task_id: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("tasks.id", ondelete="SET NULL"))
    context: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default="{}")
    frequency: Mapped[str] = mapped_column(scheduled_job_frequency, nullable=False, server_default="daily")
    status: Mapped[str] = mapped_column(scheduled_job_status, nullable=False, server_default="active")
    last_run_at: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True))
    last_run_result: Mapped[str | None] = mapped_column(String(500))
    next_run_at: Mapped[datetime] = mapped_column(TIMESTAMP(timezone=True), nullable=False, server_default=text("now()"))
    created_by: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("users.id"))