"""
Dieses Modul bietet Zugriffsfunktionen für Benutzerdaten in der Datenbank.

Hier sind die Zugriffe wo die Applikation benötigt um die Daten anzuzeigen und zu bearbeiten. 
"""

from db import mysql
import json
import ast
from datetime import datetime, date
import csv, io


LOGIN_QUERY = (
    "SELECT CONCAT(firstname, ' ', lastname) as user_name, password, admin FROM webapp_logins WHERE "
    "email = %s"
)

VIEW_QUERY = (
    "SELECT users.id, users.user_id AS user_id, users.user_name AS user_name, "
    "users_region.region AS region, users_typ.typ AS typ, users.prefix AS prefix, "
    "users.phone AS phone, users.email AS email, users_approval.approval AS approval, "
    "users.approval_until, users.able_to_drive, "
    "users_status_application.status_application_state AS status_application, "
    "users_status_work.status AS status_work, users.doc_url "
    "FROM users "
    "LEFT JOIN users_approval ON users.approval = users_approval.id "
    "LEFT JOIN users_civil_status ON users.civil_status = users_civil_status.id "
    "LEFT JOIN users_fleet ON users.fleet = users_fleet.id "
    "LEFT JOIN users_region ON users.region = users_region.id "
    "LEFT JOIN users_gender ON users.gender = users_gender.id "
    "LEFT JOIN users_status_application ON users.status_application = users_status_application.id "
    "LEFT JOIN users_status_work ON users.status_work = users_status_work.id "
    "LEFT JOIN users_typ ON users.typ = users_typ.id "
    "WHERE deleted = 0 AND users.status_work NOT IN (8, 10, 12, 14, 7) and family_member = 0 "
    "ORDER BY users.id DESC"
)


EXTERNAL_VIEW_QUERY = (
    "SELECT users.id, users.user_id AS user_id, users.user_name AS user_name, "
    "users_region.region as region , users.prefix AS prefix, users.phone AS phone, users.email AS email, "
    "users.able_to_drive,  "
    "users_status_work.status AS status_work "
    "FROM users "
    "LEFT JOIN users_approval ON users.approval = users_approval.id "
    "LEFT JOIN users_civil_status ON users.civil_status = users_civil_status.id "
    "LEFT JOIN users_fleet ON users.fleet = users_fleet.id "
    "LEFT JOIN users_region ON users.region = users_region.id "
    "LEFT JOIN users_gender ON users.gender = users_gender.id "
    "LEFT JOIN users_status_application ON users.status_application = users_status_application.id "
    "LEFT JOIN users_status_work ON users.status_work = users_status_work.id "
    "LEFT JOIN users_typ ON users.typ = users_typ.id "
    "WHERE deleted = 0 and users.status_work in (12) "
)

INACTIVE_VIEW_QUERY = (
    "SELECT users.id, users.user_id AS user_id, users.user_name AS user_name, "
    "users_region.region as region ,users_typ.typ as typ, users.prefix AS prefix, users.phone AS phone, users.email AS email, "
    "users_approval.approval AS approval, users.approval_until, users.able_to_drive,  "
    "users_status_application.status_application_state AS status_application, "
    "users_status_work.status AS status_work, users.doc_url "
    "FROM users "
    "LEFT JOIN users_approval ON users.approval = users_approval.id "
    "LEFT JOIN users_civil_status ON users.civil_status = users_civil_status.id "
    "LEFT JOIN users_fleet ON users.fleet = users_fleet.id "
    "LEFT JOIN users_region ON users.region = users_region.id "
    "LEFT JOIN users_gender ON users.gender = users_gender.id "
    "LEFT JOIN users_status_application ON users.status_application = users_status_application.id "
    "LEFT JOIN users_status_work ON users.status_work = users_status_work.id "
    "LEFT JOIN users_typ ON users.typ = users_typ.id "
    "WHERE deleted = 0 and users.status_work in (8,10,14,7) and family_member = 0 "
    "ORDER BY users.id DESC"
)

TECH_VIEW_QUERY = (
    "SELECT users.id, users.user_id AS user_id, users.user_name AS user_name, users.email,  "
    "users_region.region AS region, users_typ.typ as typ, users.able_to_drive, users.tookan_id as Tookan, "
    "users.planday_id as Planday, users.uuid_id as Uber, users.telegram_id as Telegram "
    "FROM users "
    "LEFT JOIN users_region ON users.region = users_region.id "
    "LEFT JOIN users_typ ON users.typ = users_typ.id "
    "WHERE deleted = 0"

)

DETAILS_QUERY = (
    "SELECT users.user_id AS user_id, "
    "users.user_name AS user_name, users.first_name AS first_name, users.last_name AS last_name, "
    "users.phone AS phone, users.prefix AS prefix, "
    "users.email AS email, users.uuid_id AS uuid_id, "
    "users.STATUS AS STATUS, users_fleet.fleet_name AS fleet, users.planday_id, users.tookan_id, users.navision_id, " 
    "users_gender.description AS gender, users_typ.typ AS typ, users.application_date AS application_date, "
    "users.vehicle AS vehicle, users_approval.approval AS approval, users.address AS address, "
    "users.postal_code AS postal_code, users.location AS location, users.country AS country, "
    "users.ahv_number AS ahv_number, users.birth_date AS birth_date, users.language AS language, "
    "users.nationality AS nationality, users.first_conversation AS first_conversation, "
    "users.first_work_day AS first_work_day, users.last_work_day AS last_work_day, "
    "users.entry_date_contract_on_duty_ag AS entry_date_contract_on_duty_ag, "
    "users.approval_until AS approval_until, users_civil_status.civil_status AS civil_status, "
    "users.other_jobs AS other_jobs, users.number_of_children AS number_of_children, "
    "users.entry_date_old_contract AS entry_date_old_contract, users.entry_date_onduty_ag AS entry_date_onduty_ag, "
    "users.marriage_date AS marriage_date, users.divorce_date AS divorce_date, "
    "users.iban AS iban,  "
    "users.termination_date AS termination_date, users.termination_final_date AS termination_final_date, "
    "users.termination_reason AS termination_reason, "
    "users.note AS note, users_status_application.status_application_state AS status_application, "
    "users.vehicle_model AS vehicle_model, users.able_to_drive AS able_to_drive, "
    "users.application_from_where AS application_from_where, users_region.region AS region, "
    "users_status_work.status AS status_work, users.introduction_with AS introduction_with, "
    "users.introduction_date AS introduction_date, users.start_short_operation AS start_short_operation, "
    "users.private_mail AS private_mail, users.current_state AS current_state, users.doc_url AS doc_url "
    "FROM users "
    "LEFT JOIN users_approval ON users.approval = users_approval.id "
    "LEFT JOIN users_civil_status ON users.civil_status = users_civil_status.id "
    "LEFT JOIN users_fleet ON users.fleet = users_fleet.id "
    "LEFT JOIN users_region ON users.region = users_region.id "
    "LEFT JOIN users_gender ON users.gender = users_gender.id "
    "LEFT JOIN users_status_application ON users.status_application = users_status_application.id "
    "LEFT JOIN users_status_work ON users.status_work = users_status_work.id "
    "LEFT JOIN users_typ ON users.typ = users_typ.id "
    "WHERE users.user_id = %s;"
)

CHECK_DUPLICATE_INSERT_QUERY = "SELECT * FROM users WHERE email = %s OR phone = %s"

CHECK_DUPLICATE_EDIT_QUERY = "SELECT * FROM users WHERE (email = %s OR phone = %s) AND user_id != %s"


