File size: 2,634 Bytes
d3ecfe5 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 |
"""
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()
|