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

my_version = "3.6.87"     # log_heading_common
import os, sys
import sqlite3
from sqlite3 import IntegrityError, OperationalError, Row
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

from calibre_plugins.quarantine_and_scrub.heading import log_heading_common



notifications = Queue()

mynothing = ""

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

    #----------------------------------------------------------------------------------------------------------------
def util_purge_work_data(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


    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)
    except Exception as e:
        return (self,gui,None,None,None)

    my_cursor = my_db.cursor()

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

    header_s1 =  str("SQLite Version: " + str(sqlite3.version))
    header_s3 = "Beginning Purge of All Work Data"

    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)

    log(" ")
    log("Erasing All Work Custom Column Data plus Transient Q&S Table Data")
    log(" ")

    notifications.put((0.01, 'Erasing All Work Custom Column Data plus Transient Q&S Table Data'))

    #----------------------------------------------------------------------------------------------
    try:
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM custom_column_4  "
        my_cursor.execute (mysql)
        my_db.commit()

        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM books_custom_column_4_link  "
        my_cursor.execute (mysql)
        my_db.commit()

        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM custom_column_8  "
        my_cursor.execute (mysql)
        my_db.commit()

        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM books_custom_column_8_link  "
        my_cursor.execute (mysql)
        my_db.commit()

        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM custom_column_10  "
        my_cursor.execute (mysql)
        my_db.commit()

        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM books_custom_column_10_link  "
        my_cursor.execute (mysql)
        my_db.commit()

        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM custom_column_12  "
        my_cursor.execute (mysql)
        my_db.commit()

        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM custom_column_13  "
        my_cursor.execute (mysql)
        my_db.commit()

        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM books_custom_column_13_link  "
        my_cursor.execute (mysql)
        my_db.commit()

        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM custom_column_15  "
        my_cursor.execute (mysql)
        my_db.commit()

        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM custom_column_16 "
        my_cursor.execute (mysql)
        my_db.commit()

        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM custom_column_18 "
        my_cursor.execute (mysql)
        my_db.commit()

        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM books_custom_column_18_link "
        my_cursor.execute (mysql)
        my_db.commit()

        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM _books_work  "
        my_cursor.execute (mysql)
        my_db.commit()

        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM _tags_work  "
        my_cursor.execute (mysql)
        my_db.commit()

        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM _instr_title_author  "
        my_cursor.execute (mysql)
        my_db.commit()

        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM _instr_series_series "
        my_cursor.execute (mysql)
        my_db.commit()

        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM _tags_by_author "
        my_cursor.execute (mysql)
        my_db.commit()

        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM _tags_by_comment_candidates "
        my_cursor.execute (mysql)
        my_db.commit()

        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM _tags_by_series "
        my_cursor.execute (mysql)
        my_db.commit()

        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM _book_awards_mapping "
        my_cursor.execute (mysql)
        my_db.commit()


    except Exception as e:
        print(mysql)
        print(str(e))
        raise e


    log("All Work Data Has Been Purged")
    log(" ")

    my_cursor.execute ("BEGIN TRANSACTION")

    mysql = "UPDATE sqlite_sequence SET seq = 1 WHERE name = 'custom_column_4'  ;"  #work_author
    my_cursor.execute (mysql)
    mysql = "UPDATE sqlite_sequence SET seq = 1 WHERE name = 'books_custom_column_4_link'  ;"  #work_author
    my_cursor.execute (mysql)
    mysql = "UPDATE sqlite_sequence SET seq = 1 WHERE name = 'custom_column_8'  ;"   #work_title
    my_cursor.execute (mysql)
    mysql = "UPDATE sqlite_sequence SET seq = 1 WHERE name = 'books_custom_column_8_link'  ;"  #work_title
    my_cursor.execute (mysql)
    mysql = "UPDATE sqlite_sequence SET seq = 1 WHERE name = 'custom_column_10'  ;"   #work_series
    my_cursor.execute (mysql)
    mysql = "UPDATE sqlite_sequence SET seq = 1 WHERE name = 'books_custom_column_10_link'  ;"   #work_series
    my_cursor.execute (mysql)
    mysql = "UPDATE sqlite_sequence SET seq = 1 WHERE name = 'custom_column_12'  ;"   #work_series_number
    my_cursor.execute (mysql)
    mysql = "UPDATE sqlite_sequence SET seq = 1 WHERE name = 'custom_column_13'  ;"  #work_tags
    my_cursor.execute (mysql)
    mysql = "UPDATE sqlite_sequence SET seq = 1 WHERE name = 'books_custom_column_13_link'  ;"   #work_tags
    my_cursor.execute (mysql)
    mysql = "UPDATE sqlite_sequence SET seq = 1 WHERE name = 'custom_column_15'  ;"   #work_series_full
    my_cursor.execute (mysql)
    mysql = "UPDATE sqlite_sequence SET seq = 1 WHERE name = 'custom_column_16'  ;"   #work_freeze
    my_cursor.execute (mysql)
    mysql = "UPDATE sqlite_sequence SET seq = 1 WHERE name = 'custom_column_18'  ;"   #status
    my_cursor.execute (mysql)
    mysql = "UPDATE sqlite_sequence SET seq = 1 WHERE name = 'books_custom_column_18_link'  ;"  #status
    my_cursor.execute (mysql)
    mysql = "UPDATE sqlite_sequence SET seq = 1 WHERE name = '_books_work'  ;"
    my_cursor.execute (mysql)
    mysql = "UPDATE sqlite_sequence SET seq = 1 WHERE name = '_tags_work'  ;"
    my_cursor.execute (mysql)
    mysql = "UPDATE sqlite_sequence SET seq = 1 WHERE name = '_instr_series_series'  ;"
    my_cursor.execute (mysql)

    my_db.commit()

    log("Work Tables: Sequence Numbers Reset to 1")
    log(" ")

    my_db.close()

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


    #END OF purgework.py