"""Employee lookup API endpoints."""

from flask import Blueprint, request
from shopdb.utils.responses import success_response, error_response, ErrorCodes

employees_bp = Blueprint('employees', __name__)


@employees_bp.route('/search', methods=['GET'])
def search_employees():
    """
    Search employees by name.

    Query parameters:
    - q: Search query (searches first and last name)
    - limit: Max results (default 10)
    """
    query = request.args.get('q', '').strip()
    limit = min(int(request.args.get('limit', 10)), 50)

    if len(query) < 2:
        return error_response(
            ErrorCodes.VALIDATION_ERROR,
            'Search query must be at least 2 characters'
        )

    try:
        import pymysql
        conn = pymysql.connect(
            host='localhost',
            user='root',
            password='rootpassword',
            database='wjf_employees',
            cursorclass=pymysql.cursors.DictCursor
        )

        with conn.cursor() as cur:
            # Search by first name, last name, or SSO
            cur.execute('''
                SELECT SSO, First_Name, Last_Name, Team, Role, Picture
                FROM employees
                WHERE First_Name LIKE %s
                   OR Last_Name LIKE %s
                   OR CAST(SSO AS CHAR) LIKE %s
                ORDER BY Last_Name, First_Name
                LIMIT %s
            ''', (f'%{query}%', f'%{query}%', f'%{query}%', limit))

            employees = cur.fetchall()

        conn.close()

        return success_response(employees)

    except Exception as e:
        return error_response(
            ErrorCodes.DATABASE_ERROR,
            f'Employee lookup failed: {str(e)}',
            http_code=500
        )


@employees_bp.route('/lookup/<sso>', methods=['GET'])
def lookup_employee(sso):
    """Look up a single employee by SSO."""
    if not sso.isdigit():
        return error_response(
            ErrorCodes.VALIDATION_ERROR,
            'SSO must be numeric'
        )

    try:
        import pymysql
        conn = pymysql.connect(
            host='localhost',
            user='root',
            password='rootpassword',
            database='wjf_employees',
            cursorclass=pymysql.cursors.DictCursor
        )

        with conn.cursor() as cur:
            cur.execute(
                'SELECT SSO, First_Name, Last_Name, Team, Role, Picture FROM employees WHERE SSO = %s',
                (int(sso),)
            )
            employee = cur.fetchone()

        conn.close()

        if not employee:
            return error_response(
                ErrorCodes.NOT_FOUND,
                f'Employee with SSO {sso} not found',
                http_code=404
            )

        return success_response(employee)

    except Exception as e:
        return error_response(
            ErrorCodes.DATABASE_ERROR,
            f'Employee lookup failed: {str(e)}',
            http_code=500
        )


@employees_bp.route('/lookup', methods=['GET'])
def lookup_employees():
    """
    Look up multiple employees by SSO list.

    Query parameters:
    - sso: Comma-separated list of SSOs
    """
    sso_list = request.args.get('sso', '')
    ssos = [s.strip() for s in sso_list.split(',') if s.strip().isdigit()]

    if not ssos:
        return error_response(
            ErrorCodes.VALIDATION_ERROR,
            'At least one valid SSO is required'
        )

    try:
        import pymysql
        conn = pymysql.connect(
            host='localhost',
            user='root',
            password='rootpassword',
            database='wjf_employees',
            cursorclass=pymysql.cursors.DictCursor
        )

        with conn.cursor() as cur:
            placeholders = ','.join(['%s'] * len(ssos))
            cur.execute(
                f'SELECT SSO, First_Name, Last_Name, Team, Role, Picture FROM employees WHERE SSO IN ({placeholders})',
                [int(s) for s in ssos]
            )
            employees = cur.fetchall()

        conn.close()

        # Build name string
        names = ', '.join(
            f"{e['First_Name'].strip()} {e['Last_Name'].strip()}"
            for e in employees
        )

        return success_response({
            'employees': employees,
            'names': names
        })

    except Exception as e:
        return error_response(
            ErrorCodes.DATABASE_ERROR,
            f'Employee lookup failed: {str(e)}',
            http_code=500
        )