File size: 5,817 Bytes
a0c836f | 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 | from sqlalchemy import (
create_engine, Column, Integer, String, Text, TIMESTAMP, ForeignKey,
Boolean, UniqueConstraint
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from datetime import datetime
import os
from dotenv import load_dotenv
load_dotenv()
# Allow overriding the DB via env (e.g. sqlite for local dev); the original
# Postgres connection stays the default so existing setups are unchanged.
Database_URL = os.getenv(
"DATABASE_URL",
"postgresql://postgres:12345678@localhost:5432/medicine_assistant",
)
# SQLite needs check_same_thread=False when used across threads (APScheduler).
_connect_args = {"check_same_thread": False} if Database_URL.startswith("sqlite") else {}
engine = create_engine(Database_URL, connect_args=_connect_args)
SessionLocal = sessionmaker(bind=engine)
Base = declarative_base()
class Conversation(Base):
__tablename__ = "conversations"
id = Column(Integer, primary_key=True, index=True)
user_id = Column(Integer, nullable=True)
title = Column(String(100), nullable=True)
created_at = Column(TIMESTAMP, default=datetime.utcnow)
class Message(Base):
__tablename__ = "messages"
id = Column(Integer, primary_key=True, index=True)
conversation_id = Column(Integer, ForeignKey("conversations.id", ondelete="CASCADE"))
role = Column(String(10), nullable=False)
text = Column(Text, nullable=True)
image_path = Column(String(255), nullable=True) # ← new
created_at = Column(TIMESTAMP, default=datetime.utcnow)
# ---------------------------------------------------------------------------
# Medication-adherence layer (ADDITIVE — new tables only, existing ones intact)
# ---------------------------------------------------------------------------
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True, index=True)
email = Column(String(255), unique=True, index=True, nullable=False)
password_hash = Column(String(255), nullable=False)
name = Column(String(120), nullable=True)
language = Column(String(5), default="en")
points = Column(Integer, default=0)
created_at = Column(TIMESTAMP, default=datetime.utcnow)
medications = relationship(
"UserMedication", back_populates="user",
cascade="all, delete-orphan"
)
schedule_entries = relationship(
"ScheduleEntry", cascade="all, delete-orphan"
)
dose_events = relationship(
"DoseEvent", cascade="all, delete-orphan"
)
push_subscriptions = relationship(
"PushSubscription", cascade="all, delete-orphan"
)
badges = relationship(
"Badge", cascade="all, delete-orphan"
)
class UserMedication(Base):
__tablename__ = "user_medications"
id = Column(Integer, primary_key=True, index=True)
user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), index=True)
drug_name = Column(String(120), nullable=False) # canonical name from medicines.json
doses_per_day = Column(Integer, nullable=True)
meal_relation_cached = Column(String(255), nullable=True)
pack_quantity = Column(Integer, nullable=True) # Phase 7: refill reminder
refill_at = Column(TIMESTAMP, nullable=True) # Phase 7: when stock runs low
created_at = Column(TIMESTAMP, default=datetime.utcnow)
user = relationship("User", back_populates="medications")
class ScheduleEntry(Base):
__tablename__ = "schedule_entries"
id = Column(Integer, primary_key=True, index=True)
user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), index=True)
user_medication_id = Column(
Integer, ForeignKey("user_medications.id", ondelete="CASCADE")
)
drug_name = Column(String(120), nullable=False)
dose_time = Column(String(5), nullable=False) # "HH:MM" local
meal_note = Column(String(120), nullable=True)
created_at = Column(TIMESTAMP, default=datetime.utcnow)
class DoseEvent(Base):
__tablename__ = "dose_events"
id = Column(Integer, primary_key=True, index=True)
user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), index=True)
schedule_entry_id = Column(
Integer, ForeignKey("schedule_entries.id", ondelete="CASCADE"), nullable=True
)
drug_name = Column(String(120), nullable=False)
scheduled_for = Column(TIMESTAMP, nullable=False, index=True)
# pending / taken / snoozed / skipped / missed
status = Column(String(12), default="pending", index=True)
responded_at = Column(TIMESTAMP, nullable=True)
pushed = Column(Boolean, default=False) # reminder already sent
caregiver_notified = Column(Boolean, default=False)
points_awarded = Column(Integer, default=0)
created_at = Column(TIMESTAMP, default=datetime.utcnow)
class PushSubscription(Base):
__tablename__ = "push_subscriptions"
id = Column(Integer, primary_key=True, index=True)
user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), index=True)
endpoint = Column(Text, unique=True, nullable=False)
p256dh = Column(Text, nullable=False)
auth = Column(Text, nullable=False)
created_at = Column(TIMESTAMP, default=datetime.utcnow)
class Badge(Base):
__tablename__ = "badges"
__table_args__ = (UniqueConstraint("user_id", "code", name="uq_user_badge"),)
id = Column(Integer, primary_key=True, index=True)
user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), index=True)
code = Column(String(40), nullable=False) # first_dose / 7_day_streak / perfect_week
awarded_at = Column(TIMESTAMP, default=datetime.utcnow)
def get_db():
db = SessionLocal()
try:
yield db
finally:
db.close() |