"""
Migration 004: Add route_directions column to assignments table
Adds JSONB column to store turn-by-turn navigation instructions from Google Routes API
"""

import sys
import os

# Add parent directory to path for imports
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))))

from database.connection import get_db_connection

MIGRATION_SQL = """
-- Add route_directions column to store turn-by-turn navigation steps
ALTER TABLE assignments
ADD COLUMN IF NOT EXISTS route_directions JSONB;

-- Add comment to explain the column
COMMENT ON COLUMN assignments.route_directions IS 'Turn-by-turn navigation instructions from Google Routes API (array of steps with instructions, distance, duration)';
"""

ROLLBACK_SQL = """
-- Drop route_directions column
ALTER TABLE assignments
DROP COLUMN IF EXISTS route_directions;
"""


def up():
    """Apply migration - add route_directions column"""
    print("Running migration 004: Add route_directions column to assignments table...")

    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        # Execute migration SQL
        cursor.execute(MIGRATION_SQL)

        conn.commit()
        cursor.close()
        conn.close()

        print("SUCCESS: Migration 004 applied successfully")
        print("  - Added route_directions JSONB column to assignments table")
        print("  - Column will store turn-by-turn navigation instructions")
        return True

    except Exception as e:
        print(f"ERROR: Migration 004 failed: {e}")
        return False


def down():
    """Rollback migration - drop route_directions column"""
    print("Rolling back migration 004: Drop route_directions column...")

    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        # Execute rollback SQL
        cursor.execute(ROLLBACK_SQL)

        conn.commit()
        cursor.close()
        conn.close()

        print("SUCCESS: Migration 004 rolled back successfully")
        return True

    except Exception as e:
        print(f"ERROR: Migration 004 rollback failed: {e}")
        return False


if __name__ == "__main__":
    import sys

    if len(sys.argv) > 1 and sys.argv[1] == "down":
        down()
    else:
        up()