app.py

from flask import Flask, render_template, request, redirect, url_for, session
import pandas as pd
import sqlite3
import json
import os

# =========================
# SETUP
# =========================
app = Flask(__name__)
app.secret_key = "supersecret"

DB_PATH = os.path.join(os.path.dirname(__file__), "beyond.db")

def get_db_connection():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

# =========================
# LOAD DATA
# =========================
def load_sector_data():
    base = os.path.dirname(__file__)
    path = os.path.join(base, "data", "sector_jobs.json")
    with open(path, "r", encoding="utf-8") as f:
        return json.load(f)

sector_data = load_sector_data()

# Load jobs.csv
try:
    jobs = pd.read_csv(os.path.join(os.path.dirname(__file__), "data", "jobs.csv"))
except FileNotFoundError:
    jobs = pd.DataFrame(columns=["title", "description"])  # fallback

# =========================
# CAREER TEST QUESTIONS (Unified System)
# =========================
QUESTIONS = [
    {"id":"openness_1","text":"I enjoy exploring new ideas and theories.","type":"personality"},
    {"id":"openness_2","text":"I like learning about different cultures and perspectives.","type":"personality"},
    {"id":"conscientious_1","text":"I prefer planning tasks rather than improvising.","type":"personality"},
    {"id":"conscientious_2","text":"I finish what I start even when it’s boring.","type":"personality"},
    {"id":"extraversion_1","text":"I gain energy from working with other people.","type":"personality"},
    {"id":"extraversion_2","text":"I enjoy speaking in front of groups.","type":"personality"},
    {"id":"agreeableness_1","text":"I like helping others even if it takes extra effort.","type":"personality"},
    {"id":"agreeableness_2","text":"I avoid conflict and try to keep group harmony.","type":"personality"},
    {"id":"neuroticism_1","text":"I feel anxious or stressed in uncertain situations.","type":"personality"},
    {"id":"neuroticism_2","text":"I find it difficult to stay calm under pressure.","type":"personality"},

    {"id":"numeric_ability","text":"I am comfortable working with numbers and data.","type":"cognitive"},
    {"id":"verbal_ability","text":"I express ideas clearly in writing or speech.","type":"cognitive"},
    {"id":"manual_dexterity","text":"I enjoy tasks that require precise hand skills or tools.","type":"cognitive"},
    {"id":"technical_skill","text":"I enjoy learning and using technical systems or software.","type":"cognitive"},
    {"id":"analytical_thinking","text":"I enjoy solving complex logical problems.","type":"cognitive"},
    {"id":"creativity","text":"I like creating original work or solving problems in new ways.","type":"cognitive"},
    {"id":"leadership_interest","text":"I enjoy organising and leading groups or projects.","type":"cognitive"},

    {"id":"people_work","text":"I prefer work that involves interacting with people regularly.","type":"preference"},
    {"id":"helping_people","text":"I want a job where I can help improve people's lives.","type":"preference"},
    {"id":"stability_preference","text":"I prefer predictable, stable work schedules.","type":"preference"},
    {"id":"outdoor_preference","text":"I enjoy working outside or in varied environments.","type":"preference"},
    {"id":"research_interest","text":"I like doing research or scientific investigations.","type":"preference"},
    {"id":"creative_interest","text":"I want work that lets me express myself creatively.","type":"preference"},
    {"id":"business_interest","text":"I am interested in finance, markets or business operations.","type":"preference"},
    {"id":"teaching_interest","text":"I am interested in teaching or mentoring others.","type":"preference"},
    {"id":"noise_tolerance","text":"I am comfortable working in noisy, busy environments.","type":"preference"},
    {"id":"team_vs_alone","text":"I prefer working as part of a team rather than alone.","type":"preference"},
    {"id":"shift_work_ok","text":"I am okay with working non-traditional hours / shifts.","type":"preference"},
    {"id":"physical_demand","text":"I am comfortable doing physically demanding tasks.","type":"preference"},
    {"id":"remote_ok","text":"I prefer jobs that can be done remotely at least part-time.","type":"preference"},
    {"id":"travel_ok","text":"I am willing to travel frequently for work.","type":"preference"},
    {"id":"money_motivation","text":"High salary is a major factor when choosing a career.","type":"preference"},
    {"id":"social_impact","text":"Having a positive social impact is essential for me.","type":"preference"},
    {"id":"work_life_balance","text":"Work-life balance is more important than rapid career growth.","type":"preference"},
    {"id":"professional_growth","text":"I want continuous opportunities to develop professionally.","type":"preference"},
    {"id":"job_security","text":"Job security and benefits are very important to me.","type":"preference"}
]

