from models.db import db_cursor


def create_user(name, email, phone, s3_image_key, s3_image_url, face_id=None):
    query = """
        INSERT INTO users (name, email, phone, image_url, s3_image_key, s3_image_url, face_id)
        VALUES (%s, %s, %s, %s, %s, %s, %s)
    """
    with db_cursor() as (_, cursor):
        cursor.execute(query, (name, email, phone, s3_image_url, s3_image_key, s3_image_url, face_id))
        return cursor.lastrowid


def create_registered_user(name, email, phone, image_url, s3_image_key):
    """Insert a visitor into users without any Rekognition face indexing."""
    query = """
        INSERT INTO users (name, email, phone, image_url, s3_image_key, s3_image_url, face_id)
        VALUES (%s, %s, %s, %s, %s, %s, NULL)
    """
    with db_cursor() as (_, cursor):
        cursor.execute(query, (name, email, phone, image_url, s3_image_key, image_url))
        return cursor.lastrowid


def get_all_users():
    """Fetch all registered users for the admin panel."""
    query = """
        SELECT id, name, email, phone, s3_image_key, face_id, created_at
        FROM users
        ORDER BY created_at DESC
    """
    with db_cursor(dictionary=True) as (_, cursor):
        cursor.execute(query)
        return cursor.fetchall()


def get_user_by_email(email):
    query = """
        SELECT id, name, email, phone, image_url, s3_image_key, s3_image_url, face_id, created_at
        FROM users
        WHERE LOWER(COALESCE(email, '')) = LOWER(%s)
        LIMIT 1
    """
    with db_cursor(dictionary=True) as (_, cursor):
        cursor.execute(query, ((email or "").strip(),))
        return cursor.fetchone()


def get_user_by_email_or_phone(email=None, phone=None):
    """Return an existing user by email or phone if one is already registered."""
    normalized_email = (email or "").strip().lower()
    normalized_phone = (phone or "").strip()

    if not normalized_email and not normalized_phone:
        return None

    conditions = []
    params = []

    if normalized_email:
        conditions.append("LOWER(COALESCE(email, '')) = %s")
        params.append(normalized_email)
    if normalized_phone:
        conditions.append("phone = %s")
        params.append(normalized_phone)

    query = f"""
        SELECT id, name, email, phone, image_url, s3_image_key, s3_image_url, face_id, created_at
        FROM users
        WHERE {' OR '.join(conditions)}
        LIMIT 1
    """
    with db_cursor(dictionary=True) as (_, cursor):
        cursor.execute(query, tuple(params))
        return cursor.fetchone()


def get_user_by_face_id(face_id):
    query = """
        SELECT id, name, email, phone, s3_image_key, s3_image_url, face_id, created_at
        FROM users
        WHERE face_id = %s
        LIMIT 1
    """
    with db_cursor(dictionary=True) as (_, cursor):
        cursor.execute(query, (face_id,))
        return cursor.fetchone()


def get_user_token(user_id):
    query = """
        SELECT access_token FROM users WHERE id = %s LIMIT 1
    """
    with db_cursor(dictionary=True) as (_, cursor):
        cursor.execute(query, (user_id,))
        row = cursor.fetchone()
        return row.get("access_token") if row else None


def update_user(user_id, name=None, email=None, phone=None):
    """Update user details."""
    updates = []
    params = []

    if name is not None:
        updates.append("name = %s")
        params.append(name)
    if email is not None:
        updates.append("email = %s")
        params.append(email)
    if phone is not None:
        updates.append("phone = %s")
        params.append(phone)

    if not updates:
        return

    params.append(user_id)
    with db_cursor() as (_, cursor):
        cursor.execute(f"UPDATE users SET {', '.join(updates)} WHERE id = %s", params)


def update_user_face_id(user_id, face_id):
    """Persist the Rekognition face id for a registered user."""
    with db_cursor() as (_, cursor):
        cursor.execute("UPDATE users SET face_id = %s WHERE id = %s", (face_id, user_id))


def get_user_matched_image_ids(user_id):
    query = """
        SELECT DISTINCT photo_id FROM matches WHERE user_id = %s ORDER BY photo_id ASC
    """
    with db_cursor(dictionary=True) as (_, cursor):
        cursor.execute(query, (user_id,))
        return [row["photo_id"] for row in cursor.fetchall()]