"""
admin_routes.py

Purpose:
  Admin panel HTML and actions for managing users. Includes add, remove, and
  change-password endpoints, protected by the admin_required decorator.
  Also exposes admin-only JSON APIs to view and manage any user's cameras and
  images, and to read audit logs. All routes are CSRF-protected globally.

Routes:
  - GET /admin
  - POST /admin/add_user
  - POST /admin/remove_user/<int:user_id>
  - POST /admin/change_password/<int:user_id>
  - GET  /admin/users.json
  - GET  /admin/user/<int:user_id>/cameras.json
  - GET  /admin/user/<int:user_id>/images.json
  - POST /admin/user/<int:user_id>/cameras/rename
  - POST /admin/user/<int:user_id>/cameras/delete
  - POST /admin/image/delete
  - GET  /admin/logs.json
"""

import os
import re
import sqlite3
import bcrypt
import time
from flask import Blueprint, render_template, request, redirect, url_for, flash, session, jsonify
from .db import get_db
from .security import admin_required
from .audit import log_admin_action, get_request_ip
from .paths import STATIC_PATH, USER_PHOTOS_REAL
from .images_service import latest_from_db_or_fs, collect_user_images
from .helpers import parse_ts_from_any, format_dt, normalize_to_static_user_photos, build_media_url, build_share_url


bp = Blueprint('admin_routes', __name__)


@bp.route('/admin')
@admin_required
def admin_panel():
    db = get_db()
    users = db.execute('SELECT id, username, IFNULL(is_admin,0) AS is_admin FROM users ORDER BY username COLLATE NOCASE').fetchall()
    return render_template('admin.html', users=users)


@bp.route('/admin/add_user', methods=['POST'])
@admin_required
def admin_add_user():
    username = (request.form.get('username') or '').strip().lower()
    password = request.form.get('password') or ''
    # Strict username policy: 3-32 chars, lowercase letters, digits, underscore
    if not username or not password:
        flash('Korisničko ime i lozinka su obavezni.', 'error')
        return redirect(url_for('admin_routes.admin_panel'))
    if not re.fullmatch(r'[a-z0-9_]{3,32}', username):
        flash('Korisničko ime smije sadržavati samo mala slova, brojeve i donju crtu (3-32 znaka).', 'error')
        return redirect(url_for('admin_routes.admin_panel'))
    try:
        pwd_hash = bcrypt.hashpw(password.encode('utf-8'), bcrypt.gensalt()).decode('utf-8')
        db = get_db()
        db.execute('INSERT INTO users (username, password_hash) VALUES (?, ?)', (username, pwd_hash))
        db.commit()
        flash('Korisnik dodan.', 'success')
        try:
            log_admin_action('add_user', admin_user_id=session.get('user_id'), target_user_id=None, ip=get_request_ip(request), detail=f'username={username}')
        except Exception:
            pass
    except sqlite3.IntegrityError:
        flash('Korisničko ime već postoji.', 'error')
    except sqlite3.Error:
        flash('Greška baze.', 'error')
    return redirect(url_for('admin_routes.admin_panel'))


@bp.route('/admin/remove_user/<int:user_id>', methods=['POST'])
@admin_required
def admin_remove_user(user_id: int):
    try:
        db = get_db()
        db.execute('DELETE FROM cameras WHERE user_id = ?', (user_id,))
        db.execute('DELETE FROM users WHERE id = ?', (user_id,))
        db.commit()
        flash('Korisnik i kamere obrisani.', 'success')
        try:
            log_admin_action('remove_user', admin_user_id=session.get('user_id'), target_user_id=user_id, ip=get_request_ip(request))
        except Exception:
            pass
    except sqlite3.Error:
        flash('Greška baze.', 'error')
    return redirect(url_for('admin_routes.admin_panel'))


