/**
 * SQLite Database Wrapper for BraceFlow DEV
 * Mirrors the MySQL schema but uses SQLite for local development
 */
import Database from 'better-sqlite3';
import path from 'path';
import { fileURLToPath } from 'url';

const __dirname = path.dirname(fileURLToPath(import.meta.url));
// Use DB_PATH from env (Docker) or local path (dev)
const DB_PATH = process.env.DB_PATH || path.join(__dirname, '..', 'braceflow_DEV.db');

console.log(`Database path: ${DB_PATH}`);

// Initialize database
const db = new Database(DB_PATH);
db.pragma('journal_mode = WAL');

// Create tables
db.exec(`
  -- Patients table
  CREATE TABLE IF NOT EXISTS patients (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    mrn TEXT UNIQUE,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    date_of_birth TEXT,
    gender TEXT CHECK(gender IN ('male', 'female', 'other')),
    email TEXT,
    phone TEXT,
    address TEXT,
    diagnosis TEXT,
    curve_type TEXT,
    medical_history TEXT,
    referring_physician TEXT,
    insurance_info TEXT,
    notes TEXT,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_by INTEGER,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
  );

  -- Main cases table (linked to patients)
  CREATE TABLE IF NOT EXISTS brace_cases (
    case_id TEXT PRIMARY KEY,
    patient_id INTEGER,
    case_type TEXT NOT NULL DEFAULT 'braceflow',
    visit_date TEXT DEFAULT (date('now')),
    status TEXT NOT NULL DEFAULT 'created' CHECK(status IN (
      'created', 'running', 'completed', 'failed', 'cancelled',
      'processing_brace', 'brace_generated', 'brace_failed',
      'landmarks_detected', 'landmarks_approved', 'analysis_complete',
      'body_scan_uploaded'
    )),
    current_step TEXT DEFAULT NULL,
    execution_arn TEXT DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    analysis_result TEXT DEFAULT NULL,
    landmarks_data TEXT DEFAULT NULL,
    analysis_data TEXT DEFAULT NULL,
    markers_data TEXT DEFAULT NULL,
    body_scan_path TEXT DEFAULT NULL,
    body_scan_url TEXT DEFAULT NULL,
    body_scan_metadata TEXT DEFAULT NULL,
    is_archived INTEGER NOT NULL DEFAULT 0,
    archived_at TEXT DEFAULT NULL,
    created_by INTEGER DEFAULT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE SET NULL
  );

  -- Case steps table
  CREATE TABLE IF NOT EXISTS brace_case_steps (
    case_id TEXT NOT NULL,
    step_name TEXT NOT NULL,
    step_order INTEGER NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN (
      'pending', 'running', 'done', 'failed', 'waiting_for_landmarks'
    )),
    error_message TEXT DEFAULT NULL,
    started_at TEXT DEFAULT NULL,
    finished_at TEXT DEFAULT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
    PRIMARY KEY (case_id, step_name),
    FOREIGN KEY (case_id) REFERENCES brace_cases(case_id) ON DELETE CASCADE
  );

  -- Task tokens table (for pipeline state)
  CREATE TABLE IF NOT EXISTS brace_case_task_tokens (
    case_id TEXT NOT NULL,
    step_name TEXT NOT NULL,
    task_token TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active', 'consumed', 'expired')),
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
    PRIMARY KEY (case_id, step_name),
    FOREIGN KEY (case_id) REFERENCES brace_cases(case_id) ON DELETE CASCADE
  );

  -- Users table (for authentication)
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    email TEXT DEFAULT NULL,
    full_name TEXT DEFAULT NULL,
    role TEXT NOT NULL DEFAULT 'user' CHECK(role IN ('admin', 'user', 'viewer')),
    is_active INTEGER NOT NULL DEFAULT 1,
    last_login TEXT DEFAULT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now'))
  );

  -- User sessions table (for token management)
  CREATE TABLE IF NOT EXISTS user_sessions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    session_token TEXT NOT NULL UNIQUE,
    expires_at TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
  );

  -- Audit log table (for tracking admin actions)
  CREATE TABLE IF NOT EXISTS audit_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER DEFAULT NULL,
    action TEXT NOT NULL,
    entity_type TEXT NOT NULL,
    entity_id TEXT DEFAULT NULL,
    details TEXT DEFAULT NULL,
    ip_address TEXT DEFAULT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
  );

  -- API request logging table (for tracking all HTTP API calls)
  CREATE TABLE IF NOT EXISTS api_requests (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER DEFAULT NULL,
    username TEXT DEFAULT NULL,
    method TEXT NOT NULL,
    path TEXT NOT NULL,
    route_pattern TEXT DEFAULT NULL,
    query_params TEXT DEFAULT NULL,
    request_params TEXT DEFAULT NULL,
    file_uploads TEXT DEFAULT NULL,
    status_code INTEGER DEFAULT NULL,
    response_time_ms INTEGER DEFAULT NULL,
    response_summary TEXT DEFAULT NULL,
    ip_address TEXT DEFAULT NULL,
    user_agent TEXT DEFAULT NULL,
    request_body_size INTEGER DEFAULT NULL,
    response_body_size INTEGER DEFAULT NULL,
    error_message TEXT DEFAULT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
  );

  -- Create indexes
  CREATE INDEX IF NOT EXISTS idx_patients_name ON patients(last_name, first_name);
  CREATE INDEX IF NOT EXISTS idx_patients_mrn ON patients(mrn);
  CREATE INDEX IF NOT EXISTS idx_patients_created_by ON patients(created_by);
  CREATE INDEX IF NOT EXISTS idx_patients_active ON patients(is_active);
  CREATE INDEX IF NOT EXISTS idx_cases_patient ON brace_cases(patient_id);
  CREATE INDEX IF NOT EXISTS idx_cases_status ON brace_cases(status);
  CREATE INDEX IF NOT EXISTS idx_cases_created ON brace_cases(created_at);
  CREATE INDEX IF NOT EXISTS idx_steps_case_id ON brace_case_steps(case_id);
  CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
  CREATE INDEX IF NOT EXISTS idx_users_role ON users(role);
  CREATE INDEX IF NOT EXISTS idx_sessions_token ON user_sessions(session_token);
  CREATE INDEX IF NOT EXISTS idx_sessions_user ON user_sessions(user_id);
  CREATE INDEX IF NOT EXISTS idx_audit_user ON audit_log(user_id);
  CREATE INDEX IF NOT EXISTS idx_audit_action ON audit_log(action);
  CREATE INDEX IF NOT EXISTS idx_audit_created ON audit_log(created_at);
  CREATE INDEX IF NOT EXISTS idx_api_requests_user ON api_requests(user_id);
  CREATE INDEX IF NOT EXISTS idx_api_requests_path ON api_requests(path);
  CREATE INDEX IF NOT EXISTS idx_api_requests_method ON api_requests(method);
  CREATE INDEX IF NOT EXISTS idx_api_requests_status ON api_requests(status_code);
  CREATE INDEX IF NOT EXISTS idx_api_requests_created ON api_requests(created_at);
`);

