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

__my_version__ = "3.6.70"
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 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
import codecs
from time import sleep
import time
import datetime
import unicodedata
from difflib import SequenceMatcher
from copy import deepcopy

from calibre_plugins.quarantine_and_scrub.heading import log_heading_common

notifications = Queue()
mynothing = ""

my_guidb = ""

current_library_path = ""

header_s1 = None
header_s2 = None
header_s3 = None
header_s4 = None
header_s5 = None

# ---------------------------------------------------------------------------------------
#  Import Validation Data from qs_service_pack.db Created from authormetadata.db into Q&S metadata.db
# ---------------------------------------------------------------------------------------

def main_import_from_qs_service_pack(self, guidb, am_path, log=None, abort=None, notifications=True):

    global mynothing
    global my_guidb
    global current_library_path
    global header_s1
    global header_s2
    global header_s3
    global header_s4
    global header_s5

    notifications.put((0.01, 'Beginning to Import Data from qs_service_pack.db'))

    am_path = am_path.replace(os.sep, '/')
    __printsafe(am_path)

    #----------------------------------------------------------------------------------------------------------------
    db = guidb
    path = db.library_path
    __printsafe(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, '/')
    current_library_path  = path
    log("Library DB: " + path)
    __printsafe(path)
    try:
        my_db =apsw.Connection(path)
    except Exception as e:
        __printsafe(str(e))
        log(str(e))
        raise e
        return

    my_cursor = my_db.cursor()

    my_guidb = guidb

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

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

    sleep(0.5)

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

    Import_Control(my_db, my_cursor, notifications, log, am_path)

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



    print("Job complete.")
    log(" ")
    log(" ")
    log(" ")
    log(" ")
    log("=============")
    log("Job complete.")

    return

    #----------------------------------------------------------------------------------------------------------------
def Import_Control(my_db, my_cursor, notifications, log, am_path):
    global header_s1
    global header_s2
    global header_s3
    global header_s4
    global header_s5
    global current_library_path

    header_s3 = "Importing qs_service_pack.db Validation Data into QuarantineAndScrub"
    header_s4 = "This is being done for Q&S library:       " + str(current_library_path)
    header_s5 = 'Importing Validation Data from db file:   ' + str(am_path)
    log_heading_common(log,header_s1,header_s2,header_s3,header_s4,header_s5)

    was_created = create_tmp_table(my_db, my_cursor, notifications, log)
    if was_created:
        was_attached = attach_am_db(my_db, my_cursor, notifications, log, am_path)
        if was_attached:
            was_copied = copy_am_data_to_tmp_table(my_db, my_cursor, notifications, log)
            if was_copied:
                detach_am_db(my_db, my_cursor, notifications, log, am_path)
                copy_tmp_table_to_final_qs_tables(my_db, my_cursor, notifications, log)
                my_db.close()
            else:
                return
        else:
            return
    else:
        return

    #----------------------------------------------------------------------------------------------------------------
def attach_am_db(my_db, my_cursor, notifications, log, am_path):

    try:
        s1 = "ATTACH DATABASE '"
        s2 =  "'  As '[NAME]';"
        s2 = str(s2.replace("[NAME]", str("AM"), 1))
        path = str(am_path)
        path = str(path.replace(os.sep, '/'))
        mysql = s1 + path + s2
        if isbytestring(mysql):
            mysql = mysql.decode(filesystem_encoding)
        my_cursor.execute(mysql)
        log("qs_service_pack.db has been properly attached:     " + str(path))
        return True
    except Exception as e:
        log("qs_service_pack.db has NOT been properly attached")
        log(mysql)
        print(str(e))
        s = str(e)
        log(s)
        my_db.close()
        print("terminating early...")
        log("terminating early...")
        return False

    #----------------------------------------------------------------------------------------------------------------
