import json
import random
from datetime import datetime


# get_markers requests all marker data or valid markers, converts the data to json, and writes
# the data to the response object
def get_measurements(self, cursor, lake_name):
    try:
        print("Lake name in get_measurements: ", lake_name)
        sql_query = '''
            SELECT m.MeasurementID, m.SensorID, m.TimeMeasured, m.CenterLat, m.CenterLon,
                   s.SensorType, s.Active, 
                   b.Name,
                   d.SubDivisionID, d.GroupID, d.MinimumThickness, 
                   d.AverageThickness, d.CenterLatitude, d.CenterLongitude, 
                   d.Accuracy
            FROM Measurement m
            INNER JOIN Sensor s ON m.SensorID = s.SensorID
            INNER JOIN BodyOfWater b ON m.WaterBodyName = b.Name
            LEFT JOIN SubDivision d ON m.MeasurementID = d.MeasurementID
            WHERE b.Name = ?
        '''

        cursor.execute(sql_query, (lake_name,))

        rows = cursor.fetchall()

        # List of all fetched measurement objects
        measurement_data = {}

        '''
        f = open('server/?')
        data = json.load(f)

        '''
        # Iterate over all fetched rows
        for row in rows:
            measurement_id = row[0]
            sub_div_id = row[8]
            center_lat = row[12]
            center_lng = row[13]

            '''
            curr_stat = []
            
            # Find matching ice stat
            for stat in data['stats']
                if ice_stats.sub_div_id == sub_div_id
                    curr_stat = stat
                    break
            '''

            # Create subdivision new object
            sub_division = {
                'SubdivID': sub_div_id,
                'GroupID': row[9],
                'MinThickness': row[10],
                'AvgThickness': row[11],
                'CenLatitude': center_lat,
                'CenLongitude': center_lng,
                'Accuracy': row[14],
                'Color': calculateColor(row[11]),  # NB color calculated based on average thickness, should be minimum
                'IceStats': ()  # NB temp empty, add ice stats later
            }

            # Check if measurement ID already exists in measurement_data
            if measurement_id in measurement_data:
                # Create new subdivision within measurement if it does not already exist
                if sub_division not in measurement_data[measurement_id]['Subdivisions']:
                    measurement_data[measurement_id]['Subdivisions'].append(sub_division)

            else:
                # Create a new entry for measurement_id if it does not already exist in the list
                measurement_data[measurement_id] = {
                    'MeasurementID': measurement_id,
                    'TimeMeasured': row[2],
                    'CenterLat': row[3],
                    'CenterLon': row[4],
                    'Sensor': {  # Each measurement only has one related sensor
                        'SensorID': row[1],
                        'SensorType': row[5],
                        'Active': bool(row[6])
                    },
                    'Subdivisions': [sub_division],  # Array of sub_division objects
                }

        # NB remember to clos file after implementation
        # f.close()

        # Convert dictionary values to list of measurements
        data = list(measurement_data.values())

        if len(data) == 0:
            marker_data = json.dumps(['no measurements'])
        else:
            # Convert list of dictionaries to JSON
            marker_data = json.dumps(data, indent=4)

    except Exception as e:
        print(f"Error in querying database: {e}")
        marker_data = '[]'

        # Set headers
        self.send_response(500)
        self.send_header("Content-type", "application/json")
        self.end_headers()

    # Set headers
    self.send_response(200)
    self.send_header("Content-type", "application/json")
    self.end_headers()

    # Write marker data to response object
    self.wfile.write(marker_data.encode('utf-8'))


def calculateColor(thickness: float):  # NB neither final colors nor ranges
    if 0 < thickness <= 4:
        return 0xFFff0000  # Red
    elif 4 < thickness <= 6:
        return 0xffff6a00  # Orange
    elif 6 < thickness <= 8:
        return 0xFFb1ff00  # Green
    elif thickness > 8:
        return 0xFF00d6ff  # Blue
    else:
        return 0xFF939393  # Gray