main.py

from fastapi import FastAPI, Request, Response, HTTPException
from fastapi.middleware.cors import CORSMiddleware
from pydantic import BaseModel
import databases
import sqlalchemy
from sqlalchemy.sql import select, and_
from fuzzywuzzy import process
import urllib.parse
import os
from dotenv import load_dotenv
import json
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, DateTime, Boolean, Text, JSON
import time
from datetime import datetime

load_dotenv()

class JobQuery(BaseModel):
    budget: str
    experience: str
    startDate: str
    workType: str
    semanticSearchString: str

username = urllib.parse.quote_plus('trial_user')
password = urllib.parse.quote_plus('trial_user_12345#')
host = '35.224.61.48'
port = '3306'
database_name = 'MERCOR_TRIAL_SCHEMA'

DATABASE_URL = f"mysql://{username}:{password}@{host}:{port}/{database_name}"
database = databases.Database(DATABASE_URL)

metadata = sqlalchemy.MetaData()

skills = sqlalchemy.Table(
    "Skills",
    metadata,
    sqlalchemy.Column("skillId", sqlalchemy.String, primary_key=True),
    sqlalchemy.Column("skillName", sqlalchemy.String),
    sqlalchemy.Column("skillValue", sqlalchemy.String, unique=True),
)

jobs = sqlalchemy.Table(
    "MercorUsers",
    metadata,
    sqlalchemy.Column("userId", sqlalchemy.String, primary_key=True),
    sqlalchemy.Column("email", sqlalchemy.String, unique=True),
    sqlalchemy.Column("name", sqlalchemy.String),
    sqlalchemy.Column("phone", sqlalchemy.String),
    sqlalchemy.Column("residence", JSON),
    sqlalchemy.Column("profilePic", Text),
    sqlalchemy.Column("createdAt", DateTime),
    sqlalchemy.Column("lastLogin", DateTime),
    sqlalchemy.Column("notes", Text),
    sqlalchemy.Column("referralCode", sqlalchemy.String, unique=True),
    sqlalchemy.Column("isGptEnabled", sqlalchemy.Boolean),
    sqlalchemy.Column("preferredRole", sqlalchemy.String),
    sqlalchemy.Column("fullTimeStatus", sqlalchemy.String),
    sqlalchemy.Column("workAvailability", sqlalchemy.String),
    sqlalchemy.Column("fullTimeSalaryCurrency", sqlalchemy.String),
    sqlalchemy.Column("fullTimeSalary", sqlalchemy.String),
    sqlalchemy.Column("partTimeSalaryCurrency", sqlalchemy.String),
    sqlalchemy.Column("partTimeSalary", sqlalchemy.String),
    sqlalchemy.Column("fullTime", sqlalchemy.Boolean),
    sqlalchemy.Column("fullTimeAvailability", sqlalchemy.Integer),
    sqlalchemy.Column("partTime", sqlalchemy.Boolean),
    sqlalchemy.Column("partTimeAvailability", sqlalchemy.Integer),
    sqlalchemy.Column("w8BenUrl", JSON),
    sqlalchemy.Column("tosUrl", Text),
    sqlalchemy.Column("policyUrls", JSON),
    sqlalchemy.Column("isPreVetted", sqlalchemy.Boolean),
    sqlalchemy.Column("isActive", sqlalchemy.Boolean),
    sqlalchemy.Column("isComplete", sqlalchemy.Boolean),
    sqlalchemy.Column("summary", Text),
    sqlalchemy.Column("preVettedAt", DateTime)
)

MercorUserSkills = sqlalchemy.Table(
    "MercorUserSkills",
    metadata,
    sqlalchemy.Column("userId", sqlalchemy.String, sqlalchemy.ForeignKey("MercorUsers.userId"), primary_key=True),
    sqlalchemy.Column("skillId", sqlalchemy.String, sqlalchemy.ForeignKey("Skills.skillId"), primary_key=True),
    sqlalchemy.Column("isPrimary", sqlalchemy.Boolean, default=False),
    sqlalchemy.Column("order", Integer, default=0)
)

UserResume = sqlalchemy.Table(
    "UserResume",
    metadata,
    sqlalchemy.Column("resumeId", sqlalchemy.String, primary_key=True),
    sqlalchemy.Column("userId", sqlalchemy.String, sqlalchemy.ForeignKey("MercorUsers.userId")),
    sqlalchemy.Column("url", Text),
    sqlalchemy.Column("filename", String),
    sqlalchemy.Column("createdAt", DateTime),
    sqlalchemy.Column("updatedAt", DateTime),
    sqlalchemy.Column("source", String),
    sqlalchemy.Column("ocrText", Text),
    sqlalchemy.Column("ocrEmail", String),
    sqlalchemy.Column("ocrGithubUsername", String),
    sqlalchemy.Column("resumeBasedQuestions", Text),
    sqlalchemy.Column("isInvitedToInterview", Boolean),
    sqlalchemy.Column("reminderTasksIds", JSON)
)

