CREATE TABLE HARPS (
  harpnum INTEGER PRIMARY KEY,                    -- Unique identifier for each HARP region
  start TEXT NOT NULL, -- Start timestamp of the HARP region
  end TEXT NOT NULL,  -- End timestamp of the HARP region
  pix_width INTEGER, -- Refers to the fixed width in pixel values used for cutouts
  pix_height INTEGER, -- Refers to the fixed height in pixel values used for cutouts
  area FLOAT, -- Refers to the area of the HARP region
  CHECK (area >= 0),
  CHECK (pix_width >= 0),
  CHECK (pix_height >= 0),
  CHECK (end >= start)
);
CREATE TABLE CMES (
  cme_id INTEGER NOT NULL PRIMARY KEY,            -- Unique identifier for each CME
  cme_date TEXT NOT NULL,                         -- Date and time of the CME
  cme_pa REAL,                                    -- Position angle of the CME
  cme_width REAL NOT NULL,                        -- Width of the CME
  cme_halo INTEGER,                               -- Indicator for Halo CMEs (1 for Halo, else NULL)
  cme_seen_in INTEGER NOT NULL,                   -- Where the CME was observed (e.g., C2, C3)
  cme_three_points INTEGER NOT NULL,              -- Number of observed points for the CME
  cme_quality INTEGER NOT NULL,                   -- Quality rating for the CME observation
  image_timestamp TEXT
  CHECK (
    ((cme_pa IS NULL) = (cme_halo = 1))           -- Ensure Halo CMEs don't have a position angle
  )
);
CREATE TABLE FLARES (
  flare_id INTEGER NOT NULL PRIMARY KEY,          -- Unique identifier for each flare
  harpnum INTEGER REFERENCES HARPS (harpnum),     -- Associated HARP region
  flare_date TEXT NOT NULL,                       -- Date and time of the flare
  flare_lon REAL,                                 -- Longitude of the flare
  flare_lat REAL,                                 -- Latitude of the flare
  flare_class_score REAL NOT NULL,                -- Score indicating the class of the flare
  flare_class TEXT NOT NULL,                      -- Class of the flare (e.g., M1, X2)
  flare_ar INTEGER,                               -- Active Region number of the flare
  flare_ar_source TEXT,                           -- Source of the Active Region number
  flare_verification TEXT,                        -- Verification status of the flare information
  image_timestamp TEXT REFERENCES
  CHECK (
    (flare_ar IS NULL AND flare_lon IS NOT NULL AND flare_lat IS NOT NULL) OR
    (flare_ar IS NOT NULL)                        -- Ensure either flare_ar or both flare_lon and flare_lat are provided
  )
);
CREATE TABLE RAW_HARPS_BBOX (
    -- Reference to the harpnum column in the harps table
    harpnum INTEGER REFERENCES HARPS (harpnum),
    -- Reference to the timestamp column in the images table
    timestamp TEXT,
    -- Minimum longitude of BBOX
    LONDTMIN REAL,
    -- Maximum longitude of BBOX
    LONDTMAX REAL,
    -- Minimum latitude of BBOX
    LATDTMIN REAL,
    -- Maximum latitude of BBOX
    LATDTMAX REAL,
    -- Is Rotated Bounding Box? Specifies if the BBOX is calculated using differential solar rotation because it was missing from the SHARP dataset
    IRBB INTEGER,
    -- Is trusted magnetic field data?
    IS_TMFI INTEGER,
    PRIMARY KEY (harpnum, timestamp),
    -- Need to check that the timestamp has minutes 12, 24, 36, 48, or 00
    CHECK (strftime('%M', timestamp) IN ('12', '24', '36', '48', '00'))
);
CREATE TABLE PROCESSED_HARPS_PIXEL_BBOX (
    -- Reference to the harpnum column in the harps table
    harpnum INTEGER REFERENCES HARPS (harpnum),
    -- Reference to the timestamp column in the images table
    timestamp TEXT,
    -- Minimum x-coordinate of the bounding box; value must be between 0 and 511
    x_min INTEGER NOT NULL CHECK (x_min >= 0 AND x_min <= 511),
    -- Maximum x-coordinate of the bounding box; value must be between 0 and 511
    x_max INTEGER NOT NULL CHECK (x_max >= 0 AND x_max <= 511),
    -- Minimum y-coordinate of the bounding box; value must be between 0 and 511
    y_min INTEGER NOT NULL CHECK (y_min >= 0 AND y_min <= 511),
    -- Maximum y-coordinate of the bounding box; value must be between 0 and 511
    y_max INTEGER NOT NULL CHECK (y_max >= 0 AND y_max <= 511),
    -- x-coordinate of the center of the bounding box; value must be between 0 and 511
    x_cen INTEGER NOT NULL CHECK (x_cen >= 0 AND x_cen <= 511),
    -- y-coordinate of the center of the bounding box; value must be between 0 and 511
    y_cen INTEGER NOT NULL CHECK (y_cen >= 0 AND y_cen <= 511), overlap_percent REAL, overlap_harpnum INTEGER, area REAL,
    -- Unique key for the table based on harpnum and timestamp
    PRIMARY KEY (harpnum, timestamp),
    -- References the harps_bbox table to maintain a relationship with HARPS bounding box information
    FOREIGN KEY (harpnum, timestamp) REFERENCES HARPS_BBOX (harpnum, timestamp)
);
CREATE TABLE FINAL_CME_HARP_ASSOCIATIONS (
  cme_id INTEGER UNIQUE NOT NULL REFERENCES CMES(cme_id),                 -- Unique identifier for each CME
  harpnum INTEGER NOT NULL REFERENCES HARPS(harpnum),                       -- Associated HARP region
  association_method TEXT NOT NULL,               -- Method used to determine the association
  verification_score REAL,                        -- Confidence score of the association
  independent_verified INTEGER NOT NULL DEFAULT 0, -- Verification level for association (0 if only by me, 1 if verified by external)
  PRIMARY KEY (cme_id, harpnum)                   -- Unique pairing of CME and HARP
);
CREATE TABLE BBOX_OVERLAPS (
    harpnum_a INTEGER REFERENCES HARPS(harpnum),
    harpnum_b INTEGER REFERENCES HARPS(harpnum),
    mean_overlap REAL,
    mean_pixel_overlap REAL,
    ocurrence_percentage REAL,
    pixel_ocurrence_percentage REAL,
    harpnum_a_area REAL,
    harpnum_a_pixel_area REAL,
    harpnum_b_area REAL,
    coexistence REAL
);
CREATE TABLE OVERLAP_RECORDS (
            harpnum_a INTEGER REFERENCES HARPS(harpnum),
            harpnum_b INTEGER REFERENCES HARPS(harpnum),
            decision STRING,
            mean_overlap REAL,
            std_overlap REAL,
            ocurrence_percentage REAL,
            harpnum_a_area REAL,
            harpnum_b_area REAL,
            b_over_a_area_ratio REAL,
            PRIMARY KEY (harpnum_a, harpnum_b),
            CHECK (harpnum_a_area < harpnum_b_area),
            CHECK (decision IN ('MERGED A WITH B', 'DELETED A IN FAVOR OF B'))
);
CREATE TABLE PROCESSED_HARPS_BBOX(
    -- Reference to the harpnum column in the harps table
    harpnum INTEGER REFERENCES HARPS (harpnum),
    -- Reference to the timestamp column in the images table
    timestamp TEXT,
    -- Minimum longitude of BBOX
    LONDTMIN REAL,
    -- Maximum longitude of BBOX
    LONDTMAX REAL,
    -- Minimum latitude of BBOX
    LATDTMIN REAL,
    -- Maximum latitude of BBOX
    LATDTMAX REAL,
    -- Is Rotated Bounding Box? Specifies if the BBOX is calculated using differential solar rotation because it was missing from the SHARP dataset
    IRBB INTEGER,
    -- Is trusted magnetic field data?
    IS_TMFI INTEGER,
    PRIMARY KEY (harpnum, timestamp),
    -- Need to check that the timestamp has minutes 12, 24, 36, 48, or 00
    CHECK (strftime('%M', timestamp) IN ('12', '24', '36', '48', '00'))
);
CREATE TABLE MAJUMDAR_SRC (
             cme_id INTEGER PRIMARY KEY REFERENCES CMES (cme_id),
             cme_date TEXT NOT NULL,
             cme_pa REAL,
             cme_width REAL,
             cme_speed REAL,
             sr_type TEXT,
             sr_lat REAL NOT NULL,
             sr_lon REAL NOT NULL
    , matching_harps INTEGER REFERENCES HARPS (harpnum) DEFAULT NULL, dist REAL, sr_pa REAL);
