"""Create spec-aligned plans and related tables.

This migration creates the plans table (replacing lifecycle_plans) with a
spec-aligned schema, plus the plan_projects, plan_arguments, and
plan_invariants child tables.

Revision ID: a5_004_spec_aligned_plans
Revises: a5_003_spec_aligned_actions
Create Date: 2026-02-13 00:00:01

"""

from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op

# revision identifiers, used by Alembic.
revision: str = "a5_004_spec_aligned_plans"
down_revision: str | Sequence[str] | None = "a5_003_spec_aligned_actions"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    """Create spec-aligned plans + plan_projects + plan_arguments + plan_invariants."""
    # --- Create plans table (ULID PK, spec-aligned columns) ---
    op.create_table(
        "v3_plans",
        # Identity - ULID primary key
        sa.Column("plan_id", sa.String(26), nullable=False),
        # Hierarchy - parent/root for subplan trees
        sa.Column(
            "parent_plan_id",
            sa.String(26),
            sa.ForeignKey(
                "v3_plans.plan_id",
                ondelete="SET NULL",
                name="fk_v3_plans_parent",
            ),
            nullable=True,
        ),
        sa.Column(
            "root_plan_id",
            sa.String(26),
            sa.ForeignKey(
                "v3_plans.plan_id",
                ondelete="SET NULL",
                name="fk_v3_plans_root",
            ),
            nullable=True,
        ),
        # Action linkage (by namespaced name, not ULID)
        sa.Column(
            "action_name",
            sa.String(255),
            sa.ForeignKey(
                "actions.namespaced_name",
                ondelete="RESTRICT",
                name="fk_v3_plans_action",
            ),
            nullable=False,
        ),
        # Plan naming
        sa.Column("namespaced_name", sa.String(255), nullable=False),
        sa.Column("namespace", sa.String(100), nullable=False),
        # Lifecycle phase and processing state (spec-aligned)
        sa.Column("phase", sa.String(20), nullable=False, server_default="strategize"),
        sa.Column(
            "processing_state", sa.String(20), nullable=False, server_default="queued"
        ),
        # Attempt counter
        sa.Column("attempt", sa.Integer(), nullable=False, server_default=sa.text("1")),
        # Rendered description and DoD (from action template + args)
        sa.Column("description", sa.Text(), nullable=False),
        sa.Column("definition_of_done", sa.Text(), nullable=True),
        # Actor references
        sa.Column("strategy_actor", sa.String(255), nullable=True),
        sa.Column("execution_actor", sa.String(255), nullable=True),
        sa.Column("review_actor", sa.String(255), nullable=True),
        sa.Column("apply_actor", sa.String(255), nullable=True),
        sa.Column("estimation_actor", sa.String(255), nullable=True),
        sa.Column("invariant_actor", sa.String(255), nullable=True),
        # Policy / profile
        sa.Column("automation_profile", sa.String(255), nullable=True),
        sa.Column(
            "automation_level", sa.String(30), nullable=False, server_default="manual"
        ),
        # Behavior
        sa.Column(
            "reusable", sa.Boolean(), nullable=False, server_default=sa.text("1")
        ),
        sa.Column(
            "read_only", sa.Boolean(), nullable=False, server_default=sa.text("0")
        ),
        # Inputs schema - JSON Schema dict (optional; copied from action)
        sa.Column("inputs_schema_json", sa.Text(), nullable=True),
        # Execution placeholders
        sa.Column("changeset_id", sa.String(26), nullable=True),
        sa.Column("sandbox_refs_json", sa.Text(), nullable=True),
        sa.Column("validation_summary_json", sa.Text(), nullable=True),
        sa.Column("decision_root_id", sa.String(26), nullable=True),
        # Error tracking
        sa.Column("error_message", sa.Text(), nullable=True),
        sa.Column("error_details_json", sa.Text(), nullable=True),
        # Cost/token tracking
        sa.Column("cost_estimate_usd", sa.Float(), nullable=True),
        sa.Column("cost_actual_usd", sa.Float(), nullable=True),
        sa.Column(
            "token_count_input",
            sa.Integer(),
            nullable=True,
            server_default=sa.text("0"),
        ),
        sa.Column(
            "token_count_output",
            sa.Integer(),
            nullable=True,
            server_default=sa.text("0"),
        ),
        # Metadata
        sa.Column("created_by", sa.String(255), nullable=True),
        sa.Column("tags_json", sa.Text(), nullable=False, server_default="[]"),
        # Timestamps (ISO-8601 strings)
        sa.Column("created_at", sa.String(30), nullable=False),
        sa.Column("updated_at", sa.String(30), nullable=False),
        sa.Column("completed_at", sa.String(30), nullable=True),
        sa.Column("strategize_started_at", sa.String(30), nullable=True),
        sa.Column("strategize_completed_at", sa.String(30), nullable=True),
        sa.Column("execute_started_at", sa.String(30), nullable=True),
        sa.Column("execute_completed_at", sa.String(30), nullable=True),
        sa.Column("apply_started_at", sa.String(30), nullable=True),
        sa.Column("applied_at", sa.String(30), nullable=True),
        # Constraints
        sa.PrimaryKeyConstraint("plan_id"),
        sa.CheckConstraint(
            "phase IN ('strategize', 'execute', 'apply', 'applied')",
            name="ck_v3_plans_phase",
        ),
        sa.CheckConstraint(
            "processing_state IN ("
            "'queued', 'processing', 'errored', 'complete', 'cancelled')",
            name="ck_v3_plans_state",
        ),
        sa.CheckConstraint(
            "automation_level IN ('manual', 'review_before_apply', 'full_automation')",
            name="ck_v3_plans_automation",
        ),
    )

    # Indices for common queries
    op.create_index("ix_v3_plans_phase", "v3_plans", ["phase"], unique=False)
    op.create_index("ix_v3_plans_state", "v3_plans", ["processing_state"], unique=False)
    op.create_index("ix_v3_plans_parent", "v3_plans", ["parent_plan_id"], unique=False)
    op.create_index("ix_v3_plans_root", "v3_plans", ["root_plan_id"], unique=False)
    op.create_index("ix_v3_plans_created", "v3_plans", ["created_at"], unique=False)
    op.create_index("ix_v3_plans_action", "v3_plans", ["action_name"], unique=False)
    op.create_index("ix_v3_plans_namespace", "v3_plans", ["namespace"], unique=False)
    op.create_index(
        "ix_v3_plans_phase_state",
        "v3_plans",
        ["phase", "processing_state"],
        unique=False,
    )

    # --- Create plan_projects table ---
    op.create_table(
        "plan_projects",
        sa.Column(
            "plan_id",
            sa.String(26),
            sa.ForeignKey(
                "v3_plans.plan_id",
                ondelete="CASCADE",
                name="fk_plan_projects_plan",
            ),
            nullable=False,
        ),
        sa.Column("project_name", sa.String(255), nullable=False),
        sa.Column("alias", sa.String(100), nullable=True),
        sa.Column(
            "read_only", sa.Boolean(), nullable=False, server_default=sa.text("0")
        ),
        sa.Column("created_at", sa.String(30), nullable=False),
        sa.PrimaryKeyConstraint("plan_id", "project_name"),
    )

    op.create_index(
        "ix_plan_projects_project",
        "plan_projects",
        ["project_name"],
        unique=False,
    )

    # --- Create plan_arguments table ---
    op.create_table(
        "plan_arguments",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column(
            "plan_id",
            sa.String(26),
            sa.ForeignKey(
                "v3_plans.plan_id",
                ondelete="CASCADE",
                name="fk_plan_arguments_plan",
            ),
            nullable=False,
        ),
        sa.Column("name", sa.String(100), nullable=False),
        sa.Column("value_json", sa.Text(), nullable=True),
        sa.Column("value_type", sa.String(20), nullable=False, server_default="string"),
        sa.Column("position", sa.Integer(), nullable=False),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("plan_id", "name", name="uq_plan_arguments_name"),
    )

    op.create_index(
        "ix_plan_arguments_plan",
        "plan_arguments",
        ["plan_id"],
        unique=False,
    )
    op.create_index(
        "ix_plan_arguments_position",
        "plan_arguments",
        ["plan_id", "position"],
        unique=False,
    )

    # --- Create plan_invariants table ---
    op.create_table(
        "plan_invariants",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column(
            "plan_id",
            sa.String(26),
            sa.ForeignKey(
                "v3_plans.plan_id",
                ondelete="CASCADE",
                name="fk_plan_invariants_plan",
            ),
            nullable=False,
        ),
        sa.Column("invariant_text", sa.Text(), nullable=False),
        sa.Column(
            "source_scope",
            sa.String(20),
            nullable=False,
            server_default="plan",
        ),
        sa.Column("source_name", sa.String(255), nullable=True),
        sa.Column("position", sa.Integer(), nullable=False),
        sa.Column("created_at", sa.String(30), nullable=False),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint(
            "plan_id", "invariant_text", name="uq_plan_invariants_text"
        ),
        sa.CheckConstraint(
            "source_scope IN ('global', 'project', 'action', 'plan')",
            name="ck_plan_invariants_scope",
        ),
    )

    op.create_index(
        "ix_plan_invariants_plan",
        "plan_invariants",
        ["plan_id"],
        unique=False,
    )
    op.create_index(
        "ix_plan_invariants_position",
        "plan_invariants",
        ["plan_id", "position"],
        unique=False,
    )


def downgrade() -> None:
    """Drop spec-aligned plan tables."""
    op.drop_index("ix_plan_invariants_position", table_name="plan_invariants")
    op.drop_index("ix_plan_invariants_plan", table_name="plan_invariants")
    op.drop_table("plan_invariants")

    op.drop_index("ix_plan_arguments_position", table_name="plan_arguments")
    op.drop_index("ix_plan_arguments_plan", table_name="plan_arguments")
    op.drop_table("plan_arguments")

    op.drop_index("ix_plan_projects_project", table_name="plan_projects")
    op.drop_table("plan_projects")

    op.drop_index("ix_v3_plans_phase_state", table_name="v3_plans")
    op.drop_index("ix_v3_plans_namespace", table_name="v3_plans")
    op.drop_index("ix_v3_plans_action", table_name="v3_plans")
    op.drop_index("ix_v3_plans_created", table_name="v3_plans")
    op.drop_index("ix_v3_plans_root", table_name="v3_plans")
    op.drop_index("ix_v3_plans_parent", table_name="v3_plans")
    op.drop_index("ix_v3_plans_state", table_name="v3_plans")
    op.drop_index("ix_v3_plans_phase", table_name="v3_plans")
    op.drop_table("v3_plans")