INSERT_QUERY = (
    "SELECT users.user_id AS user_id, "
    "users.first_name AS first_name, users.last_name AS last_name, users.company_name AS company_name, "
    "users.phone AS phone, users.prefix AS prefix, "
    "users.email AS email, users.uuid_id AS uuid_id, users.planday_id, users.tookan_id, "
    "users.navision_id AS navision_id, users.telegram_id AS telegram_id,  "
    "users.STATUS AS STATUS, users.fleet AS fleet, users.gender AS gender, users.typ AS typ, "
    "users.application_date AS application_date, users.vehicle AS vehicle, users.approval AS approval, "
    "users.address AS address, users.postal_code AS postal_code, users.location AS location, "
    "users.country AS country, users.ahv_number AS ahv_number, users.birth_date AS birth_date, "
    "users.language AS language, users.nationality AS nationality, users.first_conversation AS first_conversation, "
    "users.first_work_day AS first_work_day, users.last_work_day AS last_work_day, "
    "users.entry_date_contract_on_duty_ag AS entry_date_contract_on_duty_ag, users.approval_until AS approval_until, "
    "users.civil_status AS civil_status, users.other_jobs AS other_jobs, users.number_of_children AS number_of_children, "
    "users.entry_date_old_contract AS entry_date_old_contract, users.entry_date_onduty_ag AS entry_date_onduty_ag, "
    "users.marriage_date AS marriage_date, users.divorce_date AS divorce_date, users.partner_name AS partner_name, "
    "users.partner_surname AS partner_surname, users.partner_ahv_number AS partner_ahv_number, "
    "users.partner_address AS partner_address, users.partner_job AS partner_job, users.iban AS iban, "
    "users.missing_documents AS missing_documents, users.termination_date AS termination_date, "
    "users.termination_final_date AS termination_final_date, users.termination_reason AS termination_reason, "
    "users.out_of_system AS out_of_system, users.note AS note, users.status_application AS status_application, "
    "users.vehicle_model AS vehicle_model, users.able_to_drive AS able_to_drive, "
    "users.application_from_where AS application_from_where, users.region AS region, users.status_work AS status_work, "
    "users.introduction_with AS introduction_with, users.introduction_date AS introduction_date, "
    "users.start_short_operation AS start_short_operation, users.private_mail AS private_mail, "
    "users.current_state AS current_state, users.doc_url AS doc_url, users.partner_nationality "
    "FROM users "
    "LIMIT 1;"
)

INSERT_ADMINISTRATION_QUERY = ("SELECT `case_id`, `user_id`, `transmitted_over`, `receiver`, `concerns_topic`, `should_state`, `is_state`, `controller`, `control_report`, `decision`, `note`, `control_status`, `completed` FROM talentsphere_administration LIMIT 1")

EDIT_ADMINISTRATION_QUERY = ("SELECT `case_id`, `talentsphere_administration`.`user_id`, users.user_name, `transmitted_over`, `receiver`, `concerns_topic`, `should_state`, `is_state`, `controller`, `control_report`, `decision`, `talentsphere_administration`.`note`, `control_status`, `completed`,`file_url` FROM talentsphere_administration LEFT JOIN users on users.user_id = talentsphere_administration.user_id WHERE case_id = %s")

DELETE_QUERY = "UPDATE users SET deleted = 1 where id = %s"

EDIT_QUERY = (
    "SELECT users.user_id AS user_id,  "
    "users.first_name AS first_name, users.last_name AS last_name, users.company_name AS company_name, "
    "users.phone AS phone, users.prefix AS prefix, "
    "users.email AS email, users.uuid_id as uuid_id, users.planday_id AS planday_id, users.tookan_id as tookan_id, "
    "users.navision_id AS navision_id, users.telegram_id AS telegram_id, "
    "users.STATUS AS STATUS, users.fleet AS fleet, users.gender AS gender, users.typ AS typ, "
    "users.application_date AS application_date, users.vehicle AS vehicle, users.approval AS approval, "
    "users.address AS address, users.postal_code AS postal_code, users.location AS location, "
    "users.country AS country, users.ahv_number AS ahv_number, users.birth_date AS birth_date, "
    "users.language AS language, users.nationality AS nationality, users.first_conversation AS first_conversation, "
    "users.first_work_day AS first_work_day, users.last_work_day AS last_work_day, "
    "users.entry_date_contract_on_duty_ag AS entry_date_contract_on_duty_ag, users.approval_until AS approval_until, "
    "users.civil_status AS civil_status, users.other_jobs AS other_jobs, users.number_of_children AS number_of_children, "
    "users.entry_date_old_contract AS entry_date_old_contract, users.entry_date_onduty_ag AS entry_date_onduty_ag, "
    "users.marriage_date AS marriage_date, users.divorce_date AS divorce_date, users.partner_name AS partner_name, "
    "users.partner_surname AS partner_surname, users.partner_ahv_number AS partner_ahv_number, "
    "users.partner_address AS partner_address, users.partner_job AS partner_job, users.iban AS iban, "
    "users.missing_documents AS missing_documents, users.termination_date AS termination_date, "
    "users.termination_final_date AS termination_final_date, users.termination_reason AS termination_reason, "
    "users.out_of_system AS out_of_system, users.note AS note, users.status_application AS status_application, "
    "users.vehicle_model AS vehicle_model, users.able_to_drive AS able_to_drive, "
    "users.application_from_where AS application_from_where, users.region AS region, users.status_work AS status_work, "
    "users.introduction_with AS introduction_with, users.introduction_date AS introduction_date, "
    "users.start_short_operation AS start_short_operation, users.private_mail AS private_mail, "
    "users.current_state AS current_state, users.doc_url AS doc_url "
    "FROM users "
    "WHERE user_id = %s"
)



UPDATE_TOOKAN_ID_QUERY = ("UPDATE users SET tookan_id = %s WHERE user_id = %s")

UPDATE_PLANDAY_ID_QUERY = ("UPDATE users SET planday_id = %s WHERE user_id = %s")

INSERT_LOG = ("INSERT INTO f2023_logs (user_id, content_old, content_new, func, editor, webtable) VALUES (%s, %s, %s, %s, %s, 'employee')")

INSERT_ADMINISTRATION_LOG = ("INSERT INTO f2023_logs (user_id, content_old, content_new, func, editor, webtable) VALUES (%s, %s, %s, %s, %s, 'administration')")

SELECT_LOG = ("SELECT id, user_id, content_old, content_new, func, editor, ts FROM f2023_logs WHERE webtable = 'employee' and user_id = %s ORDER BY ts DESC;")

SELECT_ADMINISTRATION_LOG = ("SELECT id, user_id, content_old, content_new, func, editor, ts FROM f2023_logs WHERE webtable = 'administration' and user_id = %s ORDER BY ts DESC;")


SELECT_LOG_ROW_ID = ("SELECT content, ts FROM f2023_logs WHERE id = %s")

