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

        # Seed professional demo data (recruiter + applicant + a curated set of
        # jobs) so the platform always looks polished and the full demo flow
        # works after every restart.  On Hugging Face the SQLite DB lives in
        # the ephemeral /tmp directory and is wiped on each restart, so this
        # idempotent seeding repopulates a clean, consistent dataset every time.
        seed_demo_data()


# Known demo accounts used for the marketing demo.  Passwords are intentionally
# simple here because these are throwaway demo accounts on an ephemeral DB.
DEMO_RECRUITER_EMAIL = 'hr@codingo.ai'
DEMO_APPLICANT_EMAIL = 'candidate@codingo.ai'
DEMO_PASSWORD = 'codingo123'


def seed_demo_data():
    """Idempotently seed a demo recruiter, a demo applicant and a curated set
    of professional job listings.

    Safe to call on every startup: each entity is only created when missing,
    and jobs are only inserted when the ``jobs`` table is empty.  Any failure
    is rolled back and swallowed so seeding can never block app startup.
    """
    try:
        # --- Demo recruiter (owns the seeded jobs so they appear in the HR
        #     dashboard, which filters by recruiter_id) ---
        recruiter = User.query.filter_by(email=DEMO_RECRUITER_EMAIL).first()
        if recruiter is None:
            recruiter = User(
                username='Codingo HR',
                email=DEMO_RECRUITER_EMAIL,
                role='recruiter',
            )
            recruiter.set_password(DEMO_PASSWORD)
            db.session.add(recruiter)
            db.session.commit()

        # --- Demo applicant (job-seeker role is 'unemployed') ---
        applicant = User.query.filter_by(email=DEMO_APPLICANT_EMAIL).first()
        if applicant is None:
            applicant = User(
                username='Demo Candidate',
                email=DEMO_APPLICANT_EMAIL,
                role='unemployed',
            )
            applicant.set_password(DEMO_PASSWORD)
            db.session.add(applicant)
            db.session.commit()

        # --- Jobs: only seed when there are none, to avoid duplicates and to
        #     never clobber jobs a recruiter may have posted at runtime ---
        if Job.query.count() == 0:
            demo_jobs = [
                {
                    'role': 'Data Scientist',
                    'seniority': 'Mid-level',
                    'company': 'NorthBridge Analytics',
                    'skills': ['Python', 'SQL', 'Pandas', 'scikit-learn',
                               'Machine Learning', 'Statistics', 'Data Visualization'],
                    'description': (
                        "We are looking for a Data Scientist to turn raw data into "
                        "actionable insight. You will design and evaluate machine "
                        "learning models, run statistical analyses, and build clear "
                        "visualizations that guide product and business decisions. "
                        "You will collaborate closely with engineering and product "
                        "teams to take models from prototype to production."
                    ),
                },
                {
                    'role': 'Data Engineer',
                    'seniority': 'Senior',
                    'company': 'Cloudbyte Systems',
                    'skills': ['Python', 'SQL', 'Apache Spark', 'Airflow',
                               'ETL', 'AWS', 'Data Warehousing'],
                    'description': (
                        "Join us as a Senior Data Engineer to design, build and "
                        "maintain the data pipelines that power our analytics and "
                        "machine learning platforms. You will own scalable ETL "
                        "workflows, optimize our data warehouse, and ensure data "
                        "quality and reliability across the organization."
                    ),
                },
                {
                    'role': 'Machine Learning Engineer',
                    'seniority': 'Mid-level',
                    'company': 'Vantix AI',
                    'skills': ['Python', 'TensorFlow', 'PyTorch', 'Machine Learning',
                               'MLOps', 'Model Deployment', 'Docker'],
                    'description': (
                        "As a Machine Learning Engineer you will take models from "
                        "research into reliable, production-grade services. You will "
                        "train and fine-tune models, build robust deployment and "
                        "monitoring pipelines, and work with data scientists to "
                        "ship features that delight our users."
                    ),
                },
                {
                    'role': 'NLP Engineer',
                    'seniority': 'Senior',
                    'company': 'Lingua Labs',
                    'skills': ['Python', 'NLP', 'Transformers', 'spaCy',
                               'PyTorch', 'LLMs', 'Hugging Face'],
                    'description': (
                        "We are hiring a Senior NLP Engineer to build state-of-the-art "
                        "language understanding systems. You will develop models for "
                        "text classification, information extraction and conversational "
                        "AI, fine-tune large language models, and deploy them at scale "
                        "to power our products."
                    ),
                },
                {
                    'role': 'Computer Vision Engineer',
                    'seniority': 'Mid-level',
                    'company': 'VisionWorks AI',
                    'skills': ['Python', 'OpenCV', 'Deep Learning', 'PyTorch',
                               'Image Processing', 'CNNs', 'Model Optimization'],
                    'description': (
                        "Join our team as a Computer Vision Engineer to develop "
                        "image and video understanding systems. You will design and "
                        "train deep learning models for detection, segmentation and "
                        "recognition, optimize them for real-time performance, and "
                        "integrate them into our production pipeline."
                    ),
                },
            ]

            for spec in demo_jobs:
                db.session.add(Job(
                    role=spec['role'],
                    description=spec['description'],
                    seniority=spec['seniority'],
                    skills=json.dumps(spec['skills']),
                    company=spec['company'],
                    recruiter_id=recruiter.id,
                    num_questions=4,
                ))
            db.session.commit()

        # --- Pre-seed completed demo interviews for the demo candidate so the
        #     HR dashboard is always populated even after a restart (the SQLite
        #     DB is ephemeral on Hugging Face).  Idempotent per (candidate, job):
        #     a row is only created when one does not already exist. ---
        demo_interviews = [
            {
                'role': 'Data Scientist',
                'skills': ['Python', 'SQL', 'Pandas', 'scikit-learn',
                           'Machine Learning', 'Data Visualization'],
                'experience': [
                    'Data Analyst at BrightData (2 years) — built dashboards and predictive models',
                    'Machine Learning Intern at NorthBridge Analytics — customer churn prediction',
                ],
                'education': ['BSc in Computer Science, USAL'],
                'qa_log': [
                    {
                        'question': "Hi, I'm LUNA, your AI recruiter. Can you tell me about your background and what drew you to data science?",
                        'answer': "I have around three years working with data. I started as a data analyst building dashboards, then moved into machine learning where I built churn and forecasting models in Python with scikit-learn. I love data science because it turns messy data into decisions that actually move the business.",
                        'evaluation': {'score': 'Excellent', 'feedback': 'Clear, specific background with concrete tools and real impact on the business.'},
                    },
                    {
                        'question': "How do you prevent a model from overfitting?",
                        'answer': "I use cross-validation to get an honest estimate of performance, keep the model as simple as the data allows, and apply regularization like L1 or L2. I also use more training data when possible, early stopping for neural nets, and I always compare training versus validation error to catch overfitting early.",
                        'evaluation': {'score': 'Excellent', 'feedback': 'Covers cross-validation, regularization, and monitoring train/validation gap accurately.'},
                    },
                    {
                        'question': "Walk me through a data science project you are proud of and the impact it had.",
                        'answer': "At NorthBridge I built a customer churn model. I engineered features from usage logs, trained a gradient boosting model, and reached about 0.86 ROC-AUC. The retention team used the risk scores to target outreach and we reduced monthly churn by roughly 12 percent.",
                        'evaluation': {'score': 'Good', 'feedback': 'Solid end-to-end project with a measurable result; could add more on validation and deployment.'},
                    },
                    {
                        'question': "What are your salary expectations? Are you looking for a full-time or part-time role, and do you prefer remote or on-site work?",
                        'answer': "I'm looking for a full-time role and I'm flexible on salary within the market range for a mid-level data scientist. I'm happy with hybrid or on-site, and comfortable fully remote as well.",
                        'evaluation': {'score': 'Excellent', 'feedback': 'Clear, reasonable and flexible answer on logistics.'},
                    },
                ],
            },
            {
                'role': 'Data Engineer',
                'skills': ['Python', 'SQL', 'Apache Spark', 'Airflow', 'ETL', 'AWS'],
                'experience': [
                    'Data Engineer at Cloudbyte Systems (3 years) — built batch and streaming pipelines',
                    'Backend Developer — automated ETL jobs and data quality checks',
                ],
                'education': ['BSc in Software Engineering, USAL'],
                'qa_log': [
                    {
                        'question': "Hi, I'm LUNA, your AI recruiter. Tell me about your experience and why data engineering?",
                        'answer': "I've spent three years as a data engineer building pipelines in Python and Spark, orchestrated with Airflow on AWS. I enjoy data engineering because reliable, well-modeled data is what makes everything else — analytics and machine learning — actually work.",
                        'evaluation': {'score': 'Excellent', 'feedback': 'Strong, specific experience with the exact stack the role needs.'},
                    },
                    {
                        'question': "How do you design a reliable ETL pipeline?",
                        'answer': "I make each step idempotent so re-runs are safe, add schema validation and data quality checks early, and design for incremental loads instead of full reloads. I orchestrate with Airflow, add retries and alerting on failures, and keep raw data so I can reprocess if logic changes.",
                        'evaluation': {'score': 'Excellent', 'feedback': 'Idempotency, validation, incremental loads and observability — a complete, senior answer.'},
                    },
                    {
                        'question': "Describe a time you optimized a slow data pipeline.",
                        'answer': "A nightly Spark job was taking over four hours. I found it was shuffling too much data, so I repartitioned on the join key, cached a reused dataframe, and switched some wide transformations to broadcast joins. It dropped to about forty minutes.",
                        'evaluation': {'score': 'Good', 'feedback': 'Concrete optimization with a real result; could mention profiling and cost trade-offs.'},
                    },
                    {
                        'question': "What are your salary expectations? Are you looking for a full-time or part-time role, and do you prefer remote or on-site work?",
                        'answer': "Full-time, and I'm open on compensation within the senior data engineer range. I prefer hybrid but I'm fully comfortable working remotely.",
                        'evaluation': {'score': 'Excellent', 'feedback': 'Direct and flexible on role type, pay and location.'},
                    },
                ],
            },
        ]

        for spec in demo_interviews:
            job = Job.query.filter_by(role=spec['role'], recruiter_id=recruiter.id).first()
            if job is None:
                continue
            existing = Application.query.filter_by(user_id=applicant.id, job_id=job.id).first()
            if existing is not None:
                continue
            db.session.add(Application(
                job_id=job.id,
                user_id=applicant.id,
                name=applicant.username,
                email=applicant.email,
                status='interviewed',
                extracted_features=json.dumps({
                    'skills': spec['skills'],
                    'experience': spec['experience'],
                    'education': spec['education'],
                }),
                interview_log=json.dumps(spec['qa_log'], ensure_ascii=False),
            ))
        db.session.commit()
    except Exception as exc:
        # Never let seeding break startup.
        db.session.rollback()
        print(f"Demo data seeding skipped due to error: {exc}")