spb/job-ka

1#!/usr/bin/env python32# =============================================================================3# Job·Ka — Groupe KA4# Auteur  : Simon-Pierre Boucher5# Contact : contact@spboucher.ai6# Fichier : scripts/mesure_baseline.py7# Rôle    : Mesure des métriques de complétude/qualité (mêmes définitions que8#           l'audit 03-BASELINE du 2026-08-19) — sert au « avant/après »9# Créé    : 2026-08-19   Modifié : 2026-08-1910# =============================================================================11"""Usage : .venv/bin/python scripts/mesure_baseline.py  (lecture seule)"""12from __future__ import annotations1314import datetime as dt15import json16import sqlite317from pathlib import Path1819DB = Path(__file__).resolve().parent.parent / "data" / "jobka.db"20con = sqlite3.connect(f"file:{DB}?mode=ro", uri=True)21con.row_factory = sqlite3.Row2223ACT = "active=1 AND dup_of IS NULL"          # actives canoniques (comme baseline)24PUB = ACT + " AND quarantine IS NULL"        # publiées (nouveau périmètre)252627def one(sql):28    return con.execute(sql).fetchone()[0]293031def rows(sql):32    return [tuple(r) for r in con.execute(sql).fetchall()]333435out = {}36out["total"] = one("SELECT COUNT(*) FROM jobs")37out["actives"] = one("SELECT COUNT(*) FROM jobs WHERE active=1")38out["actives_canoniques"] = one(f"SELECT COUNT(*) FROM jobs WHERE {ACT}")39out["publiees"] = one(f"SELECT COUNT(*) FROM jobs WHERE {PUB}")40out["quarantaine"] = one(41    "SELECT COUNT(*) FROM jobs WHERE active=1 AND quarantine IS NOT NULL")42out["quarantaine_motifs"] = rows(43    "SELECT quarantine, COUNT(*) FROM jobs WHERE active=1"44    " AND quarantine IS NOT NULL GROUP BY quarantine ORDER BY 2 DESC")45out["doublons_masques"] = one(46    "SELECT COUNT(*) FROM jobs WHERE active=1 AND dup_of IS NOT NULL")47out["expirees"] = one("SELECT COUNT(*) FROM jobs WHERE active=0")4849n = out["actives_canoniques"]50champ_conds = [51    ("url", "url<>''"),52    ("date_posted", "date_posted IS NOT NULL"),53    ("description_non_vide", "LENGTH(COALESCE(description,''))>0"),54    ("description_200", "LENGTH(COALESCE(description,''))>200"),55    ("city", "city<>''"),56    ("geo", "lat IS NOT NULL AND lng IS NOT NULL"),57    ("region_admin", "region<>'' AND region<>'Québec'"),58    ("region_toute", "region<>''"),59    ("category", "category<>''"),60    ("employment_type", "employment_type IS NOT NULL"),61    ("salary_min", "salary_min IS NOT NULL"),62    ("work_mode", "work_mode IS NOT NULL"),63    ("salary_label", "salary_label<>''"),64    ("postal_code", "postal_code<>''"),65    ("date_deadline", "date_deadline IS NOT NULL"),66    ("address", "address<>''"),67    ("requirements", "requirements NOT IN ('','{}')"),68    ("benefits", "benefits NOT IN ('','[]')"),69    ("language", "language IS NOT NULL AND language<>''"),70    ("company_logo", "company_logo IS NOT NULL AND company_logo<>''"),71    ("apply_url", "apply_url IS NOT NULL AND apply_url<>''"),72    ("title_clean", "title_clean IS NOT NULL AND title_clean<>''"),73]74out["completude"] = {}75for name, cond in champ_conds:76    c = one(f"SELECT COUNT(*) FROM jobs WHERE {ACT} AND {cond}")77    out["completude"][name] = (c, round(100.0 * c / n, 1) if n else 0)7879out["par_famille"] = rows(80    f"""SELECT ats, COUNT(*),81        ROUND(100.0*SUM(salary_min IS NOT NULL)/COUNT(*),1),82        ROUND(100.0*SUM(work_mode IS NOT NULL)/COUNT(*),1),83        ROUND(100.0*SUM(employment_type IS NOT NULL)/COUNT(*),1),84        ROUND(100.0*SUM(date_deadline IS NOT NULL)/COUNT(*),1),85        ROUND(100.0*SUM(lat IS NOT NULL)/COUNT(*),1),86        ROUND(100.0*SUM(LENGTH(COALESCE(description,''))>200)/COUNT(*),1),87        ROUND(100.0*SUM(date_posted IS NOT NULL)/COUNT(*),1),88        ROUND(100.0*SUM(language IS NOT NULL AND language<>'')/COUNT(*),1),89        ROUND(100.0*SUM(apply_url IS NOT NULL AND apply_url<>'')/COUNT(*),1),90        ROUND(100.0*SUM(company_logo IS NOT NULL AND company_logo<>'')/COUNT(*),1),91        ROUND(100.0*SUM(benefits NOT IN ('','[]'))/COUNT(*),1)92        FROM jobs WHERE {ACT} GROUP BY ats ORDER BY 2 DESC""")9394today = dt.date.today()95ages = [96    (today - dt.date.fromisoformat(r[0])).days97    for r in rows(f"SELECT date_posted FROM jobs WHERE {ACT}"98                  " AND date_posted IS NOT NULL")99    if r[0] <= today.isoformat()100]101ages.sort()102out["fraicheur"] = {103    "n": len(ages),104    "median": ages[len(ages) // 2] if ages else None,105    "moyen": round(sum(ages) / len(ages), 1) if ages else None,106    "gt60": sum(1 for a in ages if a > 60),107    "gt90": sum(1 for a in ages if a > 90),108    "gt180": sum(1 for a in ages if a > 180),109    "futures": one(f"SELECT COUNT(*) FROM jobs WHERE {ACT}"110                   " AND date_posted > date('now')"),111}112113out["distributions"] = {114    "work_mode": rows(f"SELECT work_mode, COUNT(*) FROM jobs WHERE {ACT}"115                      " GROUP BY work_mode ORDER BY 2 DESC"),116    "language": rows(f"SELECT language, COUNT(*) FROM jobs WHERE {ACT}"117                     " GROUP BY language ORDER BY 2 DESC"),118    "region": rows(f"SELECT region, COUNT(*) FROM jobs WHERE {ACT}"119                   " GROUP BY region ORDER BY 2 DESC"),120    "villes_top": rows(f"SELECT city, COUNT(*) FROM jobs WHERE {ACT}"121                       " GROUP BY city ORDER BY 2 DESC LIMIT 15"),122}123124out["qualite"] = {125    "html_residuel": one(126        f"""SELECT COUNT(*) FROM jobs WHERE active=1 AND (127            description LIKE '%<p%' OR description LIKE '%<br%' OR128            description LIKE '%<li%' OR description LIKE '%&nbsp;%' OR129            description LIKE '%&amp;%' OR description LIKE '%&lt;%')"""),130    "desc_vides_canon": one(131        f"SELECT COUNT(*) FROM jobs WHERE {ACT}"132        " AND LENGTH(COALESCE(description,''))<50"),133    "city_st": one("SELECT COUNT(*) FROM jobs WHERE city IN ('St','Pointe')"),134    "salaires_aberrants": one(135        f"""SELECT COUNT(*) FROM jobs WHERE active=1 AND salary_min IS NOT NULL136            AND (salary_year_max>500000 OR salary_year_min>500000 OR137                 (salary_unit='hour' AND (salary_hour_max>200 OR salary_hour_min>200)))"""),138    "min_gt_max": one(139        "SELECT COUNT(*) FROM jobs WHERE salary_min IS NOT NULL"140        " AND salary_max IS NOT NULL AND salary_min>salary_max"),141    "dates_futures": one(f"SELECT COUNT(*) FROM jobs WHERE {ACT}"142                         " AND date_posted > date('now')"),143    "deadline_lt_posted": one(144        f"SELECT COUNT(*) FROM jobs WHERE active=1 AND date_deadline IS NOT NULL"145        " AND date_posted IS NOT NULL AND date_deadline<date_posted"),146    "geocode_failed": one(147        "SELECT COUNT(*) FROM jobs WHERE active=1 AND geocode_failed=1"),148}149150out["dedup"] = {151    "groupes": one("SELECT COUNT(DISTINCT dup_of) FROM jobs"152                   " WHERE dup_of IS NOT NULL AND active=1"),153    "masques": out["doublons_masques"],154}155156print(json.dumps(out, ensure_ascii=False, indent=1, default=str))157