SELECT_NEEDED_INFO = ("SELECT users.id, users.user_id, user_name,users.email, users.prefix, users.phone, "
                      "users_approval.approval, CASE WHEN users.approval = 1 THEN 2 WHEN users.approval != 1 "
                      "AND users.approval_until IS NULL THEN 0 WHEN users.approval != 1 AND "
                      "users.approval_until IS NOT NULL THEN 1 ELSE 0 END AS approval_until, users_civil_status.civil_status, "
                      "users.number_of_children, users.ahv_number, users.other_jobs, CASE WHEN users.civil_status IN (1, 4) THEN 2 "
                      "WHEN users.civil_status = 2 AND users_approval.approval = 1 THEN 2 WHEN users.civil_status = 2 "
                      "AND users.partner_name IS NOT NULL AND users.partner_surname IS NOT NULL AND users.partner_ahv_number IS NOT NULL "
                      "AND users.marriage_date IS NOT NULL AND users.partner_nationality IS NOT NULL AND users.partner_address IS NOT NULL "
                      "AND users.partner_job IS NOT NULL THEN 1 WHEN users.civil_status = 3 AND users.divorce_date IS NOT NULL "
                      "THEN 1 ELSE 0 END AS partner_info_complete, users.planday_id FROM `users` LEFT JOIN users_approval ON users.approval = users_approval.id "
                      "LEFT JOIN users_civil_status ON users.civil_status = users_civil_status.id WHERE users.status_work NOT IN (7, 8, 9, 10, 14) and family_member = 0 "
                      "AND users.approval IS NOT NULL;")

ADMINISTRATION_NOT_COMP_QUERY = ("SELECT "
                        "  t.case_id as case_id, "
                        "  t.id as id, "
                        "  DATE(t.date) as date, "
                        "  CONCAT(t.user_id, ' ', user_id_users.user_name) AS applicant, "
                        "  talentsphere_transmission_method.name AS transmission_method, "
                        "  receiver_users.user_name AS receiver, "
                        "  talentsphere_concerns.name AS concerns_topic, "
                        "  t.should_state, "
                        "  t.is_state, "
                        "  controller_users.user_name AS controller, "
                        "  t.control_report, "
                        "  t.decision, "
                        "  t.note, "
                        "  t.file_url, "
                        "  t.control_status, "
                        "  t.completed "
                        "FROM talentsphere_administration AS t "
                        "LEFT JOIN users AS user_id_users ON t.user_id = user_id_users.user_id "
                        "LEFT JOIN users AS receiver_users ON t.receiver = receiver_users.user_id "
                        "LEFT JOIN users AS controller_users ON t.controller = controller_users.user_id "
                        "LEFT JOIN talentsphere_transmission_method ON t.transmitted_over = talentsphere_transmission_method.id "
                        "LEFT JOIN talentsphere_concerns ON t.concerns_topic = talentsphere_concerns.id "
                        "WHERE t.completed = 0 or t.completed = 2 ORDER BY t.date ASC"
                    )

ADMINISTRATION_COMP_QUERY = ("SELECT "
                        "  t.case_id as case_id, "
                        "  t.id as id, "
                        "  DATE(t.date) as date, "
                        "   CONCAT(t.user_id, ' ', user_id_users.user_name) AS applicant, "
                        "  talentsphere_transmission_method.name AS transmission_method, "
                        "  receiver_users.user_name AS receiver, "
                        "  talentsphere_concerns.name AS concerns_topic, "
                        "  t.should_state, "
                        "  t.is_state, "
                        "  controller_users.user_name AS controller, "
                        "  t.control_report, "
                        "  t.decision, "
                        "  t.note, "
                        "  t.file_url, "
                        "  t.control_status, "
                        "  t.completed "
                        "FROM talentsphere_administration AS t "
                        "LEFT JOIN users AS user_id_users ON t.user_id = user_id_users.user_id "
                        "LEFT JOIN users AS receiver_users ON t.receiver = receiver_users.user_id "
                        "LEFT JOIN users AS controller_users ON t.controller = controller_users.user_id "
                        "LEFT JOIN talentsphere_transmission_method ON t.transmitted_over = talentsphere_transmission_method.id "
                        "LEFT JOIN talentsphere_concerns ON t.concerns_topic = talentsphere_concerns.id "
                        "WHERE t.completed = 1 ORDER BY t.date DESC" 
                    )

SELECT_USER_ID = ("SELECT user_id from users")

EXPIRED_APPROVAL = (
    "SELECT users.id,users.user_id, users.user_name AS user_name, users.email AS email, users_typ.typ, users.prefix AS prefix, users.phone AS phone, "
    "users_status_work.status AS status_work, users_status_application.status_application_state AS status_application, "
    "users_approval.approval AS approval, users.approval_until "
    "FROM users "
    "LEFT JOIN users_approval ON users.approval = users_approval.id "
    "LEFT JOIN users_status_work ON users.status_work = users_status_work.id "
    "LEFT JOIN users_status_application ON users.status_application = users_status_application.id "
    "LEFT JOIN users_typ ON users_typ.id = users.typ "
    "WHERE approval_until <= DATE(NOW()) "
    "AND users.status_work NOT IN (8, 9, 10, 14, 12, 1) "
    "AND users.able_to_drive = 1 "
    "UNION "
    "SELECT users.id,users.user_id, users.user_name AS user_name, users.email AS email,users_typ.typ , users.prefix AS prefix, users.phone AS phone, "
    "users_status_work.status AS status_work, users_status_application.status_application_state AS status_application, "
    "users_approval.approval AS approval, users.approval_until "
    "FROM users "
    "LEFT JOIN users_approval ON users.approval = users_approval.id "
    "LEFT JOIN users_status_work ON users.status_work = users_status_work.id "
    "LEFT JOIN users_status_application ON users.status_application = users_status_application.id "
    "LEFT JOIN users_typ ON users_typ.id = users.typ "
    "WHERE MONTH(approval_until) = MONTH(NOW()) "
    "AND YEAR(approval_until) = YEAR(NOW()) "
    "AND users.status_work NOT IN (8, 9, 10, 14, 12, 1) and family_member = 0 "
    "AND users.able_to_drive = 1 "
    "ORDER BY approval_until ASC"
)

SELECT_PAYCHECK_DATA = (
    "SELECT y, m, "
    "CASE "
    "    WHEN type = 12 THEN 'Kurzstrecke' "
    "    WHEN type = 18 THEN 'Langstrecke' "
    "    ELSE 'Other' "
    "END AS Typ, "
    "total, "
    "CASE "
    "    WHEN Source = 'Addons' THEN 'Zusätze' "
    "    WHEN Source = 'Driverapp' THEN 'Normale Fahrten' "
    "    WHEN Source = 'Guarantee' THEN 'Garantierte Fahrten' "
    "    ELSE Source "
    "END AS Source "
    "FROM `Ansicht_payroll_total` "
    "WHERE user_id = %s AND y = %s AND m = %s"
)



SELECT_ADDONST_DETAILS_DATA = (
    "SELECT date, qty, comment, users.user_name as Ersteller FROM `drivers_remarks` "
    "LEFT JOIN users on users.user_id = drivers_remarks.disponent_id "
    "WHERE drivers_remarks.user_id = %s and YEAR(date) = %s and MONTH(date) = %s"
)

SELECT_GOOGLE_SERVICE_ACC = ("select json from credentials where name = 'google_service_acc'")


SELECT_UBER_DATA = (
    "SELECT "
    "Y as y, "
    "M as m, "
    "CASE "
    "  WHEN art = 'WT_NT' THEN 'weekend' "
    "  WHEN art = 'NT_NT' THEN 'week' "
    "  ELSE art "
    "END AS art_type, "
    "value "
    "FROM uber_cache_payroll_total "
    "WHERE user_id = %s AND Y = %s AND M = %s"
)

SELECT_NEEDED_DOCS_FORM = ("SELECT users_documet_characteristics.name,users_approval_documents_dependency.doc_id, users_documet_characteristics.sites, users_documet_characteristics.needed_date, users_approval_documents_dependency.approval_id, users_approval_documents_dependency.typ_id FROM `users_approval_documents_dependency` LEFT JOIN users_documet_characteristics on users_documet_characteristics.id = users_approval_documents_dependency.doc_id and users_approval_documents_dependency.igno = 0 and users_documet_characteristics.users_table = 1;")

