"""add organization invitations and durable named seats

Revision ID: b91e4c7a2d30
Revises: 82d6fe46d2a1
Create Date: 2026-08-15 21:10:00
"""

from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


revision: str = "b91e4c7a2d30"
down_revision: Union[str, Sequence[str], None] = "82d6fe46d2a1"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.create_table(
        "organization_invitations",
        sa.Column("id", sa.String(length=128), nullable=False),
        sa.Column("organization_id", sa.String(length=128), nullable=False),
        sa.Column("invited_email_digest", sa.LargeBinary(length=32), nullable=False),
        sa.Column("token_digest", sa.LargeBinary(length=32), nullable=False),
        sa.Column("role", sa.String(length=32), nullable=False),
        sa.Column("status", sa.String(length=32), nullable=False),
        sa.Column("expires_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("created_by_user_id", sa.String(length=128), nullable=False),
        sa.Column("accepted_by_user_id", sa.String(length=128), nullable=True),
        sa.Column("accepted_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("(CURRENT_TIMESTAMP)"),
            nullable=False,
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("(CURRENT_TIMESTAMP)"),
            nullable=False,
        ),
        sa.CheckConstraint(
            "expires_at > created_at",
            name="ck_organization_invitation_expiry",
        ),
        sa.CheckConstraint(
            "(status = 'accepted' AND accepted_by_user_id IS NOT NULL "
            "AND accepted_at IS NOT NULL) OR (status <> 'accepted' "
            "AND accepted_by_user_id IS NULL AND accepted_at IS NULL)",
            name="ck_organization_invitation_lifecycle",
        ),
        sa.ForeignKeyConstraint(
            ["accepted_by_user_id"],
            ["users.id"],
        ),
        sa.ForeignKeyConstraint(
            ["created_by_user_id"],
            ["users.id"],
        ),
        sa.ForeignKeyConstraint(
            ["organization_id"],
            ["organizations.id"],
            ondelete="CASCADE",
        ),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("token_digest"),
    )
    op.create_index(
        "ix_organization_invitations_pending_email",
        "organization_invitations",
        ["organization_id", "status", "invited_email_digest"],
        unique=False,
    )
    op.create_table(
        "organization_seats",
        sa.Column("id", sa.String(length=128), nullable=False),
        sa.Column("organization_id", sa.String(length=128), nullable=False),
        sa.Column("license_id", sa.String(length=128), nullable=False),
        sa.Column("assigned_user_id", sa.String(length=128), nullable=True),
        sa.Column("status", sa.String(length=32), nullable=False),
        sa.Column("assigned_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("released_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("last_reassigned_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("assignment_generation", sa.Integer(), nullable=False),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("(CURRENT_TIMESTAMP)"),
            nullable=False,
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("(CURRENT_TIMESTAMP)"),
            nullable=False,
        ),
        sa.CheckConstraint(
            "(status = 'assigned' AND assigned_user_id IS NOT NULL "
            "AND assigned_at IS NOT NULL) OR (status IN ('available', 'suspended') "
            "AND assigned_user_id IS NULL AND assigned_at IS NULL)",
            name="ck_organization_seats_lifecycle",
        ),
        sa.CheckConstraint(
            "assignment_generation >= 0",
            name="ck_organization_seats_assignment_generation",
        ),
        sa.ForeignKeyConstraint(
            ["assigned_user_id"],
            ["users.id"],
        ),
        sa.ForeignKeyConstraint(
            ["license_id"],
            ["licenses.id"],
            ondelete="CASCADE",
        ),
        sa.ForeignKeyConstraint(
            ["organization_id"],
            ["organizations.id"],
            ondelete="CASCADE",
        ),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(
        "ix_organization_seats_assignee",
        "organization_seats",
        ["organization_id", "assigned_user_id", "status"],
        unique=False,
    )
    op.create_index(
        "ix_organization_seats_license_status",
        "organization_seats",
        ["license_id", "status"],
        unique=False,
    )
    op.create_index(
        "uq_organization_seats_license_assigned_user",
        "organization_seats",
        ["license_id", "assigned_user_id"],
        unique=True,
        sqlite_where=sa.text("status = 'assigned'"),
        postgresql_where=sa.text("status = 'assigned'"),
    )


def downgrade() -> None:
    op.drop_index(
        "uq_organization_seats_license_assigned_user",
        table_name="organization_seats",
    )
    op.drop_index(
        "ix_organization_seats_license_status",
        table_name="organization_seats",
    )
    op.drop_index(
        "ix_organization_seats_assignee",
        table_name="organization_seats",
    )
    op.drop_table("organization_seats")
    op.drop_index(
        "ix_organization_invitations_pending_email",
        table_name="organization_invitations",
    )
    op.drop_table("organization_invitations")