database.py

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()