import os
import django
import math
from django.db import connection

# Setup Django environment
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings.develop')
django.setup()

def test_sql_logic(lat, lon):
    print(f"\n{'='*80}")
    print(f"POSTGRESQL RAW DATA & SCORING FOR: ({lat}, {lon})")
    print(f"{'='*80}")

    # Bounding Box Range used in city_detection_ip.py
    lat_range = 3.0
    lon_range = 3.0
    lat_min, lat_max = lat - lat_range, lat + lat_range
    lon_min, lon_max = lon - lon_range, lon + lon_range

    print(f"Range: Lat [{lat_min:.2f} to {lat_max:.2f}], Lon [{lon_min:.2f} to {lon_max:.2f}]")

    with connection.cursor() as cursor:
        query = """
            WITH bounded_cities AS (
                SELECT name, country_code, latitude, longitude, population
                FROM geonames_city
                WHERE feature_class = 'P'
                  AND latitude BETWEEN %s AND %s
                  AND longitude BETWEEN %s AND %s
                  AND population IS NOT NULL
                  AND population > 0
            ),
            distance_calc AS (
                SELECT name, country_code, population, latitude, longitude,
                       (6371 * acos(least(1, greatest(-1,
                        cos(radians(%s)) * cos(radians(latitude)) *
                        cos(radians(longitude) - radians(%s)) +
                        sin(radians(%s)) * sin(radians(latitude))
                       )))) AS distance
                FROM bounded_cities
            )
            SELECT name, country_code, population, distance,
                   CASE
                       WHEN distance <= 30 THEN population / (distance + 1)
                       ELSE population / POWER(distance, 2)
                   END AS calculated_score
            FROM distance_calc
            WHERE distance <= 150
            ORDER BY calculated_score DESC
            LIMIT 15;
        """
        
        cursor.execute(query, [lat_min, lat_max, lon_min, lon_max, lat, lon, lat])
        results = cursor.fetchall()

        if not results:
            print("\n❌ No results found within range.")
            return

        print(f"\n{'Name':<25} | {'CC':<2} | {'Population':<12} | {'Distance':<8} | {'Score':<15}")
        print("-" * 80)
        
        for name, cc, pop, dist, score in results:
            dist_str = f"{dist:.2f}km"
            pop_str = f"{pop:,}"
            score_str = f"{score:.2f}"
            print(f"{name:<25} | {cc:<2} | {pop_str:<12} | {dist_str:<8} | {score_str:<15}")

        print(f"\nWINNER: {results[0][0]} ({results[0][1]})")
    print(f"{'='*80}\n")

if __name__ == "__main__":
    import sys
    if len(sys.argv) == 3:
        try:
            l1, l2 = float(sys.argv[1]), float(sys.argv[2])
            test_sql_logic(l1, l2)
        except ValueError:
            print("Please provide valid numbers.")
    else:
        # Tehran coordinates as example
        test_sql_logic(35.689, 51.389)