"""
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 MySQLdb.cursors 

import json
import ast
from datetime import datetime, date


def get_login_user(email):
    LOGIN_QUERY = "select first_name, last_name, logins.passwd from users left join logins on logins.user_id = users.id left join users_contact on users_contact.user_id = users.id where users_contact.company_email = %s"
    with mysql.connection.cursor() as cursor:
        cursor.execute(LOGIN_QUERY,(email,))
        result = cursor.fetchone()
        return result

def get_company_view_data():
    TABLE_DATA_QUERY = "select company.id , company.name, company_industry.name as industry, prefix, phone, email, address, postal_code, location, country from company left join company_industry on company.industry = company_industry.id "
    with mysql.connection.cursor() as cursor:
        cursor.execute(TABLE_DATA_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_customer_interactions_view_data():
    TABLE_DATA_QUERY = "SELECT customer_interactions.id, customer_interactions.date, customer_interactions.follow_up, company.name as company_name, company_contact_person.name as contact_person,communication_method.name as communication_method, customer_interactions.note  FROM `customer_interactions` left join communication_method on communication_method.id = customer_interactions.communicated_over LEFT JOIN company on company.id = customer_interactions.company LEFT JOIN company_contact_person on company_contact_person.id = customer_interactions.company_user;"
    with mysql.connection.cursor() as cursor:
        cursor.execute(TABLE_DATA_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_customer_interactions_for_edit(row_id):
    with mysql.connection.cursor() as cursor:
        cursor.execute("SELECT `id`, `date`, `follow_up`, `communicated_over`, `company`, `company_user`, `note` FROM `customer_interactions` WHERE id = %s", (row_id,))
        company_column_names = [desc[0] for desc in cursor.description]  # Get column names from the cursor description
        company_rows = cursor.fetchall()

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

        return customer_interactions
    
def get_company_data_for_edit(row_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("select id ,name, prefix, phone, email, address, postal_code, location, country, industry, status from company where id = %s", (row_id,))
        company_column_names = [desc[0] for desc in cursor.description]  # Get column names from the cursor description
        company_rows = cursor.fetchall()

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

        cursor.execute("select id ,name, prefix, phone, job_title, email, job_title, phone from company_contact_person where company_id = %s", (row_id,))
        company_contact_person_column_names = [desc[0] for desc in cursor.description]
        company_contact_person_rows = cursor.fetchall()

        company_contact_person_data = [
            {company_contact_person_column_names[i]: (row_data[i] if row_data[i] is not None else '') for i in range(len(company_contact_person_column_names))}
            for row_data in company_contact_person_rows
        ]

        return company_data, company_contact_person_data
    
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 check_duplicate(email, phone):
    """
    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:
        cursor.execute("SELECT * FROM users WHERE email = %s OR phone = %s", (email, phone))
        data = cursor.fetchone()

        if data:
            return data
        else:
            return None

