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

__my_version__ = "4.97.0"   #this value should be changed only when new sqlite objects are created for the latest new plugin version by the developer.
#the __my_version__ = value must be incremented at the second (not third) sequence number so the version string comparisons will compare (e.g. less than) properly.

import os, sys
import apsw
from calibre.library.database import LibraryDatabase
import datetime, re, sre_constants
from calibre.db.backend import DB
from calibre import isbytestring, force_unicode, prints
from calibre.constants import iswindows, filesystem_encoding, preferred_encoding
from Queue import Empty, Queue
from calibre.gui2.threaded_jobs import ThreadedJob, BaseJob
from calibre.utils.logging import Log as log
from calibre.gui2 import error_dialog, info_dialog
import codecs
from time import sleep
import time
import unicodedata
from copy import deepcopy

notifications = Queue()

mynothing = ""

user_db_current_version = "1.0.0"

from calibre_plugins.quarantine_and_scrub.sqlobjects_global_authors import add_global_authors
from calibre_plugins.quarantine_and_scrub.sqlobjects_book_awards import add_book_awards
from calibre_plugins.quarantine_and_scrub.sqlobjects_global_pseudonyms import add_global_pseudonyms

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

    #----------------------------------------------------------------------------------------------------------------
def util_create_sqlite_objects(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 mynothing
    global __my_version__
    global user_db_current_version

    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 =apsw.Connection(path)
        #my_db = sqlite3.connect(path)
    except Exception as e:
        log(str(e))
        return (self,None,None,None)

    my_cursor = my_db.cursor()


    header_s1 =  str("SQLite Version: " + str(apsw.SQLITE_VERSION_NUMBER) + "  [APSW]")   #e.g. 3.8.4

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

    header_s3 = "Adding or Changing SQLite Tables and Views plus Other Q&S DB Maintenance."
    log_heading_common(log,header_s1,header_s2,header_s3,header_s4,header_s5)

    mysql = "PRAGMA main.busy_timeout = 5000;"      #PRAGMA busy_timeout = milliseconds;  (obviously just for this connection)
    my_cursor.execute(mysql)

    notifications.put((0.50, 'Creating SQLite Objects '))

    #----------------------------------------------------------------------------------------------

    user_db_current_version = str("1.0.0")

    __my_version__ = str(__change_tuple_to_bytestring(__my_version__))

    mysql = "SELECT version FROM _qs_database_status"
    mysql = str(mysql)
    try:
        my_cursor.execute(mysql)
        tmp_rows = my_cursor.fetchall()     #will get the first row from the query
        if tmp_rows:
            for row in tmp_rows:
                user_db_current_version = str(row)
                user_db_current_version = str(__change_tuple_to_bytestring(user_db_current_version))
                if user_db_current_version.endswith("."):
                    user_db_current_version = str(user_db_current_version + "0")
                break
            #END FOR
        else:
            try:
                mysql = str("INSERT OR REPLACE INTO _qs_database_status (id,version) VALUES (null, '" + __my_version__ + "');")
                my_cursor.execute("begin")  #apsw
                my_cursor.execute(mysql)
                my_cursor.execute("commit")
                user_db_current_version = str(__my_version__)
                sleep(1.0)
            except Exception as e:
                raise e
                log(str(e))
                my_db.close()
                return
    except Exception as e:
        raise e
        log(str(e))
        my_db.close()
        return

    user_db_current_version = str(user_db_current_version)

    if str(user_db_current_version) < str("4.96.0") :
        log("Current DB Version is:  ", str(user_db_current_version))
        log("Your Q&S Database Is Obsolete Due to Lack of Routine Upgrades.")
        log("Your Current DB Cannot Be Upgraded.")
        log("You Must Redownload the Official Q&S Library, and Begin to Use That Instead.")
        log("This Library Can No Longer Be Used Due to Its Lack of Ongoing Maintenance.")
        my_db.close()
        return

    if  str(user_db_current_version) < str(__my_version__):
        log("Q&S Database Needs An Upgrade")
        log("Current DB Version is:  ", str(user_db_current_version))
        log("DB Will Be Upgraded to: ", str(__my_version__))
        try:
            mysql = str("DELETE FROM  _qs_database_status WHERE id > '0' ")
            my_cursor.execute("begin")  #apsw
            my_cursor.execute (mysql)
            my_cursor.execute("commit")
            sleep(1.0)
            #--------------------------------
            mysql = str("INSERT INTO _qs_database_status (id,version) VALUES (null, '" + __my_version__ + "');")
            my_cursor.execute("begin")  #apsw
            my_cursor.execute (mysql)
            my_cursor.execute("commit")
            print(mysql)
            sleep(1.0)
            #--------------------------------
            upgrade_control(my_db, my_cursor, log)
            #--------------------------------
            my_db.close()
            log(" ")
            log(" ")
            log("Job complete.")
            return
        except Exception as e:
            log(str(e))
            my_db.close()
            raise e
            return
    else:
        log(" ")
        log(" ")
        log("Q&S Database Has Already Been Upgraded to the Latest Release Level.")
        do_miscellany(my_db, my_cursor, log)     #tweaks and failsafes
        log("Q&S Database has been updated as (or if) needed.")
        my_db.close()
        log(" ")
        log(" ")
        log("Job complete.")
        return

    #-----------------------------------------------------------------------------------------------------------------
    #-----------------------------------------------------------------------------------------------------------------
def upgrade_control(my_db, my_cursor, log):

    log("Creating  any new Tables and Views.")

    do_miscellany(my_db, my_cursor, log)


    #-----------------------------------------------------------------------------------------------------------------
    #-----------------------------------------------------------------------------------------------------------------
    #-----------------------------------------------------------------------------------------------------------------
    #-----------------------------------------------------------------------------------------------------------------
    #-----------------------------------------------------------------------------------------------------------------
def do_miscellany(my_db, my_cursor, log):

    try:
        my_cursor.execute("commit")    #to avoid any job failures due to an outstanding BEGIN TRANSACTION created elsewhere
    except:
        pass

    try:
        add_book_awards(my_db, my_cursor, log)
    except:
        pass

    try:
        my_cursor.execute("commit")    #to avoid any job failures due to an outstanding BEGIN TRANSACTION created elsewhere
    except:
        pass

    try:
        add_global_pseudonyms(my_db, my_cursor, log)
        log(" ")
        log(" ")
        log("New Global Pseudonyms were added if they did not already exist.")
    except:
        pass

    try:
        my_cursor.execute("commit")    #to avoid any job failures due to an outstanding BEGIN TRANSACTION created elsewhere
    except:
        pass

    try:
        add_global_authors(my_db, my_cursor, log)
    except:
        pass

    log(" ")
    log(" ")
    log("New Global Authors were added if they did not already exist.")
    log(" ")
    log(" ")
    log("New Book Awards have been added if they did not already exist")
    log(" ")
    log(" ")
    log("Be sure to run Author-Level Scrubbing soon, as that job first performs necessary format maintenance on new Global Authors, Pristine Authors, and the Authors in the Book Awards table.")
    log(" ")
    log(" ")

    try:
        my_cursor.execute("commit")    #to avoid any job failures due to an outstanding BEGIN TRANSACTION created elsewhere
    except:
        pass

    my_cursor.execute("\
                            BEGIN TRANSACTION;\
                            DELETE FROM _global_authors WHERE name IN(SELECT name from _global_series);\
                            DELETE FROM _global_author_series_link WHERE authid NOT IN(SELECT id from _global_authors);\
                            DELETE FROM _global_author_series_link WHERE seriesid NOT IN(SELECT id from _global_series);\
                            DELETE FROM _pristine_authors WHERE name IN(SELECT name from _pristine_series);\
                            DELETE FROM _pristine_authors WHERE name IN(SELECT name from _global_series);\
                            DELETE FROM _pristine_authors WHERE name IN(SELECT name from _global_authors);\
                            DELETE FROM _pristine_series WHERE name IN(SELECT name from _global_series);\
                            DELETE FROM _pristine_author_series_link WHERE authid NOT IN(SELECT id from _pristine_authors);\
                            DELETE FROM _pristine_author_series_link WHERE seriesid NOT IN(SELECT id from _pristine_series);\
                            COMMIT;")

    log(" ")
    log(" ")
    log("Any (Redundant) Pristine Authors or Series Already in the Global Authors or Series Tables Were Removed.")
    log(" ")
    log(" ")
    sleep(0.05)

    try:
        my_cursor.execute("commit")    #to avoid any job failures due to an outstanding BEGIN TRANSACTION created elsewhere
    except:
        pass

    try:
        tweak_miscellaneous_tables(my_db, my_cursor, log)
    except:
        pass

    try:
        ensure_new_objects_exist(my_db, my_cursor, log)
    except:
        pass



   #-----------------------------------------------------------------------------------------------------------------
def tweak_miscellaneous_tables(my_db, my_cursor, log):

    #Version 4.97.0 - changed work_series_full from a non-standard to a standard combination of attributes.  2015-12-29
    try:
        my_cursor.execute("\
                            BEGIN TRANSACTION;\
                            UPDATE custom_columns SET datatype = 'comments' WHERE id = 15 AND label = 'work_series_full' AND normalized = 0 AND datatype = 'text' ; \
                            COMMIT;")

    except:
        pass

   #-----------------------------------------------------------------------------------------------------------------
def ensure_new_objects_exist(my_db, my_cursor, log):

    try:
        my_cursor.execute("commit")    #to avoid any job failures due to an outstanding BEGIN TRANSACTION created elsewhere
    except:
        pass

    try:
        my_cursor.execute("\
        BEGIN TRANSACTION;\
        CREATE TABLE IF NOT EXISTS _tag_cc_mapping_control ( \
        id    INTEGER PRIMARY KEY AUTOINCREMENT,\
        tag TEXT NOT NULL,\
        cc_number INTEGER  NOT NULL  DEFAULT 20 CHECK(cc_number>19),\
        datatype TEXT NOT NULL DEFAULT 'text' CHECK(datatype = 'text' OR datatype = 'comments'),\
        rule TEXT NOT NULL  DEFAULT '0',\
        cc_value TEXT DEFAULT NULL,\
        active BOOL NOT NULL  DEFAULT 0  ,\
        UNIQUE (tag,cc_number) ); \
        COMMIT;")
    except Exception as e:
        log("Error: ", str(e))
        pass

    try:
        my_cursor.execute("\
                            BEGIN TRANSACTION;\
                            INSERT OR IGNORE INTO _qs_table_version VALUES ('_tag_cc_mapping_control', '4.96.0','data: user maintained') ; \
                            COMMIT;")
    except Exception as e:
        log("Error: ", str(e))
        pass

    try:
        my_cursor.execute("\
        BEGIN TRANSACTION;\
        INSERT OR IGNORE INTO _tag_cc_mapping_control VALUES(NULL,'Fiction:Dystopian',20,'text','T1',NULL,0);\
        INSERT OR IGNORE INTO _tag_cc_mapping_control VALUES(NULL,'Fiction:Dystopian-Other',20,'text','V1','output_tag_rule_v1',0);\
        COMMIT;")
    except Exception as e:
        pass

    try:
        my_cursor.execute("commit")    #to avoid any job failures due to an outstanding BEGIN TRANSACTION created elsewhere
    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.

    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)
    n1 = s_string.find("(")
    n2 = s_string.find(")")
    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
    else:
        pass

    #__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


    #-----------------------------------------------------------------------------------------------------------------
    #END OF sqlobjects.py