"""timezones

Revision ID: 5cfb8c86a9f1
Revises: 02ff3e6ff3a4
Create Date: 2019-08-08 13:51:01.765028

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

# revision identifiers, used by Alembic.
revision = '5cfb8c86a9f1'
down_revision = '02ff3e6ff3a4'
branch_labels = None
depends_on = None


def upgrade():
    conn = op.get_bind()
    conn.execute('''
      UPDATE "Activity"
         SET create_date = '2019-01-01 00:00:00'
       WHERE create_date IS NULL                              
    ''')
    conn.execute('''
      UPDATE "Activity"
         SET edit_date = '2019-01-01 00:00:00'
       WHERE edit_date IS NULL                              
    ''')

    op.alter_column('Activity', 'create_date',
               existing_type=postgresql.TIMESTAMP(),
               type_=sa.TIMESTAMP(timezone=True),
               server_default=sa.func.current_timestamp(),
               nullable=False)
    op.alter_column('Activity', 'create_user_id',
               existing_type=sa.VARCHAR(),
               nullable=False)
    op.alter_column('Activity', 'edit_date',
               existing_type=postgresql.TIMESTAMP(),
               type_=sa.TIMESTAMP(timezone=True),
               server_default=sa.func.current_timestamp(),
               nullable=False)
    op.alter_column('Activity', 'end_date',
               existing_type=postgresql.TIMESTAMP(),
               type_=sa.TIMESTAMP(timezone=True),
               existing_nullable=False)
    op.alter_column('Activity', 'start_date',
               existing_type=postgresql.TIMESTAMP(),
               type_=sa.TIMESTAMP(timezone=True),
               existing_nullable=False)
    op.alter_column('Campaign', 'create_date',
               existing_type=postgresql.TIMESTAMP(),
               type_=sa.TIMESTAMP(timezone=True),
               server_default=sa.func.current_timestamp(),
               existing_nullable=False)
    op.alter_column('Campaign', 'earliest_start_date',
               existing_type=postgresql.TIMESTAMP(),
               type_=sa.TIMESTAMP(timezone=True),
               existing_nullable=True)
    op.alter_column('Campaign', 'edit_date',
               existing_type=postgresql.TIMESTAMP(),
               type_=sa.TIMESTAMP(timezone=True),
               server_default=sa.func.current_timestamp(),
               existing_nullable=False)
    op.alter_column('Campaign', 'latest_end_date',
               existing_type=postgresql.TIMESTAMP(),
               type_=sa.TIMESTAMP(timezone=True),
               existing_nullable=True)
    op.alter_column('CampaignHistory', 'edit_date',
               existing_type=postgresql.TIMESTAMP(),
               type_=sa.TIMESTAMP(timezone=True),
               server_default=sa.func.current_timestamp(),
               existing_nullable=False)
    op.alter_column('HistoryUser', 'edit_date',
               existing_type=postgresql.TIMESTAMP(),
               type_=sa.TIMESTAMP(timezone=True),
               server_default=sa.func.current_timestamp(),
               existing_nullable=False)

    conn.execute('''
      UPDATE "Activity"
         SET start_date = (start_date AT TIME ZONE 'UTC')::TIMESTAMP AT TIME ZONE 'America/New_York',
             end_date = (end_date AT TIME ZONE 'UTC')::TIMESTAMP AT TIME ZONE 'America/New_York'           
    ''')
    conn.execute('''
      UPDATE "Campaign"
         SET earliest_start_date = (earliest_start_date AT TIME ZONE 'UTC')::TIMESTAMP AT TIME ZONE 'America/New_York',
             latest_end_date = (latest_end_date AT TIME ZONE 'UTC')::TIMESTAMP AT TIME ZONE 'America/New_York'          
    ''')


def downgrade():
    op.alter_column('HistoryUser', 'edit_date',
               existing_type=sa.TIMESTAMP(timezone=True),
               type_=postgresql.TIMESTAMP(),
               server_default=None,
               existing_nullable=False)
    op.alter_column('CampaignHistory', 'edit_date',
               existing_type=sa.TIMESTAMP(timezone=True),
               type_=postgresql.TIMESTAMP(),
               server_default=None,
               existing_nullable=False)
    op.alter_column('Campaign', 'latest_end_date',
               existing_type=sa.TIMESTAMP(timezone=True),
               type_=postgresql.TIMESTAMP(),
               existing_nullable=True)
    op.alter_column('Campaign', 'edit_date',
               existing_type=sa.TIMESTAMP(timezone=True),
               type_=postgresql.TIMESTAMP(),
               server_default=None,
               existing_nullable=False)
    op.alter_column('Campaign', 'earliest_start_date',
               existing_type=sa.TIMESTAMP(timezone=True),
               type_=postgresql.TIMESTAMP(),
               existing_nullable=True)
    op.alter_column('Campaign', 'create_date',
               existing_type=sa.TIMESTAMP(timezone=True),
               type_=postgresql.TIMESTAMP(),
               server_default=None,
               existing_nullable=False)
    op.alter_column('Activity', 'start_date',
               existing_type=sa.TIMESTAMP(timezone=True),
               type_=postgresql.TIMESTAMP(),
               existing_nullable=False)
    op.alter_column('Activity', 'end_date',
               existing_type=sa.TIMESTAMP(timezone=True),
               type_=postgresql.TIMESTAMP(),
               existing_nullable=False)
    op.alter_column('Activity', 'edit_date',
               existing_type=sa.TIMESTAMP(timezone=True),
               type_=postgresql.TIMESTAMP(),
               server_default=None,
               nullable=True)
    op.alter_column('Activity', 'create_user_id',
               existing_type=sa.VARCHAR(),
               nullable=True)
    op.alter_column('Activity', 'create_date',
               existing_type=sa.TIMESTAMP(timezone=True),
               type_=postgresql.TIMESTAMP(),
               server_default=None,
               nullable=True)