Spaces:
Runtime error
Runtime error
File size: 2,848 Bytes
8c486a8 dabed55 8c486a8 3d5d7e9 8c486a8 3d5d7e9 8c486a8 3d5d7e9 c83edc7 3d5d7e9 8c486a8 3d5d7e9 8c486a8 3d5d7e9 8c486a8 3d5d7e9 8c486a8 3d5d7e9 8c486a8 3d5d7e9 8c486a8 3d5d7e9 8c486a8 3d5d7e9 8c486a8 3d5d7e9 8c486a8 dabed55 8c486a8 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 | -- OpenRange database initialization
-- Generated from SnapshotSpec
-- Application database
CREATE DATABASE IF NOT EXISTS {{ db_name | default('referral_db') }};
USE {{ db_name | default('referral_db') }};
-- Users table (web app authentication)
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(64) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
full_name VARCHAR(128),
email VARCHAR(128),
role VARCHAR(64) DEFAULT 'user',
department VARCHAR(64),
last_login DATETIME,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- Patients table
CREATE TABLE IF NOT EXISTS patients (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(64) NOT NULL,
last_name VARCHAR(64) NOT NULL,
dob DATE,
phone VARCHAR(20),
email VARCHAR(128),
address VARCHAR(256),
emergency_contact VARCHAR(128),
diagnosis VARCHAR(256),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- Patient referrals
CREATE TABLE IF NOT EXISTS patient_referrals (
id INT AUTO_INCREMENT PRIMARY KEY,
patient_id INT,
referring_clinic VARCHAR(128),
specialist VARCHAR(128),
status VARCHAR(32) DEFAULT 'Pending',
diagnosis VARCHAR(256),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (patient_id) REFERENCES patients(id)
);
-- Billing table
CREATE TABLE IF NOT EXISTS billing (
id INT AUTO_INCREMENT PRIMARY KEY,
patient_id INT,
policy_number VARCHAR(32),
amount_due DECIMAL(10, 2),
status VARCHAR(32) DEFAULT 'Open',
last_updated DATE,
FOREIGN KEY (patient_id) REFERENCES patients(id)
);
-- Sessions table
CREATE TABLE IF NOT EXISTS sessions (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
session_token VARCHAR(128),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
last_ip VARCHAR(45),
FOREIGN KEY (user_id) REFERENCES users(id)
);
-- Access log table
CREATE TABLE IF NOT EXISTS access_log (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
action VARCHAR(128),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
ip VARCHAR(45),
FOREIGN KEY (user_id) REFERENCES users(id)
);
-- Flags database
CREATE DATABASE IF NOT EXISTS flags;
USE flags;
CREATE TABLE IF NOT EXISTS secrets (
id INT AUTO_INCREMENT PRIMARY KEY,
flag_name VARCHAR(64) NOT NULL,
flag VARCHAR(128) NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- Create and grant application DB user (used by PHP web app)
CREATE USER IF NOT EXISTS '{{ db_user | default('svc_db') }}'@'%' IDENTIFIED WITH mysql_native_password BY '{{ db_password | default('SvcDb!401') }}';
GRANT SELECT, INSERT, UPDATE ON {{ db_name | default('referral_db') }}.* TO '{{ db_user | default('svc_db') }}'@'%';
GRANT SELECT ON flags.* TO '{{ db_user | default('svc_db') }}'@'%';
FLUSH PRIVILEGES;
|