# Trait mapping
TRAIT_QUESTIONS = {
    "openness": ["openness_1","openness_2"],
    "conscientiousness": ["conscientious_1","conscientious_2"],
    "extraversion": ["extraversion_1","extraversion_2"],
    "agreeableness": ["agreeableness_1","agreeableness_2"],
    "neuroticism": ["neuroticism_1","neuroticism_2"],
    "numeric_ability": ["numeric_ability"],
    "verbal_ability": ["verbal_ability"],
    "manual_dexterity": ["manual_dexterity"],
    "technical_skill": ["technical_skill"],
    "analytical_thinking": ["analytical_thinking"],
    "creativity": ["creativity"],
    "leadership_interest": ["leadership_interest"],
    "people_work": ["people_work"],
    "helping_people": ["helping_people"],
    "stability_preference": ["stability_preference"],
    "outdoor_preference": ["outdoor_preference"],
    "research_interest": ["research_interest"],
    "creative_interest": ["creative_interest"],
    "business_interest": ["business_interest"],
    "teaching_interest": ["teaching_interest"],
    "noise_tolerance": ["noise_tolerance"],
    "team_vs_alone": ["team_vs_alone"],
    "shift_work_ok": ["shift_work_ok"],
    "physical_demand": ["physical_demand"],
    "remote_ok": ["remote_ok"],
    "travel_ok": ["travel_ok"],
    "money_motivation": ["money_motivation"],
    "social_impact": ["social_impact"],
    "work_life_balance": ["work_life_balance"],
    "professional_growth": ["professional_growth"],
    "job_security": ["job_security"]
}

# Sector weights
SECTOR_PROFILES = {
    "technology": {
        "technical_skill": 1.2, "analytical_thinking": 1.1, "numeric_ability": 0.9,
        "creativity": 0.5, "remote_ok": 0.6, "professional_growth": 0.6
    },
    "healthcare": {
        "helping_people": 1.3, "people_work": 0.9, "stability_preference": 0.7,
        "research_interest": 0.6, "shift_work_ok": 0.5, "professional_growth": 0.5
    },
    "education": {
        "teaching_interest": 1.3, "openness": 0.7, "people_work": 0.9,
        "social_impact": 0.7, "professional_growth": 0.5
    },
    "engineering_and_manufacturing": {
        "technical_skill": 1.2, "manual_dexterity": 1.0, "analytical_thinking": 1.0,
        "physical_demand": 0.6, "stability_preference": 0.6
    },
    "finance_and_business": {
        "numeric_ability": 1.1, "business_interest": 1.2, "leadership_interest": 0.8,
        "money_motivation": 1.0, "remote_ok": 0.4
    },
    "arts_and_media": {
        "creativity": 1.3, "verbal_ability": 0.8, "openness": 0.7, "remote_ok": 0.6
    },
    "agriculture": {
        "outdoor_preference": 1.3, "physical_demand": 1.0, "manual_dexterity": 0.8,
        "stability_preference": 0.6
    },
    "public_and_service": {
        "people_work": 1.0, "social_impact": 1.0, "job_security": 0.8, "leadership_interest": 0.6
    },
    "transportation_and_logistics": {
        "physical_demand": 1.0, "travel_ok": 0.9, "stability_preference": 0.6, "team_vs_alone": 0.4
    },
    "hospitality_and_tourism": {
        "people_work": 1.0, "travel_ok": 0.8, "shift_work_ok": 0.7
    }
}

# =========================
# SCORING HELPERS
# =========================
def compute_trait_scores(answers):
    trait_scores = {}
    for trait, qlist in TRAIT_QUESTIONS.items():
        vals = [int(answers.get(q, 3)) for q in qlist]
        avg = sum(vals) / len(vals)
        trait_scores[trait] = (avg - 1) / 4.0  # normalize
    return trait_scores

def compute_sector_scores(trait_scores):
    sector_scores = {}
    for sector, weights in SECTOR_PROFILES.items():
        s = 0.0
        total_w = 0.0
        for trait, w in weights.items():
            trait_val = trait_scores.get(trait, 0.0)
            s += trait_val * w
            total_w += abs(w)
        sector_scores[sector] = s / (total_w or 1)
    return sector_scores

# =========================
# ROUTES
# =========================
@app.route("/")
def home():
    return render_template("home.html")
# ---------- AUTHENTICATION ----------
@app.route("/login", methods=["GET", "POST"])
def login():
    if request.method == "POST":
        username = request.form.get("username").strip()
        password = request.form.get("password").strip()

        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("SELECT username, password FROM users WHERE username = ?", (username,))
        user = cursor.fetchone()
        conn.close()

        if user and user["password"] == password:
            session["user"] = username
            return redirect(url_for("test"))
        else:
            error = "Invalid username or password."
            return render_template("login.html", error=error)

    return render_template("login.html")


