<?php
// ══════════════════════════════════════════════════════════════════
//  KAP DSI — API REST v5
//  Architecture : Sociétés / Contacts / Documents sur disque
// ══════════════════════════════════════════════════════════════════
ini_set('display_errors', 1);
error_reporting(E_ALL);
ob_start();
ini_set('log_errors', '1');
date_default_timezone_set('Europe/Paris');

require_once __DIR__ . '/kap-config.php';

// ── Constantes GED OAuth ─────────────────────────────────────────
define('GED_CLIENT_ID',     '657289384060-p9pjbrpafnpliu26pbn73rdkb2iio4ub.apps.googleusercontent.com');
define('GED_CLIENT_SECRET', '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');
define('GED_SCOPE',         'https://www.googleapis.com/auth/drive');

// ── Callback OAuth — intercepté avant les headers JSON ───────────
if (($_GET['action']??'') === 'ged_oauth_callback') {
    ob_end_clean();
    $code  = $_GET['code']  ?? '';
    $error = $_GET['error'] ?? '';
    $redirectUri = (strpos($_SERVER['HTTP_HOST']??'','kapdsi.fr')!==false
        ? 'https://kapdsi.fr'
        : 'https://td-info.fr/kapdsi').'/api.php?action=ged_oauth_callback';
    if ($error) { echo "<html><body><p>Erreur OAuth: ".htmlspecialchars($error)."</p><script>window.opener?.postMessage({type:'ged_oauth',error:'".addslashes($error)."'},'*');window.close();</script></body></html>"; exit; }
    if (!$code) { echo "<html><body><p>Code manquant</p><script>window.opener?.postMessage({type:'ged_oauth',error:'Code manquant'},'*');window.close();</script></body></html>"; exit; }
    try {
        $ch = curl_init('https://oauth2.googleapis.com/token');
        curl_setopt_array($ch,[CURLOPT_RETURNTRANSFER=>true,CURLOPT_POST=>true,
            CURLOPT_POSTFIELDS=>http_build_query(['client_id'=>GED_CLIENT_ID,'client_secret'=>GED_CLIENT_SECRET,'redirect_uri'=>$redirectUri,'code'=>$code,'grant_type'=>'authorization_code']),
            CURLOPT_HTTPHEADER=>['Content-Type: application/x-www-form-urlencoded']]);
        $res=curl_exec($ch); curl_close($ch);
        $data=json_decode($res,true);
        if (empty($data['refresh_token'])) { $msg=$data['error_description']??$data['error']??'Token manquant: '.$res; echo "<html><body><p>".htmlspecialchars($msg)."</p><script>window.opener?.postMessage({type:'ged_oauth',error:'".addslashes($msg)."'},'*');window.close();</script></body></html>"; exit; }
        $pdo=new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME.';charset='.DB_CHARSET,DB_USER,DB_PASS,[PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION]);
        $pdo->prepare("INSERT INTO app_settings (cle,valeur,label,description) VALUES ('ged_refresh_token',?,'GED','') ON DUPLICATE KEY UPDATE valeur=?")->execute([$data['refresh_token'],$data['refresh_token']]);
        $pdo->prepare("DELETE FROM app_settings WHERE cle='ged_access_token'")->execute();
        echo "<html><body><p>✅ Autorisé. Vous pouvez fermer cette fenêtre.</p><script>window.opener?.postMessage({type:'ged_oauth',success:true},'*');window.close();</script></body></html>"; exit;
    } catch(Exception $e) { echo "<html><body><p>Erreur: ".htmlspecialchars($e->getMessage())."</p></body></html>"; exit; }
}

header('Content-Type: application/json; charset=utf-8');
$origin = $_SERVER['HTTP_ORIGIN'] ?? '';
header('Access-Control-Allow-Origin: ' . $origin);
header('Access-Control-Allow-Credentials: true');
header('Access-Control-Allow-Headers: Content-Type, X-KAP-Token');
header('Access-Control-Allow-Methods: GET, POST, OPTIONS');
if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') { http_response_code(204); exit; }

// ── PDO ───────────────────────────────────────────────────────────
function db(): PDO {
    static $p = null;
    if ($p) return $p;
    $p = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME.';charset='.DB_CHARSET, DB_USER, DB_PASS, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]);
    return $p;
}

// ── Helpers ───────────────────────────────────────────────────────
function uuid(): string {
    $b = random_bytes(16);
    $b[6] = chr((ord($b[6]) & 0x0f) | 0x40);
    $b[8] = chr((ord($b[8]) & 0x3f) | 0x80);
    return vsprintf('%s%s-%s-%s-%s-%s%s%s', str_split(bin2hex($b), 4));
}
function ok(array $d = []): void  { ob_clean(); echo json_encode(['ok'=>true]+$d); exit; }
function err(string $m, int $c=400): void { http_response_code($c); ob_clean(); echo json_encode(['ok'=>false,'error'=>$m]); exit; }
function body(): array { return json_decode(file_get_contents('php://input'), true) ?? []; }
function clean(?string $v): string { return strip_tags(trim($v ?? '')); }
function getToken(): ?string { return $_SERVER['HTTP_X_KAP_TOKEN'] ?? $_GET['_t'] ?? null; }

function mkSession(string $entityId, bool $remember=false): string {
    $token = bin2hex(random_bytes(32));
    if ($remember) {
        $sql = "INSERT INTO sessions (id,entity_id,ip,user_agent,expires_at) VALUES (?,?,?,?,DATE_ADD(NOW(),INTERVAL 30 DAY))";
    } else {
        $sql = "INSERT INTO sessions (id,entity_id,ip,user_agent,expires_at) VALUES (?,?,?,?,DATE_ADD(NOW(),INTERVAL 8 HOUR))";
    }
    db()->prepare($sql)->execute([$token, $entityId, $_SERVER['REMOTE_ADDR']??'', substr($_SERVER['HTTP_USER_AGENT']??'',0,255)]);
    return $token;
}

// Répertoire uploads (chemin absolu)
function uploadsDir(): string {
    return rtrim($_SERVER['DOCUMENT_ROOT'], '/') . parse_url(KAP_SITE_URL, PHP_URL_PATH) . '/uploads';
}
function uploadsUrl(): string { return rtrim(KAP_SITE_URL, '/') . '/uploads'; }

// ── Auth CRM ──────────────────────────────────────────────────────
function crmCheck(): array {
    // Comme crmAuth mais retourne un tableau vide si non authentifié
    try { return crmAuth(); } catch (Exception $e) { return ['id'=>'','role'=>'']; }
}

function crmAuth(): array {
    $token = getToken();
    if (!$token) err('Non authentifié', 401);
    $stmt = db()->prepare("SELECT s.entity_id, u.* FROM sessions s JOIN crm_users u ON u.id = SUBSTRING(s.entity_id,5) WHERE s.id=? AND s.expires_at>NOW() AND s.entity_id LIKE 'crm:%'");
    $stmt->execute([$token]);
    $row = $stmt->fetch();
    if (!$row || !$row['actif']) err('Session expirée ou compte désactivé', 401);
    // Prolonger la session selon sa durée initiale (8h ou 30j)
    $expIn = strtotime($row['expires_at']??'now') - strtotime($row['created_at']??$row['expires_at']??'now');
    if ($expIn > 86400) {
        db()->prepare("UPDATE sessions SET last_seen=NOW(),expires_at=DATE_ADD(NOW(),INTERVAL 30 DAY) WHERE id=?")->execute([$token]);
    } else {
        db()->prepare("UPDATE sessions SET last_seen=NOW(),expires_at=DATE_ADD(NOW(),INTERVAL 8 HOUR) WHERE id=?")->execute([$token]);
    }
    return $row;
}
function superAdmin(): array {
    $u = crmAuth();
    if ($u['role'] !== 'super_admin') err('Réservé au super administrateur', 403);
    return $u;
}

// ── Auth Contact (espace client) ──────────────────────────────────
function contactAuth(): array {
    $token = getToken();
    if (!$token) err('Non authentifié', 401);
    $stmt = db()->prepare("SELECT s.*, c.*, s.entity_id FROM sessions s JOIN contacts c ON c.id=s.entity_id WHERE s.id=? AND s.expires_at>NOW() AND s.entity_id NOT LIKE 'crm:%'");
    $stmt->execute([$token]);
    $row = $stmt->fetch();
    if (!$row) err('Session expirée', 401);
    if ($row['statut_espace'] !== 'active') err('Compte non actif', 403);
    db()->prepare('UPDATE sessions SET last_seen=NOW(),expires_at=DATE_ADD(NOW(),INTERVAL 8 HOUR) WHERE id=?')->execute([$token]);
    return $row;
}

// ── Formatage ─────────────────────────────────────────────────────
function fmtUser(array $u): array {
    return ['id'=>$u['id'],'login'=>$u['login'],'email'=>$u['email'],
            'nom'=>$u['nom'],'prenom'=>$u['prenom'],'role'=>$u['role'],
            'actif'=>(bool)$u['actif'],'dateCreation'=>$u['date_creation'],'derniereCnx'=>$u['derniere_cnx']];
}
function fmtSociete(array $s): array {
    return [
        'id'=>$s['id'],'nom'=>$s['nom'],'siren'=>$s['siren']??'','siret'=>$s['siret']??'','tvaIntra'=>$s['tva_intra']??'','siteWeb'=>$s['site_web']??'',
        'adresse1'=>$s['adresse1']??'','adresse2'=>$s['adresse2']??'',
        'codePostal'=>$s['code_postal']??'','ville'=>$s['ville']??'','pays'=>$s['pays']??'France',
        'typeContact'=>$s['type_contact'],'source'=>$s['source']??'',
        'secteur'=>$s['secteur']??'','taille'=>$s['taille']??'','caFourchette'=>$s['ca_fourchette']??'',
        'prochainContact'=>$s['prochain_contact']??'','prochainContactObjet'=>$s['prochain_contact_objet']??'',
        'notes'=>$s['notes']??'',
        'pipelineStade'=>$s['pipeline_stade']??'','pipelineMontant'=>(float)($s['pipeline_montant']??0),
        'pipelineProba'=>(int)($s['pipeline_proba']??0),'pipelineDateClose'=>$s['pipeline_date_close']??'',
        'crmUserId'=>$s['crm_user_id']??'',
        'accesFactures'=>isset($s['acces_factures'])?(bool)$s['acces_factures']:true,
        'accesTickets' =>isset($s['acces_tickets']) ?(bool)$s['acces_tickets'] :true,
        'dateCreation'=>$s['date_creation'],'dateMaj'=>$s['date_maj']??'',
        'nbContacts'=>(int)($s['nb_contacts']??0),'nbDocuments'=>(int)($s['nb_documents']??0),
        'nbMissions'=>(int)($s['nb_missions']??0),'nbFactures'=>(int)($s['nb_factures']??0),
        'nbInteractions'=>(int)($s['nb_interactions']??0),
    ];
}
function fmtContact(array $c): array {
    return [
        'id'=>$c['id'],'societeId'=>$c['societe_id'],
        'prenom'=>$c['prenom']??'','nom'=>$c['nom'],
        'poste'=>$c['poste']??'','email'=>$c['email']??'',
        'telephone'=>$c['telephone']??'','telephone2'=>$c['telephone2']??'',
        'estPrincipal'=>(bool)($c['est_principal']??false),
        'accesFactures'=>isset($c['acces_factures'])?($c['acces_factures']===null?null:(bool)$c['acces_factures']):null,
        'accesTickets' =>isset($c['acces_tickets']) ?($c['acces_tickets'] ===null?null:(bool)$c['acces_tickets'] ):null,
        'statutEspace'=>$c['statut_espace']??'non_invite',
        'linkedin'=>$c['linkedin']??'',
        'notes'=>$c['notes']??'',
        'dateCreation'=>$c['date_creation'],'dateMaj'=>$c['date_maj']??'',
    ];
}
function fmtDocument(array $d): array {
    return [
        'id'=>$d['id'],'societeId'=>$d['societe_id'],
        'categorie'=>$d['categorie'],'titre'=>$d['titre'],
        'description'=>$d['description']??'','version'=>$d['version']??'','tags'=>$d['tags']??'',
        'fileName'=>$d['file_name'],'fileExt'=>$d['file_ext'],
        'fileSize'=>(int)$d['file_size'],'mimeType'=>$d['mime_type']??'',
        'crmUserId'=>$d['crm_user_id']??'','crmUserNom'=>($d['crm_prenom']??'').' '.($d['crm_nom']??''),
        'dateCreation'=>$d['date_creation'],'dateMaj'=>$d['date_maj']??'',
    ];
}
function fmtInteraction(array $i): array {
    return [
        'id'=>$i['id'],'societeId'=>$i['societe_id'],'contactId'=>$i['contact_id']??'',
        'contactNom'=>trim(($i['contact_prenom']??'').' '.($i['contact_nom']??'')),
        'crmUserId'=>$i['crm_user_id'],
        'crmUserNom'=>trim(($i['crm_prenom']??'').' '.($i['crm_nom']??'')),
        'type'=>$i['type'],'date'=>$i['date'],'dureMin'=>$i['duree_min'],
        'resume'=>$i['resume'],'suite'=>$i['suite']??'','notionUrl'=>$i['notion_url']??'','dateCreation'=>$i['date_creation'],
    ];
}
function fmtMission(array $m): array {
    return ['id'=>$m['id'],'societeId'=>$m['societe_id'],'crmUserId'=>$m['crm_user_id']??'',
            'intitule'=>$m['intitule'],'description'=>$m['description']??'','statut'=>$m['statut'],
            'dateDebut'=>$m['date_debut']??'','dateFin'=>$m['date_fin']??'',
            'joursMois'=>(float)($m['jours_mois']??0),'tarifJour'=>(float)($m['tarif_jour']??0),
            'dateCreation'=>$m['date_creation']];
}
function fmtFacture(array $f): array {
    return ['id'=>$f['id'],'societeId'=>$f['societe_id'],'contactId'=>$f['contact_id']??'',
            'missionId'=>$f['mission_id']??'','numero'=>$f['numero'],'libelle'=>$f['libelle']??'',
            'montantHT'=>(float)$f['montant_ht'],'tva'=>(float)$f['tva'],
            'dateEmission'=>$f['date_emission']??'','dateEcheance'=>$f['date_echeance']??'',
            'statut'=>$f['statut'],'fileName'=>$f['file_name']??'',
            'hasFile'=>!empty($f['file_path']),'fileSize'=>$f['file_size'],
            'dateCreation'=>$f['date_creation'],'societeNom'=>$f['societe_nom']??''];
}

// ════════════════════════════════════════════════════════════════
//  COMPTABILITÉ — Fonctions utilitaires (avant le switch)
// ════════════════════════════════════════════════════════════════

// ── Détection Factur-X / ZUGFeRD ─────────────────────────────
function detectFacturX(string $path): array {
    $status = 'non_detecte'; $version = null;
    if (!file_exists($path) || filesize($path) < 100) return [$status, $version];
    $fh = fopen($path, 'rb');
    $chunk = fread($fh, 2 * 1024 * 1024);
    fclose($fh);
    if (strpos($chunk, 'factur-x.eu') !== false || strpos($chunk, 'urn:factur-x') !== false || strpos($chunk, 'factur-x.xml') !== false) {
        $status = 'facturx';
        if      (strpos($chunk, 'MINIMUM')  !== false) $version = 'MINIMUM';
        elseif  (strpos($chunk, 'BASIC WL') !== false) $version = 'BASIC WL';
        elseif  (strpos($chunk, 'BASIC')    !== false) $version = 'BASIC';
        elseif  (strpos($chunk, 'EN 16931') !== false || strpos($chunk, 'EN16931') !== false) $version = 'EN 16931';
        elseif  (strpos($chunk, 'EXTENDED') !== false) $version = 'EXTENDED';
    } elseif (strpos($chunk, 'ZUGFeRD') !== false || strpos($chunk, 'zugferd') !== false) {
        $status = 'zugferd';
        $version = strpos($chunk, '2.') !== false ? 'ZUGFeRD 2.x' : 'ZUGFeRD 1.x';
    }
    return [$status, $version];
}

function fmtEcriture(array $e): array {
    $ht  = (float)$e['montant_ht'];
    $tva = (float)$e['tva_taux'];
    return [
        'id'              => $e['id'],
        'type'            => $e['type'],
        'categorie'       => $e['categorie']     ?? '',
        'numero'          => $e['numero']        ?? '',
        'libelle'         => $e['libelle'],
        'tiers'           => $e['tiers']         ?? '',
        'societeId'       => $e['societe_id']    ?? '',
        'societeNom'      => $e['societe_nom']   ?? '',
        'montantHT'       => $ht,
        'tvaTaux'         => $tva,
        'montantTTC'      => round($ht * (1 + $tva / 100), 2),
        'datePiece'       => $e['date_piece'],
        'dateEcheance'    => $e['date_echeance'] ?? '',
        'periode'         => $e['periode'],
        'statutPaiement'  => $e['statut_paiement'],
        'statutExport'    => $e['statut_export'],
        'dateExport'      => $e['date_export']   ?? '',
        'exportRef'       => $e['export_ref']    ?? '',
        'hasFile'         => !empty($e['file_path']),
        'fileName'        => $e['file_name']     ?? '',
        'fileSize'        => $e['file_size'],
        'facturxStatus'   => $e['facturx_status'],
        'facturxVersion'  => $e['facturx_version'] ?? '',
        'notes'           => $e['notes']         ?? '',
        'crmUserId'       => $e['crm_user_id']   ?? '',
        'dateCreation'    => $e['date_creation'],
    ];
}


function nextTicketNum(): string {
    $year = (int)date('Y');
    db()->prepare("INSERT INTO ticket_seq (annee,seq) VALUES (?,1) ON DUPLICATE KEY UPDATE seq=seq+1")->execute([$year]);
    $seq = (int)db()->prepare("SELECT seq FROM ticket_seq WHERE annee=?")->execute([$year]) ? db()->lastInsertId() : 1;
    // Re-fetch the actual value
    $stmt = db()->prepare("SELECT seq FROM ticket_seq WHERE annee=?"); $stmt->execute([$year]);
    $seq = (int)$stmt->fetchColumn();
    return sprintf("TK-%d-%03d", $year, $seq);
}

function fmtTicket(array $t): array {
    return [
        "id"             => $t["id"],
        "numero"         => $t["numero"],
        "titre"          => $t["titre"],
        "description"    => $t["description"] ?? "",
        "societeId"      => $t["societe_id"]  ?? "",
        "societeNom"     => $t["societe_nom"] ?? "",
        "contactId"      => $t["contact_id"]  ?? "",
        "contactNom"     => trim(($t["contact_prenom"] ?? "") . " " . ($t["contact_nom"] ?? "")),
        "contactEmail"   => $t["contact_email"] ?? "",
        "crmUserId"      => $t["crm_user_id"] ?? "",
        "crmUserNom"     => trim(($t["crm_prenom"] ?? "") . " " . ($t["crm_nom"] ?? "")),
        "statut"         => $t["statut"],
        "categorie"      => $t["categorie"],
        "source"         => $t["source"],
        "emailSource"    => $t["email_source"] ?? "",
        "nbMessages"     => (int)($t["nb_messages"] ?? 0),
        "dateResolution" => $t["date_resolution"] ?? "",
        "dateCreation"   => $t["date_creation"],
        "dateMaj"        => $t["date_maj"] ?? "",
    ];
}

function fmtMessage(array $m): array {
    return [
        "id"            => $m["id"],
        "ticketId"      => $m["ticket_id"],
        "auteurType"    => $m["auteur_type"],
        "auteurNom"     => $m["auteur_nom"] ?? "",
        "contenu"       => $m["contenu"],
        "visibleClient" => (bool)$m["visible_client"],
        "hasFile"       => !empty($m["file_path"]),
        "fileName"      => $m["file_name"] ?? "",
        "fileSize"      => $m["file_size"] ?? 0,
        "fileMime"      => $m["file_mime"] ?? "",
        "dateCreation"  => $m["date_creation"],
    ];
}

function shouldNotify(string $event): bool {
    // 'message' pour les réponses, ou le nom du statut pour les changements de statut
    try {
        $stmt = db()->prepare("SELECT notify_client FROM notification_settings WHERE statut=?");
        $stmt->execute([$event]);
        $row = $stmt->fetch();
        return $row ? (bool)$row['notify_client'] : true; // défaut = envoyer
    } catch (Exception $e) {
        return true; // si table absente, envoyer quand même
    }
}

function sendTicketEmail(array $ticket, string $event, string $contactEmail, string $contactPrenom, string $siteUrl): void {
    // Vérifier les paramètres de notification
    if (!shouldNotify($event)) return;
    $statuts = ["nouveau"=>"Nouveau","en_cours"=>"En cours","attente_client"=>"En attente de votre retour","resolu"=>"Résolu","ferme"=>"Fermé"];
    $statutLabel = $statuts[$ticket["statut"]] ?? $ticket["statut"];
    $ticketUrl = rtrim($siteUrl, "/") . "/espace-client/#tickets";

    $subjects = [
        "created"  => "Ticket #{$ticket["numero"]} créé — {$ticket["titre"]}",
        "updated"  => "Ticket #{$ticket["numero"]} mis à jour — Statut : {$statutLabel}",
        "message"  => "Nouveau message sur le ticket #{$ticket["numero"]}",
        "resolved" => "Ticket #{$ticket["numero"]} résolu — {$ticket["titre"]}",
    ];
    $subject = $subjects[$event] ?? "Mise à jour ticket #{$ticket["numero"]}";

    $html = "<div style='font-family:Arial,sans-serif;max-width:600px;margin:0 auto'>
      <div style='background:#0F2044;padding:24px 28px;border-bottom:3px solid #e05314'>
        <p style='color:#fff;font-size:18px;font-weight:700;margin:0'>KAP DSI — Support</p>
      </div>
      <div style='background:#f5f6f8;padding:28px'>
        <p style='font-size:14px;color:#0F2044;font-weight:600;margin:0 0 12px'>Bonjour {$contactPrenom},</p>
        <div style='background:#fff;border:1px solid #e4e7ec;border-radius:6px;padding:16px 20px;margin-bottom:16px'>
          <p style='font-size:11px;color:#6b7280;text-transform:uppercase;letter-spacing:1px;margin:0 0 4px'>Ticket</p>
          <p style='font-size:15px;font-weight:700;color:#0F2044;margin:0 0 6px'>{$ticket["numero"]} — " . htmlspecialchars($ticket["titre"]) . "</p>
          <p style='font-size:13px;margin:0'><span style='background:#dbeafe;color:#1d4ed8;padding:2px 8px;border-radius:10px;font-size:11px;font-weight:700'>{$statutLabel}</span></p>
        </div>
        <div style='text-align:center;margin:20px 0'>
          <a href='{$ticketUrl}' style='background:#e05314;color:#fff;padding:12px 28px;text-decoration:none;border-radius:4px;font-size:13px;font-weight:700'>Voir le ticket</a>
        </div>
        <p style='font-size:11px;color:#9ca3af;text-align:center;margin:0'>KAP DSI · support@kapdsi.fr</p>
      </div>
    </div>";

    $senderName = appSetting('sender_name', KAP_SENDER_NAME) . ' Support';
    $payload = json_encode([
        "sender"      => ["name" => $senderName, "email" => KAP_SENDER_EMAIL],
        "to"          => [["email" => $contactEmail, "name" => $contactPrenom]],
        "subject"     => $subject,
        "htmlContent" => $html,
    ]);
    $ch = curl_init("https://api.brevo.com/v3/smtp/email");
    curl_setopt_array($ch, [CURLOPT_RETURNTRANSFER=>true,CURLOPT_POST=>true,CURLOPT_POSTFIELDS=>$payload,
        CURLOPT_HTTPHEADER=>["accept: application/json","api-key: ".KAP_BREVO_API_KEY,"content-type: application/json"],CURLOPT_TIMEOUT=>10]);
    curl_exec($ch); curl_close($ch);
}


function saveTicketFile(string $ticketId, ?array $file): array {
    if (!$file || $file['error'] !== UPLOAD_ERR_OK) return [null,null,null,null];
    if ($file['size'] > 10*1024*1024) err('Fichier trop volumineux (max 10 Mo)');
    $allowedExts = ['jpg','jpeg','png','gif','pdf','doc','docx','xls','xlsx','txt','zip'];
    $ext = strtolower(pathinfo($file['name'],PATHINFO_EXTENSION));
    if (!in_array($ext,$allowedExts)) err('Type de fichier non autorisé ('.$ext.')');
    $dir = __DIR__.'/uploads/tickets/'.$ticketId;
    if (!is_dir($dir)) mkdir($dir,0755,true);
    $diskName = uuid().'-'.$ext;
    $filePath = 'uploads/tickets/'.$ticketId.'/'.$diskName;
    if (!move_uploaded_file($file['tmp_name'],__DIR__.'/'.$filePath)) err('Erreur sauvegarde fichier');
    $mimes = ['jpg'=>'image/jpeg','jpeg'=>'image/jpeg','png'=>'image/png','gif'=>'image/gif','pdf'=>'application/pdf','doc'=>'application/msword','docx'=>'application/vnd.openxmlformats-officedocument.wordprocessingml.document','xls'=>'application/vnd.ms-excel','xlsx'=>'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet','txt'=>'text/plain','zip'=>'application/zip'];
    return [$filePath, $file['name'], $file['size'], $mimes[$ext]??'application/octet-stream'];
}

// ── Paramètres applicatifs ────────────────────────────────────────
function appSetting(string $cle, string $default=''): string {
    try {
        $s = db()->prepare("SELECT valeur FROM app_settings WHERE cle=?");
        $s->execute([$cle]);
        $r = $s->fetchColumn();
        return $r !== false ? (string)$r : $default;
    } catch (Exception $e) { return $default; }
}

// ── Routing ───────────────────────────────────────────────────────
$action = $_GET['action'] ?? '';
$method = $_SERVER['REQUEST_METHOD'];

// ── Recalcul avancement projets ─────────────────────────────────
function recalcAvancement(string $projetId): void {
    if (!$projetId) return;
    // Recalculer les dates des phases depuis leurs tâches (toutes tâches y compris sous-tâches)
    $phases2 = db()->prepare("SELECT id FROM projet_phases WHERE projet_id=?");
    $phases2->execute([$projetId]);
    foreach ($phases2->fetchAll(PDO::FETCH_COLUMN) as $phId) {
        $dts = db()->prepare("SELECT MIN(date_debut) AS dmin, MAX(COALESCE(date_fin_reelle,date_fin)) AS dmax FROM projet_taches WHERE phase_id=? AND (date_debut IS NOT NULL OR date_fin IS NOT NULL OR date_fin_reelle IS NOT NULL)");
        $dts->execute([$phId]); $dt=$dts->fetch();
        if ($dt['dmin'] || $dt['dmax']) {
            db()->prepare("UPDATE projet_phases SET date_debut=?, date_fin=? WHERE id=?")
                ->execute([$dt['dmin']??null, $dt['dmax']??null, $phId]);
        }
    }
    $parents = db()->prepare("SELECT DISTINCT parent_id FROM projet_taches WHERE projet_id=? AND parent_id IS NOT NULL");
    $parents->execute([$projetId]);
    $parentIds = $parents->fetchAll(PDO::FETCH_COLUMN);
    foreach ($parentIds as $parentId) {
        $subs = db()->prepare("SELECT COUNT(*) AS total, SUM(CASE WHEN statut='terminee' THEN 1 ELSE 0 END) AS done FROM projet_taches WHERE parent_id=?");
        $subs->execute([$parentId]); $sub = $subs->fetch();
        $av = $sub['total']>0 ? round($sub['done']/$sub['total']*100) : 0;
        $statut = $av>=100 ? 'terminee' : ($av>0 ? 'en_cours' : 'a_faire');
        $finAuto = $statut==='terminee' ? date('Y-m-d') : null;
        db()->prepare("UPDATE projet_taches SET avancement=?,statut=?,date_fin_reelle=COALESCE(date_fin_reelle,?) WHERE id=?")->execute([$av,$statut,$finAuto,$parentId]);
    }
    $phases = db()->prepare("SELECT id FROM projet_phases WHERE projet_id=?");
    $phases->execute([$projetId]); $phaseList = $phases->fetchAll(PDO::FETCH_COLUMN);
    foreach ($phaseList as $phaseId) {
        $tasks = db()->prepare("SELECT COUNT(*) AS total, SUM(CASE WHEN statut='terminee' THEN 1 ELSE 0 END) AS done FROM projet_taches WHERE phase_id=? AND parent_id IS NULL");
        $tasks->execute([$phaseId]); $tk = $tasks->fetch();
        $av = $tk['total']>0 ? round($tk['done']/$tk['total']*100) : 0;
        $phaseStatut = $av>=100 ? 'terminee' : ($av>0 ? 'en_cours' : 'a_faire');
        db()->prepare("UPDATE projet_phases SET statut=? WHERE id=?")->execute([$phaseStatut,$phaseId]);
    }
}



// ── GED — Vérification des droits ────────────────────────────────
function gedCheckAccess(string $itemId, string $userId, string $role, array $parentIds=[], bool $strictDefault=false): string {
    // super_admin = accès total
    if ($role === 'super_admin') return 'admin';
    // Chercher les groupes de l'utilisateur
    $gStmt = db()->prepare("SELECT groupe_id FROM ged_groupe_membres WHERE crm_user_id=?");
    $gStmt->execute([$userId]); $groupeIds = $gStmt->fetchAll(PDO::FETCH_COLUMN);
    // Items à vérifier : l'item lui-même + ses parents
    $items = array_merge([$itemId], $parentIds);
    $bestAccess = null; // null = aucune règle trouvée
    $order = ['aucun'=>0,'lecture'=>1,'ecriture'=>2,'admin'=>3];
    $hasAnyRule = false;
    foreach ($items as $iid) {
        // Vérifier droit utilisateur direct
        $s = db()->prepare("SELECT acces FROM ged_droits WHERE drive_item_id=? AND crm_user_id=? AND herite=1 LIMIT 1");
        $s->execute([$iid,$userId]); $r=$s->fetch();
        if ($r) { $hasAnyRule=true; if (($order[$r['acces']]??0) > ($order[$bestAccess??'aucun']??0)) $bestAccess=$r['acces']; }
        // Vérifier droit via groupes
        if ($groupeIds) {
            $ph = implode(',',array_fill(0,count($groupeIds),'?'));
            $s2 = db()->prepare("SELECT MAX(CASE acces WHEN 'admin' THEN 3 WHEN 'ecriture' THEN 2 WHEN 'lecture' THEN 1 ELSE 0 END) AS best, COUNT(*) AS nb FROM ged_droits WHERE drive_item_id=? AND groupe_id IN ($ph) AND herite=1");
            $s2->execute(array_merge([$iid],$groupeIds)); $r2=$s2->fetch();
            if ((int)($r2['nb']??0)>0) {
                $hasAnyRule=true;
                $accesLevels=['aucun','lecture','ecriture','admin'];
                $lvl=(int)($r2['best']??0);
                if ($lvl>($order[$bestAccess??'aucun']??0)) $bestAccess=$accesLevels[$lvl];
            }
        }
        // Pour l'item lui-même (pas seulement hérité)
        if ($iid===$itemId) {
            $s3=db()->prepare("SELECT acces FROM ged_droits WHERE drive_item_id=? AND crm_user_id=? LIMIT 1");
            $s3->execute([$iid,$userId]); $r3=$s3->fetch();
            if ($r3) { $hasAnyRule=true; if (($order[$r3['acces']]??0)>($order[$bestAccess??'aucun']??0)) $bestAccess=$r3['acces']; }
        }
    }
    // Si aucune règle configurée :
    // - Mode strict (racine) → aucun accès
    // - Mode normal (sous-dossier) → lecture par défaut
    if (!$hasAnyRule) return $strictDefault ? 'aucun' : 'lecture';
    return $bestAccess ?? 'lecture';
}

// ── GED — Helper functions ────────────────────────────────────────
function gedToken(): string {
    $stmt = db()->prepare("SELECT valeur FROM app_settings WHERE cle='ged_access_token'");
    $stmt->execute(); $row = $stmt->fetch();
    if ($row && $row['valeur']) {
        $data = json_decode($row['valeur'], true);
        if (!empty($data['access_token']) && !empty($data['expires_at']) && $data['expires_at'] > time()+60)
            return $data['access_token'];
    }
    $stmt2 = db()->prepare("SELECT valeur FROM app_settings WHERE cle='ged_refresh_token'");
    $stmt2->execute(); $row2 = $stmt2->fetch();
    if (empty($row2['valeur'])) err('GED non autorisée. Allez dans Paramètres → GED → Autoriser.');
    $ch = curl_init('https://oauth2.googleapis.com/token');
    curl_setopt_array($ch,[CURLOPT_RETURNTRANSFER=>true,CURLOPT_POST=>true,
        CURLOPT_POSTFIELDS=>http_build_query(['client_id'=>GED_CLIENT_ID,'client_secret'=>GED_CLIENT_SECRET,'refresh_token'=>$row2['valeur'],'grant_type'=>'refresh_token']),
        CURLOPT_HTTPHEADER=>['Content-Type: application/x-www-form-urlencoded']]);
    $res=curl_exec($ch); curl_close($ch);
    $data=json_decode($res,true);
    if (empty($data['access_token'])) err('Token Google invalide: '.($data['error_description']??$res));
    $cache=json_encode(['access_token'=>$data['access_token'],'expires_at'=>time()+($data['expires_in']??3600)]);
    db()->prepare("INSERT INTO app_settings (cle,valeur,label,description) VALUES ('ged_access_token',?,'GED','') ON DUPLICATE KEY UPDATE valeur=?")->execute([$cache,$cache]);
    return $data['access_token'];
}

function gedApi(string $url, string $token, string $method='GET', mixed $body=null, array $extraHeaders=[]): array {
    $ch=curl_init($url);
    $headers=array_merge(['Authorization: Bearer '.$token,'Accept: application/json'],$extraHeaders);
    curl_setopt_array($ch,[CURLOPT_RETURNTRANSFER=>true,CURLOPT_CUSTOMREQUEST=>$method,CURLOPT_HTTPHEADER=>$headers]);
    if ($body!==null) {
        if (is_array($body)) { curl_setopt($ch,CURLOPT_POSTFIELDS,json_encode($body)); $headers[]='Content-Type: application/json'; curl_setopt($ch,CURLOPT_HTTPHEADER,$headers); }
        else curl_setopt($ch,CURLOPT_POSTFIELDS,$body);
    }
    $res=curl_exec($ch); $code=curl_getinfo($ch,CURLINFO_HTTP_CODE); curl_close($ch);
    $data=json_decode($res,true)??[];
    if ($code>=400) err('Google Drive API ('.$code.'): '.($data['error']['message']??$res));
    return $data;
}


// ── GED Inbox helper (défini dans ged_inbox.php) ─────────────────