CREATE TABLE IF NOT EXISTS CMES_HARPS_EVENTS (
	harpnum	INTEGER,
	cme_id	INTEGER,
	flare_id	INTEGER REFERENCES FLARES(flare_id),
	flare_hours_diff	INTEGER NOT NULL,
	dimming_id	INTEGER REFERENCES DIMMINGS(dimming_id),
	dimming_hours_diff	INTEGER NOT NULL,
	FOREIGN KEY(harpnum,cme_id) REFERENCES CMES_HARPS_SPATIALLY_CONSIST(harpnum,cme_id),
	PRIMARY KEY(harpnum,cme_id)
);
CREATE TABLE IF NOT EXISTS CMES_HARPS_SPATIALLY_CONSIST (
	harpnum	INTEGER NOT NULL REFERENCES HARPS(harpnum),
	cme_id	INTEGER NOT NULL REFERENCES CMES(cme_id),
	PRIMARY KEY(harpnum,cme_id)
);
CREATE TABLE IF NOT EXISTS DIMMINGS (
	dimming_id	INTEGER NOT NULL,
	harpnum	INTEGER REFERENCES HARPS(harpnum),
	harps_dimming_dist	REAL NOT NULL,
	dimming_start_date	TEXT NOT NULL,
	dimming_peak_date	TEXT NOT NULL,
	dimming_lon	REAL NOT NULL,
	dimming_lat	REAL NOT NULL,
	image_timestamp	TEXT REFERENCES
	PRIMARY KEY(dimming_id)
);
CREATE TABLE NOAA_HARPNUM_MAPPING (
        noaa INTEGER,
        harpnum INTEGER REFERENCES HARPS (harpnum),
        PRIMARY KEY (noaa, harpnum)
        );