SELECT_DOCS_FORMATE_FORM_ADMINISTRATION_TABLE = ("SELECT name, sites, needed_date FROM users_documet_characteristics where administrations_table = 1")


INSERT_FAMILY_CHILD_DATA = (
    "INSERT INTO users_family (user_id, children_id) VALUES (%s,%s)"
)

SELECT_CHILDREN_DATA = ("select users.user_id,users.first_name, users.last_name, users.gender, users.birth_date, users.note, users.child_allowances from users LEFT JOIN users_family on users.user_id = users_family.children_id WHERE users.family_member = 1 and users_family.deleted != 1 and users_family.user_id = %s ;")

SELECT_CHILDREN_DETAILS_DATA = ("select users.user_id,users.first_name, users.last_name, users_gender.description, users.birth_date, users.note, users.child_allowances  from users LEFT JOIN users_family on users.user_id = users_family.children_id LEFT JOIN users_gender on users_gender.id = users.gender WHERE users.family_member = 1 and users_family.deleted != 1 and users_family.user_id = %s ;")


SELECT_PARTNER_DATA = ("select users.user_id as partner_id,users.first_name, users.last_name, users.birth_date, users.ahv_number, users.approval, users.other_jobs, users.address, users.gender as gender from users LEFT JOIN users_family on users.user_id = users_family.partner_id WHERE users.family_member = 1 and users_family.deleted != 1 and users_family.user_id = %s ;")

SELECT_PARTNER_DATA_COMPARE = ("select users.user_id as partner_id,users.first_name as partner_first_name, users.last_name as partner_last_name, users.birth_date as partner_birth_date, users.ahv_number as partner_ahv_number, users.approval as partner_approval, users.other_jobs as partner_other_jobs, users.address as partner_address, users.gender as partner_gender from users LEFT JOIN users_family on users.user_id = users_family.partner_id WHERE users.family_member = 1 and users_family.deleted != 1 and users_family.user_id = %s ;")


UPDATE_CHILDREN_DATA = ("UPDATE users set first_name = %s, last_name = %s, gender = %s, birth_date = %s,  note = %s, child_allowances = %s where user_id = %s")

INSERT_CHILD_DATA = ("INSERT INTO users (user_id,user_name, first_name, last_name, gender, birth_date,note,child_allowances,family_member) VALUES (%s,%s, %s, %s, %s, %s, %s, %s, 1)")

INSERT_PARTNER_DATA = ("INSERT INTO users (user_id, first_name, last_name, birth_date,address, ahv_number, approval, other_jobs, gender,family_member) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, 1)")

UPDATE_PARTNER_DATA = (
    "UPDATE users SET first_name = %s, last_name = %s, birth_date = %s, "
    "address = %s, ahv_number = %s, approval = %s, other_jobs = %s, gender = %s "
    "WHERE user_id = %s"
)

INSERT_CHILDREN_FAMILY_DATA = ("INSERT INTO users_family (user_id, children_id) VALUES (%s, %s)")

INSERT_PARTNER_FAMILY_DATA = ("INSERT INTO users_family (user_id, partner_id) VALUES (%s, %s)")

DELETE_CHILDREN_FAMILY_DATA = ("UPDATE users_family set deleted = 1 where children_id = %s")

DELETE_PARTNER_FAMILY_DATA = ("UPDATE users_family set deleted = 1 where partner_id = %s")

SELECT_SPECIFIC_DOCS_REQUI = ("SELECT users_documet_characteristics.name,users_approval_documents_dependency.doc_id, users_documet_characteristics.sites, users_documet_characteristics.needed_date FROM `users_approval_documents_dependency` LEFT JOIN users_documet_characteristics on users_documet_characteristics.id = users_approval_documents_dependency.doc_id WHERE users_approval_documents_dependency.approval_id = %s or users_approval_documents_dependency.typ_id = %s AND doc_id != 3 AND users_approval_documents_dependency.igno = 0;")

SELECT_DOCS = ("SELECT id, name FROM `users_documet_characteristics`")

INSERT_USERS_DOC = ("INSERT INTO `users_documents`(`user_id`, `doc_typ`, `date_of_expire`, `date_of_begin`, `source`) VALUES (%s, %s, %s, %s, %s)")


ADDRESS_BY_USERID_QUERY = """
    SELECT
    CONCAT_WS(', ',
        NULLIF(TRIM(address), ''),
        NULLIF(TRIM(postal_code), ''),
        NULLIF(TRIM(location), ''),
        NULLIF(TRIM(country), '')
    ) AS full_address,
    address, postal_code, location, country
    FROM users
    WHERE user_id = %s
    LIMIT 1
    """

def insert_docs_db(user_id, doc_id,start_date,end_date,source):
    with mysql.connection.cursor() as cursor:
        cursor.execute(INSERT_USERS_DOC,(user_id, doc_id,start_date,end_date,source,))
        mysql.connection.commit()

def get_doc_fk_table():
    with mysql.connection.cursor() as cursor:
        cursor.execute(SELECT_DOCS)
        column_names = [desc[0] for desc in cursor.description]
        rows = cursor.fetchall()
        final_data = [dict(zip(column_names, row)) for row in rows]
        return final_data


def get_required_docs(approval_id, typ_id):
    with mysql.connection.cursor() as cursor:
        cursor.execute(SELECT_SPECIFIC_DOCS_REQUI,(approval_id,typ_id,))
        column_names = [desc[0] for desc in cursor.description]
        rows = cursor.fetchall()
        final_data = [dict(zip(column_names, row)) for row in rows]
        return final_data
    
def get_needed_docs_from():
    with mysql.connection.cursor() as cursor:
        cursor.execute(SELECT_NEEDED_DOCS_FORM)
        column_names = [desc[0] for desc in cursor.description]
        # Daten von Users werden in Rows gespeichert
        rows = cursor.fetchall()
        # Spaltenname und deren Wert wird zusammen zurückgeben als Liste
        final_data = [dict(zip(column_names, row)) for row in rows]
        return final_data
    
def get_docs_formate_from_administration_table():
    with mysql.connection.cursor() as cursor:
        cursor.execute(SELECT_DOCS_FORMATE_FORM_ADMINISTRATION_TABLE)
        column_names = [desc[0] for desc in cursor.description]
        # Daten von Users werden in Rows gespeichert
        rows = cursor.fetchall()
        # Spaltenname und deren Wert wird zusammen zurückgeben als Liste
        final_data = [dict(zip(column_names, row)) for row in rows]
        return final_data

def get_cred_google_service_acc():
    with mysql.connection.cursor() as cursor:
        cursor.execute(SELECT_GOOGLE_SERVICE_ACC)
        data = cursor.fetchall()
        cred_json = json.loads(data[0][0])
        return cred_json

def log_view(user_id):
    with mysql.connection.cursor() as cursor:
        # Assuming SELECT_LOG is defined elsewhere and selects all relevant columns
        cursor.execute(SELECT_LOG, (user_id,))

        column_names = [desc[0] for desc in cursor.description]

        # Fetch all rows from the cursor
        rows = cursor.fetchall()

        return rows, column_names

    
def log_administration_view(case_id):
    with mysql.connection.cursor() as cursor:
        # Assuming SELECT_LOG is defined elsewhere and selects all relevant columns
        cursor.execute(SELECT_ADMINISTRATION_LOG, (case_id,))
        
        # Extract column headers from cursor.description
        column_names = [desc[0] for desc in cursor.description]
        
        # Fetch all rows from the cursor
        rows = cursor.fetchall()
        
        # Organize rows as lists of values
        # Ensure each row_data is converted to a list, matching the desired output format
        data_rows = [list(row_data) for row_data in rows if row_data]

        # Organize the output as specified
        data = {
            "columns": column_names,
            "data": data_rows
        }
        return data
    

