# -*- coding: utf-8 -*-
from __future__ import (unicode_literals, division, absolute_import,
                        print_function)
__license__   = 'GPL v3'
__copyright__ = '2014, DaltonST <DaltonShiTzu@outlook.com>'

__my_version__ = "3.6.87"      #speed optimization
import os, sys
import sqlite3
from calibre.library.database import LibraryDatabase
from calibre.db.backend import DB
from calibre import isbytestring, force_unicode, prints
from calibre.constants import iswindows, filesystem_encoding, preferred_encoding
from sqlite3 import IntegrityError, OperationalError, Row
from Queue import Empty, Queue
from calibre.gui2.threaded_jobs import ThreadedJob, BaseJob
from calibre.utils.logging import Log as log
from time import sleep
import time

my_terminate_early = False

notifications = Queue()

mynothing = ""

from calibre_plugins.quarantine_and_scrub.heading import log_heading_common
header_s1 = None
header_s2 = None
header_s3 = None
header_s4 = None
header_s5 = None
    #----------------------------------------------------------------------------------------------------------------
    #Copy Author and Series Plus Link to Q&S metadata.com Into Tables _pristine_xxxxx
    #----------------------------------------------------------------------------------------------------------------
    #----------------------------------------------------------------------------------------------------------------
def util_copy_pristine_metadata(self, guidb, log=None, abort=None, notifications=True):  # <<<<====called from jobs.py
    global header_s1
    global header_s2
    global header_s3
    global header_s4
    global header_s5
    global my_terminate_early

    db = guidb
    path = db.library_path
    if isbytestring(path):
        path = path.decode(filesystem_encoding)
    path = path.replace(os.sep, '/')
    path = os.path.join(path, 'metadata.db')
    path = path.replace(os.sep, '/')
    print(path)
    log(path)
    try:
        my_db = sqlite3.connect(path)   #sqlite not apsw
    except Exception as e:
        return

    my_cursor = my_db.cursor()

    header_s1 =  str("SQLite Version: " + str(sqlite3.version))

    mysql = str("PRAGMA main.locking_mode=EXCLUSIVE;")
    my_cursor.execute(mysql)
    header_s2 = mysql

    header_s3 = "Copying Pristine Author Names to Q&S Pristine Author Validation Table."
    log_heading_common(log,header_s1,header_s2,header_s3,header_s4,header_s5)

    notifications.put((0.01, 'Attaching Calibre Pristine Database'))
    Attach_Calibre_metadata_db(my_db, my_cursor, path, notifications, log)

    sleep(1.0)

    if not my_terminate_early:
        Util_copy_control(my_db, my_cursor, notifications, log)
        Detach_Calibre_metadata_db(my_db, my_cursor, notifications, log)
        my_db.close()
    else:
         my_db.close()


    #----------------------------------------------------------------------------------------------
def Util_copy_control(my_db, my_cursor, notifications,log):

    global my_terminate_early

    log("Copying Pristine Authors to Q&S metadata.db")
    notifications.put((0.0001, 'Copying Pristine Authors to Q&S metadata.db'))

    copy_pristine_authors(my_db, my_cursor, notifications, log)

    log("Copying Pristine Series to Q&S metadata.db")
    notifications.put((0.81, 'Copying Pristine Series to Q&S metadata.db'))

    copy_pristine_series(my_db, my_cursor, notifications, log)

    log("Copying Pristine Author/Series Links to Q&S metadata.db")
    notifications.put((0.90, 'Copying Pristine Author/Series Links to Q&S metadata.db'))

    copy_pristine_author_series_link(my_db, my_cursor, notifications, log)

    log("Finished Copying Pristine Data to Q&S metadata.db")
    notifications.put((0.99, 'Finished Copying Pristine Data to Q&S metadata.db'))

    my_db.close()

    #----------------------------------------------------------------------------------------------
def copy_pristine_authors(my_db, my_cursor, notifications, log):

    global my_terminate_early

    sleep(1.0)

    try:
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM main._pristine_authors WHERE id >= 0 "
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        log(str(e))
        print(str(e))
        my_terminate_early = True
        raise e

    sleep(1.0)

    my_cursor.execute ("BEGIN TRANSACTION")
    mysql = "INSERT or IGNORE INTO main._pristine_authors SELECT * FROM Calibre.authors   ;"
    my_cursor.execute (mysql)
    my_db.commit()

    log("Completed Copying Pristine Authors.  Continuing.")

    sleep(1.0)

    try:
        mysql = "REINDEX _pristine_authors_index"
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e



    #----------------------------------------------------------------------------------------------
def copy_pristine_series(my_db, my_cursor, notifications, log):

    global my_terminate_early

    sleep(2.0)

    try:
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM main._pristine_series WHERE id >= 0 "
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        log(str(e))
        print(str(e))
        my_terminate_early = True
        raise e

    sleep(2.0)

    try:
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "INSERT or IGNORE INTO main._pristine_series SELECT * FROM Calibre.series WHERE id > 0 "
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        log(str(e))
        print(str(e))
        my_terminate_early = True
        raise e

    sleep(1.0)

    try:
        mysql = "REINDEX _pristine_series_index"
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e



     #----------------------------------------------------------------------------------------------