// Migration: Add new columns to existing tables
try {
  db.exec(`ALTER TABLE brace_cases ADD COLUMN analysis_data TEXT DEFAULT NULL`);
} catch (e) { /* Column already exists */ }

try {
  db.exec(`ALTER TABLE brace_cases ADD COLUMN body_scan_path TEXT DEFAULT NULL`);
} catch (e) { /* Column already exists */ }

try {
  db.exec(`ALTER TABLE brace_cases ADD COLUMN body_scan_url TEXT DEFAULT NULL`);
} catch (e) { /* Column already exists */ }

try {
  db.exec(`ALTER TABLE brace_cases ADD COLUMN body_scan_metadata TEXT DEFAULT NULL`);
} catch (e) { /* Column already exists */ }

try {
  db.exec(`ALTER TABLE brace_cases ADD COLUMN created_by INTEGER DEFAULT NULL`);
} catch (e) { /* Column already exists */ }

// Migration: Add new columns to api_requests table for enhanced logging
try {
  db.exec(`ALTER TABLE api_requests ADD COLUMN route_pattern TEXT DEFAULT NULL`);
} catch (e) { /* Column already exists */ }

try {
  db.exec(`ALTER TABLE api_requests ADD COLUMN request_params TEXT DEFAULT NULL`);
} catch (e) { /* Column already exists */ }

try {
  db.exec(`ALTER TABLE api_requests ADD COLUMN file_uploads TEXT DEFAULT NULL`);
} catch (e) { /* Column already exists */ }

try {
  db.exec(`ALTER TABLE api_requests ADD COLUMN response_summary TEXT DEFAULT NULL`);
} catch (e) { /* Column already exists */ }

// Migration: Add patient_id to brace_cases
try {
  db.exec(`ALTER TABLE brace_cases ADD COLUMN patient_id INTEGER DEFAULT NULL`);
} catch (e) { /* Column already exists */ }

// Migration: Add visit_date to brace_cases
try {
  db.exec(`ALTER TABLE brace_cases ADD COLUMN visit_date TEXT DEFAULT NULL`);
} catch (e) { /* Column already exists */ }

// Migration: Add is_archived to brace_cases
try {
  db.exec(`ALTER TABLE brace_cases ADD COLUMN is_archived INTEGER NOT NULL DEFAULT 0`);
} catch (e) { /* Column already exists */ }

try {
  db.exec(`ALTER TABLE brace_cases ADD COLUMN archived_at TEXT DEFAULT NULL`);
} catch (e) { /* Column already exists */ }

// Insert default admin user if not exists (password: admin123)
// Note: In production, use proper bcrypt hashing. This is a simple hash for dev.
try {
  const existingAdmin = db.prepare(`SELECT id FROM users WHERE username = ?`).get('admin');
  if (!existingAdmin) {
    // Simple hash for dev - in production use bcrypt
    db.prepare(`
      INSERT INTO users (username, password_hash, full_name, role, is_active)
      VALUES (?, ?, ?, ?, ?)
    `).run('admin', 'admin123', 'Administrator', 'admin', 1);
    console.log('Created default admin user (admin/admin123)');
  }
} catch (e) { /* User already exists or table not ready */ }

// Step names for the pipeline
const STEP_NAMES = [
  'LandmarkDetection',
  'LandmarkApproval',
  'SpineAnalysis',
  'BodyScanUpload',
  'BraceGeneration',
  'BraceApproval'
];

/**
 * Create a new case (optionally linked to a patient)
 */
export function createCase(caseId, caseType = 'braceflow', notes = null, patientId = null, visitDate = null) {
  const insertCase = db.prepare(`
    INSERT INTO brace_cases (case_id, patient_id, case_type, visit_date, status, notes, created_at, updated_at)
    VALUES (?, ?, ?, ?, 'created', ?, datetime('now'), datetime('now'))
  `);

  const insertStep = db.prepare(`
    INSERT INTO brace_case_steps (case_id, step_name, step_order, status, created_at, updated_at)
    VALUES (?, ?, ?, 'pending', datetime('now'), datetime('now'))
  `);

  const transaction = db.transaction(() => {
    insertCase.run(caseId, patientId, caseType, visitDate || new Date().toISOString().split('T')[0], notes);
    STEP_NAMES.forEach((stepName, idx) => {
      insertStep.run(caseId, stepName, idx + 1);
    });
  });

  transaction();
  return { caseId, patientId, status: 'created', steps: STEP_NAMES };
}

/**
 * List all cases with patient info
 * @param {Object} options - Query options
 * @param {boolean} options.includeArchived - Include archived cases (for admin view)
 * @param {boolean} options.archivedOnly - Only show archived cases
 */
export function listCases(options = {}) {
  const { includeArchived = false, archivedOnly = false } = options;
  
  let whereClause = '';
  if (archivedOnly) {
    whereClause = 'WHERE c.is_archived = 1';
  } else if (!includeArchived) {
    whereClause = 'WHERE c.is_archived = 0';
  }
  
  const stmt = db.prepare(`
    SELECT c.case_id as caseId, c.patient_id, c.case_type, c.visit_date, c.status, c.current_step, c.notes, 
           c.analysis_result, c.landmarks_data, c.is_archived, c.archived_at, c.created_at, c.updated_at,
           p.first_name as patient_first_name, p.last_name as patient_last_name,
           p.mrn as patient_mrn
    FROM brace_cases c
    LEFT JOIN patients p ON c.patient_id = p.id
    ${whereClause}
    ORDER BY c.created_at DESC
  `);
  
  const rows = stmt.all();
  
  // Transform to include patient object
  return rows.map(row => ({
    caseId: row.caseId,
    patient_id: row.patient_id,
    patient: row.patient_id ? {
      id: row.patient_id,
      firstName: row.patient_first_name,
      lastName: row.patient_last_name,
      fullName: `${row.patient_first_name} ${row.patient_last_name}`,
      mrn: row.patient_mrn
    } : null,
    case_type: row.case_type,
    visit_date: row.visit_date,
    status: row.status,
    current_step: row.current_step,
    notes: row.notes,
    analysis_result: row.analysis_result,
    landmarks_data: row.landmarks_data,
    is_archived: row.is_archived === 1,
    archived_at: row.archived_at,
    created_at: row.created_at,
    updated_at: row.updated_at
  }));
}