switch ($action) {

// ════════════════════════════════════════════════════════════════
case 'ping':
    ob_clean();
    echo json_encode(['ok'=>true,'php'=>PHP_VERSION,'time'=>date('Y-m-d H:i:s'),'uploads'=>is_writable(__DIR__.'/uploads')]);
    exit;

case 'ping_tickets':
    // Diagnostic tickets — accessible CRM uniquement
    crmAuth();
    $tickets = db()->query("SELECT t.id, t.numero, t.titre, t.societe_id, t.contact_id, t.statut, s.nom AS societe_nom FROM tickets t LEFT JOIN societes s ON s.id=t.societe_id ORDER BY t.date_creation DESC LIMIT 10")->fetchAll();
    $contacts = db()->query("SELECT c.id, c.prenom, c.nom, c.email, c.societe_id, s.nom AS societe_nom FROM contacts c LEFT JOIN societes s ON s.id=c.societe_id WHERE c.statut_espace='active' LIMIT 20")->fetchAll();
    ob_clean();
    echo json_encode(['ok'=>true,'tickets'=>$tickets,'contacts_actifs'=>$contacts]);
    exit;

case 'ping_compta':
    // Diagnostic complet pour le module comptabilité
    $results = ['php'=>PHP_VERSION,'db'=>false,'table_ecritures'=>false,'columns'=>[],'error'=>null];
    try {
        db()->query('SELECT 1'); $results['db'] = true;
        $cols = db()->query("SHOW COLUMNS FROM ecritures")->fetchAll(PDO::FETCH_COLUMN);
        $results['table_ecritures'] = true;
        $results['columns'] = $cols;
        $count = db()->query("SELECT COUNT(*) FROM ecritures")->fetchColumn();
        $results['row_count'] = (int)$count;
    } catch (Exception $e) {
        $results['error'] = $e->getMessage();
    }
    ob_clean();
    echo json_encode(['ok'=>true] + $results);
    exit;

// ════════════════════════════════════════════════════════════════
//  AUTH CRM
// ════════════════════════════════════════════════════════════════
case 'test_login':
    // Diagnostic login sans auth
    $b=body(); $login=clean($b['login']??'test');
    try {
        $stmt=db()->prepare('SELECT id,login,pwd_hash,actif FROM crm_users WHERE login=?');
        $stmt->execute([$login]); $u=$stmt->fetch();
        if (!$u) { ok(['step'=>'user_not_found','login'=>$login]); }
        $verify = password_verify($b['password']??'', $u['pwd_hash']??'');
        // Test insert session
        $token = bin2hex(random_bytes(32));
        $sql = "INSERT INTO sessions (id,entity_id,ip,user_agent,expires_at) VALUES (?,?,?,?,DATE_ADD(NOW(),INTERVAL 8 HOUR))";
        db()->prepare($sql)->execute([$token, 'crm:'.$u['id'], '127.0.0.1', 'test']);
        db()->prepare("DELETE FROM sessions WHERE id=?")->execute([$token]);
        ok(['step'=>'all_ok','user'=>$u['login'],'actif'=>$u['actif'],'verify'=>$verify]);
    } catch (Exception $e) {
        ok(['step'=>'exception','error'=>$e->getMessage(),'line'=>$e->getLine()]);
    }

case 'crm_login':
    if ($method!=='POST') err('Méthode non autorisée',405);
    $b=body(); $login=clean($b['login']??''); $pwd=$b['password']??'';
    if (!$login||!$pwd) err('Identifiant et mot de passe requis');
    $stmt=db()->prepare('SELECT * FROM crm_users WHERE login=? AND actif=1');
    $stmt->execute([$login]); $u=$stmt->fetch();
    if (!$u||!password_verify($pwd,$u['pwd_hash'])) err('Identifiant ou mot de passe incorrect');
    db()->prepare('UPDATE crm_users SET derniere_cnx=NOW() WHERE id=?')->execute([$u['id']]);
    db()->prepare("DELETE FROM sessions WHERE entity_id=? AND expires_at<NOW()")->execute(['crm:'.$u['id']]);
    $remember = (bool)($b['remember_me']??false);
    $token=mkSession('crm:'.$u['id'], $remember);
    ok(['token'=>$token,'user'=>fmtUser($u),'remember'=>$remember]);

case 'crm_logout':
    $t=getToken(); if($t) db()->prepare('DELETE FROM sessions WHERE id=?')->execute([$t]); ok();

case 'crm_revoke_all':
    $u=crmAuth();
    db()->prepare("DELETE FROM sessions WHERE entity_id=?")->execute(['crm:'.$u['id']]);
    ok(['message'=>'Toutes les sessions ont été révoquées.']);

case 'crm_check':
    $u=crmAuth(); ok(['user'=>fmtUser($u)]);

case 'crm_change_password':
    $u=crmAuth(); $b=body();
    if (!password_verify($b['currentPassword']??'',$u['pwd_hash'])) err('Mot de passe actuel incorrect');
    $new=$b['newPassword']??'';
    if (strlen($new)<8) err('Au moins 8 caractères');
    if (!preg_match('/[A-Z]/',$new)) err('Au moins une majuscule');
    if (!preg_match('/[0-9]/',$new)) err('Au moins un chiffre');
    db()->prepare('UPDATE crm_users SET pwd_hash=? WHERE id=?')->execute([password_hash($new,PASSWORD_BCRYPT,['cost'=>12]),$u['id']]);
    ok();

// ════════════════════════════════════════════════════════════════
//  UTILISATEURS CRM
// ════════════════════════════════════════════════════════════════
case 'crm_users_list':
    superAdmin();
    ok(['users'=>array_map('fmtUser',db()->query('SELECT * FROM crm_users ORDER BY date_creation DESC')->fetchAll())]);

case 'crm_user_create':
    superAdmin(); $b=body();
    $login=clean($b['login']??''); $email=filter_var(trim($b['email']??''),FILTER_VALIDATE_EMAIL);
    $pwd=$b['password']??'';
    if (!$login||!$email) err('Login et email obligatoires');
    if (strlen($pwd)<8||!preg_match('/[A-Z]/',$pwd)||!preg_match('/[0-9]/',$pwd)) err('Mot de passe : 8 car. min, 1 maj., 1 chiffre');
    $chk=db()->prepare('SELECT id FROM crm_users WHERE login=? OR email=?'); $chk->execute([$login,$email]);
    if ($chk->fetch()) err('Ce login ou email existe déjà');
    $id=uuid();
    db()->prepare('INSERT INTO crm_users (id,login,email,pwd_hash,nom,prenom,role) VALUES (?,?,?,?,?,?,?)')
        ->execute([$id,$login,$email,password_hash($pwd,PASSWORD_BCRYPT,['cost'=>12]),clean($b['nom']??''),clean($b['prenom']??''),in_array($b['role']??'',['super_admin','user'])?$b['role']:'user']);
    $row=db()->prepare('SELECT * FROM crm_users WHERE id=?'); $row->execute([$id]);
    ok(['user'=>fmtUser($row->fetch())]);

case 'crm_user_update':
    superAdmin(); $b=body(); $id=$b['id']??'';
    db()->prepare('UPDATE crm_users SET nom=?,prenom=?,email=?,role=?,actif=? WHERE id=?')
        ->execute([clean($b['nom']??''),clean($b['prenom']??''),clean($b['email']??''),in_array($b['role']??'',['super_admin','user'])?$b['role']:'user',($b['actif']??true)?1:0,$id]);
    if (!empty($b['password'])&&strlen($b['password'])>=8)
        db()->prepare('UPDATE crm_users SET pwd_hash=? WHERE id=?')->execute([password_hash($b['password'],PASSWORD_BCRYPT,['cost'=>12]),$id]);
    ok();

case 'crm_user_delete':
    $me=superAdmin(); $b=body(); $id=$b['id']??'';
    if ($me['id']===$id) err("Vous ne pouvez pas supprimer votre propre compte");
    db()->prepare('DELETE FROM sessions WHERE entity_id=?')->execute(['crm:'.$id]);
    db()->prepare('DELETE FROM crm_users WHERE id=?')->execute([$id]);
    ok();

// ════════════════════════════════════════════════════════════════
//  DASHBOARD
// ════════════════════════════════════════════════════════════════
case 'crm_dashboard':
    crmAuth();
    $mois = date('Y-m');
    $annee = date('Y');

    // ── CRM ────────────────────────────────────────────────────
    $s=db()->query("SELECT
        COUNT(*) AS total,
        SUM(type_contact='prospect') AS prospects,
        SUM(type_contact='client') AS clients_actifs,
        SUM(pipeline_stade IS NOT NULL AND pipeline_stade NOT IN ('gagne','perdu')) AS pipeline_actif,
        COALESCE(SUM(pipeline_montant*COALESCE(pipeline_proba,50)/100),0) AS pipeline_pondere,
        SUM(prochain_contact IS NOT NULL AND prochain_contact<=CURDATE()) AS relances_retard,
        SUM(prochain_contact IS NOT NULL AND prochain_contact BETWEEN CURDATE() AND DATE_ADD(CURDATE(),INTERVAL 7 DAY)) AS relances_7j
        FROM societes")->fetch();
    $mC=db()->query("SELECT COUNT(*) AS n FROM missions WHERE statut='en_cours'")->fetch()['n'];
    $lastAct=db()->query("SELECT i.*,s.nom AS soc_nom,CONCAT(c.prenom,' ',c.nom) AS contact_nom,u.prenom AS crm_prenom,u.nom AS crm_nom FROM interactions i JOIN societes s ON s.id=i.societe_id LEFT JOIN contacts c ON c.id=i.contact_id JOIN crm_users u ON u.id=i.crm_user_id ORDER BY i.date DESC LIMIT 6")->fetchAll();
    $relances=db()->query("SELECT id,nom,prochain_contact,prochain_contact_objet,type_contact FROM societes WHERE prochain_contact IS NOT NULL AND prochain_contact<=DATE_ADD(CURDATE(),INTERVAL 7 DAY) ORDER BY prochain_contact ASC LIMIT 8")->fetchAll();
    $pipeline=db()->query("SELECT pipeline_stade,COUNT(*) AS n,SUM(pipeline_montant) AS total FROM societes WHERE pipeline_stade IS NOT NULL AND pipeline_stade NOT IN ('gagne','perdu') GROUP BY pipeline_stade")->fetchAll();

    // ── COMPTABILITÉ ────────────────────────────────────────────
    // Mois en cours
    $comptaMois = db()->prepare("SELECT
        COALESCE(SUM(CASE WHEN type='facture_emise' THEN montant_ht ELSE 0 END),0) AS recettes_ht,
        COALESCE(SUM(CASE WHEN type IN ('charge','note_frais') THEN montant_ht ELSE 0 END),0) AS charges_ht,
        COALESCE(SUM(CASE WHEN type='facture_emise' THEN montant_ht*(1+tva_taux/100) ELSE 0 END),0) AS recettes_ttc
        FROM ecritures WHERE periode=?");
    $comptaMois->execute([$mois]); $cm=$comptaMois->fetch();
    // YTD (année en cours)
    $comptaYTD = db()->prepare("SELECT
        COALESCE(SUM(CASE WHEN type='facture_emise' THEN montant_ht ELSE 0 END),0) AS recettes_ht,
        COALESCE(SUM(CASE WHEN type IN ('charge','note_frais') THEN montant_ht ELSE 0 END),0) AS charges_ht,
        COALESCE(SUM(CASE WHEN type='facture_emise' THEN montant_ht*(1+tva_taux/100) ELSE 0 END),0) AS recettes_ttc
        FROM ecritures WHERE periode LIKE ?");
    $comptaYTD->execute([$annee.'-%']); $cy=$comptaYTD->fetch();
    // Factures en attente de paiement (écritures)
    $factAttente=db()->prepare("SELECT COUNT(*) AS n, COALESCE(SUM(montant_ht),0) AS total FROM ecritures WHERE type='facture_emise' AND statut_paiement='en_attente'");
    $factAttente->execute(); $fa=$factAttente->fetch();
    // Non exportées
    $nonExporte=db()->prepare("SELECT COUNT(*) AS n FROM ecritures WHERE statut_export='non_exporte'");
    $nonExporte->execute(); $ne=$nonExporte->fetch();

    // ── TICKETS ────────────────────────────────────────────────
    $tkStats=db()->query("SELECT statut,COUNT(*) AS n FROM tickets GROUP BY statut")->fetchAll();
    $tkRecents=db()->query("SELECT t.id,t.numero,t.titre,t.statut,t.categorie,t.date_creation,s.nom AS societe_nom FROM tickets t LEFT JOIN societes s ON s.id=t.societe_id WHERE t.statut NOT IN ('ferme') ORDER BY t.date_maj DESC,t.date_creation DESC LIMIT 5")->fetchAll();

    ok([
        // CRM
        'stats'          => array_map(fn($v)=>is_numeric($v)?(float)$v:$v,$s),
        'missionsEnCours'=> (int)$mC,
        'lastActivity'   => $lastAct,
        'relances'       => $relances,
        'pipeline'       => $pipeline,
        // Comptabilité
        'comptaMois'     => array_map(fn($v)=>round((float)$v,2),$cm),
        'comptaYTD'      => array_map(fn($v)=>round((float)$v,2),$cy),
        'factAttente'    => ['n'=>(int)$fa['n'],'total'=>round((float)$fa['total'],2)],
        'nonExporte'     => (int)$ne['n'],
        'moisLabel'      => (function(){
            $mois=['01'=>'Janvier','02'=>'Février','03'=>'Mars','04'=>'Avril','05'=>'Mai','06'=>'Juin',
                   '07'=>'Juillet','08'=>'Août','09'=>'Septembre','10'=>'Octobre','11'=>'Novembre','12'=>'Décembre'];
            return ($mois[date('m')]??date('m')).' '.date('Y');
        })(),
        'anneeLabel'     => $annee,
        // Tickets
        'ticketsStats'   => $tkStats,
        'ticketsRecents' => $tkRecents,
    ]);

// ════════════════════════════════════════════════════════════════
//  SOCIÉTÉS
// ════════════════════════════════════════════════════════════════
case 'societes_list':
    crmAuth();
    $type=$_GET['type']??''; $q=$_GET['q']??''; $stage=$_GET['stage']??'';
    $sql="SELECT s.*,(SELECT COUNT(*) FROM contacts c WHERE c.societe_id=s.id) AS nb_contacts,(SELECT COUNT(*) FROM documents d WHERE d.societe_id=s.id) AS nb_documents,(SELECT COUNT(*) FROM missions m WHERE m.societe_id=s.id) AS nb_missions,(SELECT COUNT(*) FROM factures f WHERE f.societe_id=s.id) AS nb_factures,(SELECT COUNT(*) FROM interactions i WHERE i.societe_id=s.id) AS nb_interactions FROM societes s WHERE 1=1";
    $params=[];
    if ($type){$sql.=" AND s.type_contact=?";$params[]=$type;}
    if ($stage){$sql.=" AND s.pipeline_stade=?";$params[]=$stage;}
    if ($q){$sql.=" AND (s.nom LIKE ? OR s.ville LIKE ? OR s.secteur LIKE ?)";$p='%'.$q.'%';$params=array_merge($params,[$p,$p,$p]);}
    $sql.=" ORDER BY s.date_creation DESC";
    $stmt=db()->prepare($sql); $stmt->execute($params);
    ok(['societes'=>array_map('fmtSociete',$stmt->fetchAll())]);

case 'societe_get':
    crmAuth(); $id=$_GET['id']??'';
    $stmt=db()->prepare("SELECT s.*,(SELECT COUNT(*) FROM contacts c WHERE c.societe_id=s.id) AS nb_contacts,(SELECT COUNT(*) FROM documents d WHERE d.societe_id=s.id) AS nb_documents,(SELECT COUNT(*) FROM missions m WHERE m.societe_id=s.id) AS nb_missions,(SELECT COUNT(*) FROM factures f WHERE f.societe_id=s.id) AS nb_factures,(SELECT COUNT(*) FROM interactions i WHERE i.societe_id=s.id) AS nb_interactions FROM societes s WHERE s.id=?");
    $stmt->execute([$id]); $s=$stmt->fetch();
    if (!$s) err('Société introuvable',404);
    $contacts=db()->prepare('SELECT * FROM contacts WHERE societe_id=? ORDER BY est_principal DESC,nom ASC'); $contacts->execute([$id]);
    $docs=db()->prepare('SELECT d.*,u.prenom AS crm_prenom,u.nom AS crm_nom FROM documents d LEFT JOIN crm_users u ON u.id=d.crm_user_id WHERE d.societe_id=? ORDER BY d.date_creation DESC'); $docs->execute([$id]);
    $ints=db()->prepare('SELECT i.*,c.prenom AS contact_prenom,c.nom AS contact_nom,u.prenom AS crm_prenom,u.nom AS crm_nom FROM interactions i LEFT JOIN contacts c ON c.id=i.contact_id JOIN crm_users u ON u.id=i.crm_user_id WHERE i.societe_id=? ORDER BY i.date DESC'); $ints->execute([$id]);
    $missions=db()->prepare('SELECT * FROM missions WHERE societe_id=? ORDER BY date_creation DESC'); $missions->execute([$id]);
    $factures=db()->prepare("SELECT f.*,s.nom AS societe_nom FROM factures f JOIN societes s ON s.id=f.societe_id WHERE f.societe_id=? ORDER BY f.date_creation DESC"); $factures->execute([$id]);
    ok(['societe'=>fmtSociete($s),'contacts'=>array_map('fmtContact',$contacts->fetchAll()),'documents'=>array_map('fmtDocument',$docs->fetchAll()),'interactions'=>array_map('fmtInteraction',$ints->fetchAll()),'missions'=>array_map('fmtMission',$missions->fetchAll()),'factures'=>array_map('fmtFacture',$factures->fetchAll())]);

case 'societe_create':
    crmAuth(); $b=body();
    if (!trim($b['nom']??'')) err('Le nom de la société est obligatoire');
    $id=uuid();
    db()->prepare("INSERT INTO societes (id,nom,siren,siret,tva_intra,site_web,linkedin,adresse1,adresse2,code_postal,ville,pays,type_contact,source,secteur,taille,ca_fourchette,prochain_contact,prochain_contact_objet,notes,pipeline_stade,pipeline_montant,pipeline_proba,pipeline_date_close,crm_user_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)")
        ->execute([$id,clean($b['nom']),clean($b['siren']??''),clean($b['siret']??''),clean($b['tvaIntra']??''),clean($b['siteWeb']??''),clean($b['linkedin']??''),clean($b['adresse1']??''),clean($b['adresse2']??''),clean($b['codePostal']??''),clean($b['ville']??''),clean($b['pays']??'France'),in_array($b['typeContact']??'',['prospect','client','partenaire','prescripteur','prestataire','fournisseur','reseau','inactif','perdu'])?$b['typeContact']:'prospect',$b['source']??null,clean($b['secteur']??''),$b['taille']??null,$b['caFourchette']??null,$b['prochainContact']??null,clean($b['prochainContactObjet']??''),clean($b['notes']??''),$b['pipelineStade']??null,$b['pipelineMontant']?(float)$b['pipelineMontant']:null,$b['pipelineProba']?(int)$b['pipelineProba']:null,$b['pipelineDateClose']??null,$b['crmUserId']??null]);
    $stmt=db()->prepare("SELECT s.*,0 AS nb_contacts,0 AS nb_documents,0 AS nb_missions,0 AS nb_factures,0 AS nb_interactions FROM societes s WHERE s.id=?"); $stmt->execute([$id]);
    ok(['societe'=>fmtSociete($stmt->fetch())]);

case 'societe_update':
    crmAuth(); $b=body(); $id=$b['id']??'';
    db()->prepare("UPDATE societes SET nom=?,siren=?,siret=?,tva_intra=?,site_web=?,linkedin=?,adresse1=?,adresse2=?,code_postal=?,ville=?,pays=?,type_contact=?,source=?,secteur=?,taille=?,ca_fourchette=?,prochain_contact=?,prochain_contact_objet=?,notes=?,pipeline_stade=?,pipeline_montant=?,pipeline_proba=?,pipeline_date_close=?,crm_user_id=? WHERE id=?")
        ->execute([clean($b['nom']??''),clean($b['siren']??''),clean($b['siret']??''),clean($b['tvaIntra']??''),clean($b['siteWeb']??''),clean($b['linkedin']??''),clean($b['adresse1']??''),clean($b['adresse2']??''),clean($b['codePostal']??''),clean($b['ville']??''),clean($b['pays']??'France'),in_array($b['typeContact']??'',['prospect','client','partenaire','prescripteur','prestataire','fournisseur','reseau','inactif','perdu'])?$b['typeContact']:'prospect',$b['source']??null,clean($b['secteur']??''),$b['taille']??null,$b['caFourchette']??null,$b['prochainContact']??null,clean($b['prochainContactObjet']??''),clean($b['notes']??''),$b['pipelineStade']??null,$b['pipelineMontant']?(float)$b['pipelineMontant']:null,$b['pipelineProba']?(int)$b['pipelineProba']:null,$b['pipelineDateClose']??null,$b['crmUserId']??null,$id]);
    ok();

case 'societe_update_access':
    superAdmin(); $b=body(); $id=$b['id']??'';
    db()->prepare('UPDATE societes SET acces_factures=?,acces_tickets=? WHERE id=?')
        ->execute([$b['accesFactures']?1:0, $b['accesTickets']?1:0, $id]);
    ok();

case 'contact_update_access':
    superAdmin(); $b=body(); $id=$b['id']??'';
    // null = hérite société, true/false = surcharge
    $af = array_key_exists('accesFactures',$b) && $b['accesFactures']!==null ? ($b['accesFactures']?1:0) : null;
    $at = array_key_exists('accesTickets', $b) && $b['accesTickets'] !==null ? ($b['accesTickets'] ?1:0) : null;
    db()->prepare('UPDATE contacts SET acces_factures=?,acces_tickets=? WHERE id=?')
        ->execute([$af, $at, $id]);
    ok();

case 'societe_delete':
    superAdmin(); $b=body(); $id=$b['id']??'';
    // Supprimer les fichiers sur disque
    $docs=db()->prepare('SELECT file_path FROM documents WHERE societe_id=?'); $docs->execute([$id]);
    foreach ($docs->fetchAll() as $d) { $fp=__DIR__.'/'.$d['file_path']; if (file_exists($fp)) unlink($fp); }
    $facts=db()->prepare('SELECT file_path FROM factures WHERE societe_id=? AND file_path IS NOT NULL'); $facts->execute([$id]);
    foreach ($facts->fetchAll() as $f) { $fp=__DIR__.'/'.$f['file_path']; if (file_exists($fp)) unlink($fp); }
    db()->prepare('DELETE FROM societes WHERE id=?')->execute([$id]);
    ok();

// ════════════════════════════════════════════════════════════════
//  CONTACTS
// ════════════════════════════════════════════════════════════════
case 'contacts_list':
    crmAuth(); $sid=$_GET['societe_id']??'';
    if ($sid) { $stmt=db()->prepare('SELECT * FROM contacts WHERE societe_id=? ORDER BY est_principal DESC,nom ASC'); $stmt->execute([$sid]); }
    else { $stmt=db()->query('SELECT c.*,s.nom AS societe_nom FROM contacts c JOIN societes s ON s.id=c.societe_id ORDER BY c.nom ASC'); }
    ok(['contacts'=>array_map('fmtContact',$stmt->fetchAll())]);

case 'contact_create':
    crmAuth(); $b=body();
    if (!trim($b['nom']??'')) err('Le nom du contact est obligatoire');
    if (!trim($b['societeId']??'')) err('La société est obligatoire');
    // Vérifier unicité email si fourni
    if (!empty($b['email'])) {
        $chk=db()->prepare('SELECT id FROM contacts WHERE email=?'); $chk->execute([strtolower($b['email'])]);
        if ($chk->fetch()) err('Un contact avec cet email existe déjà');
    }
    // Si premier contact, le marquer principal
    $nbC=db()->prepare('SELECT COUNT(*) FROM contacts WHERE societe_id=?'); $nbC->execute([$b['societeId']]); $isPrincipal=$nbC->fetchColumn()===0?1:(($b['estPrincipal']??false)?1:0);
    if ($isPrincipal) db()->prepare('UPDATE contacts SET est_principal=0 WHERE societe_id=?')->execute([$b['societeId']]);
    $id=uuid();
    db()->prepare('INSERT INTO contacts (id,societe_id,prenom,nom,poste,email,telephone,telephone2,est_principal,notes) VALUES (?,?,?,?,?,?,?,?,?,?)')
        ->execute([$id,clean($b['societeId']),clean($b['prenom']??''),clean($b['nom']),clean($b['poste']??''),strtolower(clean($b['email']??''))?:null,clean($b['telephone']??''),clean($b['telephone2']??''),$isPrincipal,clean($b['notes']??'')]);
    $row=db()->prepare('SELECT * FROM contacts WHERE id=?'); $row->execute([$id]);
    ok(['contact'=>fmtContact($row->fetch())]);

case 'contact_update':
    crmAuth(); $b=body(); $id=$b['id']??'';
    if ($b['estPrincipal']??false) db()->prepare('UPDATE contacts SET est_principal=0 WHERE societe_id=COALESCE(?,( SELECT societe_id FROM contacts WHERE id=? ))')->execute([$b['societeId']??null,$id]);
    db()->prepare('UPDATE contacts SET societe_id=COALESCE(?,societe_id),prenom=?,nom=?,poste=?,email=?,telephone=?,telephone2=?,linkedin=?,est_principal=?,notes=? WHERE id=?')
        ->execute([$b['societeId']??null,clean($b['prenom']??''),clean($b['nom']??''),clean($b['poste']??''),strtolower(clean($b['email']??''))?:null,clean($b['telephone']??''),clean($b['telephone2']??''),clean($b['linkedin']??''),($b['estPrincipal']??false)?1:0,clean($b['notes']??''),$id]);
    ok();

case 'contact_delete':
    superAdmin(); $b=body();
    db()->prepare('DELETE FROM contacts WHERE id=?')->execute([$b['id']??'']);
    ok();

// ════════════════════════════════════════════════════════════════
//  DOCUMENTS (stockage sur disque)
// ════════════════════════════════════════════════════════════════
case 'doc_upload':
    $u=crmAuth();
    // Multipart upload
    if (empty($_FILES['file'])||$_FILES['file']['error']!==UPLOAD_ERR_OK) err('Aucun fichier reçu ou erreur upload');
    $societeId=clean($_POST['societeId']??''); if (!$societeId) err('societeId manquant');
    // Vérifier que la société existe
    $chk=db()->prepare('SELECT id FROM societes WHERE id=?'); $chk->execute([$societeId]); if (!$chk->fetch()) err('Société introuvable',404);
    $file=$_FILES['file'];
    $maxSize=20*1024*1024; // 20 Mo
    if ($file['size']>$maxSize) err('Fichier trop volumineux (max 20 Mo)');
    // Extensions autorisées
    $allowedExts=['pdf','doc','docx','xls','xlsx','ppt','pptx','jpg','jpeg','png','txt','csv'];
    $ext=strtolower(pathinfo($file['name'],PATHINFO_EXTENSION));
    if (!in_array($ext,$allowedExts)) err('Type de fichier non autorisé ('.$ext.')');
    // Créer le dossier
    $dir=__DIR__.'/uploads/docs/'.$societeId;
    if (!is_dir($dir)) mkdir($dir,0755,true);
    // Nom unique
    $fileId=uuid();
    $safeName=preg_replace('/[^a-zA-Z0-9\-_\.]/','-',pathinfo($file['name'],PATHINFO_FILENAME));
    $diskName=$fileId.'-'.$safeName.'.'.$ext;
    $filePath='uploads/docs/'.$societeId.'/'.$diskName;
    $absPath=__DIR__.'/'.$filePath;
    if (!move_uploaded_file($file['tmp_name'],$absPath)) err('Erreur lors de la sauvegarde du fichier');
    // Métadonnées
    $mimes=['pdf'=>'application/pdf','doc'=>'application/msword','docx'=>'application/vnd.openxmlformats-officedocument.wordprocessingml.document','xls'=>'application/vnd.ms-excel','xlsx'=>'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet','jpg'=>'image/jpeg','jpeg'=>'image/jpeg','png'=>'image/png','txt'=>'text/plain','csv'=>'text/csv','ppt'=>'application/vnd.ms-powerpoint','pptx'=>'application/vnd.openxmlformats-officedocument.presentationml.presentation'];
    $mime=$mimes[$ext]??'application/octet-stream';
    $id=uuid();
    $cats=['devis','proposition','contrat','brochure','rapport','autre'];
    $cat=in_array($_POST['categorie']??'',array_merge($cats,[]))? $_POST['categorie']:'autre';
    db()->prepare('INSERT INTO documents (id,societe_id,categorie,titre,description,version,tags,file_path,file_name,file_ext,file_size,mime_type,crm_user_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)')
        ->execute([$id,$societeId,$cat,clean($_POST['titre']??$file['name']),clean($_POST['description']??''),clean($_POST['version']??''),clean($_POST['tags']??''),$filePath,$file['name'],$ext,$file['size'],$mime,$u['id']]);
    $row=db()->prepare('SELECT d.*,u.prenom AS crm_prenom,u.nom AS crm_nom FROM documents d LEFT JOIN crm_users u ON u.id=d.crm_user_id WHERE d.id=?'); $row->execute([$id]);
    ok(['document'=>fmtDocument($row->fetch())]);

case 'doc_download':
    // Accessible CRM + espace client (avec token)
    $token=getToken(); $canAccess=false; $contactSocieteId=null;
    if ($token) {
        // CRM?
        $adm=db()->prepare("SELECT id FROM sessions WHERE id=? AND entity_id LIKE 'crm:%' AND expires_at>NOW()"); $adm->execute([$token]);
        if ($adm->fetch()) { $canAccess=true; }
        else {
            // Contact?
            $cli=db()->prepare("SELECT c.societe_id FROM sessions s JOIN contacts c ON c.id=s.entity_id WHERE s.id=? AND s.expires_at>NOW()"); $cli->execute([$token]); $row=$cli->fetch();
            if ($row) { $canAccess=true; $contactSocieteId=$row['societe_id']; }
        }
    }
    if (!$canAccess) err('Accès refusé',403);
    $id=$_GET['id']??''; $type=$_GET['type']??'doc';
    if ($type==='facture') {
        $stmt=db()->prepare('SELECT f.*,s.id AS soc_id FROM factures f JOIN societes s ON s.id=f.societe_id WHERE f.id=?'); $stmt->execute([$id]); $f=$stmt->fetch();
        if (!$f||!$f['file_path']) err('Fichier introuvable',404);
        if ($contactSocieteId&&$f['soc_id']!==$contactSocieteId) err('Accès refusé',403);
        $absPath=__DIR__.'/'.$f['file_path'];
        if (!file_exists($absPath)) err('Fichier non trouvé sur le serveur',404);
        ob_clean(); header('Content-Type: application/pdf'); header('Content-Disposition: attachment; filename="'.($f['file_name']?:'facture.pdf').'"'); header('Content-Length: '.filesize($absPath)); readfile($absPath); exit;
    } else {
        $stmt=db()->prepare('SELECT * FROM documents WHERE id=?'); $stmt->execute([$id]); $d=$stmt->fetch();
        if (!$d) err('Document introuvable',404);
        if ($contactSocieteId&&$d['societe_id']!==$contactSocieteId) err('Accès refusé',403);
        $absPath=__DIR__.'/'.$d['file_path'];
        if (!file_exists($absPath)) err('Fichier non trouvé sur le serveur',404);
        $inline=in_array($d['file_ext'],['pdf','jpg','jpeg','png']);
        ob_clean(); header('Content-Type: '.($d['mime_type']?:'application/octet-stream')); header('Content-Disposition: '.($inline?'inline':'attachment').'; filename="'.$d['file_name'].'"'); header('Content-Length: '.filesize($absPath)); readfile($absPath); exit;
    }

case 'doc_delete':
    superAdmin(); $b=body(); $id=$b['id']??'';
    $stmt=db()->prepare('SELECT file_path FROM documents WHERE id=?'); $stmt->execute([$id]); $d=$stmt->fetch();
    if ($d&&$d['file_path']) { $fp=__DIR__.'/'.$d['file_path']; if (file_exists($fp)) unlink($fp); }
    db()->prepare('DELETE FROM documents WHERE id=?')->execute([$id]);
    ok();

// ════════════════════════════════════════════════════════════════
//  INTERACTIONS
// ════════════════════════════════════════════════════════════════
case 'interaction_add':
    $u=crmAuth(); $b=body();
    if (!trim($b['resume']??'')) err('Le resume est obligatoire');
    $id=uuid();
    $vTypes=array('appel','email','rdv','reunion','proposition','relance','note','linkedin','visio','compte_rendu_appel','compte_rendu_visio','autre');
    $iType=in_array($b['type']??'',$vTypes)?$b['type']:'note';
    $nUrl=!empty($b['notionUrl'])?clean($b['notionUrl']):null;
    db()->prepare('INSERT INTO interactions (id,societe_id,contact_id,crm_user_id,type,date,duree_min,resume,suite,notion_url) VALUES (?,?,?,?,?,?,?,?,?,?)')
        ->execute(array($id,clean($b['societeId']),($b['contactId']??'')?:null,$u['id'],$iType,$b['date']??date('Y-m-d H:i:s'),$b['dureMin']?(int)$b['dureMin']:null,clean($b['resume']),clean($b['suite']??''),$nUrl));
    if (!empty($b['prochainContact'])) db()->prepare('UPDATE societes SET prochain_contact=?,prochain_contact_objet=? WHERE id=?')->execute(array($b['prochainContact'],clean($b['prochainContactObjet']??''),$b['societeId']));
    ok(array('id'=>$id));

case 'interaction_delete':
    superAdmin(); $b=body();
    db()->prepare('DELETE FROM interactions WHERE id=?')->execute([$b['id']??'']);
    ok();

// ════════════════════════════════════════════════════════════════
//  MISSIONS
// ════════════════════════════════════════════════════════════════
case 'missions_list':
    crmAuth(); $sid=$_GET['societe_id']??null;
    if ($sid) { $stmt=db()->prepare('SELECT * FROM missions WHERE societe_id=? ORDER BY date_creation DESC'); $stmt->execute([$sid]); }
    else { $stmt=db()->query('SELECT m.*,s.nom AS societe_nom FROM missions m JOIN societes s ON s.id=m.societe_id ORDER BY m.date_creation DESC'); }
    ok(['missions'=>array_map('fmtMission',$stmt->fetchAll())]);

case 'mission_add':
    crmAuth(); $b=body();
    if (!trim($b['intitule']??'')) err('Intitulé obligatoire');
    $id=uuid();
    db()->prepare('INSERT INTO missions (id,societe_id,crm_user_id,intitule,description,statut,date_debut,date_fin,jours_mois,tarif_jour) VALUES (?,?,?,?,?,?,?,?,?,?)')
        ->execute([$id,clean($b['societeId']),($b['crmUserId']??'')?:null,clean($b['intitule']),clean($b['description']??''),in_array($b['statut']??'',['en_cours','suspendue','terminee'])?$b['statut']:'en_cours',$b['dateDebut']??null,$b['dateFin']??null,$b['joursMois']?(float)$b['joursMois']:null,$b['tarifJour']?(float)$b['tarifJour']:null]);
    ok(['id'=>$id]);

case 'mission_update':
    crmAuth(); $b=body();
    db()->prepare('UPDATE missions SET intitule=?,description=?,statut=?,date_debut=?,date_fin=?,jours_mois=?,tarif_jour=? WHERE id=?')
        ->execute([clean($b['intitule']??''),clean($b['description']??''),in_array($b['statut']??'',['en_cours','suspendue','terminee'])?$b['statut']:'en_cours',$b['dateDebut']??null,$b['dateFin']??null,$b['joursMois']?(float)$b['joursMois']:null,$b['tarifJour']?(float)$b['tarifJour']:null,$b['id']??'']);
    ok();

case 'mission_delete':
    superAdmin(); $b=body();
    db()->prepare('DELETE FROM missions WHERE id=?')->execute([$b['id']??'']);
    ok();

// ════════════════════════════════════════════════════════════════
//  FACTURES
// ════════════════════════════════════════════════════════════════
case 'factures_list':
    crmAuth(); $sid=$_GET['societe_id']??null;
    if ($sid) { $stmt=db()->prepare("SELECT f.*,s.nom AS societe_nom FROM factures f JOIN societes s ON s.id=f.societe_id WHERE f.societe_id=? ORDER BY f.date_creation DESC"); $stmt->execute([$sid]); }
    else { $stmt=db()->query("SELECT f.*,s.nom AS societe_nom FROM factures f JOIN societes s ON s.id=f.societe_id ORDER BY f.date_creation DESC"); }
    ok(['factures'=>array_map('fmtFacture',$stmt->fetchAll())]);

case 'facture_add':
    // Upload multipart — mêmes champs $_POST + $_FILES['file'] optionnel
    $u = crmAuth();
    $societeId = clean($_POST['societeId'] ?? '');
    $numero    = clean($_POST['numero']    ?? '');
    if (!$numero)    err('Numéro de facture obligatoire');
    if (!$societeId) err('societeId manquant');
    // Vérifier que la société existe
    $chk = db()->prepare('SELECT id FROM societes WHERE id=?'); $chk->execute([$societeId]);
    if (!$chk->fetch()) err('Société introuvable', 404);

    $id = uuid(); $filePath = null; $fileName = null; $fileSize = null;

    // Fichier PDF optionnel
    if (!empty($_FILES['file']) && $_FILES['file']['error'] === UPLOAD_ERR_OK) {
        $file = $_FILES['file'];
        if ($file['size'] > 10 * 1024 * 1024) err('Fichier trop volumineux (max 10 Mo)');
        $ext = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
        if ($ext !== 'pdf') err('Seuls les fichiers PDF sont acceptés pour les factures');
        $dir = __DIR__ . '/uploads/factures/' . $societeId;
        if (!is_dir($dir)) mkdir($dir, 0755, true);
        $diskName  = $id . '-' . preg_replace('/[^a-zA-Z0-9\-_]/', '-', pathinfo($file['name'], PATHINFO_FILENAME)) . '.pdf';
        $filePath  = 'uploads/factures/' . $societeId . '/' . $diskName;
        $fileName  = $file['name'];
        $fileSize  = $file['size'];
        if (!move_uploaded_file($file['tmp_name'], __DIR__ . '/' . $filePath))
            err('Erreur lors de la sauvegarde du fichier PDF');
    }

    $statut = $_POST['statut'] ?? 'en attente';
    if (!in_array($statut, ['en attente', 'payée', 'en retard'])) $statut = 'en attente';

    db()->prepare('INSERT INTO factures (id,societe_id,contact_id,mission_id,numero,libelle,montant_ht,tva,date_emission,date_echeance,statut,file_path,file_name,file_size,date_upload) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)')
        ->execute([
            $id, $societeId,
            (clean($_POST['contactId'] ?? '')) ?: null,
            (clean($_POST['missionId'] ?? '')) ?: null,
            $numero, clean($_POST['libelle'] ?? ''),
            (float)($_POST['montantHT'] ?? 0),
            (float)($_POST['tva'] ?? 20),
            $_POST['dateEmission'] ?: null,
            $_POST['dateEcheance'] ?: null,
            $statut, $filePath, $fileName, $fileSize,
            $filePath ? date('Y-m-d H:i:s') : null,
        ]);
    $row = db()->prepare('SELECT f.*,s.nom AS societe_nom FROM factures f JOIN societes s ON s.id=f.societe_id WHERE f.id=?');
    $row->execute([$id]);
    ok(['facture' => fmtFacture($row->fetch())]);

case 'facture_statut':
    crmAuth(); $b=body();
    if (!in_array($b['statut']??'',['en attente','payée','en retard'])) err('Statut invalide');
    db()->prepare('UPDATE factures SET statut=? WHERE id=?')->execute([$b['statut'],$b['id']??'']);
    ok();

case 'facture_delete':
    superAdmin(); $b=body(); $id=$b['id']??'';
    $stmt=db()->prepare('SELECT file_path FROM factures WHERE id=?'); $stmt->execute([$id]); $f=$stmt->fetch();
    if ($f&&$f['file_path']) { $fp=__DIR__.'/'.$f['file_path']; if (file_exists($fp)) unlink($fp); }
    db()->prepare('DELETE FROM factures WHERE id=?')->execute([$id]);
    ok();

// ════════════════════════════════════════════════════════════════
//  ESPACE CLIENT
// ════════════════════════════════════════════════════════════════
case 'login':
    if ($method!=='POST') err('Méthode non autorisée',405);
    $b=body(); $email=filter_var(trim($b['email']??''),FILTER_VALIDATE_EMAIL); $pwd=$b['password']??'';
    if (!$email||!$pwd) err('Email ou mot de passe manquant');
    $stmt=db()->prepare('SELECT c.*,s.nom AS societe_nom,s.acces_factures AS soc_af,s.acces_tickets AS soc_at FROM contacts c JOIN societes s ON s.id=c.societe_id WHERE c.email=?');
    $stmt->execute([strtolower($email)]); $contact=$stmt->fetch();
    if (!$contact) err('Email ou mot de passe incorrect');
    if (in_array($contact['statut_espace'],['non_invite','pending','invited'])) err("Votre compte n'est pas encore activé. Consultez votre email d'invitation.");
    if ($contact['statut_espace']==='inactive') err('Compte désactivé. Contactez KAP DSI.');
    if (!password_verify($pwd,$contact['pwd_hash'])) err('Email ou mot de passe incorrect');
    $token=mkSession($contact['id']);
    // Droits effectifs
    $effF = isset($contact['acces_factures']) && $contact['acces_factures']!==null ? (bool)$contact['acces_factures'] : (isset($contact['soc_af']) ? (bool)$contact['soc_af'] : true);
    $effT = isset($contact['acces_tickets'])  && $contact['acces_tickets'] !==null ? (bool)$contact['acces_tickets']  : (isset($contact['soc_at']) ? (bool)$contact['soc_at']  : true);
    ok(['token'=>$token,'contact'=>fmtContact($contact),'societeNom'=>$contact['societe_nom'],'droits'=>['factures'=>$effF,'tickets'=>$effT]]);

case 'logout':
    $t=getToken(); if ($t) db()->prepare('DELETE FROM sessions WHERE id=?')->execute([$t]); ok();

case 'me':
    $c=contactAuth();
    $soc=db()->prepare('SELECT nom,siren,siret,tva_intra,tva_intra,site_web,adresse1,adresse2,code_postal,ville,pays,acces_factures,acces_tickets FROM societes WHERE id=?');
    $soc->execute([$c['societe_id']]); $s=$soc->fetch();
    // Droits effectifs : surcharge contact > droits société > défaut true
    $effFactures = isset($c['acces_factures']) && $c['acces_factures'] !== null
        ? (bool)$c['acces_factures']
        : (isset($s['acces_factures']) ? (bool)$s['acces_factures'] : true);
    $effTickets  = isset($c['acces_tickets'])  && $c['acces_tickets']  !== null
        ? (bool)$c['acces_tickets']
        : (isset($s['acces_tickets'])  ? (bool)$s['acces_tickets']  : true);
    ok([
        'contact'    => fmtContact($c),
        'societeNom' => $s['nom']??'',
        'droits'     => ['factures'=>$effFactures,'tickets'=>$effTickets],
        'societe'    => $s ? [
            'nom'      => $s['nom']??'',
            'siren'    => $s['siren']??'',
            'siret'    => $s['siret']??'',
            'tvaIntra' => $s['tva_intra']??'',
            'siteWeb'  => $s['site_web']??'',
            'adresse1' => $s['adresse1']??'',
            'adresse2' => $s['adresse2']??'',
            'codePostal'=> $s['code_postal']??'',
            'ville'    => $s['ville']??'',
            'pays'     => $s['pays']??'France',
        ] : null,
    ]);

case 'mes_factures':
    $c=contactAuth();
    // Factures de la société du contact
    $stmt=db()->prepare("SELECT f.*,s.nom AS societe_nom FROM factures f JOIN societes s ON s.id=f.societe_id WHERE f.societe_id=? ORDER BY f.date_creation DESC");
    $stmt->execute([$c['societe_id']]);
    ok(['factures'=>array_map('fmtFacture',$stmt->fetchAll())]);

case 'mes_documents':
    $c=contactAuth();
    $stmt=db()->prepare("SELECT d.*,u.prenom AS crm_prenom,u.nom AS crm_nom FROM documents d LEFT JOIN crm_users u ON u.id=d.crm_user_id WHERE d.societe_id=? ORDER BY d.date_creation DESC");
    $stmt->execute([$c['societe_id']]);
    ok(['documents'=>array_map('fmtDocument',$stmt->fetchAll())]);

case 'client_update_profile':
    $c=contactAuth(); $b=body();
    db()->prepare('UPDATE contacts SET prenom=?,nom=?,telephone=?,telephone2=? WHERE id=?')
        ->execute([clean($b['prenom']??$c['prenom']),clean($b['nom']??$c['nom']),clean($b['telephone']??''),clean($b['telephone2']??''),$c['id']]);
    ok();

case 'client_change_password':
    $c=contactAuth(); $b=body();
    if (!password_verify($b['currentPassword']??'',$c['pwd_hash'])) err('Mot de passe actuel incorrect');
    $new=$b['newPassword']??'';
    if (strlen($new)<8) err('Au moins 8 caractères');
    if (!preg_match('/[A-Z]/',$new)) err('Au moins une majuscule');
    if (!preg_match('/[0-9]/',$new)) err('Au moins un chiffre');
    db()->prepare('UPDATE contacts SET pwd_hash=? WHERE id=?')->execute([password_hash($new,PASSWORD_BCRYPT,['cost'=>12]),$c['id']]);
    ok();

case 'forgot_password':
    $b=body(); $email=filter_var(trim($b['email']??''),FILTER_VALIDATE_EMAIL);
    if (!$email) err('Email invalide');
    $stmt=db()->prepare('SELECT * FROM contacts WHERE email=?'); $stmt->execute([strtolower($email)]); $ct=$stmt->fetch();
    if (!$ct||$ct['statut_espace']!=='active') { ok(['message'=>"Si cet email est associé à un compte actif, un lien vous a été envoyé."]); }
    db()->prepare("UPDATE tokens SET used_at=NOW() WHERE contact_id=? AND used_at IS NULL")->execute([$ct['id']]);
    $token=bin2hex(random_bytes(32));
    db()->prepare('INSERT INTO tokens (token,contact_id,type,expires_at) VALUES (?,?,?,DATE_ADD(NOW(),INTERVAL 1 HOUR))')->execute([$token,$ct['id'],'reset']);
    $siteUrl=rtrim($b['siteUrl']??KAP_SITE_URL,'/');
    $link=$siteUrl.'/espace-client/?reset='.$token;
    $html="<div style='font-family:Arial,sans-serif;max-width:600px;margin:0 auto'><div style='background:#0F2044;padding:32px;border-bottom:3px solid #e05314'><p style='color:#fff;font-size:20px;font-weight:700;margin:0'>KAP DSI</p></div><div style='background:#f5f6f8;padding:36px 32px'><p style='font-size:15px;color:#0F2044;font-weight:600'>Bonjour {$ct['prenom']},</p><p style='font-size:14px;color:#4B5563;line-height:1.7'>Vous avez demandé la réinitialisation de votre mot de passe.</p><div style='text-align:center;margin:28px 0'><a href='{$link}' style='background:#e05314;color:#fff;padding:14px 36px;text-decoration:none;border-radius:4px;font-size:14px;font-weight:700'>Réinitialiser mon mot de passe</a></div><p style='font-size:12px;color:#9CA3AF'>Lien valable <strong>1 heure</strong>. Ignorez cet email si vous n'êtes pas à l'origine de cette demande.</p></div></div>";
    $payload=json_encode(['sender'=>['name'=>KAP_SENDER_NAME,'email'=>KAP_SENDER_EMAIL],'to'=>[['email'=>$ct['email'],'name'=>$ct['prenom']]],'subject'=>'Réinitialisation de votre mot de passe KAP DSI','htmlContent'=>$html]);
    $ch=curl_init('https://api.brevo.com/v3/smtp/email'); curl_setopt_array($ch,[CURLOPT_RETURNTRANSFER=>true,CURLOPT_POST=>true,CURLOPT_POSTFIELDS=>$payload,CURLOPT_HTTPHEADER=>['accept: application/json','api-key: '.KAP_BREVO_API_KEY,'content-type: application/json'],CURLOPT_TIMEOUT=>15]); curl_exec($ch); curl_close($ch);
    ok(['message'=>"Si cet email est associé à un compte actif, un lien vous a été envoyé."]);

case 'reset_password':
    $b=body(); $token=preg_replace('/[^a-f0-9]/','',($b['token']??'')); $pwd=$b['password']??'';
    if (strlen($token)!==64) err('Token invalide');
    if (strlen($pwd)<8||!preg_match('/[A-Z]/',$pwd)||!preg_match('/[0-9]/',$pwd)) err('Mot de passe trop faible');
    $stmt=db()->prepare('SELECT t.*,c.email,c.prenom,c.statut_espace FROM tokens t JOIN contacts c ON c.id=t.contact_id WHERE t.token=?'); $stmt->execute([$token]); $t=$stmt->fetch();
    if (!$t) err('Lien invalide',404);
    if ($t['used_at']) err('Lien déjà utilisé.',410);
    if (strtotime($t['expires_at'])<time()) err('Lien expiré (1h).',410);
    if ($t['statut_espace']!=='active') err("Compte non actif.",403);
    db()->prepare('UPDATE contacts SET pwd_hash=? WHERE id=?')->execute([password_hash($pwd,PASSWORD_BCRYPT,['cost'=>12]),$t['contact_id']]);
    db()->prepare('UPDATE tokens SET used_at=NOW() WHERE token=?')->execute([$token]);
    $st = mkSession($t['contact_id']);
    $cRow = db()->prepare('SELECT * FROM contacts WHERE id=?'); $cRow->execute([$t['contact_id']]);
    $cObj = $cRow->fetch();
    $sRow = db()->prepare('SELECT nom,siren,siret,tva_intra,site_web,adresse1,adresse2,code_postal,ville,pays FROM societes WHERE id=?'); $sRow->execute([$cObj['societe_id']]);
    $sObj = $sRow->fetch();
    $socData=$sObj?['nom'=>$sObj['nom']??'','siren'=>$sObj['siren']??'','siret'=>$sObj['siret']??'','tvaIntra'=>$sObj['tva_intra']??'','siteWeb'=>$sObj['site_web']??'','adresse1'=>$sObj['adresse1']??'','adresse2'=>$sObj['adresse2']??'','codePostal'=>$sObj['code_postal']??'','ville'=>$sObj['ville']??'','pays'=>$sObj['pays']??'France']:null;
    ok(['token'=>$st, 'message'=>'Mot de passe modifié.', 'client'=>fmtContact($cObj), 'societeNom'=>($sObj['nom']??''), 'societe'=>$socData]);

case 'activate':
    $b=body(); $token=preg_replace('/[^a-f0-9]/','',($b['token']??'')); $pwd=$b['password']??'';
    if (strlen($token)!==64) err('Token invalide');
    if (strlen($pwd)<8||!preg_match('/[A-Z]/',$pwd)||!preg_match('/[0-9]/',$pwd)) err('Mot de passe trop faible');
    $stmt=db()->prepare('SELECT t.*,c.email,c.prenom FROM tokens t JOIN contacts c ON c.id=t.contact_id WHERE t.token=?'); $stmt->execute([$token]); $t=$stmt->fetch();
    if (!$t) err('Lien invalide',404);
    if ($t['used_at']) err('Lien déjà utilisé.',410);
    if (strtotime($t['expires_at'])<time()) err('Lien expiré.',410);
    db()->prepare("UPDATE contacts SET pwd_hash=?,statut_espace='active' WHERE id=?")->execute([password_hash($pwd,PASSWORD_BCRYPT,['cost'=>12]),$t['contact_id']]);
    db()->prepare('UPDATE tokens SET used_at=NOW() WHERE token=?')->execute([$token]);
    $st = mkSession($t['contact_id']);
    $cRow = db()->prepare('SELECT * FROM contacts WHERE id=?'); $cRow->execute([$t['contact_id']]);
    $cObj = $cRow->fetch();
    $sRow = db()->prepare('SELECT nom,siren,siret,tva_intra,site_web,adresse1,adresse2,code_postal,ville,pays FROM societes WHERE id=?'); $sRow->execute([$cObj['societe_id']]);
    $sObj = $sRow->fetch();
    $socData=$sObj?['nom'=>$sObj['nom']??'','siren'=>$sObj['siren']??'','siret'=>$sObj['siret']??'','tvaIntra'=>$sObj['tva_intra']??'','siteWeb'=>$sObj['site_web']??'','adresse1'=>$sObj['adresse1']??'','adresse2'=>$sObj['adresse2']??'','codePostal'=>$sObj['code_postal']??'','ville'=>$sObj['ville']??'','pays'=>$sObj['pays']??'France']:null;
    ok(['token'=>$st, 'message'=>'Compte activé.', 'client'=>fmtContact($cObj), 'societeNom'=>($sObj['nom']??''), 'societe'=>$socData]);

case 'send_invite':
    crmAuth(); $b=body(); $contactId=clean($b['contactId']??'');
    $stmt=db()->prepare('SELECT c.*,s.nom AS societe_nom FROM contacts c JOIN societes s ON s.id=c.societe_id WHERE c.id=?'); $stmt->execute([$contactId]); $ct=$stmt->fetch();
    if (!$ct) err('Contact introuvable',404);
    if (empty($ct['email'])) err("Ce contact n'a pas d'adresse email.");
    db()->prepare("UPDATE tokens SET used_at=NOW() WHERE contact_id=? AND used_at IS NULL")->execute([$contactId]);
    $token=bin2hex(random_bytes(32));
    db()->prepare('INSERT INTO tokens (token,contact_id,type,expires_at) VALUES (?,?,?,DATE_ADD(NOW(),INTERVAL 72 HOUR))')->execute([$token,$contactId,'invitation']);
    db()->prepare("UPDATE contacts SET statut_espace='invited',date_invitation=NOW() WHERE id=?")->execute([$contactId]);
    $siteUrl=rtrim($b['siteUrl']??KAP_SITE_URL,'/');
    $link=$siteUrl.'/espace-client/?token='.$token;
    $html="<div style='font-family:Arial,sans-serif;max-width:600px;margin:0 auto'><div style='background:#0F2044;padding:32px;border-bottom:3px solid #e05314'><p style='color:#fff;font-size:20px;font-weight:700;margin:0'>KAP DSI</p></div><div style='background:#f5f6f8;padding:36px 32px'><p style='font-size:15px;color:#0F2044;font-weight:600'>Bonjour {$ct['prenom']},</p><p style='font-size:14px;color:#4B5563;line-height:1.7'>Votre espace client <strong>KAP DSI</strong> — {$ct['societe_nom']} — a été créé. Cliquez ci-dessous pour choisir votre mot de passe.</p><div style='text-align:center;margin:28px 0'><a href='{$link}' style='background:#e05314;color:#fff;padding:14px 36px;text-decoration:none;border-radius:4px;font-size:14px;font-weight:700'>Activer mon espace client</a></div><div style='background:#fff;border:1px solid #E4E7EC;border-radius:4px;padding:16px;margin:16px 0'><p style='font-size:11px;color:#6B7280;margin:0 0 4px;text-transform:uppercase;letter-spacing:1px'>Identifiant de connexion</p><p style='font-size:15px;font-weight:700;color:#0F2044;margin:0'>{$ct['email']}</p></div><p style='font-size:12px;color:#9CA3AF'>Lien valable <strong>72 heures</strong>.</p></div></div>";
    $payload=json_encode(['sender'=>['name'=>KAP_SENDER_NAME,'email'=>KAP_SENDER_EMAIL],'to'=>[['email'=>$ct['email'],'name'=>trim($ct['prenom'].' '.$ct['nom'])]],'subject'=>'Bienvenue sur votre espace client KAP DSI','htmlContent'=>$html]);
    $ch=curl_init('https://api.brevo.com/v3/smtp/email'); curl_setopt_array($ch,[CURLOPT_RETURNTRANSFER=>true,CURLOPT_POST=>true,CURLOPT_POSTFIELDS=>$payload,CURLOPT_HTTPHEADER=>['accept: application/json','api-key: '.KAP_BREVO_API_KEY,'content-type: application/json'],CURLOPT_TIMEOUT=>15]); $resp=curl_exec($ch); $code=curl_getinfo($ch,CURLINFO_HTTP_CODE); $cerr=curl_error($ch); curl_close($ch);
    if ($cerr) err('Erreur réseau : '.$cerr,500);
    if ($code!==201) { $rb=json_decode($resp,true); err('Erreur Brevo : '.($rb['message']??$code),500); }
    ok(['message'=>"Invitation envoyée à {$ct['email']}"]);

// [comptabilité cases below]

case 'compta_list':
    crmAuth();
    $periode = $_GET['periode'] ?? '';     // YYYY-MM ou YYYY-QN ou YYYY
    $type    = $_GET['type']    ?? '';
    $export  = $_GET['export']  ?? '';
    $paiement= $_GET['paiement']?? '';

    $sql = "SELECT e.*, s.nom AS societe_nom FROM ecritures e LEFT JOIN societes s ON s.id=e.societe_id WHERE 1=1";
    $params = [];

    // Filtre période : mensuel (2025-01), trimestriel (2025-Q1), annuel (2025)
    if ($periode) {
        if (preg_match('/^(\d{4})-Q([1-4])$/', $periode, $m)) {
            $q = (int)$m[2]; $y = $m[1];
            $mStart = sprintf('%s-%02d', $y, ($q - 1) * 3 + 1);
            $mEnd   = sprintf('%s-%02d', $y, $q * 3);
            $sql .= " AND e.periode BETWEEN ? AND ?"; $params[] = $mStart; $params[] = $mEnd;
        } elseif (preg_match('/^\d{4}$/', $periode)) {
            $sql .= " AND e.periode LIKE ?"; $params[] = $periode . '-%';
        } else {
            $sql .= " AND e.periode = ?"; $params[] = $periode;
        }
    }
    if ($type)    { $sql .= " AND e.type = ?";             $params[] = $type; }
    if ($export)  { $sql .= " AND e.statut_export = ?";    $params[] = $export; }
    if ($paiement){ $sql .= " AND e.statut_paiement = ?";  $params[] = $paiement; }
    $sql .= " ORDER BY e.date_piece DESC, e.date_creation DESC";
    try {
        $stmt = db()->prepare($sql); $stmt->execute($params);
        $rows = array_map('fmtEcriture', $stmt->fetchAll());
    } catch (PDOException $e) {
        if (strpos($e->getMessage(), "doesn't exist") !== false || strpos($e->getMessage(), "Table") !== false)
            err("La table 'ecritures' n'existe pas encore en base de données. Exécutez le CREATE TABLE fourni dans kapdsi.sql.", 500);
        throw $e;
    }

    // Totaux
    $totaux = ['recettes_ht' => 0, 'charges_ht' => 0, 'recettes_ttc' => 0, 'charges_ttc' => 0, 'tva_collectee' => 0, 'tva_deductible' => 0];
    foreach ($rows as $r) {
        $ht  = $r['montantHT']; $ttc = $r['montantTTC']; $tva = $ttc - $ht;
        if ($r['type'] === 'facture_emise') {
            $totaux['recettes_ht']  += $ht; $totaux['recettes_ttc'] += $ttc; $totaux['tva_collectee']  += $tva;
        } else {
            $totaux['charges_ht']   += $ht; $totaux['charges_ttc']  += $ttc; $totaux['tva_deductible'] += $tva;
        }
    }
    $totaux['resultat_ht']  = $totaux['recettes_ht']  - $totaux['charges_ht'];
    $totaux['resultat_ttc'] = $totaux['recettes_ttc'] - $totaux['charges_ttc'];
    $totaux = array_map(fn($v) => round($v, 2), $totaux);
    ok(['ecritures' => $rows, 'totaux' => $totaux]);

case 'compta_add':
    $u = crmAuth();
    if (empty($_POST) && !empty(file_get_contents('php://input'))) {
        // JSON fallback (sans fichier)
        $b = body();
        $POST = $b;
    } else {
        $POST = $_POST;
    }
    if (!trim($POST['libelle'] ?? '')) err('Libellé obligatoire');
    if (!trim($POST['datePiece'] ?? '')) err('Date de la pièce obligatoire');
    if (!trim($POST['montantHT'] ?? '')) err('Montant HT obligatoire');

    $id      = uuid();
    $periode = substr(clean($POST['datePiece']), 0, 7); // YYYY-MM
    $type    = in_array($POST['type'] ?? '', ['facture_emise','charge','note_frais','pret']) ? $POST['type'] : 'facture_emise';

    // Fichier optionnel
    $filePath = null; $fileName = null; $fileSize = null; $fxStatus = 'non_analyse'; $fxVersion = null;
    if (!empty($_FILES['file']) && $_FILES['file']['error'] === UPLOAD_ERR_OK) {
        $file = $_FILES['file'];
        if ($file['size'] > 20 * 1024 * 1024) err('Fichier trop volumineux (max 20 Mo)');
        $ext = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
        $allowedExts = ['pdf','jpg','jpeg','png','doc','docx','xls','xlsx'];
        if (!in_array($ext, $allowedExts)) err('Type de fichier non autorisé');
        $dir = __DIR__ . '/uploads/compta/' . $periode;
        if (!is_dir($dir)) mkdir($dir, 0755, true);
        $diskName = $id . '-' . preg_replace('/[^a-zA-Z0-9\-_]/', '-', pathinfo($file['name'], PATHINFO_FILENAME)) . '.' . $ext;
        $filePath = 'uploads/compta/' . $periode . '/' . $diskName;
        $fileName = $file['name'];
        $fileSize = $file['size'];
        if (!move_uploaded_file($file['tmp_name'], __DIR__ . '/' . $filePath)) err('Erreur sauvegarde fichier');
        // Détection Factur-X uniquement pour les PDFs
        if ($ext === 'pdf') {
            [$fxStatus, $fxVersion] = detectFacturX(__DIR__ . '/' . $filePath);
        } else {
            $fxStatus = 'non_analyse';
        }
    }

    $societeId = (clean($POST['societeId'] ?? '')) ?: null;
    db()->prepare("INSERT INTO ecritures (id,type,categorie,numero,libelle,tiers,societe_id,montant_ht,tva_taux,date_piece,date_echeance,periode,statut_paiement,file_path,file_name,file_size,facturx_status,facturx_version,notes,crm_user_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)")
        ->execute([$id, $type,
            clean($POST['categorie']     ?? ''),
            clean($POST['numero']        ?? ''),
            clean($POST['libelle']),
            clean($POST['tiers']         ?? ''),
            $societeId,
            (float)($POST['montantHT']   ?? 0),
            (float)($POST['tvaTaux']     ?? 20),
            clean($POST['datePiece']),
            ($POST['dateEcheance'] ?? '') ?: null,
            $periode,
            in_array($POST['statutPaiement'] ?? '', ['en_attente','paye','en_retard']) ? $POST['statutPaiement'] : 'en_attente',
            $filePath, $fileName, $fileSize, $fxStatus, $fxVersion,
            clean($POST['notes'] ?? ''),
            $u['id'],
        ]);
    $row = db()->prepare('SELECT e.*, s.nom AS societe_nom FROM ecritures e LEFT JOIN societes s ON s.id=e.societe_id WHERE e.id=?');
    $row->execute([$id]);
    ok(['ecriture' => fmtEcriture($row->fetch())]);

case 'compta_update':
    crmAuth(); $b = body(); $id = $b['id'] ?? '';
    $chk = db()->prepare('SELECT id FROM ecritures WHERE id=?'); $chk->execute([$id]);
    if (!$chk->fetch()) err('Écriture introuvable', 404);
    $periode = substr(clean($b['datePiece'] ?? date('Y-m-d')), 0, 7);
    db()->prepare("UPDATE ecritures SET type=?,categorie=?,numero=?,libelle=?,tiers=?,societe_id=?,montant_ht=?,tva_taux=?,date_piece=?,date_echeance=?,periode=?,statut_paiement=?,notes=? WHERE id=?")
        ->execute([
            in_array($b['type'] ?? '', ['facture_emise','charge','note_frais','pret']) ? $b['type'] : 'facture_emise',
            clean($b['categorie']   ?? ''), clean($b['numero']   ?? ''),
            clean($b['libelle']     ?? ''), clean($b['tiers']    ?? ''),
            ($b['societeId'] ?? '') ?: null,
            (float)($b['montantHT'] ?? 0),  (float)($b['tvaTaux'] ?? 20),
            clean($b['datePiece']), ($b['dateEcheance'] ?? '') ?: null, $periode,
            in_array($b['statutPaiement'] ?? '', ['en_attente','paye','en_retard']) ? $b['statutPaiement'] : 'en_attente',
            clean($b['notes'] ?? ''), $id,
        ]);
    ok();

case 'compta_statut_paiement':
    crmAuth(); $b = body();
    if (!in_array($b['statut'] ?? '', ['en_attente','paye','en_retard'])) err('Statut invalide');
    db()->prepare('UPDATE ecritures SET statut_paiement=? WHERE id=?')->execute([$b['statut'], $b['id'] ?? '']);
    ok();

case 'compta_delete':
    superAdmin(); $b = body(); $id = $b['id'] ?? '';
    $stmt = db()->prepare('SELECT file_path FROM ecritures WHERE id=?'); $stmt->execute([$id]); $e = $stmt->fetch();
    if ($e && $e['file_path']) { $fp = __DIR__ . '/' . $e['file_path']; if (file_exists($fp)) unlink($fp); }
    db()->prepare('DELETE FROM ecritures WHERE id=?')->execute([$id]);
    ok();

case 'compta_download':
    $token = getToken(); $canAccess = false;
    if ($token) {
        $adm = db()->prepare("SELECT id FROM sessions WHERE id=? AND entity_id LIKE 'crm:%' AND expires_at>NOW()");
        $adm->execute([$token]);
        if ($adm->fetch()) $canAccess = true;
    }
    if (!$canAccess) err('Accès refusé', 403);
    $id = $_GET['id'] ?? '';
    $stmt = db()->prepare('SELECT * FROM ecritures WHERE id=?'); $stmt->execute([$id]); $e = $stmt->fetch();
    if (!$e || !$e['file_path']) err('Fichier introuvable', 404);
    $absPath = __DIR__ . '/' . $e['file_path'];
    if (!file_exists($absPath)) err('Fichier non trouvé sur le serveur', 404);
    $ext = strtolower(pathinfo($e['file_name'], PATHINFO_EXTENSION));
    $mimes = ['pdf'=>'application/pdf','jpg'=>'image/jpeg','jpeg'=>'image/jpeg','png'=>'image/png','doc'=>'application/msword','docx'=>'application/vnd.openxmlformats-officedocument.wordprocessingml.document','xls'=>'application/vnd.ms-excel','xlsx'=>'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];
    $mime = $mimes[$ext] ?? 'application/octet-stream';
    $inline = in_array($ext, ['pdf','jpg','jpeg','png']);
    ob_clean();
    header('Content-Type: ' . $mime);
    header('Content-Disposition: ' . ($inline ? 'inline' : 'attachment') . '; filename="' . $e['file_name'] . '"');
    header('Content-Length: ' . filesize($absPath));
    readfile($absPath); exit;

// ── Export comptable CSV + PDF ────────────────────────────────
case 'compta_export_csv':
    crmAuth();
    $periode  = $_GET['periode']  ?? '';
    $type     = $_GET['type']     ?? '';
    $nonExpo  = ($_GET['non_exporte'] ?? '0') === '1';

    $sql = "SELECT e.*, s.nom AS societe_nom FROM ecritures e LEFT JOIN societes s ON s.id=e.societe_id WHERE 1=1";
    $params = [];
    if ($periode) {
        if (preg_match('/^(\d{4})-Q([1-4])$/', $periode, $m)) {
            $q=(int)$m[2];$y=$m[1];
            $sql.=" AND e.periode BETWEEN ? AND ?";$params[]=sprintf('%s-%02d',$y,($q-1)*3+1);$params[]=sprintf('%s-%02d',$y,$q*3);
        } elseif (preg_match('/^\d{4}$/', $periode)) {
            $sql.=" AND e.periode LIKE ?";$params[]=$periode.'-%';
        } else { $sql.=" AND e.periode=?";$params[]=$periode; }
    }
    if ($type)   { $sql.=" AND e.type=?";            $params[]=$type; }
    if ($nonExpo){ $sql.=" AND e.statut_export='non_exporte'"; }
    $sql .= " ORDER BY e.date_piece ASC, e.type ASC";
    $stmt = db()->prepare($sql); $stmt->execute($params);
    $rows = $stmt->fetchAll();

    // Générer le CSV
    $ref = 'EXPORT-' . date('YmdHis');
    ob_clean();
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="export-compta-' . ($periode ?: date('Y-m')) . '.csv"');
    $out = fopen('php://output', 'w');
    // BOM UTF-8 pour Excel
    fputs($out, "ï»¿");
    fputcsv($out, ['Date','Type','Catégorie','N° Pièce','Libellé','Tiers / Client','Montant HT','Taux TVA','TVA','Montant TTC','Statut paiement','Statut export','Factur-X','Profil Factur-X','Période','Notes'], ';');
    foreach ($rows as $r) {
        $ht  = (float)$r['montant_ht'];
        $tva = round($ht * (float)$r['tva_taux'] / 100, 2);
        $ttc = round($ht + $tva, 2);
        $typeLabel = ['facture_emise'=>'Facture émise','charge'=>'Charge','note_frais'=>'Note de frais'][$r['type']] ?? $r['type'];
        $paiLabel  = ['en_attente'=>'En attente','paye'=>'Payé','en_retard'=>'En retard'][$r['statut_paiement']] ?? '';
        fputcsv($out, [
            $r['date_piece'], $typeLabel, $r['categorie']??'', $r['numero']??'',
            $r['libelle'], $r['tiers']??($r['societe_nom']??''),
            number_format($ht,2,',',' '), number_format((float)$r['tva_taux'],2,',',' '),
            number_format($tva,2,',',' '), number_format($ttc,2,',',' '),
            $paiLabel, $r['statut_export']==='exporte'?'Exporté':'Non exporté',
            $r['facturx_status']??'', $r['facturx_version']??'',
            $r['periode'], $r['notes']??'',
        ], ';');
    }
    fclose($out);
    // Marquer comme exporté
    $ids = array_column($rows, 'id');
    if ($ids) {
        $ph = implode(',', array_fill(0, count($ids), '?'));
        db()->prepare("UPDATE ecritures SET statut_export='exporte',date_export=NOW(),export_ref=? WHERE id IN ($ph)")
            ->execute(array_merge([$ref], $ids));
    }
    exit;

case 'compta_export_pdf':
    crmAuth();
    $periode  = $_GET['periode'] ?? date('Y-m');
    $type     = $_GET['type']    ?? '';
    $nonExpo  = ($_GET['non_exporte'] ?? '0') === '1';

    $sql = "SELECT e.*, s.nom AS societe_nom FROM ecritures e LEFT JOIN societes s ON s.id=e.societe_id WHERE 1=1";
    $params = [];
    if ($periode) {
        if (preg_match('/^(\d{4})-Q([1-4])$/', $periode, $m)) {
            $q=(int)$m[2];$y=$m[1];
            $sql.=" AND e.periode BETWEEN ? AND ?";$params[]=sprintf('%s-%02d',$y,($q-1)*3+1);$params[]=sprintf('%s-%02d',$y,$q*3);
        } elseif (preg_match('/^\d{4}$/', $periode)) {
            $sql.=" AND e.periode LIKE ?";$params[]=$periode.'-%';
        } else { $sql.=" AND e.periode=?";$params[]=$periode; }
    }
    if ($type)   { $sql.=" AND e.type=?";$params[]=$type; }
    if ($nonExpo){ $sql.=" AND e.statut_export='non_exporte'"; }
    $sql .= " ORDER BY e.type ASC, e.date_piece ASC";
    $stmt = db()->prepare($sql); $stmt->execute($params);
    $rows = $stmt->fetchAll();

    // Calcul des totaux
    $totRec=['ht'=>0,'tva'=>0,'ttc'=>0]; $totCha=['ht'=>0,'tva'=>0,'ttc'=>0];
    foreach ($rows as $r) {
        $ht=(float)$r['montant_ht']; $tva=round($ht*(float)$r['tva_taux']/100,2); $ttc=round($ht+$tva,2);
        if ($r['type']==='facture_emise'){$totRec['ht']+=$ht;$totRec['tva']+=$tva;$totRec['ttc']+=$ttc;}
        else{$totCha['ht']+=$ht;$totCha['tva']+=$tva;$totCha['ttc']+=$ttc;}
    }
    $resultat = $totRec['ht'] - $totCha['ht'];

    $ref = 'EXPORT-' . date('YmdHis');
    $periodeLabel = $periode;
    if (preg_match('/^(\d{4})-Q([1-4])$/', $periode, $m)) $periodeLabel = 'T'.$m[2].' '.$m[1];
    elseif (preg_match('/^(\d{4})-(\d{2})$/', $periode, $m)) {
        $mois = ['','Janvier','Février','Mars','Avril','Mai','Juin','Juillet','Août','Septembre','Octobre','Novembre','Décembre'];
        $periodeLabel = $mois[(int)$m[2]].' '.$m[1];
    }

    $typeLabels = ['facture_emise'=>'Facture émise','charge'=>'Charge','note_frais'=>'Note de frais'];
    $paiLabels  = ['en_attente'=>'En attente','paye'=>'Payé','en_retard'=>'En retard'];
    $fxLabels   = ['non_analyse'=>'','non_detecte'=>'PDF standard','facturx'=>'Factur-X ✓','zugferd'=>'ZUGFeRD ✓'];

    $rows_html = '';
    foreach ($rows as $r) {
        $ht=(float)$r['montant_ht']; $tva=round($ht*(float)$r['tva_taux']/100,2); $ttc=round($ht+$tva,2);
        $bgColor = $r['type']==='facture_emise' ? '#f0f7ff' : '#fff9f0';
        $fxBadge = $r['facturx_status']==='facturx' ? '<span style="color:#16a34a;font-weight:700;font-size:9px">✓ Factur-X</span>' : ($r['facturx_status']==='zugferd' ? '<span style="color:#2563eb;font-weight:700;font-size:9px">✓ ZUGFeRD</span>' : '');
        $rows_html .= "<tr style='background:{$bgColor}'>
            <td style='padding:5px 8px;font-size:10px;border-bottom:1px solid #e4e7ec'>{$r['date_piece']}</td>
            <td style='padding:5px 8px;font-size:10px;border-bottom:1px solid #e4e7ec'>" . htmlspecialchars($typeLabels[$r['type']]??$r['type']) . "</td>
            <td style='padding:5px 8px;font-size:10px;border-bottom:1px solid #e4e7ec'>" . htmlspecialchars($r['numero']??'') . "</td>
            <td style='padding:5px 8px;font-size:10px;border-bottom:1px solid #e4e7ec'>" . htmlspecialchars($r['libelle']) . "</td>
            <td style='padding:5px 8px;font-size:10px;border-bottom:1px solid #e4e7ec'>" . htmlspecialchars($r['tiers']??($r['societe_nom']??'')) . "</td>
            <td style='padding:5px 8px;font-size:10px;border-bottom:1px solid #e4e7ec;text-align:right'>" . number_format($ht,2,',',' ') . " €</td>
            <td style='padding:5px 8px;font-size:10px;border-bottom:1px solid #e4e7ec;text-align:right'>" . number_format($ttc,2,',',' ') . " €</td>
            <td style='padding:5px 8px;font-size:10px;border-bottom:1px solid #e4e7ec'>" . htmlspecialchars($paiLabels[$r['statut_paiement']]??'') . "</td>
            <td style='padding:5px 8px;font-size:10px;border-bottom:1px solid #e4e7ec'>{$fxBadge}</td>
        </tr>";
    }

    $html = '<!DOCTYPE html><html lang="fr"><head><meta charset="UTF-8"><style>
    body{font-family:Arial,sans-serif;font-size:11px;color:#111;margin:0;padding:20px}
    h1{font-size:18px;color:#0F2044;margin:0} h2{font-size:13px;color:#0F2044;margin:16px 0 6px}
    .hd{border-bottom:3px solid #e05314;padding-bottom:12px;margin-bottom:20px;display:flex;justify-content:space-between;align-items:flex-end}
    .brand{font-size:22px;font-weight:700;color:#0F2044} .sub{font-size:11px;color:#6b7280}
    table{width:100%;border-collapse:collapse} thead th{background:#0F2044;color:#fff;padding:6px 8px;font-size:10px;text-align:left}
    .totaux{display:grid;grid-template-columns:1fr 1fr 1fr;gap:12px;margin:16px 0}
    .tot-card{background:#f5f6f8;border-radius:4px;padding:12px 14px;border-left:3px solid #0F2044}
    .tot-card.green{border-left-color:#16a34a} .tot-card.orange{border-left-color:#e05314} .tot-card.red{border-left-color:#dc2626}
    .tot-num{font-size:16px;font-weight:700;color:#0F2044} .tot-lbl{font-size:10px;color:#6b7280;text-transform:uppercase}
    .footer{margin-top:24px;border-top:1px solid #e4e7ec;padding-top:10px;font-size:9px;color:#9ca3af;text-align:center}
    </style></head><body>
    <div class="hd">
        <div><div class="brand">KAP DSI</div><div class="sub">DSI à temps partagé — EURL</div></div>
        <div style="text-align:right"><div style="font-size:14px;font-weight:700;color:#e05314">Export Comptabilité</div>
        <div style="font-size:12px;color:#6b7280">Période : ' . htmlspecialchars($periodeLabel) . '</div>
        <div style="font-size:10px;color:#9ca3af">Généré le ' . date('d/m/Y à H:i') . ' — Réf. ' . $ref . '</div></div>
    </div>
    <div class="totaux">
        <div class="tot-card green"><div class="tot-num">' . number_format($totRec['ht'],2,',',' ') . ' € HT</div><div class="tot-lbl">Recettes</div><div style="font-size:9px;color:#6b7280">TTC : ' . number_format($totRec['ttc'],2,',',' ') . ' €</div></div>
        <div class="tot-card orange"><div class="tot-num">' . number_format($totCha['ht'],2,',',' ') . ' € HT</div><div class="tot-lbl">Charges</div><div style="font-size:9px;color:#6b7280">TTC : ' . number_format($totCha['ttc'],2,',',' ') . ' €</div></div>
        <div class="tot-card ' . ($resultat >= 0 ? 'green' : 'red') . '"><div class="tot-num">' . number_format($resultat,2,',',' ') . ' € HT</div><div class="tot-lbl">Résultat</div></div>
    </div>
    <h2>Détail des écritures (' . count($rows) . ')</h2>
    <table><thead><tr><th>Date</th><th>Type</th><th>N°</th><th>Libellé</th><th>Tiers</th><th style="text-align:right">HT</th><th style="text-align:right">TTC</th><th>Paiement</th><th>Factur-X</th></tr></thead>
    <tbody>' . $rows_html . '</tbody></table>
    <div class="footer">KAP DSI — Export comptable ' . htmlspecialchars($periodeLabel) . ' — ' . $ref . ' — Confidentiel</div>
    </body></html>';

    ob_clean();
    header('Content-Type: text/html; charset=utf-8');
    header('Content-Disposition: inline; filename="export-compta-' . ($periode ?: date('Y-m')) . '.html"');
    // Marquer comme exporté
    $ids = array_column($rows, 'id');
    if ($ids) {
        $ph = implode(',', array_fill(0, count($ids), '?'));
        db()->prepare("UPDATE ecritures SET statut_export='exporte',date_export=NOW(),export_ref=? WHERE id IN ($ph)")
            ->execute(array_merge([$ref], $ids));
    }
    echo $html; exit;

case 'compta_pl':
    // P&L par mois ou trimestre pour un exercice
    crmAuth();
    $annee = $_GET['annee'] ?? date('Y');
    $rows = db()->prepare("SELECT periode, type, SUM(montant_ht) AS total_ht, SUM(montant_ht * (1 + tva_taux/100)) AS total_ttc FROM ecritures WHERE periode LIKE ? GROUP BY periode, type ORDER BY periode ASC");
    $rows->execute([$annee . '-%']);
    $data = $rows->fetchAll();
    // Organiser par mois
    $pl = [];
    foreach ($data as $r) {
        $p = $r['periode'];
        if (!isset($pl[$p])) $pl[$p] = ['recettes_ht'=>0,'charges_ht'=>0,'recettes_ttc'=>0,'charges_ttc'=>0];
        if ($r['type']==='facture_emise') { $pl[$p]['recettes_ht']+=(float)$r['total_ht']; $pl[$p]['recettes_ttc']+=(float)$r['total_ttc']; }
        else { $pl[$p]['charges_ht']+=(float)$r['total_ht']; $pl[$p]['charges_ttc']+=(float)$r['total_ttc']; }
    }
    foreach ($pl as &$m) {
        $m['resultat_ht']  = round($m['recettes_ht']  - $m['charges_ht'],  2);
        $m['resultat_ttc'] = round($m['recettes_ttc'] - $m['charges_ttc'], 2);
        $m = array_map(fn($v) => round($v, 2), $m);
    }
    ok(['pl' => $pl, 'annee' => $annee]);


// ════════════════════════════════════════════════════════════════
//  TICKETS — Routes CRM
// ════════════════════════════════════════════════════════════════
case 'tickets_list':
    crmAuth();
    $statut   = $_GET['statut']    ?? '';
    $societeId= $_GET['societe_id']?? '';
    $q        = $_GET['q']         ?? '';
    $sql = "SELECT t.*, s.nom AS societe_nom,
        c.prenom AS contact_prenom, c.nom AS contact_nom, c.email AS contact_email,
        u.prenom AS crm_prenom, u.nom AS crm_nom,
        (SELECT COUNT(*) FROM ticket_messages m WHERE m.ticket_id=t.id) AS nb_messages
        FROM tickets t
        LEFT JOIN societes s ON s.id=t.societe_id
        LEFT JOIN contacts c ON c.id=t.contact_id
        LEFT JOIN crm_users u ON u.id=t.crm_user_id WHERE 1=1";
    $params=[];
    if ($statut)    { $sql.=" AND t.statut=?";     $params[]=$statut; }
    if ($societeId) { $sql.=" AND t.societe_id=?"; $params[]=$societeId; }
    if ($q)         { $sql.=" AND (t.titre LIKE ? OR t.numero LIKE ?)"; $p='%'.$q.'%'; $params=array_merge($params,[$p,$p]); }
    $sql .= " ORDER BY FIELD(t.statut,'nouveau','en_cours','attente_client','resolu','ferme'), t.date_creation DESC";
    $stmt=db()->prepare($sql); $stmt->execute($params);
    ok(['tickets'=>array_map('fmtTicket',$stmt->fetchAll())]);

case 'ticket_get':
    crmAuth(); $id=$_GET['id']??'';
    $stmt=db()->prepare("SELECT t.*, s.nom AS societe_nom, c.prenom AS contact_prenom, c.nom AS contact_nom, c.email AS contact_email, u.prenom AS crm_prenom, u.nom AS crm_nom, (SELECT COUNT(*) FROM ticket_messages m WHERE m.ticket_id=t.id) AS nb_messages FROM tickets t LEFT JOIN societes s ON s.id=t.societe_id LEFT JOIN contacts c ON c.id=t.contact_id LEFT JOIN crm_users u ON u.id=t.crm_user_id WHERE t.id=?");
    $stmt->execute([$id]); $t=$stmt->fetch(); if (!$t) err('Ticket introuvable',404);
    $msgs=db()->prepare('SELECT * FROM ticket_messages WHERE ticket_id=? ORDER BY date_creation ASC');
    $msgs->execute([$id]);
    ok(['ticket'=>fmtTicket($t),'messages'=>array_map('fmtMessage',$msgs->fetchAll())]);

case 'ticket_create':
    $u=crmAuth();
    $POST = !empty($_POST) ? $_POST : body();
    if (!trim($POST['titre']??'')) err('Titre obligatoire');
    $id=uuid(); $num=nextTicketNum();
    $desc = clean($POST['description']??'');
    db()->prepare("INSERT INTO tickets (id,numero,titre,description,societe_id,contact_id,crm_user_id,statut,categorie,source) VALUES (?,?,?,?,?,?,?,?,?,?)")
        ->execute([$id,$num,clean($POST['titre']),$desc,
            ($POST['societeId']??'')?:null, ($POST['contactId']??'')?:null, ($POST['crmUserId']??$u['id']),
            'nouveau',
            in_array($POST['categorie']??'',['incident','demande','question','autre'])?$POST['categorie']:'question',
            in_array($POST['source']??'',['espace_client','email','crm','telephone'])?$POST['source']:'crm']);
    // Message initial avec fichier optionnel
    if ($desc || !empty($_FILES['file'])) {
        $msgId = uuid();
        [$fp,$fn,$fs,$fm] = saveTicketFile($id, $_FILES['file']??null);
        db()->prepare("INSERT INTO ticket_messages (id,ticket_id,auteur_type,auteur_id,auteur_nom,contenu,visible_client,file_path,file_name,file_size,file_mime) VALUES (?,?,'crm_user',?,?,?,1,?,?,?,?)")
            ->execute([$msgId,$id,$u['id'],$u['prenom'].' '.$u['nom'],$desc?:('[Fichier joint]'),$fp,$fn,$fs,$fm]);
    }
    $ct=null;
    if (!empty($POST['contactId'])) { $cs=db()->prepare('SELECT * FROM contacts WHERE id=?'); $cs->execute([$POST['contactId']]); $ct=$cs->fetch(); }
    if ($ct && $ct['email']) {
        $tk2=db()->prepare("SELECT t.*, s.nom AS societe_nom FROM tickets t LEFT JOIN societes s ON s.id=t.societe_id WHERE t.id=?"); $tk2->execute([$id]);
        sendTicketEmail($tk2->fetch(), 'created', $ct['email'], $ct['prenom'], $POST['siteUrl']??KAP_SITE_URL);
    }
    $stmt2=db()->prepare("SELECT t.*, s.nom AS societe_nom, c.prenom AS contact_prenom, c.nom AS contact_nom, c.email AS contact_email, u.prenom AS crm_prenom, u.nom AS crm_nom, 0 AS nb_messages FROM tickets t LEFT JOIN societes s ON s.id=t.societe_id LEFT JOIN contacts c ON c.id=t.contact_id LEFT JOIN crm_users u ON u.id=t.crm_user_id WHERE t.id=?");
    $stmt2->execute([$id]);
    ok(['ticket'=>fmtTicket($stmt2->fetch()), 'numero'=>$num]);

case 'ticket_update_statut':
    $u=crmAuth(); $b=body(); $id=$b['id']??'';
    $newStatut=$b['statut']??'';
    if (!in_array($newStatut,['nouveau','en_cours','attente_client','resolu','ferme'])) err('Statut invalide');
    $dateRes = $newStatut==='resolu' ? date('Y-m-d H:i:s') : null;
    $old=db()->prepare('SELECT t.*, c.email AS contact_email, c.prenom AS contact_prenom, s.nom AS societe_nom FROM tickets t LEFT JOIN contacts c ON c.id=t.contact_id LEFT JOIN societes s ON s.id=t.societe_id WHERE t.id=?');
    $old->execute([$id]); $tk=$old->fetch(); if (!$tk) err('Ticket introuvable',404);
    db()->prepare('UPDATE tickets SET statut=?,date_resolution=? WHERE id=?')->execute([$newStatut,$dateRes,$id]);
    $statutLabels=['nouveau'=>'Nouveau','en_cours'=>'En cours','attente_client'=>'En attente client','resolu'=>'Resolu','ferme'=>'Ferme'];
    db()->prepare("INSERT INTO ticket_messages (id,ticket_id,auteur_type,auteur_nom,contenu,visible_client) VALUES (?,?,'system','Systeme',?,1)")
        ->execute([uuid(),$id,"Statut mis a jour : ".$statutLabels[$newStatut]]);
    if ($tk['contact_email']) {
        $tk['statut']=$newStatut;
        sendTicketEmail($tk, 'updated', $tk['contact_email'], $tk['contact_prenom'], $b['siteUrl']??KAP_SITE_URL);
    }
    ok();

case 'ticket_assign':
    crmAuth(); $b=body();
    db()->prepare('UPDATE tickets SET crm_user_id=? WHERE id=?')->execute([($b['crmUserId']??'')?:null, $b['id']??'']);
    ok();

case 'ticket_message_add':
    // Supporte multipart (avec fichier) ET JSON (sans fichier)
    $token=getToken(); if (!$token) err('Non authentifie',401);
    $isMultipart = !empty($_POST) || !empty($_FILES);
    if ($isMultipart) {
        $contenu=clean($_POST['contenu']??'');
        $visibleClient=filter_var($_POST['visibleClient']??'true',FILTER_VALIDATE_BOOLEAN);
        $ticketId=clean($_POST['ticketId']??'');
        $siteUrl=clean($_POST['siteUrl']??KAP_SITE_URL);
    } else {
        $b=body();
        $contenu=clean($b['contenu']??'');
        $visibleClient=(bool)($b['visibleClient']??true);
        $ticketId=$b['ticketId']??'';
        $siteUrl=$b['siteUrl']??KAP_SITE_URL;
    }
    if (!$contenu && empty($_FILES['file'])) err('Message ou fichier requis');
    if (!$contenu) $contenu='[Fichier joint]';

    $auteurType=null; $auteurId=null; $auteurNom='';
    $sess=db()->prepare("SELECT s.entity_id, u.* FROM sessions s JOIN crm_users u ON u.id=SUBSTRING(s.entity_id,5) WHERE s.id=? AND s.expires_at>NOW() AND s.entity_id LIKE 'crm:%'");
    $sess->execute([$token]); $crmU=$sess->fetch();
    if ($crmU) { $auteurType='crm_user'; $auteurId=$crmU['id']; $auteurNom=$crmU['prenom'].' '.$crmU['nom']; }
    else {
        $sess2=db()->prepare("SELECT s.*, c.* FROM sessions s JOIN contacts c ON c.id=s.entity_id WHERE s.id=? AND s.expires_at>NOW() AND s.entity_id NOT LIKE 'crm:%'");
        $sess2->execute([$token]); $ct=$sess2->fetch();
        if ($ct) { $auteurType='contact'; $auteurId=$ct['id']; $auteurNom=trim($ct['prenom'].' '.$ct['nom']); $visibleClient=true; }
    }
    if (!$auteurType) err('Non authentifie',401);

    $tk=db()->prepare('SELECT t.*, c.email AS contact_email, c.prenom AS contact_prenom, s.nom AS societe_nom FROM tickets t LEFT JOIN contacts c ON c.id=t.contact_id LEFT JOIN societes s ON s.id=t.societe_id WHERE t.id=?');
    $tk->execute([$ticketId]); $ticket=$tk->fetch(); if (!$ticket) err('Ticket introuvable',404);

    // Fichier optionnel
    $filePath=null; $fileName=null; $fileSize=null; $fileMime=null;
    if (!empty($_FILES['file']) && $_FILES['file']['error']===UPLOAD_ERR_OK) {
        $file=$_FILES['file'];
        if ($file['size']>10*1024*1024) err('Fichier trop volumineux (max 10 Mo)');
        $allowedExts=['jpg','jpeg','png','gif','pdf','doc','docx','xls','xlsx','txt','zip'];
        $ext=strtolower(pathinfo($file['name'],PATHINFO_EXTENSION));
        if (!in_array($ext,$allowedExts)) err('Type de fichier non autorisé');
        $dir=__DIR__.'/uploads/tickets/'.$ticketId;
        if (!is_dir($dir)) mkdir($dir,0755,true);
        $diskName=uuid().'-'.$ext; $filePath='uploads/tickets/'.$ticketId.'/'.$diskName;
        if (!move_uploaded_file($file['tmp_name'],__DIR__.'/'.$filePath)) err('Erreur sauvegarde fichier');
        $fileName=$file['name']; $fileSize=$file['size'];
        $mimes=['jpg'=>'image/jpeg','jpeg'=>'image/jpeg','png'=>'image/png','gif'=>'image/gif','pdf'=>'application/pdf','doc'=>'application/msword','docx'=>'application/vnd.openxmlformats-officedocument.wordprocessingml.document','xls'=>'application/vnd.ms-excel','xlsx'=>'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet','txt'=>'text/plain','zip'=>'application/zip'];
        $fileMime=$mimes[$ext]??'application/octet-stream';
    }

    $msgId=uuid();
    db()->prepare("INSERT INTO ticket_messages (id,ticket_id,auteur_type,auteur_id,auteur_nom,contenu,visible_client,file_path,file_name,file_size,file_mime) VALUES (?,?,?,?,?,?,?,?,?,?,?)")
        ->execute([$msgId,$ticketId,$auteurType,$auteurId,$auteurNom,$contenu,$visibleClient?1:0,$filePath,$fileName,$fileSize,$fileMime]);

    // Contact répond → repasser en_cours
    if ($auteurType==='contact' && in_array($ticket['statut'],['attente_client','resolu'])) {
        db()->prepare("UPDATE tickets SET statut='en_cours' WHERE id=?")->execute([$ticketId]);
        $msg = $ticket['statut']==='resolu' ? 'Ticket rouvert par le client' : 'Statut mis a jour : En cours (reponse client)';
        db()->prepare("INSERT INTO ticket_messages (id,ticket_id,auteur_type,auteur_nom,contenu,visible_client) VALUES (?,?,'system','Systeme',?,1)")->execute([uuid(),$ticketId,$msg]);
    }
    if ($auteurType==='crm_user' && $visibleClient && $ticket['contact_email']) {
        sendTicketEmail($ticket,'message',$ticket['contact_email'],$ticket['contact_prenom'],$siteUrl);
    }
    ok(['id'=>$msgId]);

case 'ticket_file_download':
    $token=getToken(); $canAccess=false;
    if ($token) {
        $adm=db()->prepare("SELECT id FROM sessions WHERE id=? AND entity_id LIKE 'crm:%' AND expires_at>NOW()");
        $adm->execute([$token]); if($adm->fetch()) $canAccess=true;
        else {
            $cli=db()->prepare("SELECT c.societe_id FROM sessions s JOIN contacts c ON c.id=s.entity_id WHERE s.id=? AND s.expires_at>NOW()");
            $cli->execute([$token]); $row=$cli->fetch();
            if ($row) {
                // Vérifier que le message appartient à un ticket de la société
                $msgId=$_GET['id']??'';
                $chk=db()->prepare("SELECT m.file_path FROM ticket_messages m JOIN tickets t ON t.id=m.ticket_id WHERE m.id=? AND t.societe_id=? AND m.visible_client=1");
                $chk->execute([$msgId,$row['societe_id']]); if($chk->fetch()) $canAccess=true;
            }
        }
    }
    if (!$canAccess) err('Accès refusé',403);
    $msgId=$_GET['id']??'';
    $stmt=db()->prepare('SELECT * FROM ticket_messages WHERE id=?'); $stmt->execute([$msgId]); $m=$stmt->fetch();
    if (!$m||!$m['file_path']) err('Fichier introuvable',404);
    $absPath=__DIR__.'/'.$m['file_path'];
    if (!file_exists($absPath)) err('Fichier non trouvé',404);
    $inline=str_starts_with($m['file_mime']??'','image/') || $m['file_mime']==='application/pdf';
    ob_clean();
    header('Content-Type: '.($m['file_mime']?:'application/octet-stream'));
    header('Content-Disposition: '.($inline?'inline':'attachment').'; filename="'.$m['file_name'].'"');
    header('Content-Length: '.filesize($absPath));
    readfile($absPath); exit;

case 'ticket_reopen':
    $u=crmAuth(); $b=body(); $id=$b['id']??'';
    db()->prepare("UPDATE tickets SET statut='en_cours',date_resolution=NULL WHERE id=?")->execute([$id]);
    db()->prepare("INSERT INTO ticket_messages (id,ticket_id,auteur_type,auteur_id,auteur_nom,contenu,visible_client) VALUES (?,?,'crm_user',?,?,'Ticket rouvert par l\'agent',1)")
        ->execute([uuid(),$id,$u['id'],$u['prenom'].' '.$u['nom']]);
    ok();

case 'ticket_delete':
    superAdmin(); $b=body();
    // Supprimer les fichiers joints
    $msgs=db()->prepare('SELECT file_path FROM ticket_messages WHERE ticket_id=? AND file_path IS NOT NULL'); $msgs->execute([$b['id']??'']);
    foreach($msgs->fetchAll() as $m){$fp=__DIR__.'/'.$m['file_path'];if(file_exists($fp))unlink($fp);}
    db()->prepare('DELETE FROM tickets WHERE id=?')->execute([$b['id']??'']);
    ok();

// ════════════════════════════════════════════════════════════════
//  TICKETS — Routes Espace Client
// ════════════════════════════════════════════════════════════════
case 'mes_tickets':
    $ct=contactAuth();
    $stmt=db()->prepare("SELECT t.*, s.nom AS societe_nom, c.prenom AS contact_prenom, c.nom AS contact_nom, c.email AS contact_email, u.prenom AS crm_prenom, u.nom AS crm_nom, (SELECT COUNT(*) FROM ticket_messages m WHERE m.ticket_id=t.id AND m.visible_client=1) AS nb_messages FROM tickets t LEFT JOIN societes s ON s.id=t.societe_id LEFT JOIN contacts c ON c.id=t.contact_id LEFT JOIN crm_users u ON u.id=t.crm_user_id WHERE t.societe_id=? ORDER BY FIELD(t.statut,'nouveau','en_cours','attente_client','resolu','ferme'), t.date_creation DESC");
    $stmt->execute([$ct['societe_id']]);
    ok(['tickets'=>array_map('fmtTicket',$stmt->fetchAll())]);

case 'mon_ticket':
    $ct=contactAuth(); $id=$_GET['id']??'';
    $stmt=db()->prepare("SELECT t.*, s.nom AS societe_nom, c.prenom AS contact_prenom, c.nom AS contact_nom, c.email AS contact_email, u.prenom AS crm_prenom, u.nom AS crm_nom, 0 AS nb_messages FROM tickets t LEFT JOIN societes s ON s.id=t.societe_id LEFT JOIN contacts c ON c.id=t.contact_id LEFT JOIN crm_users u ON u.id=t.crm_user_id WHERE t.id=? AND t.societe_id=?");
    $stmt->execute([$id, $ct['societe_id']]); $t=$stmt->fetch(); if (!$t) err('Ticket introuvable',404);
    $msgs=db()->prepare('SELECT * FROM ticket_messages WHERE ticket_id=? AND visible_client=1 ORDER BY date_creation ASC');
    $msgs->execute([$id]);
    ok(['ticket'=>fmtTicket($t), 'messages'=>array_map('fmtMessage',$msgs->fetchAll())]);

case 'ticket_client_create':
    $ct=contactAuth();
    $POST = !empty($_POST) ? $_POST : body();
    if (!trim($POST['titre']??'')) err('Titre obligatoire');
    $id=uuid(); $num=nextTicketNum();
    $desc = clean($POST['description']??'');
    db()->prepare("INSERT INTO tickets (id,numero,titre,description,societe_id,contact_id,statut,categorie,source) VALUES (?,?,?,?,?,?,'nouveau',?,?)")
        ->execute([$id,$num,clean($POST['titre']),$desc,$ct['societe_id'],$ct['id'],
            in_array($POST['categorie']??'',['incident','demande','question','autre'])?$POST['categorie']:'question',
            'espace_client']);
    if ($desc || !empty($_FILES['file'])) {
        $msgId = uuid();
        [$fp,$fn,$fs,$fm] = saveTicketFile($id, $_FILES['file']??null);
        db()->prepare("INSERT INTO ticket_messages (id,ticket_id,auteur_type,auteur_id,auteur_nom,contenu,visible_client,file_path,file_name,file_size,file_mime) VALUES (?,?,'contact',?,?,?,1,?,?,?,?)")
            ->execute([$msgId,$id,$ct['id'],trim($ct['prenom'].' '.$ct['nom']),$desc?:('[Fichier joint]'),$fp,$fn,$fs,$fm]);
    }
    ok(['ticket'=>['id'=>$id,'numero'=>$num]]);

// ════════════════════════════════════════════════════════════════
//  WEBHOOK EMAIL — Brevo Inbound Parsing
// ════════════════════════════════════════════════════════════════
case 'webhook_diag':
    // Diagnostic — log tout ce que Brevo envoie dans un fichier
    $secret=$_GET['secret']??''; if ($secret!==WEBHOOK_SECRET){http_response_code(403);exit;}
    $raw = file_get_contents('php://input');
    $method = $_SERVER['REQUEST_METHOD'];
    $ct = $_SERVER['CONTENT_TYPE'] ?? '';
    // Écrire dans un fichier log accessible
    $logFile = __DIR__ . '/webhook_diag.log';
    // Parser le JSON pour extraire les champs clés
    $parsed = json_decode($raw, true);
    $item = $parsed['items'][0] ?? null;
    $summary = $item ? [
        'from_name'    => $item['From']['Name'] ?? '?',
        'from_email'   => $item['From']['Address'] ?? '?',
        'subject'      => $item['Subject'] ?? '?',
        'has_html'     => isset($item['Html']) ? strlen($item['Html']) : 'NO',
        'has_text'     => isset($item['Text']) ? strlen($item['Text']) : 'NO',
        'keys'         => array_keys($item),
    ] : ['error' => 'no items[0]'];
    $logEntry = date('Y-m-d H:i:s') . ' | ' . $method . ' | ' . $ct . "
"
        . 'SUMMARY: ' . json_encode($summary) . "
"
        . substr($raw, 0, 500) . "
---
";
    file_put_contents($logFile, $logEntry, FILE_APPEND | LOCK_EX);
    ok(['method'=>$method,'content_type'=>$ct,'raw_length'=>strlen($raw),'summary'=>$summary]);

case 'webhook_email':
    $secret=$_GET['secret']??''; if ($secret!==WEBHOOK_SECRET){http_response_code(403);exit;}
    $b=body();
    // Brevo Inbound envoie les données dans items[0]
    // Format réel: {"items":[{"From":{"Name":"...","Address":"..."},"Subject":"...","Text":"...","Html":"..."}]}
    $item = isset($b['items'][0]) ? $b['items'][0] : $b;

    // Expéditeur
    if (isset($item['From']['Address'])) {
        $emailFrom = strtolower($item['From']['Address']);
        $fromName  = $item['From']['Name'] ?? '';
        $from      = $fromName ? "$fromName <$emailFrom>" : $emailFrom;
    } else {
        // Fallback pour les tests manuels
        $from = ($b['sender']??($b['From']??($b['from']??'')));
        preg_match('/[\w.\-+]+@[\w.\-]+\.\w+/', $from, $m);
        $emailFrom = strtolower($m[0]??'');
        $fromName  = '';
    }

    $subject  = $item['Subject'] ?? ($b['subject']??($b['Subject']??'Sans objet'));
    // Brevo Inbound utilise RawHtmlBody, RawTextBody ou ExtractedMarkdownMessage
    $bodyText = strip_tags(
        $item['RawHtmlBody'] ??
        $item['ExtractedMarkdownMessage'] ??
        $item['RawTextBody'] ??
        ($b['html']??($b['TextBody']??($b['text']??'')))
    );
    if (!$emailFrom){
        ok(['ignored'=>'no_email']);
    }
    $stmt=db()->prepare('SELECT c.*, s.nom AS societe_nom FROM contacts c JOIN societes s ON s.id=c.societe_id WHERE LOWER(c.email)=?');
    $stmt->execute([$emailFrom]); $ct=$stmt->fetch();

    if (!$ct) {
        // Expéditeur inconnu — créer ou récupérer la société fictive
        $SOC_FICTIVE = 'Contacts non identifiés';
        $socStmt = db()->prepare("SELECT id FROM societes WHERE nom=? AND type_contact='prospect' LIMIT 1");
        $socStmt->execute([$SOC_FICTIVE]); $socFictive = $socStmt->fetch();
        if (!$socFictive) {
            $socId = uuid();
            db()->prepare("INSERT INTO societes (id,nom,type_contact,source) VALUES (?,?,'prospect','email')")
                ->execute([$socId, $SOC_FICTIVE]);
        } else {
            $socId = $socFictive['id'];
        }
        // Extraire le nom depuis le champ From
        $displayName = isset($fromName) && $fromName ? $fromName : preg_replace('/<[^>]+>/', '', $from);
        $displayName = trim(str_replace('"', '', $displayName));
        $nameParts   = $displayName && $displayName !== $emailFrom
            ? array_filter(explode(' ', $displayName))
            : [];
        $prenom = $nameParts ? array_shift($nameParts) : '';
        $nomFam = $nameParts ? implode(' ', $nameParts) : '';
        if (!$prenom && !$nomFam) { $prenom = $emailFrom; }
        // Créer le contact
        $ctId = uuid();
        db()->prepare("INSERT INTO contacts (id,societe_id,prenom,nom,email,statut_espace) VALUES (?,?,?,?,?,'non_invite')")
            ->execute([$ctId, $socId, $prenom, $nomFam, $emailFrom]);
        $contactId = $ctId;
        $societeId = $socId;
        $auteurNom = trim("$prenom $nomFam") ?: $emailFrom;
    } else {
        $contactId = $ct['id'];
        $societeId = $ct['societe_id'];
        $auteurNom = trim($ct['prenom'].' '.$ct['nom']) ?: $emailFrom;
    }
    $id=uuid(); $num=nextTicketNum();
    $titre=mb_substr(trim($subject),0,255)?:'Email de '.$emailFrom;
    $desc = $bodyText ?: '(message vide)';
    db()->prepare("INSERT INTO tickets (id,numero,titre,description,societe_id,contact_id,statut,categorie,source,email_source) VALUES (?,?,?,?,?,?,'nouveau','question','email',?)")
        ->execute([$id,$num,$titre,mb_substr($desc,0,5000),$societeId,$contactId,$emailFrom]);
    db()->prepare("INSERT INTO ticket_messages (id,ticket_id,auteur_type,auteur_nom,contenu,visible_client) VALUES (?,?,'contact',?,?,1)")
        ->execute([uuid(),$id,$auteurNom,mb_substr($desc,0,5000)]);
    ok(['created'=>true,'numero'=>$num,'contact'=>$auteurNom,'known'=>(bool)$ct]);


// ════════════════════════════════════════════════════════════════
//  PARAMÈTRES DE NOTIFICATION
// ════════════════════════════════════════════════════════════════
case 'notif_settings_get':
    crmAuth();
    try {
        $rows = db()->query("SELECT statut,label,notify_client FROM notification_settings ORDER BY id")->fetchAll();
    } catch (Exception $e) {
        // Table pas encore créée
        $rows = [];
    }
    ok(['settings' => $rows]);

case 'notif_settings_update':
    crmAuth(); $b = body();
    // $b = [ ['statut'=>'nouveau','notify_client'=>true], ... ]
    foreach (($b['settings']??[]) as $s) {
        try {
            db()->prepare("UPDATE notification_settings SET notify_client=? WHERE statut=?")
                ->execute([$s['notify_client'] ? 1 : 0, $s['statut']]);
        } catch (Exception $e) {}
    }
    ok();


// ════════════════════════════════════════════════════════════════
//  PARAMÈTRES APPLICATIFS
// ════════════════════════════════════════════════════════════════
case 'app_settings_get':
    crmAuth();
    try {
        $rows = db()->query("SELECT cle,valeur,label,description FROM app_settings ORDER BY cle")->fetchAll();
    } catch (Exception $e) { $rows = []; }
    ok(['settings' => $rows]);

case 'app_settings_update':
    superAdmin(); $b = body();
    foreach (($b['settings'] ?? []) as $s) {
        $cle = preg_replace('/[^a-z0-9_]/', '', $s['cle'] ?? '');
        if (!$cle) continue;
        try {
            db()->prepare("INSERT INTO app_settings (cle,valeur,label,description) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE valeur=?")
                ->execute([$cle, $s['valeur']??'', $s['label']??$cle, $s['description']??'', $s['valeur']??'']);
        } catch (Exception $e) {}
    }
    ok();

case 'linkedin_extract':
    crmAuth(); $b=body();
    $text = trim($b['text']??'');
    $target = $b['target']??'soc'; // 'soc' ou 'contact'
    if (!$text) err('Texte vide');
    if (strlen($text) > 8000) $text = substr($text, 0, 8000);

    if ($target === 'soc') {
        $prompt = "Tu es un assistant qui extrait des informations d'entreprise depuis du texte LinkedIn.
Analyse ce texte et extrais UNIQUEMENT les informations disponibles dans un objet JSON strict.
Ne genere jamais de donnees inventees. Laisse le champ vide si information absente.

Champs a extraire :
- nom : nom de la societe (string)
- siteWeb : URL du site web (string, avec https://)
- linkedin : URL LinkedIn de la societe (string)
- secteur : secteur activite (string)
- taille : parmi ces valeurs exactes uniquement : 1-10, 11-50, 51-200, 201-500, 500+ (string ou vide)
- adresse1 : numero et rue (string)
- codePostal : code postal (string)
- ville : ville (string)
- pays : pays (string, defaut France)
- siren : numero SIREN si present (string, 9 chiffres)
- notes : description courte max 200 caracteres (string)

Reponds UNIQUEMENT avec le JSON, sans markdown, sans explication.

Texte LinkedIn :
" . $text;
    } else {
        $prompt = "Tu es un assistant qui extrait des informations de contact depuis du texte LinkedIn.
Analyse ce texte et extrais UNIQUEMENT les informations disponibles dans un objet JSON strict.
Ne genere jamais de donnees inventees. Laisse le champ vide si information absente.

Champs a extraire :
- prenom : prenom (string)
- nom : nom de famille (string)
- poste : intitule du poste (string)
- email : adresse email si visible (string)
- telephone : numero de telephone si visible (string)
- telephone2 : deuxieme numero si present (string)
- linkedin : URL du profil LinkedIn (string)
- societeNom : nom employeur actuel (string)
- notes : resume professionnel court max 200 caracteres (string)

Reponds UNIQUEMENT avec le JSON, sans markdown, sans explication.

Texte LinkedIn :
" . $text;
    }

    // Lire la clé API depuis kap-config ou env
    $apiKey = defined('KAP_ANTHROPIC_KEY') ? KAP_ANTHROPIC_KEY : (getenv('ANTHROPIC_API_KEY') ?: '');
    if (!$apiKey) err('Clé API Anthropic non configurée dans kap-config.php');

    $payload = json_encode([
        'model'      => 'claude-sonnet-4-6',
        'max_tokens' => 800,
        'messages'   => [['role'=>'user','content'=>$prompt]],
    ]);

    $ch = curl_init('https://api.anthropic.com/v1/messages');
    curl_setopt_array($ch, [
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_POST           => true,
        CURLOPT_POSTFIELDS     => $payload,
        CURLOPT_HTTPHEADER     => [
            'Content-Type: application/json',
            'x-api-key: ' . $apiKey,
            'anthropic-version: 2023-06-01',
        ],
        CURLOPT_TIMEOUT => 30,
    ]);
    $resp = curl_exec($ch);
    $code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    $cerr = curl_error($ch);
    curl_close($ch);

    if ($cerr) err('Erreur réseau : ' . $cerr);
    if ($code !== 200) {
        $bd = json_decode($resp, true);
        err('Erreur API : ' . ($bd['error']['message'] ?? 'HTTP ' . $code));
    }

    $data = json_decode($resp, true);
    $raw  = trim($data['content'][0]['text'] ?? '');
    $raw  = preg_replace('/^```json\s*/','', $raw);
    $raw  = preg_replace('/^```\s*/','',  $raw);
    $raw  = preg_replace('/\s*```$/','',  $raw);

    $extracted = json_decode($raw, true);
    if (!$extracted) err('Réponse IA invalide : ' . $raw);
    ok(['data' => $extracted]);

case 'setup_brevo_inbound':
    superAdmin();
    $webhookUrl = KAP_SITE_URL . '/api.php?action=webhook_email&secret=' . WEBHOOK_SECRET;
    // Selon la doc Brevo : type=inbound, events optionnel (défaut = inboundEmailProcessed)
    // Le domain doit correspondre à un domaine préalablement configuré dans Brevo
    $payload = json_encode([
        'url'    => $webhookUrl,
        'type'   => 'inbound',
        'events' => ['inboundEmailProcessed'],
        'domain' => 'support.kapdsi.fr',
    ]);
    // Note: le domaine doit être préalablement authentifié via PUT /v3/senders/domains/{domain}/authenticate
    $ch = curl_init('https://api.brevo.com/v3/webhooks');
    curl_setopt_array($ch, [CURLOPT_RETURNTRANSFER=>true,CURLOPT_POST=>true,CURLOPT_POSTFIELDS=>$payload,
        CURLOPT_HTTPHEADER=>['accept: application/json','content-type: application/json','api-key: '.KAP_BREVO_API_KEY],CURLOPT_TIMEOUT=>15]);
    $resp = curl_exec($ch); $code = curl_getinfo($ch, CURLINFO_HTTP_CODE); curl_close($ch);
    $data = json_decode($resp, true);
    if ($code === 201) {
        ok(['message'=>'Webhook Brevo créé avec succès','webhookId'=>$data['id']??null,'url'=>$webhookUrl]);
    } else {
        // Retourner la réponse brute Brevo pour diagnostic
        ok(['ok_brevo'=>false,'httpCode'=>$code,'brevo_error'=>$data,'payload_sent'=>json_decode($payload,true)]);
    }

case 'setup_brevo_transactionnel':
    superAdmin();
    $webhookUrl = KAP_SITE_URL . '/api.php?action=webhook_campagne&secret=' . WEBHOOK_SECRET;
    $payload = json_encode([
        'url'    => $webhookUrl,
        'type'   => 'transactional',
        'events' => ['delivered','opened','click','hardBounce','softBounce','unsubscribed','spam'],
    ]);
    $ch = curl_init('https://api.brevo.com/v3/webhooks');
    curl_setopt_array($ch, [CURLOPT_RETURNTRANSFER=>true,CURLOPT_POST=>true,CURLOPT_POSTFIELDS=>$payload,
        CURLOPT_HTTPHEADER=>['accept: application/json','content-type: application/json','api-key: '.KAP_BREVO_API_KEY],CURLOPT_TIMEOUT=>15]);
    $resp = curl_exec($ch); $code = curl_getinfo($ch, CURLINFO_HTTP_CODE); curl_close($ch);
    $data = json_decode($resp, true);
    if ($code === 201) {
        ok(['message'=>'Webhook transactionnel créé','webhookId'=>$data['id']??null,'url'=>$webhookUrl]);
    } else {
        ok(['ok_brevo'=>false,'httpCode'=>$code,'brevo_error'=>$data]);
    }

case 'setup_brevo_campagnes':
    superAdmin();
    $webhookUrl = KAP_SITE_URL . '/api.php?action=webhook_campagne&secret=' . WEBHOOK_SECRET;
    $payload = json_encode([
        'url'    => $webhookUrl,
        'type'   => 'marketing',
        'events' => ['delivered','opened','click','softBounce','hardBounce','unsubscribed','spam'],
    ]);
    $ch = curl_init('https://api.brevo.com/v3/webhooks');
    curl_setopt_array($ch, [CURLOPT_RETURNTRANSFER=>true,CURLOPT_POST=>true,CURLOPT_POSTFIELDS=>$payload,
        CURLOPT_HTTPHEADER=>['accept: application/json','content-type: application/json','api-key: '.KAP_BREVO_API_KEY],CURLOPT_TIMEOUT=>15]);
    $resp = curl_exec($ch); $code = curl_getinfo($ch, CURLINFO_HTTP_CODE); curl_close($ch);
    $data = json_decode($resp, true);
    if ($code === 201) {
        ok(['message'=>'Webhook campagnes créé','webhookId'=>$data['id']??null,'url'=>$webhookUrl]);
    } else {
        ok(['ok_brevo'=>false,'httpCode'=>$code,'brevo_error'=>$data]);
    }

case 'list_brevo_webhooks':
    superAdmin();
    $ch = curl_init('https://api.brevo.com/v3/webhooks?sort=desc&limit=50');
    curl_setopt_array($ch, [CURLOPT_RETURNTRANSFER=>true,
        CURLOPT_HTTPHEADER=>['accept: application/json','api-key: '.KAP_BREVO_API_KEY],CURLOPT_TIMEOUT=>10]);
    $resp = curl_exec($ch); curl_close($ch);
    $data = json_decode($resp, true);
    ok(['webhooks'=>$data['webhooks']??[]]);


// ════════════════════════════════════════════════════════════════
//  CAMPAGNES EMAIL
// ════════════════════════════════════════════════════════════════
case 'campagnes_list':
    crmAuth();
    $rows = db()->query("SELECT c.*,
        (SELECT COUNT(*) FROM campagne_destinataires d WHERE d.campagne_id=c.id) AS nb_dest,
        (SELECT COUNT(DISTINCT s.email) FROM campagne_stats s WHERE s.campagne_id=c.id AND s.event='delivered') AS nb_delivered,
        (SELECT COUNT(DISTINCT s.email) FROM campagne_stats s WHERE s.campagne_id=c.id AND s.event='opened') AS nb_opened,
        (SELECT COUNT(DISTINCT s.email) FROM campagne_stats s WHERE s.campagne_id=c.id AND s.event='clicked') AS nb_clicked,
        (SELECT COUNT(DISTINCT s.email) FROM campagne_stats s WHERE s.campagne_id=c.id AND s.event IN ('hardBounce','softBounce','hard_bounce','soft_bounce')) AS nb_bounce,
        u.prenom AS crm_prenom, u.nom AS crm_nom
        FROM campagnes c
        LEFT JOIN crm_users u ON u.id=c.crm_user_id
        ORDER BY c.date_creation DESC")->fetchAll();
    ok(['campagnes' => array_map(function($r){return [
        'id'=>$r['id'],'nom'=>$r['nom'],'sujet'=>$r['sujet'],
        'statut'=>$r['statut'],'expediteurNom'=>$r['expediteur_nom'],
        'expediteurEmail'=>$r['expediteur_email'],
        'nbDest'=>(int)$r['nb_dest'],'nbDelivered'=>(int)$r['nb_delivered'],
        'nbOpened'=>(int)$r['nb_opened'],'nbClicked'=>(int)$r['nb_clicked'],
        'nbBounce'=>(int)$r['nb_bounce'],
        'dateEnvoiPlanifie'=>$r['date_envoi_planifie']??'',
        'dateEnvoiReel'=>$r['date_envoi_reel']??'',
        'crmUserNom'=>trim(($r['crm_prenom']??'').' '.($r['crm_nom']??'')),
        'dateCreation'=>$r['date_creation'],
    ];},$rows)]);

case 'campagne_get':
    crmAuth(); $id=$_GET['id']??'';
    $c=db()->prepare("SELECT * FROM campagnes WHERE id=?"); $c->execute([$id]); $camp=$c->fetch();
    if (!$camp) err('Campagne introuvable',404);
    $dests=db()->prepare("SELECT cd.*,ct.prenom,ct.nom AS contact_nom FROM campagne_destinataires cd LEFT JOIN contacts ct ON ct.id=cd.contact_id WHERE cd.campagne_id=? ORDER BY cd.email"); $dests->execute([$id]);
    $stats=db()->query("SELECT CASE event WHEN 'hard_bounce' THEN 'hardBounce' WHEN 'soft_bounce' THEN 'softBounce' WHEN 'click' THEN 'clicked' WHEN 'open' THEN 'opened' ELSE event END AS event, COUNT(DISTINCT email) AS n FROM campagne_stats WHERE campagne_id=".db()->quote($id)." GROUP BY 1")->fetchAll();
    $statsMap=[]; foreach($stats as $s) $statsMap[$s['event']]=(int)$s['n'];
    ok(['campagne'=>[
        'id'=>$camp['id'],'nom'=>$camp['nom'],'sujet'=>$camp['sujet'],
        'contenuHtml'=>$camp['contenu_html']??'','contenuTexte'=>$camp['contenu_texte']??'',
        'expediteurNom'=>$camp['expediteur_nom'],'expediteurEmail'=>$camp['expediteur_email'],
        'statut'=>$camp['statut'],'brevoCampaignId'=>$camp['brevo_campaign_id'],
        'dateEnvoiPlanifie'=>$camp['date_envoi_planifie']??'',
        'dateEnvoiReel'=>$camp['date_envoi_reel']??'',
        'dateCreation'=>$camp['date_creation'],
    ],'destinataires'=>$dests->fetchAll(),'stats'=>$statsMap]);

case 'campagne_create':
    $u=crmAuth(); $b=body();
    if (!trim($b['nom']??'')) err('Nom obligatoire');
    if (!trim($b['sujet']??'')) err('Sujet obligatoire');
    $id=uuid();
    $expedNom   = appSetting('sender_name', KAP_SENDER_NAME);
    $expedEmail = KAP_SENDER_EMAIL;
    db()->prepare("INSERT INTO campagnes (id,nom,sujet,contenu_html,contenu_texte,expediteur_nom,expediteur_email,statut,crm_user_id) VALUES (?,?,?,?,?,?,?,'brouillon',?)")
        ->execute([$id,clean($b['nom']),clean($b['sujet']),$b['contenuHtml']??'',$b['contenuTexte']??'',$expedNom,$expedEmail,$u['id']]);
    ok(['id'=>$id]);

case 'campagne_update':
    crmAuth(); $b=body(); $id=$b['id']??'';
    db()->prepare("UPDATE campagnes SET nom=?,sujet=?,contenu_html=?,contenu_texte=?,expediteur_nom=?,expediteur_email=?,date_envoi_planifie=? WHERE id=? AND statut='brouillon'")
        ->execute([clean($b['nom']??''),clean($b['sujet']??''),$b['contenuHtml']??'',$b['contenuTexte']??'',$b['expediteurNom']??'',clean($b['expediteurEmail']??''),$b['dateEnvoiPlanifie']??null,$id]);
    ok();

case 'campagne_destinataires_set':
    crmAuth(); $b=body(); $id=$b['id']??'';
    // Vérifier que la campagne est en brouillon
    $chk=db()->prepare("SELECT id FROM campagnes WHERE id=? AND statut='brouillon'"); $chk->execute([$id]); if(!$chk->fetch()) err('Campagne non modifiable');
    db()->prepare("DELETE FROM campagne_destinataires WHERE campagne_id=?")->execute([$id]);
    $ins=db()->prepare("INSERT INTO campagne_destinataires (id,campagne_id,contact_id,email,nom) VALUES (?,?,?,?,?)");
    foreach (($b['destinataires']??[]) as $d) {
        if (!filter_var($d['email']??'',FILTER_VALIDATE_EMAIL)) continue;
        $ins->execute([uuid(),$id,$d['contactId']??null,strtolower($d['email']),clean($d['nom']??'')]);
    }
    $n=db()->prepare("SELECT COUNT(*) FROM campagne_destinataires WHERE campagne_id=?"); $n->execute([$id]);
    ok(['count'=>(int)$n->fetchColumn()]);

case 'campagne_send':
    $u=crmAuth(); $b=body(); $id=$b['id']??'';
    $camp=db()->prepare("SELECT * FROM campagnes WHERE id=? AND statut='brouillon'"); $camp->execute([$id]); $c=$camp->fetch();
    if (!$c) err('Campagne introuvable ou déjà envoyée');
    $dests=db()->prepare("SELECT * FROM campagne_destinataires WHERE campagne_id=?"); $dests->execute([$id]);
    $destList=$dests->fetchAll();
    if (!count($destList)) err('Aucun destinataire');
    if (!$c['contenu_html'] && !$c['contenu_texte']) err('Contenu vide');

    $htmlBase = $c['contenu_html'] ?: '<html><body>'.nl2br(htmlspecialchars($c['contenu_texte'])).'</body></html>';
    $nbOk=0; $nbErr=0; $errors=[];

    // Envoi transactionnel un par un via /v3/smtp/email (pas de liste nécessaire)
    foreach ($destList as $dest) {
        $prenom = explode(' ', trim($dest['nom']??''))[0] ?: $dest['email'];
        $html = str_replace('{{contact.PRENOM}}', htmlspecialchars($prenom), $htmlBase);
        $payload = json_encode([
            'sender'      => ['name'=>$c['expediteur_nom'],'email'=>$c['expediteur_email']],
            'to'          => [['email'=>$dest['email'],'name'=>($dest['nom']??'')?:$dest['email']]],
            'subject'     => $c['sujet'],
            'htmlContent' => $html,
            'headers'     => ['X-KAP-Campaign-Id'=>$id],
            'tags'        => [$id],
        ]);
        $ch=curl_init('https://api.brevo.com/v3/smtp/email');
        curl_setopt_array($ch,[CURLOPT_RETURNTRANSFER=>true,CURLOPT_POST=>true,CURLOPT_POSTFIELDS=>$payload,
            CURLOPT_HTTPHEADER=>['accept: application/json','content-type: application/json','api-key: '.KAP_BREVO_API_KEY],CURLOPT_TIMEOUT=>10]);
        $resp=curl_exec($ch); $code=curl_getinfo($ch,CURLINFO_HTTP_CODE); curl_close($ch);
        if ($code===201) { $nbOk++; }
        else { $nbErr++; $errors[]=$dest['email'].': '.($code); }
    }

    db()->prepare("UPDATE campagnes SET statut='envoyee',date_envoi_reel=NOW() WHERE id=?")->execute([$id]);
    ok(['nbOk'=>$nbOk,'nbErr'=>$nbErr,'nbDest'=>count($destList),'errors'=>$errors]);

case 'campagne_delete':
    crmAuth(); $b=body(); $id=$b['id']??'';
    // Supprimer stats et destinataires (CASCADE) puis la campagne
    db()->prepare("DELETE FROM campagne_stats WHERE campagne_id=?")->execute([$id]);
    db()->prepare("DELETE FROM campagne_destinataires WHERE campagne_id=?")->execute([$id]);
    db()->prepare("DELETE FROM campagnes WHERE id=?")->execute([$id]);
    ok();

// ── Webhook stats campagnes (Brevo → Hub) ─────────────────────
case 'webhook_campagne':
    $secret=$_GET['secret']??''; if ($secret!==WEBHOOK_SECRET){http_response_code(403);exit;}
    $b=body();
    // Brevo envoie un tableau d'événements
    $events = isset($b[0]) ? $b : [$b];
    // Vérifier que l'email a bien été envoyé depuis cet environnement
    // en vérifiant que la campagne existe dans cette base
    // (évite la pollution croisée recette/prod quand les bases ont les mêmes UUIDs)
    foreach ($events as $ev) {
        $email = strtolower($ev['email']??'');
        $event = $ev['event']??'';
        // Normalisation des noms d'événements
        if ($event==='click')        $event='clicked';
        if ($event==='open')         $event='opened';
        if ($event==='unsubscribed') $event='unsubscribe';
        if ($event==='hard_bounce')  $event='hardBounce';
        if ($event==='soft_bounce')  $event='softBounce';
        $url = $ev['link']??($ev['url']??'');
        if (!$email||!$event) continue;

        // Retrouver la campagne via les tags (envoi transactionnel)
        $tags = $ev['tags']??[];
        if (is_string($tags)) $tags = [$tags];
        $campId = null;
        foreach ($tags as $tag) {
            $stmt=db()->prepare("SELECT id FROM campagnes WHERE id=?");
            $stmt->execute([$tag]); $camp=$stmt->fetch();
            if ($camp) { $campId=$camp['id']; break; }
        }
        // Fallback : retrouver par brevo_campaign_id (campagnes classiques)
        if (!$campId) {
            $campBrevoId = $ev['campaignId']??null;
            if ($campBrevoId) {
                $stmt=db()->prepare("SELECT id FROM campagnes WHERE brevo_campaign_id=?");
                $stmt->execute([$campBrevoId]); $camp=$stmt->fetch();
                if ($camp) $campId=$camp['id'];
            }
        }
        if ($campId) {
            db()->prepare("INSERT INTO campagne_stats (id,campagne_id,email,event,url) VALUES (?,?,?,?,?)")
                ->execute([uuid(),$campId,$email,$event,mb_substr($url,0,500)]);
        }
    }
    ok(['processed'=>count($events)]);

case 'campagne_stats_detail':
    crmAuth(); $id=$_GET['id']??'';
    $rows=db()->prepare("SELECT cs.email,cs.event,cs.url,cs.date_event,cd.nom FROM campagne_stats cs LEFT JOIN campagne_destinataires cd ON cd.email=cs.email AND cd.campagne_id=? WHERE cs.campagne_id=? ORDER BY cs.date_event DESC LIMIT 500");
    $rows->execute([$id,$id]);
    ok(['events'=>$rows->fetchAll()]);


// ════════════════════════════════════════════════════════════════
//  NOTES DE FRAIS
// ════════════════════════════════════════════════════════════════

// ── Véhicules ────────────────────────────────────────────────────
case 'ndf_vehicules_list':
    crmAuth();
    $rows = db()->query("SELECT * FROM ndf_vehicules WHERE actif=1 ORDER BY nom")->fetchAll();
    ok(['vehicules' => array_map(fn($v)=>[
        'id'=>$v['id'],'nom'=>$v['nom'],'type'=>$v['type'],
        'cvFiscaux'=>$v['cv_fiscaux'],'immatriculation'=>$v['immatriculation']??'',
    ], $rows)]);

case 'ndf_vehicule_save':
    crmAuth(); $b=body(); $id=$b['id']??'';
    if (!$id) {
        $id=uuid();
        db()->prepare("INSERT INTO ndf_vehicules (id,nom,type,cv_fiscaux,immatriculation,crm_user_id) VALUES (?,?,?,?,?,?)")
            ->execute([$id,clean($b['nom']??''),$b['type']??'voiture',$b['cvFiscaux']??(int)$b['cvFiscaux']??null,clean($b['immatriculation']??''),$b['crmUserId']??null]);
    } else {
        db()->prepare("UPDATE ndf_vehicules SET nom=?,type=?,cv_fiscaux=?,immatriculation=? WHERE id=?")
            ->execute([clean($b['nom']??''),$b['type']??'voiture',$b['cvFiscaux']?null:(int)$b['cvFiscaux'],clean($b['immatriculation']??''),$id]);
    }
    ok(['id'=>$id]);

case 'ndf_vehicule_delete':
    crmAuth(); $b=body();
    db()->prepare("UPDATE ndf_vehicules SET actif=0 WHERE id=?")->execute([$b['id']??'']);
    ok();

// ── Barèmes ───────────────────────────────────────────────────────
case 'ndf_baremes_list':
    crmAuth();
    $annee = (int)($_GET['annee']??date('Y'));
    $rows = db()->prepare("SELECT * FROM ndf_baremes WHERE annee=? ORDER BY type,cv_min")->execute([$annee]) ? db()->query("SELECT * FROM ndf_baremes WHERE annee=$annee ORDER BY type,cv_min")->fetchAll() : [];
    $stmt = db()->prepare("SELECT * FROM ndf_baremes WHERE annee=? ORDER BY type,cv_min"); $stmt->execute([$annee]);
    ok(['baremes'=>$stmt->fetchAll(),'annee'=>$annee]);

case 'ndf_taux_km':
    // Calcule le taux kilométrique pour un véhicule donné
    crmAuth();
    $vehiculeId = $_GET['vehiculeId']??'';
    $annee = (int)($_GET['annee']??date('Y'));
    $v = db()->prepare("SELECT * FROM ndf_vehicules WHERE id=?"); $v->execute([$vehiculeId]); $veh=$v->fetch();
    if (!$veh) err('Véhicule introuvable',404);
    $type = $veh['type']==='moto' ? 'moto' : ($veh['type']==='velo' ? 'velo' : 'voiture');
    $cv = (int)($veh['cv_fiscaux']??5);
    $b = db()->prepare("SELECT taux_km FROM ndf_baremes WHERE annee=? AND type=? AND cv_min<=? AND (cv_max>=? OR cv_max IS NULL) ORDER BY cv_min DESC LIMIT 1");
    $b->execute([$annee,$type,$cv,$cv]); $bareme=$b->fetch();
    ok(['taux'=>$bareme?$bareme['taux_km']:0,'cv'=>$cv,'type'=>$type,'annee'=>$annee]);

// ── Dépenses ──────────────────────────────────────────────────────
case 'ndf_list':
    crmAuth();
    $mois  = $_GET['mois']??'';
    $annee = $_GET['annee']??date('Y');
    $statut= $_GET['statut']??'';
    $sql = "SELECT d.*, s.nom AS societe_nom, v.nom AS vehicule_nom
            FROM ndf_depenses d
            LEFT JOIN societes s ON s.id=d.societe_id
            LEFT JOIN ndf_vehicules v ON v.id=d.vehicule_id
            WHERE 1=1";
    $params=[];
    if ($mois)   { $sql.=" AND DATE_FORMAT(d.date_depense,'%Y-%m')=?"; $params[]=sprintf('%04d-%02d',(int)$annee,(int)$mois); }
    elseif ($annee) { $sql.=" AND YEAR(d.date_depense)=?"; $params[]=(int)$annee; }
    if ($statut) { $sql.=" AND d.statut=?"; $params[]=$statut; }
    $sql.=" ORDER BY d.date_depense DESC";
    $stmt=db()->prepare($sql); $stmt->execute($params);
    $rows=$stmt->fetchAll();
    $total=array_sum(array_column($rows,'montant_ttc'));
    ok(['depenses'=>array_map(fn($r)=>[
        'id'=>$r['id'],'date'=>$r['date_depense'],'categorie'=>$r['categorie'],
        'libelle'=>$r['libelle'],'fournisseur'=>$r['fournisseur']??'',
        'montantTtc'=>(float)$r['montant_ttc'],'montantHt'=>(float)($r['montant_ht']??0),
        'tva'=>(float)($r['tva']??0),'tvaRecuperable'=>(bool)$r['tva_recuperable'],
        'typeFrais'=>$r['type_frais'],'vehiculeId'=>$r['vehicule_id']??'',
        'vehiculeNom'=>$r['vehicule_nom']??'','km'=>(float)($r['km']??0),
        'depart'=>$r['depart']??'','arrivee'=>$r['arrivee']??'','tauxKm'=>(float)($r['taux_km']??0),
        'societeId'=>$r['societe_id']??'','societeNom'=>$r['societe_nom']??'',
        'refacturable'=>(bool)$r['refacturable'],'factureId'=>$r['facture_id']??'',
        'justificatifPath'=>$r['justificatif_path']??'','justificatifNom'=>$r['justificatif_nom']??'',
        'statut'=>$r['statut'],'notes'=>$r['notes']??'','dateCreation'=>$r['date_creation'],
    ], $rows),'total'=>$total]);

case 'ndf_save':
    $u=crmAuth(); $b=body(); $id=$b['id']??'';
    $montantTtc = (float)($b['montantTtc']??0);
    $montantHt  = isset($b['montantHt'])  ? (float)$b['montantHt']  : null;
    $tva        = isset($b['tva'])        ? (float)$b['tva']        : null;
    // Pour les frais km, calculer le montant
    if (($b['typeFrais']??'depense')==='kilometrique') {
        $km = (float)($b['km']??0);
        $taux = (float)($b['tauxKm']??0);
        $montantTtc = round($km * $taux, 2);
        $montantHt  = $montantTtc;
        $tva        = 0;
    }
    if (!$id) {
        $id=uuid();
        db()->prepare("INSERT INTO ndf_depenses (id,crm_user_id,date_depense,categorie,libelle,fournisseur,montant_ttc,montant_ht,tva,tva_recuperable,type_frais,vehicule_id,km,depart,arrivee,taux_km,societe_id,refacturable,statut,notes) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)")
            ->execute([$id,$u['id'],clean($b['date']??date('Y-m-d')),clean($b['categorie']??'autre'),clean($b['libelle']??''),clean($b['fournisseur']??''),$montantTtc,$montantHt,$tva,($b['tvaRecuperable']??false)?1:0,clean($b['typeFrais']??'depense'),$b['vehiculeId']??null,$b['km']??null,clean($b['depart']??''),clean($b['arrivee']??''),$b['tauxKm']??null,$b['societeId']??null,($b['refacturable']??false)?1:0,clean($b['statut']??'brouillon'),clean($b['notes']??'')]);
    } else {
        db()->prepare("UPDATE ndf_depenses SET date_depense=?,categorie=?,libelle=?,fournisseur=?,montant_ttc=?,montant_ht=?,tva=?,tva_recuperable=?,type_frais=?,vehicule_id=?,km=?,depart=?,arrivee=?,taux_km=?,societe_id=?,refacturable=?,statut=?,notes=? WHERE id=?")
            ->execute([clean($b['date']??date('Y-m-d')),clean($b['categorie']??'autre'),clean($b['libelle']??''),clean($b['fournisseur']??''),$montantTtc,$montantHt,$tva,($b['tvaRecuperable']??false)?1:0,clean($b['typeFrais']??'depense'),$b['vehiculeId']??null,$b['km']??null,clean($b['depart']??''),clean($b['arrivee']??''),$b['tauxKm']??null,$b['societeId']??null,($b['refacturable']??false)?1:0,clean($b['statut']??'brouillon'),clean($b['notes']??''),$id]);
    }
    ok(['id'=>$id,'montantTtc'=>$montantTtc]);

case 'ndf_delete':
    crmAuth(); $b=body(); $id=$b['id']??'';
    // Supprimer justificatif si présent
    $d=db()->prepare("SELECT justificatif_path FROM ndf_depenses WHERE id=?"); $d->execute([$id]); $dep=$d->fetch();
    if ($dep && $dep['justificatif_path'] && file_exists($dep['justificatif_path'])) @unlink($dep['justificatif_path']);
    db()->prepare("DELETE FROM ndf_depenses WHERE id=?")->execute([$id]);
    ok();

case 'ndf_valider':
    crmAuth(); $b=body(); $ids=$b['ids']??[];
    foreach ($ids as $id) {
        db()->prepare("UPDATE ndf_depenses SET statut='validee' WHERE id=?")->execute([$id]);
    }
    ok(['count'=>count($ids)]);

case 'ndf_rembourser':
    crmAuth(); $b=body(); $ids=$b['ids']??[];
    foreach ($ids as $id) {
        // Créer une écriture comptable
        $dep=db()->prepare("SELECT * FROM ndf_depenses WHERE id=?"); $dep->execute([$id]); $d=$dep->fetch();
        if (!$d) continue;
        $ecId=uuid();
        $montantHt = $d['montant_ht']??$d['montant_ttc'];
        $tvaTaux   = ($montantHt>0 && $d['tva']>0) ? round(($d['tva']/$montantHt)*100,1) : 0;
        $numStmt   = db()->query("SELECT MAX(CAST(SUBSTRING(numero,3) AS UNSIGNED)) FROM ecritures WHERE numero LIKE 'NF%'");
        $lastNum   = (int)($numStmt->fetchColumn()??0);
        $numero    = 'NF'.str_pad($lastNum+1,4,'0',STR_PAD_LEFT);
        $categorie = $d['categorie']??'autre';
        $catMap    = ['repas'=>'repas','deplacement'=>'deplacement','hebergement'=>'hebergement',
                      'fournitures'=>'fournitures','abonnement'=>'abonnement','representation'=>'representation','autre'=>'frais_divers'];
        $categorieEc = $catMap[$categorie]??'frais_divers';
        db()->prepare("INSERT INTO ecritures (id,type,categorie,numero,libelle,tiers,societe_id,montant_ht,tva_taux,date_piece,date_echeance,periode,statut_paiement,file_path,file_name,file_size,facturx_status,facturx_version,notes,crm_user_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)")
            ->execute([$ecId,'charge',$categorieEc,$numero,clean($d['libelle']),'',
                $d['societe_id']??null,$montantHt,$tvaTaux,
                $d['date_depense'],$d['date_depense'],
                substr($d['date_depense'],0,7),'paye',
                $d['justificatif_path']??null,$d['justificatif_nom']??null,null,
                'none',null,'Note de frais',null]);
        db()->prepare("UPDATE ndf_depenses SET statut='remboursee',ecriture_id=? WHERE id=?")->execute([$ecId,$id]);
    }
    ok(['count'=>count($ids)]);

// ── Upload justificatif ───────────────────────────────────────────
case 'ndf_upload_justificatif':
    crmAuth(); $id=$_POST['id']??'';
    if (!$id) err('ID manquant');
    $file = $_FILES['file']??null;
    if (!$file||$file['error']!==0) err('Fichier manquant ou erreur upload');
    $allowed = ['image/jpeg','image/png','image/webp','image/heic','application/pdf'];
    if (!in_array($file['type'],$allowed)) err('Type de fichier non autorisé');
    if ($file['size']>20*1024*1024) err('Fichier trop volumineux (max 20 Mo)');
    $ext = pathinfo($file['name'],PATHINFO_EXTENSION);
    $dir = rtrim($_SERVER['DOCUMENT_ROOT'],'/').parse_url(KAP_SITE_URL,PHP_URL_PATH).'/uploads/ndf/';
    if (!is_dir($dir)) mkdir($dir,0755,true);
    $fname = $id.'.'.$ext;
    $path  = $dir.$fname;
    if (!move_uploaded_file($file['tmp_name'],$path)) err('Erreur enregistrement fichier');
    $relPath = 'uploads/ndf/'.$fname;
    // Supprimer ancien justificatif si différent
    $old=db()->prepare("SELECT justificatif_path FROM ndf_depenses WHERE id=?"); $old->execute([$id]); $prev=$old->fetch();
    if ($prev && $prev['justificatif_path'] && $prev['justificatif_path']!==$relPath && file_exists($_SERVER['DOCUMENT_ROOT'].'/'.$prev['justificatif_path'])) @unlink($_SERVER['DOCUMENT_ROOT'].'/'.$prev['justificatif_path']);
    db()->prepare("UPDATE ndf_depenses SET justificatif_path=?,justificatif_nom=?,justificatif_mime=? WHERE id=?")
        ->execute([$relPath,clean($file['name']),$file['type'],$id]);
    ok(['path'=>$relPath,'nom'=>$file['name']]);

// ── Extraction IA du justificatif ─────────────────────────────────
case 'ndf_justificatif':
    crmAuth(); $id=$_GET['id']??'';
    $stmt=db()->prepare("SELECT d.justificatif_path,d.justificatif_nom,d.justificatif_mime,d.libelle,d.date_depense FROM ndf_depenses d WHERE d.id=?");
    $stmt->execute([$id]); $dep=$stmt->fetch();
    if (!$dep||!$dep['justificatif_path']) err('Justificatif introuvable',404);
    $absPath=rtrim($_SERVER['DOCUMENT_ROOT'],'/').parse_url(KAP_SITE_URL,PHP_URL_PATH).'/'.$dep['justificatif_path'];
    if (!file_exists($absPath)) err('Fichier introuvable',404);
    $mime = $dep['justificatif_mime']??'application/octet-stream';
    $titre = htmlspecialchars($dep['libelle']??'Justificatif').' — '.($dep['date_depense']??'');
    ob_clean();
    // Pour les images, envelopper dans une page HTML pour avoir un vrai titre d'onglet
    if (strpos($mime,'image/')===0) {
        $b64 = base64_encode(file_get_contents($absPath));
        header('Content-Type: text/html; charset=utf-8');
        echo "<!DOCTYPE html><html><head><meta charset='utf-8'><title>{$titre}</title>
        <style>body{margin:0;background:#1a1a1a;display:flex;justify-content:center;align-items:flex-start;min-height:100vh;}
        img{max-width:100%;height:auto;display:block;}</style></head>
        <body><img src='data:{$mime};base64,{$b64}' alt='{$titre}'></body></html>";
        exit;
    }
    // Pour les PDF : servir directement avec Content-Disposition
    header('Content-Type: '.$mime);
    header('Content-Disposition: inline; filename="'.addslashes($dep['justificatif_nom']??'justificatif').'"');
    header('Content-Length: '.filesize($absPath));
    header('Cache-Control: private, max-age=3600');
    readfile($absPath);
    exit;

case 'ndf_extract_receipt':
    crmAuth(); $b=body();
    $imageBase64 = $b['imageBase64']??'';
    $mimeType    = $b['mimeType']??'image/jpeg';
    if (!$imageBase64) err('Image manquante');

    $apiKey = defined('KAP_ANTHROPIC_KEY') ? KAP_ANTHROPIC_KEY : '';
    if (!$apiKey) err('Clé API Anthropic non configurée');

    $prompt = "Analyse ce reçu ou justificatif de dépense professionnelle et extrais les informations disponibles.
Réponds UNIQUEMENT en JSON sans markdown ni explication.

Champs à extraire :
- date : date de la dépense au format YYYY-MM-DD (string ou vide)
- fournisseur : nom du commerçant / fournisseur (string)
- libelle : description courte de la dépense (string)
- montant_ttc : montant total TTC en euros (number ou null)
- montant_ht : montant HT si visible (number ou null)
- tva : montant TVA si visible (number ou null)
- categorie : parmi ces valeurs exactes : repas, deplacement, hebergement, fournitures, abonnement, representation, autre (string)
- tva_recuperable : true si TVA récupérable selon la nature (repas=false, transport=true, fournitures=true) (boolean)

Réponds UNIQUEMENT avec le JSON.";

    $payload = json_encode([
        'model'      => 'claude-sonnet-4-6',
        'max_tokens' => 500,
        'messages'   => [[
            'role'    => 'user',
            'content' => [
                ['type'=>'image','source'=>['type'=>'base64','media_type'=>$mimeType,'data'=>$imageBase64]],
                ['type'=>'text','text'=>$prompt],
            ]
        ]],
    ]);

    $ch = curl_init('https://api.anthropic.com/v1/messages');
    curl_setopt_array($ch,[CURLOPT_RETURNTRANSFER=>true,CURLOPT_POST=>true,CURLOPT_POSTFIELDS=>$payload,
        CURLOPT_HTTPHEADER=>['Content-Type: application/json','x-api-key: '.$apiKey,'anthropic-version: 2023-06-01'],CURLOPT_TIMEOUT=>30]);
    $resp=curl_exec($ch); $code=curl_getinfo($ch,CURLINFO_HTTP_CODE); curl_close($ch);
    if ($code!==200) { $d=json_decode($resp,true); err('Erreur API : '.($d['error']['message']??'HTTP '.$code)); }
    $data=json_decode($resp,true);
    $raw=trim($data['content'][0]['text']??'');
    $raw=preg_replace('/^```json\s*/','', $raw); $raw=preg_replace('/^```\s*/','', $raw); $raw=preg_replace('/\s*```$/','', $raw);
    $extracted=json_decode($raw,true);
    if (!$extracted) err('Réponse IA invalide');
    ok(['data'=>$extracted]);


// ════════════════════════════════════════════════════════════════
//  LISTES DE DIFFUSION
// ════════════════════════════════════════════════════════════════
case 'diffusion_listes_list':
    crmAuth();
    $rows = db()->query("SELECT l.*, COUNT(m.id) AS nb_membres
        FROM diffusion_listes l
        LEFT JOIN diffusion_membres m ON m.liste_id=l.id
        GROUP BY l.id ORDER BY l.nom")->fetchAll();
    ok(['listes' => array_map(fn($r)=>[
        'id'=>$r['id'],'nom'=>$r['nom'],'description'=>$r['description']??'',
        'nbMembres'=>(int)$r['nb_membres'],'dateCreation'=>$r['date_creation'],
    ], $rows)]);

case 'diffusion_liste_get':
    crmAuth(); $id=$_GET['id']??'';
    $l=db()->prepare("SELECT * FROM diffusion_listes WHERE id=?"); $l->execute([$id]); $liste=$l->fetch();
    if (!$liste) err('Liste introuvable',404);
    $m=db()->prepare("SELECT * FROM diffusion_membres WHERE liste_id=? ORDER BY email"); $m->execute([$id]);
    ok(['liste'=>['id'=>$liste['id'],'nom'=>$liste['nom'],'description'=>$liste['description']??''],
        'membres'=>$m->fetchAll()]);

case 'diffusion_liste_save':
    $u=crmAuth(); $b=body(); $id=$b['id']??'';
    if (!trim($b['nom']??'')) err('Nom obligatoire');
    if (!$id) {
        $id=uuid();
        db()->prepare("INSERT INTO diffusion_listes (id,nom,description,crm_user_id) VALUES (?,?,?,?)")
            ->execute([$id,clean($b['nom']),clean($b['description']??''),$u['id']]);
    } else {
        db()->prepare("UPDATE diffusion_listes SET nom=?,description=? WHERE id=?")
            ->execute([clean($b['nom']),clean($b['description']??''),$id]);
    }
    ok(['id'=>$id]);

case 'diffusion_liste_delete':
    crmAuth(); $b=body();
    db()->prepare("DELETE FROM diffusion_listes WHERE id=?")->execute([$b['id']??'']);
    ok();

case 'diffusion_membres_set':
    crmAuth(); $b=body(); $id=$b['id']??'';
    // Remplace tous les membres de la liste
    db()->prepare("DELETE FROM diffusion_membres WHERE liste_id=?")->execute([$id]);
    $ins=db()->prepare("INSERT IGNORE INTO diffusion_membres (id,liste_id,email,nom) VALUES (?,?,?,?)");
    foreach (($b['membres']??[]) as $m) {
        if (!filter_var($m['email']??'',FILTER_VALIDATE_EMAIL)) continue;
        $ins->execute([uuid(),$id,strtolower($m['email']),clean($m['nom']??'')]);
    }
    $n=db()->prepare("SELECT COUNT(*) FROM diffusion_membres WHERE liste_id=?"); $n->execute([$id]);
    ok(['count'=>(int)$n->fetchColumn()]);

case 'diffusion_membres_add':
    // Ajouter des membres sans supprimer les existants
    crmAuth(); $b=body(); $id=$b['id']??'';
    $ins=db()->prepare("INSERT IGNORE INTO diffusion_membres (id,liste_id,email,nom) VALUES (?,?,?,?)");
    $added=0;
    foreach (($b['membres']??[]) as $m) {
        if (!filter_var($m['email']??'',FILTER_VALIDATE_EMAIL)) continue;
        $ins->execute([uuid(),$id,strtolower($m['email']),clean($m['nom']??'')]);
        $added++;
    }
    ok(['added'=>$added]);


// ════════════════════════════════════════════════════════════════
//  GESTION DE PROJETS
// ════════════════════════════════════════════════════════════════

case 'projet_types_get':
    crmAuth();
    $stmt=db()->prepare("SELECT valeur FROM app_settings WHERE cle='projet_types'");
    $stmt->execute(); $row=$stmt->fetch();
    $default=['migration','cybersecurite','erp','infrastructure','transformation','autre'];
    ok(['types'=>$row?json_decode($row['valeur'],true):$default]);

case 'projet_types_save':
    superAdmin(); $b=body();
    $val=json_encode($b['types']??[]);
    db()->prepare("INSERT INTO app_settings (cle,valeur,label,description) VALUES ('projet_types',?,'Types de projets','') ON DUPLICATE KEY UPDATE valeur=?")
        ->execute([$val,$val]);
    ok();

case 'projets_list':
    crmAuth();
    $rows = db()->query("
        SELECT p.*, s.nom AS societe_nom,
          (SELECT COUNT(*) FROM projet_phases ph WHERE ph.projet_id=p.id) AS nb_phases,
          (SELECT COUNT(*) FROM projet_taches t WHERE t.projet_id=p.id AND t.parent_id IS NULL) AS nb_taches,
          (SELECT COUNT(*) FROM projet_taches t WHERE t.projet_id=p.id AND t.statut='terminee') AS nb_terminees,
          (SELECT COUNT(*) FROM projet_taches t WHERE t.projet_id=p.id) AS nb_taches_total,
          (SELECT COALESCE(ROUND(AVG(
            CASE ph2.statut WHEN 'terminee' THEN 100 WHEN 'annulee' THEN 100
            ELSE (SELECT COALESCE(ROUND(AVG(CASE t2.statut WHEN 'terminee' THEN 100 ELSE t2.avancement END)),0)
                  FROM projet_taches t2 WHERE t2.phase_id=ph2.id AND t2.parent_id IS NULL) END
          )),0) FROM projet_phases ph2 WHERE ph2.projet_id=p.id) AS avg_phases_av
        FROM projets p
        LEFT JOIN societes s ON s.id=p.societe_id
        ORDER BY p.date_creation DESC")->fetchAll();
    ok(['projets' => array_map(fn($r)=>[
        'id'=>$r['id'],'nom'=>$r['nom'],'description'=>$r['description']??'',
        'type'=>$r['type'],'statut'=>$r['statut'],'rag'=>$r['rag'],
        'societeId'=>$r['societe_id']??'','societeNom'=>$r['societe_nom']??'',
        'dateDebut'=>$r['date_debut']??'','dateFinPrev'=>$r['date_fin_prev']??'',
        'dateFinReelle'=>$r['date_fin_reelle']??'',
        'budgetEstime'=>$r['budget_estime']??(float)0,'budgetConsomme'=>$r['budget_consomme']??(float)0,
        'chefProjet'=>$r['chef_projet']??'',
        'nbPhases'=>(int)$r['nb_phases'],'nbTaches'=>(int)$r['nb_taches'],
        'nbTerminees'=>(int)$r['nb_terminees'],'nbTachesTotal'=>(int)$r['nb_taches_total'],
        'avancement'=>(int)$r['avg_phases_av'],
        'dateCreation'=>$r['date_creation'],
    ], $rows)]);

case 'projet_get':
    crmAuth(); $id=$_GET['id']??'';
    $stmt=db()->prepare("SELECT p.*,s.nom AS societe_nom FROM projets p LEFT JOIN societes s ON s.id=p.societe_id WHERE p.id=?");
    $stmt->execute([$id]); $p=$stmt->fetch();
    if (!$p) err('Projet introuvable',404);
    // Phases avec tâches et sous-tâches
    $phases=db()->prepare("SELECT *,depends_on_phase_id FROM projet_phases WHERE projet_id=? ORDER BY COALESCE(date_debut,'9999-12-31'),ordre,date_creation");
    $phases->execute([$id]); $phaseList=$phases->fetchAll();
    // Toutes les tâches du projet
    $taches=db()->prepare("SELECT *,depends_on_phase_id,depends_on_tache_id FROM projet_taches WHERE projet_id=? ORDER BY ordre,date_creation");
    $taches->execute([$id]); $tacheList=$taches->fetchAll();
    // Commentaires
    $comms=db()->prepare("SELECT * FROM projet_commentaires WHERE projet_id=? ORDER BY date_creation DESC LIMIT 50");
    $comms->execute([$id]); $commList=$comms->fetchAll();
    ok([
        'projet'=>[
            'id'=>$p['id'],'nom'=>$p['nom'],'description'=>$p['description']??'',
            'type'=>$p['type'],'statut'=>$p['statut'],'rag'=>$p['rag'],
            'societeId'=>$p['societe_id']??'','societeNom'=>$p['societe_nom']??'',
            'dateDebut'=>$p['date_debut']??'','dateFinPrev'=>$p['date_fin_prev']??'',
            'dateFinReelle'=>$p['date_fin_reelle']??'',
            'budgetEstime'=>(float)($p['budget_estime']??0),'budgetConsomme'=>(float)($p['budget_consomme']??0),
            'chefProjet'=>$p['chef_projet']??'','objectifs'=>$p['objectifs']??'','risques'=>$p['risques']??'',
        ],
        'phases'=>array_map(fn($ph)=>[
            'id'=>$ph['id'],'nom'=>$ph['nom'],'description'=>$ph['description']??'',
            'ordre'=>(int)$ph['ordre'],'statut'=>$ph['statut'],
            'dateDebut'=>$ph['date_debut']??'','dateFin'=>$ph['date_fin']??'',
            'dependsOnPhaseId'=>$ph['depends_on_phase_id']??'',
        ], $phaseList),
        'taches'=>array_map(fn($t)=>[
            'id'=>$t['id'],'phaseId'=>$t['phase_id'],'parentId'=>$t['parent_id']??'',
            'nom'=>$t['nom'],'description'=>$t['description']??'',
            'responsable'=>$t['responsable']??'','statut'=>$t['statut'],'priorite'=>$t['priorite'],
            'dateDebut'=>$t['date_debut']??'','dateFin'=>$t['date_fin']??'',
            'dateFinReelle'=>$t['date_fin_reelle']??'',
            'avancement'=>(int)$t['avancement'],'ordre'=>(int)$t['ordre'],'notes'=>$t['notes']??'',
            'dependsOnTacheId'=>$t['depends_on_tache_id']??'',
            'dependsOnPhaseIdTache'=>$t['depends_on_phase_id']??'',
            'dateCreation'=>$t['date_creation'],
        ], $tacheList),
        'commentaires'=>array_map(fn($c)=>[
            'id'=>$c['id'],'tacheId'=>$c['tache_id']??'','auteur'=>$c['auteur']??'',
            'contenu'=>$c['contenu'],'type'=>$c['type'],'dateCreation'=>$c['date_creation'],
        ], $commList),
    ]);

case 'projet_save':
    $u=crmAuth(); $b=body(); $id=$b['id']??'';
    if (!trim($b['nom']??'')) err('Nom obligatoire');
    if (!$id) {
        $id=uuid();
        db()->prepare("INSERT INTO projets (id,nom,description,societe_id,type,statut,rag,date_debut,date_fin_prev,budget_estime,chef_projet,objectifs,risques,crm_user_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)")
            ->execute([$id,clean($b['nom']),clean($b['description']??''),$b['societeId']??null,
                $b['type']??'autre',$b['statut']??'etude',$b['rag']??'vert',
                $b['dateDebut']??null,$b['dateFinPrev']??null,
                $b['budgetEstime']??null,clean($b['chefProjet']??''),
                clean($b['objectifs']??''),clean($b['risques']??''),$u['id']]);
    } else {
        db()->prepare("UPDATE projets SET nom=?,description=?,societe_id=?,type=?,statut=?,rag=?,date_debut=?,date_fin_prev=?,date_fin_reelle=?,budget_estime=?,budget_consomme=?,chef_projet=?,objectifs=?,risques=? WHERE id=?")
            ->execute([clean($b['nom']),clean($b['description']??''),$b['societeId']??null,
                $b['type']??'autre',$b['statut']??'etude',$b['rag']??'vert',
                $b['dateDebut']??null,$b['dateFinPrev']??null,$b['dateFinReelle']??null,
                $b['budgetEstime']??null,$b['budgetConsomme']??null,
                clean($b['chefProjet']??''),clean($b['objectifs']??''),clean($b['risques']??''),$id]);
    }
    ok(['id'=>$id]);

case 'projet_delete':
    crmAuth(); $b=body();
    db()->prepare("DELETE FROM projets WHERE id=?")->execute([$b['id']??'']);
    ok();

// ── Phases ────────────────────────────────────────────────────────
case 'phase_save':
    crmAuth(); $b=body(); $id=$b['id']??'';
    if (!$id) {
        $id=uuid();
        $maxOrdre=db()->prepare("SELECT COALESCE(MAX(ordre),0)+1 FROM projet_phases WHERE projet_id=?");
        $maxOrdre->execute([$b['projetId']??'']); $ordre=(int)$maxOrdre->fetchColumn();
        db()->prepare("INSERT INTO projet_phases (id,projet_id,nom,description,ordre,date_debut,date_fin,statut,depends_on_phase_id) VALUES (?,?,?,?,?,?,?,?,?)")
            ->execute([$id,$b['projetId']??'',clean($b['nom']??''),clean($b['description']??''),
                $ordre,$b['dateDebut']??null,$b['dateFin']??null,$b['statut']??'a_faire',$b['dependsOnPhaseId']??null]);
    } else {
        db()->prepare("UPDATE projet_phases SET nom=?,description=?,ordre=?,date_debut=?,date_fin=?,statut=?,depends_on_phase_id=? WHERE id=?")
            ->execute([clean($b['nom']??''),clean($b['description']??''),(int)($b['ordre']??0),
                $b['dateDebut']??null,$b['dateFin']??null,$b['statut']??'a_faire',$b['dependsOnPhaseId']??null,$id]);
    }
    ok(['id'=>$id]);

case 'phase_delete':
    crmAuth(); $b=body();
    db()->prepare("DELETE FROM projet_phases WHERE id=?")->execute([$b['id']??'']);
    ok();

// ── Tâches ────────────────────────────────────────────────────────
case 'tache_save':
    crmAuth(); $b=body(); $id=$b['id']??'';
    if (!$id) {
        $id=uuid();
        $maxOrdre=db()->prepare("SELECT COALESCE(MAX(ordre),0)+1 FROM projet_taches WHERE phase_id=? AND parent_id IS NULL");
        $maxOrdre->execute([$b['phaseId']??'']); $ordre=(int)$maxOrdre->fetchColumn();
        $av_ins=(int)($b['avancement']??0);
        $st_ins=$b['statut']??'a_faire';
        if($av_ins>=100) $st_ins='terminee'; elseif($av_ins>0) $st_ins='en_cours'; elseif($av_ins===0&&$st_ins!=='bloquee'&&$st_ins!=='annulee') $st_ins='a_faire';
        // Valider dépendance phase pour INSERT
        $depPhIns = $b['dependsOnPhaseId']??null;
        if ($depPhIns && ($b['dateDebut']??null)) {
            $depPhI = db()->prepare("SELECT date_fin, nom FROM projet_phases WHERE id=?");
            $depPhI->execute([$depPhIns]); $depPhRI = $depPhI->fetch();
            if ($depPhRI && $depPhRI['date_fin'] && $b['dateDebut'] < $depPhRI['date_fin']) {
                err('La date de début ('.date('d/m/Y',strtotime($b['dateDebut'])).') ne peut pas être avant la date de fin de la phase "'.$depPhRI['nom'].'" ('.date('d/m/Y',strtotime($depPhRI['date_fin'])).')');
            }
        }
        // Valider dépendance date pour INSERT aussi
        $depIdIns = $b['dependsOnTacheId']??null;
        if ($depIdIns && ($b['dateDebut']??null)) {
            $depTi = db()->prepare("SELECT COALESCE(date_fin_reelle, date_fin) AS fin, nom FROM projet_taches WHERE id=?");
            $depTi->execute([$depIdIns]); $depRowI = $depTi->fetch();
            if ($depRowI && $depRowI['fin'] && $b['dateDebut'] < $depRowI['fin']) {
                err('La date de début ('.date('d/m/Y',strtotime($b['dateDebut'])).') ne peut pas être avant la date de fin de "'.$depRowI['nom'].'" ('.date('d/m/Y',strtotime($depRowI['fin'])).')');
            }
        }
        $dateFin_ins = $b['dateFin']??null ?: ($b['dateDebut']??null);
        db()->prepare("INSERT INTO projet_taches (id,phase_id,projet_id,parent_id,nom,description,responsable,statut,priorite,date_debut,date_fin,avancement,ordre,notes,depends_on_tache_id,depends_on_phase_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)")
            ->execute([$id,$b['phaseId']??'',$b['projetId']??'',$b['parentId']??null,
                clean($b['nom']??''),clean($b['description']??''),clean($b['responsable']??''),
                $st_ins,$b['priorite']??'normale',
                $b['dateDebut']??null,$dateFin_ins,
                $av_ins,$ordre,clean($b['notes']??''),$b['dependsOnTacheId']??null,$b['dependsOnPhaseId']??null]);
    } else {
        $av_upd=(int)($b['avancement']??0);
        $st_upd=$b['statut']??'a_faire';
        // Dériver le statut depuis l'avancement (priorité sur le statut fourni sauf bloquée/annulée)
        if($st_upd!=='bloquee'&&$st_upd!=='annulee'){
            if($av_upd>=100) $st_upd='terminee';
            elseif($av_upd>0) $st_upd='en_cours';
            else $st_upd='a_faire';
        }
        $fin_upd=$st_upd==='terminee'?date('Y-m-d'):($b['dateFinReelle']??null);
        $dateFin_upd = $b['dateFin']??null ?: ($b['dateDebut']??null); // date_fin = date_debut si vide
        db()->prepare("UPDATE projet_taches SET nom=?,description=?,responsable=?,statut=?,priorite=?,date_debut=?,date_fin=?,date_fin_reelle=?,avancement=?,notes=?,depends_on_tache_id=?,depends_on_phase_id=? WHERE id=?")
            ->execute([clean($b['nom']??''),clean($b['description']??''),clean($b['responsable']??''),
                $st_upd,$b['priorite']??'normale',
                $b['dateDebut']??null,$dateFin_upd,$fin_upd,
                $av_upd,clean($b['notes']??''),$b['dependsOnTacheId']??null,$b['dependsOnPhaseId']??null,$id]);
    }
    // Valider dépendance sur phase
    $depPhaseId = $b['dependsOnPhaseId']??null;
    if ($depPhaseId && ($b['dateDebut']??null)) {
        $depPh = db()->prepare("SELECT date_fin, nom FROM projet_phases WHERE id=?");
        $depPh->execute([$depPhaseId]); $depPhRow = $depPh->fetch();
        if ($depPhRow && $depPhRow['date_fin'] && $b['dateDebut'] < $depPhRow['date_fin']) {
            err('La date de début ('.date('d/m/Y',strtotime($b['dateDebut'])).') ne peut pas être avant la date de fin de la phase "'.$depPhRow['nom'].'" ('.date('d/m/Y',strtotime($depPhRow['date_fin'])).')');
        }
    }
    // Valider que date_debut >= date_fin de la tâche dont cette tâche dépend
    $depId = $b['dependsOnTacheId']??null;
    if ($depId && ($b['dateDebut']??null)) {
        $depT = db()->prepare("SELECT COALESCE(date_fin_reelle, date_fin) AS fin, nom FROM projet_taches WHERE id=?");
        $depT->execute([$depId]); $depRow = $depT->fetch();
        if ($depRow && $depRow['fin'] && $b['dateDebut'] < $depRow['fin']) {
            err('La date de début ('.date('d/m/Y',strtotime($b['dateDebut'])).') ne peut pas être avant la date de fin de "'.$depRow['nom'].'" ('.date('d/m/Y',strtotime($depRow['fin'])).')');
        }
    }
    // Recalculer l'avancement
    recalcAvancement($b['projetId']??'');
    ok(['id'=>$id]);

case 'tache_delete':
    crmAuth(); $b=body(); $tid=$b['id']??'';
    // Récupérer le projet_id avant suppression
    $tp=db()->prepare("SELECT projet_id FROM projet_taches WHERE id=?"); $tp->execute([$tid]); $tr=$tp->fetch();
    db()->prepare("DELETE FROM projet_taches WHERE id=? OR parent_id=?")->execute([$tid,$tid]);
    if ($tr) recalcAvancement($tr['projet_id']);
    ok();

case 'tache_statut':
    crmAuth(); $b=body(); $tid=$b['id']??'';
    $av = isset($b['avancement']) ? (int)$b['avancement'] : null;
    $statut = $b['statut']??null;
    // Déduire le statut depuis l'avancement si non fourni explicitement
    if ($av !== null && !$statut) {
        if ($av >= 100)  $statut = 'terminee';
        elseif ($av > 0) $statut = 'en_cours';
        else             $statut = 'a_faire';
    }
    // Forcer l'avancement depuis le statut si fourni
    if ($statut === 'terminee' && $av === null) $av = 100;
    if ($statut === 'a_faire'  && $av === null) $av = 0;
    if ($av === null) $av = 0;
    if (!$statut) $statut = 'a_faire';
    $fin = $statut === 'terminee' ? date('Y-m-d') : null;
    // Si pas de date_fin, mettre date_debut comme date_fin
    db()->prepare("UPDATE projet_taches SET statut=?,avancement=?,date_fin_reelle=COALESCE(?,date_fin_reelle),date_fin=COALESCE(date_fin,date_debut) WHERE id=?")
        ->execute([$statut,$av,$fin,$tid]);
    $tp=db()->prepare("SELECT projet_id FROM projet_taches WHERE id=?"); $tp->execute([$tid]); $tr=$tp->fetch();
    if ($tr) recalcAvancement($tr['projet_id']);
    ok();

// ── Commentaires ──────────────────────────────────────────────────
case 'commentaire_save':
    crmAuth(); $b=body();
    if (!trim($b['contenu']??'')) err('Contenu obligatoire');
    $id=uuid();
    db()->prepare("INSERT INTO projet_commentaires (id,projet_id,tache_id,auteur,contenu,type) VALUES (?,?,?,?,?,?)")
        ->execute([$id,$b['projetId']??'',$b['tacheId']??null,clean($b['auteur']??''),
            clean($b['contenu']),$b['type']??'commentaire']);
    ok(['id'=>$id]);

case 'commentaire_delete':
    crmAuth(); $b=body();
    db()->prepare("DELETE FROM projet_commentaires WHERE id=?")->execute([$b['id']??'']);
    ok();


// ════════════════════════════════════════════════════════════════
//  ACCÈS MODULES UTILISATEURS HUB
// ════════════════════════════════════════════════════════════════

case 'user_modules_get':
    crmAuth(); $userId=$_GET['userId']??'';
    $stmt=db()->prepare("SELECT modules FROM crm_users WHERE id=?");
    $stmt->execute([$userId]); $row=$stmt->fetch();
    $modules = $row && $row['modules'] ? json_decode($row['modules'],true) : null;
    ok(['modules'=>$modules]);

case 'user_modules_save':
    superAdmin(); $b=body();
    db()->prepare("UPDATE crm_users SET modules=? WHERE id=?")
        ->execute([json_encode($b['modules']??null),$b['userId']??'']);
    ok();

case 'nav_order_save':
    superAdmin(); $b=body();
    $stmt=db()->prepare("INSERT INTO app_settings (cle,valeur,label,description) VALUES ('nav_order',?,'Ordre navigation','') ON DUPLICATE KEY UPDATE valeur=?");
    $val=json_encode($b['order']??[]);
    $stmt->execute([$val,$val]);
    ok();

case 'nav_order_get':
    crmAuth();
    $stmt=db()->prepare("SELECT valeur FROM app_settings WHERE cle='nav_order'");
    $stmt->execute(); $row=$stmt->fetch();
    ok(['order'=>$row?json_decode($row['valeur'],true):null]);


// ════════════════════════════════════════════════════════════════
//  GED — Google Drive
// ════════════════════════════════════════════════════════════════
case 'ged_oauth_start':
    crmAuth();
    try {
        $ruri=(strpos($_SERVER['HTTP_HOST']??'','kapdsi.fr')!==false?'https://kapdsi.fr':'https://td-info.fr/kapdsi').'/api.php?action=ged_oauth_callback';
        $params=http_build_query(['client_id'=>GED_CLIENT_ID,'redirect_uri'=>$ruri,'response_type'=>'code','scope'=>GED_SCOPE,'access_type'=>'offline','prompt'=>'consent','state'=>'kapdsi_ged_'.bin2hex(random_bytes(8))]);
        ok(['url'=>'https://accounts.google.com/o/oauth2/v2/auth?'.$params]);
    } catch(Exception $e){err($e->getMessage());}

case 'ged_oauth_revoke':
    superAdmin();
    db()->prepare("DELETE FROM app_settings WHERE cle IN ('ged_refresh_token','ged_access_token')")->execute();
    ok();

case 'ged_settings_save':
    superAdmin(); $b=body();
    $key='ged_root_folder_id'; $val=$b[$key]??'';
    if ($val) db()->prepare("INSERT INTO app_settings (cle,valeur,label,description) VALUES (?,?,'GED','') ON DUPLICATE KEY UPDATE valeur=?")->execute([$key,$val,$val]);
    ok();

case 'ged_settings_get':
    crmAuth();
    $r1=db()->prepare("SELECT valeur FROM app_settings WHERE cle='ged_root_folder_id'"); $r1->execute();
    $r2=db()->prepare("SELECT valeur FROM app_settings WHERE cle='ged_refresh_token'"); $r2->execute();
    $r3=db()->prepare("SELECT valeur FROM app_settings WHERE cle='ged_inbox_folder_id'"); $r3->execute();
    $rt=$r2->fetchColumn();
    ok(['rootFolderId'=>$r1->fetchColumn()??'','hasServiceAccount'=>!empty($rt),'authorized'=>!empty($rt),'inboxFolderId'=>$r3->fetchColumn()??'']);

case 'ged_list':
    crmAuth();
    try {
        $folderId=$_GET['folderId']??'';
        if (!$folderId){$stmt=db()->prepare("SELECT valeur FROM app_settings WHERE cle='ged_root_folder_id'");$stmt->execute();$row=$stmt->fetch();$folderId=$row['valeur']??'root';}
        $token=gedToken();
        $fields=urlencode('files(id,name,mimeType,size,modifiedTime,webViewLink,iconLink,parents),nextPageToken');
        $isSharedDrive=(strlen($folderId)>2&&substr($folderId,0,2)==='0A');
        if ($isSharedDrive){
            // Drive Partagé : lister les fichiers dont le parent direct est ce dossier
            $q=urlencode("'$folderId' in parents and trashed=false");
            $url="https://www.googleapis.com/drive/v3/files?driveId=$folderId&corpora=drive&includeItemsFromAllDrives=true&supportsAllDrives=true&fields=$fields&orderBy=folder,name&pageSize=100&q=$q";
            $data=gedApi($url,$token);
            $folderInfo=['id'=>$folderId,'name'=>'Accueil'];
        } else {
            $q=urlencode("'$folderId' in parents and trashed=false");
            $data=gedApi("https://www.googleapis.com/drive/v3/files?q=$q&fields=$fields&orderBy=folder,name&pageSize=100&supportsAllDrives=true&includeItemsFromAllDrives=true",$token);
            $folderInfo=[];
            if ($folderId!=='root'){$fi=gedApi("https://www.googleapis.com/drive/v3/files/$folderId?fields=id,name,parents&supportsAllDrives=true",$token);$folderInfo=$fi;}
        }
        // Dédoublonner : ne montrer que le fichier courant de chaque fiche GED
        // (les anciennes versions sont des fichiers Drive séparés)
        $allFiles = $data['files'] ?? [];
        if (!empty($allFiles)) {
            // Récupérer les drive_file_id courants pour ce dossier
            $fileIds = array_column(array_filter($allFiles, fn($f)=>$f['mimeType']!=='application/vnd.google-apps.folder'), 'id');
            if ($fileIds) {
                $ph = implode(',', array_fill(0, count($fileIds), '?'));
                // IDs qui sont des versions archivées (dans ged_versions mais pas drive_file_id courant de ged_fichiers)
                $archivedStmt = db()->prepare("SELECT gv.drive_file_id FROM ged_versions gv JOIN ged_fichiers gf ON gf.id=gv.fichier_id WHERE gv.drive_file_id IN ($ph) AND gf.drive_file_id != gv.drive_file_id");
                $archivedStmt->execute($fileIds);
                $archivedIds = $archivedStmt->fetchAll(PDO::FETCH_COLUMN);
                // Exclure les versions archivées de la liste
                $allFiles = array_values(array_filter($allFiles, fn($f)=>!in_array($f['id'], $archivedIds)));
            }
        }

        // Filtrer selon les droits si pas super_admin
        $u = crmCheck();
        $files = $allFiles;
        if (($u['role']??'') !== 'super_admin') {
            $userId = $u['id']??'';
            // Construire la liste des parents (dossier courant + racine)
            $stmt3 = db()->prepare("SELECT valeur FROM app_settings WHERE cle='ged_root_folder_id'");
            $stmt3->execute(); $rootId = $stmt3->fetchColumn() ?? '';
            $parentChain = array_filter(array_unique([$folderId, $rootId]));
            $isRootLevel = ($folderId === $rootId || empty($rootId));
            $files = array_filter($files, function($f) use ($userId, $u, $parentChain, $isRootLevel) {
                // Mode strict à la racine : pas de règle = pas d'accès
                $acces = gedCheckAccess($f['id'], $userId, $u['role']??'', array_values($parentChain), $isRootLevel);
                return $acces !== 'aucun';
            });
            $files = array_values($files);
        }
        ok(['files'=>$files,'folder'=>$folderInfo]);
    } catch(Exception $e){err('ged_list: '.$e->getMessage());}

case 'ged_folder_create':
    crmAuth(); $b=body(); $token=gedToken();
    $data=gedApi('https://www.googleapis.com/drive/v3/files?supportsAllDrives=true',$token,'POST',['name'=>clean($b['name']??'Nouveau dossier'),'mimeType'=>'application/vnd.google-apps.folder','parents'=>[$b['parentId']??'root']]);
    ok(['id'=>$data['id'],'name'=>$data['name']]);

case 'ged_rename':
    crmAuth(); $b=body(); $token=gedToken();
    gedApi('https://www.googleapis.com/drive/v3/files/'.($b['fileId']??'').'?supportsAllDrives=true',$token,'PATCH',['name'=>clean($b['name']??'')]);
    ok();

case 'ged_delete':
    crmAuth(); $b=body(); $token=gedToken();
    gedApi('https://www.googleapis.com/drive/v3/files/'.($b['fileId']??'').'?supportsAllDrives=true',$token,'DELETE');
    ok();

case 'ged_upload':
    crmAuth(); $parentId=$_POST['parentId']??'root';
    if (empty($_FILES['file'])) err('Aucun fichier');
    $file=$_FILES['file']; $token=gedToken();
    $boundary='GED_'.uniqid();
    $meta=json_encode(['name'=>$file['name'],'parents'=>[$parentId],'driveId'=>'']);
    $content=file_get_contents($file['tmp_name']);
    $mime=$file['type']?:'application/octet-stream';
    $body="--$boundary
Content-Type: application/json; charset=UTF-8

$meta
--$boundary
Content-Type: $mime

$content
--$boundary--";
    $ch=curl_init('https://www.googleapis.com/upload/drive/v3/files?uploadType=multipart&fields=id,name,mimeType,size,webViewLink&supportsAllDrives=true');
    curl_setopt_array($ch,[CURLOPT_RETURNTRANSFER=>true,CURLOPT_POST=>true,CURLOPT_POSTFIELDS=>$body,CURLOPT_HTTPHEADER=>['Authorization: Bearer '.$token,"Content-Type: multipart/related; boundary=$boundary",'Content-Length: '.strlen($body)]]);
    $res=curl_exec($ch); curl_close($ch);
    $data=json_decode($res,true)??[];
    if (empty($data['id'])) err('Échec upload: '.$res);
    ok(['file'=>$data]);

case 'ged_download_link':
    crmAuth(); $fileId=$_GET['fileId']??''; $token=gedToken();
    $data=gedApi("https://www.googleapis.com/drive/v3/files/$fileId?fields=id,name,mimeType,webViewLink,webContentLink&supportsAllDrives=true",$token);
    ok(['file'=>$data,'token'=>$token]);

case 'ged_search':
    crmAuth(); $q=$_GET['q']??'';
    $stmt=db()->prepare("SELECT valeur FROM app_settings WHERE cle='ged_root_folder_id'"); $stmt->execute(); $row=$stmt->fetch();
    $token=gedToken();
    $query="name contains '".addslashes($q)."' and trashed=false";
    $qEnc=urlencode($query);
    $fields=urlencode('files(id,name,mimeType,size,modifiedTime,webViewLink,parents)');
    $data=gedApi("https://www.googleapis.com/drive/v3/files?q=$qEnc&fields=$fields&pageSize=30&supportsAllDrives=true&includeItemsFromAllDrives=true",$token);
    ok(['files'=>$data['files']??[]]);

case 'ged_droits_list':
    superAdmin();
    $rows=db()->query("SELECT g.*,u.prenom,u.nom,u.login FROM ged_droits g LEFT JOIN crm_users u ON u.id=g.crm_user_id ORDER BY g.folder_path,u.nom")->fetchAll();
    ok(['droits'=>array_map(fn($r)=>['id'=>$r['id'],'driveFolderId'=>$r['drive_folder_id'],'folderPath'=>$r['folder_path']??'','userId'=>$r['crm_user_id']??'','userNom'=>trim(($r['prenom']??'').' '.($r['nom']??'')),'userLogin'=>$r['login']??'','acces'=>$r['acces']],$rows)]);

case 'ged_droit_save':
    superAdmin(); $b=body();
    if (!($b['driveFolderId']??'')) err('Dossier obligatoire');
    $id=$b['id']??'';
    if (!$id){$id=uuid();db()->prepare("INSERT INTO ged_droits (id,drive_folder_id,folder_path,crm_user_id,acces) VALUES (?,?,?,?,?)")->execute([$id,$b['driveFolderId'],clean($b['folderPath']??''),$b['userId']??null,$b['acces']??'lecture']);}
    else{db()->prepare("UPDATE ged_droits SET drive_folder_id=?,folder_path=?,crm_user_id=?,acces=? WHERE id=?")->execute([$b['driveFolderId'],clean($b['folderPath']??''),$b['userId']??null,$b['acces']??'lecture',$id]);}
    ok(['id'=>$id]);

case 'ged_droit_delete':
    superAdmin(); $b=body();
    db()->prepare("DELETE FROM ged_droits WHERE id=?")->execute([$b['id']??'']);
    ok();


// ── GED Groupes ───────────────────────────────────────────────────
case 'ged_groupes_list':
    crmAuth();
    $rows=db()->query("SELECT g.*,COUNT(m.crm_user_id) AS nb_membres FROM ged_groupes g LEFT JOIN ged_groupe_membres m ON m.groupe_id=g.id GROUP BY g.id ORDER BY g.nom")->fetchAll();
    ok(['groupes'=>array_map(fn($r)=>['id'=>$r['id'],'nom'=>$r['nom'],'description'=>$r['description']??'','nbMembres'=>(int)$r['nb_membres']],$rows)]);

case 'ged_groupe_get':
    crmAuth(); $id=$_GET['id']??'';
    $g=db()->prepare("SELECT * FROM ged_groupes WHERE id=?"); $g->execute([$id]); $groupe=$g->fetch();
    if (!$groupe) err('Groupe introuvable',404);
    $m=db()->prepare("SELECT m.crm_user_id,u.prenom,u.nom,u.login FROM ged_groupe_membres m JOIN crm_users u ON u.id=m.crm_user_id WHERE m.groupe_id=?");
    $m->execute([$id]);
    ok(['groupe'=>['id'=>$groupe['id'],'nom'=>$groupe['nom'],'description'=>$groupe['description']??''],'membres'=>$m->fetchAll()]);

case 'ged_groupe_save':
    superAdmin(); $b=body(); $id=$b['id']??'';
    if (!trim($b['nom']??'')) err('Nom obligatoire');
    if (!$id){$id=uuid();db()->prepare("INSERT INTO ged_groupes (id,nom,description) VALUES (?,?,?)")->execute([$id,clean($b['nom']),clean($b['description']??'')]);}
    else{db()->prepare("UPDATE ged_groupes SET nom=?,description=? WHERE id=?")->execute([clean($b['nom']),clean($b['description']??''),$id]);}
    // Membres
    db()->prepare("DELETE FROM ged_groupe_membres WHERE groupe_id=?")->execute([$id]);
    $ins=db()->prepare("INSERT IGNORE INTO ged_groupe_membres (groupe_id,crm_user_id) VALUES (?,?)");
    foreach (($b['membres']??[]) as $uid) $ins->execute([$id,$uid]);
    ok(['id'=>$id]);

case 'ged_groupe_delete':
    superAdmin(); $b=body();
    db()->prepare("DELETE FROM ged_groupes WHERE id=?")->execute([$b['id']??'']);
    ok();

// ── GED Droits avancés ────────────────────────────────────────────
case 'ged_item_droits_get':
    crmAuth(); $itemId=$_GET['itemId']??'';
    $rows=db()->query("SELECT d.*,u.prenom,u.nom,u.login,g.nom AS groupe_nom
        FROM ged_droits d
        LEFT JOIN crm_users u ON u.id=d.crm_user_id
        LEFT JOIN ged_groupes g ON g.id=d.groupe_id
        WHERE d.drive_item_id='".addslashes($itemId)."'
        ORDER BY d.acces DESC")->fetchAll();
    ok(['droits'=>array_map(fn($r)=>[
        'id'=>$r['id'],'itemType'=>$r['item_type'],'itemPath'=>$r['item_path']??'',
        'userId'=>$r['crm_user_id']??'','userNom'=>trim(($r['prenom']??'').' '.($r['nom']??'')),'userLogin'=>$r['login']??'',
        'groupeId'=>$r['groupe_id']??'','groupeNom'=>$r['groupe_nom']??'',
        'acces'=>$r['acces'],'herite'=>(bool)$r['herite'],
    ],$rows)]);

case 'ged_item_droit_save':
    superAdmin(); $b=body();
    $id=$b['id']??'';
    if (!($b['driveItemId']??'')) err('Item obligatoire');
    if (!($b['userId']??'') && !($b['groupeId']??'')) err('Utilisateur ou groupe obligatoire');
    if (!$id){
        $id=uuid();
        db()->prepare("INSERT INTO ged_droits (id,drive_item_id,item_type,item_path,crm_user_id,groupe_id,acces,herite) VALUES (?,?,?,?,?,?,?,?)")
            ->execute([$id,$b['driveItemId'],$b['itemType']??'folder',clean($b['itemPath']??''),$b['userId']??null,$b['groupeId']??null,$b['acces']??'lecture',(int)($b['herite']??1)]);
    } else {
        db()->prepare("UPDATE ged_droits SET acces=?,herite=? WHERE id=?")->execute([$b['acces']??'lecture',(int)($b['herite']??1),$id]);
    }
    ok(['id'=>$id]);

case 'ged_item_droit_delete':
    superAdmin(); $b=body();
    db()->prepare("DELETE FROM ged_droits WHERE id=?")->execute([$b['id']??'']);
    ok();

case 'ged_user_access':
    // Retourner le niveau d'accès de l'utilisateur connecté sur un item
    $u=crmAuth(); $itemId=$_GET['itemId']??'';
    $parentIds=explode(',',($_GET['parentIds']??''));
    $parentIds=array_filter($parentIds);
    $acces=gedCheckAccess($itemId,$u['id'],$u['role'],$parentIds);
    ok(['acces'=>$acces]);


// ════════════════════════════════════════════════════════════════
//  GED — Métadonnées et versioning
// ════════════════════════════════════════════════════════════════
case 'ged_fichier_create':
    $u=crmAuth(); $b=body();
    if (!($b['driveFileId']??'')) err('driveFileId obligatoire');
    $id=uuid();
    db()->prepare("INSERT INTO ged_fichiers (id,drive_file_id,dossier_id,nom_affichage,description,societe_id,projet_id,crm_user_id) VALUES (?,?,?,?,?,?,?,?)")
        ->execute([$id,$b['driveFileId'],$b['dossierId']??'',clean($b['nomAffichage']??$b['nomOriginal']??''),clean($b['description']??''),$b['societeId']??null,$b['projetId']??null,$u['id']]);
    db()->prepare("INSERT INTO ged_versions (id,fichier_id,drive_file_id,version,nom_original,taille,mime_type,crm_user_id) VALUES (?,?,?,1,?,?,?,?)")
        ->execute([uuid(),$id,$b['driveFileId'],clean($b['nomOriginal']??''),$b['taille']??null,clean($b['mimeType']??''),$u['id']]);
    ok(['id'=>$id]);

case 'ged_fichier_get':
    crmAuth();
    $driveFileId=$_GET['driveFileId']??'';
    $fichierById=$_GET['fichierId']??'';
    if ($driveFileId) {
        $f=db()->prepare("SELECT f.*,u.prenom,u.nom FROM ged_fichiers f LEFT JOIN crm_users u ON u.id=f.crm_user_id WHERE f.drive_file_id=?");
        $f->execute([$driveFileId]);
    } elseif ($fichierById) {
        $f=db()->prepare("SELECT f.*,u.prenom,u.nom FROM ged_fichiers f LEFT JOIN crm_users u ON u.id=f.crm_user_id WHERE f.id=?");
        $f->execute([$fichierById]);
    } else err('driveFileId ou fichierId obligatoire');
    $fichier=$f->fetch();
    if (!$fichier) ok(['fichier'=>null,'versions'=>[]]);
    $v=db()->prepare("SELECT gv.*,u.prenom,u.nom FROM ged_versions gv LEFT JOIN crm_users u ON u.id=gv.crm_user_id WHERE gv.fichier_id=? ORDER BY gv.version DESC");
    $v->execute([$fichier['id']]); $versions=$v->fetchAll();
    ok(['fichier'=>['id'=>$fichier['id'],'driveFileId'=>$fichier['drive_file_id'],'dossierId'=>$fichier['dossier_id'],
        'nomAffichage'=>$fichier['nom_affichage'],'description'=>$fichier['description']??'',
        'nbVersionsMax'=>$fichier['nb_versions_max']??null,'dateCreation'=>$fichier['date_creation'],
        'createur'=>trim(($fichier['prenom']??'').' '.($fichier['nom']??'')),
    ],'versions'=>array_map(fn($v)=>['id'=>$v['id'],'driveFileId'=>$v['drive_file_id'],'version'=>(int)$v['version'],
        'nomOriginal'=>$v['nom_original']??'','taille'=>$v['taille']??0,'mimeType'=>$v['mime_type']??'',
        'dateUpload'=>$v['date_upload'],'auteur'=>trim(($v['prenom']??'').' '.($v['nom']??'')),
    ],$versions)]);

case 'ged_fichier_update':
    crmAuth(); $b=body();
    db()->prepare("UPDATE ged_fichiers SET nom_affichage=?,description=?,nb_versions_max=? WHERE id=?")
        ->execute([clean($b['nomAffichage']??''),clean($b['description']??''),$b['nbVersionsMax']??null,$b['id']??'']);
    ok();

case 'ged_fichier_new_version':
    $u=crmAuth();
    $fichierId=$_POST['fichierId']??'';
    if (!$fichierId||empty($_FILES['file'])) err('Paramètres manquants');
    $f=db()->prepare("SELECT * FROM ged_fichiers WHERE id=?"); $f->execute([$fichierId]); $fichier=$f->fetch();
    if (!$fichier) err('Fichier introuvable');
    $file=$_FILES['file']; $token=gedToken();
    $boundary='GED_'.uniqid();
    $meta=json_encode(['name'=>$file['name'],'parents'=>[$fichier['dossier_id']]]);
    $fileContent=file_get_contents($file['tmp_name']);
    $mime=$file['type']?:'application/octet-stream';
    $body2="--$boundary
Content-Type: application/json; charset=UTF-8

$meta
--$boundary
Content-Type: $mime

$fileContent
--$boundary--";
    $ch=curl_init('https://www.googleapis.com/upload/drive/v3/files?uploadType=multipart&fields=id,name,mimeType,size&supportsAllDrives=true');
    curl_setopt_array($ch,[CURLOPT_RETURNTRANSFER=>true,CURLOPT_POST=>true,CURLOPT_POSTFIELDS=>$body2,CURLOPT_HTTPHEADER=>['Authorization: Bearer '.$token,"Content-Type: multipart/related; boundary=$boundary",'Content-Length: '.strlen($body2)]]);
    $res=curl_exec($ch); curl_close($ch);
    $driveFile=json_decode($res,true)??[];
    if (empty($driveFile['id'])) err('Échec upload Drive: '.$res);
    $vStmt=db()->prepare("SELECT COALESCE(MAX(version),0)+1 FROM ged_versions WHERE fichier_id=?");
    $vStmt->execute([$fichierId]); $nextVersion=(int)$vStmt->fetchColumn();
    db()->prepare("INSERT INTO ged_versions (id,fichier_id,drive_file_id,version,nom_original,taille,mime_type,crm_user_id) VALUES (?,?,?,?,?,?,?,?)")
        ->execute([uuid(),$fichierId,$driveFile['id'],$nextVersion,clean($file['name']),$driveFile['size']??null,clean($mime),$u['id']]);
    db()->prepare("UPDATE ged_fichiers SET drive_file_id=? WHERE id=?")->execute([$driveFile['id'],$fichierId]);
    // Purge anciennes versions
    $maxV=$fichier['nb_versions_max'];
    if (!$maxV){$sv=db()->prepare("SELECT valeur FROM app_settings WHERE cle='ged_nb_versions_max'");$sv->execute();$maxV=(int)($sv->fetchColumn()??10);}
    if ($maxV>0){
        $oldVs=db()->prepare("SELECT id,drive_file_id FROM ged_versions WHERE fichier_id=? ORDER BY version DESC LIMIT 999 OFFSET ?");
        $oldVs->execute([$fichierId,$maxV]);
        foreach($oldVs->fetchAll() as $ov){
            try{$ch2=curl_init('https://www.googleapis.com/drive/v3/files/'.$ov['drive_file_id'].'?supportsAllDrives=true');curl_setopt_array($ch2,[CURLOPT_RETURNTRANSFER=>true,CURLOPT_CUSTOMREQUEST=>'DELETE',CURLOPT_HTTPHEADER=>['Authorization: Bearer '.$token]]);curl_exec($ch2);curl_close($ch2);}catch(Exception $e){}
            db()->prepare("DELETE FROM ged_versions WHERE id=?")->execute([$ov['id']]);
        }
    }
    ok(['driveFileId'=>$driveFile['id'],'version'=>$nextVersion]);

case 'ged_move_file':
    crmAuth(); $b=body();
    $fileId=$b['fileId']??''; $targetId=$b['targetFolderId']??'';
    if (!$fileId||!$targetId) err('Paramètres manquants');
    $token=gedToken();
    // Récupérer le dossier parent actuel
    $info=gedApi("https://www.googleapis.com/drive/v3/files/$fileId?fields=parents&supportsAllDrives=true",$token);
    $oldParents=implode(',',$info['parents']??[]);
    // Déplacer
    gedApi("https://www.googleapis.com/drive/v3/files/$fileId?addParents=$targetId&removeParents=$oldParents&supportsAllDrives=true",$token,'PATCH',[]);
    // Mettre à jour dossier_id dans ged_fichiers si fiche existe
    db()->prepare("UPDATE ged_fichiers SET dossier_id=? WHERE drive_file_id=?")->execute([$targetId,$fileId]);
    ok();

case 'ged_nb_versions_save':
    superAdmin(); $b=body(); $v=(int)($b['value']??10);
    db()->prepare("INSERT INTO app_settings (cle,valeur,label,description) VALUES ('ged_nb_versions_max',?,'GED','') ON DUPLICATE KEY UPDATE valeur=?")->execute([$v,$v]);
    ok();


// ════════════════════════════════════════════════════════════════
//  GED — Partages
// ════════════════════════════════════════════════════════════════

case 'ged_partage_create':
    try {
    $u=crmAuth(); $b=body();
    if (!($b['driveItemId']??'')) err('driveItemId obligatoire');
    $id=uuid();
    $token=bin2hex(random_bytes(32)); // 64 chars
    $pwHash=null;
    if (!empty($b['password'])) $pwHash=password_hash($b['password'],PASSWORD_DEFAULT);
    db()->prepare("INSERT INTO ged_partages (id,token,type,drive_item_id,fichier_id,nom_affichage,destinataire_email,destinataire_user_id,password_hash,date_expiration,nb_acces_max,cree_par)
        VALUES (?,?,?,?,?,?,?,?,?,?,?,?)")
        ->execute([$id,$token,$b['type']??'fichier',$b['driveItemId'],$b['fichierId']??null,
            clean($b['nomAffichage']??'Document'),clean($b['destinataireEmail']??''),
            $b['destinataireUserId']??null,$pwHash,
            $b['dateExpiration']??null,$b['nbAccesMax']??null,$u['id']]);
    // URL publique
    $baseUrl=(strpos($_SERVER['HTTP_HOST']??'','kapdsi.fr')!==false?'https://kapdsi.fr':'https://td-info.fr/kapdsi');
    $shareUrl=$baseUrl.'/partage/?token='.$token;
    // Envoyer email si destinataire externe
    if (!empty($b['destinataireEmail']) && empty($b['destinataireUserId'])) {
        $nomFichier=clean($b['nomAffichage']??'Document');
        $expTxt=!empty($b['dateExpiration'])?(' Lien valable jusqu\'au '.date('d/m/Y',strtotime($b['dateExpiration']))).'.' : '';
        $pwTxt=!empty($b['password'])?' Un mot de passe vous sera demandé.' : '';
        $html="<p>Bonjour,</p><p>KAP DSI vous partage le document : <strong>".htmlspecialchars($nomFichier)."</strong></p>
            <p><a href=\"$shareUrl\" style=\"background:#F5821F;color:#fff;padding:10px 20px;border-radius:4px;text-decoration:none;font-weight:bold\">Accéder au document</a></p>
            <p style=\"font-size:12px;color:#666\">$expTxt$pwTxt</p>
            <p style=\"font-size:12px;color:#666\">Lien : $shareUrl</p>";
        $payload=['sender'=>['name'=>'KAP DSI','email'=>'noreply@kapdsi.fr'],
            'to'=>[['email'=>clean($b['destinataireEmail']),'name'=>clean($b['destinataireEmail'])]],
            'subject'=>'KAP DSI vous partage : '.$nomFichier,
            'htmlContent'=>$html];
        if (defined('BREVO_KEY') && BREVO_KEY) {
            $ch=curl_init('https://api.brevo.com/v3/smtp/email');
            curl_setopt_array($ch,[CURLOPT_RETURNTRANSFER=>true,CURLOPT_POST=>true,
                CURLOPT_POSTFIELDS=>json_encode($payload),
                CURLOPT_HTTPHEADER=>['Content-Type: application/json','api-key: '.BREVO_KEY]]);
            @curl_exec($ch); @curl_close($ch);
        }
    }
    ok(['id'=>$id,'token'=>$token,'url'=>$shareUrl]);
    } catch(Exception $ePc) { err('ged_partage_create: '.$ePc->getMessage().' line '.$ePc->getLine()); }

case 'ged_partages_list':
    $u=crmAuth();
    $isSA=($u['role']??'')==='super_admin';
    $where=$isSA?'':'WHERE p.cree_par='.db()->quote($u['id']);
    $rows=db()->query("SELECT p.*,u.prenom,u.nom FROM ged_partages p LEFT JOIN crm_users u ON u.id=p.cree_par $where ORDER BY p.date_creation DESC LIMIT 100")->fetchAll();
    ok(['partages'=>array_map(fn($r)=>[
        'id'=>$r['id'],'token'=>$r['token'],'type'=>$r['type'],
        'driveItemId'=>$r['drive_item_id'],'nomAffichage'=>$r['nom_affichage'],
        'destinataireEmail'=>$r['destinataire_email']??'',
        'dateExpiration'=>$r['date_expiration']??'','nbAccesMax'=>$r['nb_acces_max']??'',
        'nbAcces'=>(int)$r['nb_acces'],'actif'=>(bool)$r['actif'],
        'creePar'=>trim(($r['prenom']??'').' '.($r['nom']??'')),
        'dateCreation'=>$r['date_creation'],'derniereConsultation'=>$r['derniere_consultation']??'',
        'hasPassword'=>!empty($r['password_hash']),
    ],$rows)]);

case 'ged_partage_revoke':
    $u=crmAuth(); $b=body();
    $isSA=($u['role']??'')==='super_admin';
    if ($isSA) db()->prepare("UPDATE ged_partages SET actif=0 WHERE id=?")->execute([$b['id']??'']);
    else db()->prepare("UPDATE ged_partages SET actif=0 WHERE id=? AND cree_par=?")->execute([$b['id']??'',$u['id']]);
    ok();

case 'ged_partage_delete':
    $u=crmAuth(); $b=body();
    $isSA=($u['role']??'')==='super_admin';
    if ($isSA) db()->prepare("DELETE FROM ged_partages WHERE id=?")->execute([$b['id']??'']);
    else db()->prepare("DELETE FROM ged_partages WHERE id=? AND cree_par=?")->execute([$b['id']??'',$u['id']]);
    ok();

case 'ged_share_check':
    // Vérifier l'accès à un partage (token + mot de passe éventuel)
    $token=$_GET['token']??'';
    $pw=$_POST['password']??$_GET['pw']??'';
    $stmt=db()->prepare("SELECT * FROM ged_partages WHERE token=? AND actif=1");
    $stmt->execute([$token]); $p=$stmt->fetch();
    if (!$p) err('Lien invalide ou expiré',404);
    if ($p['date_expiration'] && strtotime($p['date_expiration'])<time()) { db()->prepare("UPDATE ged_partages SET actif=0 WHERE id=?")->execute([$p['id']]); err('Ce lien a expiré',403); }
    if ($p['nb_acces_max'] && $p['nb_acces']>=$p['nb_acces_max']) err('Nombre maximal d\'accès atteint',403);
    if ($p['password_hash'] && !password_verify($pw,$p['password_hash'])) err('Mot de passe incorrect',401);
    // Incrémenter le compteur
    db()->prepare("UPDATE ged_partages SET nb_acces=nb_acces+1,derniere_consultation=NOW() WHERE id=?")->execute([$p['id']]);
    // Retourner les infos du partage
    $token2=gedToken();
    if ($p['type']==='dossier') {
        $q=urlencode("'".$p['drive_item_id']."' in parents and trashed=false");
        $fields=urlencode('files(id,name,mimeType,size,modifiedTime,webViewLink)');
        $isSD=(strlen($p['drive_item_id'])>2&&substr($p['drive_item_id'],0,2)==='0A');
        if ($isSD) $data=gedApi("https://www.googleapis.com/drive/v3/files?driveId=".$p['drive_item_id']."&corpora=drive&includeItemsFromAllDrives=true&supportsAllDrives=true&fields=$fields&orderBy=folder,name&pageSize=100&q=".urlencode("trashed=false"),$token2);
        else $data=gedApi("https://www.googleapis.com/drive/v3/files?q=$q&fields=$fields&orderBy=folder,name&pageSize=100&supportsAllDrives=true&includeItemsFromAllDrives=true",$token2);
        ok(['partage'=>['type'=>'dossier','nomAffichage'=>$p['nom_affichage'],'hasPassword'=>!empty($p['password_hash'])],'files'=>$data['files']??[]]);
    } else {
        $fi=gedApi("https://www.googleapis.com/drive/v3/files/".$p['drive_item_id']."?fields=id,name,mimeType,size,webViewLink,webContentLink&supportsAllDrives=true",$token2);
        ok(['partage'=>['type'=>'fichier','nomAffichage'=>$p['nom_affichage'],'hasPassword'=>!empty($p['password_hash'])],'file'=>$fi,'driveToken'=>$token2]);
    }

case 'ged_share_view':
    // Page HTML publique de partage — interceptée avant les headers JSON
    // (Déjà géré dans le bloc d'interception en haut du fichier si besoin)
    $token=$_GET['token']??'';
    $pw=$_POST['password']??'';
    // Retourner l'URL de redirection vers la page de partage
    $baseUrl=(strpos($_SERVER['HTTP_HOST']??'','kapdsi.fr')!==false?'https://kapdsi.fr':'https://td-info.fr/kapdsi');
    ok(['redirect'=>$baseUrl.'/partage/?token='.$token]);


case 'notion_page_get':
    try {
    crmAuth();
    $notionUrl2 = $_GET['url'] ?? '';
    if (!$notionUrl2) err('URL manquante');
    preg_match('/([a-f0-9]{32})(?:[?#]|$)/i', str_replace('-','', $notionUrl2), $nm);
    if (empty($nm[1])) err('ID Notion introuvable');
    $nId = $nm[1];
    $nIdF = substr($nId,0,8).'-'.substr($nId,8,4).'-'.substr($nId,12,4).'-'.substr($nId,16,4).'-'.substr($nId,20);
    $nKey = db()->query("SELECT valeur FROM app_settings WHERE cle='notion_api_key'")->fetchColumn();
    if (!$nKey) err('Cle API Notion non configuree');
    $nHdrs = array("Authorization: Bearer $nKey","Notion-Version: 2022-06-28","Content-Type: application/json");
    $nc=curl_init("https://api.notion.com/v1/pages/$nIdF"); curl_setopt_array($nc,array(CURLOPT_RETURNTRANSFER=>true,CURLOPT_HTTPHEADER=>$nHdrs)); $npd=json_decode(curl_exec($nc),true); curl_close($nc);
    if (!empty($npd['object'])&&$npd['object']==='error') err('Notion: '.$npd['message']);
    $nTitle=''; foreach(($npd['properties']??array()) as $np){if(($np['type']??'')==='title'&&!empty($np['title'])){$nTitle=implode('',array_column($np['title'],'plain_text'));break;}} if(!$nTitle)$nTitle='Sans titre';
    $nc2=curl_init("https://api.notion.com/v1/blocks/$nIdF/children?page_size=100"); curl_setopt_array($nc2,array(CURLOPT_RETURNTRANSFER=>true,CURLOPT_HTTPHEADER=>$nHdrs)); $nc2raw=curl_exec($nc2); $nbd=json_decode($nc2raw,true); curl_close($nc2);
    $nBlocks=$nbd['results']??array(); $nHtml=''; $nUl=false; $nOl=false;
    // Debug: log block types
    $nDebugTypes=array_map(function($b){return ($b['type']??'?').':hc='.json_encode($b['has_children']??null);},$nBlocks);
    // Fonction de rendu d'un bloc (sans récursion - appels API directs)
    foreach($nBlocks as $nb){
        $nt=$nb['type']??''; $nbt=$nb[$nt]??array(); $nrt=$nbt['rich_text']??array();
        $ntxt=implode('',array_map(function($r){return htmlspecialchars($r['plain_text']??'');},$nrt));
        if($nt==='bulleted_list_item'){if(!$nUl){$nHtml.='<ul>';$nUl=true;}}
        elseif($nt==='numbered_list_item'){if(!$nOl){$nHtml.='<ol>';$nOl=true;}}
        else{if($nUl){$nHtml.='</ul>';$nUl=false;}if($nOl){$nHtml.='</ol>';$nOl=false;}}
        if($nt==='transcription'){
            $nTitleB=implode('',array_column($nbt['title']??array(),'plain_text'));
            if($nTitleB) $nHtml.='<h2>'.htmlspecialchars($nTitleB).'</h2>';
            // Charger le premier enfant seulement (resume)
            if($nb['has_children']===true || $nb['has_children']===1){
                $ncX=curl_init("https://api.notion.com/v1/blocks/{$nb['id']}/children?page_size=3");
                curl_setopt_array($ncX,array(CURLOPT_RETURNTRANSFER=>true,CURLOPT_HTTPHEADER=>$nHdrs));
                $ncdX=json_decode(curl_exec($ncX),true); curl_close($ncX);
                $firstChild=$ncdX['results'][0]??null;
                if($firstChild && ($firstChild['has_children']===true||$firstChild['has_children']===1)){
                    $nc2=curl_init("https://api.notion.com/v1/blocks/{$firstChild['id']}/children?page_size=100");
                    curl_setopt_array($nc2,array(CURLOPT_RETURNTRANSFER=>true,CURLOPT_HTTPHEADER=>$nHdrs));
                    $nc2d=json_decode(curl_exec($nc2),true); curl_close($nc2);
                    $nUl2=false; $nOl2=false;
                    foreach(($nc2d['results']??array()) as $nc2b){
                        $nt2=$nc2b['type']??''; $nbt2=$nc2b[$nt2]??array(); $nrt2=$nbt2['rich_text']??array();
                        $ntxt2=implode('',array_map(function($r){return htmlspecialchars($r['plain_text']??'');},$nrt2));
                        if($nt2==='bulleted_list_item'){if(!$nUl2){$nHtml.='<ul>';$nUl2=true;}}
                        elseif($nt2==='numbered_list_item'){if(!$nOl2){$nHtml.='<ol>';$nOl2=true;}}
                        else{if($nUl2){$nHtml.='</ul>';$nUl2=false;}if($nOl2){$nHtml.='</ol>';$nOl2=false;}}
                        if($nt2==='heading_1')$nHtml.="<h1>$ntxt2</h1>";
                        elseif($nt2==='heading_2')$nHtml.="<h2>$ntxt2</h2>";
                        elseif($nt2==='heading_3')$nHtml.="<h3>$ntxt2</h3>";
                        elseif(in_array($nt2,array('bulleted_list_item','numbered_list_item')))$nHtml.="<li>$ntxt2</li>";
                        elseif($nt2==='quote')$nHtml.="<blockquote>$ntxt2</blockquote>";
                        elseif($nt2==='divider')$nHtml.='<hr>';
                        elseif($nt2==='code')$nHtml.="<pre>$ntxt2</pre>";
                        elseif($ntxt2)$nHtml.="<p>$ntxt2</p>";
                    }
                    if($nUl2)$nHtml.='</ul>'; if($nOl2)$nHtml.='</ol>';
                }
            }
        }
        elseif($nt==='paragraph'&&empty($nrt)&&($nb['has_children']===true||$nb['has_children']===1)){
            // Paragraphe conteneur vide - charger enfants
            $ncX=curl_init("https://api.notion.com/v1/blocks/{$nb['id']}/children?page_size=100");
            curl_setopt_array($ncX,array(CURLOPT_RETURNTRANSFER=>true,CURLOPT_HTTPHEADER=>$nHdrs));
            $ncdX=json_decode(curl_exec($ncX),true); curl_close($ncX);
            $nUlX=false; $nOlX=false;
            foreach(($ncdX['results']??array()) as $ncB){
                $ntX=$ncB['type']??''; $nbtX=$ncB[$ntX]??array(); $nrtX=$nbtX['rich_text']??array();
                $ntxtX=implode('',array_map(function($r){return htmlspecialchars($r['plain_text']??'');},$nrtX));
                if($ntX==='bulleted_list_item'){if(!$nUlX){$nHtml.='<ul>';$nUlX=true;}}
                elseif($ntX==='numbered_list_item'){if(!$nOlX){$nHtml.='<ol>';$nOlX=true;}}
                else{if($nUlX){$nHtml.='</ul>';$nUlX=false;}if($nOlX){$nHtml.='</ol>';$nOlX=false;}}
                if($ntX==='heading_1')$nHtml.="<h1>$ntxtX</h1>";
                elseif($ntX==='heading_2')$nHtml.="<h2>$ntxtX</h2>";
                elseif($ntX==='heading_3')$nHtml.="<h3>$ntxtX</h3>";
                elseif(in_array($ntX,array('bulleted_list_item','numbered_list_item')))$nHtml.="<li>$ntxtX</li>";
                elseif($ntX==='quote')$nHtml.="<blockquote>$ntxtX</blockquote>";
                elseif($ntX==='divider')$nHtml.='<hr>';
                elseif($ntX==='code')$nHtml.="<pre>$ntxtX</pre>";
                elseif($ntxtX)$nHtml.="<p>$ntxtX</p>";
            }
            if($nUlX)$nHtml.='</ul>'; if($nOlX)$nHtml.='</ol>';
        }
        elseif($nt==='heading_1')$nHtml.="<h1>$ntxt</h1>";
        elseif($nt==='heading_2')$nHtml.="<h2>$ntxt</h2>";
        elseif($nt==='heading_3')$nHtml.="<h3>$ntxt</h3>";
        elseif(in_array($nt,array('bulleted_list_item','numbered_list_item')))$nHtml.="<li>$ntxt</li>";
        elseif($nt==='to_do')$nHtml.='<li>'.($nbt['checked']?'[x]':'[ ]').' '.$ntxt.'</li>';
        elseif($nt==='quote')$nHtml.="<blockquote>$ntxt</blockquote>";
        elseif($nt==='divider')$nHtml.='<hr>';
        elseif($nt==='code')$nHtml.="<pre>$ntxt</pre>";
        elseif($ntxt)$nHtml.="<p>$ntxt</p>";
    }
    if($nUl)$nHtml.='</ul>'; if($nOl)$nHtml.='</ol>';
    ok(array('title'=>$nTitle,'dateCreated'=>$npd['created_time']??'','dateModified'=>$npd['last_edited_time']??'','html'=>$nHtml,'url'=>$notionUrl2));
    } catch(Exception $eNPG){ err('notion_page_get err: '.$eNPG->getMessage()); }

case 'notion_inbound':
    try {
    $b=body();
    $secret=$b['secret']??$_GET['secret']??'';
    $whSecret=db()->query("SELECT valeur FROM app_settings WHERE cle='notion_webhook_secret'")->fetchColumn();
    if($whSecret&&$secret!==$whSecret) err('Secret invalide',401);
    $pageUrl=''; $pageTitle=''; $societeNom='';
    if(!empty($b['data'])){
        $nd=$b['data'];
        // URL : priorité propriété URL, sinon url de la page
        $props=$nd['properties']??array();
        $pageUrl='';
        foreach($props as $pKey=>$prop){
            if(($prop['type']??'')==='url'&&!empty($prop['url'])) { $pageUrl=$prop['url']; break; }
        }
        if(!$pageUrl) $pageUrl=$nd['url']??'';
        // Titre
        foreach($props as $pKey=>$prop){
            if(($prop['type']??'')==='title'&&!empty($prop['title'])){
                $pageTitle=implode('',array_column($prop['title'],'plain_text')); break;
            }
        }
        // Société : type relation -> chercher par ID Notion dans app_settings ou par texte
        // Pour la relation, on ne peut pas avoir le nom directement
        // Chercher une propriété texte/select contenant soci/client
        foreach($props as $pKey=>$prop){
            $pType=$prop['type']??'';
            if(stripos($pKey,'soci')!==false||stripos($pKey,'client')!==false){
                if($pType==='rich_text'&&!empty($prop['rich_text'][0]['plain_text'])){
                    $societeNom=$prop['rich_text'][0]['plain_text']; break;
                }
                if($pType==='select'&&!empty($prop['select']['name'])){
                    $societeNom=$prop['select']['name']; break;
                }
                if($pType==='title'&&!empty($prop['title'][0]['plain_text'])){
                    $societeNom=$prop['title'][0]['plain_text']; break;
                }
                // Si relation : extraire la societe depuis le titre de la page CR
                // Ex: "KAIROS - Reunion" -> chercher "KAIROS"
                if($pType==='relation'&&!empty($prop['relation'])) {
                    // Essayer d'extraire depuis le titre de la page
                    if($pageTitle && strpos($pageTitle,' - ')!==false){
                        $parts=explode(' - ',$pageTitle,2);
                        $societeNom=trim($parts[0]);
                    } elseif($pageTitle && strpos($pageTitle,' ')!==false){
                        $parts=explode(' ',$pageTitle,2);
                        $societeNom=trim($parts[0]);
                    }
                    break;
                }
            }
        }
    } else {
        $pageUrl=clean($b['pageUrl']??$b['url']??'');
        $pageTitle=clean($b['pageTitle']??$b['title']??'');
        $societeNom=clean($b['societe']??'');
    }
    $crType=in_array($b['type']??'',array('compte_rendu_appel','compte_rendu_visio'))?$b['type']:'compte_rendu_visio';
    // Extraire la date depuis les propriétés Notion
    $crDate=date('Y-m-d H:i:s');
    if(!empty($b['data'])){
        foreach(($b['data']['properties']??array()) as $pKey=>$prop){
            $pType=$prop['type']??'';
            if($pType==='date'&&!empty($prop['date']['start'])){
                $crDate=date('Y-m-d H:i:s',strtotime($prop['date']['start']));
                break;
            }
            if($pType==='created_time'&&!empty($prop['created_time'])){
                $crDate=date('Y-m-d H:i:s',strtotime($prop['created_time']));
                break;
            }
        }
        // Fallback: date de creation de la page
        if($crDate===date('Y-m-d H:i:s')&&!empty($b['data']['created_time'])){
            $crDate=date('Y-m-d H:i:s',strtotime($b['data']['created_time']));
        }
    }
    $societeId=null;
    if($societeNom){
        $sStmt=db()->prepare("SELECT id FROM societes WHERE nom LIKE ? LIMIT 1");
        $sStmt->execute(array('%'.$societeNom.'%'));
        $soc=$sStmt->fetchColumn();
        if($soc) $societeId=$soc;
    }
    if(!$societeId) err('Societe non trouvee : '.($societeNom?:('(vide)')).' - verifiez la propriete Societe dans Notion',422);
    $nIntId=uuid();
    // Utiliser le premier super_admin comme crm_user_id
    $defUser=db()->query("SELECT id FROM crm_users WHERE role='super_admin' LIMIT 1")->fetchColumn();
    db()->prepare('INSERT INTO interactions (id,societe_id,crm_user_id,type,date,resume,notion_url) VALUES (?,?,?,?,?,?,?)')->execute(array($nIntId,$societeId,$defUser?:null,$crType,$crDate,$pageTitle?:('Compte-rendu Notion'),$pageUrl?:null));
    ok(array('id'=>$nIntId,'societeFound'=>true));
    } catch(Exception $eNI){ err('notion_inbound: '.$eNI->getMessage().' L'.$eNI->getLine()); }


// ════════════════════════════════════════════════════════════════
//  GED Boite de réception
// ════════════════════════════════════════════════════════════════
case 'ged_inbox_run':
    superAdmin();
    try {
        include __DIR__.'/ged_inbox.php';
        // Lire les derniers logs depuis la DB (évite les pb de buffer imbriqué)
        $rows = inbox_pdo()->query("SELECT level,message,created_at FROM ged_inbox_log ORDER BY created_at DESC LIMIT 30")->fetchAll();
        $lines = array_map(fn($r) => "[{$r['created_at']}][{$r['level']}] {$r['message']}", array_reverse($rows));
        ok(['log' => implode("\n", $lines)]);
    } catch(Throwable $e) {
        err('Erreur inbox: '.$e->getMessage().' (line '.$e->getLine().')');
    }

case 'ged_inbox_logs':
    superAdmin();
    $rows = inbox_pdo()->query("SELECT level,message,created_at FROM ged_inbox_log ORDER BY created_at DESC LIMIT 50")->fetchAll();
    ok(array('logs'=>$rows));

case 'ged_inbox_badge_reset':
    crmAuth();
    inbox_pdo()->prepare("UPDATE app_settings SET valeur='0' WHERE cle='ged_inbox_new_count'")->execute();
    ok();

default:
    err('Action inconnue : '.htmlspecialchars($action),404);
}


// ════════════════════════════════════════════════════════════════
//  TICKETS — Fonctions utilitaires
// ════════════════════════════════════════════════════════════════
