import os
import mysql.connector
from dotenv import load_dotenv
import re

load_dotenv()

def get_db():
    return mysql.connector.connect(
        host=os.getenv("DB_HOST"),
        user=os.getenv("DB_USER"),
        password=os.getenv("DB_PASSWORD"),
        database=os.getenv("DB_NAME")
    )

def save_message(db, user_id, name, identifier, source, direction, content, timestamp, channel, subject=None):
    cursor = db.cursor()

    # Insertar o actualizar contacto
    cursor.execute("""
        INSERT INTO contact (name, number_or_email, source)
        VALUES (%s, %s, %s)
        ON DUPLICATE KEY UPDATE name = VALUES(name)
    """, (name, identifier, source))
    db.commit()

    cursor.execute("SELECT id FROM contact WHERE number_or_email = %s AND source = %s", (identifier, source))
    contact_id = cursor.fetchone()[0]

    # Insertar mensaje
    cursor.execute("""
        INSERT INTO message (user_id, contact_id, direction, content, timestamp, channel, subject)
        VALUES (%s, %s, %s, %s, %s, %s, %s)
    """, (user_id, contact_id, direction, content, timestamp, channel, subject))
    db.commit()

def normalizephone(contact_phone):
    contact_phone = contact_phone.replace("@c.us", "")
    contact_phone = contact_phone.replace("p:", "")
    contact_phone = contact_phone.replace("+", "")
    contact_phone = re.sub(r'\b549', '54', contact_phone)
    if len(contact_phone) < 12:
        return "54" + contact_phone
    return contact_phone