/**
 * Archive a case (soft delete)
 */
export function archiveCase(caseId) {
  const stmt = db.prepare(`
    UPDATE brace_cases 
    SET is_archived = 1, archived_at = datetime('now'), updated_at = datetime('now')
    WHERE case_id = ?
  `);
  return stmt.run(caseId);
}

/**
 * Unarchive a case
 */
export function unarchiveCase(caseId) {
  const stmt = db.prepare(`
    UPDATE brace_cases 
    SET is_archived = 0, archived_at = NULL, updated_at = datetime('now')
    WHERE case_id = ?
  `);
  return stmt.run(caseId);
}

/**
 * Get case by ID with steps and patient info
 */
export function getCase(caseId) {
  const caseStmt = db.prepare(`
    SELECT c.case_id, c.patient_id, c.case_type, c.visit_date, c.status, c.current_step, c.notes, 
           c.analysis_result, c.landmarks_data, c.analysis_data, c.markers_data,
           c.body_scan_path, c.body_scan_url, c.body_scan_metadata,
           c.created_at, c.updated_at,
           p.first_name as patient_first_name, p.last_name as patient_last_name,
           p.mrn as patient_mrn, p.date_of_birth as patient_dob, p.gender as patient_gender
    FROM brace_cases c
    LEFT JOIN patients p ON c.patient_id = p.id
    WHERE c.case_id = ?
  `);
  
  const stepsStmt = db.prepare(`
    SELECT step_name, step_order, status, error_message, started_at, finished_at
    FROM brace_case_steps
    WHERE case_id = ?
    ORDER BY step_order ASC
  `);

  const caseData = caseStmt.get(caseId);
  if (!caseData) return null;

  const steps = stepsStmt.all(caseId);

  // Parse JSON fields
  let analysisResult = null;
  let landmarksData = null;
  let analysisData = null;
  let markersData = null;
  let bodyScanMetadata = null;

  try {
    if (caseData.analysis_result) {
      analysisResult = JSON.parse(caseData.analysis_result);
    }
  } catch (e) { /* ignore */ }

  try {
    if (caseData.landmarks_data) {
      landmarksData = JSON.parse(caseData.landmarks_data);
    }
  } catch (e) { /* ignore */ }

  try {
    if (caseData.analysis_data) {
      analysisData = JSON.parse(caseData.analysis_data);
    }
  } catch (e) { /* ignore */ }

  try {
    if (caseData.markers_data) {
      markersData = JSON.parse(caseData.markers_data);
    }
  } catch (e) { /* ignore */ }

  try {
    if (caseData.body_scan_metadata) {
      bodyScanMetadata = JSON.parse(caseData.body_scan_metadata);
    }
  } catch (e) { /* ignore */ }

  // Build patient object if patient_id exists
  const patient = caseData.patient_id ? {
    id: caseData.patient_id,
    firstName: caseData.patient_first_name,
    lastName: caseData.patient_last_name,
    fullName: `${caseData.patient_first_name} ${caseData.patient_last_name}`,
    mrn: caseData.patient_mrn,
    dateOfBirth: caseData.patient_dob,
    gender: caseData.patient_gender
  } : null;

  return {
    caseId: caseData.case_id,
    patient_id: caseData.patient_id,
    patient,
    case_type: caseData.case_type,
    visit_date: caseData.visit_date,
    status: caseData.status,
    current_step: caseData.current_step,
    notes: caseData.notes,
    analysis_result: analysisResult,
    landmarks_data: landmarksData,
    analysis_data: analysisData,
    markers_data: markersData,
    body_scan_path: caseData.body_scan_path,
    body_scan_url: caseData.body_scan_url,
    body_scan_metadata: bodyScanMetadata,
    created_at: caseData.created_at,
    updated_at: caseData.updated_at,
    steps
  };
}

/**
 * Update case status
 */
export function updateCaseStatus(caseId, status, currentStep = null) {
  const stmt = db.prepare(`
    UPDATE brace_cases 
    SET status = ?, current_step = ?, updated_at = datetime('now')
    WHERE case_id = ?
  `);
  return stmt.run(status, currentStep, caseId);
}

/**
 * Save landmarks data
 */
export function saveLandmarks(caseId, landmarksData) {
  const stmt = db.prepare(`
    UPDATE brace_cases 
    SET landmarks_data = ?, status = 'landmarks_detected', 
        current_step = 'LandmarkApproval', updated_at = datetime('now')
    WHERE case_id = ?
  `);
  return stmt.run(JSON.stringify(landmarksData), caseId);
}

/**
 * Approve landmarks
 */
export function approveLandmarks(caseId, updatedLandmarks = null) {
  const stmt = db.prepare(`
    UPDATE brace_cases 
    SET landmarks_data = COALESCE(?, landmarks_data), 
        status = 'landmarks_approved',
        current_step = 'SpineAnalysis', 
        updated_at = datetime('now')
    WHERE case_id = ?
  `);
  const data = updatedLandmarks ? JSON.stringify(updatedLandmarks) : null;
  return stmt.run(data, caseId);
}

/**
 * Save analysis result
 */
export function saveAnalysisResult(caseId, analysisResult) {
  const stmt = db.prepare(`
    UPDATE brace_cases 
    SET analysis_result = ?, status = 'analysis_complete',
        current_step = 'BraceGeneration', updated_at = datetime('now')
    WHERE case_id = ?
  `);
  return stmt.run(JSON.stringify(analysisResult), caseId);
}

/**
 * Save brace generation result
 */
export function saveBraceResult(caseId, braceResult) {
  const currentData = getCase(caseId);
  const updatedAnalysis = {
    ...(currentData?.analysis_result || {}),
    brace: braceResult
  };
  
  const stmt = db.prepare(`
    UPDATE brace_cases 
    SET analysis_result = ?, status = 'brace_generated',
        current_step = 'BraceApproval', updated_at = datetime('now')
    WHERE case_id = ?
  `);
  return stmt.run(JSON.stringify(updatedAnalysis), caseId);
}

/**
 * Save both braces generation result (regular + vase)
 */