@bp.route('/admin/change_password/<int:user_id>', methods=['POST'])
@admin_required
def admin_change_password(user_id: int):
    """Change a user's password.
    - Supports form POST (HTML) and JSON (AJAX) with CSRF protection.
    - Enforces minimal password policy server-side.
    """
    is_json = request.is_json or (
        (request.headers.get('Content-Type') or '').split(';', 1)[0].strip() == 'application/json'
    )

    if is_json:
        data = request.get_json(silent=True) or {}
        new_password = (data.get('new_password') or '').strip()
    else:
        new_password = (request.form.get('new_password') or '').strip()

    # Minimal server-side policy; UI may enforce stricter rules
    if len(new_password) < 8:
        if is_json:
            return jsonify({'success': False, 'message': 'Lozinka mora imati najmanje 8 znakova.'}), 400
        flash('Lozinka mora imati najmanje 8 znakova.', 'error')
        return redirect(url_for('admin_routes.admin_panel'))

    try:
        new_hash = bcrypt.hashpw(new_password.encode('utf-8'), bcrypt.gensalt()).decode('utf-8')
        db = get_db()
        db.execute('UPDATE users SET password_hash = ? WHERE id = ?', (new_hash, user_id))
        db.commit()
        try:
            log_admin_action('change_password', admin_user_id=session.get('user_id'), target_user_id=user_id, ip=get_request_ip(request))
        except Exception:
            pass
        if is_json:
            return jsonify({'success': True})
        flash('Lozinka promijenjena.', 'success')
    except sqlite3.Error:
        if is_json:
            return jsonify({'success': False, 'message': 'Greška baze.'}), 500
        flash('Greška baze.', 'error')
    return redirect(url_for('admin_routes.admin_panel'))


# ---------- Admin JSON APIs (admin-only) ----------

@bp.route('/admin/users.json')
@admin_required
def admin_users_json():
    db = get_db()
    rows = db.execute('''
        SELECT u.id, u.username, IFNULL(u.is_admin,0) AS is_admin,
               COUNT(c.camera_id) AS camera_count
        FROM users u
        LEFT JOIN cameras c ON c.user_id = u.id
        GROUP BY u.id
        ORDER BY u.username COLLATE NOCASE
    ''').fetchall()
    users = []
    for r in rows:
        users.append({
            'id': r['id'],
            'username': r['username'],
            'is_admin': int(r['is_admin'] or 0) == 1,
            'camera_count': int(r['camera_count'] or 0)
        })
    return jsonify({'users': users})


@bp.route('/admin/user/<int:user_id>/cameras.json')
@admin_required
def admin_user_cameras_json(user_id: int):
    db = get_db()
    cols = [row[1] for row in db.execute('PRAGMA table_info(cameras)').fetchall()]
    has_model = 'model' in set(cols)
    select_fields = 'camera_id, camera_name, file_paths' + (', model' if has_model else '')
    cur = db.execute(f'SELECT {select_fields} FROM cameras WHERE user_id = ? ORDER BY camera_name COLLATE NOCASE', (user_id,))
    rows = []
    for r in cur.fetchall():
        cam_id = str(r['camera_id'])
        latest_dt, _ = latest_from_db_or_fs(cam_id, r['file_paths'] or '', STATIC_PATH)
        thumb_url = '/static/camera_render.png'
        model = (r['model'] if has_model else None) or 'Vision mini'
        rows.append({
            'camera_id': cam_id,
            'camera_name': r['camera_name'],
            'last_active': format_dt(latest_dt) if latest_dt else 'Nema aktivnosti',
            'latest_image_ts': int(latest_dt.timestamp()) if latest_dt else None,
            'thumbnail_url': thumb_url,
            'model': model
        })
    return jsonify({'cameras': rows})


