File size: 3,372 Bytes
a6d2d54 |
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 100 101 102 103 104 105 106 107 |
"""
Migration 006: Add delivery timing and SLA tracking fields to orders table
Adds expected_delivery_time (mandatory), delivery_status, and sla_grace_period_minutes
"""
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 expected delivery time (mandatory deadline promised to customer)
ALTER TABLE orders
ADD COLUMN IF NOT EXISTS expected_delivery_time TIMESTAMP;
-- Add delivery performance status
ALTER TABLE orders
ADD COLUMN IF NOT EXISTS delivery_status VARCHAR(20)
CHECK(delivery_status IN ('on_time', 'late', 'very_late', 'failed_on_time', 'failed_late'));
-- Add SLA grace period (minutes after expected time that's still acceptable)
ALTER TABLE orders
ADD COLUMN IF NOT EXISTS sla_grace_period_minutes INTEGER DEFAULT 15;
-- Add comments
COMMENT ON COLUMN orders.expected_delivery_time IS 'Required delivery deadline promised to customer (mandatory when creating order)';
COMMENT ON COLUMN orders.delivery_status IS 'Delivery performance: on_time, late (within grace), very_late (SLA violation), failed_on_time, failed_late';
COMMENT ON COLUMN orders.sla_grace_period_minutes IS 'Grace period in minutes after expected_delivery_time (default: 15 mins)';
-- Create index for querying by expected delivery time
CREATE INDEX IF NOT EXISTS idx_orders_expected_delivery ON orders(expected_delivery_time);
"""
ROLLBACK_SQL = """
-- Drop indexes
DROP INDEX IF EXISTS idx_orders_expected_delivery;
-- Drop columns
ALTER TABLE orders
DROP COLUMN IF EXISTS expected_delivery_time,
DROP COLUMN IF EXISTS delivery_status,
DROP COLUMN IF EXISTS sla_grace_period_minutes;
"""
def up():
"""Apply migration - add delivery timing fields"""
print("Running migration 006: Add delivery timing and SLA tracking fields...")
try:
conn = get_db_connection()
cursor = conn.cursor()
# Execute migration SQL
cursor.execute(MIGRATION_SQL)
conn.commit()
cursor.close()
conn.close()
print("SUCCESS: Migration 006 applied successfully")
print(" - Added expected_delivery_time TIMESTAMP column")
print(" - Added delivery_status VARCHAR(20) column")
print(" - Added sla_grace_period_minutes INTEGER column (default: 15)")
print(" - Created index on expected_delivery_time")
print(" - Valid delivery statuses: on_time, late, very_late, failed_on_time, failed_late")
return True
except Exception as e:
print(f"ERROR: Migration 006 failed: {e}")
return False
def down():
"""Rollback migration - drop delivery timing fields"""
print("Rolling back migration 006: Drop delivery timing fields...")
try:
conn = get_db_connection()
cursor = conn.cursor()
# Execute rollback SQL
cursor.execute(ROLLBACK_SQL)
conn.commit()
cursor.close()
conn.close()
print("SUCCESS: Migration 006 rolled back successfully")
return True
except Exception as e:
print(f"ERROR: Migration 006 rollback failed: {e}")
return False
if __name__ == "__main__":
import sys
if len(sys.argv) > 1 and sys.argv[1] == "down":
down()
else:
up()
|