def copy_pristine_author_series_link(my_db, my_cursor, notifications, log):

    global my_terminate_early

    sleep(1.0)

    try:
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM main._pristine_author_series_link WHERE id >= 0 "
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        log(str(e))
        print(str(e))
        my_terminate_early = True
        raise e

    sleep(2.0)

    try:
        mysql =  "INSERT OR IGNORE INTO main._pristine_author_series_link (id,authid,seriesid) SELECT DISTINCT null, \
              (SELECT id FROM Calibre.authors WHERE authors.id IN (SELECT author FROM Calibre.books_authors_link \
              WHERE book = books.id)) authid,(SELECT id FROM Calibre.series WHERE series.id IN (SELECT series \
              FROM Calibre.books_series_link WHERE book=books.id)) seriesid FROM Calibre.books WHERE seriesid >= 0  ;"

        my_cursor.execute ("BEGIN TRANSACTION")
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        log(mysql)
        log(str(e))
        raise e

    sleep(2.0)

    try:
        mysql = "REINDEX _pristine_author_series_link_index_a"
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    sleep(1.0)

    try:
        mysql = "REINDEX _pristine_author_series_link_index_b"
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e


  #----------------------------------------------------------------------------------------------------------------
def Attach_Calibre_metadata_db(my_db, my_cursor, path, notifications, log):

    global my_terminate_early

    sleep(0.5)

    from calibre_plugins.quarantine_and_scrub.config import prefs
    pristine_path = prefs['pristine_library']
    log(" ")
    pristine_path = pristine_path.replace(os.sep, '/')
    log("Configured Pristine Library Path is: " + str(pristine_path))
    log(" ")
    path = path.replace(os.sep, '/')
    log("Current Q&S Library Path is: " + str(path))
    log(" ")

    if pristine_path == path:
        my_terminate_early = True
        print("ERROR.  Pristine Calibre Library Cannot Be The Q+S Library!")
        log("ERROR.  Pristine Calibre Library Cannot Be The Q+S Library!")
        return

    try:
        s1 = "ATTACH DATABASE '"
        s2 =  "'  As 'Calibre';"
        mysql = s1 + pristine_path + s2
        if isbytestring(mysql):
            mysql = mysql.decode(filesystem_encoding)
        my_cursor.execute(mysql)
        print("Calibre Pristine Library metadata.db has been properly attached")
        log("Calibre Pristine Library metadata.db has been properly attached")
    except Exception as e:
        print("Calibre Pristine Library metadata.db has NOT been properly attached")
        print(mysql)
        print(str(e))
        s = str(e)
        log(s)
        my_db.close()
        print("terminating early...")
        log("terminating early...")
        my_terminate_early = True
        return

    #now test the connection using a Calibre metadata.db table...
    mysql = "select id from Calibre.books where id < 1000000 ;"
    mysql = str(mysql)
    try:
        my_cursor.execute(mysql)
        tmp_rows = my_cursor.fetchone() #will get the first row from the query
        n = len(tmp_rows)
        if n > 0:
            del tmp_rows
            return
        else:
            print("Calibre metadata.db has NOT been properly attached")
            log("Calibre metadata.db has NOT been properly attached")
            my_db.close()
            print("terminating early...")
            log("terminating early...")
            my_terminate_early = True
            return
    except Exception as e:
        print("Calibre metadata.db has NOT been properly attached")
        print(str(e))
        s = str(e)
        log(s)
        my_db.close()
        print("terminating early...")
        log("terminating early...")
        my_terminate_early = True
        return

    #----------------------------------------------------------------------------------------------------------------
def Detach_Calibre_metadata_db(my_db, my_cursor, notifications, log):

    try:
        mysql = "DETACH DATABASE 'Calibre' "
        my_cursor.execute(mysql)
        log("Calibre Pristine Library metadata.db has been detached")
        print("Calibre Pristine Library metadata.db has been detached")
    except:
        pass

 #---------------------------------------------------------------------------------------------------------------------------------------------