@bp.route('/admin/user/<int:user_id>/images.json')
@admin_required
def admin_user_images_json(user_id: int):
    # Return images organized by camera with pagination
    db = get_db()
    all_images = collect_user_images(user_id, db, STATIC_PATH)
    
    # Get camera info
    cam_rows = db.execute('SELECT camera_id, camera_name FROM cameras WHERE user_id = ? ORDER BY camera_name', (user_id,)).fetchall()
    camera_names = {str(r['camera_id']): r['camera_name'] for r in cam_rows}
    
    # Organize images by camera
    cameras = {}
    for img in all_images:
        cam_id = img.get('camera_id')
        if not cam_id:
            continue
        if cam_id not in cameras:
            cameras[cam_id] = {
                'camera_id': cam_id,
                'camera_name': camera_names.get(cam_id, f'Camera {cam_id}'),
                'images': []
            }
        cameras[cam_id]['images'].append({
            'url': img['url'],
            'rel': img['rel'],
            'camera_id': cam_id,
            'ts': format_dt(img.get('ts')) if img.get('ts') else ''
        })
    
    # Apply pagination per camera
    offset = max(0, int(request.args.get('offset', 0)))
    limit = max(1, min(int(request.args.get('limit', 30)), 100))
    
    result_cameras = []
    for cam_id, cam_data in cameras.items():
        total_images = len(cam_data['images'])
        images_slice = cam_data['images'][offset:offset + limit]
        has_more = (offset + limit) < total_images
        
        result_cameras.append({
            'camera_id': cam_id,
            'camera_name': cam_data['camera_name'],
            'images': images_slice,
            'total_count': total_images,
            'has_more': has_more,
            'next_offset': offset + limit if has_more else None
        })
    
    # Sort cameras by name for consistent display
    result_cameras.sort(key=lambda x: x['camera_name'])
    
    return jsonify({
        'cameras': result_cameras,
        'total_cameras': len(result_cameras)
    })


@bp.route('/admin/share_link', methods=['POST'])
@admin_required
def admin_generate_share_link():
    """Generate a short-lived public share URL for an image.
    Admin-only endpoint to prevent user-level mass sharing without oversight.
    Body: { rel: 'User-photos/PICT_...', ttl_minutes: 30 }
    """
    data = request.get_json(silent=True) or {}
    rel = normalize_to_static_user_photos(str(data.get('rel') or ''))
    ttl_minutes = int(data.get('ttl_minutes') or 14*24*60)
    ttl_minutes = max(1, min(ttl_minutes, 24*60))  # between 1 minute and 24h
    if not rel:
        return jsonify({'success': False, 'message': 'Nedostaje slika.'}), 400
    # Verify file exists and parse camera
    ts, cam = parse_ts_from_any(rel)
    if not cam:
        return jsonify({'success': False, 'message': 'Neispravno ime datoteke.'}), 400
    abs_path = os.path.join(STATIC_PATH, rel)
    if not os.path.exists(abs_path):
        return jsonify({'success': False, 'message': 'Datoteka ne postoji.'}), 404
    # Generate expiring URL
    exp = int(time.time()) + ttl_minutes * 60
    url = build_share_url(rel, exp)
    return jsonify({'success': True, 'url': url, 'expires_at': exp})


@bp.route('/admin/user/<int:user_id>/camera/<camera_id>/images.json')
@admin_required
def admin_user_camera_images_json(user_id: int, camera_id: str):
    # Return images for a specific camera with pagination
    if not (camera_id.isdigit() and len(camera_id) == 12):
        return jsonify({'error': 'Invalid camera ID'}), 400
    
    db = get_db()
    # Verify camera belongs to user
    cam_check = db.execute('SELECT camera_name FROM cameras WHERE user_id = ? AND camera_id = ?', (user_id, camera_id)).fetchone()
    if not cam_check:
        return jsonify({'error': 'Camera not found'}), 404
    
    all_images = collect_user_images(user_id, db, STATIC_PATH)
    
    # Filter for this specific camera
    camera_images = [img for img in all_images if img.get('camera_id') == camera_id]
    
    # Apply pagination
    offset = max(0, int(request.args.get('offset', 0)))
    limit = max(1, min(int(request.args.get('limit', 30)), 100))
    
    total_images = len(camera_images)
    images_slice = camera_images[offset:offset + limit]
    has_more = (offset + limit) < total_images
    
    # Convert timestamps to string
    result_images = []
    for img in images_slice:
        result_images.append({
            'url': img['url'],
            'rel': img['rel'],
            'camera_id': camera_id,
            'ts': format_dt(img.get('ts')) if img.get('ts') else ''
        })
    
    return jsonify({
        'camera_id': camera_id,
        'camera_name': cam_check['camera_name'],
        'images': result_images,
        'total_count': total_images,
        'has_more': has_more,
        'next_offset': offset + limit if has_more else None,
        'current_offset': offset
    })