export function saveBothBracesResult(caseId, bracesData) {
  const currentData = getCase(caseId);
  const updatedAnalysis = {
    ...(currentData?.analysis_result || {}),
    braces: bracesData.braces,
    rigoType: bracesData.rigoType,
    cobbAngles: bracesData.cobbAngles,
    bodyScanUsed: bracesData.bodyScanUsed
  };
  
  const stmt = db.prepare(`
    UPDATE brace_cases 
    SET analysis_result = ?, status = 'brace_generated',
        current_step = 'BraceApproval', updated_at = datetime('now')
    WHERE case_id = ?
  `);
  return stmt.run(JSON.stringify(updatedAnalysis), caseId);
}

/**
 * Save markers data (for brace editor)
 */
export function saveMarkers(caseId, markersData) {
  const stmt = db.prepare(`
    UPDATE brace_cases 
    SET markers_data = ?, updated_at = datetime('now')
    WHERE case_id = ?
  `);
  return stmt.run(JSON.stringify(markersData), caseId);
}

/**
 * Save analysis data (from recalculate - separate from brace result)
 */
export function saveAnalysisData(caseId, analysisData) {
  const stmt = db.prepare(`
    UPDATE brace_cases 
    SET analysis_data = ?, updated_at = datetime('now')
    WHERE case_id = ?
  `);
  return stmt.run(JSON.stringify(analysisData), caseId);
}

/**
 * Save body scan info
 * Note: We use 'analysis_complete' status for compatibility with existing databases
 * that may not have 'body_scan_uploaded' in their CHECK constraint
 */
export function saveBodyScan(caseId, scanPath, scanUrl, metadata = null) {
  const stmt = db.prepare(`
    UPDATE brace_cases 
    SET body_scan_path = ?, body_scan_url = ?, body_scan_metadata = ?,
        status = 'analysis_complete', current_step = 'BraceGeneration',
        updated_at = datetime('now')
    WHERE case_id = ?
  `);
  const metadataJson = metadata ? JSON.stringify(metadata) : null;
  return stmt.run(scanPath, scanUrl, metadataJson, caseId);
}

/**
 * Clear body scan (user wants to skip or remove)
 */
export function clearBodyScan(caseId) {
  const stmt = db.prepare(`
    UPDATE brace_cases 
    SET body_scan_path = NULL, body_scan_url = NULL, body_scan_metadata = NULL,
        updated_at = datetime('now')
    WHERE case_id = ?
  `);
  return stmt.run(caseId);
}

/**
 * Delete case
 */
export function deleteCase(caseId) {
  const stmt = db.prepare(`DELETE FROM brace_cases WHERE case_id = ?`);
  return stmt.run(caseId);
}

/**
 * Update step status
 */
export function updateStepStatus(caseId, stepName, status, errorMessage = null) {
  const stmt = db.prepare(`
    UPDATE brace_case_steps 
    SET status = ?, error_message = ?, 
        started_at = CASE WHEN ? = 'running' THEN datetime('now') ELSE started_at END,
        finished_at = CASE WHEN ? IN ('done', 'failed') THEN datetime('now') ELSE finished_at END,
        updated_at = datetime('now')
    WHERE case_id = ? AND step_name = ?
  `);
  return stmt.run(status, errorMessage, status, status, caseId, stepName);
}

// ============================================
// PATIENT MANAGEMENT
// ============================================

/**
 * Create a new patient
 */
export function createPatient(data) {
  const stmt = db.prepare(`
    INSERT INTO patients (
      mrn, first_name, last_name, date_of_birth, gender,
      email, phone, address, diagnosis, curve_type,
      medical_history, referring_physician, insurance_info, notes,
      created_by, created_at, updated_at
    )
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, datetime('now'), datetime('now'))
  `);
  
  const result = stmt.run(
    data.mrn || null,
    data.firstName,
    data.lastName,
    data.dateOfBirth || null,
    data.gender || null,
    data.email || null,
    data.phone || null,
    data.address || null,
    data.diagnosis || null,
    data.curveType || null,
    data.medicalHistory || null,
    data.referringPhysician || null,
    data.insuranceInfo || null,
    data.notes || null,
    data.createdBy || null
  );
  
  return {
    id: result.lastInsertRowid,
    ...data
  };
}

/**
 * Get patient by ID
 */
export function getPatient(patientId) {
  const stmt = db.prepare(`
    SELECT p.*, u.username as created_by_username
    FROM patients p
    LEFT JOIN users u ON p.created_by = u.id
    WHERE p.id = ?
  `);
  return stmt.get(patientId);
}

/**
 * List all patients with optional filters
 */
export function listPatients(options = {}) {
  const { search, isActive = true, createdBy, limit = 50, offset = 0, sortBy = 'created_at', sortOrder = 'DESC' } = options;
  
  let where = [];
  let values = [];
  
  if (isActive !== undefined && isActive !== null) {
    where.push('p.is_active = ?');
    values.push(isActive ? 1 : 0);
  }
  
  if (createdBy) {
    where.push('p.created_by = ?');
    values.push(createdBy);
  }
  
  if (search) {
    where.push('(p.first_name LIKE ? OR p.last_name LIKE ? OR p.mrn LIKE ? OR p.email LIKE ?)');
    const searchPattern = `%${search}%`;
    values.push(searchPattern, searchPattern, searchPattern, searchPattern);
  }
  
  const whereClause = where.length > 0 ? `WHERE ${where.join(' AND ')}` : '';
  const validSortColumns = ['created_at', 'updated_at', 'last_name', 'first_name', 'date_of_birth'];
  const sortColumn = validSortColumns.includes(sortBy) ? sortBy : 'created_at';
  const order = sortOrder.toUpperCase() === 'ASC' ? 'ASC' : 'DESC';
  
  // Get total count
  const countStmt = db.prepare(`SELECT COUNT(*) as count FROM patients p ${whereClause}`);
  const totalCount = countStmt.get(...values).count;
  
  // Get patients with case count
  const stmt = db.prepare(`
    SELECT p.*, 
           u.username as created_by_username,
           (SELECT COUNT(*) FROM brace_cases c WHERE c.patient_id = p.id) as case_count,
           (SELECT MAX(c.created_at) FROM brace_cases c WHERE c.patient_id = p.id) as last_visit
    FROM patients p
    LEFT JOIN users u ON p.created_by = u.id
    ${whereClause}
    ORDER BY p.${sortColumn} ${order}
    LIMIT ? OFFSET ?
  `);
  
  const patients = stmt.all(...values, limit, offset);
  
  return {
    patients,
    total: totalCount,
    limit,
    offset
  };
}

