"""Registrants table — Phase 0 vertical slice.

Revision ID: 0003_registrants
Revises: 0002_framework_tables
Create Date: 2026-09-30

Milestone 0.5. ARCHITECTURE.md §6.1 / PHASE0_PLAN.md §4.

- `ssn_encrypted` holds AES-256-GCM ciphertext from security/column_crypto.py
  (NIST SC-28; source RFP `C.5.7.50`). Never plaintext.
- `ssn_last4` is the only SSN-derived value stored in the clear — for search
  and masked display (``XXX-XX-1234``).
- No `minor_flag` column: derived from `dob` at read time.
- `user_id` links the youth's own portal account (Phase 1 account-link flow).

Per repo rule (CONTRIBUTING.md): this file is immutable once merged.
"""

from __future__ import annotations

from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op

revision: str = "0003_registrants"
down_revision: str | Sequence[str] | None = "0002_framework_tables"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    op.create_table(
        "registrants",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column(
            "user_id",
            sa.Integer(),
            sa.ForeignKey("users.id", ondelete="SET NULL"),
            nullable=True,
        ),
        sa.Column("first_name", sa.String(length=100), nullable=False),
        sa.Column("middle_name", sa.String(length=100), nullable=True),
        sa.Column("last_name", sa.String(length=100), nullable=False),
        sa.Column("dob", sa.Date(), nullable=False),
        sa.Column("ssn_encrypted", sa.String(length=255), nullable=True),
        sa.Column("ssn_last4", sa.String(length=4), nullable=True),
        sa.Column("email", sa.String(length=255), nullable=True),
        sa.Column("phone", sa.String(length=32), nullable=True),
        sa.Column("address_line1", sa.String(length=255), nullable=True),
        sa.Column("address_line2", sa.String(length=255), nullable=True),
        sa.Column("city", sa.String(length=100), nullable=True),
        sa.Column("state", sa.String(length=2), nullable=True),
        sa.Column("zip", sa.String(length=10), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("version", sa.Integer(), nullable=False, server_default=sa.text("1")),
    )
    op.create_index("ix_registrants_user_id", "registrants", ["user_id"])
    op.create_index("ix_registrants_last_name", "registrants", ["last_name"])
    op.create_index("ix_registrants_dob", "registrants", ["dob"])
    op.create_index("ix_registrants_ssn_last4", "registrants", ["ssn_last4"])


def downgrade() -> None:
    op.drop_index("ix_registrants_ssn_last4", table_name="registrants")
    op.drop_index("ix_registrants_dob", table_name="registrants")
    op.drop_index("ix_registrants_last_name", table_name="registrants")
    op.drop_index("ix_registrants_user_id", table_name="registrants")
    op.drop_table("registrants")