"""Add public booking support with submitted_via and pilot_email columns

Revision ID: 003_public_booking
Revises: 002_local_flights
Create Date: 2026-02-20 12:00:00.000000

This migration adds support for public flight booking by adding:
- submitted_via enum field to track ADMIN vs PUBLIC submissions
- pilot_email field to store contact info for public submissions
- Indexes on submitted_via for filtering queries

"""
from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision = '003_public_booking'
down_revision = '002_local_flights'
branch_labels = None
depends_on = None


def upgrade() -> None:
    """
    Add public booking support columns to local_flights, departures, and arrivals tables.
    """
    
    # Create the SubmissionSource enum type
    submission_source_enum = sa.Enum('ADMIN', 'PUBLIC', name='submissionsource')
    
    # Add submitted_via and pilot_email to local_flights table
    op.add_column('local_flights', sa.Column('submitted_via', submission_source_enum, nullable=False, server_default='ADMIN'))
    op.add_column('local_flights', sa.Column('pilot_email', sa.String(length=128), nullable=True))
    
    # Add indexes for submitted_via and pilot_email on local_flights
    op.create_index('idx_lf_submitted_via', 'local_flights', ['submitted_via'])
    op.create_index('idx_lf_pilot_email', 'local_flights', ['pilot_email'])
    
    # Add submitted_via and pilot_email to departures table
    op.add_column('departures', sa.Column('submitted_via', submission_source_enum, nullable=False, server_default='ADMIN'))
    op.add_column('departures', sa.Column('pilot_email', sa.String(length=128), nullable=True))
    
    # Add indexes for submitted_via and pilot_email on departures
    op.create_index('idx_dep_submitted_via', 'departures', ['submitted_via'])
    op.create_index('idx_dep_pilot_email', 'departures', ['pilot_email'])
    
    # Add submitted_via and pilot_email to arrivals table
    op.add_column('arrivals', sa.Column('submitted_via', submission_source_enum, nullable=False, server_default='ADMIN'))
    op.add_column('arrivals', sa.Column('pilot_email', sa.String(length=128), nullable=True))
    
    # Add indexes for submitted_via and pilot_email on arrivals
    op.create_index('idx_arr_submitted_via', 'arrivals', ['submitted_via'])
    op.create_index('idx_arr_pilot_email', 'arrivals', ['pilot_email'])


def downgrade() -> None:
    """
    Remove the submitted_via and pilot_email columns from local_flights, departures, and arrivals tables.
    """
    
    # Drop indexes first
    op.drop_index('idx_lf_submitted_via', table_name='local_flights')
    op.drop_index('idx_lf_pilot_email', table_name='local_flights')
    op.drop_index('idx_dep_submitted_via', table_name='departures')
    op.drop_index('idx_dep_pilot_email', table_name='departures')
    op.drop_index('idx_arr_submitted_via', table_name='arrivals')
    op.drop_index('idx_arr_pilot_email', table_name='arrivals')
    
    # Drop columns from local_flights
    op.drop_column('local_flights', 'pilot_email')
    op.drop_column('local_flights', 'submitted_via')
    
    # Drop columns from departures
    op.drop_column('departures', 'pilot_email')
    op.drop_column('departures', 'submitted_via')
    
    # Drop columns from arrivals
    op.drop_column('arrivals', 'pilot_email')
    op.drop_column('arrivals', 'submitted_via')
    
    # Drop the enum type
    op.execute('DROP TYPE IF EXISTS submissionsource')