"""Json settings for settings table

Revision ID: cd34fdfafdaf
Revises: 197672984df0
Create Date: 2023-02-16 14:47:20.424959

"""

import json

import sqlalchemy as sa
from alembic import op
from sqlalchemy.engine import Connection
from sqlalchemy.orm import sessionmaker

from boefjes.sql.config_storage import create_encrypter
from boefjes.sql.db import get_engine

# revision identifiers, used by Alembic.
revision = "cd34fdfafdaf"
down_revision = "197672984df0"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        "settings",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("values", sa.String(length=512), nullable=False),
        sa.Column("plugin_id", sa.String(length=64), nullable=False),
        sa.Column("organisation_pk", sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(["organisation_pk"], ["organisation.pk"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("organisation_pk", "plugin_id", name="unique_settings_per_organisation_per_plugin"),
    )

    upgrade_encrypted_settings(op.get_bind())

    op.drop_table("setting")
    # ### end Alembic commands ###


def upgrade_encrypted_settings(conn: Connection):
    encrypter = create_encrypter()

    with conn.begin():
        res = conn.execute(
            "SELECT json_object_agg(key, value) "
            "AS values, plugin_id, organisation_pk FROM setting GROUP BY plugin_id, organisation_pk"
        )

        results = []
        for result in res.fetchall():
            new_values = {}
            for key, value in result[0].items():
                new_values[key] = encrypter.decode(value)

            new_result = (encrypter.encode(json.dumps(new_values)), result[1], result[2])
            results.append(new_result)

        # Seed the encrypted original data into the new table
        for result in results:
            conn.execute(
                "INSERT INTO settings (values, plugin_id, organisation_pk) VALUES (%s, %s, %s)",
                [result[0], result[1], result[2]],
            )


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        "setting",
        sa.Column("id", sa.INTEGER(), autoincrement=True, nullable=False),
        sa.Column("key", sa.VARCHAR(length=128), autoincrement=False, nullable=False),
        sa.Column("value", sa.VARCHAR(length=128), autoincrement=False, nullable=False),
        sa.Column("organisation_pk", sa.INTEGER(), autoincrement=False, nullable=False),
        sa.Column("plugin_id", sa.VARCHAR(length=64), autoincrement=False, nullable=False),
        sa.ForeignKeyConstraint(
            ["organisation_pk"], ["organisation.pk"], name="setting_organisation_pk_fkey", ondelete="CASCADE"
        ),
        sa.PrimaryKeyConstraint("id", name="setting_pkey"),
        sa.UniqueConstraint("key", "organisation_pk", "plugin_id", name="unique_keys_per_organisation_per_plugin"),
    )

    downgrade_encrypted_settings(op.get_bind())

    op.drop_table("settings")
    # ### end Alembic commands ###


def downgrade_encrypted_settings(conn: Connection):
    encrypter = create_encrypter()

    with conn.begin():
        res = conn.execute("SELECT values, plugin_id, organisation_pk from settings")

        results = []
        for result in res.fetchall():
            decoded_values = json.loads(encrypter.decode(result[0]))

            for key, value in decoded_values.items():
                results.append((key, encrypter.encode(value), result[1], result[2]))

        for result in results:
            conn.execute(
                "INSERT INTO setting (key, value, plugin_id, organisation_pk) VALUES (%s, %s, %s, %s)",
                [result[0], result[1], result[2], result[3]],
            )


if __name__ == "__main__":
    """Only use this entrypoint when no alembic is used in your setup to keep encrypted settings after this migration"""

    session = sessionmaker(bind=get_engine())()

    upgrade_encrypted_settings(session.connection())
    session.commit()

    session.close()
