#!/usr/bin/env python3
# =============================================================================
# Job·Ka — Groupe KA
# Auteur  : Simon-Pierre Boucher
# Contact : contact@spboucher.ai
# Fichier : scripts/mesure_baseline.py
# Rôle    : Mesure des métriques de complétude/qualité (mêmes définitions que
#           l'audit 03-BASELINE du 2026-08-19) — sert au « avant/après »
# Créé    : 2026-08-19   Modifié : 2026-08-19
# =============================================================================
"""Usage : .venv/bin/python scripts/mesure_baseline.py  (lecture seule)"""
from __future__ import annotations

import datetime as dt
import json
import sqlite3
from pathlib import Path

DB = Path(__file__).resolve().parent.parent / "data" / "jobka.db"
con = sqlite3.connect(f"file:{DB}?mode=ro", uri=True)
con.row_factory = sqlite3.Row

ACT = "active=1 AND dup_of IS NULL"          # actives canoniques (comme baseline)
PUB = ACT + " AND quarantine IS NULL"        # publiées (nouveau périmètre)


def one(sql):
    return con.execute(sql).fetchone()[0]


def rows(sql):
    return [tuple(r) for r in con.execute(sql).fetchall()]


out = {}
out["total"] = one("SELECT COUNT(*) FROM jobs")
out["actives"] = one("SELECT COUNT(*) FROM jobs WHERE active=1")
out["actives_canoniques"] = one(f"SELECT COUNT(*) FROM jobs WHERE {ACT}")
out["publiees"] = one(f"SELECT COUNT(*) FROM jobs WHERE {PUB}")
out["quarantaine"] = one(
    "SELECT COUNT(*) FROM jobs WHERE active=1 AND quarantine IS NOT NULL")
out["quarantaine_motifs"] = rows(
    "SELECT quarantine, COUNT(*) FROM jobs WHERE active=1"
    " AND quarantine IS NOT NULL GROUP BY quarantine ORDER BY 2 DESC")
out["doublons_masques"] = one(
    "SELECT COUNT(*) FROM jobs WHERE active=1 AND dup_of IS NOT NULL")
out["expirees"] = one("SELECT COUNT(*) FROM jobs WHERE active=0")

n = out["actives_canoniques"]
champ_conds = [
    ("url", "url<>''"),
    ("date_posted", "date_posted IS NOT NULL"),
    ("description_non_vide", "LENGTH(COALESCE(description,''))>0"),
    ("description_200", "LENGTH(COALESCE(description,''))>200"),
    ("city", "city<>''"),
    ("geo", "lat IS NOT NULL AND lng IS NOT NULL"),
    ("region_admin", "region<>'' AND region<>'Québec'"),
    ("region_toute", "region<>''"),
    ("category", "category<>''"),
    ("employment_type", "employment_type IS NOT NULL"),
    ("salary_min", "salary_min IS NOT NULL"),
    ("work_mode", "work_mode IS NOT NULL"),
    ("salary_label", "salary_label<>''"),
    ("postal_code", "postal_code<>''"),
    ("date_deadline", "date_deadline IS NOT NULL"),
    ("address", "address<>''"),
    ("requirements", "requirements NOT IN ('','{}')"),
    ("benefits", "benefits NOT IN ('','[]')"),
    ("language", "language IS NOT NULL AND language<>''"),
    ("company_logo", "company_logo IS NOT NULL AND company_logo<>''"),
    ("apply_url", "apply_url IS NOT NULL AND apply_url<>''"),
    ("title_clean", "title_clean IS NOT NULL AND title_clean<>''"),
]
out["completude"] = {}
for name, cond in champ_conds:
    c = one(f"SELECT COUNT(*) FROM jobs WHERE {ACT} AND {cond}")
    out["completude"][name] = (c, round(100.0 * c / n, 1) if n else 0)

