import psycopg2
import psycopg2.extensions
import requests
from PIL import Image
from io import BytesIO
import numpy as np
import math
from datetime import datetime, timedelta




color_to_dbz = {
(0, 0, 0): -60,(255, 255, 255): -80, (133, 113, 143): -32, (133, 114, 143): -31.5, (134, 115, 141): -31,
(135, 117, 139): -30.5, (135, 118, 139): -30, (136, 119, 137): -29.5, (137, 121, 135): -29,
(137, 122, 135): -28.5, (138, 123, 133): -28, (139, 125, 132): -27.5, (139, 126, 132): -27,
(140, 127, 130): -26.5, (141, 129, 128): -26, (141, 130, 128): -25.5, (142, 131, 126): -25,
(143, 132, 124): -24.5, (143, 133, 124): -24, (144, 135, 123): -23.5, (145, 136, 121): -23,
(145, 137, 121): -22.5, (146, 139, 119): -22, (147, 141, 117): -21.5, (150, 145, 83): -21,
(152, 148, 87): -20.5, (155, 151, 91): -20, (157, 154, 96): -19.5, (160, 157, 100): -19,
(163, 160, 104): -18.5, (165, 163, 109): -18, (168, 166, 113): -17.5, (170, 169, 118): -17,
(173, 172, 122): -16.5, (176, 175, 126): -16, (178, 178, 131): -15.5, (183, 184, 140): -15,
(186, 187, 144): -14.5, (189, 190, 148): -14, (191, 193, 153): -13.5, (194, 196, 157): -13,
(196, 199, 162): -12.5, (199, 202, 166): -12, (202, 205, 170): -11.5, (204, 208, 175): -11,
(210, 212, 180): -10.5, (207, 210, 180): -10, (201, 204, 180): -9.5, (198, 201, 180): -9,
(195, 199, 180): -8.5, (192, 196, 180): -8, (189, 193, 180): -7.5, (185, 190, 180): -7,
(182, 187, 180): -6.5, (179, 185, 180): -6, (176, 182, 180): -5.5, (173, 179, 180): -5,
(170, 176, 180): -4.5, (164, 171, 180): -4, (160, 168, 180): -3.5, (157, 165, 180): -3,
(154, 162, 180): -2.5, (151, 160, 180): -2, (148, 157, 180): -1.5, (145, 154, 180): -1,
(148, 155, 181): -0.5, (144, 152, 180): 0, (140, 149, 179): 0.5, (136, 146, 178): 1,
(128, 140, 176): 1.5, (124, 137, 175): 2, (120, 134, 174): 2.5, (116, 131, 172): 3,
(112, 128, 171): 3.5, (108, 125, 170): 4, (103, 121, 169): 4.5, (99, 118, 168): 5,
(95, 115, 167): 5.5, (91, 112, 166): 6, (87, 109, 164): 6.5, (79, 103, 162): 7,
(75, 100, 161): 7.5, (71, 97, 160): 8, (67, 94, 159): 8.5, (65, 91, 158): 9,
(67, 97, 162): 9.5, (69, 104, 166): 10, (72, 111, 170): 10.5,
(74, 118, 174): 11, (77, 125, 178): 11.5, (79, 132, 182): 12, (81, 139, 187): 12.5,
(86, 153, 195): 13, (89, 159, 199): 13.5, (91, 166, 203): 14, (94, 173, 207): 14.5,
(96, 180, 212): 15, (98, 187, 216): 15.5, (101, 194, 220): 16, (103, 201, 224): 16.5,
(106, 208, 228): 17, (111, 214, 232): 17.5, (104, 214, 215): 18, (89, 214, 179): 18.5,
(82, 214, 162): 19, (75, 214, 144): 19.5, (67, 214, 126): 20, (60, 214, 109): 20.5,
(53, 214, 91): 21, (17, 213, 24): 21.5, (17, 209, 23): 22, (16, 205, 23): 22.5,
(16, 200, 22): 23, (16, 196, 22): 23.5, (15, 188, 21): 24, (15, 183, 20): 24.5,
(14, 179, 20): 25, (14, 175, 19): 25.5, (14, 171, 19): 26, (13, 166, 18): 26.5, 
(13, 162, 18): 27, (13, 158, 17): 27.5, (12, 153, 17): 28, (12, 149, 16): 28.5,
(12, 145, 16): 29, (11, 136, 15): 29.5, (11, 132, 14): 30, (10, 128, 14): 30.5,
(10, 124, 13): 31, (10, 119, 13): 31.5, (9, 115, 12): 32, (9, 111, 12): 32.5, 
(9, 107, 11): 33, (8, 102, 11): 33.5, (8, 98, 10): 34, (9, 94, 9): 34.5, 
(50, 115, 8): 35, (70, 125, 8): 35.5, (91, 136, 7): 36, (111, 146, 7): 36.5,
(132, 157, 6): 37, (152, 168, 6): 37.5, (173, 178, 5): 38, (193, 189, 5): 38.5,
(214, 199, 4): 39, (234, 210, 4): 39.5, (255, 226, 0): 40, (255, 216, 0): 40.5,
(255, 211, 0): 41, (255, 206, 0): 41.5, (255, 201, 0): 42, (255, 196, 0): 42.5,
(255, 192, 0): 43, (255, 187, 0): 43.5, (255, 182, 0): 44, (255, 177, 0): 44.5, 
(255, 172, 0): 45, (255, 167, 0): 45.5, (255, 162, 0): 46, (255, 153, 0): 46.5,
(255, 148, 0): 47, (255, 143, 0): 47.5, (255, 138, 0): 48, (255, 133, 0): 48.5, 
(255, 128, 0): 49, (255, 0, 0): 49.5, (248, 0, 0): 50, (241, 0, 0): 50.5,
(234, 0, 0): 51, (227, 0, 0): 51.5, (213, 0, 0): 52, (205, 0, 0): 52.5, 
(198, 0, 0): 53, (191, 0, 0): 53.5, (184, 0, 0): 54, (177, 0, 0): 54.5, 
(170, 0, 0): 55, (163, 0, 0): 55.5, (155, 0, 0): 56, (148, 0, 0): 56.5, 
(141, 0, 0): 57, (127, 0, 0): 57.5, (120, 0, 0): 58, (113, 0, 0): 58.5, 
(255, 255, 255): 59, (255, 245, 255): 59.5, (255, 234, 255): 60, (255, 223, 255): 60.5,
(255, 212, 255): 61, (255, 201, 255): 61.5, (255, 190, 255): 62, (255, 179, 255): 62.5,
(255, 157, 255): 63, (255, 146, 255): 63.5, (255, 117, 255): 64, (252, 107, 253): 64.5,
(249, 96, 250): 65, (246, 86, 247): 65.5, (243, 75, 244): 66, (240, 64, 241): 66.5,
(237, 54, 239): 67, (234, 43, 236): 67.5, (231, 32, 233): 68, (225, 11, 227): 68.5,
(178, 0, 255): 69, (172, 0, 252): 69.5, (164, 0, 247): 70, (155, 0, 244): 70.5,
(147, 0, 239): 71, (136, 0, 234): 71.5, (131, 0, 232): 72, (121, 0, 226): 72.5,
(114, 0, 221): 73, (105, 0, 219): 73.5, (5, 236, 240): 74, (5, 235, 240): 74.5,
(5, 234, 240): 75, (5, 221, 224): 75.5, (5, 220, 224): 76, (5, 219, 224): 76.5,
(5, 205, 208): 77, (5, 204, 208): 77.5, (4, 189, 192): 78, (4, 188, 192): 78.5,
(4, 187, 192): 79, (4, 174, 176): 79.5, (4, 173, 176): 80, (4, 158, 160): 80.5,
(4, 157, 160): 81, (4, 156, 160): 81.5, (3, 142, 144): 82, (3, 141, 144): 82.5,
(3, 140, 144): 83, (3, 126, 128): 83.5, (3, 125, 128): 84, (3, 111, 112): 84.5,
(3, 110, 112): 85, (3, 109, 112): 85.5, (2, 95, 96): 86, (2, 94, 96): 86.5,
(2, 79, 80): 87, (2, 78, 80): 87.5, (2, 77, 80): 88, (2, 63, 64): 88.5,
(2, 62, 64): 89, (2, 61, 64): 89.5, (1, 48, 48): 90, (1, 47, 48): 90.5, (1, 32, 32): 91,
(1, 31, 32): 91.5, (1, 30, 32): 92, (58, 103, 181): 92.5, (58, 102, 181): 93,
(58, 101, 181): 93.5, (58, 100, 181): 94, (58, 99, 181): 94.5, (58, 98, 181): 95
    
}
    


