Spaces:
Sleeping
Sleeping
File size: 10,862 Bytes
c024705 |
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 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 |
#!/usr/bin/env python3
"""
Initialize the database with all required tables
"""
import sqlite3
import os
import time
# Database file path
DB_FILE = "aimhsa.db"
def init_database():
"""Initialize the database with all required tables"""
print("="*60)
print("INITIALIZING DATABASE")
print("="*60)
# Create directory if it doesn't exist (only if there's a directory)
db_dir = os.path.dirname(DB_FILE)
if db_dir:
os.makedirs(db_dir, exist_ok=True)
conn = sqlite3.connect(DB_FILE)
try:
print("Creating tables...")
# Messages table
conn.execute("""
CREATE TABLE IF NOT EXISTS messages (
id INTEGER PRIMARY KEY AUTOINCREMENT,
conv_id TEXT NOT NULL,
role TEXT NOT NULL,
content TEXT NOT NULL,
ts REAL NOT NULL
)
""")
print("✅ messages table created")
# Attachments table
conn.execute("""
CREATE TABLE IF NOT EXISTS attachments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
conv_id TEXT NOT NULL,
filename TEXT NOT NULL,
text TEXT NOT NULL,
ts REAL NOT NULL
)
""")
print("✅ attachments table created")
# Sessions table
conn.execute("""
CREATE TABLE IF NOT EXISTS sessions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
key TEXT UNIQUE NOT NULL,
conv_id TEXT NOT NULL,
ts REAL NOT NULL
)
""")
print("✅ sessions table created")
# Users table
conn.execute("""
CREATE TABLE IF NOT EXISTS users (
username TEXT PRIMARY KEY,
password_hash TEXT NOT NULL,
created_ts REAL NOT NULL
)
""")
print("✅ users table created")
# Add additional columns to users table
cursor = conn.execute("PRAGMA table_info(users)")
columns = [column[1] for column in cursor.fetchall()]
if "email" not in columns:
conn.execute("ALTER TABLE users ADD COLUMN email TEXT")
if "fullname" not in columns:
conn.execute("ALTER TABLE users ADD COLUMN fullname TEXT")
if "telephone" not in columns:
conn.execute("ALTER TABLE users ADD COLUMN telephone TEXT")
if "province" not in columns:
conn.execute("ALTER TABLE users ADD COLUMN province TEXT")
if "district" not in columns:
conn.execute("ALTER TABLE users ADD COLUMN district TEXT")
if "created_at" not in columns:
conn.execute("ALTER TABLE users ADD COLUMN created_at REAL")
print("✅ users table columns updated")
# Password resets table
conn.execute("""
CREATE TABLE IF NOT EXISTS password_resets (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL,
token TEXT UNIQUE NOT NULL,
expires_ts REAL NOT NULL,
used INTEGER DEFAULT 0
)
""")
print("✅ password_resets table created")
# Conversations table
conn.execute("""
CREATE TABLE IF NOT EXISTS conversations (
conv_id TEXT PRIMARY KEY,
owner_key TEXT,
preview TEXT,
ts REAL
)
""")
print("✅ conversations table created")
# Add additional columns to conversations table
try:
cur = conn.execute("PRAGMA table_info(conversations)")
cols = [r[1] for r in cur.fetchall()]
if "archive_pw_hash" not in cols:
conn.execute("ALTER TABLE conversations ADD COLUMN archive_pw_hash TEXT")
if "booking_prompt_shown" not in cols:
conn.execute("ALTER TABLE conversations ADD COLUMN booking_prompt_shown INTEGER DEFAULT 0")
except Exception:
pass
print("✅ conversations table columns updated")
# Professionals table
conn.execute("""
CREATE TABLE IF NOT EXISTS professionals (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT UNIQUE NOT NULL,
password_hash TEXT NOT NULL,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
email TEXT NOT NULL,
phone TEXT,
license_number TEXT,
specialization TEXT NOT NULL,
expertise_areas TEXT NOT NULL,
languages TEXT NOT NULL,
qualifications TEXT NOT NULL,
availability_schedule TEXT NOT NULL,
location_latitude REAL,
location_longitude REAL,
location_address TEXT,
district TEXT,
max_patients_per_day INTEGER DEFAULT 10,
consultation_fee REAL,
experience_years INTEGER,
bio TEXT,
profile_picture TEXT,
is_active BOOLEAN DEFAULT 1,
created_ts REAL NOT NULL,
updated_ts REAL NOT NULL
)
""")
print("✅ professionals table created")
# Risk assessments table
conn.execute("""
CREATE TABLE IF NOT EXISTS risk_assessments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
conv_id TEXT NOT NULL,
user_query TEXT NOT NULL,
risk_score REAL NOT NULL,
risk_level TEXT NOT NULL,
detected_indicators TEXT,
assessment_timestamp REAL NOT NULL,
processed BOOLEAN DEFAULT 0,
booking_created BOOLEAN DEFAULT 0
)
""")
print("✅ risk_assessments table created")
# Automated bookings table
conn.execute("""
CREATE TABLE IF NOT EXISTS automated_bookings (
id INTEGER PRIMARY KEY AUTOINCREMENT,
booking_id TEXT UNIQUE NOT NULL,
conv_id TEXT NOT NULL,
user_account TEXT,
user_ip TEXT,
professional_id INTEGER NOT NULL,
risk_level TEXT NOT NULL,
risk_score REAL NOT NULL,
detected_indicators TEXT,
conversation_summary TEXT,
booking_status TEXT DEFAULT 'pending',
scheduled_datetime REAL,
session_type TEXT DEFAULT 'routine',
location_preference TEXT,
notes TEXT,
created_ts REAL NOT NULL,
updated_ts REAL NOT NULL,
FOREIGN KEY (professional_id) REFERENCES professionals (id)
)
""")
print("✅ automated_bookings table created")
# Professional notifications table
conn.execute("""
CREATE TABLE IF NOT EXISTS professional_notifications (
id INTEGER PRIMARY KEY AUTOINCREMENT,
professional_id INTEGER NOT NULL,
booking_id TEXT NOT NULL,
notification_type TEXT NOT NULL,
title TEXT NOT NULL,
message TEXT NOT NULL,
is_read BOOLEAN DEFAULT 0,
priority TEXT DEFAULT 'normal',
created_ts REAL NOT NULL,
FOREIGN KEY (professional_id) REFERENCES professionals (id)
)
""")
print("✅ professional_notifications table created")
# Therapy sessions table
conn.execute("""
CREATE TABLE IF NOT EXISTS therapy_sessions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
booking_id TEXT NOT NULL,
professional_id INTEGER NOT NULL,
conv_id TEXT NOT NULL,
session_start REAL,
session_end REAL,
session_notes TEXT,
treatment_plan TEXT,
follow_up_required BOOLEAN DEFAULT 0,
follow_up_date REAL,
session_rating INTEGER,
session_feedback TEXT,
created_ts REAL NOT NULL,
FOREIGN KEY (professional_id) REFERENCES professionals (id)
)
""")
print("✅ therapy_sessions table created")
# Session notes table
conn.execute("""
CREATE TABLE IF NOT EXISTS session_notes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
booking_id TEXT NOT NULL,
professional_id INTEGER NOT NULL,
notes TEXT,
treatment_plan TEXT,
follow_up_required BOOLEAN DEFAULT 0,
follow_up_date REAL,
created_ts REAL NOT NULL,
FOREIGN KEY (professional_id) REFERENCES professionals (id)
)
""")
print("✅ session_notes table created")
# Conversation messages table
conn.execute("""
CREATE TABLE IF NOT EXISTS conversation_messages (
id INTEGER PRIMARY KEY AUTOINCREMENT,
conv_id TEXT NOT NULL,
sender TEXT NOT NULL,
content TEXT NOT NULL,
timestamp REAL NOT NULL
)
""")
print("✅ conversation_messages table created")
# Admin users table
conn.execute("""
CREATE TABLE IF NOT EXISTS admin_users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT UNIQUE NOT NULL,
password_hash TEXT NOT NULL,
email TEXT NOT NULL,
role TEXT DEFAULT 'admin',
permissions TEXT,
created_ts REAL NOT NULL
)
""")
print("✅ admin_users table created")
# Commit all changes
conn.commit()
print("\n" + "="*60)
print("DATABASE INITIALIZATION COMPLETE!")
print("="*60)
# Verify tables were created
tables = conn.execute("""
SELECT name FROM sqlite_master WHERE type='table' ORDER BY name
""").fetchall()
print(f"\nCreated {len(tables)} tables:")
for table in tables:
print(f" ✅ {table[0]}")
conn.close()
except Exception as e:
print(f"❌ Error initializing database: {e}")
conn.close()
raise
if __name__ == "__main__":
init_database()
|