CREATE TABLE NOAAS (
    noaa INTEGER PRIMARY KEY
);
CREATE TABLE SANJIV_SRC (
        cme_id INTEGER PRIMARY KEY REFERENCES CMES (cme_id),
        cme_date TEXT,
        cme_width REAL,
        cme_speed REAL,
        flare_time TEXT,
        flare_class TEXT,
        noaa INTEGER REFERENCES NOAAS (noaa)
        );
CREATE TABLE HOURLY_BBOX(
    -- Reference to the harpnum column in the harps table
    harpnum INTEGER REFERENCES HARPS (harpnum),
    -- Reference to the timestamp column in the images table
    timestamp TEXT,
    -- Minimum longitude of BBOX
    LONDTMIN REAL,
    -- Maximum longitude of BBOX
    LONDTMAX REAL,
    -- Minimum latitude of BBOX
    LATDTMIN REAL,
    -- Maximum latitude of BBOX
    LATDTMAX REAL,
    -- Is Rotated Bounding Box? Specifies if the BBOX is calculated using differential solar rotation because it was missing from the SHARP dataset
    IRBB INTEGER,
    -- Is trusted magnetic field data?
    IS_TMFI INTEGER,
    PRIMARY KEY (harpnum, timestamp),
    -- Need to check that the timestamp has minutes 12, 24, 36, 48, or 00
    CHECK (strftime('%M', timestamp) IN ('12', '24', '36', '48', '00'))
);
CREATE TABLE HOURLY_PIXEL_BBOX(
    -- Reference to the harpnum column in the harps table
    harpnum INTEGER REFERENCES HARPS (harpnum),
    -- Reference to the timestamp column in the images table
    timestamp TEXT,
    -- Minimum x-coordinate of the bounding box; value must be between 0 and 511
    x_min INTEGER NOT NULL CHECK (x_min >= 0 AND x_min <= 511),
    -- Maximum x-coordinate of the bounding box; value must be between 0 and 511
    x_max INTEGER NOT NULL CHECK (x_max >= 0 AND x_max <= 511),
    -- Minimum y-coordinate of the bounding box; value must be between 0 and 511
    y_min INTEGER NOT NULL CHECK (y_min >= 0 AND y_min <= 511),
    -- Maximum y-coordinate of the bounding box; value must be between 0 and 511
    y_max INTEGER NOT NULL CHECK (y_max >= 0 AND y_max <= 511),
    -- x-coordinate of the center of the bounding box; value must be between 0 and 511
    x_cen INTEGER NOT NULL CHECK (x_cen >= 0 AND x_cen <= 511),
    -- y-coordinate of the center of the bounding box; value must be between 0 and 511
    y_cen INTEGER NOT NULL CHECK (y_cen >= 0 AND y_cen <= 511), overlap_percent REAL, overlap_harpnum INTEGER, area REAL,
    -- Unique key for the table based on harpnum and timestamp
    PRIMARY KEY (harpnum, timestamp),
    -- References the harps_bbox table to maintain a relationship with HARPS bounding box information
    FOREIGN KEY (harpnum, timestamp) REFERENCES HARPS_BBOX (harpnum, timestamp)
);

df = pd.read_csv(LASCO_CME_DATABASE)

no_duplicates = df.drop_duplicates(subset="CME_ID", keep=False)

for i, row in no_duplicates.drop_duplicates(subset=["CME_ID"], keep="first").iterrows():
    new_conn.execute(
        """
        INSERT INTO CMES (cme_id, cme_date, cme_pa, cme_width, cme_halo, cme_seen_in, cme_quality, cme_three_points)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        """,
        (
            int(row["CME_ID"][2:]),
            row["CME_DATE"],
            row["CME_PA"],
            row["CME_WIDTH"],
            row["CME_HALO"],
            row["CME_SEEN_IN"],
            row["CME_QUALITY"],
            row["CME_THREE_POINTS"]
        )
    )

WITH AREAS AS (
                SELECT harpnum,
                COALESCE(AVG(NULLIF(100.0 * ((PI()/180.0 * (LONDTMAX - LONDTMIN) * ABS(SIN(PI()/180.0 * LATDTMAX) - SIN(PI()/180.0 * LATDTMIN))) / (2.0 * PI())), 0)),0) AS area
                FROM RAW_HARPS_BBOX
                GROUP BY harpnum
                )
UPDATE HARPS
SET area = (SELECT area FROM AREAS WHERE AREAS.harpnum = HARPS.harpnum);

CREATE TEMPORARY TABLE NO_BIG_HARPS AS
                SELECT RHB.* FROM RAW_HARPS_BBOX RHB
                INNER JOIN HARPS H ON RHB.harpnum = H.harpnum
                WHERE H.area < 18

CREATE TEMPORARY TABLE NO_BIG_HARPS AS
                SELECT RHB.* FROM RAW_HARPS_BBOX RHB
                INNER JOIN HARPS H ON RHB.harpnum = H.harpnum
                WHERE H.area < 18;

CREATE TEMPORARY TABLE TRIMMED_HARPS_BBOX AS
SELECT NBH.* FROM NO_BIG_HARPS NBH

CREATE TEMPORARY TABLE temp_overlap AS
SELECT
    a.harpnum AS harpnum1,
    b.harpnum AS harpnum2,
    a.timestamp AS timestamp,
    100.0 * CASE WHEN a.LONDTMIN < b.LONDTMAX AND a.LONDTMAX > b.LONDTMIN AND a.LATDTMIN < b.LATDTMAX AND a.LATDTMAX > b.LATDTMIN
        THEN (MIN(a.LONDTMAX, b.LONDTMAX) - MAX(a.LONDTMIN, b.LONDTMIN)) * ABS((SIN(PI() / 180.0 * MIN(a.LATDTMAX, b.LATDTMAX)) - SIN(PI() / 180.0 * MAX(a.LATDTMIN, b.LATDTMIN))))
        ELSE 0
    END / ((2.0 * PI() / (100.0 * PI() / 180.0)) * H.area) AS overlap_percent
FROM TRIMMED_HARPS_BBOX a
JOIN TRIMMED_HARPS_BBOX b ON a.timestamp = b.timestamp AND a.harpnum != b.harpnum
JOIN HARPS H ON H.harpnum = a.harpnum;

SELECT tpo.harpnum1,
tpo.harpnum2,
AVG(tpo.overlap_percent) AS mean_overlap,
(100.0 * (strftime('%s', MAX(tpo.timestamp)) - strftime('%s', MIN(tpo.timestamp)))) / (1.0 * NULLIF(strftime('%s', H.end) - strftime('%s', H.start), 0)) AS ocurrence_percentage
FROM temp_overlap tpo
JOIN HARPS H ON H.harpnum = tpo.harpnum1
WHERE tpo.overlap_percent > 0
GROUP BY harpnum1, harpnum2;

CREATE TEMP TABLE avg_overlap AS
    SELECT tpo.harpnum1 as harpnum_a, tpo.harpnum2 as harpnum_b, AVG(tpo.overlap_percent) AS mean_overlap,
    (100.0 * (COUNT(tpo.timestamp) - 1) * 12.0 * 60.0) / (1.0 * NULLIF(strftime('%s', H.end) - strftime('%s', H.start), 0)) AS ocurrence_percentage
    FROM temp_overlap tpo
    JOIN HARPS H ON H.harpnum = tpo.harpnum1
    WHERE tpo.overlap_percent > 0
    GROUP BY harpnum1, harpnum2;

CREATE TABLE OVERLAPS AS
    SELECT ao.*,
    -- Standard deviation of the overlap
    SQRT(SUM((tpo.overlap_percent - ao.mean_overlap) * (tpo.overlap_percent - ao.mean_overlap)) / (COUNT(tpo.timestamp) - 1)) AS std_overlap
    FROM avg_overlap ao
    INNER JOIN TEMP_OVERLAP tpo ON ao.harpnum_a = tpo.harpnum1 AND ao.harpnum_b = tpo.harpnum2
    GROUP BY harpnum_a, harpnum_b;

DROP TABLE IF EXISTS PROCESSED_HARPS_BBOX;

CREATE TABLE PROCESSED_HARPS_BBOX AS
                      SELECT RHB.* FROM RAW_HARPS_BBOX RHB
                      INNER JOIN HARPS H
                      ON RHB.harpnum = H.harpnum
                      WHERE H.area < 18 AND RHB.harpnum NOT IN (SELECT harpnum_a FROM OVERLAP_RECORDS);

DELETE FROM PROCESSED_HARPS_BBOX
WHERE (LONDTMIN < -90 AND LONDTMAX < -90)
OR (LONDTMIN > 90 AND LONDTMAX > 90);

UPDATE PROCESSED_HARPS_BBOX
SET LONDTMIN = -90
WHERE LONDTMIN < -90;

UPDATE PROCESSED_HARPS_BBOX
SET LONDTMAX = 90
WHERE LONDTMAX > 90;

# Spatially consistent

df = pd.read_pickle(SPATIOTEMPORAL_MATCHING_HARPS_DATABASE_PICKLE)
df = df[df["HARPS_SPAT_CONSIST"]]

