app/db/models.py

from __future__ import annotations

from datetime import datetime
from typing import Any

from sqlalchemy import (
    JSON,
    BigInteger,
    DateTime,
    Float,
    ForeignKey,
    Integer,
    String,
    Text,
    UniqueConstraint,
    func,
)
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship

from app.core.constants import (
    DEFAULT_INTERFACE_LANGUAGE,
    DEFAULT_LEVEL,
    DEFAULT_SPEECH_RATE,
    DEFAULT_TOPIC,
    DEFAULT_VOICE,
)


class Base(DeclarativeBase):
    """Declarative base for all ORM models."""


class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=False)
    level: Mapped[str] = mapped_column(String(2), default=DEFAULT_LEVEL.value, nullable=False)
    voice_name: Mapped[str] = mapped_column(String(32), default=DEFAULT_VOICE.value, nullable=False)
    topic: Mapped[str] = mapped_column(String(32), default=DEFAULT_TOPIC.value, nullable=False)
    speech_rate: Mapped[float] = mapped_column(Float, default=DEFAULT_SPEECH_RATE, nullable=False)
    interface_language: Mapped[str] = mapped_column(
        String(2),
        default=DEFAULT_INTERFACE_LANGUAGE.value,
        server_default=DEFAULT_INTERFACE_LANGUAGE.value,
        nullable=False,
    )
    last_assessed_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), nullable=True, default=None
    )
    assessment_message_count: Mapped[int] = mapped_column(Integer, default=0, server_default="0")
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        server_default=func.now(),
        onupdate=func.now(),
        nullable=False,
    )

    turns: Mapped[list[DialogTurn]] = relationship(
        back_populates="user", cascade="all, delete-orphan", lazy="raise"
    )


class DialogTurn(Base):
    __tablename__ = "dialog_turns"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    user_id: Mapped[int] = mapped_column(
        BigInteger, ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False
    )
    role: Mapped[str] = mapped_column(String(16), nullable=False)
    text: Mapped[str | None] = mapped_column(Text, nullable=True)
    corrections_count: Mapped[int] = mapped_column(Integer, default=0, server_default="0")
    # Storage key of this turn's recorded ogg/opus (None until audio is persisted).
    audio_key: Mapped[str | None] = mapped_column(String(255), nullable=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )

    user: Mapped[User] = relationship(back_populates="turns")


class SavedWord(Base):
    __tablename__ = "saved_words"
    __table_args__ = (UniqueConstraint("user_id", "word"),)

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    user_id: Mapped[int] = mapped_column(
        BigInteger, ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False
    )
    word: Mapped[str] = mapped_column(String(100), nullable=False)
    meaning: Mapped[str] = mapped_column(Text, nullable=False)
    context: Mapped[str | None] = mapped_column(Text, nullable=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )


class PronunciationError(Base):
    __tablename__ = "pronunciation_errors"
    __table_args__ = (UniqueConstraint("user_id", "word_example"),)

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    user_id: Mapped[int] = mapped_column(
        BigInteger, ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False
    )
    expected_phoneme: Mapped[str] = mapped_column(String(200), nullable=False)
    actual_phoneme: Mapped[str] = mapped_column(String(200), nullable=False)
    word_example: Mapped[str] = mapped_column(String(100), nullable=False)
    occurrence_count: Mapped[int] = mapped_column(Integer, default=1, server_default="1")
    last_seen_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )


class GrammarExercise(Base):
    __tablename__ = "grammar_exercises"
    __table_args__ = (UniqueConstraint("user_id", "wrong_text", "correct_text"),)

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    user_id: Mapped[int] = mapped_column(
        BigInteger, ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False
    )
    wrong_text: Mapped[str] = mapped_column(Text, nullable=False)
    correct_text: Mapped[str] = mapped_column(Text, nullable=False)
    rule: Mapped[str] = mapped_column(Text, nullable=False)
    easiness_factor: Mapped[float] = mapped_column(Float, default=2.5, server_default="2.5")
    interval: Mapped[int] = mapped_column(Integer, default=1, server_default="1")
    repetitions: Mapped[int] = mapped_column(Integer, default=0, server_default="0")
    next_review_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), nullable=True, default=None
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )


class LevelAssessment(Base):
    __tablename__ = "level_assessments"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    user_id: Mapped[int] = mapped_column(
        BigInteger, ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False
    )
    level: Mapped[str] = mapped_column(String(2), nullable=False)
    confidence: Mapped[float] = mapped_column(Float, nullable=False)
    reasoning: Mapped[str] = mapped_column(Text, nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )


class SpeechAssessment(Base):
    """Per-turn speech quality scores (0-100). A null metric means it could not be measured."""

    __tablename__ = "speech_assessments"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    user_id: Mapped[int] = mapped_column(
        BigInteger, ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False
    )
    dialog_turn_id: Mapped[int | None] = mapped_column(
        ForeignKey("dialog_turns.id", ondelete="SET NULL"), index=True, nullable=True
    )
    accuracy: Mapped[int | None] = mapped_column(Integer, nullable=True)
    fluency: Mapped[int | None] = mapped_column(Integer, nullable=True)
    prosody: Mapped[int | None] = mapped_column(Integer, nullable=True)
    vocabulary: Mapped[int | None] = mapped_column(Integer, nullable=True)
    grammar: Mapped[int | None] = mapped_column(Integer, nullable=True)
    topic: Mapped[int | None] = mapped_column(Integer, nullable=True)
    transcript: Mapped[str] = mapped_column(Text, nullable=False, default="")
    # Per-turn grammar fixes: [{"wrong": str, "correct": str, "rule": str}, ...]
    corrections: Mapped[list[dict[str, Any]] | None] = mapped_column(JSON, nullable=True)
    # Per-turn pronunciation notes:
    # [{"word", "your_pronunciation", "correct_pronunciation", "tip"}, ...]
    pronunciation_notes: Mapped[list[dict[str, Any]] | None] = mapped_column(JSON, nullable=True)
    # Word-stress accuracy (0-100): share of multi-syllable words stressed correctly.
    stress_accuracy: Mapped[int | None] = mapped_column(Integer, nullable=True)
    # Per-turn word-stress notes:
    # [{"word", "expected_syllable", "actual_syllable", "expected_phonemes", "tip"}, ...]
    stress_notes: Mapped[list[dict[str, Any]] | None] = mapped_column(JSON, nullable=True)
    # Waveform amplitude bars (0-100) of the user's recording, for the analysis screen.
    peaks: Mapped[list[int] | None] = mapped_column(JSON, nullable=True)
    # Recording length in seconds (from the source PCM).
    duration_sec: Mapped[float | None] = mapped_column(Float, nullable=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )