<?php
// ============================================================
// data.php — accès aux données (requêtes réutilisables).
// ============================================================
require_once __DIR__ . '/config.php';

// --- Fabriques d'URL propres (centralisées) ---
function url_temoignages(): string { return SITE_URL . '/temoignages/'; }
function url_temoignage(string $slug): string { return SITE_URL . '/temoignages/' . $slug . '/'; }
function url_type(string $slug): string { return SITE_URL . '/temoignages/type/' . $slug . '/'; }


// Types de soins (catégories) avec le nombre de témoignages publiés
function types_de_soins(): array {
    return db()->query("
        SELECT c.id, c.nom, c.slug, COUNT(t.id) AS nb
        FROM categories c
        LEFT JOIN temoignages t ON t.type_soin_id = c.id AND t.statut='publie' AND t.est_formation=0
        WHERE c.type='type_de_soins'
        GROUP BY c.id
        HAVING nb > 0
        ORDER BY nb DESC
    ")->fetchAll();
}

// Liste paginée des témoignages publiés (option : filtrer par slug de type)
function temoignages_liste(?string $typeSlug = null, int $page = 1, int $parPage = 24, ?string $q = null): array {
    $db = db();
    $where = "t.statut='publie' AND t.est_formation=0";
    $params = [];
    if ($typeSlug) {
        $where .= " AND c.slug = ?";
        $params[] = $typeSlug;
    }
    if ($q !== null && trim($q) !== '') {
        $where .= " AND (t.titre LIKE ? OR t.contenu LIKE ?)";
        $like = '%' . trim($q) . '%';
        $params[] = $like;
        $params[] = $like;
    }
    $offset = max(0, ($page-1) * $parPage);

    $total = (function() use ($db,$where,$params){
        $sql = "SELECT COUNT(*) FROM temoignages t LEFT JOIN categories c ON c.id=t.type_soin_id WHERE $where";
        $st = $db->prepare($sql); $st->execute($params); return (int)$st->fetchColumn();
    })();

    $sql = "SELECT t.id,t.titre,t.slug,t.contenu,t.date_pub,c.nom AS type_nom,c.slug AS type_slug,
                   m.url AS image_url, m.alt AS image_alt
            FROM temoignages t
            LEFT JOIN categories c ON c.id=t.type_soin_id
            LEFT JOIN medias m ON m.id=t.image_id
            WHERE $where
            ORDER BY t.date_pub DESC
            LIMIT $parPage OFFSET $offset";
    $st = $db->prepare($sql); $st->execute($params);
    $rows = $st->fetchAll();

    return ['items'=>$rows,'total'=>$total,'page'=>$page,'par_page'=>$parPage,
            'pages'=>(int)ceil($total/$parPage)];
}

// Un témoignage par slug
function temoignage_par_slug(string $slug): ?array {
    $st = db()->prepare("
        SELECT t.*, c.nom AS type_nom, c.slug AS type_slug, m.url AS image_url, m.alt AS image_alt
        FROM temoignages t
        LEFT JOIN categories c ON c.id=t.type_soin_id
        LEFT JOIN medias m ON m.id=t.image_id
        WHERE t.slug = ? AND t.statut='publie'
    ");
    $st->execute([$slug]);
    $r = $st->fetch();
    return $r ?: null;
}

// Quelques témoignages publiés d'un (ou plusieurs) type(s) de soin, pour illustration
function temoignages_illustration(array $typeSlugs, int $limite = 3): array {
    if (!$typeSlugs) return [];
    $in = implode(',', array_fill(0, count($typeSlugs), '?'));
    $sql = "SELECT t.titre, t.slug, t.contenu, c.nom AS type_nom
            FROM temoignages t JOIN categories c ON c.id=t.type_soin_id
            WHERE t.statut='publie' AND t.est_formation=0 AND c.slug IN ($in)
            ORDER BY RANDOM() LIMIT $limite";
    $st = db()->prepare($sql);
    $st->execute($typeSlugs);
    return $st->fetchAll();
}

// Sélection de témoignages par mots-clés (pertinence thématique).
// $motscles : liste de mots/expressions à chercher dans titre + contenu.
// $typeSlugs : catégories de repli si pas assez de résultats par mots-clés.
// Retourne au plus $limite témoignages, les plus pertinents d'abord.
function temoignages_par_motscles(array $motscles, int $limite = 3, array $typeSlugs = []): array {
    $db = db();
    $vus = [];
    $res = [];

    // 1) Score par nombre de mots-clés présents (titre compte double)
    if ($motscles) {
        $score = [];
        $params = [];
        foreach ($motscles as $mc) {
            $like = '%' . trim($mc) . '%';
            $score[] = "(CASE WHEN t.titre LIKE ? THEN 2 ELSE 0 END) + (CASE WHEN t.contenu LIKE ? THEN 1 ELSE 0 END)";
            $params[] = $like; $params[] = $like;
        }
        $scoreSql = implode(' + ', $score);
        $sql = "SELECT * FROM (
                  SELECT t.titre, t.slug, t.contenu, c.nom AS type_nom, ($scoreSql) AS pertinence
                  FROM temoignages t LEFT JOIN categories c ON c.id=t.type_soin_id
                  WHERE t.statut='publie' AND t.est_formation=0
                    AND (c.slug IS NULL OR c.slug != 'temoignages-de-formation')
                ) WHERE pertinence > 0
                ORDER BY pertinence DESC, RANDOM()
                LIMIT $limite";
        $st = $db->prepare($sql);
        $st->execute($params);
        foreach ($st->fetchAll() as $r) { $res[] = $r; $vus[$r['slug']] = true; }
    }

    // 2) Complément par catégorie si on n'a pas atteint la limite
    if (count($res) < $limite && $typeSlugs) {
        $manque = $limite - count($res);
        $in = implode(',', array_fill(0, count($typeSlugs), '?'));
        $sql = "SELECT t.titre, t.slug, t.contenu, c.nom AS type_nom
                FROM temoignages t JOIN categories c ON c.id=t.type_soin_id
                WHERE t.statut='publie' AND t.est_formation=0 AND c.slug IN ($in)
                ORDER BY RANDOM()";
        $st = $db->prepare($sql);
        $st->execute($typeSlugs);
        foreach ($st->fetchAll() as $r) {
            if (isset($vus[$r['slug']])) continue;
            $res[] = $r; $vus[$r['slug']] = true;
            if (count($res) >= $limite) break;
        }
    }
    return array_slice($res, 0, $limite);
}

// Extrait texte court depuis le contenu HTML
function extrait_texte(string $html, int $max = 160): string {
    $t = trim(html_entity_decode(strip_tags($html), ENT_QUOTES, 'UTF-8'));
    $t = preg_replace('/\s+/', ' ', $t);
    if (mb_strlen($t) <= $max) return $t;
    return mb_substr($t, 0, $max) . '…';
}


// ---------- ARTICLES ----------
function url_articles(): string { return SITE_URL . '/articles.php'; }
function url_article(string $slug): string { return SITE_URL . '/articles/' . $slug . '/'; }

function articles_liste(int $limite = 0, bool $publiesSeulement = true): array {
    $sql = "SELECT a.*, c.nom AS cat_nom, c.slug AS cat_slug, m.url AS image_url
            FROM articles a
            LEFT JOIN categories c ON c.id = a.categorie_id
            LEFT JOIN medias m ON m.id = a.image_id";
    if ($publiesSeulement) $sql .= " WHERE a.statut = 'publie'";
    $sql .= " ORDER BY a.cree_le DESC";
    if ($limite > 0) $sql .= " LIMIT " . (int)$limite;
    return db()->query($sql)->fetchAll();
}

function article_par_slug(string $slug): ?array {
    $st = db()->prepare("SELECT a.*, c.nom AS cat_nom, c.slug AS cat_slug, m.url AS image_url
        FROM articles a
        LEFT JOIN categories c ON c.id = a.categorie_id
        LEFT JOIN medias m ON m.id = a.image_id
        WHERE a.slug = ? AND a.statut = 'publie'");
    $st->execute([$slug]);
    $r = $st->fetch();
    return $r ?: null;
}


// ---------- ARTICLES EXTERNES (lishan.fr, catégorie Tuina) ----------
function articles_lishan(int $cacheMinutes = 120): array {
    $cacheFile = DATA_DIR . '/cache_lishan_articles.json';
    if (is_file($cacheFile) && (time() - filemtime($cacheFile) < $cacheMinutes*60)) {
        $c = json_decode(file_get_contents($cacheFile), true);
        if (is_array($c)) return $c;
    }
    $ctx = stream_context_create(['http'=>['timeout'=>8],'ssl'=>['verify_peer'=>false,'verify_peer_name'=>false]]);
    $raw = @file_get_contents('https://lishan.fr/tuina-feed.php', false, $ctx);
    if ($raw === false) {
        if (is_file($cacheFile)) { $c = json_decode(file_get_contents($cacheFile), true); if (is_array($c)) return $c; }
        return [];
    }
    $d = json_decode($raw, true);
    $d = is_array($d) ? $d : [];
    @file_put_contents($cacheFile, json_encode($d, JSON_UNESCAPED_UNICODE));
    return $d;
}

// Fusionne les articles locaux + lishan, triés par date décroissante.
// Chaque entrée : titre, date, lien, extrait, image, source (local|lishan)
function articles_fusionnes(): array {
    $items = [];
    // locaux (publiés)
    foreach (articles_liste() as $a) {
        $items[] = [
            'titre'   => $a['titre'],
            'date'    => $a['cree_le'] ?: date('c'),
            'lien'    => url_article($a['slug']),
            'extrait' => $a['extrait'] ?: extrait_texte($a['contenu'], 160),
            'image'   => !empty($a['image_url']) ? SITE_URL.'/'.$a['image_url'] : null,
            'source'  => 'local',
            'cat'     => $a['cat_nom'] ?? null,
        ];
    }
    // lishan
    foreach (articles_lishan() as $l) {
        $items[] = [
            'titre'   => $l['titre'] ?? '',
            'date'    => $l['date'] ?? date('c'),
            'lien'    => $l['lien'] ?? '#',
            'extrait' => $l['extrait'] ?? '',
            'image'   => $l['image'] ?? null,
            'source'  => 'lishan',
            'cat'     => 'Lishan',
        ];
    }
    // tri par date décroissante
    usort($items, function($a,$b){ return strtotime($b['date']) <=> strtotime($a['date']); });
    return $items;
}


// Articles liés pour le maillage interne : même catégorie en priorité, complétés par les plus récents.
function articles_lies(int $excludeId, ?int $categorieId = null, int $limite = 4): array {
    $db = db();
    $res = [];
    $norm = function(array $r): array {
        return [
            'titre' => $r['titre'],
            'slug' => $r['slug'],
            'extrait' => $r['extrait'] ?? '',
            'contenu' => $r['contenu'] ?? '',
            'image_url' => !empty($r['image_url']) ? '/' . ltrim($r['image_url'],'/') : null,
            'cat_nom' => $r['cat_nom'] ?? null,
            'lien' => url_article($r['slug']),
            'externe' => false,
        ];
    };
    if ($categorieId) {
        $st = $db->prepare("SELECT a.titre,a.slug,a.extrait,a.contenu,m.url AS image_url,c.nom AS cat_nom
            FROM articles a LEFT JOIN medias m ON m.id=a.image_id LEFT JOIN categories c ON c.id=a.categorie_id
            WHERE a.statut='publie' AND a.id<>? AND a.categorie_id=? ORDER BY a.cree_le DESC LIMIT ?");
        $st->execute([$excludeId,$categorieId,$limite]);
        foreach ($st->fetchAll() as $r) $res[] = $norm($r);
    }
    if (count($res) < $limite) {
        $have = array_column($res,'slug');
        $st = $db->prepare("SELECT a.titre,a.slug,a.extrait,a.contenu,m.url AS image_url,c.nom AS cat_nom
            FROM articles a LEFT JOIN medias m ON m.id=a.image_id LEFT JOIN categories c ON c.id=a.categorie_id
            WHERE a.statut='publie' AND a.id<>? ORDER BY a.cree_le DESC LIMIT ?");
        $st->execute([$excludeId,$limite + count($have)]);
        foreach ($st->fetchAll() as $r) {
            if (count($res) >= $limite) break;
            if (in_array($r['slug'],$have,true)) continue;
            $res[] = $norm($r);
        }
    }
    // compléter avec les articles lishan (liens externes) si besoin
    if (count($res) < $limite) {
        foreach (articles_lishan() as $l) {
            if (count($res) >= $limite) break;
            $res[] = [
                'titre' => $l['titre'] ?? '',
                'slug' => '',
                'extrait' => $l['extrait'] ?? '',
                'contenu' => '',
                'image_url' => $l['image'] ?? null,
                'cat_nom' => 'Lishan',
                'lien' => $l['lien'] ?? '#',
                'externe' => true,
            ];
        }
    }
    return $res;
}
