"""Keep original wall-clock display values without changing calendar-date business fields."""
import datetime as dt
from zoneinfo import ZoneInfo

from alembic import op
import sqlalchemy as sa

from flyway.config import settings
from flyway.migration.dates import Decision, apply_date

revision = '0004'
down_revision = '0003'
branch_labels = None
depends_on = None


def upgrade():
    fields = [('fw_ProductBatch', 'Date', 'display_date'),
              ('fw_ClientProduct', 'OpeningDate', 'opening_display_date'),
              ('fw_ClientProduct', 'ClosingDate', 'closing_display_date')]
    connection = op.get_bind()
    for table, source, target in fields:
        op.add_column(table, sa.Column(target, sa.DateTime(), nullable=True))
        rows = connection.execute(sa.text(f'''SELECT v.raw_value, v.resolution, t."ID", t."{source}" AS calendar_date
            FROM migration_legacy_value v JOIN "{table}" t ON lower(v.row_id)=CAST(t."ID" AS text)
            WHERE v.table_name=:table AND v.column_name=:column'''), {'table': table, 'column': source}).mappings()
        for row in rows:
            raw = dt.datetime.fromisoformat(row['raw_value'])
            expected = apply_date(raw, Decision(row['resolution'], ''), ZoneInfo(settings.legacy_server_tz))
            # A later business edit must not be replaced by an older migration value.
            if row['calendar_date'] == expected:
                connection.execute(sa.text(f'UPDATE "{table}" SET "{target}"=:value WHERE "ID"=:id'),
                                   {'value': raw.replace(tzinfo=None), 'id': row['ID']})


def downgrade():
    op.drop_column('fw_ClientProduct', 'closing_display_date')
    op.drop_column('fw_ClientProduct', 'opening_display_date')
    op.drop_column('fw_ProductBatch', 'display_date')