backend/database.py

import os
from datetime import datetime
from sqlalchemy import (
    create_engine, Column, Integer, String, Float,
    Boolean, DateTime, ForeignKey, Text
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

DATABASE_URL = os.getenv("DATABASE_URL", "sqlite:///./nutqai.db")
connect_args = {"check_same_thread": False} if "sqlite" in DATABASE_URL else {}

engine = create_engine(DATABASE_URL, connect_args=connect_args)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()


class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=False)
    email = Column(String, unique=True, index=True, nullable=False)
    password_hash = Column(String, nullable=False)
    role = Column(String, default="child")   # "child" | "doctor"
    created_at = Column(DateTime, default=datetime.utcnow)

    child_profile = relationship("Child", back_populates="user",
                                 foreign_keys="Child.user_id", uselist=False)


class Child(Base):
    __tablename__ = "children"
    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id"), unique=True)
    age = Column(Integer, nullable=True)
    doctor_id = Column(Integer, ForeignKey("users.id"), nullable=True)
    current_level = Column(Integer, default=1)   # 1 | 2 | 3
    total_xp = Column(Integer, default=0)
    assessment_done = Column(Boolean, default=False)

    user = relationship("User", back_populates="child_profile",
                        foreign_keys=[user_id])
    sessions = relationship("Session", back_populates="child")
    assessments = relationship("Assessment", back_populates="child")


class WordBank(Base):
    __tablename__ = "word_bank"
    id = Column(Integer, primary_key=True, index=True)
    arabic_word = Column(String, nullable=False)
    emoji = Column(String, default="📝")
    category = Column(String)   # animals | fruits | colors | body | home
    level = Column(Integer)     # 1 | 2 | 3
    phonetic_hint = Column(String)


class Session(Base):
    __tablename__ = "sessions"
    id = Column(Integer, primary_key=True, index=True)
    child_id = Column(Integer, ForeignKey("children.id"))
    level = Column(Integer)
    category = Column(String)
    started_at = Column(DateTime, default=datetime.utcnow)
    ended_at = Column(DateTime, nullable=True)
    total_words = Column(Integer, default=0)
    correct_count = Column(Integer, default=0)
    avg_score = Column(Float, default=0.0)

    child = relationship("Child", back_populates="sessions")
    attempts = relationship("WordAttempt", back_populates="session")


class WordAttempt(Base):
    __tablename__ = "word_attempts"
    id = Column(Integer, primary_key=True, index=True)
    session_id = Column(Integer, ForeignKey("sessions.id"), nullable=True)
    child_id = Column(Integer, ForeignKey("children.id"))
    word_id = Column(Integer, ForeignKey("word_bank.id"), nullable=True)
    arabic_word = Column(String)
    is_correct = Column(Boolean)
    confidence_score = Column(Float)
    detected_word = Column(String, nullable=True)
    speech_probability = Column(Float, nullable=True)
    is_assessment = Column(Boolean, default=False)
    timestamp = Column(DateTime, default=datetime.utcnow)

    session = relationship("Session", back_populates="attempts")
    word = relationship("WordBank")


class Assessment(Base):
    __tablename__ = "assessments"
    id = Column(Integer, primary_key=True, index=True)
    child_id = Column(Integer, ForeignKey("children.id"))
    level_assigned = Column(Integer)
    overall_score = Column(Float)
    created_at = Column(DateTime, default=datetime.utcnow)

    child = relationship("Child", back_populates="assessments")


def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


def init_db():
    Base.metadata.create_all(bind=engine)