for cme_id, harpnum in df[["CME_ID", "HARPNUM"]].values:
    cme_id = int(cme_id[2:])
    harpnum = int(harpnum)

    # Add the match to CMES_HARPS_SPATIALLY_CONSIST

    new_cur.execute("""
                INSERT INTO CMES_HARPS_SPATIALLY_CONSIST (harpnum, cme_id)
                VALUES (?, ?)
                """, (harpnum, cme_id))

# Dimmings
dimmings = pd.read_pickle(DIMMINGS_MATCHED_TO_HARPS_PICKLE)
dimmings = dimmings[dimmings["MATCH"]]

for i, row in dimmings[dimmings["MATCH"]].iterrows():
    new_cur.execute(
        """
        INSERT INTO dimmings (dimming_id, harpnum, harps_dimming_dist, dimming_start_date, dimming_peak_date, dimming_lon, dimming_lat)
        VALUES (?, ?, ?, ?, ?, ?, ?)
        """,
        (
            int(row["dimming_id"]),
            int(row["HARPNUM"]),
            row["HARPS_DIMMING_DISTANCE"],
            row["start_time"].split(".")[0],
            row["max_detection_time"].iso[:-4],
            row["longitude"],
            row["latitude"]
        )
    )

# Flares

flares = pd.read_pickle(FLARES_MATCHED_TO_HARPS_PICKLE)

for i, row in flares.iterrows():
    new_cur.execute(
        """
        INSERT INTO flares (flare_id, HARPNUM, flare_date, flare_lon, flare_lat, flare_class_score, flare_class, flare_ar, flare_ar_source, flare_verification)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """,
        (
            int(row["FLARE_ID"]),
            int(row["HARPNUM"]),
            row["FLARE_DATE"].iso[:-4],
            row["FLARE_LON"],
            row["FLARE_LAT"],
            row["FLARE_CLASS_SCORE"],
            row["FLARE_CLASS"],
            row["FLARE_AR"],
            row["FLARE_AR_SOURCE"],
            row["FLARE_VERIFICATION"]
        )
    )

def formatted_timestamp(timestamp):
    """
    Format it to be the same hour and the closest minutes out
    of 00, 12, 24, 36, 48

    Using datetime
    """
    minutes = [0, 12, 24, 36, 48]

    # Check if already is a datetime
    if not isinstance(timestamp, datetime):
        datetime_timestamp = datetime.strptime(timestamp, "%Y-%m-%d %H:%M:%S")
    else:
        datetime_timestamp = timestamp

    # Get the closest minute
    minute = datetime_timestamp.minute

    closest_minute = min(minutes, key=lambda x: abs(x - minute))

    # Now we need to check if we need to add an hour
    # We may need to if the original minute was greater than 48
    if closest_minute == 0 and minute > 48:
        # Add an hour
        datetime_timestamp += timedelta(hours=1)

    # Now we need to set the minute to the closest minute
    datetime_timestamp = datetime_timestamp.replace(minute=closest_minute)

    return datetime_timestamp


image_timestamps = new_cur.execute("SELECT DISTINCT(timestamp) FROM PROCESSED_HARPS_BBOX").fetchall()
image_timestamps = [t[0] for t in image_timestamps]

timestamps = sorted([datetime.strptime(t, "%Y-%m-%d %H:%M:%S") for t in image_timestamps])
image_timestamps = timestamps

# Dimmings
for row in tqdm(new_cur.execute("SELECT dimming_id, dimming_start_date FROM dimmings").fetchall()):
    dimming_timestamp = datetime.strptime(row[1], "%Y-%m-%d %H:%M:%S")
    image_timestamp = formatted_timestamp(dimming_timestamp)
    new_cur.execute("UPDATE dimmings SET image_timestamp = ? WHERE dimming_id = ?", (image_timestamp.strftime("%Y-%m-%d %H:%M:%S"), row[0]))

