"""use UTC DateTime in DB

Revision ID: 4a3773e332a0
Revises: 
Create Date: 2022-11-15 17:35:11.717714

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '4a3773e332a0'
down_revision = None
branch_labels = None
depends_on = None


def upgrade() -> None:
	# ### commands auto generated by Alembic - please adjust! ###
	op.add_column('borrows', sa.Column('purchase_utc', sa.DateTime(), nullable=True))
	op.add_column('borrows', sa.Column('expiration_utc', sa.DateTime(), nullable=True))
	op.add_column('borrows', sa.Column('purchase_timezone', sa.Integer(), nullable=True))
	op.add_column('borrows', sa.Column('expiration_timezone', sa.Integer(), nullable=True))
	borrows = sa.Table(
		"borrows",
		sa.MetaData(),
		sa.Column("id", sa.Integer, primary_key=True, nullable=False),
		sa.Column("purchase", sa.String, nullable=True),
		sa.Column("expiration", sa.String, nullable=True),
		sa.Column("purchase_utc", sa.DateTime, nullable=True),
		sa.Column("expiration_utc", sa.DateTime, nullable=True),
		sa.Column("purchase_timezone", sa.Integer, nullable=True),
		sa.Column("expiration_timezone", sa.Integer, nullable=True)
	)
	connection = op.get_bind()
	results = connection.execute(sa.select([
		borrows.c.id,
		borrows.c.purchase,
		borrows.c.expiration,
		borrows.c.purchase_utc,
		borrows.c.expiration_utc,
		borrows.c.purchase_timezone,
		borrows.c.expiration_timezone
	])).fetchall()
	from datetime import datetime, timezone
	for id, purchase, expiration, purchase_utc, expiration_utc, purchase_timezone, expiration_timezone in results:
		if id % 1000 == 0:
			print(f"... obdelujem id {id}", end="\r")
		if purchase == None:
			print(f"at id {id} purchase is None")
			continue
		purchase_utc = datetime.strptime(purchase, "%Y-%m-%dT%H:%M:%S%z")
		expiration_utc = datetime.strptime(expiration, "%Y-%m-%dT%H:%M:%S%z")
		purchase_timezone = purchase_utc.tzinfo.utcoffset(None).seconds
		expiration_timezone = expiration_utc.tzinfo.utcoffset(None).seconds
		purchase_utc = purchase_utc.astimezone(timezone.utc).replace(tzinfo=None)
		expiration_utc = expiration_utc.astimezone(timezone.utc).replace(tzinfo=None)
		connection.execute(borrows.update().where(borrows.c.id == id).values(
			purchase_utc = purchase_utc,
			expiration_utc = expiration_utc,
			purchase_timezone = purchase_timezone,
			expiration_timezone = expiration_timezone
		))
	op.drop_column('borrows', 'expiration')
	op.drop_column('borrows', 'purchase')
	# ### end Alembic commands ###


def downgrade() -> None:
	# ### commands auto generated by Alembic - please adjust! ###
	op.add_column('borrows', sa.Column('purchase', sa.VARCHAR(), nullable=True))
	op.add_column('borrows', sa.Column('expiration', sa.VARCHAR(), nullable=True))
	borrows = sa.Table(
		"borrows",
		sa.MetaData(),
		sa.Column("id", sa.Integer, primary_key=True, nullable=False),
		sa.Column("purchase", sa.String, nullable=True),
		sa.Column("expiration", sa.String, nullable=True),
		sa.Column("purchase_utc", sa.DateTime, nullable=True),
		sa.Column("expiration_utc", sa.DateTime, nullable=True),
		sa.Column("purchase_timezone", sa.Integer, nullable=True),
		sa.Column("expiration_timezone", sa.Integer, nullable=True)
	)
	connection = op.get_bind()
	results = connection.execute(sa.select([
		borrows.c.id,
		borrows.c.purchase,
		borrows.c.expiration,
		borrows.c.purchase_utc,
		borrows.c.expiration_utc,
		borrows.c.purchase_timezone,
		borrows.c.expiration_timezone
	])).fetchall()
	from datetime import datetime, timezone, timedelta
	for id, purchase, expiration, purchase_utc, expiration_utc, purchase_timezone, expiration_timezone in results:
		if id % 1000 == 0:
			print(f"... obdelujem id {id}", end="\r")
		if purchase_utc == None:
			print(f"at id {id} purchase_utc is None")
			continue
		connection.execute(borrows.update().where(borrows.c.id == id).values(
			purchase = purchase_utc.astimezone(timezone(timedelta(seconds=purchase_timezone))).isoformat(),
			expiration = expiration_utc.astimezone(timezone(timedelta(seconds=expiration_timezone))).isoformat()
		))
	op.drop_column('borrows', 'expiration_timezone')
	op.drop_column('borrows', 'purchase_timezone')
	op.drop_column('borrows', 'expiration_utc')
	op.drop_column('borrows', 'purchase_utc')
	# ### end Alembic commands ###