def log_details(row_id):
    with mysql.connection.cursor() as cursor:
        # Assuming SELECT_LOG is defined elsewhere and selects all relevant columns
        cursor.execute(SELECT_LOG_ROW_ID, (row_id,))
        data = cursor.fetchall()
        resulting_dict = ast.literal_eval(data[0][0])
        return [resulting_dict], data[0][1]


def log(user_id, content_old, content_new, func, editor, table):
    with mysql.connection.cursor() as cursor:
        if table == 'employee':
            cursor.execute(INSERT_LOG,(user_id, str(content_old), str(content_new), func, editor,))
        elif table == 'administration':
             cursor.execute(INSERT_ADMINISTRATION_LOG,(user_id, str(content_old), str(content_new), func, editor,))
        mysql.connection.commit()

def get_login_user(login_email):
    """
    Args:
        login_email (String): Email Input von User

    Returns:
        Tuple: Vorname, Passwort
    """
    with mysql.connection.cursor() as cursor:
        #Anhand der Email wird der User in der DB gesucht
        cursor.execute(LOGIN_QUERY,(login_email,))
        #Daten werden in
        result = cursor.fetchone()
        return result

def get_users_view_data():
    """
    Returns:
        Tuple: Spaltennamen und Userdaten
    """
    with mysql.connection.cursor() as cursor:
        cursor.execute(VIEW_QUERY)
        #cursor.description (Spalten Namen) werden in column_names gespeichert
        column_names = [desc[0] for desc in cursor.description]
        #Daten von Users werden in Rows gespeichert
        rows = cursor.fetchall()
        return column_names, rows
    
def get_users_external_view_data():
    """
    Returns:
        Tuple: Spaltennamen und Userdaten
    """
    with mysql.connection.cursor() as cursor:
        cursor.execute(EXTERNAL_VIEW_QUERY)
        #cursor.description (Spalten Namen) werden in column_names gespeichert
        column_names = [desc[0] for desc in cursor.description]
        #Daten von Users werden in Rows gespeichert
        rows = cursor.fetchall()
        return column_names, rows
    
def get_inactive_users_view_data():
    """
    Returns:
        Tuple: Spaltennamen und Userdaten
    """
    with mysql.connection.cursor() as cursor:
        cursor.execute(INACTIVE_VIEW_QUERY)
        #cursor.description (Spalten Namen) werden in column_names gespeichert
        column_names = [desc[0] for desc in cursor.description]
        #Daten von Users werden in Rows gespeichert
        rows = cursor.fetchall()
        return column_names, rows
    
def get_users_tech_data():
    """
    Returns:
        Tuple: Spaltennamen und Userdaten
    """
    with mysql.connection.cursor() as cursor:
        cursor.execute(TECH_VIEW_QUERY)
        #cursor.description (Spalten Namen) werden in column_names gespeichert
        column_names = [desc[0] for desc in cursor.description]
        #Daten von Users werden in Rows gespeichert
        rows = cursor.fetchall()
        return column_names, rows
    
def get_administration_not_comp_data():
    """
    Returns:
        Tuple: Spaltennamen und Userdaten
    """
    with mysql.connection.cursor() as cursor:
        cursor.execute(ADMINISTRATION_NOT_COMP_QUERY)
        #cursor.description (Spalten Namen) werden in column_names gespeichert
        column_names = [desc[0] for desc in cursor.description]
        #Daten von Users werden in Rows gespeichert
        rows = cursor.fetchall()
        return column_names, rows
    
def get_administration_comp_data():
    """
    Returns:
        Tuple: Spaltennamen und Userdaten
    """
    with mysql.connection.cursor() as cursor:
        cursor.execute(ADMINISTRATION_COMP_QUERY)
        #cursor.description (Spalten Namen) werden in column_names gespeichert
        column_names = [desc[0] for desc in cursor.description]
        #Daten von Users werden in Rows gespeichert
        rows = cursor.fetchall()
        return column_names, rows
    
def get_users_details_data(user_id):
    """
    Details über den spezifischen Fahrer

    Args:
        user_id String: row_id für den die Spezifischen Daten

    Returns:
        data List: Liste mit die Spaltennamen und deren Wert
    """
    with mysql.connection.cursor() as cursor:
        cursor.execute(DETAILS_QUERY,(user_id,))
        # cursor.description (Spalten Namen) werden in column_names gespeichert
        column_names = [desc[0] for desc in cursor.description]
        # Daten von Users werden in Rows gespeichert
        rows = cursor.fetchall()
        # Spaltenname und deren Wert wird zusammen zurückgeben als Liste
        data = [
            {column_names[i]: (row_data[i] if row_data[i] is not None else '') for i in range(len(column_names))}
            for row_data in rows
        ]

        return data
    
def get_users_paycheck_data(user_id, year = None, month = None):
    """
    Details über den spezifischen Fahrer

    Args:
        user_id String: row_id für den die Spezifischen Daten

    Returns:
        data List: Liste mit die Spaltennamen und deren Wert
    """
    with mysql.connection.cursor() as cursor:
        cursor.execute(SELECT_PAYCHECK_DATA,(user_id,year, month))
        # cursor.description (Spalten Namen) werden in column_names gespeichert
        column_names = [desc[0] for desc in cursor.description]
        # Daten von Users werden in Rows gespeichert
        rows = cursor.fetchall()
        # Spaltenname und deren Wert wird zusammen zurückgeben als Liste
        final_data = [dict(zip(column_names, row)) for row in rows]

        return final_data
    
    
def get_users_addons_details_data(user_id, year = None, month = None):
    """
    Details über den spezifischen Fahrer

    Args:
        user_id String: row_id für den die Spezifischen Daten

    Returns:
        data List: Liste mit die Spaltennamen und deren Wert
    """
    with mysql.connection.cursor() as cursor:
        cursor.execute(SELECT_ADDONST_DETAILS_DATA,(user_id,year, month))
        # cursor.description (Spalten Namen) werden in column_names gespeichert
        column_names = [desc[0] for desc in cursor.description]
        # Daten von Users werden in Rows gespeichert
        rows = cursor.fetchall()
        # Spaltenname und deren Wert wird zusammen zurückgeben als Liste
        final_data = [dict(zip(column_names, row)) for row in rows]

        return final_data
    
def get_users_paycheck_data_uber(user_id, year = None, month = None):
    """
    Details über den spezifischen Fahrer

    Args:
        user_id String: row_id für den die Spezifischen Daten

    Returns:
        data List: Liste mit die Spaltennamen und deren Wert
    """
    with mysql.connection.cursor() as cursor:
        cursor.execute(SELECT_UBER_DATA,(user_id,year, month))
        # cursor.description (Spalten Namen) werden in column_names gespeichert
        column_names = [desc[0] for desc in cursor.description]
        # Daten von Users werden in Rows gespeichert
        rows = cursor.fetchall()
        # Spaltenname und deren Wert wird zusammen zurückgeben als Liste
        final_data = [dict(zip(column_names, row)) for row in rows]

        return final_data
    

def check_duplicate(email, phone, user_id=None):
    """
    Kontrolliert ob der email oder Telefonummer schon existiert

    Args:
        email String: _description_
        phone String: _description_
        func_row_id (Boolean), optional): Row id muss keine value haben. Defaults to None.

    Returns:
        dict: Gibt dict zurück mit daten
    """
    with mysql.connection.cursor() as cursor:
        if user_id is None:
            cursor.execute(CHECK_DUPLICATE_INSERT_QUERY, (email, phone))
            data = cursor.fetchone()
        else:
            cursor.execute(CHECK_DUPLICATE_EDIT_QUERY, (email, phone,user_id))
            data = cursor.fetchone()
        if data:
            return data
        else:
            return None

