"""add name snapshots to contracts and messages

Revision ID: add_name_snapshots_to_contracts_messages
Revises: add_legal_hold_data_to_users
Create Date: 2025-12-08 00:00:00.000000

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'add_name_snapshots'
down_revision = 'add_legal_hold_data_to_users'
branch_labels = None
depends_on = None


def upgrade():
    """Add name snapshot fields to preserve user names in historical records"""
    # Add name snapshots to contracts
    op.add_column('contracts', sa.Column('contractor_name', sa.String(200), nullable=True))
    op.add_column('contracts', sa.Column('worker_name', sa.String(200), nullable=True))
    
    # Add name snapshot to messages
    op.add_column('messages', sa.Column('sender_name', sa.String(200), nullable=True))
    
    # Backfill existing records with current user names
    op.execute("""
        UPDATE contracts 
        SET contractor_name = (
            SELECT CONCAT(u.first_name, ' ', u.last_name) 
            FROM users u 
            WHERE u.id = contracts.contractor_id
        )
        WHERE contractor_name IS NULL
    """)
    
    op.execute("""
        UPDATE contracts 
        SET worker_name = (
            SELECT CONCAT(u.first_name, ' ', u.last_name) 
            FROM users u 
            WHERE u.id = contracts.worker_id
        )
        WHERE worker_name IS NULL
    """)
    
    op.execute("""
        UPDATE messages 
        SET sender_name = (
            SELECT CONCAT(u.first_name, ' ', u.last_name) 
            FROM users u 
            WHERE u.id = messages.sender_id
        )
        WHERE sender_name IS NULL
    """)


def downgrade():
    """Remove name snapshot fields"""
    op.drop_column('messages', 'sender_name')
    op.drop_column('contracts', 'worker_name')
    op.drop_column('contracts', 'contractor_name')