database.py

# Day 6: Database setup. One file, simple.

from sqlalchemy import create_engine, Column, String, Integer, Text, DateTime
from sqlalchemy.orm import sessionmaker, declarative_base
from datetime import datetime

# Create SQLite database (saved as a file)
engine = create_engine("sqlite:///interviews.db")
SessionLocal = sessionmaker(bind=engine)
Base = declarative_base()

# ─── Define the Interview table ───
class Interview(Base):
    __tablename__ = "interviews"

    id = Column(String, primary_key=True)     # Session UUID
    name = Column(String)
    position = Column(String)
    company = Column(String)
    level = Column(String)
    messages = Column(Text)                    # JSON string of chat history
    feedback = Column(Text, nullable=True)     # Feedback (filled later)
    score = Column(Integer, nullable=True)     # Score 1-10 (filled later)
    created_at = Column(DateTime, default=datetime.utcnow)

# Create the table
Base.metadata.create_all(engine)