app.py

from pathlib import Path
import sqlite3

import click
from flask import Flask, flash, g, redirect, render_template, request, url_for


BASE_DIR = Path(__file__).resolve().parent
STATUS_CHOICES = ("saved", "applied", "interview", "offer", "rejected", "archived")
WORK_MODE_CHOICES = ("hybrid", "remote", "onsite")

app = Flask(__name__)
app.config.from_mapping(
    SECRET_KEY="dev",
    DATABASE=Path(app.instance_path) / "datapath.db",
)


def get_db():
    if "db" not in g:
        Path(app.instance_path).mkdir(parents=True, exist_ok=True)
        g.db = sqlite3.connect(app.config["DATABASE"])
        g.db.row_factory = sqlite3.Row
        g.db.execute("PRAGMA foreign_keys = ON")
    return g.db


def close_db(error=None):
    db = g.pop("db", None)
    if db is not None:
        db.close()


def run_script(db, filename):
    script = (BASE_DIR / filename).read_text(encoding="utf-8")
    db.executescript(script)


def init_database(seed=True):
    db_path = Path(app.config["DATABASE"])
    db_path.parent.mkdir(parents=True, exist_ok=True)
    if db_path.exists():
        db_path.unlink()

    db = sqlite3.connect(db_path)
    db.execute("PRAGMA foreign_keys = ON")
    run_script(db, "schema.sql")
    if seed:
        run_script(db, "seed.sql")
    db.close()


@app.before_request
def ensure_database():
    if request.endpoint != "static" and not Path(app.config["DATABASE"]).exists():
        init_database()


@app.teardown_appcontext
def teardown_db(error=None):
    close_db(error)


@app.cli.command("init-db")
@click.option("--empty", is_flag=True, help="Create the schema without seed data.")
def init_db_command(empty):
    init_database(seed=not empty)
    click.echo("Initialized the DataPath database.")


def query_all(sql, params=()):
    return get_db().execute(sql, params).fetchall()


def query_one(sql, params=()):
    return get_db().execute(sql, params).fetchone()


def company_id_for(name, industry, website):
    db = get_db()
    existing = db.execute(
        'SELECT "id" FROM "companies" WHERE lower("name") = lower(?)',
        (name,),
    ).fetchone()
    if existing:
        if website:
            db.execute(
                'UPDATE "companies" SET "industry" = ?, "website" = COALESCE("website", ?) WHERE "id" = ?',
                (industry, website, existing["id"]),
            )
        return existing["id"]

    cursor = db.execute(
        'INSERT INTO "companies" ("name", "industry", "website") VALUES (?, ?, ?)',
        (name, industry, website or None),
    )
    return cursor.lastrowid


def select_options():
    return {
        "roles": query_all('SELECT "id", "title" FROM "roles" ORDER BY "title"'),
        "statuses": STATUS_CHOICES,
        "work_modes": WORK_MODE_CHOICES,
    }


@app.route("/")
def dashboard():
    stats = query_one(
        """
        SELECT
            COUNT(*) AS job_count,
            SUM(CASE WHEN "status" IN ('saved', 'applied', 'interview') THEN 1 ELSE 0 END) AS active_jobs,
            SUM(CASE WHEN "status" = 'interview' THEN 1 ELSE 0 END) AS interviews
        FROM "job_postings"
        """
    )
    skill_stats = query_one(
        """
        SELECT
            COUNT(*) AS skill_count,
            SUM(CASE WHEN "acquired" = 1 THEN 1 ELSE 0 END) AS acquired_count
        FROM "skills"
        """
    )
    best_role = query_one('SELECT * FROM "best_fit_roles" LIMIT 1')
    pipeline = query_all(
        """
        SELECT "status", "posting_count"
        FROM "application_pipeline"
        ORDER BY
            CASE "status"
                WHEN 'saved' THEN 1
                WHEN 'applied' THEN 2
                WHEN 'interview' THEN 3
                WHEN 'offer' THEN 4
                WHEN 'rejected' THEN 5
                ELSE 6
            END
        """
    )
    saved_jobs = query_all(
        """
        SELECT *
        FROM "open_job_postings"
        ORDER BY "application_deadline" IS NULL, "application_deadline", "company"
        LIMIT 5
        """
    )
    gaps = query_all(
        """
        SELECT "role", "skill", "category"
        FROM "missing_high_priority_skills"
        ORDER BY "role", "skill"
        LIMIT 8
        """
    )
    return render_template(
        "dashboard.html",
        stats=stats,
        skill_stats=skill_stats,
        best_role=best_role,
        pipeline=pipeline,
        saved_jobs=saved_jobs,
        gaps=gaps,
    )


@app.route("/jobs")
def jobs():
    status = request.args.get("status", "")
    role_id = request.args.get("role_id", "")
    search = request.args.get("q", "").strip()

    conditions = []
    params = []

    if status:
        conditions.append('"job_postings"."status" = ?')
        params.append(status)
    if role_id:
        conditions.append('"job_postings"."role_id" = ?')
        params.append(role_id)
    if search:
        conditions.append(
            """
            ("companies"."name" LIKE ?
             OR "roles"."title" LIKE ?
             OR "job_postings"."title" LIKE ?
             OR "job_postings"."location" LIKE ?)
            """
        )
        params.extend([f"%{search}%"] * 4)

    where_clause = "WHERE " + " AND ".join(conditions) if conditions else ""
    rows = query_all(
        f"""
        SELECT
            "job_postings".*,
            "companies"."name" AS "company",
            "roles"."title" AS "role"
        FROM "job_postings"
        JOIN "companies" ON "companies"."id" = "job_postings"."company_id"
        JOIN "roles" ON "roles"."id" = "job_postings"."role_id"
        {where_clause}
        ORDER BY
            CASE "job_postings"."status"
                WHEN 'saved' THEN 1
                WHEN 'applied' THEN 2
                WHEN 'interview' THEN 3
                WHEN 'offer' THEN 4
                WHEN 'rejected' THEN 5
                ELSE 6
            END,
            "job_postings"."application_deadline" IS NULL,
            "job_postings"."application_deadline",
            "companies"."name"
        """,
        params,
    )
    return render_template(
        "jobs.html",
        jobs=rows,
        filters={"status": status, "role_id": role_id, "q": search},
        **select_options(),
    )


