# encoding=utf-8
"""
Manages sqlite3 connection and db
"""

__author__ = "Roland Trouville"
__copyright__ = "Copyright 2015+, Consortium MonPaGe"
__license__ = "Creative Commons 4.0 By-Nc-Sa"
__maintainer__ = "Roland Trouville"
__email__ = "contact.monpage@gmail.com"
__status__ = "Production"


import logging
import os
import shutil
import sqlite3
from datetime import datetime
from typing import Any, Callable, Optional, Sequence

from tools.general_tools import GeneralTools
from tools.options import Options
from tools.ui_tools import UITools

logger = logging.getLogger(__name__)


class DBManager(object):
	"""
	Database manager for MonPaGe application.

	This class handles SQLite database connections, operations, and maintenance.
	It provides functionality for:
	- Managing database connections and executing SQL queries
	- Initializing and updating user result databases
	- Creating and maintaining result tables for various data types
	- Backing up databases during updates
	- Handling database schema migrations

	The class supports creating dictionaries from query results and manages
	foreign key constraints as needed during operations.
	"""

	COTATION_DB: Optional["DBManager"] = None

	CHECK_TABLE_EXISTANCE_SQL: str = (
		"SELECT name FROM sqlite_master WHERE type='table' AND name=?"
	)

	@staticmethod
	def get_cotation_db() -> "DBManager":
		"""
		Retrieve the singleton instance of the cotation database manager.

		If the cotation database connection does not exist yet, this method
		initializes it by opening the database file at './data/cotation/cotation.db'.

		Returns:
			DBManager: The shared instance managing the cotation database.
		"""
		logger.info("Getting cotation database")
		if DBManager.COTATION_DB is None:
			logger.info("Opening new connection for cotation database")
			DBManager.COTATION_DB = DBManager("./data/cotation/cotation.db")

		return DBManager.COTATION_DB

	# Tables to create in the user's database
	RESULT_TABLES_TO_CREATE = [
		"results_pw",
		"results_int",
		"results_qa",
		"results_acou",
		"step_data",
		"step_input_data",
	]

	# Tables to remove from the user's database
	RESULT_TABLES_TO_REMOVE = [
		"results_praat_qa",
		"results_praat_pw",
		"results_devscore",
	]

	# Required fields types that need to be present in the user's database
	# (table_name, field_name, field_type)
	FIELDS_TO_CHECK = [
		("results_pw", "version", "TEXT DEFAULT NULL"),
		("results_pw", "phonem", "TEXT DEFAULT NULL"),
		("results_qa", "version", "TEXT DEFAULT NULL"),
		("results_int", "word_order", "INTEGER NOT NULL DEFAULT 0"),
		("results_int", "version", "TEXT DEFAULT NULL"),
		("results_acou", "erreur", "TEXT DEFAULT NULL"),
	]

	def __init__(self, filename: str):
		"""
		Initialize a DBManager instance by connecting to the specified SQLite database file.

		Args:
			filename (str): Path to the SQLite database file to connect to.

		Notes:
			- If the filename ends with 'cotation.db', the instance is marked as the reference cotation database.
			- Enables foreign key support on the SQLite connection.
		"""
		logger.info(f"Connecting to '{filename}'")
		self._filename = filename
		self.__connection__ = sqlite3.connect(filename)
		# Flag used to know if the current instance is the COTATION_DB constant
		self.is_reference_db = filename.endswith("cotation.db")

		if self.is_reference_db:
			logger.info("Reference database detected")

		self.__connection__.row_factory = sqlite3.Row
		self.set_foreign_keys(True)
		self.__cursor__ = self.__connection__.cursor()

	def __enter__(self):
		"""
		Support context management protocol for the DBManager.

		Returns:
			DBManager: The current instance to be used in a 'with' statement.
		"""
		return self

	def __exit__(self, exc_type, exc_value, traceback):
		"""
		Ensure proper cleanup when exiting the context.

		Closes the database connection unless the instance is the reference cotation database,
		which remains open to prevent unintended closure.

		Args:
			exc_type (type): Exception type if raised within the context.
			exc_value (Exception): Exception instance raised within the context.
			traceback (traceback): Traceback object.
		"""
		# Prevent COTATION_DB closing
		if not self.is_reference_db:
			logger.info(f"Closing connection to '{self._filename}'")
			self.__connection__.close()

	def set_foreign_keys(self, keys_on: bool):
		"""
		Sets the foreign key constraints on or off
		:param keys_on: wether we want foreign keys checks on (True) or off (False)
		"""
		logger.info("Setting foreign keys param to " + ("ON" if keys_on else "OFF"))
		if keys_on:
			self.__connection__.execute("pragma foreign_keys=ON")
		else:
			self.__connection__.execute("pragma foreign_keys=OFF")

	def commit(self):
		"""
		Commit any pending transaction to the database.
		If autocommit is True, or there is no open transaction, this method does nothing.
		If autocommit is False, a new transaction is implicitly opened if a pending transaction was committed by this method.
		"""
		logger.info("Committing transaction")
		self.__connection__.commit()

	def get_dump(self):
		"""
		Return an iterator to dump the database as SQL source code
		:return: an iterator to the dump
		"""
		logger.info(f"Dumping database '{self._filename}'")
		return self.__connection__.iterdump()

	def execute_script(self, sql: str):
		"""
		Execute a multi command sql script
		:param sql: the sql script.
		:return: a pointer to the result
		"""
		logger.info(f"Executing script\n-----\n{sql}\n-----")
		return self.__cursor__.executescript(sql)

	def execute(
		self, sql: str, values: Optional[Sequence[Any]] = None
	) -> sqlite3.Cursor:
		"""
		Executes a simple sql query
		:param sql: the sql query
		:param values: the tuple of values to be binded in the query
		:return: a pointer to the result
		"""
		logger.info(f"Executing query _{sql}_ with params {values}")
		try:
			return self.__cursor__.execute(sql, values if values is not None else ())
		except sqlite3.Error as er:
			logger.error(f"Error executing query: {er}")
			UITools.error_log(er, f"db_manager.execute() \n{sql}\n{values}")
			raise er

	@staticmethod
	def create_user_cotation_db(
		target_filename: str,
	) -> "DBManager":
		"""
		Will create or update an existing user cotation database in the participant/username folder
		If the new database has to be created, it will be a copy of the cotation main database with two additional
		tables to store results of the pseudoword cotation and the question cotation.

		If the main (reference) cotation database is newer than the already existing user database,
		we update the user database after making a backup of the existing user database
		"""

		logger.info(f"Creating user cotation DB at '{target_filename}'")

		reference_db = DBManager.get_cotation_db()
		target_db = DBManager(target_filename)

		if not os.path.isfile(target_filename) or os.path.getsize(target_filename) < 1000:
			# Had to add the size check because initializing via DBManager create an empty file so the condition was
			# always false
			logger.info(f"Updating user cotation DB at '{target_filename}' from cotation DB baseline")
			# If the .db file doesn't exist or not a regular file
			# We get a dump of the cotation main database and create a sql string that we then execute in user database
			sql_dump = "\n".join(reference_db.get_dump())

			# Disabling foreign keys to be able to import the dump without violating constraints
			target_db.set_foreign_keys(False)
			target_db.execute_script(sql_dump)
			target_db.commit()
			target_db.set_foreign_keys(True)
		else:
			logger.info("The target database exists already")
			user_database_modification_time = os.path.getmtime(target_filename)
			logger.info(
				f"User database last modification time: {user_database_modification_time}"
			)
			main_database_modification_time = os.path.getmtime(
				"./data/cotation/cotation.db"
			)
			logger.info(
				f"Main database last modification time: {main_database_modification_time}"
			)
			# We check main database modified time against user database modified time
			if (
				Options.is_enabled(Options.Option.FORCE_DB_UPDATE)
				or main_database_modification_time > user_database_modification_time
				or reference_db.is_update_needed(target_db)
			):
				logger.info("Updating user cotation database")
				reference_db._update_user_cotation_database(target_filename, target_db)

		target_db.create_result_tables()
		return target_db

	def _update_user_cotation_database(
		self,
		participant_db_filename: str,
		participant_result_db: "DBManager",
	):
		"""
		We update the user cotation database to be consistent with the newer version of the main database
		"""
		logger.info("Updating user cotation db " + participant_db_filename)

		# Backup of the user database before we update it
		now = datetime.now().isoformat("-", "minutes").replace(":", "")
		backup_name = participant_db_filename.replace(".db", "_backup" + now + ".db")
		logger.info(
			"Backuped result db " + participant_db_filename + " to " + backup_name,
		)
		logger.info(f"Backing up old user database to {backup_name}")
		shutil.copy(participant_db_filename, backup_name)
		logger.info("Backed up")
		participant_result_db.set_foreign_keys(False)
		tables = [
			"answer",
			"phonem",
			"pseudo_word",
			"pseudo_word_syllable",
			"question",
			"stimuli",
			"stimuli_question",
			"syllable",
			"syllable_phonem",
			"type_desc",
			"pseudo_word_phonem_special",
			"indicator",
			"indicator_norm",
			"last_update",
			"deviant_score",
			"deviant_score_norm",
			"step",
			"step_input",
		]
		views = []  # ["v_expected_results_pw"]
		# Reseting the auto increment index in the user database
		try:
			participant_result_db.execute("delete from sqlite_sequence")
			# Droping all the tables that we want to import from cotation main database
			for table in tables:
				try:
					participant_result_db.execute(f"DROP TABLE IF EXISTS {table}")
				except sqlite3.Error as e:
					logger.error(f"Cannot drop table {table}: {e}")
					logger.info("cannot drop table " + table)
			for view in views:
				try:
					participant_result_db.execute("DROP VIEW IF EXISTS ?", (view,))
				except sqlite3.Error as e:
					logger.error(f"Cannot drop view {view}: {e}")
					logger.info("cannot drop view " + view)
			# And now we dump and import the main database in the user database
			sql_dump = "\n".join(self.get_dump())

			participant_result_db.execute_script(sql_dump)
			participant_result_db.set_foreign_keys(True)

			participant_result_db.commit()
		except Exception as e:
			logger.error(f"Error updating user cotation database: {e}")
			UITools.error_log(e, "update_user_cotation_database")

	@staticmethod
	def _call_for_each_table(
		tables: list[str],
		f: Callable[[str], Any],
	):
		"""
		Execute a function on each table in a list if the table exists.

		This utility method checks if each table in the provided list exists,
		and calls the given function on each existing table.

		Args:
						tables: List of table names to check for existence
						f: Function to call for each existing table, taking the table name as its only parameter

		Returns:
						bool: True if at least one table existed and the function was called, False otherwise
		"""

		for table_name in tables:
			f(table_name)

	def _create_missing_tables(self):
		"""Creates missing tables in the database.

		This method checks for the existence of required tables (defined in RESULT_TABLES_TO_CREATE)
		and creates them if they are missing.

		Returns:
						bool: True if any table was created, False if no changes were made.
		"""

		logger.info(f"Creating missing tables in {self._filename}")

		self._call_for_each_table(
			DBManager.RESULT_TABLES_TO_CREATE,
			lambda table_name: self.execute(
				DBManager.get_sql_creation_script_for_predefined_tables(table_name)
			),
		)

	def _remove_deprecated_tables(self):
		"""Removes deprecated tables from the database.

		This method checks for the existence of deprecated tables (defined in RESULT_TABLES_TO_REMOVE)
		and drops them if found.

		Returns:
						bool: True if any table was removed, False if no changes were made.
		"""

		logger.info(f"Removing deprecated tables from {self._filename}")

		self._call_for_each_table(
			DBManager.RESULT_TABLES_TO_REMOVE,
			lambda table_name: self.execute(f"DROP TABLE IF EXISTS {table_name}"),
		)

	def _add_missing_fields(self):
		"""Adds missing fields to result tables.

		This method checks if certain fields are present in the result tables and adds them if missing.
		Fields to check are defined in the FIELDS_TO_CHECK class constant.

		Returns:
						bool: True if any table was modified, False otherwise.
		"""

		logger.info(f"Adding missing fields to {self._filename}")

		for table_name, field_name, field_type in DBManager.FIELDS_TO_CHECK:
			stmt = self.execute(
				"""
				SELECT name
				FROM sqlite_master
				WHERE type='table' AND name=? AND sql LIKE '%' || ? || '%'
				""",
				(table_name, field_name),
			)

			if stmt is None:
				continue

			existing_tables = stmt.fetchall()

			if len(existing_tables) == 0:
				self.execute(
					f"ALTER TABLE {table_name} ADD COLUMN {field_name} {field_type}",
				)

	def create_result_tables(self):
		"""
		Creates the three table used to store results
		"""

		logger.info(f"Creating result tables in {self._filename}")

		try:
			self._create_missing_tables()
			self._remove_deprecated_tables()

			# 17/2/2017
			# We check if there is the new v19 fields in the db
			self._add_missing_fields()

			self.commit()
		except Exception as e:
			UITools.error_log(e, "create_results_table()")

	@staticmethod
	def generate_table_insertion_query_with_bindings_sql_from_dict(
		dic: dict[str, Any], table_name: str
	) -> str:
		"""
		Generates an SQL query for inserting data into a table using dictionary keys as column names.

		This method creates an 'INSERT OR REPLACE INTO' SQL statement with proper bindings
		using the keys from the provided dictionary as column names.

		Args:
				dic: A dictionary whose keys represent column names
				table_name: The name of the target database table

		Returns:
				An SQL query string with bindings ready for parameter substitution
		"""
		return DBManager.generate_table_insertion_query_with_bindings_sql(
			list(dic.keys()), table_name
		)

	@staticmethod
	def generate_table_insertion_query_with_bindings_sql(
		column_names: list[str], table_name: str
	) -> str:
		"""
		Generates an SQL query for inserting data into a table using parameterized values.

		This method creates an 'INSERT OR REPLACE INTO' SQL statement with proper bindings
		using question mark placeholders for the values.

		Args:
						column_names: A list of column names to insert values for
						table_name: The name of the target database table

		Returns:
						An SQL query string with bindings ready for parameter substitution
		"""

		sql = "INSERT OR REPLACE INTO `" + table_name + "` (`"
		sql += ", ".join(column_names)
		sql += "`) VALUES ("
		sql += "?, " * (len(column_names) - 1) + "?"  # Skip last comma
		sql += ")"

		return sql

	@staticmethod
	def get_sql_creation_script_for_predefined_tables(table: str) -> str:
		"""
		Returns the SQL creation script for a predefined table.

		This method generates the SQL statement needed to create one of the
		predefined result tables listed in RESULT_TABLES_TO_CREATE.

		Args:
						table: The name of the table to generate the creation script for

		Returns:
						str: SQL creation script for the specified table

		Raises:
						SystemExit: If the table name is not recognized
		"""

		logger.info(f"Trying to get SQL creation script for '{table}'")

		if table == "results_pw":
			return """
			CREATE TABLE IF NOT EXISTS `results_pw`
			  (
			     `pseudo_word_id` INTEGER NOT NULL,
			     `syll_pos`       INTEGER NOT NULL,
			     `phon_pos`       INTEGER NOT NULL,
			     `session_path`   TEXT NOT NULL,
			     `participant`    TEXT NOT NULL,
			     `judge`          TEXT NOT NULL,
			     `is_cluster`     INTEGER NOT NULL DEFAULT 0,
			     `state`          INTEGER NOT NULL DEFAULT 0,
			     `effort`         INTEGER NOT NULL DEFAULT 0,
			     `inversion`      INTEGER NOT NULL DEFAULT 0,
			     `ajout`          INTEGER NOT NULL DEFAULT 0,
			     `error_type`     INTEGER NOT NULL DEFAULT 0,
			     `error_nature`   INTEGER NOT NULL DEFAULT 0,
			     `error_verbatim` TEXT DEFAULT NULL,
			     `version`        TEXT DEFAULT NULL,
			     `phonem`         TEXT DEFAULT NULL,
			     PRIMARY KEY(pseudo_word_id, syll_pos, phon_pos, session_path, participant,
			     judge),
			     FOREIGN KEY(`pseudo_word_id`) REFERENCES pseudo_word(id)
			  );
				"""
		elif table == "results_qa":
			return """
			CREATE TABLE IF NOT EXISTS `results_qa`
				  (
				     `question_id`  INTEGER NOT NULL,
				     `stimuli_id`   INTEGER NOT NULL,
				     `value`        INTEGER NOT NULL,
				     `session_path` TEXT NOT NULL,
				     `participant`  TEXT NOT NULL,
				     `judge`        TEXT NOT NULL,
				     `version`      TEXT DEFAULT NULL,
				     PRIMARY KEY(question_id, stimuli_id, session_path, participant, judge),
				     FOREIGN KEY(`question_id`) REFERENCES question(id),
				     FOREIGN KEY(`stimuli_id`) REFERENCES stimuli(id)
				  );
				"""
		elif table == "results_acou":
			return """
			CREATE TABLE IF NOT EXISTS `results_acou`
				  (
				     `indicator_id` INTEGER NOT NULL,
				     `value`        REAL NOT NULL,
				     `session`      TEXT NOT NULL,
				     `participant`  TEXT NOT NULL,
				     `judge`        TEXT NOT NULL,
				     `version`      TEXT DEFAULT NULL,
				     PRIMARY KEY(indicator_id, session, participant, judge),
				     FOREIGN KEY(`indicator_id`) REFERENCES indicator(id)
				  );
				"""
		elif table == "results_int":
			return """
			CREATE TABLE IF NOT EXISTS `results_int`
				  (
				     `word`         TEXT NOT NULL,
				     `word_order`   INTEGER NOT NULL DEFAULT 0,
				     `score`        FLOAT NOT NULL,
				     `session_path` TEXT NOT NULL,
				     `participant`  TEXT NOT NULL,
				     `judge`        TEXT NOT NULL,
				     `version`      TEXT DEFAULT NULL,
				     PRIMARY KEY(word, session_path, participant, judge)
				  );
				"""
		elif table == "step_data":
			return """
				CREATE TABLE IF NOT EXISTS step_data (
						id INTEGER PRIMARY KEY,
						step_id INTEGER NOT NULL,
						session_date TEXT NOT NULL,
						participant TEXT NOT NULL,
						judge TEXT NOT NULL,
						version TEXT NOT NULL,
						entry_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
						interval_start REAL,
						interval_duration REAL CHECK (interval_duration > 0),
						FOREIGN KEY (step_id) REFERENCES step(id),
						UNIQUE (step_id, session_date, participant, judge)
					);
				"""
		elif table == "step_input_data":
			return """
				CREATE TABLE IF NOT EXISTS step_input_data (
						step_input_id INTEGER,
						step_data_id INTEGER,
						info_value REAL NOT NULL,
						version TEXT NOT NULL,
						PRIMARY KEY (step_input_id, step_data_id),
						FOREIGN KEY (step_input_id) REFERENCES step_input(id),
						FOREIGN KEY (step_data_id) REFERENCES step_data(id)
					);
				"""
		else:
			logger.error(f"No script to create table {table}")
			exit(1)

	@staticmethod
	def __fuse_results_in__(source_db: "DBManager", target_db: "DBManager"):
		"""
		Fuses results from one database into another.

		This static method copies all data from the specified result tables in the
		source database to the target database. For each table in RESULT_TABLES_TO_CREATE
		that exists in the source database, this method:
		1. Retrieves all records
		2. Inserts them into the corresponding table in the target database

		Args:
						source_db: Source DBManager instance containing data to be copied
						target_db: Target DBManager instance where data will be inserted
		"""

		logger.info(f"Fusing results from {source_db} into {target_db}")
		for table_name in DBManager.RESULT_TABLES_TO_CREATE:
			stmt = source_db.execute(DBManager.CHECK_TABLE_EXISTANCE_SQL, (table_name,))

			assert stmt is not None

			table_exists = stmt.fetchone() is not None

			# TODO: And if it doesn't ?
			if table_exists:
				cursor = source_db.execute(f"SELECT * FROM {table_name}")
				assert cursor is not None  # Table exists, checked it in if statement
				cols: list[str] = [description[0] for description in cursor.description]
				values = cursor.fetchall()
				sql = DBManager.generate_table_insertion_query_with_bindings_sql(
					cols, table_name
				)

				if len(values) > 0:
					target_db.execute(sql, values)

				target_db.commit()

	@staticmethod
	def get_db_files_in_path(path: str):
		"""
		Searches for '.db' files in the specified directory (non-recursive) and returns
		a generator yielding matching file paths.

		Args:
			path (str): Directory path to search in.

		Returns:
			Iterator[str]: Generator yielding paths of files ending with '.db' in the directory.
		"""
		logger.info(f"Searching for .db files in {path}")
		return GeneralTools.find_files(path, ".db", False)

	@staticmethod
	def get_cotation_result_file(
		path: str,
	):
		"""
		Retrieves the cotation result file from a specified directory.

		This static method finds all database files in the given path, then filters
		them to include only .db files that don't contain "backup" in their filename.
		It ensures there is exactly one such file in the path.

		Args:
						path: Directory path to search for the cotation result file

		Returns:
						str: Path to the cotation result file

		Raises:
						Exception: If multiple qualifying database files are found in the path
		"""

		logger.info(f"Searching for resulting cotation database in {path}")

		db_files = filter(
			lambda filename: filename.endswith(".db") and "backup" not in filename,
			DBManager.get_db_files_in_path(path),
		)

		try:
			cotation_result_db = next(db_files)
		except StopIteration:
			logger.error(f"No cotation result file found in {path}")
			raise Exception(
				"Aucun fichier de resultats de cotation trouvé dans " + path
			)

		try:
			other_db = next(db_files)
			logger.error(
				f"Multiple cotation result files found in {path}: {', '.join([cotation_result_db + other_db] + list(db_files))}"
			)
			raise Exception(
				"Plusieurs fichiers de resultats de cotation trouvés dans " + path
			)
		except StopIteration:
			pass

		logger.info(f"The cotation result db found is {cotation_result_db}")

		return cotation_result_db

	def is_update_needed(self, participant_db: "DBManager") -> bool:
		"""
		Determines if the participant database needs updating.

		This method compares the 'last_update' timestamp of the source database (self)
		with the 'last_update' timestamp of the participant database to determine if
		an update is required.

		Args:
			participant_db: The participant database to check for updates

		Returns:
			bool: True if the participant database needs updating, False otherwise.
			Updates are needed when:
			- The participant database has no 'last_update' table
			- The source database has a newer 'last_update' timestamp
		"""

		logger.info(
			f"Checking if update is needed for participant db at {participant_db._filename}"
		)

		def get_last_update(db: "DBManager") -> Optional[str]:
			"""
			Retrieves the last update timestamp from the given database.

			This function checks if the 'last_update' table exists in the database,
			and if so, fetches the 'last_update' value. Returns None if the table
			does not exist or if an error occurs during the query.

			Args:
				db (DBManager): The database instance to query.

			Returns:
				Optional[str]: The last update timestamp as a string, or None if not available.
			"""
			logger.info(f"Getting last update of {db._filename}")
			stmt = db.execute(DBManager.CHECK_TABLE_EXISTANCE_SQL, ("last_update",))

			if stmt is None:
				return None

			if stmt.fetchone() is None:
				logger.info(f"Table last_update doesn't exist in {db._filename}")
				return None

			stmt = db.execute("select last_update from last_update")

			if stmt is None:
				logger.warning(
					f"Failed to get last update from {db._filename}, assuming needs updating"
				)
				return None

			res = stmt.fetchone()
			last_update = res["last_update"]

			logger.info(f"Last update of {db._filename} is {last_update}")

			return last_update

		source_db_last_update = get_last_update(self)
		participant_db_last_update: Optional[str] = None
		participant_db_needs_updating: bool = False

		try:
			participant_db_last_update = get_last_update(participant_db)
		except Exception:
			participant_db_needs_updating = True

		if participant_db_last_update is not None and source_db_last_update is not None:
			participant_db_needs_updating = (
				source_db_last_update > participant_db_last_update
			)

		logger.info(f"Participant db needs updating: {participant_db_needs_updating}")

		return participant_db_needs_updating
