"""Adding task_creation_mode column to project

Revision ID: 30b2ebaea3ff
Revises: d3ed291041a3
Create Date: 2017-06-23 08:42:23.898615

"""
from alembic import op
import sqlalchemy as sa
from server.models.postgis.project import Project
from server.models.postgis.task import Task
from server.models.postgis.statuses import TaskCreationMode

# revision identifiers, used by Alembic.
revision = '30b2ebaea3ff'
down_revision = 'd3ed291041a3'
branch_labels = None
depends_on = None

projects = Project.__table__
tasks = Task.__table__


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    conn = op.get_bind()

    op.add_column('projects',
                  sa.Column(
                      'task_creation_mode',
                      sa.Integer(),
                      nullable=False,
                      server_default=str(TaskCreationMode.GRID.value)))

    # Set the task creation mode
    # Projects with at least one task with not null zoom are considered gridded
    for project in conn.execute(projects.select()):
        zooms = conn.execute(
            sa.sql.expression.select([tasks.c.zoom]).distinct(tasks.c.zoom)
            .where(tasks.c.project_id == project.id))
        zooms = zooms.fetchall()

        if len(zooms) == 1 and zooms[0] == (None, ):
            op.execute(
                projects.update().where(projects.c.id == project.id)
                .values(task_creation_mode=TaskCreationMode.ARBITRARY.value))
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('projects', 'task_creation_mode')
    # ### end Alembic commands ###