def detach_am_db(my_db, my_cursor, notifications, log, am_path):

    try:
        mysql = "DETACH 'AM'"
        my_cursor.execute (mysql)
        log("Detached: " + str(am_path))
    except:
        log("NOT Detached: " + str(am_path))
        pass

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

    try:
        my_cursor.execute("begin")        #apsw
        mysql = 'CREATE TEMP TABLE author_series_books \
            ("id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "authid" INTEGER NOT NULL , "name" TEXT NOT NULL ,\
            "seriesid" INTEGER, "seriesname" TEXT, "bookid" INTEGER NOT NULL , "title" TEXT NOT NULL , \
            "awardscount" INTEGER NOT NULL  DEFAULT 0, "published" TEXT DEFAULT 0, \
            UNIQUE (authid,seriesid,bookid) ON CONFLICT REPLACE ) '
        my_cursor.execute(mysql)           #apsw
        my_cursor.execute("commit")     #apsw
        #log("Temporary table was created")
        sleep(0.05)
        my_cursor.execute("begin")        #apsw
        mysql = "CREATE TEMP VIEW view_temp_a AS SELECT id,name FROM _global_authors WHERE name IN(SELECT name FROM temp.author_series_books WHERE name NOT NULL) "
        my_cursor.execute(mysql)           #apsw
        mysql = "CREATE TEMP VIEW view_temp_s AS SELECT id,name FROM _global_series WHERE name IN(SELECT seriesname FROM temp.author_series_books WHERE seriesname NOT NULL) "
        my_cursor.execute(mysql)           #apsw
        my_cursor.execute("commit")     #apsw
        sleep(0.05)
        my_cursor.execute("begin")        #apsw
        mysql = "CREATE TEMP VIEW view_temp_all AS SELECT id AS authorid,name AS authorname,\
                        (SELECT id FROM view_temp_s WHERE name IN(SELECT seriesname FROM temp.author_series_books \
                        WHERE name = view_temp_a.name AND temp.author_series_books.seriesname NOT NULL) ) AS seriesid,\
                        (SELECT name FROM view_temp_s WHERE name IN(SELECT seriesname FROM temp.author_series_books \
                        WHERE name = view_temp_a.name AND temp.author_series_books.seriesname NOT NULL) ) AS seriesname \
                         FROM view_temp_a WHERE seriesid NOT NULL ORDER BY authorname,seriesname"
        my_cursor.execute(mysql)           #apsw
        my_cursor.execute("commit")     #apsw
        #log("Temporary views were created")
        sleep(0.05)
        return True
    except Exception as e:
        log("ERROR in creating temporary table")
        log(str(e))
        log(str(mysql))
        my_db.close()
        return False

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

    try:
        my_cursor.execute("begin")        #apsw
        mysql = "INSERT OR IGNORE INTO  temp.author_series_books SELECT * FROM AM.author_series_books_awardscount"
        my_cursor.execute(mysql)           #apsw
        my_cursor.execute("commit")     #apsw
        #log("Data was copied from qs_service_pack.db to a Temporary Table")
        return True
    except Exception as e:
        log("ERROR in copying Q&S metadata.db")
        log(str(e))
        log(str(mysql))
        my_db.close()
        return False

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

    my_cursor.execute("begin")        #apsw
    mysql = "INSERT OR REPLACE INTO _global_authors SELECT null,name,' ',' ' FROM temp.author_series_books GROUP BY authid"
    my_cursor.execute(mysql)           #apsw
    my_cursor.execute("commit")     #apsw
    log("New Global Authors were created")
    sleep(0.5)


    my_cursor.execute("begin")        #apsw
    mysql = "INSERT OR REPLACE INTO _global_series SELECT null,seriesname,seriesname FROM temp.author_series_books WHERE seriesname NOT NULL GROUP BY seriesname"
    my_cursor.execute(mysql)           #apsw
    my_cursor.execute("commit")     #apsw
    log("New Global Series were created")
    sleep(0.5)


    my_cursor.execute("begin")        #apsw
    mysql = "INSERT OR REPLACE INTO _global_author_series_link SELECT null,authorid,seriesid FROM view_temp_all WHERE authorid NOT NULL AND seriesid NOT NULL"
    my_cursor.execute(mysql)           #apsw
    my_cursor.execute("commit")     #apsw
    log("New Global Authors for New Global Series were linked")
    sleep(0.5)

    date_added = str(datetime.datetime.now())

    my_cursor.execute("begin")        #apsw
    mysql = "INSERT OR IGNORE INTO _global_web_series_detail SELECT name,seriesname,title,'0','qs_service_pack.db',?  \
                    FROM temp.author_series_books WHERE seriesname NOT NULL AND title NOT NULL AND name NOT NULL"
    my_cursor.execute(mysql,([date_added]))           #apsw
    my_cursor.execute("commit")     #apsw
    log("Global Web Series Detail Table was updated with new WSSVD")
    sleep(0.05)

    log(" ")
    log(" ")
    log("Please run Author-Level Scrubbing now (even if you have no books to scrub), since that job will first perform necessary maintenance on the New Global Authors.")
    log(" ")
    log(" ")
    log("This identical process needs to be performed individually for each of your QuarantineAndScrub libraries.")
    log(" ")
    log(" ")
    #----------------------------------------------------------------------------------------------------------------
    #----------------------------------------------------------------------------------------------------------------
    #----------------------------------------------------------------------------------------------------------------
    #----------------------------------------------------------------------------------------------------------------
    #----------------------------------------------------------------------------------------------------------------

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





    #----------------------------------------------------------------------------------------------------------------
def __printsafe(*arg):
    try:
        print(arg)
        pass
    except:
        pass
        print("...cannot be printed...")
    #----------------------------------------------------------------------------------------------------------------
    #----------------------------------------------------------------------------------------------------------------
    #----------------------------------------------------------------------------------------------------------------
    #----------------------------------------------------------------------------------------------------------------
    #END of service_pack.py
    #=================================================================================

