import pymysql
from dotenv import load_dotenv
import os
import socket
import time

load_dotenv()

def get_my_ip():
    try:
        s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
        s.connect(("8.8.8.8", 80))
        ip = s.getsockname()[0]
        s.close()
        return ip
    except:
        return "Could not determine IP"

def test_port():
    host = os.getenv('DB_HOST')
    port = int(os.getenv('DB_PORT'))
    try:
        start_time = time.time()
        sock = socket.create_connection((host, port), timeout=5)
        connection_time = time.time() - start_time
        sock.close()
        return True, f"Connected in {connection_time:.2f} seconds"
    except socket.timeout:
        return False, "Connection timed out"
    except ConnectionRefusedError:
        return False, "Connection refused"
    except Exception as e:
        return False, str(e)

def test_database_connection():
    db_config = {
        'host': os.getenv('DB_HOST'),
        'port': int(os.getenv('DB_PORT')),
        'user': os.getenv('DB_USER'),
        'password': os.getenv('DB_PASSWORD'),
        'database': os.getenv('DB_NAME'),
        'connect_timeout': 10,
        'read_timeout': 10,
        'write_timeout': 10
    }

    print("\n=== Connection Test Results ===")
    print(f"Local IP: {get_my_ip()}")
    print(f"Host: {db_config['host']}")
    print(f"Port: {db_config['port']}")
    print(f"User: {db_config['user']}")
    print(f"Database: {db_config['database']}")
    port_result, port_details = test_port()
    print(f"\nPort test: {'✓' if port_result else '✗'}")
    print(f"Details: {port_details}")

    if not port_result:
        return

    try:
        print("\nAttempting database connection...")
        conn = pymysql.connect(**db_config)
        print("Successfully connected to database!")
        
        with conn.cursor() as cursor:
            cursor.execute("SELECT VERSION()")
            version = cursor.fetchone()
            print(f"MySQL version: {version[0]}")
        
        conn.close()
        
    except pymysql.Error as e:
        print(f"\nDatabase error ({e.args[0]}): {e.args[1]}")
        print("\nTroubleshooting suggestions:")
        print("1. Check if database server allows external connections")
        print("2. Verify database credentials in .env file")
        print("3. Contact database admin to allow access from your IP")
        print(f"4. Try connecting through a VPN if available")

if __name__ == "__main__":
    test_database_connection()