/**
 * Update patient
 */
export function updatePatient(patientId, data) {
  const fields = [];
  const values = [];
  
  if (data.mrn !== undefined) { fields.push('mrn = ?'); values.push(data.mrn); }
  if (data.firstName !== undefined) { fields.push('first_name = ?'); values.push(data.firstName); }
  if (data.lastName !== undefined) { fields.push('last_name = ?'); values.push(data.lastName); }
  if (data.dateOfBirth !== undefined) { fields.push('date_of_birth = ?'); values.push(data.dateOfBirth); }
  if (data.gender !== undefined) { fields.push('gender = ?'); values.push(data.gender); }
  if (data.email !== undefined) { fields.push('email = ?'); values.push(data.email); }
  if (data.phone !== undefined) { fields.push('phone = ?'); values.push(data.phone); }
  if (data.address !== undefined) { fields.push('address = ?'); values.push(data.address); }
  if (data.diagnosis !== undefined) { fields.push('diagnosis = ?'); values.push(data.diagnosis); }
  if (data.curveType !== undefined) { fields.push('curve_type = ?'); values.push(data.curveType); }
  if (data.medicalHistory !== undefined) { fields.push('medical_history = ?'); values.push(data.medicalHistory); }
  if (data.referringPhysician !== undefined) { fields.push('referring_physician = ?'); values.push(data.referringPhysician); }
  if (data.insuranceInfo !== undefined) { fields.push('insurance_info = ?'); values.push(data.insuranceInfo); }
  if (data.notes !== undefined) { fields.push('notes = ?'); values.push(data.notes); }
  if (data.isActive !== undefined) { fields.push('is_active = ?'); values.push(data.isActive ? 1 : 0); }
  
  if (fields.length === 0) return null;
  
  fields.push('updated_at = datetime(\'now\')');
  values.push(patientId);
  
  const stmt = db.prepare(`UPDATE patients SET ${fields.join(', ')} WHERE id = ?`);
  return stmt.run(...values);
}

/**
 * Archive patient (soft delete - set is_active = 0)
 */
export function archivePatient(patientId) {
  const stmt = db.prepare(`
    UPDATE patients 
    SET is_active = 0, updated_at = datetime('now') 
    WHERE id = ?
  `);
  return stmt.run(patientId);
}

/**
 * Unarchive patient (restore - set is_active = 1)
 */
export function unarchivePatient(patientId) {
  const stmt = db.prepare(`
    UPDATE patients 
    SET is_active = 1, updated_at = datetime('now') 
    WHERE id = ?
  `);
  return stmt.run(patientId);
}

/**
 * Delete patient - kept for backwards compatibility, now archives
 */
export function deletePatient(patientId, hard = false) {
  if (hard) {
    // Hard delete should never be used in normal operation
    const stmt = db.prepare(`DELETE FROM patients WHERE id = ?`);
    return stmt.run(patientId);
  } else {
    return archivePatient(patientId);
  }
}

/**
 * Get cases for a patient
 * @param {number} patientId - Patient ID
 * @param {Object} options - Query options
 * @param {boolean} options.includeArchived - Include archived cases
 */
export function getPatientCases(patientId, options = {}) {
  const { includeArchived = false } = options;
  
  const archivedFilter = includeArchived ? '' : 'AND is_archived = 0';
  
  const stmt = db.prepare(`
    SELECT case_id, case_type, status, current_step, visit_date, notes,
           analysis_result, landmarks_data, body_scan_path, body_scan_url,
           is_archived, archived_at, created_at, updated_at
    FROM brace_cases
    WHERE patient_id = ? ${archivedFilter}
    ORDER BY created_at DESC
  `);
  return stmt.all(patientId);
}

/**
 * Get patient statistics
 */
export function getPatientStats() {
  const total = db.prepare(`SELECT COUNT(*) as count FROM patients`).get();
  const active = db.prepare(`SELECT COUNT(*) as count FROM patients WHERE is_active = 1`).get();
  const withCases = db.prepare(`
    SELECT COUNT(DISTINCT patient_id) as count 
    FROM brace_cases 
    WHERE patient_id IS NOT NULL
  `).get();
  
  const byGender = db.prepare(`
    SELECT gender, COUNT(*) as count 
    FROM patients 
    WHERE is_active = 1 
    GROUP BY gender
  `).all();
  
  const recentPatients = db.prepare(`
    SELECT COUNT(*) as count 
    FROM patients 
    WHERE created_at >= datetime('now', '-30 days')
  `).get();
  
  return {
    total: total.count,
    active: active.count,
    inactive: total.count - active.count,
    withCases: withCases.count,
    byGender: byGender.reduce((acc, row) => { acc[row.gender || 'unspecified'] = row.count; return acc; }, {}),
    recentPatients: recentPatients.count
  };
}

// ============================================
// USER MANAGEMENT
// ============================================

/**
 * Get user by username (for login)
 */
export function getUserByUsername(username) {
  const stmt = db.prepare(`
    SELECT id, username, password_hash, email, full_name, role, is_active, last_login, created_at
    FROM users WHERE username = ?
  `);
  return stmt.get(username);
}

/**
 * Get user by ID
 */
export function getUserById(userId) {
  const stmt = db.prepare(`
    SELECT id, username, email, full_name, role, is_active, last_login, created_at, updated_at
    FROM users WHERE id = ?
  `);
  return stmt.get(userId);
}

/**
 * List all users
 */
export function listUsers() {
  const stmt = db.prepare(`
    SELECT id, username, email, full_name, role, is_active, last_login, created_at, updated_at
    FROM users ORDER BY created_at DESC
  `);
  return stmt.all();
}

/**
 * Create user
 */
export function createUser(username, passwordHash, email = null, fullName = null, role = 'user') {
  const stmt = db.prepare(`
    INSERT INTO users (username, password_hash, email, full_name, role, is_active, created_at, updated_at)
    VALUES (?, ?, ?, ?, ?, 1, datetime('now'), datetime('now'))
  `);
  const result = stmt.run(username, passwordHash, email, fullName, role);
  return { id: result.lastInsertRowid, username, email, fullName, role };
}

/**
 * Update user
 */
