257 lines
11 KiB
JavaScript
257 lines
11 KiB
JavaScript
/**
|
|
* db.js — PostgreSQL connection pool + schema initialisation for Curio HMS.
|
|
* Tables: tenants, users, pending_registrations
|
|
*/
|
|
|
|
const { Pool } = require('pg');
|
|
const bcrypt = require('bcryptjs');
|
|
|
|
const pool = new Pool({
|
|
connectionString: process.env.DATABASE_URL || 'postgres://postgres:curio_secret@curio-db:5432/curio',
|
|
ssl: false,
|
|
});
|
|
|
|
// ── Schema ──────────────────────────────────────────────────────────────────
|
|
|
|
const SCHEMA_SQL = `
|
|
CREATE TABLE IF NOT EXISTS tenants (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
logo TEXT DEFAULT 'C',
|
|
primary_color TEXT DEFAULT '#0F172A',
|
|
secondary_color TEXT DEFAULT '#38BDF8',
|
|
consultation_fee INTEGER DEFAULT 500,
|
|
currency TEXT DEFAULT '₹',
|
|
plan TEXT DEFAULT 'basic',
|
|
status TEXT DEFAULT 'active',
|
|
whatsapp_number TEXT,
|
|
specialty TEXT,
|
|
city TEXT,
|
|
working_hours JSONB DEFAULT '[{"start":"09:00","end":"13:00"},{"start":"17:00","end":"22:00"}]',
|
|
days_off JSONB DEFAULT '[0]',
|
|
holidays JSONB DEFAULT '[]',
|
|
services_catalog JSONB DEFAULT '[]',
|
|
created_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id SERIAL PRIMARY KEY,
|
|
tenant_id TEXT REFERENCES tenants(id) ON DELETE CASCADE,
|
|
email TEXT UNIQUE NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
role TEXT NOT NULL CHECK (role IN ('SUPER_ADMIN','OWNER','DOCTOR','RECEPTIONIST','PHARMACIST','LAB_TECH','PATIENT')),
|
|
profile_media JSONB DEFAULT '{}',
|
|
q_and_a JSONB DEFAULT '[]',
|
|
blocked_dates JSONB DEFAULT '[]',
|
|
status TEXT DEFAULT 'active',
|
|
created_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS pending_registrations (
|
|
id SERIAL PRIMARY KEY,
|
|
owner_name TEXT NOT NULL,
|
|
clinic_name TEXT NOT NULL,
|
|
specialty TEXT,
|
|
city TEXT,
|
|
whatsapp_number TEXT NOT NULL,
|
|
email TEXT NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
plan TEXT DEFAULT 'basic',
|
|
status TEXT DEFAULT 'pending',
|
|
created_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS transactions (
|
|
id SERIAL PRIMARY KEY,
|
|
order_id TEXT NOT NULL UNIQUE,
|
|
payment_id TEXT,
|
|
amount INTEGER NOT NULL,
|
|
currency TEXT DEFAULT 'INR',
|
|
status TEXT DEFAULT 'created',
|
|
tenant_id TEXT REFERENCES tenants(id),
|
|
user_id INTEGER REFERENCES users(id),
|
|
created_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS agent_configs (
|
|
id SERIAL PRIMARY KEY,
|
|
tenant_id TEXT REFERENCES tenants(id) ON DELETE CASCADE,
|
|
agent_type TEXT NOT NULL CHECK (agent_type IN ('BILLING', 'TRIAGE', 'FOLLOWUP', 'INVENTORY', 'CDSS', 'RX_VISION', 'DOC_INTELLIGENCE')),
|
|
is_active BOOLEAN DEFAULT false,
|
|
config JSONB DEFAULT '{}',
|
|
created_at TIMESTAMPTZ DEFAULT now(),
|
|
UNIQUE(tenant_id, agent_type)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS prescriptions (
|
|
id TEXT PRIMARY KEY,
|
|
tenant_id TEXT REFERENCES tenants(id) ON DELETE CASCADE,
|
|
doctor_id INTEGER REFERENCES users(id),
|
|
patient_id TEXT NOT NULL,
|
|
patient_name TEXT NOT NULL,
|
|
diagnosis TEXT,
|
|
medicines JSONB DEFAULT '[]',
|
|
lab_tests JSONB DEFAULT '[]',
|
|
status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'ready', 'dispensed')),
|
|
created_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS patients (
|
|
id SERIAL PRIMARY KEY,
|
|
tenant_id TEXT REFERENCES tenants(id) ON DELETE CASCADE,
|
|
display_id TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
whatsapp_number TEXT NOT NULL,
|
|
age INTEGER,
|
|
gender TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT now(),
|
|
UNIQUE(tenant_id, whatsapp_number)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS visits (
|
|
id SERIAL PRIMARY KEY,
|
|
tenant_id TEXT REFERENCES tenants(id) ON DELETE CASCADE,
|
|
patient_id INTEGER REFERENCES patients(id) ON DELETE CASCADE,
|
|
token_number TEXT NOT NULL,
|
|
status TEXT DEFAULT 'waiting' CHECK (status IN ('waiting', 'in-room', 'completed', 'cancelled')),
|
|
type TEXT DEFAULT 'walk-in' CHECK (type IN ('walk-in', 'online')),
|
|
triage_category TEXT DEFAULT 'Routine',
|
|
payment_status TEXT DEFAULT 'unpaid' CHECK (payment_status IN ('unpaid', 'paid')),
|
|
fee INTEGER DEFAULT 0,
|
|
notes TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_users_tenant ON users(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
|
|
CREATE INDEX IF NOT EXISTS idx_pending_status ON pending_registrations(status);
|
|
CREATE INDEX IF NOT EXISTS idx_transactions_order ON transactions(order_id);
|
|
CREATE INDEX IF NOT EXISTS idx_agent_configs_tenant ON agent_configs(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_prescriptions_tenant_status ON prescriptions(tenant_id, status);
|
|
|
|
|
|
-- Migrations for existing DBs
|
|
ALTER TABLE tenants ADD COLUMN IF NOT EXISTS services_catalog JSONB DEFAULT '[]';
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS profile_media JSONB DEFAULT '{}';
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS q_and_a JSONB DEFAULT '[]';
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS blocked_dates JSONB DEFAULT '[]';
|
|
ALTER TABLE users DROP CONSTRAINT IF EXISTS users_role_check;
|
|
ALTER TABLE users ADD CONSTRAINT users_role_check CHECK (role IN ('SUPER_ADMIN','OWNER','DOCTOR','RECEPTIONIST','PHARMACIST','LAB_TECH','PATIENT'));
|
|
ALTER TABLE agent_configs DROP CONSTRAINT IF EXISTS agent_configs_agent_type_check;
|
|
ALTER TABLE agent_configs ADD CONSTRAINT agent_configs_agent_type_check CHECK (agent_type IN ('BILLING', 'TRIAGE', 'FOLLOWUP', 'INVENTORY', 'CDSS', 'RX_VISION', 'DOC_INTELLIGENCE'));
|
|
`;
|
|
|
|
// ── Seed super admin ─────────────────────────────────────────────────────────
|
|
|
|
async function seedSuperAdmin() {
|
|
const email = process.env.SUPER_ADMIN_EMAIL || 'admin@curio.app';
|
|
const password = process.env.SUPER_ADMIN_PASSWORD || 'superadmin';
|
|
|
|
const existing = await pool.query('SELECT id FROM users WHERE email = $1', [email]);
|
|
if (existing.rows.length > 0) return;
|
|
|
|
const hash = await bcrypt.hash(password, 10);
|
|
await pool.query(
|
|
`INSERT INTO users (tenant_id, email, password_hash, name, role)
|
|
VALUES (NULL, $1, $2, 'Super Admin', 'SUPER_ADMIN')`,
|
|
[email, hash]
|
|
);
|
|
console.log(`[DB] Super admin seeded: ${email}`);
|
|
}
|
|
|
|
async function seedDemoUsers() {
|
|
const defaultPassword = 'password';
|
|
const hash = await bcrypt.hash(defaultPassword, 10);
|
|
const demoUsers = [
|
|
{ email: 'doctor@curio.app', name: 'Dr. Sharma', role: 'DOCTOR', tenant_id: 'default' },
|
|
{ email: 'reception@curio.app', name: 'Front Desk', role: 'RECEPTIONIST', tenant_id: 'default' },
|
|
{ email: 'pharmacy@curio.app', name: 'Pharmacist', role: 'PHARMACIST', tenant_id: 'default' },
|
|
];
|
|
|
|
for (const u of demoUsers) {
|
|
const existing = await pool.query('SELECT id FROM users WHERE email = $1', [u.email]);
|
|
if (existing.rows.length === 0) {
|
|
await pool.query(
|
|
`INSERT INTO users (tenant_id, email, password_hash, name, role)
|
|
VALUES ($1, $2, $3, $4, $5)`,
|
|
[u.tenant_id, u.email, hash, u.name, u.role]
|
|
);
|
|
console.log(`[DB] Demo user seeded: ${u.email}`);
|
|
}
|
|
}
|
|
}
|
|
|
|
// ── Seed legacy in-memory tenants into DB ─────────────────────────────────────
|
|
|
|
async function seedLegacyTenants() {
|
|
const legacy = [
|
|
{
|
|
id: 'default',
|
|
name: 'Curio Clinic',
|
|
logo: 'C',
|
|
primary_color: '#0F172A',
|
|
secondary_color: '#38BDF8',
|
|
consultation_fee: 500,
|
|
plan: 'basic',
|
|
working_hours: JSON.stringify([{ start: '09:00', end: '13:00' }, { start: '17:00', end: '22:00' }]),
|
|
days_off: JSON.stringify([0]),
|
|
},
|
|
{
|
|
id: 'sharma-clinic',
|
|
name: "Dr. Sharma's Cardiology",
|
|
logo: 'S',
|
|
primary_color: '#1E3A8A',
|
|
secondary_color: '#60A5FA',
|
|
consultation_fee: 800,
|
|
plan: 'pro',
|
|
working_hours: JSON.stringify([{ start: '10:00', end: '14:00' }, { start: '18:00', end: '21:00' }]),
|
|
days_off: JSON.stringify([0, 6]),
|
|
},
|
|
{
|
|
id: 'apollo-hospitals',
|
|
name: 'Apollo Multispecialty',
|
|
logo: 'A',
|
|
primary_color: '#065F46',
|
|
secondary_color: '#34D399',
|
|
consultation_fee: 1200,
|
|
plan: 'enterprise',
|
|
working_hours: JSON.stringify([{ start: '09:00', end: '22:00' }]),
|
|
days_off: JSON.stringify([]),
|
|
},
|
|
];
|
|
|
|
for (const t of legacy) {
|
|
await pool.query(
|
|
`INSERT INTO tenants (id, name, logo, primary_color, secondary_color, consultation_fee, plan, working_hours, days_off)
|
|
VALUES ($1,$2,$3,$4,$5,$6,$7,$8::jsonb,$9::jsonb)
|
|
ON CONFLICT (id) DO NOTHING`,
|
|
[t.id, t.name, t.logo, t.primary_color, t.secondary_color, t.consultation_fee, t.plan, t.working_hours, t.days_off]
|
|
);
|
|
}
|
|
console.log('[DB] Legacy tenants seeded.');
|
|
}
|
|
|
|
// ── Init ──────────────────────────────────────────────────────────────────────
|
|
|
|
async function initSchema() {
|
|
try {
|
|
await pool.query(SCHEMA_SQL);
|
|
console.log('[DB] Schema ready.');
|
|
await seedSuperAdmin();
|
|
await seedLegacyTenants();
|
|
await seedDemoUsers();
|
|
} catch (err) {
|
|
console.error('[DB] Schema init failed:', err.message);
|
|
// Don't crash — let server start even if DB is temporarily unavailable
|
|
}
|
|
}
|
|
|
|
// ── Exports ───────────────────────────────────────────────────────────────────
|
|
|
|
function query(sql, params) {
|
|
return pool.query(sql, params);
|
|
}
|
|
|
|
module.exports = { query, pool, initSchema };
|