320dbbdd0b27_add_last_kilometer_for_vehicle.py

"""Add last kilometer for Vehicle

Revision ID: 320dbbdd0b27
Revises: bc7abe3eb83c
Create Date: 2022-06-09 10:19:59.480830

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = "320dbbdd0b27"
down_revision = "bc7abe3eb83c"
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column(
        "vehicle",
        sa.Column("last_kilometer_reading", sa.Integer(), nullable=True),
    )
    # ### end Alembic commands ###
    op.execute("create index ix_mission_vehicle_id on mission (vehicle_id)")
    op.execute(
        """update vehicle v
                  set last_kilometer_reading = (select max(kilometer_reading)
                                                from location_entry l, mission m
                                                where m.vehicle_id = v.id
                                                    and m.id = l.mission_id
                                                    and l.type = 'mission_end_location')
                """
    )
    op.execute("drop index ix_mission_vehicle_id")


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column("vehicle", "last_kilometer_reading")
    # ### end Alembic commands ###