WorkExperience = sqlalchemy.Table(
    "WorkExperience",
    metadata,
    sqlalchemy.Column("workExperienceId", sqlalchemy.String, primary_key=True),
    sqlalchemy.Column("company", String),
    sqlalchemy.Column("role", String),
    sqlalchemy.Column("startDate", String),
    sqlalchemy.Column("endDate", String),
    sqlalchemy.Column("description", Text),
    sqlalchemy.Column("locationCity", String),
    sqlalchemy.Column("locationCountry", String),
    sqlalchemy.Column("resumeId", String, sqlalchemy.ForeignKey("UserResume.resumeId"))
)

app = FastAPI()

app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],  # This allows all domains
    allow_methods=["*"],  # This allows all methods
    allow_headers=["*"],  # This allows all headers
)

@app.options("/{rest_of_path:path}")
async def preflight_handler(request: Request, rest_of_path: str) -> Response:
    return Response(headers={
        "Access-Control-Allow-Origin": "*",  # Allows all origins
        "Access-Control-Allow-Methods": "POST, GET, OPTIONS, PUT, DELETE",  # Specify methods allowed
        "Access-Control-Allow-Headers": "*",  # Allows all headers
    })


    
@app.on_event("startup")
async def startup():
    await database.connect()

@app.on_event("shutdown")
async def shutdown():
    await database.disconnect()

@app.post("/query-jobs/")
async def query_jobs(query: JobQuery):
    start_time = time.time()  # Start timing
    
    # Fetch and match skills
    all_skills = await database.fetch_all(select(skills.c.skillId, skills.c.skillName))
    skills_dict = {skill['skillName']: skill['skillId'] for skill in all_skills}
    matching_skills = process.extract(query.semanticSearchString, skills_dict.keys(), limit=10)
    matching_skill_ids = [skills_dict[skill[0]] for skill in matching_skills if skill[1] > 70]

    if not matching_skill_ids:
        return {"message": "No skills matched your query.", "query": query.semanticSearchString}

    user_skill_query = select(MercorUserSkills.c.userId).where(MercorUserSkills.c.skillId.in_(matching_skill_ids)).distinct()
    user_ids = await database.fetch_all(user_skill_query)
    user_ids = [user['userId'] for user in user_ids]

    if not user_ids:
        return {"message": "No users found with the matching skills."}

    user_query = (
        select(jobs)
        .where(jobs.c.userId.in_(user_ids), jobs.c.fullTimeSalary <= int(query.budget))
        .order_by(jobs.c.fullTimeSalary.desc())  # Sorting by fullTimeSalary descending
        .limit(4)  # Limiting to 4 results
    )
    users_with_skills = await database.fetch_all(user_query)

    result = []
    for user in users_with_skills:
        user_skills_query = select(skills.c.skillName).join(MercorUserSkills, skills.c.skillId == MercorUserSkills.c.skillId).where(MercorUserSkills.c.userId == user['userId'])
        user_skills = await database.fetch_all(user_skills_query)
        user_skills_list = [skill['skillName'] for skill in user_skills]

        # Fetch resume ID for user
        resume_query = select(UserResume.c.resumeId).where(UserResume.c.userId == user['userId'])
        resume_id = await database.fetch_one(resume_query)

        # Fetch and process work experience
        experience_query = select(WorkExperience).where(WorkExperience.c.resumeId == resume_id['resumeId'])
        experiences = await database.fetch_all(experience_query)
        companies = set()
        total_experience = 0

        for exp in experiences:
            companies.add(exp['company'])
            # Calculate duration
            start_date = datetime.strptime(exp['startDate'], '%Y') if exp['startDate'] else None
            end_date = datetime.strptime(exp['endDate'], '%Y') if exp['endDate'] else datetime.now()
            if start_date:
                duration_years = (end_date.year - start_date.year)
                total_experience += duration_years

        experiences_list = [{'company': exp['company'], 'role': exp['role'], 'startDate': exp['startDate'], 'endDate': exp['endDate']} for exp in experiences]

        result.append({
            "user_id": user['userId'],
            "name": user['name'],
            "email": user['email'],
            "skills": user_skills_list,
            "full_time_salary": user['fullTimeSalary'],
            "companies": list(companies),
            "total_experience_years": total_experience
        })

    execution_time = time.time() - start_time  # Calculate execution time

    return {"users": result, "execution_time": execution_time}