app/database/models.py

from datetime import datetime
from typing import Optional
from sqlalchemy import BigInteger, String, ForeignKey, Integer, DateTime, Boolean
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
from sqlalchemy.ext.asyncio import AsyncAttrs, async_sessionmaker, create_async_engine
from app.config.config import DATABASE_URL

engine = create_async_engine(DATABASE_URL) # подключение и создание БД
async_session = async_sessionmaker(engine, expire_on_commit=False)


class Base(AsyncAttrs, DeclarativeBase):
    """Base class for all models"""
    pass


class User(Base):
    """User model for storing telegram user data"""
    __tablename__ = 'users'

    id: Mapped[int] = mapped_column(primary_key=True)
    tg_id: Mapped[int] = mapped_column(BigInteger, unique=True, nullable=False)
    name: Mapped[str] = mapped_column(String(100), nullable=True)
    login: Mapped[str] = mapped_column(String(100), nullable=True)
    contact: Mapped[str] = mapped_column(String(100), nullable=True)
    subscription_status: Mapped[str] = mapped_column(
        String(20), default='inactive')
    # Relationships
    test_attempts = relationship("TestAttempt", back_populates="user")
    feedback = relationship("Feedback", back_populates="user")


class Service(Base):
    """Service model for storing available services"""
    __tablename__ = 'services'

    id: Mapped[int] = mapped_column(primary_key=True)
    service_name: Mapped[str] = mapped_column(String(100), unique=True, nullable=False)
    service_description: Mapped[str] = mapped_column(String(500))
    service_price: Mapped[int] = mapped_column(BigInteger, nullable=False)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    # Relationships
    feedback = relationship("Feedback", back_populates="service")


class Test(Base):
    """Test model for storing quiz/test information"""
    __tablename__ = 'tests'

    id: Mapped[int] = mapped_column(primary_key=True)
    test_name: Mapped[str] = mapped_column(String(100), unique=True, nullable=False)
    test_type: Mapped[str] = mapped_column(String(20), nullable=False)
    test_description: Mapped[str] = mapped_column(String(250))
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    completion_message: Mapped[Optional[str]] = mapped_column(String(1000))
    # Relationships
    questions = relationship("TestQuestion", back_populates="test", cascade="all, delete-orphan")
    results = relationship("TestResult", back_populates="test", cascade="all, delete-orphan")
    attempts = relationship("TestAttempt", back_populates="test")


class TestQuestion(Base):
    """Model for storing test questions"""
    __tablename__ = 'test_questions'

    id: Mapped[int] = mapped_column(primary_key=True)
    test_id: Mapped[int] = mapped_column(ForeignKey('tests.id', ondelete='CASCADE'), nullable=False)
    question_content: Mapped[str] = mapped_column(String(150), nullable=False)
    question_variants: Mapped[str] = mapped_column(String(500), nullable=False)  # JSON string
    question_points: Mapped[str] = mapped_column(String(100), nullable=False)  # JSON string
    # Relationships
    test = relationship("Test", back_populates="questions")


class TestResult(Base):
    """Model for storing possible test results"""
    __tablename__ = 'test_results'

    id: Mapped[int] = mapped_column(primary_key=True)
    test_id: Mapped[int] = mapped_column(ForeignKey('tests.id', ondelete='CASCADE'), nullable=False)
    min_points: Mapped[int] = mapped_column(Integer, nullable=False)
    max_points: Mapped[int] = mapped_column(Integer, nullable=False)
    result_text: Mapped[str] = mapped_column(String(1000), nullable=False)
    # Relationships
    test = relationship("Test", back_populates="results")


class TestAttempt(Base):
    """Model for storing user test attempts"""
    __tablename__ = 'test_attempts'

    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    test_id: Mapped[int] = mapped_column(ForeignKey('tests.id', ondelete='CASCADE'), nullable=False)
    score: Mapped[int] = mapped_column(Integer, nullable=True)
    result: Mapped[Optional[str]] = mapped_column(String(1000), nullable=True)
    completed_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.now, nullable=True)
    # Relationships
    user = relationship("User", back_populates="test_attempts")
    test = relationship("Test", back_populates="attempts")


class TestAnswer(Base):
    """Stores individual answers for each question"""
    __tablename__ = 'test_answers'

    id: Mapped[int] = mapped_column(primary_key=True)
    attempt_id: Mapped[int] = mapped_column(ForeignKey('test_attempts.id', ondelete='CASCADE'))
    question_id: Mapped[int] = mapped_column(ForeignKey('test_questions.id', ondelete='CASCADE'))
    answer_given: Mapped[str] = mapped_column(String(500))
    points_earned: Mapped[int] = mapped_column(Integer, nullable=True)


class LeadMagnet(Base):
    """Model for storing lead magnets/content triggers"""
    __tablename__ = 'lead_magnets'

    id: Mapped[int] = mapped_column(primary_key=True)
    trigger: Mapped[str] = mapped_column(String(50), unique=True, nullable=False)
    content: Mapped[str] = mapped_column(String(500), nullable=False)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)


class Feedback(Base):
    """Model for storing user feedback"""
    __tablename__ = 'feedback'

    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    service_id: Mapped[int] = mapped_column(ForeignKey('services.id', ondelete='CASCADE'), nullable=False)
    rating: Mapped[int] = mapped_column(Integer, nullable=False)
    review: Mapped[str] = mapped_column(String(1000))
    is_new: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    # Relationships
    user = relationship("User", back_populates="feedback")
    service = relationship("Service", back_populates="feedback")


async def async_main():
    """Initialize database tables"""
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)