out["par_famille"] = rows(
    f"""SELECT ats, COUNT(*),
        ROUND(100.0*SUM(salary_min IS NOT NULL)/COUNT(*),1),
        ROUND(100.0*SUM(work_mode IS NOT NULL)/COUNT(*),1),
        ROUND(100.0*SUM(employment_type IS NOT NULL)/COUNT(*),1),
        ROUND(100.0*SUM(date_deadline IS NOT NULL)/COUNT(*),1),
        ROUND(100.0*SUM(lat IS NOT NULL)/COUNT(*),1),
        ROUND(100.0*SUM(LENGTH(COALESCE(description,''))>200)/COUNT(*),1),
        ROUND(100.0*SUM(date_posted IS NOT NULL)/COUNT(*),1),
        ROUND(100.0*SUM(language IS NOT NULL AND language<>'')/COUNT(*),1),
        ROUND(100.0*SUM(apply_url IS NOT NULL AND apply_url<>'')/COUNT(*),1),
        ROUND(100.0*SUM(company_logo IS NOT NULL AND company_logo<>'')/COUNT(*),1),
        ROUND(100.0*SUM(benefits NOT IN ('','[]'))/COUNT(*),1)
        FROM jobs WHERE {ACT} GROUP BY ats ORDER BY 2 DESC""")

today = dt.date.today()
ages = [
    (today - dt.date.fromisoformat(r[0])).days
    for r in rows(f"SELECT date_posted FROM jobs WHERE {ACT}"
                  " AND date_posted IS NOT NULL")
    if r[0] <= today.isoformat()
]
ages.sort()
out["fraicheur"] = {
    "n": len(ages),
    "median": ages[len(ages) // 2] if ages else None,
    "moyen": round(sum(ages) / len(ages), 1) if ages else None,
    "gt60": sum(1 for a in ages if a > 60),
    "gt90": sum(1 for a in ages if a > 90),
    "gt180": sum(1 for a in ages if a > 180),
    "futures": one(f"SELECT COUNT(*) FROM jobs WHERE {ACT}"
                   " AND date_posted > date('now')"),
}

out["distributions"] = {
    "work_mode": rows(f"SELECT work_mode, COUNT(*) FROM jobs WHERE {ACT}"
                      " GROUP BY work_mode ORDER BY 2 DESC"),
    "language": rows(f"SELECT language, COUNT(*) FROM jobs WHERE {ACT}"
                     " GROUP BY language ORDER BY 2 DESC"),
    "region": rows(f"SELECT region, COUNT(*) FROM jobs WHERE {ACT}"
                   " GROUP BY region ORDER BY 2 DESC"),
    "villes_top": rows(f"SELECT city, COUNT(*) FROM jobs WHERE {ACT}"
                       " GROUP BY city ORDER BY 2 DESC LIMIT 15"),
}

out["qualite"] = {
    "html_residuel": one(
        f"""SELECT COUNT(*) FROM jobs WHERE active=1 AND (
            description LIKE '%<p%' OR description LIKE '%<br%' OR
            description LIKE '%<li%' OR description LIKE '%&nbsp;%' OR
            description LIKE '%&amp;%' OR description LIKE '%&lt;%')"""),
    "desc_vides_canon": one(
        f"SELECT COUNT(*) FROM jobs WHERE {ACT}"
        " AND LENGTH(COALESCE(description,''))<50"),
    "city_st": one("SELECT COUNT(*) FROM jobs WHERE city IN ('St','Pointe')"),
    "salaires_aberrants": one(
        f"""SELECT COUNT(*) FROM jobs WHERE active=1 AND salary_min IS NOT NULL
            AND (salary_year_max>500000 OR salary_year_min>500000 OR
                 (salary_unit='hour' AND (salary_hour_max>200 OR salary_hour_min>200)))"""),
    "min_gt_max": one(
        "SELECT COUNT(*) FROM jobs WHERE salary_min IS NOT NULL"
        " AND salary_max IS NOT NULL AND salary_min>salary_max"),
    "dates_futures": one(f"SELECT COUNT(*) FROM jobs WHERE {ACT}"
                         " AND date_posted > date('now')"),
    "deadline_lt_posted": one(
        f"SELECT COUNT(*) FROM jobs WHERE active=1 AND date_deadline IS NOT NULL"
        " AND date_posted IS NOT NULL AND date_deadline<date_posted"),
    "geocode_failed": one(
        "SELECT COUNT(*) FROM jobs WHERE active=1 AND geocode_failed=1"),
}

out["dedup"] = {
    "groupes": one("SELECT COUNT(DISTINCT dup_of) FROM jobs"
                   " WHERE dup_of IS NOT NULL AND active=1"),
    "masques": out["doublons_masques"],
}

print(json.dumps(out, ensure_ascii=False, indent=1, default=str))