shopdb-flask/plugins/employees/api/routes.py

"""Employee lookup API endpoints.

These read from the separate employee directory DB (employee_connection is core
infrastructure exposed via shopdb.api, shared with search + notifications). The
endpoints are intentionally reachable by the unauthenticated shopfloor kiosk
displays (recognition wall), so they are not JWT-gated; keep them read-only and
never return more than the directory fields below.
"""

import csv
import glob
import io
import logging
import os

from flask import Blueprint, request, current_app
from flask_jwt_extended import jwt_required
from werkzeug.utils import secure_filename

from shopdb.api import (
    db,
    success_response,
    error_response,
    ErrorCodes,
    employee_connection,
    require_role,
)
from shopdb.api import Setting
from shopdb.api import send_upload

from ..models import DirectoryEmployee

logger = logging.getLogger(__name__)

employees_bp = Blueprint('employees', __name__)

# Columns safe to expose to the directory/recognition UI
_FIELDS = 'SSO, First_Name, Last_Name, Team, Role, Picture'

# Uploaded self-hosted employee photos live in the instance dir and are served
# publicly (kiosk recognition/recertification cards read them without auth).
EMPLOYEE_PHOTO_EXTENSIONS = {'.png', '.jpg', '.jpeg', '.gif', '.webp'}

# URL prefix a served upload resolves to (self-hosted mode).
EMPLOYEE_PHOTO_URL_PREFIX = '/api/employees/photo/'

# Fallback URL prefix external HR relative picture paths resolve under when
# the employee_photo_base_url setting is unset. Sites whose photos live on
# another host (e.g. the classic EmployeeDBAPP) set the setting to a full URL
# such as https://host/EmployeeDBAPP/images/ instead.
EMPLOYEE_PHOTO_STATIC_PREFIX = '/static/employees/'


def _employeephoto_dir():
    return os.path.join(current_app.instance_path, 'employeephotos')


def _selfhosted():
    """True when the directory is the app-owned table, not an external HR DB."""
    row = Setting.query.filter_by(key='employee_directory_mode').first()
    return (row.value if row and row.value else 'external').lower() == 'selfhosted'


def _require_selfhosted():
    """Guard for management endpoints - only valid in self-hosted mode."""
    if not _selfhosted():
        return error_response(
            ErrorCodes.VALIDATION_ERROR,
            'Directory is in external mode; manage people in the source HR database.',
            http_code=400)
    return None


def _require_selfhosted_photo():
    """Guard for photo write endpoints - 409 when the directory is external.

    In external mode the photo is owned by the HR database (read-only
    pass-through), so upload/delete cannot apply here."""
    if not _selfhosted():
        return error_response(
            ErrorCodes.CONFLICT,
            'Employee directory is external; photos are supplied by the HR '
            'database and cannot be uploaded or deleted here.',
            http_code=409)
    return None


def _external_photo_url(picture):
    """Turn an external HR Picture value into a usable URL, or None.

    Absolute URLs and already-rooted paths pass through untouched (future
    full-URL HR feeds); a bare relative path is served under the static prefix
    (current WJ convention, e.g. 'Support/210009518.png')."""
    if not picture:
        return None
    text = str(picture).strip()
    if not text:
        return None
    if text.startswith(('http://', 'https://', '/')):
        return text
    from shopdb.api import Setting
    base = (Setting.get('employee_photo_base_url') or '').strip() \
        or EMPLOYEE_PHOTO_STATIC_PREFIX
    return base.rstrip('/') + '/' + text.lstrip('/')


def _hr_picture(sso):
    """Raw Picture value for an SSO from the external HR directory. None on miss."""
    try:
        conn = employee_connection()
        with conn.cursor() as cur:
            cur.execute('SELECT Picture FROM employees WHERE SSO = %s', (int(sso),))
            row = cur.fetchone()
        conn.close()
        return row.get('Picture') if row else None
    except Exception:
        return None