def __change_tuple_to_bytestring(tuple):
    #do not ever remove spaces here;  for commas,  only remove unicode special character combinations that may contain a comma.

    #example:   (u'Microsoft Word - New Microsoft Office Word Document',)
    #example:   (u"Saurabh's Account",)
    #example:   (u"Saurabh's Account", u"Account, Saurabh's")  <<=== keep the possessive 's
    #example:   '(199)' should be simply  '199'  in mysql
    #example:   (199) should be simply 199
    #example:   Sarah Miles'  should be simply  Sarah Miles
    #example:   Matt Scudder)  should be simply Matt Scudder
    #example:  (u'Hope Over Fear (Over #1)',)) should be simply  Hope Over Fear (Over #1)
    #example:   u"The Wizard King: Heart's Desire Book 3") should be simply   The Wizard King: Heart's Desire Book 3

    #but:  do NOT break this:  Follow the Queen: A Menage, Sexy and Short Romance)  <<< trailing ) that is not matched at all on the left.

    global mynothing

    s_string = str(tuple)

    s_string = s_string.replace('(u"', mynothing, 100)
    s_string = s_string.replace("(u'", mynothing, 100)  #example: (u'Hope Over Fear (Over #1)',)) becomes Hope Over Fear (Over #1)',))

    s_string = s_string.replace("',))", mynothing, 100)  #example: Hope Over Fear (Over #1)',)) becomes Hope Over Fear (Over #1)
    s_string = s_string.replace("',)", mynothing, 100)  #example:   (u"Saurabh's Account",)
    s_string = s_string.replace('",)', mynothing, 100)

    s_string = s_string.replace('u"', mynothing, 100)   #example:   (u"Saurabh's Account", u"Account, Saurabh's")  <<=== keep the possessive 's
    s_string = s_string.replace("u'", mynothing, 100)

    s_string = s_string.replace("'(", "'", 100)  #example:   '(199)' should be simply  '199'  in mysql
    s_string = s_string.replace(")'", "'", 100)

    s_string = s_string.replace('u"', mynothing, 100)  #example:   u"The Wizard King: Heart's Desire Book 3")
    s_string = s_string.replace('")', mynothing, 100)

    #return if string is good at this point.  example:  Hope Over Fear (Over #1)
    #__printsafe("in strip 1: ", s_string)
    s_string = str(s_string)
    n1 = s_string.find("(")  #left
    n2 = s_string.find(")")  #right
    #__printsafe("left:", str(n1), " right:", str(n2))
    if n1 >= 0:
        if n2 >= 0 and n2 > n1:   #example:  Hope Over Fear (Over #1)     ... matched set ...
            #__printsafe("in strip 2 returning: ", s_string)
            return s_string
        else:  #example:  Hope Over Fear ) (Over #1                                        ... not a matched set ...
            pass
    n1 = s_string.count("(")  #left
    n2 = s_string.count(")")  #right
    #__printsafe("left:", str(n1), " right:", str(n2))
    if n2 == 2 and n1 == 1:   # don't break:  Follow the Queen: A Menage, Sexy and Short Romance)   within the unicode    ('xxxxxxxxxxxxxxxx)',)
        __printsafe(str(s_string))
        return s_string  #otherwise, the following logic would strip  e) leaving Romanc
    if n2 == 1 and n1 == 0:   # don't break:  Follow the Queen: A Menage, Sexy and Short Romance)   within the unicode    ('xxxxxxxxxxxxxxxx)',)
        __printsafe(str(s_string))
        return s_string  #otherwise, the following logic would strip  e) leaving Romanc

    #__printsafe("in strip 3 NOT returning: ", s_string)

    n = s_string.find("(")
    if n == 0: #example:  is (199)
        s_string = s_string.replace("(", mynothing, 1)  #now is 199)
        c = s_string.count(")")  #example:  is 199)
        if c == 1:
            s_string = s_string.replace(")", mynothing, 1)  #example:  now 199
        else:
            pass

    #must do each of these twice
    n = s_string.rfind("'")  #s_string =  Sarah Miles', so n == 11
    l = len(s_string)  #l = 12
    if n == (l - 1):  # 11 = 12 - 1, so True
        z = (n - 1)  # 11 - 1 = 10
        s_string = s_string[0:z]  # now  Sarah Miles not Sarah Miles'
    else:
        pass

    n = s_string.rfind(")")  #s_string =  Matt Scudder) , so n == 13
    l = len(s_string)  #l = 14
    if n == (l - 1):  # 13 = 14 - 1, so True
        z = (n - 1)  # 11 - 1 = 10
        s_string = s_string[0:z]  # now  Matt Scudder not Matt Scudder)
        pass

    #must do each of these twice
    n = s_string.rfind("'")  #s_string =  Sarah Miles', so n == 11
    l = len(s_string)  #l = 12
    if n == (l - 1):  # 11 = 12 - 1, so True
        z = (n - 1)  # 11 - 1 = 10
        s_string = s_string[0:z]  # now  Sarah Miles not Sarah Miles'
    else:
        pass

    n = s_string.rfind(")")  #s_string =  Matt Scudder) , so n == 13
    l = len(s_string)  #l = 14
    if n == (l - 1):  # 13 = 14 - 1, so True
        z = (n - 1)  # 11 - 1 = 10
        s_string = s_string[0:z]  # now  Matt Scudder not Matt Scudder)
        pass

    s_string = s_string.strip()

    if isinstance(s_string, unicode):
        s_string = str(s_string)
        if isinstance(s_string, unicode):
            pass
            __printsafe("s_string is still a unicode object! [2]", s_string)

    return s_string


 #----------------------------------------------------------------------------------------------------------------
def __strip_numerics(s):

    global mynothing

    s = str(s)

    s = __change_tuple_to_bytestring(s)

    s = s.replace(',', mynothing, 4)
    s = s.replace("(", mynothing, 4)
    s = s.replace(")", mynothing, 4)
    s = s.replace(" ", mynothing, 10)
    s = s.strip()

    return s

#END of copypristine.py