import json
from json import JSONDecodeError

import pyodbc


CREDS_FILE = "creds.json"
QUERY_RESULTS_FILE = "staters.json"
FIELDS = ["PIN", "FNAME", "LNAME", "CITYNAME", "COUNTYNAME"]
QUERY = f"SELECT {', '.join(FIELDS)} FROM state"


with open(CREDS_FILE) as f:
    creds = json.load(f)


def update_staters():
    """Get a list of all staters and their PINS by querying the DB."""
    with pyodbc.connect(
        "DRIVER={driver};SERVER=tcp:{server};PORT=1433;DATABASE={database};UID={username};PWD={password}".format_map(
            creds
        )
    ) as conn:
        with conn.cursor() as cursor:
            cursor.execute(QUERY)
            return cursor.fetchall()


def get_staters():
    """Load list of staters from disk. If not found, query and save."""
    try:
        with open(QUERY_RESULTS_FILE) as f:
            return json.load(f)

    except (FileNotFoundError, JSONDecodeError):
        with open(QUERY_RESULTS_FILE, "w") as f:
            staters = {
                str(int(r[0])): dict(zip(FIELDS[1:], r[1:])) for r in update_staters()
            }
            json.dump(staters, f)
            return staters

if __name__ == "__main__":
    get_staters()