def resolve_employee_display_name(sso):
    """Display name ("First Last") for an SSO in either directory mode.

    The shopfloor feed uses this as a live fallback when a notification has
    no stored employeename (e.g. imported without the employee source).
    None on any miss."""
    if sso is None or not str(sso).isdigit():
        return None
    # 1. The employee directory (selfhosted table or external wjf_employees).
    if _selfhosted():
        emp = db.session.get(DirectoryEmployee, int(sso))
        if emp:
            name = f'{emp.firstname} {emp.lastname}'.strip()
            if name:
                return name
    else:
        try:
            conn = employee_connection()
            with conn.cursor() as cur:
                cur.execute(
                    'SELECT First_Name, Last_Name FROM employees WHERE SSO = %s',
                    (int(sso),))
                row = cur.fetchone()
            conn.close()
            if row:
                first = row.get('First_Name') or ''
                last = row.get('Last_Name') or ''
                name = f'{first.strip()} {last.strip()}'.strip()
                if name:
                    return name
        except Exception:
            # Logged, not swallowed. This is the branch that fails on a live
            # site - an unreachable HR host, a rotated credential, a renamed
            # column - and a bare pass made all of those look identical to
            # "no such employee": a lowercase SSO where a name should be, and
            # no photo, with nothing written down anywhere.
            current_app.logger.exception(
                'Employee directory lookup failed for SSO %s', sso)

    # 2. Fallback: a shopdb login account for this SSO (e.g. a user from another
    #    location not carried in the directory). Their name lives on the User
    #    record; GE usernames are the SSO. Photo still falls back to the GE mark.
    try:
        from shopdb.api import User
        user = User.query.filter_by(username=str(sso)).first()
        if user:
            name = f'{(user.firstname or "").strip()} {(user.lastname or "").strip()}'.strip()
            if name:
                return name
    except Exception:
        current_app.logger.exception(
            'User-account name fallback failed for SSO %s', sso)
    return None


def resolve_employee_photo_url(sso, external_picture=None):
    """Single resolver both consumers share: the display photo URL for an SSO.

    Self-hosted: the served upload URL when the employee has an uploaded photo,
    else None (the legacy Picture text field does not drive the photo here).
    External: the HR-supplied Picture resolved to a URL (pass external_picture to
    avoid a re-query), else None. Returns None on any miss or bad SSO."""
    if sso is None or not str(sso).isdigit():
        return None
    if _selfhosted():
        emp = db.session.get(DirectoryEmployee, int(sso))
        if emp and emp.photofilename:
            return EMPLOYEE_PHOTO_URL_PREFIX + emp.photofilename
        return None
    picture = external_picture if external_picture is not None else _hr_picture(sso)
    return _external_photo_url(picture)


def _with_photo_url(employee):
    """Add the resolved photourl to an employee dict (self-hosted or external)."""
    employee['photourl'] = resolve_employee_photo_url(
        employee.get('SSO'), employee.get('Picture'))
    return employee


