"""add last_update to issues and pull-requests

Revision ID: 114d3a68c1fd
Revises: 5083efccac7
Create Date: 2016-11-15 11:02:30.652540

"""

# revision identifiers, used by Alembic.
revision = '114d3a68c1fd'
down_revision = '5083efccac7'

from alembic import op
import sqlalchemy as sa
import datetime


def upgrade():
    ''' Add the column last_updated to the table issues/pull-requests
    '''
    op.add_column(
        'issues',
        sa.Column('last_updated', sa.DateTime, nullable=True,
            default=datetime.datetime.utcnow,
            onupdate=datetime.datetime.utcnow)
    )
    # Update all the tickets having comments
    op.execute('''
UPDATE "issues" SET last_updated=o_date
FROM (
    SELECT issue_uid, GREATEST(date_created, edited_on) AS o_date
    FROM issue_comments
    ORDER BY o_date DESC
) AS subq
WHERE "issues".uid = issue_uid;''')
    # Update all the tickets without comments
    op.execute('''UPDATE "issues" SET last_updated=date_created '''
               '''WHERE last_updated IS NULL;''')
    # Require `last_updated` no NULL at the DB level
    op.alter_column(
        'issues', 'last_updated',
        nullable=False, existing_nullable=True)

    op.add_column(
        'pull_requests',
        sa.Column('last_updated', sa.DateTime, nullable=True,
            default=datetime.datetime.utcnow,
            onupdate=datetime.datetime.utcnow)
    )
    # Update all the PRs having comments
    op.execute('''
UPDATE "pull_requests" SET last_updated=o_date
FROM (
    SELECT pull_request_uid, GREATEST(date_created, edited_on) AS o_date
    FROM pull_request_comments
    ORDER BY o_date DESC
) AS subq
WHERE "pull_requests".uid = pull_request_uid;''')
    # Update all the PRs without comments
    op.execute('''UPDATE "pull_requests" SET last_updated=date_created '''
               '''WHERE last_updated IS NULL;''')
    # Require `last_updated` no NULL at the DB level
    op.alter_column(
        'pull_requests', 'last_updated',
        nullable=False, existing_nullable=True)


def downgrade():
    ''' Drop the column last_update from the table issues/pull-requests
    '''
    op.drop_column('issues', 'last_updated')
    op.drop_column('pull_requests', 'last_updated')