"""rename s3details to app_config with encryption

Revision ID: c7890abc1234
Revises: b475dfc51aee
Create Date: 2025-01-15 12:00:00.000000

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = 'c7890abc1234'
down_revision: Union[str, None] = 'b475dfc51aee'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """
    Rename S3details table to app_config
    Migrate existing data and encrypt sensitive fields
    """
    # Check if S3details table exists
    conn = op.get_bind()
    inspector = sa.inspect(conn)
    tables = inspector.get_table_names()
    
    if 'S3details' in tables:
        # Get existing data before renaming
        existing_data = []
        try:
            result = conn.execute(sa.text("SELECT id, org_id, access_key, secret_access_key, bucket, region FROM S3details"))
            existing_data = result.fetchall()
        except Exception as e:
            # If query fails, continue with table rename
            pass
        
        # Rename table
        op.rename_table('S3details', 'app_config')
        
        # Add timestamp column if it doesn't exist (for backward compatibility)
        try:
            # Check if timestamp column exists
            columns = [col['name'] for col in inspector.get_columns('app_config')]
            if 'timestamp' not in columns:
                op.add_column('app_config', sa.Column('timestamp', sa.DateTime(), nullable=True))
        except Exception as e:
            # Column might already exist, continue
            pass
        
        # Encrypt existing plaintext data
        if existing_data:
            try:
                from ws_package.utils.encryption import get_encryption_util
                encryption_util = get_encryption_util()
                
                for row in existing_data:
                    row_id, org_id, access_key, secret_key, bucket, region = row
                    
                    # Encrypt fields that are not already encrypted (don't contain '$')
                    updates = {}
                    if access_key and '$' not in str(access_key):
                        updates['access_key'] = encryption_util.encrypt(str(access_key))
                    if secret_key and '$' not in str(secret_key):
                        updates['secret_access_key'] = encryption_util.encrypt(str(secret_key))
                    if bucket and '$' not in str(bucket):
                        updates['bucket'] = encryption_util.encrypt(str(bucket))
                    if region and '$' not in str(region):
                        updates['region'] = encryption_util.encrypt(str(region))
                    
                    # Update row with encrypted values
                    if updates:
                        set_clause = ', '.join([f"{k} = :{k}" for k in updates.keys()])
                        updates['id'] = row_id
                        conn.execute(
                            sa.text(f"UPDATE app_config SET {set_clause} WHERE id = :id"),
                            updates
                        )
                
                conn.commit()
            except Exception as e:
                # If encryption fails, log but continue
                # Data will be encrypted on next update via model event handlers
                import logging
                logging.warning(f"Could not encrypt existing data during migration: {e}")
        
    elif 'app_config' not in tables:
        # Table doesn't exist, create it fresh
        op.create_table('app_config',
            sa.Column('id', sa.Integer(), nullable=False),
            sa.Column('org_id', sa.Integer(), nullable=True),
            sa.Column('access_key', sa.String(), nullable=True),
            sa.Column('secret_access_key', sa.String(), nullable=True),
            sa.Column('bucket', sa.String(), nullable=True),
            sa.Column('region', sa.String(), nullable=True),
            sa.Column('timestamp', sa.DateTime(), nullable=True),
            sa.PrimaryKeyConstraint('id')
        )


def downgrade() -> None:
    """
    Revert app_config table back to S3details
    Note: This will NOT decrypt data - data remains encrypted
    """
    conn = op.get_bind()
    inspector = sa.inspect(conn)
    tables = inspector.get_table_names()
    
    if 'app_config' in tables:
        # Rename table back
        op.rename_table('app_config', 'S3details')
        
        # Remove timestamp column if it was added in upgrade
        try:
            columns = [col['name'] for col in inspector.get_columns('S3details')]
            if 'timestamp' in columns:
                op.drop_column('S3details', 'timestamp')
        except Exception as e:
            # Column might not exist, continue
            pass