@employees_bp.route('/resolve/<sso>', methods=['GET'])
@jwt_required()
def resolve_employee(sso):
    """Resolve one SSO to a name and photo, and say HOW.

    Built because the failure it diagnoses is invisible. The shopfloor board
    resolves names and photos through two functions that returned None on any
    problem, so an unreachable HR host, a rotated credential and a genuinely
    unknown SSO all produced the same thing: a lowercase SSO where a name
    should be, and no photo. Nothing distinguished them and nothing was logged.

    This returns the same answer the board gets, plus which source produced it,
    which mode the directory is in, and the error when a source failed. It is
    the difference between "the board is broken" and "the HR host refused the
    connection at 09:12".
    """
    result = {
        'sso': sso,
        'mode': 'selfhosted' if _selfhosted() else 'external',
        'name': None,
        'photourl': None,
        'source': None,
        'error': None,
    }

    if sso is None or not str(sso).isdigit():
        result['error'] = 'SSO must be digits'
        return success_response(result)

    if _selfhosted():
        employee = db.session.get(DirectoryEmployee, int(sso))
        if employee:
            name = '{} {}'.format(employee.firstname or '',
                                  employee.lastname or '').strip()
            if name:
                result['name'] = name
                result['source'] = 'directory'
    else:
        try:
            conn = employee_connection()
            with conn.cursor() as cur:
                cur.execute(
                    'SELECT First_Name, Last_Name FROM employees WHERE SSO = %s',
                    (int(sso),))
                row = cur.fetchone()
            conn.close()
            if row:
                name = '{} {}'.format((row.get('First_Name') or '').strip(),
                                      (row.get('Last_Name') or '').strip()).strip()
                if name:
                    result['name'] = name
                    result['source'] = 'hrdirectory'
            else:
                result['error'] = 'No row in the HR directory for this SSO'
        except Exception as exception:
            # Reported, not hidden. This string is the whole point of the
            # endpoint.
            result['error'] = '{}: {}'.format(type(exception).__name__, exception)
            current_app.logger.exception(
                'Employee directory lookup failed for SSO %s', sso)

    if not result['name']:
        from shopdb.api import User
        user = User.query.filter_by(username=str(sso)).first()
        if user:
            name = '{} {}'.format((user.firstname or '').strip(),
                                  (user.lastname or '').strip()).strip()
            if name:
                result['name'] = name
                result['source'] = 'useraccount'

    result['photourl'] = resolve_employee_photo_url(sso)
    if result['name'] is None and result['error'] is None:
        result['error'] = 'No name found in the directory or in a user account'
    return success_response(result)