def update_company(update_set,values,company_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 company SET {update_set} WHERE id = %s",values +[company_id])
        mysql.connection.commit()

def update_customer_interactions(update_set,values,company_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 customer_interactions SET {update_set} WHERE id = %s",values +[company_id])
        mysql.connection.commit()

def insert_company_and_get_id(company_data):
    """
    Fügt eine neue Firma in die company-Tabelle ein und gibt die neue ID zurück.

    Args:
        company_data (dict): Dictionary mit den Schlüsseln:
            'name', 'email', 'prefix', 'phone', 'address', 'postal_code', 'industry'

    Returns:
        int: Die ID der neu eingefügten Firma.
    """
    with mysql.connection.cursor() as cursor:
        sql = """
            INSERT INTO company (name, email, prefix, phone, address, postal_code, industry)
            VALUES (%s, %s, %s, %s, %s, %s, %s)
        """
        values = (
            company_data.get('name'),
            company_data.get('email'),
            company_data.get('prefix'),
            company_data.get('phone'),
            company_data.get('address'),
            company_data.get('postal_code'),
            company_data.get('industry')
        )
        cursor.execute(sql, values)
        mysql.connection.commit()
        return cursor.lastrowid  # Neue Firmen-ID zurückgeben

def insert_customer_interactions_and_get_id(company_data):
    """
    Fügt eine neue Firma in die company-Tabelle ein und gibt die neue ID zurück.

    Args:
        company_data (dict): Dictionary mit den Schlüsseln:
            'name', 'email', 'prefix', 'phone', 'address', 'postal_code', 'industry'

    Returns:
        int: Die ID der neu eingefügten Firma.
    """
    with mysql.connection.cursor() as cursor:
        sql = """
            INSERT INTO customer_interactions (`date`, `follow_up`, `communicated_over`, `company`, `company_user`, `note`)
            VALUES (%s, %s, %s, %s, %s, %s)
        """
        values = (
            company_data.get('date'),
            company_data.get('follow_up'),
            company_data.get('communicated_over'),
            company_data.get('company'),
            company_data.get('company_user'),
            company_data.get('note')
        )
        cursor.execute(sql, values)
        mysql.connection.commit()
        return cursor.lastrowid  # Neue Firmen-ID zurückgeben

def get_cred_google_service_acc():
    with mysql.connection.cursor() as cursor:
        cursor.execute("select json from credentials where name = 'google_service_acc'")
        data = cursor.fetchall()
        cred_json = json.loads(data[0][0])
        return cred_json
    
def get_insert_description():
    """
    Erhalte Columnamen

    Returns:
        List: Columnamen
    """
    with mysql.connection.cursor() as cursor:
        cursor.execute("select id ,name, phone, email, address, postal_code, location, country from company")
        column_names = [desc[0] for desc in cursor.description]
        return column_names

def get_insert_description_customer_interactions():
    """
    Erhalte Columnamen

    Returns:
        List: Columnamen
    """
    with mysql.connection.cursor() as cursor:
        cursor.execute("SELECT `id`, `date`, `follow_up`, `communicated_over`, `company`, `company_user`, `note` FROM `customer_interactions` ")
        column_names = [desc[0] for desc in cursor.description]
        return column_names

def update_contact_person(update_set, values, person_id):
    """
    Updates a contact person with the provided values.

    Args:
        update_set (str): SQL SET clause, e.g., "name = %s, email = %s"
        values (list): List of values to substitute in the SQL query.
        person_id (str): The ID of the contact person to update.
    """
    with mysql.connection.cursor() as cursor:
        cursor.execute(f"UPDATE company_contact_person SET {update_set} WHERE id = %s", values + [person_id])
        mysql.connection.commit()

def insert_contact_person(data):
    """
    Inserts a new contact person into the database.

    Args:
        data (dict): A dictionary containing 'name', 'email', 'phone', 'job_title', and 'company_id'.
    """
    with mysql.connection.cursor() as cursor:
        cursor.execute("""
            INSERT INTO company_contact_person (name, email, phone, job_title, company_id)
            VALUES (%s, %s, %s, %s, %s)
        """, (data['name'], data['email'], data['phone'], data['job_title'], data['company_id']))
        mysql.connection.commit()

def update_google_url(google_url, company_id):
    with mysql.connection.cursor() as cursor:
        sql = "UPDATE company SET folder_id = %s WHERE id = %s"
        cursor.execute(sql, (google_url, company_id))
        mysql.connection.commit()

def get_company_by_id_for_log(company_id):
    with mysql.connection.cursor(MySQLdb.cursors.DictCursor) as cursor:
        # Get company data
        cursor.execute("""
            SELECT company.name as name, company_industry.name as industry , company_status.name as status, prefix, phone,
                   email, address, postal_code, location, country, note
            FROM company left join company_industry on company_industry.id = company.industry left join company_status on company_status.id = company.status
            WHERE company.id = %s
        """, (company_id,))
        company = cursor.fetchone() or {}

        # Get contacts
        cursor.execute("""
            SELECT id, name, email, prefix, phone, job_title
            FROM company_contact_person
            WHERE company_id = %s
        """, (company_id,))
        contacts = cursor.fetchall()

        return {
            "company": company,
            "contacts": {str(c["id"]): c for c in contacts}
        }

def get_customer_interactions_by_id_for_log(customer_interactions_id):
    with mysql.connection.cursor(MySQLdb.cursors.DictCursor) as cursor:
        # Get company data
        cursor.execute("""
            SELECT date, follow_up, communication_method.name as communicated_over, company.name as company,company_contact_person.name as company_user, customer_interactions.note
            FROM customer_interactions left join communication_method on communication_method.id = customer_interactions.communicated_over left join company on company.id = customer_interactions.company left join company_contact_person on customer_interactions.company_user = company_contact_person.id
            WHERE customer_interactions.id = %s
        """, (customer_interactions_id,))
        customer_interactions = cursor.fetchone() or {}


        return {
            "customer_interactions": customer_interactions        
        }

import json

def insert_audit_log(table_name, record_id, user, changes):
    if not changes.get("company") and not changes.get("contacts") and not changes.get("customer_interactions"):
        return  # Nothing to log

    with mysql.connection.cursor() as cursor:
        cursor.execute("""
            INSERT INTO audit_log (table_name, record_id, changed_by, changed_at, change_summary)
            VALUES (%s, %s, %s, NOW(), %s)
        """, (
            table_name,
            record_id,
            user,
            json.dumps(changes, ensure_ascii=False)
        ))
        mysql.connection.commit()

def get_logs_from_db(table, row_id):
    with mysql.connection.cursor(MySQLdb.cursors.DictCursor) as cursor:
        cursor.execute("""
            SELECT audit_log.id as id, table_name, company.name as name, changed_by, changed_at, change_summary FROM audit_log left join company on company.id = audit_log.record_id
            WHERE table_name = %s AND record_id = %s
            ORDER BY changed_at DESC
        """, (table, row_id))
        logs = cursor.fetchall()

        for log in logs:
            try:
                log['change_summary'] = json.loads(log['change_summary'])
            except Exception:
                log['change_summary'] = {}

        return logs

def get_customer_contact_person_with_company_name(row_id):
    """
    Bereitet Daten für Dropdowns mit Namen + zugehörigem Firmennamen vor.

    Returns:
        List[dict]: Liste mit {'id': ..., 'description': ...}
    """
    with mysql.connection.cursor() as cursor:
        cursor.execute("""
            SELECT 
                company_contact_person.id, 
                company_contact_person.name 
            FROM 
                company_contact_person 
            LEFT JOIN 
                company ON company.id = company_contact_person.company_id
            WHERE
                company_contact_person.company_id = %s
        """, (row_id,))  # Pass the row_id to filter by company
        # Fetch all data from the cursor
        data = cursor.fetchall()
        final_data = [{'id': row[0], 'description': row[1]} for row in data]
        return final_data