"""v0.1.7 core baseline

Revision ID: 4f2a9c8e7b6d
Revises: None
Create Date: 2026-07-11 00:00:00.000000
"""
from __future__ import annotations

from datetime import datetime, timezone
from uuid import uuid4
from alembic import op
import sqlalchemy as sa


revision = '4f2a9c8e7b6d'
down_revision = None
branch_labels = None
depends_on = None


def _now() -> datetime:
    return datetime.now(timezone.utc)


def _seed_core_defaults() -> None:
    bind = op.get_bind()
    now = _now()
    if not bind.execute(sa.text("SELECT 1 FROM core_system_settings WHERE id = 'global'")).first():
        settings_table = sa.table(
            "core_system_settings",
            sa.column("id", sa.String),
            sa.column("default_locale", sa.String),
            sa.column("allow_tenant_custom_groups", sa.Boolean),
            sa.column("allow_tenant_custom_roles", sa.Boolean),
            sa.column("allow_tenant_api_keys", sa.Boolean),
            sa.column("settings", sa.JSON),
            sa.column("created_at", sa.DateTime(timezone=True)),
            sa.column("updated_at", sa.DateTime(timezone=True)),
        )
        bind.execute(
            settings_table.insert().values({
                "id": "global",
                "default_locale": "en",
                "allow_tenant_custom_groups": True,
                "allow_tenant_custom_roles": True,
                "allow_tenant_api_keys": True,
                "settings": {},
                "created_at": now,
                "updated_at": now,
            }),
        )
    system_roles = {
        "system_owner": {
            "name": "System owner",
            "description": "Protected full instance-wide administration.",
            "permissions": ["system:*"],
            "is_builtin": True,
        },
        "system_admin": {
            "name": "System administrator",
            "description": "Manage the instance without granting the protected System owner role.",
            "permissions": [
                "system:tenants:read", "system:tenants:create", "system:tenants:update", "system:tenants:suspend",
                "system:accounts:read", "system:accounts:create", "system:accounts:update", "system:accounts:suspend",
                "system:roles:read", "system:roles:write", "system:roles:assign", "system:access:read", "system:access:assign",
                "system:audit:read", "system:settings:read", "system:settings:write", "system:governance:read", "system:governance:write",
            ],
            "is_builtin": False,
        },
        "system_auditor": {
            "name": "System auditor",
            "description": "Read-only access to system administration and audit.",
            "permissions": [
                "system:tenants:read", "system:accounts:read", "system:roles:read", "system:access:read",
                "system:audit:read", "system:settings:read", "system:governance:read",
            ],
            "is_builtin": False,
        },
    }
    roles_table = sa.table(
        "access_roles",
        sa.column("id", sa.String),
        sa.column("tenant_id", sa.String),
        sa.column("slug", sa.String),
        sa.column("name", sa.String),
        sa.column("description", sa.Text),
        sa.column("permissions", sa.JSON),
        sa.column("is_builtin", sa.Boolean),
        sa.column("is_assignable", sa.Boolean),
        sa.column("system_template_id", sa.String),
        sa.column("system_required", sa.Boolean),
        sa.column("created_at", sa.DateTime(timezone=True)),
        sa.column("updated_at", sa.DateTime(timezone=True)),
    )
    for slug, role in system_roles.items():
        if bind.execute(
            sa.text("SELECT 1 FROM access_roles WHERE tenant_id IS NULL AND slug = :slug"),
            {"slug": slug},
        ).first():
            continue
        bind.execute(
            roles_table.insert().values(
                id=str(uuid4()),
                tenant_id=None,
                slug=slug,
                name=role["name"],
                description=role["description"],
                permissions=role["permissions"],
                is_builtin=bool(role["is_builtin"]),
                is_assignable=True,
                system_template_id=None,
                system_required=False,
                created_at=now,
                updated_at=now,
            ),
        )


