"""resolve foreign key conflicts

Revision ID: f6ec7393c858
Revises: e35936e392dd
Create Date: 2021-02-10 17:31:22.145457

"""
import sqlalchemy as sa
from alembic import op

# revision identifiers, used by Alembic.
revision = "f6ec7393c858"
down_revision = "e443e876bf33"
branch_labels = None
depends_on = None


def upgrade():
    foreign_keys = {
        "users": "city_id",
        "games": "sports_id",
        "venues": "user_id",
        "venue_images": "user_id",
    }
    for table, field in foreign_keys.items():
        if table == "venues" or table == "venue_images":
            query = f"UPDATE {table} SET {field} = 1976 WHERE {field} = 0"
        else:
            query = f"UPDATE {table} SET {field} = 1 WHERE {field} = 0"
        op.execute(query)
    game_id_tables = [
        "player_availabilities",
        "teams",
        "user_ratings",
        "purchase_games",
        "payments",
        "player_cancel_games",
    ]
    venue_id_tables = [
        "venue_images",
        "games",
    ]
    for table in game_id_tables:
        query = f"DELETE FROM {table} WHERE game_id NOT IN (SELECT id from games)"
        op.execute(query)
    for table in venue_id_tables:
        query = f"DELETE FROM {table} WHERE venue_id NOT IN (SELECT id from venues)"
        op.execute(query)


def downgrade():
    pass