"""etudiant_annotations : ajoute clé externe etudiant et moduleimpl

Revision ID: 2e4875004e12
Revises: 3fa988ff8970
Create Date: 2024-02-11 12:10:36.743212

"""

from alembic import op


# revision identifiers, used by Alembic.
revision = "2e4875004e12"
down_revision = "3fa988ff8970"
branch_labels = None
depends_on = None


def upgrade():

    # Supprime les annotations orphelines
    op.execute(
        """DELETE FROM etud_annotations
        WHERE etudid NOT IN (SELECT id FROM identite);
        """
    )
    # Ajoute clé:
    with op.batch_alter_table("etud_annotations", schema=None) as batch_op:
        batch_op.create_foreign_key(None, "identite", ["etudid"], ["id"])

    # Et modif liée au commit 072d013590abf715395bc987fb48de49f6750527
    with op.batch_alter_table("notes_moduleimpl", schema=None) as batch_op:
        batch_op.drop_constraint(
            "notes_moduleimpl_responsable_id_fkey", type_="foreignkey"
        )
        batch_op.create_foreign_key(
            None, "user", ["responsable_id"], ["id"], ondelete="SET NULL"
        )

    # cet index en trop trainait depuis longtemps...
    with op.batch_alter_table("assiduites", schema=None) as batch_op:
        batch_op.drop_index("ix_assiduites_user_id")


def downgrade():
    with op.batch_alter_table("notes_moduleimpl", schema=None) as batch_op:
        batch_op.drop_constraint(None, type_="foreignkey")
        batch_op.create_foreign_key(
            "notes_moduleimpl_responsable_id_fkey", "user", ["responsable_id"], ["id"]
        )

    with op.batch_alter_table("etud_annotations", schema=None) as batch_op:
        batch_op.drop_constraint(None, type_="foreignkey")

    with op.batch_alter_table("assiduites", schema=None) as batch_op:
        batch_op.create_index("ix_assiduites_user_id", ["user_id"], unique=False)