def upgrade() -> None:
    op.create_table('core_scopes',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('slug', sa.String(length=100), nullable=False),
    sa.Column('name', sa.String(length=255), nullable=False),
    sa.Column('description', sa.Text(), nullable=True),
    sa.Column('default_locale', sa.String(length=20), nullable=False),
    sa.Column('settings', sa.JSON(), nullable=False),
    sa.Column('allow_custom_groups', sa.Boolean(), nullable=True),
    sa.Column('allow_custom_roles', sa.Boolean(), nullable=True),
    sa.Column('allow_api_keys', sa.Boolean(), nullable=True),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_core_scopes'))
    )
    op.create_index(op.f('ix_core_scopes_slug'), 'core_scopes', ['slug'], unique=True)
    op.create_table('access_accounts',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('email', sa.String(length=320), nullable=False),
    sa.Column('normalized_email', sa.String(length=320), nullable=False),
    sa.Column('display_name', sa.String(length=255), nullable=True),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('auth_provider', sa.String(length=50), nullable=False),
    sa.Column('password_hash', sa.String(length=500), nullable=True),
    sa.Column('password_reset_required', sa.Boolean(), nullable=False),
    sa.Column('last_login_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_access_accounts'))
    )
    op.create_index(op.f('ix_access_accounts_normalized_email'), 'access_accounts', ['normalized_email'], unique=True)
    op.create_table('access_groups',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('tenant_id', sa.String(length=36), nullable=False),
    sa.Column('slug', sa.String(length=100), nullable=False),
    sa.Column('name', sa.String(length=255), nullable=False),
    sa.Column('description', sa.Text(), nullable=True),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('system_template_id', sa.String(length=36), nullable=True),
    sa.Column('system_required', sa.Boolean(), nullable=False),
    sa.Column('settings', sa.JSON(), nullable=False),
    sa.Column('mail_profile_policy', sa.JSON(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_access_groups')),
    sa.UniqueConstraint('tenant_id', 'slug', name='uq_groups_tenant_slug')
    )
    op.create_index(op.f('ix_access_groups_system_template_id'), 'access_groups', ['system_template_id'], unique=False)
    op.create_index(op.f('ix_access_groups_tenant_id'), 'access_groups', ['tenant_id'], unique=False)
    op.create_table('access_roles',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('tenant_id', sa.String(length=36), nullable=True),
    sa.Column('slug', sa.String(length=100), nullable=False),
    sa.Column('name', sa.String(length=255), nullable=False),
    sa.Column('description', sa.Text(), nullable=True),
    sa.Column('permissions', sa.JSON(), nullable=False),
    sa.Column('is_builtin', sa.Boolean(), nullable=False),
    sa.Column('is_assignable', sa.Boolean(), nullable=False),
    sa.Column('system_template_id', sa.String(length=36), nullable=True),
    sa.Column('system_required', sa.Boolean(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_access_roles')),
    sa.UniqueConstraint('tenant_id', 'slug', name='uq_roles_tenant_slug')
    )
    op.create_index(op.f('ix_access_roles_system_template_id'), 'access_roles', ['system_template_id'], unique=False)
    op.create_index(op.f('ix_access_roles_tenant_id'), 'access_roles', ['tenant_id'], unique=False)
    op.create_index('uq_roles_system_slug', 'access_roles', ['slug'], unique=True, sqlite_where=sa.text('tenant_id IS NULL'), postgresql_where=sa.text('tenant_id IS NULL'))
    op.create_table('admin_governance_templates',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('kind', sa.String(length=20), nullable=False),
    sa.Column('slug', sa.String(length=100), nullable=False),
    sa.Column('name', sa.String(length=255), nullable=False),
    sa.Column('description', sa.Text(), nullable=True),
    sa.Column('permissions', sa.JSON(), nullable=False),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_admin_governance_templates')),
    sa.UniqueConstraint('kind', 'slug', name='uq_governance_templates_kind_slug')
    )
    op.create_index(op.f('ix_admin_governance_templates_kind'), 'admin_governance_templates', ['kind'], unique=False)
    op.create_table('attachment_blobs',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('tenant_id', sa.String(length=36), nullable=False),
    sa.Column('sha256', sa.String(length=64), nullable=False),
    sa.Column('size_bytes', sa.Integer(), nullable=False),
    sa.Column('mime_type', sa.String(length=255), nullable=True),
    sa.Column('storage_bucket', sa.String(length=255), nullable=False),
    sa.Column('storage_key', sa.String(length=1000), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_attachment_blobs')),
    sa.UniqueConstraint('tenant_id', 'sha256', name='uq_attachment_blobs_tenant_sha256')
    )
    op.create_index(op.f('ix_attachment_blobs_sha256'), 'attachment_blobs', ['sha256'], unique=False)
    op.create_index(op.f('ix_attachment_blobs_tenant_id'), 'attachment_blobs', ['tenant_id'], unique=False)
    op.create_table('audit_outbox_events',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('event_id', sa.String(length=36), nullable=False),
    sa.Column('event_type', sa.String(length=200), nullable=False),
    sa.Column('module_id', sa.String(length=100), nullable=False),
    sa.Column('correlation_id', sa.String(length=128), nullable=True),
    sa.Column('causation_id', sa.String(length=128), nullable=True),
    sa.Column('classification', sa.String(length=40), nullable=False),
    sa.Column('payload', sa.JSON(), nullable=False),
    sa.Column('status', sa.String(length=20), nullable=False),
    sa.Column('attempts', sa.Integer(), nullable=False),
    sa.Column('next_attempt_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('dispatched_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('last_error', sa.Text(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_audit_outbox_events')),
    sa.UniqueConstraint('event_id', name='uq_audit_outbox_events_event_id')
    )
    op.create_index('ix_audit_outbox_events_correlation_id', 'audit_outbox_events', ['correlation_id'], unique=False)
    op.create_index('ix_audit_outbox_events_event_type', 'audit_outbox_events', ['event_type'], unique=False)
    op.create_index(op.f('ix_audit_outbox_events_status'), 'audit_outbox_events', ['status'], unique=False)
    op.create_index('ix_audit_outbox_events_status_next_attempt_at', 'audit_outbox_events', ['status', 'next_attempt_at'], unique=False)
    op.create_table('core_change_sequence',
    sa.Column('id', sa.BigInteger().with_variant(sa.Integer(), 'sqlite'), autoincrement=True, nullable=False),
    sa.Column('tenant_id', sa.String(length=36), nullable=True),
    sa.Column('module_id', sa.String(length=100), nullable=False),
    sa.Column('collection', sa.String(length=150), nullable=False),
    sa.Column('resource_type', sa.String(length=100), nullable=False),
    sa.Column('resource_id', sa.String(length=255), nullable=False),
    sa.Column('operation', sa.String(length=30), nullable=False),
    sa.Column('actor_type', sa.String(length=30), nullable=True),
    sa.Column('actor_id', sa.String(length=255), nullable=True),
    sa.Column('payload', sa.JSON(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_core_change_sequence'))
    )
    op.create_index(op.f('ix_core_change_sequence_actor_id'), 'core_change_sequence', ['actor_id'], unique=False)
    op.create_index(op.f('ix_core_change_sequence_actor_type'), 'core_change_sequence', ['actor_type'], unique=False)
    op.create_index(op.f('ix_core_change_sequence_collection'), 'core_change_sequence', ['collection'], unique=False)
    op.create_index('ix_core_change_sequence_collection_id', 'core_change_sequence', ['collection', 'id'], unique=False)
    op.create_index(op.f('ix_core_change_sequence_created_at'), 'core_change_sequence', ['created_at'], unique=False)
    op.create_index(op.f('ix_core_change_sequence_module_id'), 'core_change_sequence', ['module_id'], unique=False)
    op.create_index(op.f('ix_core_change_sequence_operation'), 'core_change_sequence', ['operation'], unique=False)
    op.create_index('ix_core_change_sequence_resource', 'core_change_sequence', ['module_id', 'resource_type', 'resource_id'], unique=False)
    op.create_index(op.f('ix_core_change_sequence_resource_id'), 'core_change_sequence', ['resource_id'], unique=False)
    op.create_index(op.f('ix_core_change_sequence_resource_type'), 'core_change_sequence', ['resource_type'], unique=False)
    op.create_index(op.f('ix_core_change_sequence_tenant_id'), 'core_change_sequence', ['tenant_id'], unique=False)
    op.create_index('ix_core_change_sequence_tenant_module_id', 'core_change_sequence', ['tenant_id', 'module_id', 'id'], unique=False)
    op.create_table('core_change_sequence_retention_floor',
    sa.Column('id', sa.BigInteger().with_variant(sa.Integer(), 'sqlite'), autoincrement=True, nullable=False),
    sa.Column('tenant_key', sa.String(length=36), nullable=False),
    sa.Column('module_id', sa.String(length=100), nullable=False),
    sa.Column('collection', sa.String(length=150), nullable=False),
    sa.Column('min_valid_sequence', sa.BigInteger().with_variant(sa.Integer(), 'sqlite'), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_core_change_sequence_retention_floor')),
    sa.UniqueConstraint('tenant_key', 'module_id', 'collection', name='uq_core_change_sequence_retention_scope')
    )
    op.create_index('ix_core_change_sequence_retention_scope', 'core_change_sequence_retention_floor', ['tenant_key', 'module_id', 'collection'], unique=False)
    op.create_table('core_system_settings',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('default_locale', sa.String(length=20), nullable=False),
    sa.Column('allow_tenant_custom_groups', sa.Boolean(), nullable=False),
    sa.Column('allow_tenant_custom_roles', sa.Boolean(), nullable=False),
    sa.Column('allow_tenant_api_keys', sa.Boolean(), nullable=False),
    sa.Column('settings', sa.JSON(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_core_system_settings'))
    )
    op.create_table('file_blobs',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('tenant_id', sa.String(length=36), nullable=False),
    sa.Column('storage_backend', sa.String(length=50), nullable=False),
    sa.Column('storage_bucket', sa.String(length=255), nullable=True),
    sa.Column('storage_key', sa.String(length=1000), nullable=False),
    sa.Column('checksum_sha256', sa.String(length=64), nullable=False),
    sa.Column('size_bytes', sa.Integer(), nullable=False),
    sa.Column('content_type', sa.String(length=255), nullable=True),
    sa.Column('ref_count', sa.Integer(), nullable=False),
    sa.Column('retained_until', sa.DateTime(timezone=True), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_file_blobs')),
    sa.UniqueConstraint('tenant_id', 'checksum_sha256', 'size_bytes', name='uq_file_blobs_tenant_checksum_size')
    )
    op.create_index(op.f('ix_file_blobs_checksum_sha256'), 'file_blobs', ['checksum_sha256'], unique=False)
    op.create_index(op.f('ix_file_blobs_tenant_id'), 'file_blobs', ['tenant_id'], unique=False)
    op.create_table('access_group_role_assignments',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('tenant_id', sa.String(length=36), nullable=False),
    sa.Column('group_id', sa.String(length=36), nullable=False),
    sa.Column('role_id', sa.String(length=36), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['group_id'], ['access_groups.id'], name=op.f('fk_access_group_role_assignments_group_id_access_groups'), ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['role_id'], ['access_roles.id'], name=op.f('fk_access_group_role_assignments_role_id_access_roles'), ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_access_group_role_assignments')),
    sa.UniqueConstraint('tenant_id', 'group_id', 'role_id', name='uq_group_role_assignments')
    )
    op.create_index(op.f('ix_access_group_role_assignments_group_id'), 'access_group_role_assignments', ['group_id'], unique=False)
    op.create_index(op.f('ix_access_group_role_assignments_role_id'), 'access_group_role_assignments', ['role_id'], unique=False)
    op.create_index(op.f('ix_access_group_role_assignments_tenant_id'), 'access_group_role_assignments', ['tenant_id'], unique=False)
    op.create_table('access_system_role_assignments',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('account_id', sa.String(length=36), nullable=False),
    sa.Column('role_id', sa.String(length=36), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['account_id'], ['access_accounts.id'], name=op.f('fk_access_system_role_assignments_account_id_access_accounts'), ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['role_id'], ['access_roles.id'], name=op.f('fk_access_system_role_assignments_role_id_access_roles'), ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_access_system_role_assignments')),
    sa.UniqueConstraint('account_id', 'role_id', name='uq_system_role_assignments')
    )
    op.create_index(op.f('ix_access_system_role_assignments_account_id'), 'access_system_role_assignments', ['account_id'], unique=False)
    op.create_index(op.f('ix_access_system_role_assignments_role_id'), 'access_system_role_assignments', ['role_id'], unique=False)
    op.create_table('access_users',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('tenant_id', sa.String(length=36), nullable=False),
    sa.Column('account_id', sa.String(length=36), nullable=False),
    sa.Column('email', sa.String(length=320), nullable=False),
    sa.Column('display_name', sa.String(length=255), nullable=True),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('is_tenant_admin', sa.Boolean(), nullable=False),
    sa.Column('auth_provider', sa.String(length=50), nullable=False),
    sa.Column('password_hash', sa.String(length=500), nullable=True),
    sa.Column('last_login_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('settings', sa.JSON(), nullable=False),
    sa.Column('mail_profile_policy', sa.JSON(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['account_id'], ['access_accounts.id'], name=op.f('fk_access_users_account_id_access_accounts'), ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_access_users')),
    sa.UniqueConstraint('tenant_id', 'account_id', name='uq_users_tenant_account'),
    sa.UniqueConstraint('tenant_id', 'email', name='uq_users_tenant_email')
    )
    op.create_index(op.f('ix_access_users_account_id'), 'access_users', ['account_id'], unique=False)
    op.create_index(op.f('ix_access_users_email'), 'access_users', ['email'], unique=False)
    op.create_index(op.f('ix_access_users_tenant_id'), 'access_users', ['tenant_id'], unique=False)
    op.create_table('admin_governance_template_assignments',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('template_id', sa.String(length=36), nullable=False),
    sa.Column('tenant_id', sa.String(length=36), nullable=False),
    sa.Column('mode', sa.String(length=20), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['template_id'], ['admin_governance_templates.id'], name=op.f('fk_admin_governance_template_assignments_template_id_admin_governance_templates'), ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_admin_governance_template_assignments')),
    sa.UniqueConstraint('template_id', 'tenant_id', name='uq_governance_template_tenant')
    )
    op.create_index(op.f('ix_admin_governance_template_assignments_template_id'), 'admin_governance_template_assignments', ['template_id'], unique=False)
    op.create_index(op.f('ix_admin_governance_template_assignments_tenant_id'), 'admin_governance_template_assignments', ['tenant_id'], unique=False)
    op.create_table('access_api_keys',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('tenant_id', sa.String(length=36), nullable=False),
    sa.Column('user_id', sa.String(length=36), nullable=False),
    sa.Column('name', sa.String(length=255), nullable=False),
    sa.Column('prefix', sa.String(length=16), nullable=False),
    sa.Column('key_hash', sa.String(length=128), nullable=False),
    sa.Column('scopes', sa.JSON(), nullable=False),
    sa.Column('expires_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('last_used_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('revoked_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['user_id'], ['access_users.id'], name=op.f('fk_access_api_keys_user_id_access_users'), ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_access_api_keys'))
    )
    op.create_index(op.f('ix_access_api_keys_prefix'), 'access_api_keys', ['prefix'], unique=False)
    op.create_index(op.f('ix_access_api_keys_tenant_id'), 'access_api_keys', ['tenant_id'], unique=False)
    op.create_index(op.f('ix_access_api_keys_user_id'), 'access_api_keys', ['user_id'], unique=False)
    op.create_table('access_auth_sessions',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('tenant_id', sa.String(length=36), nullable=False),
    sa.Column('user_id', sa.String(length=36), nullable=False),
    sa.Column('account_id', sa.String(length=36), nullable=False),
    sa.Column('token_hash', sa.String(length=128), nullable=False),
    sa.Column('csrf_token_hash', sa.String(length=128), nullable=True),
    sa.Column('expires_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('last_seen_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('revoked_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('user_agent', sa.String(length=500), nullable=True),
    sa.Column('ip_address', sa.String(length=100), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['account_id'], ['access_accounts.id'], name=op.f('fk_access_auth_sessions_account_id_access_accounts'), ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['user_id'], ['access_users.id'], name=op.f('fk_access_auth_sessions_user_id_access_users'), ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_access_auth_sessions'))
    )
    op.create_index(op.f('ix_access_auth_sessions_account_id'), 'access_auth_sessions', ['account_id'], unique=False)
    op.create_index(op.f('ix_access_auth_sessions_expires_at'), 'access_auth_sessions', ['expires_at'], unique=False)
    op.create_index(op.f('ix_access_auth_sessions_revoked_at'), 'access_auth_sessions', ['revoked_at'], unique=False)
    op.create_index(op.f('ix_access_auth_sessions_tenant_id'), 'access_auth_sessions', ['tenant_id'], unique=False)
    op.create_index(op.f('ix_access_auth_sessions_token_hash'), 'access_auth_sessions', ['token_hash'], unique=True)
    op.create_index(op.f('ix_access_auth_sessions_user_id'), 'access_auth_sessions', ['user_id'], unique=False)
    op.create_table('access_user_group_memberships',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('tenant_id', sa.String(length=36), nullable=False),
    sa.Column('user_id', sa.String(length=36), nullable=False),
    sa.Column('group_id', sa.String(length=36), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['group_id'], ['access_groups.id'], name=op.f('fk_access_user_group_memberships_group_id_access_groups'), ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['user_id'], ['access_users.id'], name=op.f('fk_access_user_group_memberships_user_id_access_users'), ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_access_user_group_memberships')),
    sa.UniqueConstraint('tenant_id', 'user_id', 'group_id', name='uq_user_group_memberships')
    )
    op.create_index(op.f('ix_access_user_group_memberships_group_id'), 'access_user_group_memberships', ['group_id'], unique=False)
    op.create_index(op.f('ix_access_user_group_memberships_tenant_id'), 'access_user_group_memberships', ['tenant_id'], unique=False)
    op.create_index(op.f('ix_access_user_group_memberships_user_id'), 'access_user_group_memberships', ['user_id'], unique=False)
    op.create_table('access_user_role_assignments',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('tenant_id', sa.String(length=36), nullable=False),
    sa.Column('user_id', sa.String(length=36), nullable=False),
    sa.Column('role_id', sa.String(length=36), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['role_id'], ['access_roles.id'], name=op.f('fk_access_user_role_assignments_role_id_access_roles'), ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['user_id'], ['access_users.id'], name=op.f('fk_access_user_role_assignments_user_id_access_users'), ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_access_user_role_assignments')),
    sa.UniqueConstraint('tenant_id', 'user_id', 'role_id', name='uq_user_role_assignments')
    )
    op.create_index(op.f('ix_access_user_role_assignments_role_id'), 'access_user_role_assignments', ['role_id'], unique=False)
    op.create_index(op.f('ix_access_user_role_assignments_tenant_id'), 'access_user_role_assignments', ['tenant_id'], unique=False)
    op.create_index(op.f('ix_access_user_role_assignments_user_id'), 'access_user_role_assignments', ['user_id'], unique=False)
    op.create_table('campaigns',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('tenant_id', sa.String(length=36), nullable=False),
    sa.Column('created_by_user_id', sa.String(length=36), nullable=True),
    sa.Column('owner_user_id', sa.String(length=36), nullable=True),
    sa.Column('owner_group_id', sa.String(length=36), nullable=True),
    sa.Column('external_id', sa.String(length=255), nullable=False),
    sa.Column('name', sa.String(length=255), nullable=False),
    sa.Column('description', sa.Text(), nullable=True),
    sa.Column('status', sa.String(length=50), nullable=False),
    sa.Column('current_version_id', sa.String(length=36), nullable=True),
    sa.Column('settings', sa.JSON(), nullable=False),
    sa.Column('mail_profile_policy', sa.JSON(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['created_by_user_id'], ['access_users.id'], name=op.f('fk_campaigns_created_by_user_id_access_users'), ondelete='SET NULL'),
    sa.ForeignKeyConstraint(['owner_group_id'], ['access_groups.id'], name=op.f('fk_campaigns_owner_group_id_access_groups'), ondelete='SET NULL'),
    sa.ForeignKeyConstraint(['owner_user_id'], ['access_users.id'], name=op.f('fk_campaigns_owner_user_id_access_users'), ondelete='SET NULL'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_campaigns')),
    sa.UniqueConstraint('tenant_id', 'external_id', name='uq_campaigns_tenant_external_id')
    )
    op.create_index(op.f('ix_campaigns_created_by_user_id'), 'campaigns', ['created_by_user_id'], unique=False)
    op.create_index(op.f('ix_campaigns_external_id'), 'campaigns', ['external_id'], unique=False)
    op.create_index(op.f('ix_campaigns_owner_group_id'), 'campaigns', ['owner_group_id'], unique=False)
    op.create_index(op.f('ix_campaigns_owner_user_id'), 'campaigns', ['owner_user_id'], unique=False)
    op.create_index(op.f('ix_campaigns_status'), 'campaigns', ['status'], unique=False)
    op.create_index(op.f('ix_campaigns_tenant_id'), 'campaigns', ['tenant_id'], unique=False)
    op.create_table('file_assets',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('tenant_id', sa.String(length=36), nullable=False),
    sa.Column('owner_type', sa.String(length=20), nullable=False),
    sa.Column('owner_user_id', sa.String(length=36), nullable=True),
    sa.Column('owner_group_id', sa.String(length=36), nullable=True),
    sa.Column('current_version_id', sa.String(length=36), nullable=True),
    sa.Column('display_path', sa.String(length=1000), nullable=False),
    sa.Column('filename', sa.String(length=500), nullable=False),
    sa.Column('description', sa.Text(), nullable=True),
    sa.Column('created_by_user_id', sa.String(length=36), nullable=True),
    sa.Column('deleted_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('metadata', sa.JSON(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['created_by_user_id'], ['access_users.id'], name=op.f('fk_file_assets_created_by_user_id_access_users'), ondelete='SET NULL'),
    sa.ForeignKeyConstraint(['owner_group_id'], ['access_groups.id'], name=op.f('fk_file_assets_owner_group_id_access_groups'), ondelete='SET NULL'),
    sa.ForeignKeyConstraint(['owner_user_id'], ['access_users.id'], name=op.f('fk_file_assets_owner_user_id_access_users'), ondelete='SET NULL'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_file_assets'))
    )
    op.create_index(op.f('ix_file_assets_created_by_user_id'), 'file_assets', ['created_by_user_id'], unique=False)
    op.create_index(op.f('ix_file_assets_current_version_id'), 'file_assets', ['current_version_id'], unique=False)
    op.create_index(op.f('ix_file_assets_deleted_at'), 'file_assets', ['deleted_at'], unique=False)
    op.create_index(op.f('ix_file_assets_display_path'), 'file_assets', ['display_path'], unique=False)
    op.create_index(op.f('ix_file_assets_filename'), 'file_assets', ['filename'], unique=False)
    op.create_index(op.f('ix_file_assets_owner_group_id'), 'file_assets', ['owner_group_id'], unique=False)
    op.create_index(op.f('ix_file_assets_owner_type'), 'file_assets', ['owner_type'], unique=False)
    op.create_index(op.f('ix_file_assets_owner_user_id'), 'file_assets', ['owner_user_id'], unique=False)
    op.create_index(op.f('ix_file_assets_tenant_id'), 'file_assets', ['tenant_id'], unique=False)
    op.create_table('file_folders',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('tenant_id', sa.String(length=36), nullable=False),
    sa.Column('owner_type', sa.String(length=20), nullable=False),
    sa.Column('owner_user_id', sa.String(length=36), nullable=True),
    sa.Column('owner_group_id', sa.String(length=36), nullable=True),
    sa.Column('path', sa.String(length=1000), nullable=False),
    sa.Column('created_by_user_id', sa.String(length=36), nullable=True),
    sa.Column('deleted_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('metadata', sa.JSON(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['created_by_user_id'], ['access_users.id'], name=op.f('fk_file_folders_created_by_user_id_access_users'), ondelete='SET NULL'),
    sa.ForeignKeyConstraint(['owner_group_id'], ['access_groups.id'], name=op.f('fk_file_folders_owner_group_id_access_groups'), ondelete='SET NULL'),
    sa.ForeignKeyConstraint(['owner_user_id'], ['access_users.id'], name=op.f('fk_file_folders_owner_user_id_access_users'), ondelete='SET NULL'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_file_folders'))
    )
    op.create_index(op.f('ix_file_folders_created_by_user_id'), 'file_folders', ['created_by_user_id'], unique=False)
    op.create_index(op.f('ix_file_folders_deleted_at'), 'file_folders', ['deleted_at'], unique=False)
    op.create_index(op.f('ix_file_folders_owner_group_id'), 'file_folders', ['owner_group_id'], unique=False)
    op.create_index(op.f('ix_file_folders_owner_type'), 'file_folders', ['owner_type'], unique=False)
    op.create_index(op.f('ix_file_folders_owner_user_id'), 'file_folders', ['owner_user_id'], unique=False)
    op.create_index(op.f('ix_file_folders_path'), 'file_folders', ['path'], unique=False)
    op.create_index(op.f('ix_file_folders_tenant_id'), 'file_folders', ['tenant_id'], unique=False)
    op.create_index('uq_file_folders_active_group_path', 'file_folders', ['tenant_id', 'owner_group_id', 'path'], unique=True, sqlite_where=sa.text("owner_type = 'group' AND deleted_at IS NULL"), postgresql_where=sa.text("owner_type = 'group' AND deleted_at IS NULL"))
    op.create_index('uq_file_folders_active_user_path', 'file_folders', ['tenant_id', 'owner_user_id', 'path'], unique=True, sqlite_where=sa.text("owner_type = 'user' AND deleted_at IS NULL"), postgresql_where=sa.text("owner_type = 'user' AND deleted_at IS NULL"))
    op.create_table('mail_server_profiles',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('tenant_id', sa.String(length=36), nullable=True),
    sa.Column('scope_type', sa.String(length=20), nullable=False),
    sa.Column('scope_id', sa.String(length=36), nullable=True),
    sa.Column('name', sa.String(length=255), nullable=False),
    sa.Column('slug', sa.String(length=100), nullable=False),
    sa.Column('description', sa.Text(), nullable=True),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('smtp_config', sa.JSON(), nullable=False),
    sa.Column('smtp_username', sa.String(length=320), nullable=True),
    sa.Column('smtp_password_encrypted', sa.Text(), nullable=True),
    sa.Column('imap_config', sa.JSON(), nullable=True),
    sa.Column('imap_username', sa.String(length=320), nullable=True),
    sa.Column('imap_password_encrypted', sa.Text(), nullable=True),
    sa.Column('created_by_user_id', sa.String(length=36), nullable=True),
    sa.Column('updated_by_user_id', sa.String(length=36), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['created_by_user_id'], ['access_users.id'], name=op.f('fk_mail_server_profiles_created_by_user_id_access_users'), ondelete='SET NULL'),
    sa.ForeignKeyConstraint(['updated_by_user_id'], ['access_users.id'], name=op.f('fk_mail_server_profiles_updated_by_user_id_access_users'), ondelete='SET NULL'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_mail_server_profiles')),
    sa.UniqueConstraint('tenant_id', 'slug', name='uq_mail_server_profiles_tenant_slug')
    )
    op.create_index(op.f('ix_mail_server_profiles_created_by_user_id'), 'mail_server_profiles', ['created_by_user_id'], unique=False)
    op.create_index(op.f('ix_mail_server_profiles_is_active'), 'mail_server_profiles', ['is_active'], unique=False)
    op.create_index('ix_mail_server_profiles_scope', 'mail_server_profiles', ['scope_type', 'scope_id'], unique=False)
    op.create_index(op.f('ix_mail_server_profiles_scope_id'), 'mail_server_profiles', ['scope_id'], unique=False)
    op.create_index(op.f('ix_mail_server_profiles_scope_type'), 'mail_server_profiles', ['scope_type'], unique=False)
    op.create_index(op.f('ix_mail_server_profiles_tenant_id'), 'mail_server_profiles', ['tenant_id'], unique=False)
    op.create_index(op.f('ix_mail_server_profiles_updated_by_user_id'), 'mail_server_profiles', ['updated_by_user_id'], unique=False)
    op.create_table('attachment_instances',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('tenant_id', sa.String(length=36), nullable=False),
    sa.Column('owner_user_id', sa.String(length=36), nullable=True),
    sa.Column('campaign_id', sa.String(length=36), nullable=True),
    sa.Column('blob_id', sa.String(length=36), nullable=False),
    sa.Column('logical_name', sa.String(length=500), nullable=True),
    sa.Column('filename', sa.String(length=500), nullable=False),
    sa.Column('tags', sa.JSON(), nullable=False),
    sa.Column('metadata', sa.JSON(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['blob_id'], ['attachment_blobs.id'], name=op.f('fk_attachment_instances_blob_id_attachment_blobs'), ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['campaign_id'], ['campaigns.id'], name=op.f('fk_attachment_instances_campaign_id_campaigns'), ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['owner_user_id'], ['access_users.id'], name=op.f('fk_attachment_instances_owner_user_id_access_users'), ondelete='SET NULL'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_attachment_instances'))
    )
    op.create_index(op.f('ix_attachment_instances_blob_id'), 'attachment_instances', ['blob_id'], unique=False)
    op.create_index(op.f('ix_attachment_instances_campaign_id'), 'attachment_instances', ['campaign_id'], unique=False)
    op.create_index(op.f('ix_attachment_instances_owner_user_id'), 'attachment_instances', ['owner_user_id'], unique=False)
    op.create_index(op.f('ix_attachment_instances_tenant_id'), 'attachment_instances', ['tenant_id'], unique=False)
    op.create_table('audit_log',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('scope', sa.String(length=20), nullable=False),
    sa.Column('tenant_id', sa.String(length=36), nullable=True),
    sa.Column('user_id', sa.String(length=36), nullable=True),
    sa.Column('api_key_id', sa.String(length=36), nullable=True),
    sa.Column('action', sa.String(length=100), nullable=False),
    sa.Column('object_type', sa.String(length=100), nullable=True),
    sa.Column('object_id', sa.String(length=100), nullable=True),
    sa.Column('details', sa.JSON(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['api_key_id'], ['access_api_keys.id'], name=op.f('fk_audit_log_api_key_id_access_api_keys'), ondelete='SET NULL'),
    sa.ForeignKeyConstraint(['user_id'], ['access_users.id'], name=op.f('fk_audit_log_user_id_access_users'), ondelete='SET NULL'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_audit_log'))
    )
    op.create_index(op.f('ix_audit_log_action'), 'audit_log', ['action'], unique=False)
    op.create_index(op.f('ix_audit_log_api_key_id'), 'audit_log', ['api_key_id'], unique=False)
    op.create_index(op.f('ix_audit_log_object_id'), 'audit_log', ['object_id'], unique=False)
    op.create_index(op.f('ix_audit_log_object_type'), 'audit_log', ['object_type'], unique=False)
    op.create_index(op.f('ix_audit_log_scope'), 'audit_log', ['scope'], unique=False)
    op.create_index('ix_audit_log_scope_created_at', 'audit_log', ['scope', 'created_at'], unique=False)
    op.create_index(op.f('ix_audit_log_tenant_id'), 'audit_log', ['tenant_id'], unique=False)
    op.create_index('ix_audit_log_tenant_scope_created_at', 'audit_log', ['tenant_id', 'scope', 'created_at'], unique=False)
    op.create_index(op.f('ix_audit_log_user_id'), 'audit_log', ['user_id'], unique=False)
    op.create_table('campaign_shares',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('tenant_id', sa.String(length=36), nullable=False),
    sa.Column('campaign_id', sa.String(length=36), nullable=False),
    sa.Column('target_type', sa.String(length=20), nullable=False),
    sa.Column('target_id', sa.String(length=36), nullable=False),
    sa.Column('permission', sa.String(length=20), nullable=False),
    sa.Column('created_by_user_id', sa.String(length=36), nullable=True),
    sa.Column('revoked_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['campaign_id'], ['campaigns.id'], name=op.f('fk_campaign_shares_campaign_id_campaigns'), ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['created_by_user_id'], ['access_users.id'], name=op.f('fk_campaign_shares_created_by_user_id_access_users'), ondelete='SET NULL'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_campaign_shares')),
    sa.UniqueConstraint('campaign_id', 'target_type', 'target_id', name='uq_campaign_share_target')
    )
    op.create_index(op.f('ix_campaign_shares_campaign_id'), 'campaign_shares', ['campaign_id'], unique=False)
    op.create_index(op.f('ix_campaign_shares_created_by_user_id'), 'campaign_shares', ['created_by_user_id'], unique=False)
    op.create_index(op.f('ix_campaign_shares_revoked_at'), 'campaign_shares', ['revoked_at'], unique=False)
    op.create_index(op.f('ix_campaign_shares_target_id'), 'campaign_shares', ['target_id'], unique=False)
    op.create_index(op.f('ix_campaign_shares_target_type'), 'campaign_shares', ['target_type'], unique=False)
    op.create_index(op.f('ix_campaign_shares_tenant_id'), 'campaign_shares', ['tenant_id'], unique=False)
    op.create_table('campaign_versions',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('campaign_id', sa.String(length=36), nullable=False),
    sa.Column('version_number', sa.Integer(), nullable=False),
    sa.Column('raw_json', sa.JSON(), nullable=False),
    sa.Column('schema_version', sa.String(length=50), nullable=False),
    sa.Column('source_filename', sa.String(length=500), nullable=True),
    sa.Column('source_base_path', sa.String(length=1000), nullable=True),
    sa.Column('workflow_state', sa.String(length=50), nullable=False),
    sa.Column('current_flow', sa.String(length=50), nullable=False),
    sa.Column('current_step', sa.String(length=100), nullable=True),
    sa.Column('is_complete', sa.Boolean(), nullable=False),
    sa.Column('editor_state', sa.JSON(), nullable=False),
    sa.Column('autosaved_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('published_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('locked_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('locked_by_user_id', sa.String(length=36), nullable=True),
    sa.Column('user_lock_state', sa.String(length=20), nullable=True),
    sa.Column('user_locked_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('user_locked_by_user_id', sa.String(length=36), nullable=True),
    sa.Column('validation_summary', sa.JSON(), nullable=True),
    sa.Column('build_summary', sa.JSON(), nullable=True),
    sa.Column('execution_snapshot', sa.JSON(), nullable=True),
    sa.Column('execution_snapshot_hash', sa.String(length=64), nullable=True),
    sa.Column('execution_snapshot_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['campaign_id'], ['campaigns.id'], name=op.f('fk_campaign_versions_campaign_id_campaigns'), ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['locked_by_user_id'], ['access_users.id'], name=op.f('fk_campaign_versions_locked_by_user_id_access_users'), ondelete='SET NULL'),
    sa.ForeignKeyConstraint(['user_locked_by_user_id'], ['access_users.id'], name=op.f('fk_campaign_versions_user_locked_by_user_id_access_users'), ondelete='SET NULL'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_campaign_versions')),
    sa.UniqueConstraint('campaign_id', 'version_number', name='uq_campaign_versions_campaign_number')
    )
    op.create_index(op.f('ix_campaign_versions_campaign_id'), 'campaign_versions', ['campaign_id'], unique=False)
    op.create_index(op.f('ix_campaign_versions_current_flow'), 'campaign_versions', ['current_flow'], unique=False)
    op.create_index(op.f('ix_campaign_versions_execution_snapshot_hash'), 'campaign_versions', ['execution_snapshot_hash'], unique=False)
    op.create_index(op.f('ix_campaign_versions_locked_by_user_id'), 'campaign_versions', ['locked_by_user_id'], unique=False)
    op.create_index(op.f('ix_campaign_versions_user_lock_state'), 'campaign_versions', ['user_lock_state'], unique=False)
    op.create_index(op.f('ix_campaign_versions_user_locked_by_user_id'), 'campaign_versions', ['user_locked_by_user_id'], unique=False)
    op.create_index(op.f('ix_campaign_versions_workflow_state'), 'campaign_versions', ['workflow_state'], unique=False)
    op.create_table('file_shares',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('tenant_id', sa.String(length=36), nullable=False),
    sa.Column('file_asset_id', sa.String(length=36), nullable=False),
    sa.Column('target_type', sa.String(length=20), nullable=False),
    sa.Column('target_id', sa.String(length=36), nullable=False),
    sa.Column('permission', sa.String(length=20), nullable=False),
    sa.Column('created_by_user_id', sa.String(length=36), nullable=True),
    sa.Column('revoked_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['created_by_user_id'], ['access_users.id'], name=op.f('fk_file_shares_created_by_user_id_access_users'), ondelete='SET NULL'),
    sa.ForeignKeyConstraint(['file_asset_id'], ['file_assets.id'], name=op.f('fk_file_shares_file_asset_id_file_assets'), ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_file_shares')),
    sa.UniqueConstraint('file_asset_id', 'target_type', 'target_id', 'revoked_at', name='uq_file_shares_active_target')
    )
    op.create_index(op.f('ix_file_shares_created_by_user_id'), 'file_shares', ['created_by_user_id'], unique=False)
    op.create_index(op.f('ix_file_shares_file_asset_id'), 'file_shares', ['file_asset_id'], unique=False)
    op.create_index(op.f('ix_file_shares_revoked_at'), 'file_shares', ['revoked_at'], unique=False)
    op.create_index(op.f('ix_file_shares_target_id'), 'file_shares', ['target_id'], unique=False)
    op.create_index(op.f('ix_file_shares_target_type'), 'file_shares', ['target_type'], unique=False)
    op.create_index(op.f('ix_file_shares_tenant_id'), 'file_shares', ['tenant_id'], unique=False)
    op.create_table('file_versions',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('tenant_id', sa.String(length=36), nullable=False),
    sa.Column('file_asset_id', sa.String(length=36), nullable=False),
    sa.Column('blob_id', sa.String(length=36), nullable=False),
    sa.Column('version_number', sa.Integer(), nullable=False),
    sa.Column('filename_at_upload', sa.String(length=500), nullable=False),
    sa.Column('display_path_at_upload', sa.String(length=1000), nullable=False),
    sa.Column('content_type', sa.String(length=255), nullable=True),
    sa.Column('size_bytes', sa.Integer(), nullable=False),
    sa.Column('checksum_sha256', sa.String(length=64), nullable=False),
    sa.Column('created_by_user_id', sa.String(length=36), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['blob_id'], ['file_blobs.id'], name=op.f('fk_file_versions_blob_id_file_blobs'), ondelete='RESTRICT'),
    sa.ForeignKeyConstraint(['created_by_user_id'], ['access_users.id'], name=op.f('fk_file_versions_created_by_user_id_access_users'), ondelete='SET NULL'),
    sa.ForeignKeyConstraint(['file_asset_id'], ['file_assets.id'], name=op.f('fk_file_versions_file_asset_id_file_assets'), ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_file_versions')),
    sa.UniqueConstraint('file_asset_id', 'version_number', name='uq_file_versions_asset_number')
    )
    op.create_index(op.f('ix_file_versions_blob_id'), 'file_versions', ['blob_id'], unique=False)
    op.create_index(op.f('ix_file_versions_checksum_sha256'), 'file_versions', ['checksum_sha256'], unique=False)
    op.create_index(op.f('ix_file_versions_created_by_user_id'), 'file_versions', ['created_by_user_id'], unique=False)
    op.create_index(op.f('ix_file_versions_file_asset_id'), 'file_versions', ['file_asset_id'], unique=False)
    op.create_index(op.f('ix_file_versions_tenant_id'), 'file_versions', ['tenant_id'], unique=False)
    op.create_table('campaign_jobs',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('tenant_id', sa.String(length=36), nullable=False),
    sa.Column('campaign_id', sa.String(length=36), nullable=False),
    sa.Column('campaign_version_id', sa.String(length=36), nullable=False),
    sa.Column('entry_index', sa.Integer(), nullable=False),
    sa.Column('entry_id', sa.String(length=255), nullable=True),
    sa.Column('recipient_email', sa.String(length=320), nullable=True),
    sa.Column('subject', sa.String(length=998), nullable=True),
    sa.Column('message_id_header', sa.String(length=255), nullable=True),
    sa.Column('eml_storage_key', sa.String(length=1000), nullable=True),
    sa.Column('eml_local_path', sa.String(length=1000), nullable=True),
    sa.Column('eml_size_bytes', sa.Integer(), nullable=True),
    sa.Column('eml_sha256', sa.String(length=64), nullable=True),
    sa.Column('build_status', sa.String(length=50), nullable=False),
    sa.Column('validation_status', sa.String(length=50), nullable=False),
    sa.Column('queue_status', sa.String(length=50), nullable=False),
    sa.Column('send_status', sa.String(length=50), nullable=False),
    sa.Column('imap_status', sa.String(length=50), nullable=False),
    sa.Column('attempt_count', sa.Integer(), nullable=False),
    sa.Column('last_error', sa.Text(), nullable=True),
    sa.Column('queued_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('claimed_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('claim_token', sa.String(length=36), nullable=True),
    sa.Column('smtp_started_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('outcome_unknown_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('sent_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('resolved_recipients', sa.JSON(), nullable=True),
    sa.Column('resolved_attachments', sa.JSON(), nullable=False),
    sa.Column('issues_snapshot', sa.JSON(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['campaign_id'], ['campaigns.id'], name=op.f('fk_campaign_jobs_campaign_id_campaigns'), ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['campaign_version_id'], ['campaign_versions.id'], name=op.f('fk_campaign_jobs_campaign_version_id_campaign_versions'), ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_campaign_jobs')),
    sa.UniqueConstraint('campaign_version_id', 'entry_index', name='uq_campaign_jobs_version_entry')
    )
    op.create_index(op.f('ix_campaign_jobs_build_status'), 'campaign_jobs', ['build_status'], unique=False)
    op.create_index(op.f('ix_campaign_jobs_campaign_id'), 'campaign_jobs', ['campaign_id'], unique=False)
    op.create_index(op.f('ix_campaign_jobs_campaign_version_id'), 'campaign_jobs', ['campaign_version_id'], unique=False)
    op.create_index(op.f('ix_campaign_jobs_claim_token'), 'campaign_jobs', ['claim_token'], unique=False)
    op.create_index(op.f('ix_campaign_jobs_eml_sha256'), 'campaign_jobs', ['eml_sha256'], unique=False)
    op.create_index(op.f('ix_campaign_jobs_entry_id'), 'campaign_jobs', ['entry_id'], unique=False)
    op.create_index(op.f('ix_campaign_jobs_imap_status'), 'campaign_jobs', ['imap_status'], unique=False)
    op.create_index(op.f('ix_campaign_jobs_queue_status'), 'campaign_jobs', ['queue_status'], unique=False)
    op.create_index(op.f('ix_campaign_jobs_recipient_email'), 'campaign_jobs', ['recipient_email'], unique=False)
    op.create_index(op.f('ix_campaign_jobs_send_status'), 'campaign_jobs', ['send_status'], unique=False)
    op.create_index(op.f('ix_campaign_jobs_tenant_id'), 'campaign_jobs', ['tenant_id'], unique=False)
    op.create_index(op.f('ix_campaign_jobs_validation_status'), 'campaign_jobs', ['validation_status'], unique=False)
    op.create_table('campaign_attachment_uses',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('tenant_id', sa.String(length=36), nullable=False),
    sa.Column('campaign_id', sa.String(length=36), nullable=False),
    sa.Column('campaign_version_id', sa.String(length=36), nullable=False),
    sa.Column('campaign_job_id', sa.String(length=36), nullable=True),
    sa.Column('entry_index', sa.Integer(), nullable=True),
    sa.Column('entry_id', sa.String(length=255), nullable=True),
    sa.Column('file_asset_id', sa.String(length=36), nullable=False),
    sa.Column('file_version_id', sa.String(length=36), nullable=False),
    sa.Column('file_blob_id', sa.String(length=36), nullable=False),
    sa.Column('filename_used', sa.String(length=500), nullable=False),
    sa.Column('checksum_sha256', sa.String(length=64), nullable=False),
    sa.Column('size_bytes', sa.Integer(), nullable=False),
    sa.Column('content_type', sa.String(length=255), nullable=True),
    sa.Column('use_stage', sa.String(length=20), nullable=False),
    sa.Column('used_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['campaign_id'], ['campaigns.id'], name=op.f('fk_campaign_attachment_uses_campaign_id_campaigns'), ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['campaign_job_id'], ['campaign_jobs.id'], name=op.f('fk_campaign_attachment_uses_campaign_job_id_campaign_jobs'), ondelete='SET NULL'),
    sa.ForeignKeyConstraint(['campaign_version_id'], ['campaign_versions.id'], name=op.f('fk_campaign_attachment_uses_campaign_version_id_campaign_versions'), ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['file_asset_id'], ['file_assets.id'], name=op.f('fk_campaign_attachment_uses_file_asset_id_file_assets'), ondelete='RESTRICT'),
    sa.ForeignKeyConstraint(['file_blob_id'], ['file_blobs.id'], name=op.f('fk_campaign_attachment_uses_file_blob_id_file_blobs'), ondelete='RESTRICT'),
    sa.ForeignKeyConstraint(['file_version_id'], ['file_versions.id'], name=op.f('fk_campaign_attachment_uses_file_version_id_file_versions'), ondelete='RESTRICT'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_campaign_attachment_uses')),
    sa.UniqueConstraint('campaign_job_id', 'file_version_id', 'filename_used', 'use_stage', name='uq_campaign_attachment_uses_job_file_stage')
    )
    op.create_index(op.f('ix_campaign_attachment_uses_campaign_id'), 'campaign_attachment_uses', ['campaign_id'], unique=False)
    op.create_index(op.f('ix_campaign_attachment_uses_campaign_job_id'), 'campaign_attachment_uses', ['campaign_job_id'], unique=False)
    op.create_index(op.f('ix_campaign_attachment_uses_campaign_version_id'), 'campaign_attachment_uses', ['campaign_version_id'], unique=False)
    op.create_index(op.f('ix_campaign_attachment_uses_entry_id'), 'campaign_attachment_uses', ['entry_id'], unique=False)
    op.create_index(op.f('ix_campaign_attachment_uses_file_asset_id'), 'campaign_attachment_uses', ['file_asset_id'], unique=False)
    op.create_index(op.f('ix_campaign_attachment_uses_file_blob_id'), 'campaign_attachment_uses', ['file_blob_id'], unique=False)
    op.create_index(op.f('ix_campaign_attachment_uses_file_version_id'), 'campaign_attachment_uses', ['file_version_id'], unique=False)
    op.create_index(op.f('ix_campaign_attachment_uses_tenant_id'), 'campaign_attachment_uses', ['tenant_id'], unique=False)
    op.create_index(op.f('ix_campaign_attachment_uses_use_stage'), 'campaign_attachment_uses', ['use_stage'], unique=False)
    op.create_index(op.f('ix_campaign_attachment_uses_used_at'), 'campaign_attachment_uses', ['used_at'], unique=False)
    op.create_table('campaign_issues',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('tenant_id', sa.String(length=36), nullable=False),
    sa.Column('campaign_id', sa.String(length=36), nullable=False),
    sa.Column('campaign_version_id', sa.String(length=36), nullable=True),
    sa.Column('job_id', sa.String(length=36), nullable=True),
    sa.Column('severity', sa.String(length=20), nullable=False),
    sa.Column('code', sa.String(length=100), nullable=False),
    sa.Column('message', sa.Text(), nullable=False),
    sa.Column('source', sa.String(length=255), nullable=True),
    sa.Column('behavior', sa.String(length=50), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['campaign_id'], ['campaigns.id'], name=op.f('fk_campaign_issues_campaign_id_campaigns'), ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['campaign_version_id'], ['campaign_versions.id'], name=op.f('fk_campaign_issues_campaign_version_id_campaign_versions'), ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['job_id'], ['campaign_jobs.id'], name=op.f('fk_campaign_issues_job_id_campaign_jobs'), ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_campaign_issues'))
    )
    op.create_index(op.f('ix_campaign_issues_campaign_id'), 'campaign_issues', ['campaign_id'], unique=False)
    op.create_index(op.f('ix_campaign_issues_campaign_version_id'), 'campaign_issues', ['campaign_version_id'], unique=False)
    op.create_index(op.f('ix_campaign_issues_code'), 'campaign_issues', ['code'], unique=False)
    op.create_index(op.f('ix_campaign_issues_job_id'), 'campaign_issues', ['job_id'], unique=False)
    op.create_index(op.f('ix_campaign_issues_severity'), 'campaign_issues', ['severity'], unique=False)
    op.create_index(op.f('ix_campaign_issues_tenant_id'), 'campaign_issues', ['tenant_id'], unique=False)
    op.create_table('imap_append_attempts',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('job_id', sa.String(length=36), nullable=False),
    sa.Column('attempt_number', sa.Integer(), nullable=False),
    sa.Column('folder', sa.String(length=500), nullable=True),
    sa.Column('status', sa.String(length=50), nullable=False),
    sa.Column('error_message', sa.Text(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['job_id'], ['campaign_jobs.id'], name=op.f('fk_imap_append_attempts_job_id_campaign_jobs'), ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_imap_append_attempts'))
    )
    op.create_index(op.f('ix_imap_append_attempts_job_id'), 'imap_append_attempts', ['job_id'], unique=False)
    op.create_table('send_attempts',
    sa.Column('id', sa.String(length=36), nullable=False),
    sa.Column('job_id', sa.String(length=36), nullable=False),
    sa.Column('attempt_number', sa.Integer(), nullable=False),
    sa.Column('status', sa.String(length=50), nullable=False),
    sa.Column('claim_token', sa.String(length=36), nullable=True),
    sa.Column('smtp_status_code', sa.Integer(), nullable=True),
    sa.Column('smtp_response', sa.Text(), nullable=True),
    sa.Column('error_type', sa.String(length=255), nullable=True),
    sa.Column('error_message', sa.Text(), nullable=True),
    sa.Column('started_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('finished_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['job_id'], ['campaign_jobs.id'], name=op.f('fk_send_attempts_job_id_campaign_jobs'), ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_send_attempts'))
    )
    op.create_index(op.f('ix_send_attempts_claim_token'), 'send_attempts', ['claim_token'], unique=False)
    op.create_index(op.f('ix_send_attempts_job_id'), 'send_attempts', ['job_id'], unique=False)
    op.create_index(op.f('ix_send_attempts_status'), 'send_attempts', ['status'], unique=False)
    _seed_core_defaults()


def downgrade() -> None:
    op.drop_table('send_attempts')
    op.drop_table('imap_append_attempts')
    op.drop_table('campaign_issues')
    op.drop_table('campaign_attachment_uses')
    op.drop_table('campaign_jobs')
    op.drop_table('file_versions')
    op.drop_table('file_shares')
    op.drop_table('campaign_versions')
    op.drop_table('campaign_shares')
    op.drop_table('audit_log')
    op.drop_table('attachment_instances')
    op.drop_table('mail_server_profiles')
    op.drop_table('file_folders')
    op.drop_table('file_assets')
    op.drop_table('campaigns')
    op.drop_table('access_user_role_assignments')
    op.drop_table('access_user_group_memberships')
    op.drop_table('access_auth_sessions')
    op.drop_table('access_api_keys')
    op.drop_table('admin_governance_template_assignments')
    op.drop_table('access_users')
    op.drop_table('access_system_role_assignments')
    op.drop_table('access_group_role_assignments')
    op.drop_table('file_blobs')
    op.drop_table('core_system_settings')
    op.drop_table('core_change_sequence_retention_floor')
    op.drop_table('core_change_sequence')
    op.drop_table('audit_outbox_events')
    op.drop_table('attachment_blobs')
    op.drop_table('admin_governance_templates')
    op.drop_table('access_roles')
    op.drop_table('access_groups')
    op.drop_table('access_accounts')
    op.drop_table('core_scopes')
