#! /usr/bin/env python3

import mariadb
import sys
from urllib.parse import quote
from subprocess import Popen, PIPE


# Connect to MariaDB Platform
try:
    conn = mariadb.connect(
        user="root",
        password="root",
        host="0.0.0.0",
        port=3306,
        database="fr_geodata"
    )
except mariadb.Error as e:
    print(f"Error connecting to MariaDB Platform: {e}")
    sys.exit(1)

# Get Cursor
cursor = conn.cursor()

departmentSaved = {} # for caching.

def getEntryFromLine(line):
    labels = [
        'muncipality',
        'muncipalityLink',
        'arrondissment',
        'arrondissmentLink',
        'department',
        'postcode',
        'insee',
        'area',
        'lat',
        'lon'
    ]
    newEntry = {}
    entry = line.strip()[1:-1].split(', ')
    k = 0
    zipcodeList = []
    for i in range(len(entry)):
        cur = entry[i].strip()
        if cur[0] == "'" and cur[-1] == "'":
            cur = cur[1:-1] # Remove the surrounding quotes
        if i == 5: # zip code list
            if cur[-1] == ']':
                cur = cur[1:-1].replace("'", '')
                if '–' in cur:
                    newEntry['postcode'] = list(map(str, range(int(cur[0:cur.index('–')]), int(cur[cur.index('–') + 1:]) + 1)))
                else:
                    newEntry['postcode'] = cur.split(',')
            else:
                zipcodeList = []
                zipcodeList.append(cur.replace('[', '').replace("'", '').replace(',', ''))
            continue
        elif len(zipcodeList) > 0:
            zipcodeList.append(cur.replace(']', '').replace("'", '').replace(',', ''))
            if cur[-1] == ']':
                newEntry['postcode'] = zipcodeList
                k = len(zipcodeList) - 1
                zipcodeList = []
            continue
        if (cur[0:2] == '//'): # url
            parts = cur.split('/')
            parts[-1] = quote(parts[-1])
            cur = 'https:' + '/'.join(parts)
        newEntry[labels[i - k]] = cur
    return newEntry

def getDeptarmentFromInsee(insee: str) -> int:
    prefix = insee[0:2]
    if prefix == '97':
        return int(insee[0:3])
    if prefix == '2A':
        return 201
    if prefix == '2B':
        return 202
    return int(insee[0:2])

def saveToDB(entry):
    # Must have coordinates to save
    if entry['lat'] == '0' and entry['lon'] == '0':
        print(f"Skipping entry {entry} due to missing coordinates.")
        return
    # Save department
    departmentId = getDeptarmentFromInsee(entry['insee'])
    if departmentId not in departmentSaved:
        departmentSaved[departmentId] = saveDepartment(departmentId, entry['department'])

    # Save arrondissement
    arrondissementId = saveArrondissement(entry['arrondissment'], entry['arrondissmentLink'], departmentId)

    # Save place
    try:
        cursor.execute(
            "INSERT INTO place (name, wikiurl, lat, lon, insee, area, department, arrondissement) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
            (entry['muncipality'], entry['muncipalityLink'], entry['lat'], entry['lon'], entry['insee'], entry['area'], departmentId, arrondissementId)
        )
        conn.commit()
        placeId = cursor.lastrowid
    except mariadb.Error as e:
        print(f"Error saving place {entry}: {e}")
        return

    # Save zipcodes
    for zipcode in entry['postcode']:
        try:
            if zipcode.strip() == '':
                continue
            cursor.execute(
                "INSERT INTO zipcode (zip, place) VALUES (?, ?)",
                (zipcode, placeId)
            )
            conn.commit()
        except mariadb.Error as e:
            print(f"Error saving zipcode {zipcode}: {e}")

def saveArrondissement(name, wikiurl, departmentId):
    cursor.execute("SELECT id FROM arrondissement WHERE name = ? AND department = ?", (name, departmentId))
    result = cursor.fetchone()
    if result:
        return result[0]
    else:
        # Insert new arrondissement
        try:
            cursor.execute(
                "INSERT INTO arrondissement (name, wikiurl, department) VALUES (?, ?, ?)",
                (name, wikiurl, departmentId)
            )
            conn.commit()
            return cursor.lastrowid
        except mariadb.Error as e:
            print(f"Error saving arrondissement {name}: {e}")
            return None

def saveDepartment(id, name):
    try:
        cursor.execute("INSERT INTO department (id, name) VALUES (?, ?)", (id, name,))
        conn.commit()
        return cursor.lastrowid
    except mariadb.Error as e:
        print(f"Error saving department {name}: {e}")
        return None

# Main program:
try:
    with open(sys.argv[1], 'r', encoding='utf-8') as fp:
        while True:
            line = fp.readline()
            if not line:
                break
            entry = getEntryFromLine(line)
            if entry is None:
                continue
            saveToDB(entry)
except Exception as e:
    print("Error: " + str(e))
    sys.exit(1)