def get_radar_data(lat, lon, time):
    base_url = "https://mesonet.agron.iastate.edu/cgi-bin/wms/nexrad/n0q-t.cgi"
    params = {
        "service": "WMS", "version": "1.1.1", "request": "GetMap", "layers": "nexrad-n0q-wmst",
        "srs": "EPSG:4326", "bbox": f"{lon-0.075},{lat-0.075},{lon+0.075},{lat+0.075}",
        "width": "256", "height": "256", "format": "image/png", "time": time
    }
    response = requests.get(base_url, params=params)
    image = Image.open(BytesIO(response.content))
    radar_data = np.array(image)
    return radar_data, image


   
def find_max_reflectivity(lat, lon, start_time, end_time):
    start_dt = start_time - timedelta(minutes=5) - timedelta(minutes=start_time.minute % 5, seconds=start_time.second, microseconds=start_time.microsecond)
    end_dt = end_time + timedelta(minutes=5) - timedelta(minutes=end_time.minute % 5, seconds=end_time.second, microseconds=end_time.microsecond)
    
    max_reflectivity = 0
    max_reflectivity_time = None
    current_dt = start_dt
    while current_dt <= end_dt:
        time_str = current_dt.strftime("%Y-%m-%dT%H:%M:%SZ")
        radar_data, _ = get_radar_data(lat, lon, time_str)
        
        for y in range(radar_data.shape[0]):
            for x in range(radar_data.shape[1]):
                r, g, b = radar_data[y, x, :3]
                if (r, g, b) in color_to_dbz:
                    dbz_value = color_to_dbz[(r, g, b)]
                    if dbz_value != None and dbz_value > max_reflectivity:
                        max_reflectivity = dbz_value
                        max_reflectivity_time = time_str
        
        current_dt += timedelta(minutes=5)
    
    return max_reflectivity, max_reflectivity_time