export function updateUser(userId, updates) {
  const fields = [];
  const values = [];
  
  if (updates.email !== undefined) { fields.push('email = ?'); values.push(updates.email); }
  if (updates.fullName !== undefined) { fields.push('full_name = ?'); values.push(updates.fullName); }
  if (updates.role !== undefined) { fields.push('role = ?'); values.push(updates.role); }
  if (updates.isActive !== undefined) { fields.push('is_active = ?'); values.push(updates.isActive ? 1 : 0); }
  if (updates.passwordHash !== undefined) { fields.push('password_hash = ?'); values.push(updates.passwordHash); }
  
  if (fields.length === 0) return null;
  
  fields.push('updated_at = datetime(\'now\')');
  values.push(userId);
  
  const stmt = db.prepare(`UPDATE users SET ${fields.join(', ')} WHERE id = ?`);
  return stmt.run(...values);
}

/**
 * Update last login
 */
export function updateLastLogin(userId) {
  const stmt = db.prepare(`UPDATE users SET last_login = datetime('now'), updated_at = datetime('now') WHERE id = ?`);
  return stmt.run(userId);
}

/**
 * Delete user
 */
export function deleteUser(userId) {
  const stmt = db.prepare(`DELETE FROM users WHERE id = ?`);
  return stmt.run(userId);
}

// ============================================
// SESSION MANAGEMENT
// ============================================

/**
 * Create session
 */
export function createSession(userId, token, expiresAt) {
  const stmt = db.prepare(`
    INSERT INTO user_sessions (user_id, session_token, expires_at, created_at)
    VALUES (?, ?, ?, datetime('now'))
  `);
  return stmt.run(userId, token, expiresAt);
}

/**
 * Get session by token
 */
export function getSessionByToken(token) {
  const stmt = db.prepare(`
    SELECT s.*, u.username, u.role, u.full_name, u.is_active
    FROM user_sessions s
    JOIN users u ON s.user_id = u.id
    WHERE s.session_token = ? AND s.expires_at > datetime('now')
  `);
  return stmt.get(token);
}

/**
 * Delete session
 */
export function deleteSession(token) {
  const stmt = db.prepare(`DELETE FROM user_sessions WHERE session_token = ?`);
  return stmt.run(token);
}

/**
 * Delete expired sessions
 */
export function cleanupExpiredSessions() {
  const stmt = db.prepare(`DELETE FROM user_sessions WHERE expires_at < datetime('now')`);
  return stmt.run();
}

// ============================================
// AUDIT LOGGING
// ============================================

/**
 * Log an action
 */
export function logAudit(userId, action, entityType, entityId = null, details = null, ipAddress = null) {
  const stmt = db.prepare(`
    INSERT INTO audit_log (user_id, action, entity_type, entity_id, details, ip_address, created_at)
    VALUES (?, ?, ?, ?, ?, ?, datetime('now'))
  `);
  return stmt.run(userId, action, entityType, entityId, details ? JSON.stringify(details) : null, ipAddress);
}

/**
 * Get audit log entries
 */
export function getAuditLog(options = {}) {
  const { userId, action, entityType, limit = 100, offset = 0 } = options;
  
  let where = [];
  let values = [];
  
  if (userId) { where.push('a.user_id = ?'); values.push(userId); }
  if (action) { where.push('a.action = ?'); values.push(action); }
  if (entityType) { where.push('a.entity_type = ?'); values.push(entityType); }
  
  const whereClause = where.length > 0 ? `WHERE ${where.join(' AND ')}` : '';
  
  const stmt = db.prepare(`
    SELECT a.*, u.username
    FROM audit_log a
    LEFT JOIN users u ON a.user_id = u.id
    ${whereClause}
    ORDER BY a.created_at DESC
    LIMIT ? OFFSET ?
  `);
  
  return stmt.all(...values, limit, offset);
}

// ============================================
// API REQUEST LOGGING
// ============================================

/**
 * Log an API request with full details
 */
export function logApiRequest(data) {
  const stmt = db.prepare(`
    INSERT INTO api_requests (
      user_id, username, method, path, route_pattern, query_params, 
      request_params, file_uploads, status_code, response_time_ms, 
      response_summary, ip_address, user_agent, request_body_size, 
      response_body_size, error_message, created_at
    )
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, datetime('now'))
  `);
  
  return stmt.run(
    data.userId || null,
    data.username || null,
    data.method,
    data.path,
    data.routePattern || null,
    data.queryParams ? JSON.stringify(data.queryParams) : null,
    data.requestParams ? JSON.stringify(data.requestParams) : null,
    data.fileUploads ? JSON.stringify(data.fileUploads) : null,
    data.statusCode || null,
    data.responseTimeMs || null,
    data.responseSummary ? JSON.stringify(data.responseSummary) : null,
    data.ipAddress || null,
    data.userAgent || null,
    data.requestBodySize || null,
    data.responseBodySize || null,
    data.errorMessage || null
  );
}

/**
 * Get API request logs with filters
 */
export function getApiRequests(options = {}) {
  const { 
    userId, 
    username,
    method, 
    path, 
    statusCode,
    minStatusCode,
    maxStatusCode,
    startDate,
    endDate,
    limit = 100, 
    offset = 0 
  } = options;
  
  let where = [];
  let values = [];
  
  if (userId) { where.push('user_id = ?'); values.push(userId); }
  if (username) { where.push('username LIKE ?'); values.push(`%${username}%`); }
  if (method) { where.push('method = ?'); values.push(method); }
  if (path) { where.push('path LIKE ?'); values.push(`%${path}%`); }
  if (statusCode) { where.push('status_code = ?'); values.push(statusCode); }
  if (minStatusCode) { where.push('status_code >= ?'); values.push(minStatusCode); }
  if (maxStatusCode) { where.push('status_code < ?'); values.push(maxStatusCode); }
  if (startDate) { where.push('created_at >= ?'); values.push(startDate); }
  if (endDate) { where.push('created_at <= ?'); values.push(endDate); }
  
  const whereClause = where.length > 0 ? `WHERE ${where.join(' AND ')}` : '';
  
  // Get total count
  const countStmt = db.prepare(`SELECT COUNT(*) as count FROM api_requests ${whereClause}`);
  const totalCount = countStmt.get(...values).count;
  
  // Get paginated results
  const stmt = db.prepare(`
    SELECT *
    FROM api_requests
    ${whereClause}
    ORDER BY created_at DESC
    LIMIT ? OFFSET ?
  `);
  
  const requests = stmt.all(...values, limit, offset);
  
  return {
    requests,
    total: totalCount,
    limit,
    offset
  };
}

/**
 * Get API request statistics
 */
