"""custom geometry admin

Revision ID: be7a44d96984
Revises: 8350e5a3e24c
Create Date: 2026-01-15 15:27:11.159411

"""
from alembic import op
import sqlalchemy as sa
import geoalchemy2
from setup import CONFIG
from main.services.custom_sqlalchemy_types import EnumSqlalchemyType
from main.models.insar.custom_group import MetricsType

# revision identifiers, used by Alembic.
revision = 'be7a44d96984'
down_revision = '8350e5a3e24c'
branch_labels = None
depends_on = None


def upgrade():
    # Joining table - but user_id is against a different database, so can't have a foreign key constraint
    op.create_table('custom_group_users',
        sa.Column('custom_group_id', sa.Integer(), nullable=False),
        sa.Column('user_id', sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(['custom_group_id'], ['custom_groups.id']),
        sa.PrimaryKeyConstraint('custom_group_id', 'user_id')
    )

    op.add_column('custom_points', sa.Column('created_at', sa.TIMESTAMP(), nullable=True))
    op.add_column('custom_points', sa.Column('updated_at', sa.TIMESTAMP(), nullable=True))

    # Temporarily set a server default using a system user to allow the migration to continue
    op.add_column('custom_polygons', sa.Column('created_by_id', sa.Integer(), nullable=False, server_default=str(CONFIG['satbots']['update_grids_script']['created_by_id'])))
    op.add_column('custom_polygons', sa.Column('updated_by_id', sa.Integer(), nullable=True))
    op.add_column('custom_points', sa.Column('created_by_id', sa.Integer(), nullable=False, server_default=str(CONFIG['satbots']['update_grids_script']['created_by_id'])))
    op.add_column('custom_points', sa.Column('updated_by_id', sa.Integer(), nullable=True))

    # Require a created_by_id to be provided by the code from now on
    op.alter_column('custom_polygons', 'created_by_id', server_default=None, nullable=False)
    op.alter_column('custom_points', 'created_by_id', server_default=None, nullable=False)

    # Don't require timestamp values to be provided
    op.alter_column('custom_polygons', 'created_at', server_default=sa.text("TIMEZONE('utc', CURRENT_TIMESTAMP)"))
    op.alter_column('custom_points', 'created_at', server_default=sa.text("TIMEZONE('utc', CURRENT_TIMESTAMP)"))
    op.alter_column('custom_points', 'created_at', nullable=True)
    op.alter_column('custom_polygons', 'updated_at', nullable=True, server_default=None)

    op.add_column('custom_groups', sa.Column('metrics_type', EnumSqlalchemyType(MetricsType), nullable=True))


def downgrade():
    op.drop_table('custom_group_users')
    op.drop_column('custom_polygons', 'created_by_id')
    op.drop_column('custom_polygons', 'updated_by_id')
    op.drop_column('custom_points', 'created_by_id')
    op.drop_column('custom_points', 'updated_by_id')
    op.drop_column('custom_points', 'created_at')
    op.drop_column('custom_points', 'updated_at')
    op.drop_column('custom_groups', 'metrics_type')

    op.alter_column('custom_polygons', 'created_at', server_default=None)
    op.alter_column('custom_polygons', 'updated_at', server_default=sa.text("TIMEZONE('utc', CURRENT_TIMESTAMP)"))

    op.execute("UPDATE custom_polygons SET updated_at = TIMEZONE('utc', CURRENT_TIMESTAMP) WHERE updated_at is NULL")

    op.alter_column('custom_polygons', 'updated_at', nullable=False)