#"update power set vtec = svr.vtec from svr where ST_Contains(svr.nwspoly,power.realgeom) and power.outagen > 50 and power.startguess> svr.issue and power.startguess < svr.endtime + (120 ||'minutes')::interval"
#CHECK FOR point in svr polygon within 2 hours of polygon end
#if exists, add vtec to vtec
#run max radar reflectivity where vtec exists and populate lsrtime and lsrref
    
    
conn = psycopg2.connect(host='localhost', database='nws', user='nws', password='nws')
cursor = conn.cursor()

sql = "update newpower set vtec = svr.vtec from svr where ST_Contains(svr.nwspoly,newpower.geom) and newpower.outagen > 5 and newpower.start_time > svr.issue and newpower.start_time < svr.endtime + (120 ||'minutes')::interval"
cursor.execute(sql)
conn.commit()


#find potentiall verifying reports and add ids to lsrids in svr
"""
-- This comment block seems to have an unrelated query example.
UPDATE svr SET lsrids = array_cat( your_array_column, ARRAY[ (SELECT unnest(ARRAY[1, 2, 3]) EXCEPT SELECT unnest(your_array_column)) ] );


"""
sql = """WITH unique_ids AS (
    SELECT svr.vtec, array_agg(DISTINCT power.id) AS new_ids
    FROM svr
    JOIN newpower power ON svr.vtec = power.vtec
	WHERE EXTRACT(EPOCH FROM (current_timestamp - svr.endtime ))/60/60/24 < 3
    GROUP BY svr.vtec
)
UPDATE svr
SET lsrids = ARRAY(
    SELECT DISTINCT unnest(array_cat(svr.lsrids, unique_ids.new_ids))
)
FROM unique_ids
WHERE svr.vtec = unique_ids.vtec
"""

cursor.execute(sql)
conn.commit()


cursor.execute("SELECT r.id, r.lat, r.lon, s.issue, s.endtime FROM newpower r JOIN svr s ON r.vtec = s.vtec WHERE r.vtec IS NOT NULL AND r.lsrref is null and s.issue > '2024-07-25' and outagen > 4 limit 50")

svrreports = cursor.fetchall()

for i in svrreports:
    repid = i[0]
    lat = i[1]
    lon = i[2]
    start_time = i[3]
    end_time = i[4]
    max_reflectivity, reflectivity_time = find_max_reflectivity(lat, lon, start_time, end_time)
    sql = "UPDATE newpower set lsrtime = %s, lsrref = %s where id = %s"
    vals = (reflectivity_time,max_reflectivity,repid)
    print(vals)
    cursor.execute(sql,vals)
    conn.commit()
                                                                       
"""

lat = 38.52513
lon = -82.459656
start_time = datetime.strptime("2024-09-27T18:48:00Z","%Y-%m-%dT%H:%M:%SZ")
end_time = datetime.strptime("2024-09-27T19:15:00Z","%Y-%m-%dT%H:%M:%SZ")
max_reflectivity, reflectivity_time = find_max_reflectivity(lat, lon, start_time, end_time)
print("Maximum Reflectivity:", max_reflectivity)
print("Time of Maximum Reflectivity:", reflectivity_time)

"""