#!/usr/bin/env python3

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

config = ConfigParser()
config.read('.taguette_gdocs')

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

## 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')

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

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

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: '{id}' is not unique, SKIPPING")
    elif len(tag_info) == 0:
        print(f"ERROR: 'tag with ID {id}' does not exist, SKIPPING")
    else:
        oldname = tag_info[0][1]
        old_description = tag_info[0][2]

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

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

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

con.commit()
con.close()