# Same for flares
for row in tqdm(new_cur.execute("SELECT flare_id, flare_date FROM flares").fetchall()):
    flare_timestamp = datetime.strptime(row[1], "%Y-%m-%d %H:%M:%S")
    image_timestamp = formatted_timestamp(flare_timestamp)
    new_cur.execute("UPDATE flares SET image_timestamp = ? WHERE flare_id = ?", (image_timestamp.strftime("%Y-%m-%d %H:%M:%S"), row[0]))

# And for cmes
for row in tqdm(new_cur.execute("SELECT cme_id, cme_date FROM cmes").fetchall()):
    cme_timestamp = datetime.strptime(row[1], "%Y-%m-%d %H:%M:%S")
    image_timestamp = formatted_timestamp(cme_timestamp)
    new_cur.execute("UPDATE cmes SET image_timestamp = ? WHERE cme_id = ?", (image_timestamp.strftime("%Y-%m-%d %H:%M:%S"), row[0]))

new_conn.commit()

unique_cmes = new_cur.execute("SELECT DISTINCT cme_id from CMES_HARPS_SPATIALLY_CONSIST").fetchall()

# Now let's get the matches

def get_verfification_level(has_dimming, has_flare, flare_class, flare_threshold=25):
    if has_dimming:
        if has_flare:
            if flare_class > flare_threshold:
                return 1
            else:
                return 3
        else:
            return 5
    else:
        if has_flare:
            if flare_class > flare_threshold:
                return 2
            else:
                return 4
        else:
            return -1

def verif_level_from_row(row):
    has_dimming = row['has_dimming']
    has_flare = 0 if pd.isnull(row['flare_id']) else 1
    flare_class = None if pd.isnull(row['flare_class_score']) else row['flare_class_score']
    harpnum = row['harpnum']

    return get_verfification_level(has_dimming, has_flare, flare_class)

matches = dict()

for unique_cme in tqdm(unique_cmes):
    query = f"""
    SELECT CHSC.cme_id, CHSC.harpnum, CHE.flare_id, CHE.dimming_id, F.flare_class_score from CMES_HARPS_SPATIALLY_CONSIST CHSC
    LEFT JOIN CMES_HARPS_EVENTS CHE ON CHSC.cme_id = CHE.cme_id AND CHSC.harpnum = CHE.harpnum
    LEFT JOIN FLARES F ON CHE.flare_id = F.flare_id
    WHERE CHSC.cme_id = {unique_cme[0]}
    """

    df = pd.read_sql_query(query, new_conn)

    # Need to replace dimming_id here with either 0 or 1

    df['has_dimming'] = df['dimming_id'].apply(lambda x: 0 if pd.isnull(x) else 1)

    sorted_df = df.sort_values(by=['has_dimming', 'flare_class_score'], ascending=False)

    # Apply verif_level_from_row to each row

    df["verif_level"] = df.apply(verif_level_from_row, axis=1)

    sorted_df = df.sort_values(by=['verif_level'], ascending=True)

    # But we don't want verification level -1

    sorted_df = sorted_df[sorted_df['verif_level'] != -1]

    # And if this is empty, well there's no match so we continue

    if sorted_df.empty:
        continue

    # Otherwise we can continue

    top_choice = sorted_df.iloc[0]

    verification_level = top_choice['verif_level']
    harpnum = top_choice['harpnum']

    # There's a match!
    matches[unique_cme[0]] = {
        'harpnum': harpnum,
        'verification_level': verification_level
    }

new_cur.execute("DELETE FROM FINAL_CME_HARP_ASSOCIATIONS")

# Iterate through key, value pairs of matches
for cme_id, values in tqdm(matches.items()):
    harpnum = int(values["harpnum"])
    verification_score = int(values["verification_level"])

    association_method = "automatic"
    independent_verfied = 0

    # Add to database
    new_cur.execute("INSERT INTO FINAL_CME_HARP_ASSOCIATIONS (cme_id, harpnum, verification_score, association_method, independent_verified) VALUES (?, ?, ?, ?, ?)", (cme_id, harpnum, verification_score, association_method, independent_verfied))

new_conn.commit()