"""phase 1 foundation

Revision ID: e579fe82c6a7
Revises: 
"""
from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects import postgresql

revision: str = 'e579fe82c6a7'
down_revision: str | Sequence[str] | None = None
branch_labels = None
depends_on = None

def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    installation_state = op.create_table('installation_state',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('owner_setup_completed', sa.Boolean(), nullable=False),
    sa.Column('completed_at', sa.DateTime(timezone=True), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.bulk_insert(installation_state, [{
        'id': 1,
        'owner_setup_completed': False,
        'completed_at': None,
    }])
    op.create_table('permissions',
    sa.Column('key', sa.String(length=120), nullable=False),
    sa.Column('description', sa.String(length=255), nullable=False),
    sa.PrimaryKeyConstraint('key')
    )
    op.create_table('users',
    sa.Column('id', sa.Uuid(), nullable=False),
    sa.Column('email', sa.String(length=320), nullable=False),
    sa.Column('password_hash', sa.String(length=255), nullable=False),
    sa.Column('display_name', sa.String(length=160), nullable=False),
    sa.Column('is_active', sa.Boolean(), 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.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('email')
    )
    op.create_table('workspaces',
    sa.Column('id', sa.Uuid(), nullable=False),
    sa.Column('name', sa.String(length=160), nullable=False),
    sa.Column('timezone', sa.String(length=80), nullable=False),
    sa.Column('audit_sequence', sa.Integer(), nullable=False),
    sa.Column('audit_head_hash', sa.LargeBinary(length=32), 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('audit_sequence >= 0', name='ck_workspace_audit_sequence'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('audit_events',
    sa.Column('id', sa.Uuid(), nullable=False),
    sa.Column('workspace_id', sa.Uuid(), nullable=False),
    sa.Column('sequence', sa.Integer(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
    sa.Column('event_type', sa.String(length=120), nullable=False),
    sa.Column('actor_type', sa.String(length=32), nullable=False),
    sa.Column('actor_id', sa.Uuid(), nullable=False),
    sa.Column('request_id', sa.String(length=80), nullable=True),
    sa.Column('execution_id', sa.Uuid(), nullable=True),
    sa.Column('data', sa.JSON().with_variant(postgresql.JSONB(astext_type=sa.Text()), 'postgresql'), nullable=False),
    sa.Column('previous_hash', sa.LargeBinary(length=32), nullable=True),
    sa.Column('event_hash', sa.LargeBinary(length=32), nullable=False),
    sa.CheckConstraint('(sequence = 1 AND previous_hash IS NULL) OR (sequence > 1 AND previous_hash IS NOT NULL)', name='ck_audit_predecessor'),
    sa.CheckConstraint('sequence > 0', name='ck_audit_sequence'),
    sa.ForeignKeyConstraint(['workspace_id'], ['workspaces.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('event_hash'),
    sa.UniqueConstraint('workspace_id', 'sequence', name='uq_audit_workspace_sequence')
    )
    op.create_index(op.f('ix_audit_events_workspace_id'), 'audit_events', ['workspace_id'], unique=False)
    op.create_index('ix_audit_workspace_created', 'audit_events', ['workspace_id', 'created_at'], unique=False)
    op.create_table('idempotency_records',
    sa.Column('id', sa.Uuid(), nullable=False),
    sa.Column('workspace_id', sa.Uuid(), nullable=False),
    sa.Column('key', sa.String(length=180), nullable=False),
    sa.Column('request_hash', sa.String(length=64), nullable=False),
    sa.Column('status', sa.String(length=24), nullable=False),
    sa.Column('response_status', sa.Integer(), nullable=True),
    sa.Column('response_body', sa.JSON().with_variant(postgresql.JSONB(astext_type=sa.Text()), 'postgresql'), nullable=True),
    sa.Column('expires_at', sa.DateTime(timezone=True), nullable=False),
    sa.CheckConstraint("status IN ('in_progress','completed','failed','unknown')", name='ck_idempotency_status'),
    sa.ForeignKeyConstraint(['workspace_id'], ['workspaces.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_idempotency_records_workspace_id'), 'idempotency_records', ['workspace_id'], unique=False)
    op.create_index('uq_idempotency_workspace_key', 'idempotency_records', ['workspace_id', 'key'], unique=True)
    op.create_table('login_events',
    sa.Column('id', sa.Uuid(), nullable=False),
    sa.Column('workspace_id', sa.Uuid(), nullable=True),
    sa.Column('user_id', sa.Uuid(), nullable=True),
    sa.Column('email_hash', sa.String(length=64), nullable=False),
    sa.Column('successful', sa.Boolean(), nullable=False),
    sa.Column('reason', sa.String(length=80), nullable=False),
    sa.Column('ip_hash', sa.String(length=64), nullable=True),
    sa.Column('user_agent', sa.String(length=500), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
    sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
    sa.ForeignKeyConstraint(['workspace_id'], ['workspaces.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_login_events_user_id'), 'login_events', ['user_id'], unique=False)
    op.create_index(op.f('ix_login_events_workspace_id'), 'login_events', ['workspace_id'], unique=False)
    op.create_index('ix_login_workspace_created', 'login_events', ['workspace_id', 'created_at'], unique=False)
    op.create_table('outbox_events',
    sa.Column('id', sa.Uuid(), nullable=False),
    sa.Column('workspace_id', sa.Uuid(), nullable=False),
    sa.Column('topic', sa.String(length=160), nullable=False),
    sa.Column('schema_version', sa.Integer(), nullable=False),
    sa.Column('payload', sa.JSON().with_variant(postgresql.JSONB(astext_type=sa.Text()), 'postgresql'), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
    sa.Column('published_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('attempts', sa.Integer(), nullable=False),
    sa.Column('last_error', sa.Text(), nullable=True),
    sa.CheckConstraint('attempts >= 0', name='ck_outbox_attempts'),
    sa.CheckConstraint('schema_version > 0', name='ck_outbox_schema_version'),
    sa.ForeignKeyConstraint(['workspace_id'], ['workspaces.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_outbox_events_workspace_id'), 'outbox_events', ['workspace_id'], unique=False)
    op.create_index('ix_outbox_unpublished', 'outbox_events', ['published_at', 'created_at'], unique=False)
    op.create_table('roles',
    sa.Column('id', sa.Uuid(), nullable=False),
    sa.Column('workspace_id', sa.Uuid(), nullable=False),
    sa.Column('name', sa.String(length=80), nullable=False),
    sa.Column('is_system', sa.Boolean(), 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.ForeignKeyConstraint(['workspace_id'], ['workspaces.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('workspace_id', 'id', name='uq_role_workspace_id'),
    sa.UniqueConstraint('workspace_id', 'name', name='uq_role_workspace_name')
    )
    op.create_index(op.f('ix_roles_workspace_id'), 'roles', ['workspace_id'], unique=False)
    op.create_table('workspace_memberships',
    sa.Column('id', sa.Uuid(), nullable=False),
    sa.Column('workspace_id', sa.Uuid(), nullable=False),
    sa.Column('user_id', sa.Uuid(), nullable=False),
    sa.Column('status', sa.String(length=32), 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 IN ('invited','active','suspended','revoked')", name='ck_membership_status'),
    sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
    sa.ForeignKeyConstraint(['workspace_id'], ['workspaces.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('workspace_id', 'id', name='uq_membership_workspace_id'),
    sa.UniqueConstraint('workspace_id', 'user_id', name='uq_membership_workspace_user')
    )
    op.create_index(op.f('ix_workspace_memberships_user_id'), 'workspace_memberships', ['user_id'], unique=False)
    op.create_index(op.f('ix_workspace_memberships_workspace_id'), 'workspace_memberships', ['workspace_id'], unique=False)
    op.create_table('approval_requests',
    sa.Column('id', sa.Uuid(), nullable=False),
    sa.Column('workspace_id', sa.Uuid(), nullable=False),
    sa.Column('action_id', sa.Uuid(), nullable=False),
    sa.Column('requested_by_type', sa.String(length=32), nullable=False),
    sa.Column('requested_by_id', sa.Uuid(), nullable=False),
    sa.Column('permission', sa.String(length=120), nullable=False),
    sa.Column('risk', sa.String(length=16), nullable=False),
    sa.Column('canonical_payload_hash', sa.String(length=64), nullable=False),
    sa.Column('payload', sa.JSON().with_variant(postgresql.JSONB(astext_type=sa.Text()), 'postgresql'), nullable=False),
    sa.Column('status', sa.String(length=24), nullable=False),
    sa.Column('expires_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('decided_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('decided_by_user_id', sa.Uuid(), 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("(status = 'pending' AND decided_at IS NULL AND decided_by_user_id IS NULL) OR (status IN ('approved','rejected') AND decided_at IS NOT NULL AND decided_by_user_id IS NOT NULL) OR (status = 'expired' AND decided_at IS NOT NULL)", name='ck_approval_decision_state'),
    sa.CheckConstraint("risk IN ('low','medium','high')", name='ck_approval_risk'),
    sa.CheckConstraint("status IN ('pending','approved','rejected','expired')", name='ck_approval_status'),
    sa.ForeignKeyConstraint(['permission'], ['permissions.key'], ),
    sa.ForeignKeyConstraint(['workspace_id', 'decided_by_user_id'], ['workspace_memberships.workspace_id', 'workspace_memberships.user_id'], name='fk_approval_decider_membership'),
    sa.ForeignKeyConstraint(['workspace_id'], ['workspaces.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_approval_requests_workspace_id'), 'approval_requests', ['workspace_id'], unique=False)
    op.create_index('ix_approval_workspace_status', 'approval_requests', ['workspace_id', 'status'], unique=False)
    op.create_index('uq_approval_workspace_action', 'approval_requests', ['workspace_id', 'action_id'], unique=True)
    op.create_table('device_sessions',
    sa.Column('id', sa.Uuid(), nullable=False),
    sa.Column('workspace_id', sa.Uuid(), nullable=False),
    sa.Column('user_id', sa.Uuid(), nullable=False),
    sa.Column('token_hash', sa.String(length=64), nullable=False),
    sa.Column('csrf_hash', sa.String(length=64), nullable=False),
    sa.Column('device_name', sa.String(length=160), nullable=True),
    sa.Column('user_agent', sa.String(length=500), nullable=True),
    sa.Column('ip_hash', sa.String(length=64), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
    sa.Column('last_seen_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
    sa.Column('expires_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('revoked_at', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['workspace_id', 'user_id'], ['workspace_memberships.workspace_id', 'workspace_memberships.user_id'], name='fk_session_membership_identity'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('token_hash')
    )
    op.create_index(op.f('ix_device_sessions_user_id'), 'device_sessions', ['user_id'], unique=False)
    op.create_index(op.f('ix_device_sessions_workspace_id'), 'device_sessions', ['workspace_id'], unique=False)
    op.create_index('ix_session_workspace_user', 'device_sessions', ['workspace_id', 'user_id'], unique=False)
    op.create_table('membership_roles',
    sa.Column('workspace_id', sa.Uuid(), nullable=False),
    sa.Column('membership_id', sa.Uuid(), nullable=False),
    sa.Column('role_id', sa.Uuid(), nullable=False),
    sa.ForeignKeyConstraint(['workspace_id', 'membership_id'], ['workspace_memberships.workspace_id', 'workspace_memberships.id'], name='fk_membership_role_workspace_membership'),
    sa.ForeignKeyConstraint(['workspace_id', 'role_id'], ['roles.workspace_id', 'roles.id'], name='fk_membership_role_workspace_role'),
    sa.PrimaryKeyConstraint('workspace_id', 'membership_id', 'role_id')
    )
    op.create_table('password_reset_tokens',
    sa.Column('id', sa.Uuid(), nullable=False),
    sa.Column('workspace_id', sa.Uuid(), nullable=False),
    sa.Column('user_id', sa.Uuid(), nullable=False),
    sa.Column('token_hash', sa.String(length=64), nullable=False),
    sa.Column('expires_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('used_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
    sa.ForeignKeyConstraint(['workspace_id', 'user_id'], ['workspace_memberships.workspace_id', 'workspace_memberships.user_id'], name='fk_reset_membership_identity'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('token_hash')
    )
    op.create_index(op.f('ix_password_reset_tokens_user_id'), 'password_reset_tokens', ['user_id'], unique=False)
    op.create_index(op.f('ix_password_reset_tokens_workspace_id'), 'password_reset_tokens', ['workspace_id'], unique=False)
    op.create_table('role_permissions',
    sa.Column('workspace_id', sa.Uuid(), nullable=False),
    sa.Column('role_id', sa.Uuid(), nullable=False),
    sa.Column('permission_key', sa.String(length=120), nullable=False),
    sa.ForeignKeyConstraint(['permission_key'], ['permissions.key'], ),
    sa.ForeignKeyConstraint(['workspace_id', 'role_id'], ['roles.workspace_id', 'roles.id'], name='fk_role_permission_workspace_role'),
    sa.PrimaryKeyConstraint('workspace_id', 'role_id', 'permission_key')
    )
    dialect = op.get_bind().dialect.name
    if dialect == "postgresql":
        op.execute("""
            CREATE FUNCTION hayva_reject_append_only_mutation() RETURNS trigger AS $$
            BEGIN
                RAISE EXCEPTION '% is append-only', TG_TABLE_NAME;
            END;
            $$ LANGUAGE plpgsql
        """)
        op.execute("""
            CREATE TRIGGER audit_events_append_only
            BEFORE UPDATE OR DELETE ON audit_events
            FOR EACH ROW EXECUTE FUNCTION hayva_reject_append_only_mutation()
        """)
        op.execute("""
            CREATE TRIGGER login_events_append_only
            BEFORE UPDATE OR DELETE ON login_events
            FOR EACH ROW EXECUTE FUNCTION hayva_reject_append_only_mutation()
        """)
    elif dialect == "sqlite":
        op.execute("""
            CREATE TRIGGER audit_events_no_update BEFORE UPDATE ON audit_events
            BEGIN SELECT RAISE(ABORT, 'audit_events are append-only'); END
        """)
        op.execute("""
            CREATE TRIGGER audit_events_no_delete BEFORE DELETE ON audit_events
            BEGIN SELECT RAISE(ABORT, 'audit_events are append-only'); END
        """)
        op.execute("""
            CREATE TRIGGER login_events_no_update BEFORE UPDATE ON login_events
            BEGIN SELECT RAISE(ABORT, 'login_events are append-only'); END
        """)
        op.execute("""
            CREATE TRIGGER login_events_no_delete BEFORE DELETE ON login_events
            BEGIN SELECT RAISE(ABORT, 'login_events are append-only'); END
        """)
    # ### end Alembic commands ###

def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    dialect = op.get_bind().dialect.name
    if dialect == "postgresql":
        op.execute("DROP TRIGGER IF EXISTS audit_events_append_only ON audit_events")
        op.execute("DROP TRIGGER IF EXISTS login_events_append_only ON login_events")
        op.execute("DROP FUNCTION IF EXISTS hayva_reject_append_only_mutation()")
    elif dialect == "sqlite":
        op.execute("DROP TRIGGER IF EXISTS audit_events_no_update")
        op.execute("DROP TRIGGER IF EXISTS audit_events_no_delete")
        op.execute("DROP TRIGGER IF EXISTS login_events_no_update")
        op.execute("DROP TRIGGER IF EXISTS login_events_no_delete")
    op.drop_table('role_permissions')
    op.drop_index(op.f('ix_password_reset_tokens_workspace_id'), table_name='password_reset_tokens')
    op.drop_index(op.f('ix_password_reset_tokens_user_id'), table_name='password_reset_tokens')
    op.drop_table('password_reset_tokens')
    op.drop_table('membership_roles')
    op.drop_index('ix_session_workspace_user', table_name='device_sessions')
    op.drop_index(op.f('ix_device_sessions_workspace_id'), table_name='device_sessions')
    op.drop_index(op.f('ix_device_sessions_user_id'), table_name='device_sessions')
    op.drop_table('device_sessions')
    op.drop_index('uq_approval_workspace_action', table_name='approval_requests')
    op.drop_index('ix_approval_workspace_status', table_name='approval_requests')
    op.drop_index(op.f('ix_approval_requests_workspace_id'), table_name='approval_requests')
    op.drop_table('approval_requests')
    op.drop_index(op.f('ix_workspace_memberships_workspace_id'), table_name='workspace_memberships')
    op.drop_index(op.f('ix_workspace_memberships_user_id'), table_name='workspace_memberships')
    op.drop_table('workspace_memberships')
    op.drop_index(op.f('ix_roles_workspace_id'), table_name='roles')
    op.drop_table('roles')
    op.drop_index('ix_outbox_unpublished', table_name='outbox_events')
    op.drop_index(op.f('ix_outbox_events_workspace_id'), table_name='outbox_events')
    op.drop_table('outbox_events')
    op.drop_index('ix_login_workspace_created', table_name='login_events')
    op.drop_index(op.f('ix_login_events_workspace_id'), table_name='login_events')
    op.drop_index(op.f('ix_login_events_user_id'), table_name='login_events')
    op.drop_table('login_events')
    op.drop_index('uq_idempotency_workspace_key', table_name='idempotency_records')
    op.drop_index(op.f('ix_idempotency_records_workspace_id'), table_name='idempotency_records')
    op.drop_table('idempotency_records')
    op.drop_index('ix_audit_workspace_created', table_name='audit_events')
    op.drop_index(op.f('ix_audit_events_workspace_id'), table_name='audit_events')
    op.drop_table('audit_events')
    op.drop_table('workspaces')
    op.drop_table('users')
    op.drop_table('permissions')
    op.drop_table('installation_state')
    # ### end Alembic commands ###