database.py

import sqlite3
import os
from datetime import date

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


def init_db():
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute("""
        CREATE TABLE IF NOT EXISTS jobs (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            job_title   TEXT    NOT NULL,
            company     TEXT    NOT NULL,
            location    TEXT,
            date_posted TEXT,
            apply_url   TEXT,
            source_site TEXT,
            easy_apply  INTEGER DEFAULT 0,
            first_seen  DATE    NOT NULL,
            UNIQUE(company, job_title)
        )
    """)
    conn.commit()
    conn.close()


def insert_jobs(jobs):
    """Insert a list of job dicts. Returns only the newly inserted ones."""
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    today = date.today().isoformat()
    new_jobs = []

    for job in jobs:
        try:
            c.execute(
                """
                INSERT INTO jobs
                    (job_title, company, location, date_posted, apply_url, source_site, easy_apply, first_seen)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                """,
                (
                    job.get("job_title", "").strip(),
                    job.get("company", "").strip(),
                    job.get("location", "").strip(),
                    job.get("date_posted", "").strip(),
                    job.get("apply_url", "").strip(),
                    job.get("source_site", "").strip(),
                    1 if job.get("easy_apply") else 0,
                    today,
                ),
            )
            new_jobs.append(job)
        except sqlite3.IntegrityError:
            pass  # Already seen before — skip

    conn.commit()
    conn.close()
    return new_jobs