"""program versions

Revision ID: 68fd4bed8a8e
Revises: 82e0f9c7a952
Create Date: 2023-01-18 15:20:20.789670

"""
import sqlalchemy as sa
from alembic import op

# revision identifiers, used by Alembic.
revision = "68fd4bed8a8e"
down_revision = "82e0f9c7a952"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###

    # Add the new "created" column on programs to track the program creation
    # time
    op.add_column(
        "programs",
        sa.Column("created", sa.TIMESTAMP(timezone=True), nullable=True),
    )
    op.execute(
        """
        update programs SET created = to_timestamp((content->>'time')::float) 
            FROM messages WHERE messages.item_hash = programs.item_hash
        """
    )
    op.alter_column("programs", "created", nullable=False)

    # Update the foreign keys on volume tables to use on delete cascade
    op.drop_constraint(
        "program_code_volumes_program_hash_fkey",
        "program_code_volumes",
        type_="foreignkey",
    )
    op.create_foreign_key(
        None,
        "program_code_volumes",
        "programs",
        ["program_hash"],
        ["item_hash"],
        ondelete="CASCADE",
    )
    op.drop_constraint(
        "program_data_volumes_program_hash_fkey",
        "program_data_volumes",
        type_="foreignkey",
    )
    op.create_foreign_key(
        None,
        "program_data_volumes",
        "programs",
        ["program_hash"],
        ["item_hash"],
        ondelete="CASCADE",
    )
    op.drop_constraint(
        "program_export_volumes_program_hash_fkey",
        "program_export_volumes",
        type_="foreignkey",
    )
    op.create_foreign_key(
        None,
        "program_export_volumes",
        "programs",
        ["program_hash"],
        ["item_hash"],
        ondelete="CASCADE",
    )
    op.drop_constraint(
        "program_machine_volumes_program_hash_fkey",
        "program_machine_volumes",
        type_="foreignkey",
    )
    op.create_foreign_key(
        None,
        "program_machine_volumes",
        "programs",
        ["program_hash"],
        ["item_hash"],
        ondelete="CASCADE",
    )
    op.drop_constraint(
        "program_runtimes_program_hash_fkey", "program_runtimes", type_="foreignkey"
    )
    op.create_foreign_key(
        None,
        "program_runtimes",
        "programs",
        ["program_hash"],
        ["item_hash"],
        ondelete="CASCADE",
    )

    # Create the program versions table
    op.create_table(
        "program_versions",
        sa.Column("program_hash", sa.String(), nullable=False),
        sa.Column("owner", sa.String(), nullable=False),
        sa.Column("current_version", sa.String(), nullable=False),
        sa.Column("last_updated", sa.TIMESTAMP(timezone=True), nullable=False),
        sa.PrimaryKeyConstraint("program_hash"),
    )

    # Add new error codes for programs and stores
    op.execute(
        """
        INSERT INTO error_codes(code, description) VALUES
            (200, 'Store reference not found'),
            (201, 'Store update not targeting the original version of the file'),
            (300, 'Program reference not found'),
            (301, 'Program volume reference(s) not found'),
            (302, 'Program update not allowed'),
            (303, 'Program update not targeting the original version of the program')
        """
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.execute(
        """
        DELETE FROM error_codes WHERE code IN (200, 201, 300, 301, 302, 303)
        """
    )
    op.drop_table("program_versions")
    op.drop_constraint(None, "program_runtimes", type_="foreignkey")
    op.create_foreign_key(
        "program_runtimes_program_hash_fkey",
        "program_runtimes",
        "programs",
        ["program_hash"],
        ["item_hash"],
    )
    op.drop_constraint(None, "program_machine_volumes", type_="foreignkey")
    op.create_foreign_key(
        "program_machine_volumes_program_hash_fkey",
        "program_machine_volumes",
        "programs",
        ["program_hash"],
        ["item_hash"],
    )
    op.drop_constraint(None, "program_export_volumes", type_="foreignkey")
    op.create_foreign_key(
        "program_export_volumes_program_hash_fkey",
        "program_export_volumes",
        "programs",
        ["program_hash"],
        ["item_hash"],
    )
    op.drop_constraint(None, "program_data_volumes", type_="foreignkey")
    op.create_foreign_key(
        "program_data_volumes_program_hash_fkey",
        "program_data_volumes",
        "programs",
        ["program_hash"],
        ["item_hash"],
    )
    op.drop_constraint(None, "program_code_volumes", type_="foreignkey")
    op.create_foreign_key(
        "program_code_volumes_program_hash_fkey",
        "program_code_volumes",
        "programs",
        ["program_hash"],
        ["item_hash"],
    )
    op.drop_column("programs", "created")
    # ### end Alembic commands ###
