"""Drop deprecated config_file columns

Revision ID: 011
Revises: 010
Create Date: 2025-11-19

This migration removes the deprecated config_file columns from scans and schedules
tables. All functionality now uses config_id to reference database-stored configs.
"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic
revision = '011'
down_revision = '010'
branch_labels = None
depends_on = None


def upgrade():
    """
    Drop config_file columns from scans and schedules tables.

    Prerequisites:
    - All scans must have config_id set
    - All schedules must have config_id set
    - Code must be updated to no longer reference config_file
    """

    connection = op.get_bind()

    # Check for any records missing config_id
    result = connection.execute(sa.text(
        "SELECT COUNT(*) FROM scans WHERE config_id IS NULL"
    ))
    scans_without_config = result.scalar()

    result = connection.execute(sa.text(
        "SELECT COUNT(*) FROM schedules WHERE config_id IS NULL"
    ))
    schedules_without_config = result.scalar()

    if scans_without_config > 0:
        print(f"WARNING: {scans_without_config} scans have NULL config_id")
        print("  These scans will lose their config reference after migration")

    if schedules_without_config > 0:
        raise Exception(
            f"Cannot proceed: {schedules_without_config} schedules have NULL config_id. "
            "Please set config_id for all schedules before running this migration."
        )

    # Drop config_file from scans table
    with op.batch_alter_table('scans', schema=None) as batch_op:
        batch_op.drop_column('config_file')

    # Drop config_file from schedules table
    with op.batch_alter_table('schedules', schema=None) as batch_op:
        batch_op.drop_column('config_file')

    print("✓ Migration complete: Dropped config_file columns")
    print("  - Removed config_file from scans table")
    print("  - Removed config_file from schedules table")
    print("  - All references should now use config_id")


def downgrade():
    """Re-add config_file columns (data will be lost)."""

    # Add config_file back to scans
    with op.batch_alter_table('scans', schema=None) as batch_op:
        batch_op.add_column(
            sa.Column('config_file', sa.Text(), nullable=True,
                     comment='Path to YAML config used (deprecated)')
        )

    # Add config_file back to schedules
    with op.batch_alter_table('schedules', schema=None) as batch_op:
        batch_op.add_column(
            sa.Column('config_file', sa.Text(), nullable=True,
                     comment='Path to YAML config (deprecated)')
        )

    print("✓ Downgrade complete: Re-added config_file columns")
    print("  WARNING: config_file values are lost and will be NULL")