@app.route("/jobs/new", methods=("GET", "POST"))
def new_job():
    if request.method == "POST":
        return save_job()
    return render_template("job_form.html", job=None, **select_options())


@app.route("/jobs/<int:job_id>/edit", methods=("GET", "POST"))
def edit_job(job_id):
    job = query_one(
        """
        SELECT
            "job_postings".*,
            "companies"."name" AS "company_name",
            "companies"."industry" AS "industry",
            "companies"."website" AS "company_website"
        FROM "job_postings"
        JOIN "companies" ON "companies"."id" = "job_postings"."company_id"
        WHERE "job_postings"."id" = ?
        """,
        (job_id,),
    )
    if job is None:
        flash("Job posting not found.", "error")
        return redirect(url_for("jobs"))

    if request.method == "POST":
        return save_job(job_id)

    return render_template("job_form.html", job=job, **select_options())


@app.route("/jobs/<int:job_id>/delete", methods=("POST",))
def delete_job(job_id):
    get_db().execute('DELETE FROM "job_postings" WHERE "id" = ?', (job_id,))
    get_db().commit()
    flash("Job posting deleted.", "success")
    return redirect(url_for("jobs"))


def save_job(job_id=None):
    form = request.form
    company_name = form.get("company_name", "").strip()
    industry = form.get("industry", "").strip()
    role_id = form.get("role_id", "").strip()
    title = form.get("title", "").strip()
    location = form.get("location", "").strip()
    status = form.get("status", "saved")
    work_mode = form.get("work_mode", "hybrid")

    if not company_name or not industry or not role_id or not title or not location:
        flash("Company, industry, role, title, and location are required.", "error")
        endpoint = "edit_job" if job_id else "new_job"
        return redirect(url_for(endpoint, job_id=job_id) if job_id else url_for(endpoint))

    if status not in STATUS_CHOICES or work_mode not in WORK_MODE_CHOICES:
        flash("Invalid status or work mode.", "error")
        return redirect(url_for("jobs"))

    db = get_db()
    company_id = company_id_for(
        company_name,
        industry,
        form.get("company_website", "").strip(),
    )
    values = (
        company_id,
        int(role_id),
        title,
        location,
        work_mode,
        form.get("posting_url", "").strip() or None,
        form.get("application_deadline", "").strip() or None,
        form.get("starting_date", "").strip() or None,
        form.get("salary_range", "").strip() or None,
        status,
        form.get("date_posted", "").strip() or None,
        form.get("date_applied", "").strip() or None,
        form.get("notes", "").strip() or None,
    )

    try:
        if job_id:
            db.execute(
                """
                UPDATE "job_postings"
                SET
                    "company_id" = ?,
                    "role_id" = ?,
                    "title" = ?,
                    "location" = ?,
                    "work_mode" = ?,
                    "posting_url" = ?,
                    "application_deadline" = ?,
                    "starting_date" = ?,
                    "salary_range" = ?,
                    "status" = ?,
                    "date_posted" = ?,
                    "date_applied" = ?,
                    "notes" = ?
                WHERE "id" = ?
                """,
                values + (job_id,),
            )
            flash("Job posting updated.", "success")
        else:
            db.execute(
                """
                INSERT INTO "job_postings"
                    ("company_id", "role_id", "title", "location", "work_mode", "posting_url",
                     "application_deadline", "starting_date", "salary_range", "status",
                     "date_posted", "date_applied", "notes")
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """,
                values,
            )
            flash("Job posting added.", "success")
        db.commit()
    except sqlite3.IntegrityError as error:
        db.rollback()
        flash(f"Could not save job posting: {error}", "error")
        endpoint = "edit_job" if job_id else "new_job"
        return redirect(url_for(endpoint, job_id=job_id) if job_id else url_for(endpoint))

    return redirect(url_for("jobs"))


@app.route("/skills")
def skills():
    rows = query_all(
        """
        SELECT *
        FROM "skills"
        ORDER BY "category", "name"
        """
    )
    return render_template("skills.html", skills=rows)


@app.route("/skills/<int:skill_id>/toggle", methods=("POST",))
def toggle_skill(skill_id):
    get_db().execute(
        """
        UPDATE "skills"
        SET "acquired" = CASE WHEN "acquired" = 1 THEN 0 ELSE 1 END
        WHERE "id" = ?
        """,
        (skill_id,),
    )
    get_db().commit()
    flash("Skill status updated.", "success")
    return redirect(url_for("skills"))


@app.route("/roles")
def roles():
    progress = query_all(
        """
        SELECT *
        FROM "role_skill_progress"
        ORDER BY "skill_progress_percent" DESC, "role"
        """
    )
    best_fit = query_all('SELECT * FROM "best_fit_roles"')
    gaps = query_all(
        """
        SELECT *
        FROM "missing_high_priority_skills"
        ORDER BY "role", "skill"
        """
    )
    return render_template("roles.html", progress=progress, best_fit=best_fit, gaps=gaps)


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