# encoding=utf-8
"""
Class to manage exporting to TSV/CSV format
"""

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

from cffi.backend_ctypes import unicode

from tools.csv_manager import CSVManager
from tools.db_manager import DBManager
from tools.participant_manager import ParticipantManager


class ExportCSV(object):
	"""
	Class providing static methods to export various query results from a database into CSV format.

	Attributes:
	    queries (dict): SQL queries for different data exports keyed by descriptive names.
	"""

	queries = {
		"pw": """select rpw.participant, rpw.judge, rpw.session_path,rpw.pseudo_word_id, pw.ortho, pw.type,
	  rpw.syll_pos,
		s.api as syll_api, rpw.phon_pos, p.api as p_api, rpw.phonem as presented_phonem,p.consonnant, rpw.is_cluster, rpw.state,
		'' as state_label, rpw.effort, '' as effort_label,
		rpw.inversion,'' as inversion_label, rpw.ajout, '' as ajout_label, rpw.error_type,'' as error_type_label,
		 rpw.error_nature,'' as error_nature_label, rpw.error_verbatim, rpw.version
		from results_pw as rpw
		inner join pseudo_word as pw on pw.id = rpw.pseudo_word_id
		inner join pseudo_word_syllable as pws on pws.pseudo_word_id = pw.id and pws.position = rpw.syll_pos
		inner join syllable as s on s.id = pws.syllable_id
		inner join syllable_phonem as sp on sp.syllable_id = pws.syllable_id  and sp.position = rpw.phon_pos
		inner join phonem as p on p.id = sp.phonem_id
		left outer join pseudo_word_phonem_special as pwps on pwps.pseudo_word_id = pw.id and pwps.syllable_pos =
		pws.position and pwps.phonem_pos = sp.position
		WHERE (pwps.ignore IS NULL or pwps.ignore = 0)
		""",
		# left join results_praat_pw as rpp on rpp.pseudo_word_id = pw.id
		"pw_count": """  select pwrs.participant as participant,pwrs.session_path as session_path,  pwrs.judge as
	  judge,pw.type as type, pw.ortho as ortho, p.api as api, pwrs.pseudo_word_id as pseudo_word_id,
	  p.id as phonem_id, pwrs.syll_pos as syll_pos, pwrs.phon_pos as phon_pos,  'EN TROP' as status
				from results_pw as pwrs
				inner join pseudo_word as pw on pw.id = pwrs.pseudo_word_id
				inner join pseudo_word_syllable as pws on pws.pseudo_word_id = pw.id and pws.position =
				pwrs.syll_pos
				inner join syllable as s on s.id = pws.syllable_id
				inner join syllable_phonem as sp on sp.syllable_id = pws.syllable_id  and sp.position =
				pwrs.phon_pos
				inner join phonem as p on p.id = sp.phonem_id
				left outer join v_expected_results_pw_full as e_pwrs on e_pwrs.pseudo_word_id =
				pwrs.pseudo_word_id and e_pwrs.syll_pos = pwrs.syll_pos
				where e_pwrs.pseudo_word_id IS NULL

				UNION

				select e_pwrs.participant, e_pwrs.session_path, e_pwrs.judge, pw.type, pw.ortho, p.api,
				e_pwrs.pseudo_word_id, e_pwrs.phonem_id, e_pwrs.syll_pos, e_pwrs.phon_pos,  'MANQUANT' as status
				from v_expected_results_pw_full as e_pwrs
				inner join pseudo_word as pw on pw.id = e_pwrs.pseudo_word_id
				inner join phonem as p on p.id = e_pwrs.phonem_id
				left outer join results_pw as pwrs  on e_pwrs.pseudo_word_id = pwrs.pseudo_word_id and
				e_pwrs.syll_pos = pwrs.syll_pos
				where pwrs.pseudo_word_id is null

				order by participant, session_path, judge, pseudo_word_id, syll_pos, phon_pos
		""",
		"pw_count_prep": """CREATE TEMP VIEW IF NOT EXISTS v_expected_results_pw
				AS
				select pw.id as pseudo_word_id, p.id as phonem_id,
				CASE (pw.type)
				  WHEN "e" THEN 1
				  WHEN "g" THEN 1
				  ELSE 0
				  END as is_syllable, pws.position as syll_pos, sp.position as phon_pos
						from  pseudo_word as pw
						inner join pseudo_word_syllable as pws on pws.pseudo_word_id = pw.id
						inner join syllable as s on s.id = pws.syllable_id
						inner join syllable_phonem as sp on sp.syllable_id = pws.syllable_id
						inner join phonem as p on p.id = sp.phonem_id
						left outer join pseudo_word_phonem_special as pwps on pwps.pseudo_word_id = pw.id
						and pwps.syllable_pos = pws.position and pwps.phonem_pos = sp.position
						WHERE (pwps.ignore IS NULL or pwps.ignore = 0) AND ((is_syllable = 1 and
						sp.position = 1) OR (is_syllable=0))
				;


				CREATE TEMP VIEW IF NOT EXISTS v_sessions
				AS
				select session_path, participant, judge from results_pw
				group by session_path, participant, judge

				;

				CREATE TEMP VIEW IF NOT EXISTS v_expected_results_pw_full
				AS
				select *
				from v_expected_results_pw as e_pwrs
				join v_sessions
		""",
		"qa": """select rqa.participant, rqa.judge, rqa.session_path, rqa.stimuli_id, s.file,
		rqa.question_id, q.label as label_question , rqa.value, a.label as label_answer, rqa.version
		from results_qa as rqa
		inner join question as q on q.id = rqa.question_id
		inner join stimuli as s on s.id = rqa.stimuli_id
		left join answer as a on a.question_id = q.id and a.value = rqa.value""",
		# left join results_praat_qa as rpq on rpq.stimuli_id = s.id""",
		"qa_pneumo": """select rqa.participant, rqa.judge, rqa.session_path, rqa.stimuli_id, s.file,
		rqa.question_id, q.label as label_question , rqa.value, a.label as label_answer, rqa.version
		from results_qa as rqa
		inner join question as q on q.id = rqa.question_id
		inner join stimuli as s on s.id = rqa.stimuli_id
		left join answer as a on a.question_id = q.id and a.value = rqa.value
		where s.module = 'ModulePneumoPhonatoire' """,
		"qa_diado": """select rqa.participant, rqa.judge, rqa.session_path, rqa.stimuli_id, s.file,
		rqa.question_id, q.label as label_question , rqa.value, a.label as label_answer, rqa.version
		from results_qa as rqa
		inner join question as q on q.id = rqa.question_id
		inner join stimuli as s on s.id = rqa.stimuli_id
		left join answer as a on a.question_id = q.id and a.value = rqa.value
		where s.module = 'ModuleDiadoco' """,
		"qa_phrases": """select rqa.participant, rqa.judge, rqa.session_path, rqa.stimuli_id, s.file,
		rqa.question_id, q.label as label_question , rqa.value, a.label as label_answer, rqa.version
		from results_qa as rqa
		inner join question as q on q.id = rqa.question_id
		inner join stimuli as s on s.id = rqa.stimuli_id
		left join answer as a on a.question_id = q.id and a.value = rqa.value
		where s.module = 'ModulePhrases' """,
		"qa_texte": """select rqa.participant, rqa.judge, rqa.session_path, rqa.stimuli_id, s.file,
		rqa.question_id, q.label as label_question , rqa.value, a.label as label_answer, rqa.version
		from results_qa as rqa
		inner join question as q on q.id = rqa.question_id
		inner join stimuli as s on s.id = rqa.stimuli_id
		left join answer as a on a.question_id = q.id and a.value = rqa.value
		where s.module = 'ModuleTexte' """,
		"int": """select * from results_int""",
	}

	def __init__(self):
		return

	@staticmethod
	def export_pw_results(
		result_db_path, result_db_name, export_target_file, append=True, headers=False
	):
		"""
		Export 'pw' results from the database to a CSV file.

		Parameters:
		    result_db_path (str): Path to the database directory.
		    result_db_name (str): Name of the database file (without extension).
		    export_target_file (str): Path to the output CSV file.
		    append (bool): If True, append to the file; otherwise overwrite.
		    headers (bool): If True, include headers in the output.
		"""
		sql = ExportCSV.queries["pw"]
		ExportCSV.__export_sql__(
			sql, result_db_path, result_db_name, export_target_file, append, headers
		)

	@staticmethod
	def export_qa_results(
		result_db_path, result_db_name, export_target_file, append=True, headers=False
	):
		"""
		Export 'qa' results from the database to a CSV file.

		Parameters:
		    result_db_path (str): Path to the database directory.
		    result_db_name (str): Name of the database file (without extension).
		    export_target_file (str): Path to the output CSV file.
		    append (bool): If True, append to the file; otherwise overwrite.
		    headers (bool): If True, include headers in the output.
		"""
		sql = ExportCSV.queries["qa"]
		ExportCSV.__export_sql__(
			sql, result_db_path, result_db_name, export_target_file, append, headers
		)

	@staticmethod
	def export_int_results(
		result_db_path, result_db_name, export_target_file, append=True, headers=False
	):
		"""
		Export 'int' results from the database to a CSV file.

		Parameters:
		    result_db_path (str): Path to the database directory.
		    result_db_name (str): Name of the database file (without extension).
		    export_target_file (str): Path to the output CSV file.
		    append (bool): If True, append to the file; otherwise overwrite.
		    headers (bool): If True, include headers in the output.
		"""
		sql = ExportCSV.queries["int"]
		ExportCSV.__export_sql__(
			sql, result_db_path, result_db_name, export_target_file, append, headers
		)

	@staticmethod
	def get_export_data(data_type: str, participant_code: str) -> tuple:
		"""
		Retrieve export data for a given data type and participant.

		This method optionally runs a preparation SQL script before fetching the data.

		Parameters:
		    data_type (str): The type of data to export (e.g., 'pw', 'qa', 'int').
		    participant_code (str): Identifier for the participant.

		Returns:
		    tuple: A tuple containing
		        - headers (list of str): Column names from the query.
		        - data (list of lists of str): The query results as string values.
		"""
		cotation_db = DBManager.get_cotation_db()
		ParticipantManager.set_current_participant(participant_code)
		result_db = cotation_db.create_user_cotation_db(
			ParticipantManager.cotation_db_filename
		)

		prep_type = data_type + "_prep"
		if prep_type in ExportCSV.queries:
			result_db.execute_script(ExportCSV.queries[prep_type])

		sql = ExportCSV.queries[data_type]
		result = result_db.execute(sql)
		headers = []
		for val in result.description:
			headers.append(val[0])
		data = []
		for r in result:
			tmp = []
			for v in r:
				tmp.append(str(v).replace('"', "'"))
			data.append(tmp)

		return headers, data

	@staticmethod
	def __export_sql_to_list__(sql, result_db_path, result_db_name, headers=False):
		"""
		Execute an SQL query and return the results as a list, optionally including headers.

		Parameters:
		    sql (str): The SQL query to execute.
		    result_db_path (str): Path to the database directory.
		    result_db_name (str): Name of the database file (without extension).
		    headers (bool): If True, prepend the headers (column names) to the result list.

		Returns:
		    list: A list of rows (each a list of values), optionally with headers as the first row.
		"""
		dbmanager = DBManager(result_db_path + result_db_name + ".db")
		result = dbmanager.execute(sql)
		data = []

		if headers:
			tmp = []
			for val in result.description:
				tmp.append(val[0])

			data.append(tmp)

		for r in result:
			tmp = []
			for v in r:
				if isinstance(v, unicode):
					tmp.append(v.encode("utf-8"))
				else:
					tmp.append(v)
			data.append(tmp)

		return data

	@staticmethod
	def __export_sql__(
		sql,
		result_db_path,
		result_db_name,
		export_target_file,
		append=True,
		headers=False,
	):
		"""
		Export the result of an SQL query to a CSV file.

		Parameters:
		    sql (str): The SQL query to execute.
		    result_db_path (str): Path to the database directory.
		    result_db_name (str): Name of the database file (without extension).
		    export_target_file (str): Path to the output CSV file.
		    append (bool): If True, append to the file; otherwise overwrite.
		    headers (bool): If True, include headers in the output.
		"""
		data = ExportCSV.__export_sql_to_list__(
			sql, result_db_path, result_db_name, headers
		)

		mode = "w+"
		if append:
			mode = "a+"

		CSVManager.write_file(export_target_file, "\t", data, mode)
