"""Add performance indexes for time tracking system

Revision ID: time_tracking_indexes
Revises: dc3c13ef2107
Create Date: 2025-08-25 15:02:00.000000

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'time_tracking_indexes'
down_revision = 'dc3c13ef2107'
branch_labels = None
depends_on = None


def upgrade():
    """Add performance indexes identified in gap analysis"""
    
    conn = op.get_bind()
    inspector = sa.inspect(conn)
    
    # Only proceed if time_entries table exists
    if 'time_entries' not in inspector.get_table_names():
        return
    
    # Get existing indexes
    existing_indexes = {idx['name'] for idx in inspector.get_indexes('time_entries')}
    
    # Critical indexes for time tracking performance
    
    # 1. Composite index for worker + date queries (most common pattern)
    if 'idx_time_entries_worker_date' not in existing_indexes:
        op.create_index(
            'idx_time_entries_worker_date', 
            'time_entries', 
            ['worker_id', 'work_date'], 
            unique=False
        )
    
    # 2. Composite index for contract + approval status (fixes N+1 query)
    if 'idx_time_entries_contract_approval' not in existing_indexes:
        op.create_index(
            'idx_time_entries_contract_approval', 
            'time_entries', 
            ['contract_id', 'is_approved'], 
            unique=False
        )
    
    # 3. Composite index for pending approvals query optimization
    if 'idx_time_entries_contract_pending' not in existing_indexes:
        op.create_index(
            'idx_time_entries_contract_pending', 
            'time_entries', 
            ['contract_id', 'is_approved', 'work_date'], 
            unique=False
        )
    
    # 4. Index for active clock-in queries (today's unclosed entries)
    if 'idx_time_entries_worker_active' not in existing_indexes:
        op.create_index(
            'idx_time_entries_worker_active', 
            'time_entries', 
            ['worker_id', 'work_date', 'clock_out'], 
            unique=False
        )
    
    # 5. Index for date range queries
    if 'idx_time_entries_date_range' not in existing_indexes:
        op.create_index(
            'idx_time_entries_date_range', 
            'time_entries', 
            ['work_date', 'contract_id'], 
            unique=False
        )


def downgrade():
    """Remove performance indexes"""
    
    conn = op.get_bind()
    inspector = sa.inspect(conn)
    
    if 'time_entries' not in inspector.get_table_names():
        return
    
    existing_indexes = {idx['name'] for idx in inspector.get_indexes('time_entries')}
    
    if 'idx_time_entries_date_range' in existing_indexes:
        op.drop_index('idx_time_entries_date_range', table_name='time_entries')
    if 'idx_time_entries_worker_active' in existing_indexes:
        op.drop_index('idx_time_entries_worker_active', table_name='time_entries')
    if 'idx_time_entries_contract_pending' in existing_indexes:
        op.drop_index('idx_time_entries_contract_pending', table_name='time_entries')
    if 'idx_time_entries_contract_approval' in existing_indexes:
        op.drop_index('idx_time_entries_contract_approval', table_name='time_entries')
    if 'idx_time_entries_worker_date' in existing_indexes:
        op.drop_index('idx_time_entries_worker_date', table_name='time_entries')