@bp.route('/admin/user/<int:user_id>/cameras/add', methods=['POST'])
@admin_required
def admin_user_camera_add(user_id: int):
    data = request.get_json(silent=True) or {}
    camera_id = str(data.get('camera_id', '')).strip()
    camera_name = (data.get('camera_name') or '').strip()
    # Validate camera id and name
    if not (camera_id.isdigit() and len(camera_id) == 12):
        return jsonify({'success': False, 'message': 'ID kamere mora biti 12 brojeva.'}), 400
    if not camera_name or len(camera_name) > 60 or not re.fullmatch(r'[\w\s\-.]{1,60}', camera_name):
        return jsonify({'success': False, 'message': 'Neispravan naziv kamere.'}), 400
    
    db = get_db()
    try:
        # Check if camera already exists for any user
        existing = db.execute('SELECT user_id FROM cameras WHERE camera_id=?', (camera_id,)).fetchone()
        if existing:
            return jsonify({'success': False, 'message': 'Kamera s tim ID već postoji.'}), 400
        
        # Check if user exists
        user_exists = db.execute('SELECT id FROM users WHERE id=?', (user_id,)).fetchone()
        if not user_exists:
            return jsonify({'success': False, 'message': 'Korisnik ne postoji.'}), 404
        
        # Insert new camera
        db.execute('''
            INSERT INTO cameras (user_id, camera_id, camera_name) 
            VALUES (?, ?, ?)
        ''', (user_id, camera_id, camera_name))
        db.commit()
        
        try:
            log_admin_action('add_camera', admin_user_id=session.get('user_id'), target_user_id=user_id, ip=get_request_ip(request), detail=f'camera_id={camera_id}, name={camera_name}')
        except Exception:
            pass
            
        return jsonify({'success': True, 'message': 'Kamera je uspješno dodana.'})
    except sqlite3.Error as e:
        return jsonify({'success': False, 'message': 'Greška baze podataka.'}), 500


@bp.route('/admin/user/<int:user_id>/cameras/rename', methods=['POST'])
@admin_required
def admin_user_camera_rename(user_id: int):
    data = request.get_json(silent=True) or {}
    camera_id = str(data.get('camera_id', '')).strip()
    new_name = (data.get('camera_name') or '').strip()
    if not (camera_id.isdigit() and len(camera_id) == 12):
        return jsonify({'success': False, 'message': 'Neispravni podaci.'}), 400
    if not new_name or len(new_name) > 60 or not re.fullmatch(r'[\w\s\-.]{1,60}', new_name):
        return jsonify({'success': False, 'message': 'Neispravan naziv kamere.'}), 400
    db = get_db()
    try:
        cur = db.execute('UPDATE cameras SET camera_name=? WHERE user_id=? AND camera_id=?', (new_name, user_id, camera_id))
        db.commit()
        if cur.rowcount == 0:
            return jsonify({'success': False, 'message': 'Kamera nije pronađena.'}), 404
        try:
            log_admin_action('rename_camera', admin_user_id=session.get('user_id'), target_user_id=user_id, ip=get_request_ip(request), detail=f'camera_id={camera_id}')
        except Exception:
            pass
        return jsonify({'success': True})
    except sqlite3.Error:
        return jsonify({'success': False, 'message': 'Greška baze.'}), 500


@bp.route('/admin/user/<int:user_id>/cameras/delete', methods=['POST'])
@admin_required
def admin_user_camera_delete(user_id: int):
    data = request.get_json(silent=True) or {}
    camera_id = str(data.get('camera_id', '')).strip()
    if not (camera_id.isdigit() and len(camera_id) == 12):
        return jsonify({'success': False, 'message': 'Neispravan ID kamere.'}), 400
    db = get_db()
    try:
        cur = db.execute('DELETE FROM cameras WHERE user_id=? AND camera_id=?', (user_id, camera_id))
        db.commit()
        if cur.rowcount == 0:
            return jsonify({'success': False, 'message': 'Kamera nije pronađena.'}), 404
        try:
            log_admin_action('delete_camera', admin_user_id=session.get('user_id'), target_user_id=user_id, ip=get_request_ip(request), detail=f'camera_id={camera_id}')
        except Exception:
            pass
        return jsonify({'success': True})
    except sqlite3.Error:
        return jsonify({'success': False, 'message': 'Greška baze.'}), 500


