1554 lines
47 KiB
JavaScript
1554 lines
47 KiB
JavaScript
/**
|
|
* 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
|
|
};
|