"""
This module contains a Caribou migration.

Migration Name: user_external_auth
Migration Version: 20250112153541
"""


def upgrade(connection):
    """
    - delete access_token, refresh_token, and expires_at from users
    - add external_auth table which will store the external auths:
        - type: twitch or discord
        - credentials: JSON
    """

    sql = """
    ALTER TABLE users DROP COLUMN access_token;
    """
    connection.execute(sql)
    sql = """
    ALTER TABLE users DROP COLUMN refresh_token;
    """
    connection.execute(sql)
    sql = """
    ALTER TABLE users DROP COLUMN expires_at;
    """
    connection.execute(sql)

    sql = """
    CREATE TABLE external_auth(
        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        type VARCHAR(255) NOT NULL,
        credentials JSON NOT NULL
    );
    """
    connection.execute(sql)

    sql = """
    CREATE TABLE user_external_auth(
        user_id VARCHAR(255) NOT NULL,
        external_auth_id INTEGER NOT NULL,
        FOREIGN KEY (user_id) REFERENCES users(id),
        FOREIGN KEY (external_auth_id) REFERENCES external_auth(id)
    );
    """
    connection.execute(sql)
    connection.commit()


def downgrade(connection):
    # add your downgrade step here
    pass