shopdb-flask/migrations/versions/7d33_buildings_and_levels.py

"""Buildings and levels: a map is a drawing per level, not one image per site.

See ADR-017. `map_blueprint_light`, `map_blueprint_dark`, `map_width` and
`map_height` described one image for the whole site, and `assets.mapx`/`mapy`
were pixels in it. A second level and a likely second building make that a
table.

This migration is written so nothing renders differently the day it lands: the
four settings become one building and one level, marked default, and every asset
that has a position points at it. The settings rows are left in place here and
retired separately, so a rollback does not lose the blueprint paths.

`levelid` is nullable because an asset with no position needs no level. A
position WITHOUT a level is the case the UI refuses to guess about, and after
this migration no such row exists.

Revision ID: 7d33_buildings_and_levels
Revises: 7d32_displayrole_kiosk_vocabulary
"""
from alembic import op
import sqlalchemy as sa


revision = '7d33_buildings_and_levels'
down_revision = '7d32_displayrole_kiosk_vocabulary'
branch_labels = None
depends_on = None

# What the settings said before this table existed. Read at upgrade time; these
# are only the fallbacks for a site that never set them.
DEFAULT_WIDTH = 3300
DEFAULT_HEIGHT = 2550
PLACEHOLDER = '/static/images/floorplan-placeholder.svg'


def _existing(insp, name):
    return name in insp.get_table_names()


