"""create satbots table

Revision ID: 221e976a80af
Revises: 
Create Date: 2026-01-14 14:45:18.895002

"""
import os
from alembic import op, context
import sqlalchemy as sa

from setup import CONFIG
from main.services.custom_sqlalchemy_types import EnumSqlalchemyType
from main.services.satbots.satbot_priority import SatbotPriority
from main.services.satbots.satbot_state import SatbotState
from main.services.satbots.satbot_type import SatbotType


# revision identifiers, used by Alembic.
revision = '221e976a80af'
down_revision = None
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table('satbots',
        sa.Column('id', sa.Integer(), nullable=False, autoincrement=True),
        sa.Column('type', EnumSqlalchemyType(SatbotType), nullable=False),
        sa.Column('state', EnumSqlalchemyType(SatbotState), nullable=False),
        sa.Column('progress', sa.Integer(), nullable=True),
        sa.Column('pid', sa.Integer(), nullable=True),
        sa.Column('priority', EnumSqlalchemyType(SatbotPriority), nullable=True),
        sa.Column('log', sa.ARRAY(sa.String()), nullable=True),
        sa.Column('created_at', sa.TIMESTAMP(), server_default=sa.text("TIMEZONE('utc', CURRENT_TIMESTAMP)"), nullable=False),
        sa.Column('created_by_id', sa.Integer(), nullable=True),
        sa.Column('started_at', sa.TIMESTAMP(), nullable=True),
        sa.Column('finished_at', sa.TIMESTAMP(), nullable=True),
        sa.Column('parameters', sa.JSON(), nullable=True),
        sa.PrimaryKeyConstraint('id')
    )

    database = context.get_context().connection.engine.url.database.replace('satbots', 'apps')
    if os.path.exists(os.path.join(CONFIG['satbots']['backups_directory'], f"{database}_satbots.csv")):
        op.execute(sa.text(f"COPY satbots FROM '{CONFIG['satbots']['backups_directory']}/{database}_satbots.csv' (FORMAT CSV)"))
        max_id = op.get_bind().execute(sa.text("SELECT MAX(id) AS id FROM satbots")).fetchone().id
        if max_id is not None:
            op.execute(sa.text(f"ALTER SEQUENCE satbots_id_seq RESTART WITH {max_id + 1}"))


def downgrade() -> None:
    op.drop_table('satbots')