import json
from datetime import datetime

from server.consts import LAKE_RELATIONS_PATH
from server.ModelFromNVE.icemodellingscripts.getIceThicknessLakes import get_raw_dates, ice_prognosis_raw_data


def get_measurements(self, cursor, lake_name):
    try:
        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 = {}
        sub_div_ids = []

        # 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]

            # 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': get_raw_dates(ice_prognosis_raw_data(sub_div_id=sub_div_id, x=center_lat, y=center_lng))
            }
            sub_div_ids.append(sub_div_id)

            # Check if measurement sub_div_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
                }

        # Populate remaining subdivisions and create "invalid" measurement to store them
        remaining_sub_divs = fill_remaining_subdivisions(lake_name, sub_div_ids)
        measurement_data[-1] = {
            'MeasurementID': -1,
            'TimeMeasured': str(datetime.now()),
            'CenterLat': None,
            'CenterLon': None,
            'Sensor': None,
            'Subdivisions': remaining_sub_divs
        }

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

        # Write the newest measurements to file
        with open(LAKE_RELATIONS_PATH + lake_name.lower() + '_measurements.json', 'w') as f:
            json.dump(data, f)

        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 getting measurements: {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'))


# Get data for subdivisions that have not been measured by sensors, and thus are not in the database
def fill_remaining_subdivisions(lake_name: str, sub_div_ids: list):
    try:
        with open(LAKE_RELATIONS_PATH + lake_name + '_div.json', 'r') as file:
            data = json.load(file)

        relation = data
        sub_divisions = []

        for sub_div in relation['features']:
            sub_div_id = int(sub_div['properties']['sub_div_id'])
            if sub_div_id not in sub_div_ids:
                center_lat = sub_div['properties']['sub_div_center'][0]
                center_lng = sub_div['properties']['sub_div_center'][1]

                ice_stats = get_raw_dates(ice_prognosis_raw_data(sub_div_id=sub_div_id, x=center_lat, y=center_lng))

                sub_division = {
                    'SubdivID': sub_div_id,
                    'GroupID': None,
                    'MinThickness': -1.0,  # NB placeholders
                    'AvgThickness': -1.0,
                    'CenLatitude': center_lat,
                    'CenLongitude': center_lng,
                    'Accuracy': None,
                    'Color': 0xFF00d6ff,  # NB placeholder
                    'IceStats': ice_stats,
                }

                sub_divisions.append(sub_division)

        return sub_divisions

    except FileNotFoundError as e:
        print("Failed to find relation file: ", e)
    except Exception as e:
        print("Failed to add remaining subdivisions: ", e)


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 0xFF8C8C8C  # Grey