def get_data_from_fk_table(table_name, description_pos, ignore=False):
    """
    Vorbereitung daten für FK Tabellen für Dropdown liste

    Args:
        table_name String: Tabellen name
        description_pos int: welche Spalte steht Beschreibung

    Returns:
        List: Tabelle mit id und beschreibung
    """
    with mysql.connection.cursor() as cursor:
        if ignore is True:
            cursor.execute("SELECT * FROM " + table_name + " WHERE igno = 0")
        else:
            cursor.execute("SELECT * FROM " + table_name)
        data_fetch = cursor.fetchall()
        final_data = [{'id': item[0], 'description': item[description_pos]} for item in data_fetch]
        return final_data
    
def get_all_data_from_fk_table(table_name):
    """
    Vorbereitung daten für FK Tabellen für Dropdown liste

    Args:
        table_name String: Tabellen name
        description_pos int: welche Spalte steht Beschreibung

    Returns:
        List: Tabelle mit id und beschreibung
    """
    with mysql.connection.cursor() as cursor:
        query = f"SELECT * FROM `{table_name}`;"
        cursor.execute(query)
        
        # Fetch the column headers and all rows
        columns = [desc[0] for desc in cursor.description]
        data_fetch = cursor.fetchall()
        
        # Convert each row into a dictionary using column headers as keys
        final_data = [dict(zip(columns, row)) for row in data_fetch]
        
        return final_data
    
def get_userid_username():
    """
    Prepares data for foreign key tables for dropdown lists.

    Returns:
        List[dict]: A list of dictionaries where each dictionary represents a row with user_id, user_name,
                    region_name, and office information, sorted alphabetically by user_name.
    """
    with mysql.connection.cursor() as cursor:
        # SQL query with ORDER BY to sort results alphabetically by user_name
        query = """
            SELECT user_id, user_name, users_region.region AS region_name,
                   CASE WHEN users.region = 36 AND users.status_work != 10 THEN 1 ELSE 0 END AS office
            FROM users
            LEFT JOIN users_region ON users_region.id = users.region
            WHERE users.family_member = 0
            ORDER BY user_name ASC;
        """

        cursor.execute(query)
        
        # Fetch column headers and all rows
        columns = [desc[0] for desc in cursor.description]
        data_fetch = cursor.fetchall()
        
        # Convert each row into a dictionary using column headers as keys
        final_data = [dict(zip(columns, row)) for row in data_fetch]
        
        return final_data

    
def get_insert_description():
    """
    Erhalte Columnamen

    Returns:
        List: Columnamen
    """
    with mysql.connection.cursor() as cursor:
        cursor.execute(INSERT_QUERY)
        column_names = [desc[0] for desc in cursor.description]
        return column_names
    
def get_insert_administration_description():
    """
    Erhalte Columnamen

    Returns:
        List: Columnamen
    """
    with mysql.connection.cursor() as cursor:
        cursor.execute(INSERT_ADMINISTRATION_QUERY)
        column_names = [desc[0] for desc in cursor.description]
        return column_names

def insert_data_db(keys,placeholders,values):
    """
    Inserten von der Daten

    Args:
        keys String: Columnamen
        placeholders String: Platzhalter für die Datem
        converted_values Dict: Die Daten
    """
    with mysql.connection.cursor() as cursor:
        insert_statement = f"INSERT INTO users ({keys}) VALUES ({placeholders})"
        cursor.execute(insert_statement,values)
        mysql.connection.commit()


def insert_data_db_administration(keys,placeholders,values):
    """
    Inserten von der Daten

    Args:
        keys String: Columnamen
        placeholders String: Platzhalter für die Datem
        converted_values Dict: Die Daten
    """
    with mysql.connection.cursor() as cursor:
        insert_statement = f"INSERT INTO talentsphere_administration ({keys}) VALUES ({placeholders})"
        cursor.execute(insert_statement,values)
        mysql.connection.commit()

def get_user_data_for_edit(user_id):
    """
    Retrieves column names and data based on the row ID.

    Args:
        user_id (str): The ID of the user to fetch data for.

    Returns:
        list[Dict]: A list containing a dictionary for the specific row, 
                    with column names as keys and data as values. If a data value is None,
                    it is replaced with an empty string ''.
    """
    with mysql.connection.cursor() as cursor:
        cursor.execute(EDIT_QUERY, (user_id,))
        column_names = [desc[0] for desc in cursor.description]  # Get column names from the cursor description
        rows = cursor.fetchall()

        # Prepare the data, replacing None with ''
        data = [
            {column_names[i]: (row_data[i] if row_data[i] is not None else '') for i in range(len(column_names))}
            for row_data in rows
        ]
        return data
    

def get_administration_row_data_for_edit(case_id):
    """
    Retrieves column names and data based on the row ID.

    Args:
        user_id (str): The ID of the user to fetch data for.

    Returns:
        list[Dict]: A list containing a dictionary for the specific row, 
                    with column names as keys and data as values. If a data value is None,
                    it is replaced with an empty string ''.
    """
    with mysql.connection.cursor() as cursor:
        cursor.execute(EDIT_ADMINISTRATION_QUERY, (case_id,))
        column_names = [desc[0] for desc in cursor.description]  # Get column names from the cursor description
        rows = cursor.fetchall()

        # Prepare the data, replacing None with ''
        data = [
            {column_names[i]: (row_data[i] if row_data[i] is not None else '') for i in range(len(column_names))}
            for row_data in rows
        ]
        return data

def get_user_data_for_edit_as_string(user_id):
    """
    Fetch column names and data for a specific row ID from two separate queries, converting all values to string format,
    except for `None` which remains unmodified. Child data is stored in separate dictionaries with an additional key 'child': True.

    Args:
        user_id (str): The ID of the user to retrieve data for.

    Returns:
        dict: A dictionary containing two keys: 'user_data' and 'children_data'. 'user_data' is a list of dictionaries
              with column names as keys and corresponding data as strings. 'children_data' is a list of dictionaries,
              each containing child data with an additional key 'child': True.
    """
    final_data = {}

    with mysql.connection.cursor() as cursor:
        # Execute the first query for user data
        cursor.execute(EDIT_QUERY, (user_id,))
        user_column_names = [desc[0] for desc in cursor.description]
        user_rows = cursor.fetchall()

        # Process user data
        user_data = []
        for row_data in user_rows:
            row_dict = {}
            for col, value in zip(user_column_names, row_data):
                if isinstance(value, datetime):
                    row_dict[col] = value.strftime('%Y-%m-%d %H:%M:%S')
                elif value is None:
                    row_dict[col] = None
                else:
                    row_dict[col] = str(value)
            # Add the row to the user_data list
            user_data.append(row_dict)
        
        # Combine first_name and last_name to create user_name in each user data dictionary
        for user in user_data:
            if user['able_to_drive'] == "0":
                user['user_name'] = user['first_name'] + " " + user['last_name'] + " " + "(DNF)"
            else:
                user['user_name'] = user['first_name'] + " " + user['last_name']

        # Store the user data in the final dictionary
        final_data['user_data'] = user_data

        # Execute the second query for child data
        cursor.execute(SELECT_CHILDREN_DATA, (user_id,))
        child_column_names = [desc[0] for desc in cursor.description]
        child_rows = cursor.fetchall()

        # Process child data
        children_data = []
        for row_data in child_rows:
            row_dict = {}
            for col, value in zip(child_column_names, row_data):
                if isinstance(value, datetime):
                    row_dict[col] = value.strftime('%Y-%m-%d %H:%M:%S')
                elif value is None:
                    row_dict[col] = None
                elif col == 'child_allowances' and value is not None:
                    # Convert 'child_allowances' to int
                    row_dict[col] = int(value)
                else:
                    row_dict[col] = str(value)
            # Add 'child': True to each child's data
            row_dict['child'] = True
            children_data.append(row_dict)

        # Store the child data in the final dictionary
        final_data['children_data'] = children_data

        # Execute the third query for partner data
        cursor.execute(SELECT_PARTNER_DATA_COMPARE, (user_id,))
        partner_column_names = [desc[0] for desc in cursor.description]
        partner_rows = cursor.fetchall()

        # Process partner data
        partner_data = []
        for row_data in partner_rows:
            row_dict = {}
            for col, value in zip(partner_column_names, row_data):
                if isinstance(value, datetime):
                    row_dict[col] = value.strftime('%Y-%m-%d %H:%M:%S')
                elif value is None:
                    row_dict[col] = None
                else:
                    row_dict[col] = str(value)
            partner_data.append(row_dict)

        # Store the partner data in the final dictionary
        final_data['partner_data'] = partner_data

    return final_data

    
