"""
Test script to verify PostgreSQL database operations
"""

import sys
from pathlib import Path
from datetime import datetime, timedelta

# Add parent directory to path
sys.path.insert(0, str(Path(__file__).parent.parent))

from database.connection import execute_query, execute_write
import logging

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)


def test_insert_order():
    """Test inserting a new order"""
    logger.info("Testing order insertion...")

    now = datetime.now()
    time_window_start = now + timedelta(hours=2)
    time_window_end = now + timedelta(hours=6)

    query = """
        INSERT INTO orders (
            order_id, customer_name, customer_phone, customer_email,
            delivery_address, delivery_lat, delivery_lng,
            time_window_start, time_window_end,
            priority, weight_kg, status
        ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    """

    params = (
        "ORD-TEST-001",
        "John Doe",
        "+1-555-0123",
        "john.doe@example.com",
        "123 Main Street, San Francisco, CA 94103",
        37.7749,
        -122.4194,
        time_window_start,
        time_window_end,
        "standard",
        5.5,
        "pending"
    )

    try:
        result = execute_write(query, params)
        logger.info(f"✓ Order inserted successfully (rows affected: {result})")
        return True
    except Exception as e:
        logger.error(f"✗ Failed to insert order: {e}")
        return False


def test_query_orders():
    """Test querying orders"""
    logger.info("Testing order query...")

    query = "SELECT * FROM orders WHERE status = %s"
    params = ("pending",)

    try:
        results = execute_query(query, params)
        logger.info(f"✓ Query successful: Found {len(results)} pending orders")

        for row in results:
            logger.info(f"  Order ID: {row['order_id']}")
            logger.info(f"  Customer: {row['customer_name']}")
            logger.info(f"  Address: {row['delivery_address']}")
            logger.info(f"  Priority: {row['priority']}")
            logger.info(f"  Status: {row['status']}")
            logger.info("  ---")

        return True
    except Exception as e:
        logger.error(f"✗ Failed to query orders: {e}")
        return False


def test_update_order():
    """Test updating an order"""
    logger.info("Testing order update...")

    query = "UPDATE orders SET status = %s, assigned_driver_id = %s WHERE order_id = %s"
    params = ("assigned", "DRV-001", "ORD-TEST-001")

    try:
        result = execute_write(query, params)
        logger.info(f"✓ Order updated successfully (rows affected: {result})")

        # Verify update
        verify_query = "SELECT status, assigned_driver_id FROM orders WHERE order_id = %s"
        verify_result = execute_query(verify_query, ("ORD-TEST-001",))

        if verify_result:
            row = verify_result[0]
            logger.info(f"  New status: {row['status']}")
            logger.info(f"  Assigned driver: {row['assigned_driver_id']}")

        return True
    except Exception as e:
        logger.error(f"✗ Failed to update order: {e}")
        return False


def test_delete_order():
    """Test deleting the test order"""
    logger.info("Testing order deletion (cleanup)...")

    query = "DELETE FROM orders WHERE order_id = %s"
    params = ("ORD-TEST-001",)

    try:
        result = execute_write(query, params)
        logger.info(f"✓ Order deleted successfully (rows affected: {result})")
        return True
    except Exception as e:
        logger.error(f"✗ Failed to delete order: {e}")
        return False


def main():
    """Run all database tests"""
    logger.info("=" * 50)
    logger.info("Starting FleetMind PostgreSQL Database Tests")
    logger.info("=" * 50)

    tests = [
        ("Insert Order", test_insert_order),
        ("Query Orders", test_query_orders),
        ("Update Order", test_update_order),
        ("Delete Order", test_delete_order),
    ]

    results = []
    for test_name, test_func in tests:
        logger.info(f"\n--- {test_name} ---")
        success = test_func()
        results.append((test_name, success))

    # Summary
    logger.info("\n" + "=" * 50)
    logger.info("Test Summary")
    logger.info("=" * 50)

    passed = sum(1 for _, success in results if success)
    total = len(results)

    for test_name, success in results:
        status = "✓ PASSED" if success else "✗ FAILED"
        logger.info(f"{test_name}: {status}")

    logger.info(f"\nTotal: {passed}/{total} tests passed")

    if passed == total:
        logger.info("\n🎉 All tests passed! Your PostgreSQL database is working correctly!")
        return 0
    else:
        logger.error("\n❌ Some tests failed. Please check the errors above.")
        return 1


if __name__ == "__main__":
    sys.exit(main())