File size: 8,271 Bytes
d712cef | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 | import sqlite3
import os
import json
from datetime import datetime
# NOTE: ResumeProcessor is imported lazily inside save_user_profile()
# so that a missing dependency (e.g. PyMuPDF) never blocks the DB save.
# ── Configuration ────────────────────────────────────────────────────────
# You can change this path later. The code will automatically create it.
ROOT_DIR = os.environ.get('WORKSPACE_ROOT', '.')
PERSONNEL_FOLDER = os.path.join(ROOT_DIR, 'Database/personnel_data')
DB_PATH = os.path.join(PERSONNEL_FOLDER, 'personnelDetails.db')
# Ensure the personnel folder exists
os.makedirs(PERSONNEL_FOLDER, exist_ok=True)
# ── 1. Initialize Database ───────────────────────────────────────────────
def init_db():
"""Creates the SQL table if it doesn't exist."""
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
# We use 'email' as the PRIMARY KEY so we don't get duplicate users
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
email TEXT PRIMARY KEY,
name TEXT,
location TEXT,
github_description TEXT,
languages TEXT,
cv_file_path TEXT,
access_token TEXT,
refresh_token TEXT,
updated_at TEXT,
is_registered BOOLEAN
)
''')
conn.commit()
conn.close()
# Run initialization immediately when the file loads
init_db()
# ── 2. The Boolean Check Function ────────────────────────────────────────
def check_user_exists(email: str) -> bool:
"""
Checks if we already have this user's information in our database.
Returns True if they exist, False otherwise.
"""
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
cursor.execute("SELECT is_registered FROM users WHERE email = ?", (email,))
result = cursor.fetchone()
conn.close()
# SQLite stores Booleans as 1 or 0. If we found a result, return True.
if result and result[0] == 1:
return True
return False
# ── 3. The Main Save Function ────────────────────────────────────────────
def save_user_profile(
email: str,
name: str,
location: str,
github_description: str,
languages: list,
cv_file_bytes: bytes, # The actual file content sent from the frontend
cv_filename: str, # The name of the uploaded file (e.g., 'resume.pdf'))
access_token: str,
refresh_token: str
):
"""
Saves or updates the user profile and their Google tokens in the SQL database.
"""
# 1. Fetch the existing cv_file_path before overwriting
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
cursor.execute("SELECT cv_file_path FROM users WHERE email = ?", (email,))
row = cursor.fetchone()
old_cv_path = row[0] if row else None
conn.close()
# 2. Save the actual CV file into the personnel folder
cv_file_path = None
if cv_file_bytes and cv_filename:
# We add the email to the filename so files don't overwrite each other
safe_filename = f"{email.replace('@', '_at_')}_{cv_filename}"
cv_file_path = os.path.join(PERSONNEL_FOLDER, safe_filename)
# If an old CV exists and its name differs, delete the old file to prevent orphans
if old_cv_path and old_cv_path != cv_file_path and os.path.exists(old_cv_path):
try:
os.remove(old_cv_path)
print(f"🗑️ Deleted old CV: {old_cv_path}")
except Exception as e:
print(f"❌ Failed to delete old CV: {e}")
with open(cv_file_path, "wb") as f:
f.write(cv_file_bytes)
# 2. Prepare data for SQL
# SQL can't store Python lists directly, so we convert languages to a JSON string
languages_str = json.dumps(languages)
updated_at = datetime.utcnow().isoformat()
is_registered = True # The boolean flag you requested
# 3. Save to SQL Database using UPSERT
# "UPSERT" means it will INSERT a new row, but if the email already exists, it will UPDATE it.
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
cursor.execute('''
INSERT INTO users (
email, name, location, github_description, languages,
cv_file_path, access_token, refresh_token, updated_at, is_registered
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(email) DO UPDATE SET
name=excluded.name,
location=excluded.location,
github_description=excluded.github_description,
languages=excluded.languages,
cv_file_path=COALESCE(excluded.cv_file_path, users.cv_file_path),
access_token=excluded.access_token,
refresh_token=excluded.refresh_token,
updated_at=excluded.updated_at,
is_registered=excluded.is_registered
''', (
email, name, location, github_description, languages_str,
cv_file_path, access_token, refresh_token, updated_at, is_registered
))
conn.commit()
conn.close()
print(f"✅ User {email} successfully saved to SQL Database!")
# 4. If a new CV was uploaded, process it with the LLM in a background thread.
# This ensures save_user_profile returns immediately after the DB save,
# allowing the Node.js backend to respond to the frontend without waiting for the LLM.
if cv_file_path:
try:
import threading
from ResumeProcessor import process_resume # noqa: PLC0415
print(f"Processing resume for {email} via LLM...", flush=True)
def run_llm_task():
try:
process_resume(cv_file_path, email)
except Exception as e:
print(f"[WARNING] Background ResumeProcessor failed: {e}")
# Daemon thread will be killed when the main Node.js process dies,
# but while Node.js is running, this Python thread will continue.
# Wait, no... if save_user_cli.py exits, the daemon thread dies!
# Wait, since save_user_cli.py is what spawns this, if it exits, the thread dies.
# That's why we don't want save_user_cli to exit if the thread is running!
# Actually, to make it completely non-blocking for Node.js, save_user_cli.py MUST print
# the JSON response, flush it, and then WAIT for the thread.
# Or better yet: Node.js handles the async detach.
t = threading.Thread(target=run_llm_task)
t.daemon = False # Must NOT be daemon, so python doesn't exit until it finishes
t.start()
except ImportError as ie:
print(f"[WARNING] ResumeProcessor not available (missing dependency?): {ie}")
except Exception as e:
print(f"[WARNING] ResumeProcessor failed to start: {e}")
# ── Example Usage ────────────────────────────────────────────────────────
# if __name__ == "__main__":
# # 1. Checking if a user exists
# exists = check_user_exists("asadirfan7533@gmail.com")
# print(f"Does Asad exist? {exists}")
# # 2. Saving a new user
# save_user_profile(
# email="asadirfan7533@gmail.com",
# name="Asad Irfan",
# location="Topi, Pakistan",
# github_description="AI student building agentic tools",
# languages=["Python", "TypeScript", "C++"],
# cv_file_bytes=b"fake_pdf_data_bytes_here",
# cv_filename='Asad_CV.pdf'),
# access_token="ya29.a0AfB...",
# refresh_token="1//0eXYZ..."
# )
# 3. Checking again
# exists_now = check_user_exists("asadirfan939@gmail.com")
# print(f"Does Asad exist now? {exists_now}") |