# -*- coding: utf-8 -*-
from .. import LANG
from ..log import logger
from ctypes import c_int32
from binascii import crc32
from sqlite3 import dbapi2 as sqlite
from threading import Thread, Lock
from .EPGImport import ISO639_associations

LANG = ISO639_associations.get(LANG, 'eng')
_lock = Lock()


class epgdb_class(object):

	events = ()

	def __init__(self, source_name, source_priority, epgdb_file):
		self.epgdb = epgdb_file
		try:
			self.connection = sqlite.connect(self.epgdb, timeout=10, isolation_level='DEFERRED', check_same_thread=False)
			self.connection.create_function("int32", 1, lambda d: c_int32(d).value)
			self.connection.create_function("getid", 2, self.get_table_id)
			self.connection.text_factory = lambda x: x.encode('utf-8')
			self.cursor = self.connection.cursor()
			self.tmpcursor = self.connection.cursor()
                        self.cursor.executescript("""
PRAGMA journal_mode = OFF;
PRAGMA synchronous = 0;
PRAGMA cache_size = 1000000;
PRAGMA temp_store = MEMORY;
CREATE INDEX IF NOT EXISTS unique_title_hash ON T_Title (hash);
CREATE INDEX IF NOT EXISTS unique_short_hash ON T_Short_Description (hash);
CREATE INDEX IF NOT EXISTS unique_extended_hash ON T_Extended_Description (hash);
""")
			self.cursor.execute("SELECT id FROM T_Source WHERE source_name=? AND priority=?", (source_name, source_priority,))
			row = self.cursor.fetchone()
			if row:
				self.source_id = row[0]
			else:
				with self.connection:
					self.cursor.execute("INSERT INTO T_Source (source_name, priority) VALUES (?,?)", (source_name, source_priority,))
					self.source_id = self.cursor.lastrowid

			logger.debug("[EPGDB] connect to {epgdb} finished".format(**self.__dict__))
		except Exception as err:
			raise ValueError("[EPGDB] connect to %s failed: %s" % (self.epgdb, err))

	def get_table_id(self, table, value):
		if not value:
			return None
		hash = crc32(value)
		self.tmpcursor.execute("SELECT id FROM %s WHERE hash=?" % table, (hash,))
		row = self.tmpcursor.fetchone()
		if row:
			return row[0]
		else:
			self.tmpcursor.execute("INSERT INTO %s (hash, %s) VALUES(?,?)" % (table, table[2:].lower()), (hash, value,))
			return self.tmpcursor.lastrowid

	def processServices(self, services):
		if not self.events:
			return
		with self.connection:
			threads = [Thread(target=self.processEvents, args=(service, self.events,),) for service in services]
			for t in threads: t.start()
			for t in threads: t.join()
			self.events = ()

	def processEvents(self, service, events):
		# sid:tsid:onid:dvbnamespace
		# {:04x}:{:04x}:{:04x}:{:08x} -> {uint16}:{uint16}:{uint16}:(int32)
		sref = tuple(int(x, 16) for x in service.split(":")[3:7])
		with _lock:
			self.cursor.execute("SELECT id FROM T_Service WHERE sid=? AND tsid=? AND onid=? AND dvbnamespace=int32(?)", sref)
			service = self.cursor.fetchone()
			if service:
				service_id = service[0]
			else:
				self.cursor.execute("INSERT INTO T_Service (sid, tsid, onid, dvbnamespace) VALUES(?,?,?,int32(?))", sref)
				service_id = self.cursor.lastrowid
		# Tuple of tuples
		# (starttime, duration, title, short_descr, long_descr, event_type, dvb_event_id, [(lang, parent_rating),])
		for event in events:
			with _lock:
				self.cursor.execute("SELECT id FROM T_Event WHERE service_id=? AND begin_time=?", (service_id, event[0],))
				row = self.cursor.fetchone()
				if row:
					# Update data for an existing event
					cmd = "UPDATE T_Data SET short_description_id=getid(?,?), extended_description_id=getid(?,?) WHERE ROWID=?"
					self.cursor.execute(cmd, ('T_Short_Description', event[3], 'T_Extended_Description', event[4], row[0]))
				else:
					# Create new event
					cmd = "INSERT INTO T_Event (service_id, begin_time, duration, source_id, dvb_event_id) VALUES(?,?,?,?,?)"
					self.cursor.execute(cmd, (service_id, event[0], event[1], self.source_id, event[0] & 0xffff))
					cmd = "INSERT INTO T_Data (event_id, title_id, short_description_id, extended_description_id, iso_639_language_code) VALUES(?,getid(?,?),getid(?,?),getid(?,?),?)"
					self.cursor.execute(cmd, (self.cursor.lastrowid, 'T_Title', event[2], 'T_Short_Description', event[3], 'T_Extended_Description', event[4], LANG))


	def final_process(self):
		self.cursor.executescript("""
DROP INDEX IF EXISTS unique_title_hash;
DROP INDEX IF EXISTS unique_short_hash;
DROP INDEX IF EXISTS unique_extended_hash;
PRAGMA incremental_vacuum;
PRAGMA analysis_limit=400;
PRAGMA optimize;
""")
		logger.debug("[EPGDB] Total changes made in {epgdb}: {connection.total_changes}".format(**self.__dict__))
		self.cursor.close()
		self.tmpcursor.close()
		self.connection.close()
		logger.debug("[EPGDB] Finished generating the SQL file {epgdb}".format(**self.__dict__))

	def flushEPGfile(self):
		logger.debug("[EPGDB] empty existing {epgdb}".format(**self.__dict__))
		connection = sqlite.connect(self.epgdb, timeout=10)
		cursor = connection.cursor()
		tables = list(cursor.execute("SELECT name FROM sqlite_master WHERE TYPE IS 'table' AND name<>'T_Source'"))
		cursor.executescript(';'.join(["DELETE FROM %s" % i[0] for i in tables]))
		cursor.execute("VACUUM")
		cursor.close()
		connection.close()
