from models.db import db_cursor


def get_visitor_registration_by_identifier(identifier):
    """Fetch a visitor registration row using either email or mobile number."""
    normalized = (identifier or "").strip()
    if not normalized:
        return None

    query = """
        SELECT id, name, mobile_number, email
        FROM visitors_registration
        WHERE LOWER(COALESCE(email, '')) = LOWER(%s)
           OR mobile_number = %s
        LIMIT 1
    """
    with db_cursor(dictionary=True) as (_, cursor):
        cursor.execute(query, (normalized, normalized))
        return cursor.fetchone()


def get_visitors(search=None, limit=25, offset=0):
    """Fetch visitors with optional search and pagination."""
    conditions = []
    params = []

    if search:
        search_pattern = f"%{search}%"
        conditions.append("(name LIKE %s OR mobile_number LIKE %s OR email LIKE %s)")
        params.extend([search_pattern, search_pattern, search_pattern])

    where_clause = f"WHERE {' AND '.join(conditions)}" if conditions else ""
    query = f"""
        SELECT id, name, mobile_number, email
        FROM visitors_registration
        {where_clause}
        ORDER BY id DESC
        LIMIT %s OFFSET %s
    """
    params.extend([limit, offset])

    with db_cursor(dictionary=True) as (_, cursor):
        cursor.execute(query, tuple(params))
        return cursor.fetchall()


def count_visitors(search=None):
    """Count total visitors matching search criteria."""
    conditions = []
    params = []

    if search:
        search_pattern = f"%{search}%"
        conditions.append("(name LIKE %s OR mobile_number LIKE %s OR email LIKE %s)")
        params.extend([search_pattern, search_pattern, search_pattern])

    where_clause = f"WHERE {' AND '.join(conditions)}" if conditions else ""
    query = f"SELECT COUNT(*) AS total FROM visitors_registration {where_clause}"

    with db_cursor(dictionary=True) as (_, cursor):
        cursor.execute(query, tuple(params))
        row = cursor.fetchone()
        return row.get("total", 0) if row else 0


def get_visitor_by_id(visitor_id):
    """Fetch a single visitor by ID."""
    query = """
        SELECT id, name, mobile_number, email
        FROM visitors_registration
        WHERE id = %s LIMIT 1
    """
    with db_cursor(dictionary=True) as (_, cursor):
        cursor.execute(query, (visitor_id,))
        return cursor.fetchone()


def create_visitor(name, mobile_number=None, email=None):
    """Create a new visitor in the visitors_registration table."""
    query = """
        INSERT INTO visitors_registration (name, mobile_number, email)
        VALUES (%s, %s, %s)
    """
    with db_cursor() as (_, cursor):
        cursor.execute(query, (name, mobile_number, email))
        return cursor.lastrowid


def update_visitor(visitor_id, name, mobile_number, email):
    """Update visitor details."""
    query = """
        UPDATE visitors_registration
        SET name = %s, mobile_number = %s, email = %s
        WHERE id = %s
    """
    with db_cursor() as (_, cursor):
        cursor.execute(query, (name, mobile_number, email, visitor_id))


def get_remaining_visitors():
    """Fetch visitors who have not completed face registration (not in users table)."""
    query = """
        SELECT v.id, v.name, v.email, v.mobile_number
        FROM visitors_registration v
        WHERE v.email NOT IN (
            SELECT u.email FROM users u WHERE u.email IS NOT NULL
        )
        ORDER BY v.id DESC
    """
    with db_cursor(dictionary=True) as (_, cursor):
        cursor.execute(query)
        return cursor.fetchall()