import json
import sqlite3
import pandas as pd
import csv


def load_data_from_csv(name, end=58925):
    data = []
    keys = None
    with open(name, "r", encoding="utf-8", errors="ignore") as f:
        csv_data = csv.reader(f)
        for i, line in enumerate(csv_data):
            if i == 0:
                keys = line
                continue
            item = {}
            for key, val in zip(keys, line):
                item[key] = val
            data.append(item)
    return data


def load_data_from_csv_to_db(name, conn, col_names=None):

    # read the dataset from csv file and create a pandas dataframe
    df = pd.read_csv(open(name, "r", encoding="utf-8", errors="ignore"))

    df.columns = [
        'state', 'parliamentary_constituency', 'constituency', 'nota_votes', 'candidate_name', 'party_name', 'total_votes' 
    ]

    # removing extra whitespace
    string_columns = df.select_dtypes(include=['object']).columns
    for col in string_columns:
        df[col] = df[col].astype(str).str.strip()

    df['constituency'] = df['constituency'].str.replace(r'\s*-\s*\d+$', '', regex=True)
    
    # Remove any parenthetical suffixes like (SC) or (ST)
    df['constituency'] = df['constituency'].str.replace(r'\s*\([^)]*\)', '', regex=True)

    # save the dataframe as a database table, name of table is: elections_2019
    result = df.to_sql("elections_2019", conn, if_exists="replace")

    return result


def query_sql(conn, query):
    cursor = conn.cursor()
    cursor.execute(query)
    result = cursor.fetchall()
    field_names = [r[0] for r in cursor.description]
    print(field_names)
    return result


if __name__ == '__main__':
    # create a connection to sql db called elections.db
    conn = sqlite3.connect('../data/elections.db')

    filename = r"../data/details_of_assembly_segment_2019.csv"
 
    data = load_data_from_csv(filename, end=5)

    res = load_data_from_csv_to_db(filename, conn)

    query = "SELECT * FROM elections_2019 LIMIT 5;"
    results = query_sql(conn, query)
    print(results)

    # keys = data.keys()
    # for i, item in enumerate(data):
    #     print(data[item])
    # jdata = json.loads(data.to_json())
    # print(jdata)