"""add offline trial approval records

Revision ID: d7f3b5a8c921
Revises: c4f7a091d8e2
Create Date: 2026-08-16 00:45:00
"""

from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


revision: str = "d7f3b5a8c921"
down_revision: Union[str, Sequence[str], None] = "c4f7a091d8e2"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.create_table(
        "offline_requests",
        sa.Column("id", sa.String(length=128), nullable=False),
        sa.Column("client_request_id", sa.String(length=128), nullable=False),
        sa.Column("request_type", sa.String(length=32), nullable=False),
        sa.Column("status", sa.String(length=32), nullable=False),
        sa.Column("request_digest", sa.LargeBinary(length=32), nullable=False),
        sa.Column("request_document_json", sa.JSON(), nullable=False),
        sa.Column("retrieval_digest", sa.LargeBinary(length=32), nullable=False),
        sa.Column("installation_id", sa.String(length=128), nullable=False),
        sa.Column("device_key_thumbprint", sa.String(length=128), nullable=False),
        sa.Column("requested_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("expires_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("decision_generation", sa.Integer(), nullable=False),
        sa.Column("decided_by_user_id", sa.String(length=128), nullable=True),
        sa.Column("decision_reason", sa.String(length=128), nullable=True),
        sa.Column("decision_note", sa.String(length=512), nullable=True),
        sa.Column("decided_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("decision_idempotency_key", sa.String(length=128), nullable=True),
        sa.Column("decision_request_digest", sa.LargeBinary(length=32), nullable=True),
        sa.Column("decision_response_json", sa.JSON(), 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(
            "decision_generation >= 0",
            name="ck_offline_requests_generation",
        ),
        sa.CheckConstraint(
            "expires_at > requested_at",
            name="ck_offline_requests_expiry",
        ),
        sa.CheckConstraint(
            "(status = 'pending' AND decided_by_user_id IS NULL AND decided_at IS NULL) "
            "OR (status IN ('approved', 'rejected') AND decided_by_user_id IS NOT NULL "
            "AND decided_at IS NOT NULL)",
            name="ck_offline_requests_decision",
        ),
        sa.ForeignKeyConstraint(["decided_by_user_id"], ["users.id"]),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("client_request_id"),
    )
    op.create_index(
        "ix_offline_requests_status_requested",
        "offline_requests",
        ["status", "requested_at"],
        unique=False,
    )
    op.create_table(
        "offline_certificates",
        sa.Column("id", sa.String(length=128), nullable=False),
        sa.Column("offline_request_id", sa.String(length=128), nullable=False),
        sa.Column("license_id", sa.String(length=128), nullable=False),
        sa.Column("activation_id", sa.String(length=128), nullable=False),
        sa.Column("lease_id", sa.String(length=128), nullable=False),
        sa.Column("file_digest", sa.LargeBinary(length=32), nullable=False),
        sa.Column("issued_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("expires_at", sa.DateTime(timezone=True), 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(
            "expires_at > issued_at",
            name="ck_offline_certificates_validity",
        ),
        sa.ForeignKeyConstraint(["activation_id"], ["activations.id"]),
        sa.ForeignKeyConstraint(["lease_id"], ["leases.id"]),
        sa.ForeignKeyConstraint(["license_id"], ["licenses.id"]),
        sa.ForeignKeyConstraint(
            ["offline_request_id"],
            ["offline_requests.id"],
            ondelete="CASCADE",
        ),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("lease_id"),
        sa.UniqueConstraint("offline_request_id"),
    )


def downgrade() -> None:
    op.drop_table("offline_certificates")
    op.drop_index(
        "ix_offline_requests_status_requested",
        table_name="offline_requests",
    )
    op.drop_table("offline_requests")