"""strategy and steps

Revision ID: d45c69d7619a
Revises: cf80ac9c98fd
Create Date: 2025-09-14 04:10:56.974193
"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa

# ─────────────────────────────────────────────────────────────
# Revision identifiers
revision: str = "d45c69d7619a"
down_revision: Union[str, Sequence[str], None] = "cf80ac9c98fd"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None
# ─────────────────────────────────────────────────────────────


def upgrade() -> None:
    """Create minimal strategy tables (portable enums, no changes to existing tables)."""
    # Create "strategies" table
    op.create_table(
        "strategies",
        sa.Column("id", sa.Integer(), primary_key=True, nullable=False),
        sa.Column("user_id", sa.Integer(), sa.ForeignKey("users.id", ondelete="CASCADE"), nullable=False),

        # Full, human-readable strategy text (HTML/Markdown/plain)
        sa.Column("strategy", sa.Text(), nullable=False),

        # Map of day → content (e.g., {"1": {"html": "..."} , "2": {...}})
        sa.Column("steps", sa.JSON(), nullable=False),

        # Portable ENUM: active|completed (stored as VARCHAR + CHECK)
        sa.Column(
            "status",
            sa.Enum("active", "completed", name="strategystatus", native_enum=False, create_constraint=True),
            nullable=False,
        ),

        # Current logical day cursor (1-based)
        sa.Column("current_day", sa.Integer(), nullable=False, server_default="1"),
    )
    op.create_index(
        "ix_strategies_user_status",
        "strategies",
        ["user_id", "status"],
        unique=False,
    )

    # Create per-day state table
    op.create_table(
        "strategy_day_state",
        sa.Column("id", sa.Integer(), primary_key=True, nullable=False),
        sa.Column("strategy_id", sa.Integer(), sa.ForeignKey("strategies.id", ondelete="CASCADE"), nullable=False),

        # Day number from Strategy.steps
        sa.Column("day_number", sa.Integer(), nullable=False),

        # Day status machine
        sa.Column(
            "status",
            sa.Enum(
                "planned", "issued", "done", "snoozed", "failed", "hint",
                name="daystatus",
                native_enum=False,
                create_constraint=True,
            ),
            nullable=False,
        ),

        # Optional failure reason
        sa.Column(
            "fail_reason",
            sa.Enum(
                "fear", "laziness", "no_time", "other",
                name="failreason",
                native_enum=False,
                create_constraint=True,
            ),
            nullable=True,
        ),

        # Snooze wake-up timestamp (for reminders)
        sa.Column("snooze_until", sa.DateTime(timezone=True), nullable=True),
    )
    op.create_index(
        "ix_day_state_strategy_day",
        "strategy_day_state",
        ["strategy_id", "day_number"],
        unique=False,
    )
    # Ensure one state row per (strategy, day)
    op.create_unique_constraint(
        "uq_day_state_strategy_day",
        "strategy_day_state",
        ["strategy_id", "day_number"],
    )


def downgrade() -> None:
    """Drop strategy tables (reverse order)."""
    op.drop_constraint("uq_day_state_strategy_day", "strategy_day_state", type_="unique")
    op.drop_index("ix_day_state_strategy_day", table_name="strategy_day_state")
    op.drop_table("strategy_day_state")

    op.drop_index("ix_strategies_user_status", table_name="strategies")
    op.drop_table("strategies")