backend/models/database.py

from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
import json

db = SQLAlchemy()

# ✅ Import User model here, before Job and Application use it
from backend.models.user import User

class Job(db.Model):
    __tablename__ = 'jobs'

    id = db.Column(db.Integer, primary_key=True)
    role = db.Column(db.String(100), nullable=False)
    description = db.Column(db.Text, nullable=False)
    seniority = db.Column(db.String(50), nullable=False)
    skills = db.Column(db.Text, nullable=False)
    company = db.Column(db.String(100), nullable=False)
    date_posted = db.Column(db.DateTime, default=datetime.utcnow)

    # Number of interview questions for this job. Recruiters can set this
    # value when posting a job. Defaults to 3 to preserve existing
    # behaviour where the interview consists of three questions. The
    # interview API uses this field to determine when to stop asking
    # follow‑up questions. See backend/routes/interview_api.py for
    # details.
    num_questions = db.Column(db.Integer, nullable=False, default=3)

    recruiter_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=True)
    recruiter = db.relationship('User', backref='posted_jobs')

    @property
    def skills_list(self):
        """Return a list of skills parsed from the JSON string stored in ``skills``.

        The ``skills`` column stores a JSON encoded list of skills (e.g. '["Python", "Flask"]').
        In templates it is convenient to work with a Python list so that skills can be joined
        or iterated over.  If parsing fails for any reason an empty list is returned.
        """
        try:
            # Import json lazily to avoid circular imports at module import time.
            import json as _json
            return _json.loads(self.skills) if self.skills else []
        except Exception:
            return []

    def __repr__(self):
        return f"<Job {self.role} at {self.company}>"

class Application(db.Model):
    __tablename__ = 'applications'

    id = db.Column(db.Integer, primary_key=True)
    job_id = db.Column(db.Integer, db.ForeignKey('jobs.id'), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)

    name = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(100), nullable=False)
    resume_path = db.Column(db.String(255), nullable=True)
    cover_letter = db.Column(db.Text, nullable=True)
    extracted_features = db.Column(db.Text, nullable=True)
    status = db.Column(db.String(50), default='applied')
    date_applied = db.Column(db.DateTime, default=datetime.utcnow)

    user = db.relationship('User', backref='applications')

    # Set up a relationship back to the Job so that templates can access
    # ``application.job`` directly.  Without this relationship you'd need to
    # query the Job model manually in the route or template, which is less
    # convenient and can lead to additional database queries.
    job = db.relationship('Job', backref='applications', lazy='joined')

    def __repr__(self):
        return f"Application('{self.name}', '{self.email}', Job ID: {self.job_id})"

    def get_profile_data(self):
        try:
            return json.loads(self.extracted_features) if self.extracted_features else {}
        except:
            return {}

def init_db(app):
    db.init_app(app)
    with app.app_context():
        # Create any missing tables.  SQLAlchemy does not automatically add
        # columns to existing tables, so we call create_all() first to ensure
        # new tables (like ``applications`` or ``jobs``) are present.
        db.create_all()

        from sqlalchemy import inspect
        inspector = inspect(db.engine)
        try:
            columns = [col['name'] for col in inspector.get_columns('jobs')]
            if 'num_questions' not in columns:
                db.session.execute('ALTER TABLE jobs ADD COLUMN num_questions INTEGER NOT NULL DEFAULT 3')
                db.session.commit()
        except Exception:
            pass