@bp.route('/admin/image/delete', methods=['POST'])
@admin_required
def admin_delete_image():
    data = request.get_json(silent=True) or {}
    rel = normalize_to_static_user_photos(data.get('rel', ''))
    user_id = int(data.get('user_id') or 0)
    if not rel or not user_id:
        return jsonify({'success': False, 'message': 'Nedostaju podaci.'}), 400
    # Validate that the image camera belongs to the specified user
    _, cam = parse_ts_from_any(rel)
    if not cam:
        return jsonify({'success': False, 'message': 'Neispravno ime datoteke.'}), 400
    db = get_db()
    owner = db.execute('SELECT 1 FROM cameras WHERE user_id=? AND camera_id=?', (user_id, cam)).fetchone()
    if not owner:
        return jsonify({'success': False, 'message': 'Nedozvoljeno.'}), 403
    abs_path = os.path.join(STATIC_PATH, rel)
    real_static = USER_PHOTOS_REAL if rel.startswith('User-photos/') else os.path.realpath(STATIC_PATH)
    real_abs = os.path.realpath(abs_path)
    if not real_abs.startswith(real_static):
        return jsonify({'success': False, 'message': 'Putanja nije dozvoljena.'}), 400
    if os.path.exists(abs_path):
        parent_dir = os.path.dirname(abs_path)
        if not os.access(parent_dir, os.W_OK):
            return jsonify({'success': False, 'message': 'Server nema dozvolu za brisanje datoteke.'}), 403
        try:
            os.remove(abs_path)
        except PermissionError:
            return jsonify({'success': False, 'message': 'Server nema dozvolu za brisanje datoteke.'}), 403
        except OSError:
            return jsonify({'success': False, 'message': 'Ne mogu obrisati datoteku.'}), 500
    # Update DB file_paths if present
    try:
        row = db.execute('SELECT file_paths FROM cameras WHERE user_id=? AND camera_id=?', (user_id, cam)).fetchone()
        if row and row['file_paths']:
            fps = [p.strip() for p in row['file_paths'].split(',') if p.strip()]
            name_only = os.path.basename(rel)
            kept = [p for p in fps if (os.path.basename(p) != name_only) and (normalize_to_static_user_photos(p) != rel)]
            db.execute('UPDATE cameras SET file_paths=? WHERE user_id=? AND camera_id=?', (','.join(kept), user_id, cam))
            db.commit()
    except sqlite3.Error:
        pass
    try:
        log_admin_action('delete_image', admin_user_id=session.get('user_id'), target_user_id=user_id, ip=get_request_ip(request), detail=f'rel={rel}')
    except Exception:
        pass
    return jsonify({'success': True})


@bp.route('/admin/logs.json')
@admin_required
def admin_logs_json():
    limit = max(1, min(int(request.args.get('limit', 100)), 500))
    db = get_db()
    
    # Get combined logs with proper context
    auth_logs = db.execute('''
        SELECT ts, ip, user_id, username, event, detail, 'auth' as log_type
        FROM auth_log 
        ORDER BY ts DESC LIMIT ?
    ''', (limit,)).fetchall()
    
    admin_logs = db.execute('''
        SELECT aa.ts, aa.ip, aa.admin_user_id, aa.target_user_id, aa.action, aa.detail, 'admin' as log_type,
               au.username as admin_username, tu.username as target_username
        FROM admin_audit aa
        LEFT JOIN users au ON au.id = aa.admin_user_id
        LEFT JOIN users tu ON tu.id = aa.target_user_id
        ORDER BY aa.ts DESC LIMIT ?
    ''', (limit,)).fetchall()
    
    # Combine and sort all logs
    all_logs = []
    
    # Process auth logs
    for log in auth_logs:
        all_logs.append({
            'ts': log['ts'],
            'type': 'AUTH',
            'ip': log['ip'] or 'Unknown',
            'username': log['username'] or f'User#{log["user_id"] or "?"}',
            'action': log['event'],
            'detail': log['detail'] or '',
            'severity': 'error' if 'fail' in log['event'].lower() or 'lock' in log['event'].lower() else 'info'
        })
    
    # Process admin logs
    for log in admin_logs:
        admin_name = log['admin_username'] or f'Admin#{log["admin_user_id"]}'
        target_name = log['target_username'] or (f'User#{log["target_user_id"]}' if log['target_user_id'] else '')
        
        action_desc = log['action']
        if target_name:
            action_desc = f"{log['action']} → {target_name}"
        
        all_logs.append({
            'ts': log['ts'],
            'type': 'ADMIN',
            'ip': log['ip'] or 'Unknown',
            'username': admin_name,
            'action': action_desc,
            'detail': log['detail'] or '',
            'severity': 'warning' if 'delete' in log['action'].lower() or 'remove' in log['action'].lower() else 'info'
        })
    
    # Sort by timestamp descending
    all_logs.sort(key=lambda x: x['ts'], reverse=True)
    
    return jsonify({
        'logs': all_logs[:limit],
        'total_count': len(all_logs)
    })


