import sqlite3
import sys
import re

insert = """
INSERT INTO tools (tool, pocket, X, Y, Z, A, B, C, U, V, W, D, frontangle, backangle, orientation, description)
VALUES (:Tool, :Pocket, :X, :Y, :Z, :A, :B, :C, :U, :V, :W, :Diameter, :Frontangle, :Backangle, :Orientation, ":Comment");
"""

schema = """
BEGIN TRANSACTION;
DROP TABLE IF EXISTS tools;
CREATE TABLE tools (
    tool        INTEGER PRIMARY KEY UNIQUE NOT NULL,
    pocket      INTEGER NOT NULL,
    X           REAL DEFAULT 0.0,
    Y           REAL DEFAULT 0.0,
    Z           REAL DEFAULT 0.0,
    A           REAL DEFAULT 0.0,
    B           REAL DEFAULT 0.0,
    C           REAL DEFAULT 0.0,
    U           REAL DEFAULT 0.0,
    V           REAL DEFAULT 0.0,
    W           REAL DEFAULT 0.0,
    D           REAL DEFAULT 0.0,
    frontangle  REAL DEFAULT NULL,
    backangle   REAL DEFAULT NULL,
    orientation INTEGER DEFAULT NULL,
    description TEXT DEFAULT ""
);
COMMIT;
"""

def dict_into_query(q, d):
    "replaces each occurence of :key in the input file with the textual representation of the value of d[key]"

    f = re.search(':([\w]+)', q)
    while f:
        if f.group(1) in d:
            if d[f.group(1)]:
                q = q[:f.start()] + str(d[f.group(1)]) + q[f.end():]
            else:
                q = q[:f.start()] + "NULL" + q[f.end():]
        else: #Tags that are in the query, but not the dict
            q = q[:f.start()] + "NULL" + q[f.end():]
        f = re.search(':([\w]+)', q)
    return q

if len(sys.argv) < 3:
    print("usage: tool.tbl new.sqlite")
    sys.exit(0)

toolfile = open(sys.argv[1])
toolinfo = toolfile.readlines()
toolfile.close()

db = sqlite3.connect(sys.argv[2])
db.executescript(schema)

for t in toolinfo:
    tool = {'Tool':re.search('[Tt]([0-9+-.]+) ', t),
            'Pocket':re.search('[Pp]([0-9+-.]+)', t),
            'X':re.search('[Xx]([0-9+-.]+)', t),
            'Y':re.search('[Yy]([0-9+-.]+)', t),
            'Z':re.search('[Zz]([0-9+-.]+)', t),
            'A':re.search('[Aa]([0-9+-.]+)', t),
            'B':re.search('[Bb]([0-9+-.]+)', t),
            'C':re.search('[Cc]([0-9+-.]+)', t),
            'U':re.search('[Uu]([0-9+-.]+)', t),
            'V':re.search('[Vv]([0-9+-.]+)', t),
            'W':re.search('[Ww]([0-9+-.]+)', t),
            'Diameter':re.search('[Dd]([0-9+-.]+)', t),
            'Frontangle':re.search('[Ii]([0-9+-.]+)', t),
            'Backangle':re.search('[Jj]([0-9+-.]+)', t),
            'Orientation':re.search('[Qq]([0-9+-.]+)', t),
            'Comment':re.search(';(.*)', t)}
    for m in tool:
        if tool[m]:
            tool[m] = tool[m].group(1)
    db.executescript(dict_into_query(insert, tool))