@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'
        )

    if _selfhosted():
        term = f'%{query}%'
        rows = (DirectoryEmployee.query
                .filter(db.or_(DirectoryEmployee.firstname.ilike(term),
                               DirectoryEmployee.lastname.ilike(term),
                               db.cast(DirectoryEmployee.sso, db.String).ilike(term)))
                .order_by(DirectoryEmployee.lastname, DirectoryEmployee.firstname)
                .limit(limit).all())
        return success_response([_with_photo_url(e.to_dict()) for e in rows])

    try:
        conn = employee_connection()
        with conn.cursor() as cur:
            cur.execute(f'''
                SELECT {_FIELDS}
                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([_with_photo_url(e) for e in employees])
    except Exception:
        logger.exception('Employee search failed')
        return error_response(
            ErrorCodes.INTERNAL_ERROR,
            'Employee lookup failed',
            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'
        )

    if _selfhosted():
        emp = db.session.get(DirectoryEmployee, int(sso))
        if not emp:
            return error_response(ErrorCodes.NOT_FOUND,
                                  f'Employee with SSO {sso} not found', http_code=404)
        return success_response(_with_photo_url(emp.to_dict()))

    try:
        conn = employee_connection()
        with conn.cursor() as cur:
            cur.execute(
                f'SELECT {_FIELDS} 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(_with_photo_url(employee))
    except Exception:
        logger.exception('Employee lookup failed for SSO %s', sso)
        return error_response(
            ErrorCodes.INTERNAL_ERROR,
            'Employee lookup failed',
            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'
        )

    if _selfhosted():
        rows = DirectoryEmployee.query.filter(
            DirectoryEmployee.sso.in_([int(s) for s in ssos])).all()
        employees = [_with_photo_url(e.to_dict()) for e in rows]
        names = ', '.join(f"{e['First_Name'].strip()} {e['Last_Name'].strip()}"
                          for e in employees)
        return success_response({'employees': employees, 'names': names})

    try:
        conn = employee_connection()
        with conn.cursor() as cur:
            placeholders = ','.join(['%s'] * len(ssos))
            cur.execute(
                f'SELECT {_FIELDS} FROM employees WHERE SSO IN ({placeholders})',
                [int(s) for s in ssos]
            )
            employees = cur.fetchall()
        conn.close()

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

        return success_response({
            'employees': employees,
            'names': names
        })
    except Exception:
        logger.exception('Employee multi-lookup failed')
        return error_response(
            ErrorCodes.INTERNAL_ERROR,
            'Employee lookup failed',
            http_code=500
        )


# =============================================================================
# Self-hosted directory management (only when directory_mode=selfhosted)
# =============================================================================

@employees_bp.route('/directory', methods=['GET'])
@jwt_required(optional=True)
def list_directory():
    """Full self-hosted directory (for the management page)."""
    guard = _require_selfhosted()
    if guard:
        return guard
    rows = (DirectoryEmployee.query
            .order_by(DirectoryEmployee.lastname, DirectoryEmployee.firstname).all())
    return success_response([_with_photo_url(e.to_dict()) for e in rows])


def _employee_from_payload(data):
    """Build kwargs from a payload accepting either external-style (SSO,
    First_Name...) or plain (sso, firstname...) keys."""
    def pick(*keys):
        for key in keys:
            if data.get(key) not in (None, ''):
                return data.get(key)
        return None
    return {
        'sso': pick('sso', 'SSO'),
        'firstname': pick('firstname', 'First_Name'),
        'lastname': pick('lastname', 'Last_Name'),
        'team': pick('team', 'Team'),
        'role': pick('role', 'Role'),
        'picture': pick('picture', 'Picture'),
    }


@employees_bp.route('/directory', methods=['POST'])
@jwt_required()
@require_role('admin')
def create_directory_employee():
    guard = _require_selfhosted()
    if guard:
        return guard
    fields = _employee_from_payload(request.get_json() or {})
    if not (fields['sso'] and fields['firstname'] and fields['lastname']):
        return error_response(ErrorCodes.VALIDATION_ERROR, 'sso, firstname and lastname are required')
    try:
        sso = int(fields['sso'])
    except (ValueError, TypeError):
        return error_response(ErrorCodes.VALIDATION_ERROR, 'sso must be numeric')
    if db.session.get(DirectoryEmployee, sso):
        return error_response(ErrorCodes.CONFLICT, f'SSO {sso} already exists', http_code=409)
    emp = DirectoryEmployee(sso=sso, firstname=fields['firstname'], lastname=fields['lastname'],
                            team=fields['team'], role=fields['role'], picture=fields['picture'])
    db.session.add(emp)
    db.session.commit()
    return success_response(emp.to_dict(), message='Employee added', http_code=201)


@employees_bp.route('/directory/<int:sso>', methods=['PUT'])
@jwt_required()
@require_role('admin')
def update_directory_employee(sso):
    guard = _require_selfhosted()
    if guard:
        return guard
    emp = db.session.get(DirectoryEmployee, sso)
    if not emp:
        return error_response(ErrorCodes.NOT_FOUND, 'Employee not found', http_code=404)
    fields = _employee_from_payload(request.get_json() or {})
    if fields['firstname']:
        emp.firstname = fields['firstname']
    if fields['lastname']:
        emp.lastname = fields['lastname']
    for key in ('team', 'role', 'picture'):
        if key in (request.get_json() or {}) or fields[key] is not None:
            setattr(emp, key, fields[key])
    db.session.commit()
    return success_response(emp.to_dict(), message='Employee updated')


@employees_bp.route('/directory/<int:sso>', methods=['DELETE'])
@jwt_required()
@require_role('admin')
def delete_directory_employee(sso):
    guard = _require_selfhosted()
    if guard:
        return guard
    emp = db.session.get(DirectoryEmployee, sso)
    if not emp:
        return error_response(ErrorCodes.NOT_FOUND, 'Employee not found', http_code=404)
    db.session.delete(emp)
    db.session.commit()
    return success_response(message='Employee removed')


@employees_bp.route('/directory/import', methods=['POST'])
@jwt_required()
@require_role('admin')
def import_directory():
    """Bulk upsert from CSV. Accepts headers SSO,First_Name,Last_Name,Team,Role,
    Picture (case-insensitive; sso/firstname/... also accepted)."""
    guard = _require_selfhosted()
    if guard:
        return guard
    text = ''
    if 'file' in request.files:
        text = request.files['file'].read().decode('utf-8-sig', errors='replace')
    else:
        data = request.get_json(silent=True) or {}
        text = data.get('csv', '')
    if not text.strip():
        return error_response(ErrorCodes.VALIDATION_ERROR, 'No CSV provided')

    reader = csv.DictReader(io.StringIO(text))
    # Normalize headers to lower for tolerant matching.
    added = updated = skipped = 0
    for raw in reader:
        row = {(k or '').strip().lower(): (v or '').strip() for k, v in raw.items()}
        sso_raw = row.get('sso') or row.get('sso ')
        first = row.get('first_name') or row.get('firstname')
        last = row.get('last_name') or row.get('lastname')
        if not (sso_raw and sso_raw.isdigit() and first and last):
            skipped += 1
            continue
        sso = int(sso_raw)
        team = row.get('team') or None
        role = row.get('role') or None
        picture = row.get('picture') or None
        emp = db.session.get(DirectoryEmployee, sso)
        if emp:
            emp.firstname, emp.lastname, emp.team, emp.role, emp.picture = first, last, team, role, picture
            updated += 1
        else:
            db.session.add(DirectoryEmployee(sso=sso, firstname=first, lastname=last,
                                             team=team, role=role, picture=picture))
            added += 1
    db.session.commit()
    return success_response({'added': added, 'updated': updated, 'skipped': skipped},
                            message=f'Import done: {added} added, {updated} updated, {skipped} skipped.')


# =============================================================================
# Self-hosted employee photos (upload/replace/delete + public serve)
# =============================================================================

@employees_bp.route('/<int:sso>/photo', methods=['POST'])
@jwt_required()
@require_role('admin')
def upload_employee_photo(sso):
    """Upload (or replace) the photo for a self-hosted directory employee.

    multipart/form-data: file=<image>. Saves to the instance employeephotos dir
    as photo-<sso><ext> (one photo per person) and points photofilename at it.
    Re-upload replaces the old file even when the extension changes. External
    mode is a 409 (photo is owned by the HR database)."""
    guard = _require_selfhosted_photo()
    if guard:
        return guard

    emp = db.session.get(DirectoryEmployee, sso)
    if not emp:
        return error_response(ErrorCodes.NOT_FOUND, 'Employee not found', http_code=404)

    upload = request.files.get('file')
    if not upload or not upload.filename:
        return error_response(ErrorCodes.VALIDATION_ERROR, 'No file provided')
    ext = os.path.splitext(upload.filename)[1].lower()
    if ext not in EMPLOYEE_PHOTO_EXTENSIONS:
        return error_response(ErrorCodes.VALIDATION_ERROR,
                              f'Unsupported image type {ext}')

    photodir = _employeephoto_dir()
    os.makedirs(photodir, exist_ok=True)

    # Wipe any prior photo-<sso>.* so a new extension does not orphan the old one.
    for old in glob.glob(os.path.join(photodir, secure_filename(f'photo-{sso}') + '.*')):
        os.remove(old)

    filename = secure_filename(f'photo-{sso}{ext}')
    upload.save(os.path.join(photodir, filename))

    emp.photofilename = filename
    db.session.commit()

    return success_response(_with_photo_url(emp.to_dict()), message='Employee photo uploaded')


@employees_bp.route('/photo/<path:filename>', methods=['GET'])
def serve_employee_photo(filename):
    """Serve an uploaded employee photo (public - kiosk cards read it)."""
    return send_upload(_employeephoto_dir(), filename)


@employees_bp.route('/<int:sso>/photo', methods=['DELETE'])
@jwt_required()
@require_role('admin')
def delete_employee_photo(sso):
    """Clear an employee photo and delete the uploaded file. External mode 409s."""
    guard = _require_selfhosted_photo()
    if guard:
        return guard

    emp = db.session.get(DirectoryEmployee, sso)
    if not emp:
        return error_response(ErrorCodes.NOT_FOUND, 'Employee not found', http_code=404)

    if emp.photofilename:
        # secure_filename strips any traversal; the file lives in our dir only.
        path = os.path.join(_employeephoto_dir(), secure_filename(emp.photofilename))
        if os.path.exists(path):
            os.remove(path)

    emp.photofilename = None
    db.session.commit()

    return success_response(_with_photo_url(emp.to_dict()), message='Employee photo removed')