AgenticControl/personeldata.py

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}")