export function getApiRequestStats(options = {}) {
  const { startDate, endDate } = options;
  
  let where = [];
  let values = [];
  
  if (startDate) { where.push('created_at >= ?'); values.push(startDate); }
  if (endDate) { where.push('created_at <= ?'); values.push(endDate); }
  
  const whereClause = where.length > 0 ? `WHERE ${where.join(' AND ')}` : '';
  
  // Total requests
  const total = db.prepare(`SELECT COUNT(*) as count FROM api_requests ${whereClause}`).get(...values);
  
  // By method
  const byMethod = db.prepare(`
    SELECT method, COUNT(*) as count 
    FROM api_requests ${whereClause}
    GROUP BY method ORDER BY count DESC
  `).all(...values);
  
  // By status code category
  const byStatusCategory = db.prepare(`
    SELECT 
      CASE 
        WHEN status_code >= 200 AND status_code < 300 THEN '2xx Success'
        WHEN status_code >= 300 AND status_code < 400 THEN '3xx Redirect'
        WHEN status_code >= 400 AND status_code < 500 THEN '4xx Client Error'
        WHEN status_code >= 500 THEN '5xx Server Error'
        ELSE 'Unknown'
      END as category,
      COUNT(*) as count
    FROM api_requests ${whereClause}
    GROUP BY category ORDER BY count DESC
  `).all(...values);
  
  // Top endpoints
  const topEndpoints = db.prepare(`
    SELECT method, path, COUNT(*) as count,
           AVG(response_time_ms) as avg_response_time
    FROM api_requests ${whereClause}
    GROUP BY method, path 
    ORDER BY count DESC 
    LIMIT 20
  `).all(...values);
  
  // Top users
  const topUsers = db.prepare(`
    SELECT user_id, username, COUNT(*) as count
    FROM api_requests 
    ${whereClause ? whereClause + ' AND username IS NOT NULL' : 'WHERE username IS NOT NULL'}
    GROUP BY user_id, username 
    ORDER BY count DESC 
    LIMIT 10
  `).all(...values);
  
  // Average response time
  const avgResponseTime = db.prepare(`
    SELECT AVG(response_time_ms) as avg, 
           MIN(response_time_ms) as min, 
           MAX(response_time_ms) as max
    FROM api_requests 
    ${whereClause ? whereClause + ' AND response_time_ms IS NOT NULL' : 'WHERE response_time_ms IS NOT NULL'}
  `).get(...values);
  
  // Requests per hour (last 24 hours)
  const requestsPerHour = db.prepare(`
    SELECT strftime('%Y-%m-%d %H:00', created_at) as hour, COUNT(*) as count
    FROM api_requests
    WHERE created_at >= datetime('now', '-24 hours')
    GROUP BY hour
    ORDER BY hour ASC
  `).all();
  
  // Error rate
  const errors = db.prepare(`
    SELECT COUNT(*) as count FROM api_requests 
    ${whereClause ? whereClause + ' AND status_code >= 400' : 'WHERE status_code >= 400'}
  `).get(...values);
  
  return {
    total: total.count,
    byMethod: byMethod.reduce((acc, row) => { acc[row.method] = row.count; return acc; }, {}),
    byStatusCategory: byStatusCategory.reduce((acc, row) => { acc[row.category] = row.count; return acc; }, {}),
    topEndpoints,
    topUsers,
    responseTime: {
      avg: Math.round(avgResponseTime?.avg || 0),
      min: avgResponseTime?.min || 0,
      max: avgResponseTime?.max || 0
    },
    requestsPerHour,
    errorRate: total.count > 0 ? Math.round((errors.count / total.count) * 100 * 10) / 10 : 0
  };
}

/**
 * Cleanup old API request logs (older than N days)
 */
export function cleanupOldApiRequests(daysToKeep = 30) {
  const stmt = db.prepare(`
    DELETE FROM api_requests 
    WHERE created_at < datetime('now', '-' || ? || ' days')
  `);
  return stmt.run(daysToKeep);
}

// ============================================
// ANALYTICS QUERIES
// ============================================

/**
 * Get case statistics
 */
export function getCaseStats() {
  const totalCases = db.prepare(`SELECT COUNT(*) as count FROM brace_cases`).get();
  const byStatus = db.prepare(`
    SELECT status, COUNT(*) as count 
    FROM brace_cases 
    GROUP BY status
  `).all();
  
  const last7Days = db.prepare(`
    SELECT DATE(created_at) as date, COUNT(*) as count
    FROM brace_cases
    WHERE created_at >= datetime('now', '-7 days')
    GROUP BY DATE(created_at)
    ORDER BY date ASC
  `).all();
  
  const last30Days = db.prepare(`
    SELECT DATE(created_at) as date, COUNT(*) as count
    FROM brace_cases
    WHERE created_at >= datetime('now', '-30 days')
    GROUP BY DATE(created_at)
    ORDER BY date ASC
  `).all();
  
  return {
    total: totalCases.count,
    byStatus: byStatus.reduce((acc, row) => { acc[row.status] = row.count; return acc; }, {}),
    last7Days,
    last30Days
  };
}

/**
 * Get Rigo classification distribution (from analysis_result JSON)
 */
export function getRigoDistribution() {
  const cases = db.prepare(`
    SELECT analysis_result FROM brace_cases 
    WHERE analysis_result IS NOT NULL AND status = 'brace_generated'
  `).all();
  
  const distribution = {};
  for (const c of cases) {
    try {
      const result = JSON.parse(c.analysis_result);
      const rigoType = result.rigoType || result.rigo_classification?.type || result.brace?.rigo_classification?.type;
      if (rigoType) {
        distribution[rigoType] = (distribution[rigoType] || 0) + 1;
      }
    } catch (e) { /* skip invalid JSON */ }
  }
  
  return distribution;
}

/**
 * Get Cobb angle statistics
 */
