"""Encryption keys table and attachment encryption_key_id

Revision ID: 012
Revises: 011
Create Date: 2026-01-05
"""
from alembic import op
import sqlalchemy as sa


# revision identifiers
revision = '012'
down_revision = '011'
branch_labels = None
depends_on = None


def upgrade():
    # Create encryption_keys table
    op.create_table(
        'pjctrl_encryption_keys',
        sa.Column('id', sa.String(36), primary_key=True),
        sa.Column('key_data', sa.Text, nullable=False),  # Encrypted key using Master Key
        sa.Column('algorithm', sa.String(20), default='AES-256-GCM', nullable=False),
        sa.Column('is_active', sa.Boolean, default=True, nullable=False),
        sa.Column('created_at', sa.DateTime, server_default=sa.func.now(), nullable=False),
        sa.Column('rotated_at', sa.DateTime, nullable=True),
    )
    op.create_index('idx_encryption_key_active', 'pjctrl_encryption_keys', ['is_active'])

    # Add encryption_key_id column to attachments table
    op.add_column(
        'pjctrl_attachments',
        sa.Column(
            'encryption_key_id',
            sa.String(36),
            sa.ForeignKey('pjctrl_encryption_keys.id', ondelete='SET NULL'),
            nullable=True
        )
    )
    op.create_index('idx_attachment_encryption_key', 'pjctrl_attachments', ['encryption_key_id'])


def downgrade():
    op.drop_index('idx_attachment_encryption_key', 'pjctrl_attachments')
    op.drop_column('pjctrl_attachments', 'encryption_key_id')
    op.drop_index('idx_encryption_key_active', 'pjctrl_encryption_keys')
    op.drop_table('pjctrl_encryption_keys')