@app.route("/register", methods=["GET", "POST"])
def register():
    if request.method == "POST":
        username = request.form.get("username").strip()
        password = request.form.get("password").strip()

        conn = get_db_connection()
        cursor = conn.cursor()
        try:
            cursor.execute("INSERT INTO users (username, password) VALUES (?, ?)", (username, password))
            conn.commit()
            session["user"] = username
            return redirect(url_for("test"))
        except sqlite3.IntegrityError:
            error = "Username already exists."
            return render_template("register.html", error=error)
        finally:
            conn.close()

    return render_template("register.html")


@app.route("/logout")
def logout():
    session.pop("user", None)
    return redirect(url_for("home"))

@app.route("/test", methods=["GET", "POST"])
def test():
    if request.method == "POST":
        answers = {k: int(v) for k, v in request.form.items()}
        session["answers"] = answers

        trait_scores = compute_trait_scores(answers)
        sector_scores = compute_sector_scores(trait_scores)

        session["results"] = {"traits": trait_scores, "sectors": sector_scores}

        # Save to DB
        if "user" in session:
            conn = get_db_connection()
            cursor = conn.cursor()
            cursor.execute(
                "INSERT INTO results (username, result_json) VALUES (?, ?)",
                (session["user"], json.dumps(session["results"]))
            )
            conn.commit()
            conn.close()

        return redirect(url_for("results"))

    personality = [q for q in QUESTIONS if q["type"] == "personality"]
    cognitive = [q for q in QUESTIONS if q["type"] == "cognitive"]
    preference = [q for q in QUESTIONS if q["type"] == "preference"]

    return render_template("test.html", personality=personality, cognitive=cognitive, preference=preference)

@app.route("/results")
def results():
    if "results" not in session:
        return redirect(url_for("test"))

    trait_scores = session["results"]["traits"]
    sector_scores = session["results"]["sectors"]

    # Get top 3 sectors
    top_sectors = sorted(sector_scores.items(), key=lambda x: x[1], reverse=True)[:3]

    # Load job data
    job_data_path = os.path.join(os.path.dirname(__file__), "data", "sector_jobs.json")
    with open(job_data_path, "r", encoding="utf-8") as f:
        job_data = json.load(f)

    matched_jobs = {}
    for sector, score in top_sectors:
        if sector in job_data:
            matched_jobs[sector] = {
                "popular": job_data[sector].get("popular_jobs", []),
                "hidden": job_data[sector].get("high_demand_jobs", [])
            }

    return render_template(
        "results.html",
        trait_scores=trait_scores,
        top_sectors=top_sectors,
        matched_jobs=matched_jobs
    )


@app.route("/my_results")
def my_results():
    if "user" not in session:
        return redirect(url_for("login"))

    conn = get_db_connection()
    cursor = conn.cursor()

    # Try to fetch both result_json and timestamp (new DBs)
    try:
        cursor.execute(
            "SELECT result_json, timestamp FROM results WHERE username = ? ORDER BY timestamp DESC",
            (session["user"],)
        )
        rows = cursor.fetchall()
    except sqlite3.OperationalError:
        # Fallback for old DBs without timestamp
        cursor.execute(
            "SELECT result_json FROM results WHERE username = ? ORDER BY ROWID DESC",
            (session["user"],)
        )
        rows = cursor.fetchall()

    conn.close()

    user_results = []

    for row in rows:
        try:
            # Handle both tuple-based and dict-based rows
            if isinstance(row, (list, tuple)):
                result_json = row[0]
                timestamp = row[1] if len(row) > 1 else None
            elif isinstance(row, dict) or isinstance(row, sqlite3.Row):
                result_json = row.get("result_json")
                timestamp = row.get("timestamp")
            else:
                # Skip unknown formats
                continue

            # Attempt to parse JSON safely
            data = json.loads(result_json) if result_json else None

            # Only add valid data
            if isinstance(data, (dict, list)) and len(data) > 0:
                user_results.append({
                    "data": data,
                    "time": timestamp
                })

        except Exception as e:
            print(f" Skipping broken result: {e}")
            continue

    return render_template("my_results.html", user_results=user_results)
@app.route("/cleanup_results")
def cleanup_results():
    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute("SELECT rowid, result_json FROM results")
    rows = cursor.fetchall()

    deleted = 0
    for row in rows:
        try:
            json.loads(row["result_json"])
        except Exception:
            cursor.execute("DELETE FROM results WHERE rowid = ?", (row["rowid"],))
            deleted += 1

    conn.commit()
    conn.close()
    return f" Cleaned up {deleted} broken records."


@app.route("/sectors")
def show_all_sectors():
    with open(os.path.join("data", "sector_jobs.json"), "r", encoding="utf-8") as f:
        sectors = json.load(f)
    return render_template("sectors.html", sectors=sectors)

if __name__ == "__main__":
    app.run(debug=True)