#!/usr/bin/env python3

import requests
from csv import DictReader
import sqlite3

gsheet_id = "1bfKljA7vw2V4yKrowxCPLdYaBrTzty5_O7msbuFw7Nc"
gsheet_gid = "0"

## this is the hardcoded project id
project_id = 13


## 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-working.sqlite3')
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']

    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 row['Axial Codes']:
            newname = row['Axial Codes'].lower() + "." + new_name.lower()
        else:
            newname = new_name.lower()

        if not oldname == newname:
            #print(tag_id)
            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 row["description"].strip() != old_description.strip():
            sql_stmt_update = "UPDATE tags SET description = ? WHERE project_id = ? AND id = ?"
            cur.execute(sql_stmt_update, (row['description'], project_id, tag_id))
            print(f"UPDATE DESC: {old_description} → {row['description']}")

con.commit()
con.close()