"""
Migration 005: Add failure_reason column to assignments table
Adds structured failure reason field for failed deliveries
"""

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 failure_reason column with predefined categories
ALTER TABLE assignments
ADD COLUMN IF NOT EXISTS failure_reason VARCHAR(100)
CHECK(failure_reason IN (
    'customer_not_available',
    'wrong_address',
    'refused_delivery',
    'damaged_goods',
    'payment_issue',
    'vehicle_breakdown',
    'access_restricted',
    'weather_conditions',
    'other'
));

-- Add comment to explain the column
COMMENT ON COLUMN assignments.failure_reason IS 'Structured reason for delivery failure (required when status is failed)';
"""

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


def up():
    """Apply migration - add failure_reason column"""
    print("Running migration 005: Add failure_reason 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 005 applied successfully")
        print("  - Added failure_reason VARCHAR(100) column to assignments table")
        print("  - Constraint added for predefined failure categories")
        print("  - Available reasons: customer_not_available, wrong_address, refused_delivery,")
        print("    damaged_goods, payment_issue, vehicle_breakdown, access_restricted,")
        print("    weather_conditions, other")
        return True

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


def down():
    """Rollback migration - drop failure_reason column"""
    print("Rolling back migration 005: Drop failure_reason 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 005 rolled back successfully")
        return True

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


if __name__ == "__main__":
    import sys

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