export function getCobbAngleStats() {
  const cases = db.prepare(`
    SELECT analysis_result, landmarks_data FROM brace_cases 
    WHERE (analysis_result IS NOT NULL OR landmarks_data IS NOT NULL)
  `).all();
  
  const angles = { PT: [], MT: [], TL: [] };
  
  for (const c of cases) {
    try {
      let cobb = null;
      
      // Try analysis_result first
      if (c.analysis_result) {
        const result = JSON.parse(c.analysis_result);
        cobb = result.cobbAngles || result.cobb_angles || result.brace?.cobb_angles;
      }
      
      // Fall back to landmarks_data
      if (!cobb && c.landmarks_data) {
        const landmarks = JSON.parse(c.landmarks_data);
        cobb = landmarks.cobb_angles;
      }
      
      if (cobb) {
        if (cobb.PT !== undefined) angles.PT.push(cobb.PT);
        if (cobb.MT !== undefined) angles.MT.push(cobb.MT);
        if (cobb.TL !== undefined) angles.TL.push(cobb.TL);
      }
    } catch (e) { /* skip invalid JSON */ }
  }
  
  const calcStats = (arr) => {
    if (arr.length === 0) return { min: 0, max: 0, avg: 0, count: 0 };
    const sum = arr.reduce((a, b) => a + b, 0);
    return {
      min: Math.min(...arr),
      max: Math.max(...arr),
      avg: Math.round((sum / arr.length) * 10) / 10,
      count: arr.length
    };
  };
  
  return {
    PT: calcStats(angles.PT),
    MT: calcStats(angles.MT),
    TL: calcStats(angles.TL),
    totalCasesWithAngles: Math.max(angles.PT.length, angles.MT.length, angles.TL.length)
  };
}

/**
 * Get processing time statistics
 */
export function getProcessingTimeStats() {
  const cases = db.prepare(`
    SELECT analysis_result FROM brace_cases 
    WHERE analysis_result IS NOT NULL AND status = 'brace_generated'
  `).all();
  
  const times = [];
  
  for (const c of cases) {
    try {
      const result = JSON.parse(c.analysis_result);
      const time = result.processing_time_ms || result.brace?.processing_time_ms;
      if (time) times.push(time);
    } catch (e) { /* skip invalid JSON */ }
  }
  
  if (times.length === 0) {
    return { min: 0, max: 0, avg: 0, count: 0 };
  }
  
  const sum = times.reduce((a, b) => a + b, 0);
  return {
    min: Math.min(...times),
    max: Math.max(...times),
    avg: Math.round(sum / times.length),
    count: times.length
  };
}

/**
 * Get body scan usage stats
 */
export function getBodyScanStats() {
  const total = db.prepare(`SELECT COUNT(*) as count FROM brace_cases WHERE status = 'brace_generated'`).get();
  const withScan = db.prepare(`SELECT COUNT(*) as count FROM brace_cases WHERE status = 'brace_generated' AND body_scan_path IS NOT NULL`).get();
  
  return {
    total: total.count,
    withBodyScan: withScan.count,
    withoutBodyScan: total.count - withScan.count,
    percentage: total.count > 0 ? Math.round((withScan.count / total.count) * 100) : 0
  };
}

/**
 * Get user statistics
 */
export function getUserStats() {
  const total = db.prepare(`SELECT COUNT(*) as count FROM users`).get();
  const byRole = db.prepare(`SELECT role, COUNT(*) as count FROM users GROUP BY role`).all();
  const active = db.prepare(`SELECT COUNT(*) as count FROM users WHERE is_active = 1`).get();
  const recentLogins = db.prepare(`
    SELECT COUNT(*) as count FROM users 
    WHERE last_login >= datetime('now', '-7 days')
  `).get();
  
  return {
    total: total.count,
    active: active.count,
    inactive: total.count - active.count,
    byRole: byRole.reduce((acc, row) => { acc[row.role] = row.count; return acc; }, {}),
    recentLogins: recentLogins.count
  };
}

/**
 * List cases with filters (for admin)
 */
export function listCasesFiltered(options = {}) {
  const { status, createdBy, search, limit = 50, offset = 0, sortBy = 'created_at', sortOrder = 'DESC', includeArchived = false, archivedOnly = false } = options;
  
  let where = [];
  let values = [];
  
  if (status) { where.push('c.status = ?'); values.push(status); }
  if (createdBy) { where.push('c.created_by = ?'); values.push(createdBy); }
  if (search) { where.push('c.case_id LIKE ?'); values.push(`%${search}%`); }
  
  // Archive filtering
  if (archivedOnly) {
    where.push('c.is_archived = 1');
  } else if (!includeArchived) {
    where.push('c.is_archived = 0');
  }
  
  const whereClause = where.length > 0 ? `WHERE ${where.join(' AND ')}` : '';
  const validSortColumns = ['created_at', 'updated_at', 'status', 'case_id'];
  const sortColumn = validSortColumns.includes(sortBy) ? sortBy : 'created_at';
  const order = sortOrder.toUpperCase() === 'ASC' ? 'ASC' : 'DESC';
  
  const countStmt = db.prepare(`SELECT COUNT(*) as count FROM brace_cases c ${whereClause}`);
  const totalCount = countStmt.get(...values).count;
  
  const stmt = db.prepare(`
    SELECT c.case_id as caseId, c.case_type, c.status, c.current_step, c.notes,
           c.analysis_result, c.landmarks_data, c.body_scan_path,
           c.is_archived, c.archived_at,
           c.created_by, c.created_at, c.updated_at,
           u.username as created_by_username
    FROM brace_cases c
    LEFT JOIN users u ON c.created_by = u.id
    ${whereClause}
    ORDER BY c.${sortColumn} ${order}
    LIMIT ? OFFSET ?
  `);
  
  const cases = stmt.all(...values, limit, offset).map(row => ({
    ...row,
    is_archived: row.is_archived === 1
  }));
  
  return {
    cases,
    total: totalCount,
    limit,
    offset
  };
}

export default {
  // Case management
  createCase,
  listCases,
  listCasesFiltered,
  getCase,
  updateCaseStatus,
  saveLandmarks,
  approveLandmarks,
  saveAnalysisResult,
  saveAnalysisData,
  saveBraceResult,
  saveBothBracesResult,
  saveMarkers,
  saveBodyScan,
  clearBodyScan,
  deleteCase,
  archiveCase,
  unarchiveCase,
  updateStepStatus,
  STEP_NAMES,
  // Patient management
  createPatient,
  getPatient,
  listPatients,
  updatePatient,
  deletePatient,
  archivePatient,
  unarchivePatient,
  getPatientCases,
  getPatientStats,
  // User management
  getUserByUsername,
  getUserById,
  listUsers,
  createUser,
  updateUser,
  updateLastLogin,
  deleteUser,
  // Session management
  createSession,
  getSessionByToken,
  deleteSession,
  cleanupExpiredSessions,
  // Audit logging
  logAudit,
  getAuditLog,
  // API request logging
  logApiRequest,
  getApiRequests,
  getApiRequestStats,
  cleanupOldApiRequests,
  // Analytics
  getCaseStats,
  getRigoDistribution,
  getCobbAngleStats,
  getProcessingTimeStats,
  getBodyScanStats,
  getUserStats
};