#!/usr/bin/env python3

import requests
from csv import DictReader
import sqlite3
from configparser import ConfigParser
import re
import json
import os

config_files = [f for f in os.listdir() if f.startswith('.taguette_gdocs')]

for file_path in config_files:

    config = ConfigParser()
    config.read(file_path)

    # this is project ID from the configuration
    project_id = int(config['General']['taguette_project_id'])
    taguette_database_file = config['General']['taguette_database_file']

    ## connect to sqlite3
    con = sqlite3.connect(taguette_database_file)
    cur = con.cursor()

    # load the googgle sheet ID from the configuration
    gsheet_id = config['General']['gsheet_id']
    gsheet_gid = config['General']['gsheet_gid']

    # get the spreadsheet data
    axial_url = f"https://docs.google.com/spreadsheets/d/{gsheet_id}/export?format=csv&id={gsheet_id}&gid={gsheet_gid}"
    rv = requests.get(axial_url)
    csv_text = rv.content.decode('utf-8')

    # import taguette.database as tagdb
    # db = tagdb.connect("sqlite:////home/mako/taguette-snapshot-20210422-1.sqlite3")

    # ditionary to ensure that we don't have duplicate tags and to handle merges
    seen_paths = {}
    for row in DictReader(csv_text.splitlines(), delimiter=","):
        #print(row)
        tag_id = row['id']
        new_name = row['tag']
        axial_code = row['axial codes']
        category = row['category']
        description = row['description']

        sql_stmt_get = "SELECT id, path, description from TAGS where id = ? AND project_id = ?"
        cur.execute(sql_stmt_get, (tag_id, project_id))
        tag_info = cur.fetchall()

        if len(tag_info) > 1:
            print(f"ERROR: '{tag_id}' is not unique, SKIPPING")
        elif len(tag_info) == 0:
            print(f"ERROR: 'tag with ID {tag_id}' does not exist, SKIPPING")
        else:
            oldname = tag_info[0][1]
            old_description = tag_info[0][2]

            # delete any code and associated snipits
            if new_name == "DELETE":
                # delete all highlights
                sql_stmt_update = "DELETE FROM highlight_tags WHERE tag_id = ?"
                cur.execute(sql_stmt_update, (tag_id,))

                # delete the tag itself
                sql_stmt_update = "DELETE FROM tags WHERE project_id = ? AND id = ?"
                cur.execute(sql_stmt_update, (project_id, tag_id))

                print(f"DELETE TAG FROM {project_id}: {tag_id}")
                continue

            if axial_code: 
                new_name = axial_code.lower() + "_" + new_name.lower()
            else:
                new_name = new_name.lower()

            new_description = description
            if description and category:
                new_description += " "
            if category:
                new_description += json.dumps({'category' : category})

            # merge tags if we have two with the same name
            if new_name in seen_paths:
                # identify what we're merging into
                merge_keep_id = seen_paths[new_name]
                merge_delete_id = tag_id

                # reassociate all the highlight associated with tag 2 so they are associated with tag 1 intsead
                sql_stmt_update = "UPDATE highlight_tags SET tag_id = ? WHERE tag_id = ?"
                cur.execute(sql_stmt_update, (merge_keep_id, merge_delete_id))
                
                # reassociate all the highlight associated with tag 2 so they are associated with tag 1 intsead
                sql_stmt_update = "DELETE FROM tags WHERE project_id = ? AND id = ?"
                cur.execute(sql_stmt_update, (project_id, merge_delete_id))

                print(f"MERGE DUPLICATE TAGS for {project_id}: {new_name}")
                continue

            else:
                seen_paths[new_name] = tag_id

            if not oldname == new_name:
                sql_stmt_update = "UPDATE tags SET path = ? WHERE project_id = ? AND id = ?"
                print(f"UPDATE TAG for {project_id}: {oldname} → {new_name}")
                cur.execute(sql_stmt_update, (new_name, project_id, tag_id))
                
            if new_description.strip() != old_description.strip():
                sql_stmt_update = "UPDATE tags SET description = ? WHERE project_id = ? AND id = ?"
                print(f"UPDATE DESC for {project_id}: {old_description} → {new_description}")
                cur.execute(sql_stmt_update, (new_description, project_id, tag_id))

    con.commit()
    con.close()