def get_administration_data_for_edit_as_string(case_id):
    """
    Fetch column names and data for a specific row ID, converting all values to string format, except for `None` which remains unmodified.

    Args:
        user_id (str): The ID of the user to retrieve data for.

    Returns:
        list[dict]: A list of dictionaries where keys are column names and values are the corresponding data as strings, except for `None`.
    """
    with mysql.connection.cursor() as cursor:
        cursor.execute(EDIT_ADMINISTRATION_QUERY, (case_id,))
        # Saving column names from cursor.description
        column_names = [desc[0] for desc in cursor.description]
        # Fetching user data
        rows = cursor.fetchall()
        data = []

        for row_data in rows:
            # Initialize a dictionary for the current row
            row_dict = {}
            for col, value in zip(column_names, row_data):
                # Convert datetime objects to string in a specific format, e.g., 'YYYY-MM-DD HH:MM:SS'
                if isinstance(value, datetime):
                    row_dict[col] = value.strftime('%Y-%m-%d %H:%M:%S')
                elif value is None:
                    row_dict[col] = None  # Keep None as None, without converting to 'None'
                else:
                    row_dict[col] = value  
            data.append(row_dict)

        return data


def _insert_key_after(d: dict, new_key: str, new_value, after_key: str) -> dict:
    """
    Return a new dict with (new_key:new_value) inserted immediately after `after_key`.
    If `after_key` doesn't exist, append at the end.
    Preserves order for Python 3.7+.
    """
    out = {}
    inserted = False
    for k, v in d.items():
        out[k] = v
        if k == after_key and not inserted:
            out[new_key] = new_value
            inserted = True
    if not inserted:
        out[new_key] = new_value
    return out

def get_administration_data_for_edit_with_full_address(case_id):
    """
    Uses your get_administration_data_for_edit_as_string(case_id).
    Adds ONLY 'full_address' when concerns_topic in (17, 18) else sets None.
    Ensures 'full_address' is placed immediately after 'user_name' in each row.
    """
    rows = get_administration_data_for_edit_as_string(case_id)
    print(rows)
    if not rows:
        return []

    # one cursor reused for all address lookups
    with mysql.connection.cursor() as cur:
        for i, row in enumerate(rows):
            topic = row.get("concerns_topic")
            full_addr = None

            if topic is not None and str(topic) in ("17", "18"):
                user_id = row.get("user_id")
                if user_id:
                    cur.execute(ADDRESS_BY_USERID_QUERY, (user_id,))
                    res = cur.fetchone()
                    if res is not None:
                        # handle tuple or dict
                        if isinstance(res, (list, tuple)):
                            full_addr = res[0]
                        elif isinstance(res, dict):
                            full_addr = res.get("full_address")
                        else:
                            full_addr = str(res)

            # put full_address into the row (no matter if None), then reorder
            row["full_address"] = full_addr
            # Remove any existing 'full_address' to avoid duplicating during reinsert
            # (not strictly necessary since we recreate dict anyway)
            rows[i] = _insert_key_after(
                {k: v for k, v in row.items() if k != "full_address"},
                "full_address",
                full_addr,
                after_key="user_name"
            )

    return rows

def update_user_delete(row_id):
    """
    Anhand der id den Row für diesen Applikation Deaktivieren

    Args:
        row_id int: Row ID
    """
    with mysql.connection.cursor() as cursor:
        cursor.execute(DELETE_QUERY,(row_id,))
        mysql.connection.commit()
        
        
def update_user(update_set,values,user_id):
    """
    Zeile Updaten mit neue Daten

    Args:
        update_set String: Update string mit Spaltennamen und dazue den Platzhalter
        values String: Die neue Values um zu Updaten
        row_id String: Zeilen ID um die Zeile zu bestimmen
    """
    with mysql.connection.cursor() as cursor:
        cursor.execute(f"UPDATE users SET {update_set} WHERE user_id = %s",values +[user_id])
        mysql.connection.commit()

def update_user_administration(update_set, values, case_id):
    """
    Zeile Updaten mit neue Daten

    Args:
        update_set String: Update string mit Spaltennamen und dazue den Platzhalter
        values List: Die neuen Values um zu Updaten
        row_id String: Zeilen ID um die Zeile zu bestimmen
    """
    with mysql.connection.cursor() as cursor:
        try:
            # Convert values to tuple and add row_id
            all_values = tuple(values) + (case_id,)
            cursor.execute(f"UPDATE talentsphere_administration SET {update_set} WHERE case_id = %s", all_values)
            mysql.connection.commit()
        except Exception as e:
            print(f"Error updating user administration: {e}")


def update_tookan_id(tookan_id, user_id):
    with mysql.connection.cursor() as cursor:
        cursor.execute(UPDATE_TOOKAN_ID_QUERY,(tookan_id,user_id,))
        mysql.connection.commit()

def update_planday_id(planday_id, user_id):
    with mysql.connection.cursor() as cursor:
        cursor.execute(UPDATE_PLANDAY_ID_QUERY,(planday_id,user_id,))
        mysql.connection.commit()

def get_value_by_id(table_name, column_name, id_value, target_column):
    """
    Retrieves a specific value from a specified column in a row identified by an ID in a database table.

    Args:
        table_name (str): The name of the table from which to retrieve the data.
        id_column (str): The name of the column that holds the identifier.
        id_value (int or str): The identifier value used to select the specific row.
        target_column (str): The name of the column from which to retrieve the value.

    Returns:
        Any: The value from the specified column of the identified row, or None if no row is found.
    """
    with mysql.connection.cursor() as cursor:
        # Safely prepare the query to avoid SQL injection
        query = f"SELECT `{target_column}` FROM `{table_name}` WHERE `{column_name}` = %s;"
        cursor.execute(query, (id_value,))
        
        # Fetch the first row of results
        result = cursor.fetchone()
        if result:
            # Return the value found in the target column
            return result[0]
        else:
            # Return None if no row was found
            return None
        
def get_needed_info():
    with mysql.connection.cursor() as cursor:
        cursor.execute(SELECT_NEEDED_INFO)
        column_names = [desc[0] for desc in cursor.description]
        #Daten von Users werden in Rows gespeichert
        rows = cursor.fetchall()
        return column_names, rows
    
