"""add contact info snapshots to contracts

Revision ID: add_contact_snapshots
Revises: add_name_snapshots
Create Date: 2025-12-08 00:00:00.000000

"""
from alembic import op
import sqlalchemy as sa


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


def upgrade():
    """Add contact and business info snapshot fields to contracts"""
    # Add contractor contact snapshots
    op.add_column('contracts', sa.Column('contractor_email', sa.String(120), nullable=True))
    op.add_column('contracts', sa.Column('contractor_phone', sa.String(20), nullable=True))
    op.add_column('contracts', sa.Column('contractor_abn', sa.String(11), nullable=True))
    op.add_column('contracts', sa.Column('contractor_business_name', sa.String(200), nullable=True))
    
    # Add worker contact snapshots
    op.add_column('contracts', sa.Column('worker_email', sa.String(120), nullable=True))
    op.add_column('contracts', sa.Column('worker_phone', sa.String(20), nullable=True))
    op.add_column('contracts', sa.Column('worker_abn', sa.String(11), nullable=True))
    op.add_column('contracts', sa.Column('worker_business_name', sa.String(200), nullable=True))
    
    # Backfill existing records with current user data
    op.execute("""
        UPDATE contracts 
        SET contractor_email = (SELECT u.email FROM users u WHERE u.id = contracts.contractor_id),
            contractor_phone = (SELECT u.phone_number FROM users u WHERE u.id = contracts.contractor_id),
            contractor_abn = (SELECT u.abn_number FROM users u WHERE u.id = contracts.contractor_id),
            contractor_business_name = (SELECT u.business_name FROM users u WHERE u.id = contracts.contractor_id)
        WHERE contractor_email IS NULL
    """)
    
    op.execute("""
        UPDATE contracts 
        SET worker_email = (SELECT u.email FROM users u WHERE u.id = contracts.worker_id),
            worker_phone = (SELECT u.phone_number FROM users u WHERE u.id = contracts.worker_id),
            worker_abn = (SELECT u.abn_number FROM users u WHERE u.id = contracts.worker_id),
            worker_business_name = (SELECT u.business_name FROM users u WHERE u.id = contracts.worker_id)
        WHERE worker_email IS NULL
    """)


def downgrade():
    """Remove contact snapshot fields"""
    op.drop_column('contracts', 'worker_business_name')
    op.drop_column('contracts', 'worker_abn')
    op.drop_column('contracts', 'worker_phone')
    op.drop_column('contracts', 'worker_email')
    op.drop_column('contracts', 'contractor_business_name')
    op.drop_column('contracts', 'contractor_abn')
    op.drop_column('contracts', 'contractor_phone')
    op.drop_column('contracts', 'contractor_email')