@bp.route('/admin/logs/export')
@admin_required  
def admin_logs_export():
    format_type = request.args.get('format', 'csv').lower()
    limit = max(1, min(int(request.args.get('limit', 1000)), 5000))
    
    db = get_db()
    
    # Get combined logs with proper context (same as logs.json but more limit)
    auth_logs = db.execute('''
        SELECT ts, ip, user_id, username, event, detail, 'auth' as log_type
        FROM auth_log 
        ORDER BY ts DESC LIMIT ?
    ''', (limit,)).fetchall()
    
    admin_logs = db.execute('''
        SELECT aa.ts, aa.ip, aa.admin_user_id, aa.target_user_id, aa.action, aa.detail, 'admin' as log_type,
               au.username as admin_username, tu.username as target_username
        FROM admin_audit aa
        LEFT JOIN users au ON au.id = aa.admin_user_id
        LEFT JOIN users tu ON tu.id = aa.target_user_id
        ORDER BY aa.ts DESC LIMIT ?
    ''', (limit,)).fetchall()
    
    # Combine and sort all logs
    all_logs = []
    
    # Process auth logs
    for log in auth_logs:
        all_logs.append({
            'timestamp': log['ts'],
            'datetime': format_dt(log['ts']) if log['ts'] else '',
            'type': 'AUTH',
            'ip': log['ip'] or 'Unknown',
            'username': log['username'] or f'User#{log["user_id"] or "?"}',
            'action': log['event'],
            'detail': log['detail'] or '',
            'severity': 'error' if 'fail' in log['event'].lower() or 'lock' in log['event'].lower() else 'info'
        })
    
    # Process admin logs
    for log in admin_logs:
        admin_name = log['admin_username'] or f'Admin#{log["admin_user_id"]}'
        target_name = log['target_username'] or (f'User#{log["target_user_id"]}' if log['target_user_id'] else '')
        
        action_desc = log['action']
        if target_name:
            action_desc = f"{log['action']} -> {target_name}"
        
        all_logs.append({
            'timestamp': log['ts'],
            'datetime': format_dt(log['ts']) if log['ts'] else '',
            'type': 'ADMIN',
            'ip': log['ip'] or 'Unknown',
            'username': admin_name,
            'action': action_desc,
            'detail': log['detail'] or '',
            'severity': 'warning' if 'delete' in log['action'].lower() or 'remove' in log['action'].lower() else 'info'
        })
    
    # Sort by timestamp descending
    all_logs.sort(key=lambda x: x['timestamp'], reverse=True)
    
    if format_type == 'json':
        from flask import Response
        import json
        response_data = {
            'export_timestamp': format_dt(int(time.time())),
            'total_logs': len(all_logs),
            'logs': all_logs
        }
        response = Response(
            json.dumps(response_data, indent=2, ensure_ascii=False),
            mimetype='application/json',
            headers={'Content-Disposition': f'attachment; filename=audit_logs_{int(time.time())}.json'}
        )
        return response
    
    else:  # CSV format
        from flask import Response
        import csv
        from io import StringIO
        
        output = StringIO()
        writer = csv.writer(output)
        
        # Write header
        writer.writerow(['Timestamp', 'DateTime', 'Type', 'IP', 'Username', 'Action', 'Detail', 'Severity'])
        
        # Write data
        for log in all_logs:
            writer.writerow([
                log['timestamp'],
                log['datetime'],
                log['type'],
                log['ip'],
                log['username'],
                log['action'],
                log['detail'],
                log['severity']
            ])
        
        output.seek(0)
        return Response(
            output.getvalue(),
            mimetype='text/csv',
            headers={'Content-Disposition': f'attachment; filename=audit_logs_{int(time.time())}.csv'}
        )