def get_all_user_id():
    with mysql.connection.cursor() as cursor:
        cursor.execute(SELECT_USER_ID)
        existing_uids = set(row[0] for row in cursor.fetchall())
        return existing_uids
    
def get_expired_approval_employee():
    with mysql.connection.cursor() as cursor:
        cursor.execute(EXPIRED_APPROVAL)
        column_names = [desc[0] for desc in cursor.description]
        #Daten von Users werden in Rows gespeichert
        rows = cursor.fetchall()
        return column_names, rows
    


def get_childrens_data(user_id):
    with mysql.connection.cursor() as cursor:
        cursor.execute(SELECT_CHILDREN_DATA, (user_id,))
        columns = [desc[0] for desc in cursor.description]
        data_fetch = cursor.fetchall()
        
        # Convert each row into a dictionary using column headers as keys
        final_data = [dict(zip(columns, row)) for row in data_fetch]
        
        # Convert date/datetime objects to strings in the format 'YYYY-MM-DD'
        for item in final_data:
            for key, value in item.items():
                if isinstance(value, (date, datetime)):  # Check if the value is date or datetime
                    item[key] = value.strftime('%Y-%m-%d')  # Convert to string format

        return final_data

def get_childrens_details_data(user_id):
    with mysql.connection.cursor() as cursor:
        cursor.execute(SELECT_CHILDREN_DETAILS_DATA, (user_id,))
        columns = [desc[0] for desc in cursor.description]
        data_fetch = cursor.fetchall()
        
        # Convert each row into a dictionary using column headers as keys
        final_data = [dict(zip(columns, row)) for row in data_fetch]
        
        # Convert date/datetime objects to strings in the format 'YYYY-MM-DD'
        for item in final_data:
            for key, value in item.items():
                if isinstance(value, (date, datetime)):  # Check if the value is date or datetime
                    item[key] = value.strftime('%Y-%m-%d')  # Convert to string format

        return final_data
    
def get_partner_data(user_id):
    with mysql.connection.cursor() as cursor:
        cursor.execute(SELECT_PARTNER_DATA, (user_id,))
        columns = [desc[0] for desc in cursor.description]
        data_fetch = cursor.fetchall()

        # If no data is found, return keys with None values
        if not data_fetch:
            return [{col: None for col in columns}]

        final_data = [dict(zip(columns, row)) for row in data_fetch]
        
        # Convert date/datetime objects to strings in the format 'YYYY-MM-DD'
        for item in final_data:
            for key, value in item.items():
                if isinstance(value, (date, datetime)):  # Check if the value is date or datetime
                    item[key] = value.strftime('%Y-%m-%d')  # Convert to string format

        return final_data

def update_child_data(data):
    with mysql.connection.cursor() as cursor:
        cursor.execute(UPDATE_CHILDREN_DATA, (data['first_name'],data['last_name'],data['gender'],data['birth_date'],data['note'],data['child_allowances'],data['user_id'],))
        mysql.connection.commit()

def insert_childerns_data(user_id, childrens_list):
    with mysql.connection.cursor() as cursor:
        for children in childrens_list:
            full_name = None
            if children['first_name'] and children['last_name']:
                full_name = children['first_name'] + " " + children['last_name']
            cursor.execute(INSERT_CHILD_DATA,(children['user_id'],full_name, children['first_name'], children['last_name'], children['gender'], children['birth_date'], children['note'],children['child_allowances'],))
            cursor.execute(INSERT_FAMILY_CHILD_DATA,(user_id, children['user_id'],))        
        mysql.connection.commit()

def insert_child_data(data, user_id, child_id):
    with mysql.connection.cursor() as cursor:
        full_name = None
        if data['first_name'] and data['last_name']:
            full_name = data['first_name'] + " " + data['last_name']
        cursor.execute(INSERT_CHILD_DATA,(child_id,full_name,data['first_name'],data['last_name'],data['gender'], data['birth_date'], data['note'],data['child_allowances'],))
        cursor.execute(INSERT_CHILDREN_FAMILY_DATA,(user_id,child_id,))
        mysql.connection.commit()

def delete_child_data(child_id):
    with mysql.connection.cursor() as cursor:
        cursor.execute(DELETE_CHILDREN_FAMILY_DATA,(child_id,))
        mysql.connection.commit()

INSERT_PARTNER_DATA = ("INSERT INTO users (user_id, first_name, last_name, birth_date,address, ahv_number, approval, other_jobs, gender,family_member) VALUES (%s, %s, %s, %s, %s, %s, %s, %s,%s, 1)")


def insert_partner_db(data, user_id, partner_id):
    with mysql.connection.cursor() as cursor:
        cursor.execute(INSERT_PARTNER_DATA,(partner_id,data['partner_first_name'],data['partner_last_name'],data['partner_birth_date'],data['partner_address'],data['partner_ahv_number'],data['partner_approval'],data['partner_other_jobs'],data['partner_gender'],))
        cursor.execute(INSERT_PARTNER_FAMILY_DATA,(user_id,partner_id,))
        mysql.connection.commit()

def update_partner_data(data):
    with mysql.connection.cursor() as cursor:
        cursor.execute(UPDATE_PARTNER_DATA,(data['partner_first_name'],data['partner_last_name'],data['partner_birth_date'],data['partner_address'],data['partner_ahv_number'],data['partner_approval'],data['partner_other_jobs'],data['partner_gender'],data['partner_id'],))
        mysql.connection.commit()

def delete_partner_data(partner_id):
    with mysql.connection.cursor() as cursor:
        cursor.execute(DELETE_PARTNER_FAMILY_DATA,(partner_id,))
        mysql.connection.commit()


def insert_external_acc_status(user_id, name, status):
    with mysql.connection.cursor() as cursor:
        sql = "INSERT INTO users_external_acc_status (user_id ,name, status, updated_at) VALUES (%s, %s, %s, NOW())"
        cursor.execute(sql, (user_id, name, status,))
        mysql.connection.commit()

def update_external_acc_status(user_id, name, status):
    with mysql.connection.cursor() as cursor:
        sql = "UPDATE users_external_acc_status SET status = %s, updated_at = NOW() WHERE user_id = %s AND name = %s"
        cursor.execute(sql, (status, user_id, name,))
        mysql.connection.commit()


def get_external_account_status(user_id):
    """
    Retrieves the external account status for all services for a specific user.

    Args:
        user_id (int): The ID of the user.

    Returns:
        list: A list of dictionaries like:
              [
                  {"name": "tookan", "status": "0"},
                  {"name": "planday", "status": "1"}
              ]
              or an empty list if no entries are found.
    """
    with mysql.connection.cursor() as cursor:
        sql = """
        SELECT name, status
        FROM users_external_acc_status
        WHERE user_id = %s
        """
        cursor.execute(sql, (user_id,))
        rows = cursor.fetchall()

        return [{"name": row[0], "status": str(row[1])} for row in rows] if rows else []


def insert_last_planday_department_users(user_id, employeeGroups):
    data = {"employeeGroups": employeeGroups}
    with mysql.connection.cursor() as cursor:
        sql = "INSERT INTO users_last_planday_department (planday_id ,employee_groups) VALUES (%s, %s)"
        cursor.execute(sql, (user_id, json.dumps(data),))
        mysql.connection.commit()

def select_last_planday_department_users(user_id):
    with mysql.connection.cursor() as cursor:
        sql = "SELECT employee_groups FROM users_last_planday_department WHERE planday_id = %s limit 1"
        cursor.execute(sql, (user_id,))
        row = cursor.fetchone()
        return row[0] if row else None