#   This is a component of LinuxCNC
#   Copyright 2011, 2013, 2014 Dewey Garrett <dgarrett@panix.com>,
#   Michael Haberler <git@mah.priv.at>
#
#   This program is free software; you can redistribute it and/or modify
#   it under the terms of the GNU General Public License as published by
#   the Free Software Foundation; either version 2 of the License, or
#   (at your option) any later version.
#
#   This program is distributed in the hope that it will be useful,
#   but WITHOUT ANY WARRANTY; without even the implied warranty of
#   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#   GNU General Public License for more details.
#
#   You should have received a copy of the GNU General Public License
#   along with this program; if not, write to the Free Software
#   Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
#
import os
import re
import pyodbc
import emc # for ini file access only
import sys, traceback

class SqlToolAccess(object):
    '''
    beginnings of ODBC tooltable access
    '''

    def __init__(self,inifile,random_toolchanger):
        print("SQL tt init")
        self.inifile = inifile
        self.random_toolchanger = random_toolchanger
        self.persist =  int(self.inifile.find("TOOL", "SAVE_TOOLSTATE") or 0)

        self.connectstring = self.inifile.find("TOOL", "ODBC_CONNECT")
        conn = pyodbc.connect(self.connectstring)
        cursor = conn.cursor()
        print("tables in database %s:" % (self.connectstring))
        for row in cursor.tables():
            print(row.table_name)
        cursor.close()
        conn.close()


    def load_table(self, tooltable,comments,fms):
        ''' populate the table'''
        try:
            conn = pyodbc.connect(self.connectstring)
            conn.autocommit = True

            cursor = conn.cursor()
            cursor.execute("select * from tools;")

            for row in cursor.fetchall():
                pocket = row.pocket
                t = tooltable[pocket]
                t.toolno = row.toolno
                t.orientation = row.orientation
                t.diameter = row.diameter
                t.frontangle = row.frontangle
                t.backangle = row.backangle
                t.offset.tran.x = row.x_offset
                t.offset.y = row.y_offset
                t.offset.z = row.z_offset
                t.offset.a = row.a_offset
                t.offset.b = row.b_offset
                t.offset.c = row.c_offset
                t.offset.u = row.u_offset
                t.offset.v = row.v_offset
                t.offset.w = row.w_offset

        except pyodbc.Error as e:
            traceback.print_exc(file=sys.stdout)
        else:
            start = 0 if self.random_toolchanger else 1
            for p in range(start,len(tooltable)):
                t = tooltable[p]
                if t.toolno != -1: print(str(t))
        finally:
            cursor.close()
            conn.close()

    def save_table(self, tooltable, comments,fms):
        try:
            conn = pyodbc.connect(self.connectstring)
            conn.autocommit = True
            cursor = conn.cursor()
            cursor.execute("delete from tools;")
            start = 0 if self.random_toolchanger else 1
            for p in range(start,len(tooltable)):
                t = tooltable[p]
                if t.toolno != -1:
                    cursor.execute("insert into tools values(?,?,?,?,?,?,'?',?,?,?,?,?,?,?,?,?);",
                                        (t.toolno, p, t.diameter, t.backangle,t.frontangle,t.orientation,
                                         comment[p],t.offset.x,t.offset.y,t.offset.z,t.offset.a,t.offset.b,
                                         t.offset.c,t.offset.u,t.offset.v,t.offset.w))
        except pyodbc.Error as msg:
            print("saving tooltable failed:")
            traceback.print_exc(file=sys.stdout)
        finally:
            cursor.close()
            conn.close()

    def save_state(self,e):
        if not self.persist:
            return
        print("SQL save_state", end=" ")
        try:
            conn = pyodbc.connect(self.connectstring)
            conn.autocommit = True
            cursor = conn.cursor()
            cursor.execute("delete from state;")
            cursor.execute("insert into state (tool_in_spindle,pocket_prepped) values(?,?)", e.io.tool.toolInSpindle,e.io.tool.pocketPrepped)
            print("done")

        except pyodbc.Error as msg:
            print("save_state() failed:")
            traceback.print_exc(file=sys.stdout)
        finally:
            cursor.close()
            conn.close()


    def restore_state(self,e):
        if not self.persist:
            return

        try:
            conn = pyodbc.connect(self.connectstring)
            conn.autocommit = True
            cursor = conn.cursor()
            if not cursor.tables(table='state').fetchone():
                print("trying to restore, but no 'state' table found")
                return
            row = cursor.execute("select tool_in_spindle,pocket_prepped from state;").fetchone()
            if row:
                e.io.tool.pocketPrepped = row.pocket_prepped
                e.io.tool.toolInSpindle = row.tool_in_spindle
                print("restored tool=%d pocketPrepped=%d" % (row.tool_in_spindle,row.pocket_prepped))
            else:
                print("no saved state record found")

        except pyodbc.Error as msg:
            print("restore_state() failed:")
            traceback.print_exc(file=sys.stdout)
        finally:
            cursor.close()
            conn.close()