database/migrations/006_add_delivery_timing.py

"""
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()