def upgrade():
    bind = op.get_bind()
    insp = sa.inspect(bind)

    # Guarded like every other table-creating migration in this project: on a
    # FRESH database the tables are built from the SQLAlchemy models, which
    # already declare them, so an unconditional create fails.
    if not _existing(insp, 'buildings'):
        op.create_table(
            'buildings',
            sa.Column('buildingid', sa.Integer, primary_key=True),
            sa.Column('buildingname', sa.String(100), nullable=False,
                      unique=True),
            sa.Column('sortorder', sa.Integer, nullable=False,
                      server_default='0'),
            sa.Column('createddate', sa.DateTime, nullable=False),
            sa.Column('modifieddate', sa.DateTime, nullable=False),
            sa.Column('isactive', sa.Boolean, nullable=False,
                      server_default=sa.true()),
        )

    if not _existing(insp, 'maplevels'):
        op.create_table(
            'maplevels',
            sa.Column('levelid', sa.Integer, primary_key=True),
            sa.Column('buildingid', sa.Integer,
                      sa.ForeignKey('buildings.buildingid'), nullable=False,
                      index=True),
            sa.Column('levelname', sa.String(100), nullable=False),
            sa.Column('sortorder', sa.Integer, nullable=False,
                      server_default='0'),
            sa.Column('blueprintlight', sa.String(255), nullable=True),
            sa.Column('blueprintdark', sa.String(255), nullable=True),
            sa.Column('mapwidth', sa.Integer, nullable=False,
                      server_default=str(DEFAULT_WIDTH)),
            sa.Column('mapheight', sa.Integer, nullable=False,
                      server_default=str(DEFAULT_HEIGHT)),
            sa.Column('isdefault', sa.Boolean, nullable=False,
                      server_default=sa.false()),
            sa.Column('createddate', sa.DateTime, nullable=False),
            sa.Column('modifieddate', sa.DateTime, nullable=False),
            sa.Column('isactive', sa.Boolean, nullable=False,
                      server_default=sa.true()),
            sa.UniqueConstraint('buildingid', 'levelname',
                                name='uq_maplevel_building_name'),
        )

    # Positions have no history, and a bulk transform rewrites hundreds of them
    # at once. A snapshot table is what makes that reversible - without it, the
    # honest advice would be "back up the database first", which nobody does
    # before a UI action.
    if not _existing(insp, 'mappositionsnapshots'):
        op.create_table(
            'mappositionsnapshots',
            sa.Column('snapshotid', sa.Integer, primary_key=True),
            sa.Column('levelid', sa.Integer, nullable=True),
            sa.Column('reason', sa.String(255), nullable=True),
            sa.Column('assetcount', sa.Integer, nullable=False,
                      server_default='0'),
            # The positions themselves, as JSON: assetid, mapx, mapy, levelid,
            # mapverifiedat per row. Deliberately not a child table - a snapshot
            # is read back whole or not at all, and one row per snapshot keeps
            # restore a single statement.
            sa.Column('positionsjson', sa.Text, nullable=False),
            sa.Column('restoredat', sa.DateTime, nullable=True),
            sa.Column('createdby', sa.String(100), nullable=True),
            sa.Column('createddate', sa.DateTime, nullable=False),
            sa.Column('modifieddate', sa.DateTime, nullable=False),
            sa.Column('isactive', sa.Boolean, nullable=False,
                      server_default=sa.true()),
        )

    assetcolumns = {c['name'] for c in insp.get_columns('assets')}
    if 'levelid' not in assetcolumns:
        op.add_column('assets', sa.Column('levelid', sa.Integer, nullable=True))
        op.create_index('idx_assets_levelid', 'assets', ['levelid'])
        # The FK is added separately from the column so a site whose assets
        # table is large is not rewritten twice.
        op.create_foreign_key('fk_assets_levelid', 'assets', 'maplevels',
                              ['levelid'], ['levelid'])
    if 'mapverifiedat' not in assetcolumns:
        op.add_column('assets',
                      sa.Column('mapverifiedat', sa.DateTime, nullable=True))

    # Locations carry map coordinates too - they are the default position for
    # assets at that location - so they need a level for exactly the same
    # reason. Missed on the first pass and caught by the payload gate.
    locationcolumns = {c['name'] for c in insp.get_columns('locations')}
    if 'levelid' not in locationcolumns:
        op.add_column('locations', sa.Column('levelid', sa.Integer, nullable=True))
        op.create_index('idx_locations_levelid', 'locations', ['levelid'])
        op.create_foreign_key('fk_locations_levelid', 'locations', 'maplevels',
                              ['levelid'], ['levelid'])

    # --- carry the settings forward -------------------------------------
    # Only when there is nothing here yet: re-running must not create a second
    # default level, and a site that has already set its levels up must not have
    # them joined by a stale one built from retired settings.
    existinglevels = bind.execute(
        sa.text('SELECT COUNT(*) FROM maplevels')).scalar() or 0
    if existinglevels:
        return

    settings = dict(bind.execute(sa.text(
        "SELECT `key`, value FROM settings WHERE `key` IN "
        "('map_blueprint_light','map_blueprint_dark','map_width','map_height')"
    )).fetchall())

    def _int(value, fallback):
        try:
            number = int(str(value).strip())
            return number if number > 0 else fallback
        except (TypeError, ValueError):
            return fallback

    bind.execute(sa.text(
        'INSERT INTO buildings (buildingname, sortorder, createddate, '
        'modifieddate, isactive) VALUES (:name, 0, :now, :now, :active)'),
        {'name': 'Main', 'now': sa.func.now(), 'active': True})
    buildingid = bind.execute(sa.text(
        'SELECT buildingid FROM buildings WHERE buildingname = :name'),
        {'name': 'Main'}).scalar()

    bind.execute(sa.text(
        'INSERT INTO maplevels (buildingid, levelname, sortorder, '
        'blueprintlight, blueprintdark, mapwidth, mapheight, isdefault, '
        'createddate, modifieddate, isactive) VALUES (:building, :name, 0, '
        ':light, :dark, :width, :height, :isdefault, :now, :now, :active)'),
        {'building': buildingid,
         'name': 'Ground floor',
         'light': settings.get('map_blueprint_light') or PLACEHOLDER,
         'dark': settings.get('map_blueprint_dark') or PLACEHOLDER,
         'width': _int(settings.get('map_width'), DEFAULT_WIDTH),
         'height': _int(settings.get('map_height'), DEFAULT_HEIGHT),
         'isdefault': True, 'now': sa.func.now(), 'active': True})

    levelid = bind.execute(sa.text(
        'SELECT levelid FROM maplevels WHERE isdefault = :flag'),
        {'flag': True}).scalar()

    # Every asset that already has a position had it in this one drawing's
    # coordinate space, so it belongs to this level. An asset with no position
    # is left null: it needs no level until somebody places it.
    bind.execute(sa.text(
        'UPDATE assets SET levelid = :levelid '
        'WHERE mapx IS NOT NULL AND mapy IS NOT NULL AND levelid IS NULL'),
        {'levelid': levelid})
    bind.execute(sa.text(
        'UPDATE locations SET levelid = :levelid '
        'WHERE mapx IS NOT NULL AND mapy IS NOT NULL AND levelid IS NULL'),
        {'levelid': levelid})


def downgrade():
    bind = op.get_bind()
    insp = sa.inspect(bind)
    assetcolumns = {c['name'] for c in insp.get_columns('assets')}
    if 'levelid' in assetcolumns:
        op.drop_constraint('fk_assets_levelid', 'assets', type_='foreignkey')
        op.drop_index('idx_assets_levelid', table_name='assets')
        op.drop_column('assets', 'levelid')
    if 'mapverifiedat' in assetcolumns:
        op.drop_column('assets', 'mapverifiedat')
    locationcolumns = {c['name'] for c in insp.get_columns('locations')}
    if 'levelid' in locationcolumns:
        op.drop_constraint('fk_locations_levelid', 'locations', type_='foreignkey')
        op.drop_index('idx_locations_levelid', table_name='locations')
        op.drop_column('locations', 'levelid')
    if _existing(insp, 'mappositionsnapshots'):
        op.drop_table('mappositionsnapshots')
    if _existing(insp, 'maplevels'):
        op.drop_table('maplevels')
    if _existing(insp, 'buildings'):
        op.drop_table('buildings')