@bp.route('/admin/user/<int:user_id>/stats.json')
@admin_required
def admin_user_stats_json(user_id: int):
    db = get_db()
    u = db.execute('SELECT id, username, IFNULL(is_admin,0) AS is_admin FROM users WHERE id = ?', (user_id,)).fetchone()
    if not u:
        return jsonify({'error': 'not found'}), 404
    # Cameras
    cam_rows = db.execute('SELECT camera_id FROM cameras WHERE user_id = ?', (user_id,)).fetchall()
    cam_ids = {str(r['camera_id']) for r in cam_rows}
    camera_count = len(cam_ids)
    # Images: count, size, earliest
    image_count = 0
    total_bytes = 0
    earliest_ts = None
    root = os.path.join(STATIC_PATH, 'User-photos')
    import re
    cam_any_re = re.compile(r"[A-Za-z0-9]{12}")
    for dirpath, _, files in os.walk(root):
        for name in files:
            nl = name.lower()
            if not (nl.endswith('.jpg') or nl.endswith('.jpeg') or nl.endswith('.png') or nl.endswith('.gif') or nl.endswith('.webp')):
                continue
            ts, cam = parse_ts_from_any(name)
            if not cam:
                # Fallback: try to find any 12-char alnum token in name
                m = cam_any_re.findall(name)
                if m:
                    for token in m[::-1]:
                        if token in cam_ids:
                            cam = token
                            break
            if cam and (cam in cam_ids):
                abs_path = os.path.join(dirpath, name)
                try:
                    st = os.stat(abs_path)
                except OSError:
                    continue
                total_bytes += int(st.st_size)
                image_count += 1
                if ts is None:
                    from datetime import datetime
                    ts = datetime.fromtimestamp(st.st_mtime)
                if earliest_ts is None or ts < earliest_ts:
                    earliest_ts = ts
    # Last login
    last = db.execute('SELECT ts, ip FROM auth_log WHERE event = ? AND (user_id = ? OR username = ?) ORDER BY ts DESC LIMIT 1', ('login_success', user_id, u['username'])).fetchone()
    first_auth = db.execute('SELECT ts FROM auth_log WHERE (user_id = ? OR username = ?) ORDER BY ts ASC LIMIT 1', (user_id, u['username'])).fetchone()
    
    # User creation info from admin audit logs
    creation_info = db.execute('''
        SELECT aa.ts, aa.admin_user_id, au.username as admin_username
        FROM admin_audit aa
        LEFT JOIN users au ON au.id = aa.admin_user_id
        WHERE aa.action = 'add_user' AND aa.detail LIKE ?
        ORDER BY aa.ts ASC LIMIT 1
    ''', (f'username={u["username"]}',)).fetchone()
    
    # Format timestamps as numbers; client will render
    from datetime import datetime
    first_seen_ts = int(first_auth['ts']) if first_auth else (int(earliest_ts.timestamp()) if earliest_ts else 0)
    last_login_ts = int(last['ts']) if last else 0
    last_login_ip = (last['ip'] if last else '') or ''
    
    # Creation details
    created_by_username = None
    created_at_ts = 0
    if creation_info:
        created_by_username = creation_info['admin_username'] or f"Admin #{creation_info['admin_user_id']}"
        created_at_ts = int(creation_info['ts'])
    
    return jsonify({
        'user': {'id': u['id'], 'username': u['username'], 'is_admin': int(u['is_admin'] or 0) == 1},
        'camera_count': camera_count,
        'image_count': image_count,
        'total_bytes': total_bytes,
        'first_seen_ts': first_seen_ts,
        'last_login_ts': last_login_ts,
        'last_login_ip': last_login_ip,
        'created_by_username': created_by_username,
        'created_at_ts': created_at_ts,
    })