"""replace events.end_dt with duration_minutes (Fable #160)

Revision ID: 0043
Revises: 0042
Create Date: 2026-04-29

Structural fix for the "end before start" bug class observed on prod
2026-04-29: an event landed with end_dt 32 days before start_dt due
to a tool-call mishap, then disappeared from upcoming-list filters.
Storing duration instead of end_dt makes the invalid state
inexpressible at the schema level (duration_minutes >= 0).

Backfill rules:
  - end_dt valid (end_dt > start_dt) → duration_minutes = total minutes
  - end_dt == start_dt → duration_minutes = 0 (zero-duration point)
  - end_dt NULL OR end_dt < start_dt → duration_minutes = NULL (corrupt
    or open-ended; treated as a point event from here on)

Existing API consumers continue to receive `end_dt` in responses — the
field is now derived from `start_dt + duration_minutes` in
``Event.to_dict()`` rather than stored.
"""
from alembic import op
import sqlalchemy as sa


revision = "0043"
down_revision = "0042"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.add_column(
        "events",
        sa.Column("duration_minutes", sa.Integer(), nullable=True),
    )
    op.create_check_constraint(
        "events_duration_minutes_non_negative",
        "events",
        "duration_minutes IS NULL OR duration_minutes >= 0",
    )
    # Backfill: convert valid end_dt into a minute count; leave NULL for
    # corrupt or absent end_dt. Bad rows (end_dt <= start_dt) collapse
    # cleanly to point events instead of forcing a recovery guess.
    op.execute(
        """
        UPDATE events
        SET duration_minutes = CAST(
            EXTRACT(EPOCH FROM (end_dt - start_dt)) / 60 AS INTEGER
        )
        WHERE end_dt IS NOT NULL AND end_dt >= start_dt
        """
    )
    op.drop_column("events", "end_dt")


def downgrade() -> None:
    op.add_column(
        "events",
        sa.Column("end_dt", sa.DateTime(timezone=True), nullable=True),
    )
    # Restore end_dt = start_dt + duration_minutes minutes for rows that
    # had a duration. NULL duration → NULL end_dt (point event).
    op.execute(
        """
        UPDATE events
        SET end_dt = start_dt + (duration_minutes || ' minutes')::interval
        WHERE duration_minutes IS NOT NULL
        """
    )
    op.drop_constraint("events_duration_minutes_non_negative", "events")
    op.drop_column("events", "duration_minutes")
