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)
    interview_log = db.Column(db.Text)

    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()

        # Dynamically add the ``num_questions`` column to the ``jobs`` table
        # if it is missing.  When deploying an updated application against an
        # existing database, the new field will not appear until we run an
        # explicit ALTER TABLE.  Inspect the current table schema and add
        # ``num_questions`` with a default of 3 if it doesn't exist.  This
        # logic is idempotent: the ALTER TABLE statement runs only when
        # necessary.
        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:
                # SQLite supports adding new columns via ALTER TABLE.  The
                # default value of 3 matches the default declared in the
                # Job model.  If you are using a different database backend,
                # verify that this syntax is supported.
                db.session.execute('ALTER TABLE jobs ADD COLUMN num_questions INTEGER NOT NULL DEFAULT 3')
                db.session.commit()
        except Exception:
            # If inspection fails (e.g. the table does not exist yet), rely on
            # SQLAlchemy's create_all() to create a fresh schema with the
            # ``num_questions`` column.
            pass

        # Database tables are created on application start.  We intentionally do not
        # seed any sample data here.  Previously a block of code inserted dummy
        # job listings whenever the jobs table was empty.  In production, jobs
        # should only be added by authenticated recruiters via the job posting
        # interface.  Leaving the seeding logic in place would result in fake
        # positions appearing every time the application starts, which is
        # undesirable for a live recruitment platform.  If your environment
        # requires initial data for testing, insert it manually via the
        # database or through the new recruiter job posting page.