<?php
// /plata/webhooks/stripe.php
declare (strict_types = 1);

// Webhook Stripe: pas de session user / pas de require_login
require_once realpath(__DIR__ . '/../../../includes/bootstrap_global.php');
// ↑ ajuste le chemin selon ton arborescence réelle
// (l'idée : charger .env + $pdo + $translations si tu veux, mais pas de login)

// Endpoint public (pas de session / pas de require_login)
require_once __DIR__ . '/../lib/stripe_client.php'; // $stripe (StripeClient)

$configAll      = require __DIR__ . '/../config/stripe.php';
$cfg            = $configAll[$configAll['mode']] ?? null;
$endpointSecret = $cfg['webhook_secret'] ?? '';

if (! $cfg || empty($cfg['secret_key']) || empty($endpointSecret)) {
    http_response_code(500);
    echo 'Config Stripe invalide';
    exit;
}

// Durcir PDO
if (isset($pdo) && $pdo instanceof PDO) {
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
}

// ---------------- LOG ----------------
$logFile = '/tmp/stripe_webhook.log';
@touch($logFile);
function wh_log(string $msg): void
{
    global $logFile;
    @file_put_contents($logFile, '[' . date('c') . '] ' . $msg . "\n", FILE_APPEND);
}

// ---------------- Helpers DB ----------------
function colExists(PDO $pdo, string $table, string $col): bool
{
    static $cache = [];
    $k            = $table . '.' . $col;
    if (array_key_exists($k, $cache)) {
        return $cache[$k];
    }

    $sql = "SELECT 1 FROM information_schema.COLUMNS
          WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ? LIMIT 1";
    $st = $pdo->prepare($sql);
    $st->execute([$table, $col]);
    $cache[$k] = (bool) $st->fetchColumn();
    return $cache[$k];
}

function enumOptions(PDO $pdo, string $table, string $col): array
{
    $sql = "SELECT COLUMN_TYPE FROM information_schema.COLUMNS
          WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ? LIMIT 1";
    $st = $pdo->prepare($sql);
    $st->execute([$table, $col]);
    $type = (string) $st->fetchColumn();
    if (stripos($type, 'enum(') !== 0) {
        return [];
    }

    preg_match_all("/'([^']*)'/", $type, $m);
    return $m[1] ?? [];
}

function pickStatut(PDO $pdo, string $fallback = 'payé'): string
{
    if (! colExists($pdo, 'paiement', 'statut')) {
        return $fallback;
    }

    $opts = enumOptions($pdo, 'paiement', 'statut');
    if (! $opts) {
        return $fallback;
    }

    $prefs = ['payé', 'paye', 'paid', 'validé', 'valide', 'completed', 'ok'];
    foreach ($prefs as $p) {
        if (in_array($p, $opts, true)) {
            return $p;
        }

    }
    return $opts[0];
}

function periodFallbackFromPlan(PDO $pdo, int $abonnement_id): array
{
    // Retourne [cps, cpe] en UTC Y-m-d H:i:s
    try {
        $st = $pdo->prepare("SELECT duree_jours FROM abonnement WHERE id = ? LIMIT 1");
        $st->execute([$abonnement_id]);
        $row   = $st->fetch(PDO::FETCH_ASSOC);
        $duree = isset($row['duree_jours']) ? (int) $row['duree_jours'] : 30;
        if ($duree <= 0) {
            $duree = 30;
        }

        $now = new DateTime('now', new DateTimeZone('UTC'));
        $cps = $now->format('Y-m-d H:i:s');
        $cpe = (clone $now)->modify("+$duree days")->format('Y-m-d H:i:s');
        return [$cps, $cpe];
    } catch (Throwable $e) {
        return [null, null];
    }
}

/**
 * Dernier état billing_subscriptions du praticien (source de vérité pour bloquer auto/manual).
 */
function getLastBilling(PDO $pdo, int $praticien_id): ?array
{
    try {
        $st = $pdo->prepare("
      SELECT *
      FROM billing_subscriptions
      WHERE praticien_id = ?
      ORDER BY id DESC
      LIMIT 1
    ");
        $st->execute([$praticien_id]);
        $row = $st->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    } catch (Throwable $e) {
        return null;
    }
}

/**
 * Actif si current_period_end > now (UTC) + status dans liste.
 */
function isBillingStillActive(?array $b): bool
{
    if (! $b) {
        return false;
    }

    $cpe = (string) ($b['current_period_end'] ?? '');
    if ($cpe === '') {
        return false;
    }

    $status         = strtolower((string) ($b['status'] ?? ''));
    $activeStatuses = ['active', 'trialing', 'paid', 'updated'];
    if (! in_array($status, $activeStatuses, true)) {
        return false;
    }

    try {
        $end = new DateTime($cpe, new DateTimeZone('UTC'));
        $now = new DateTime('now', new DateTimeZone('UTC'));
        return $end > $now;
    } catch (Throwable $e) {
        return false;
    }
}

// ---------------- Helpers Stripe ----------------
function toArrayMeta($mdRaw): array
{
    if (is_object($mdRaw) && method_exists($mdRaw, 'toArray')) {
        return $mdRaw->toArray();
    }

    if (is_array($mdRaw)) {
        return $mdRaw;
    }

    return [];
}

// Stripe peut renvoyer string OU objet {id:...} OU array ['id'=>...]
function stripeId($v): ?string
{
    if (is_string($v) && $v !== '') {
        return $v;
    }

    if (is_object($v)) {
        if (isset($v->id) && is_string($v->id) && $v->id !== '') {
            return (string) $v->id;
        }

        if (method_exists($v, 'toArray')) {
            $a = $v->toArray();
            if (isset($a['id']) && is_string($a['id']) && $a['id'] !== '') {
                return (string) $a['id'];
            }

        }
    }
    if (is_array($v) && isset($v['id']) && is_string($v['id']) && $v['id'] !== '') {
        return (string) $v['id'];
    }

    return null;
}

function dtFromUnix($ts): ?string
{
    if (! is_numeric($ts)) {
        return null;
    }

    $ts = (int) $ts;
    if ($ts <= 0) {
        return null;
    }

    return (new DateTime('@' . $ts))->setTimezone(new DateTimeZone('UTC'))->format('Y-m-d H:i:s');
}

function insertPaiementFromInvoice(PDO $pdo, array $cfgAll, $stripe, \Stripe\Invoice $inv, int $praticien_id, int $abonnement_id, ?string $sub_id, string $eventId): void
{
    $invoice_id = (string) ($inv->id ?? '');
    if ($invoice_id === '' || $praticien_id <= 0 || $abonnement_id <= 0) {
        return;
    }

    // Idempotence par invoice_id si colonne dispo
    if (colExists($pdo, 'paiement', 'stripe_invoice_id')) {
        $st = $pdo->prepare("SELECT 1 FROM paiement WHERE stripe_invoice_id = ? LIMIT 1");
        $st->execute([$invoice_id]);
        if ((bool) $st->fetchColumn()) {
            return;
        }

    }

    $amount_cent = is_numeric($inv->amount_paid ?? null) ? (int) $inv->amount_paid : null;
    $prix        = is_numeric($amount_cent) ? $amount_cent / 100.0 : 0.0;

    $statut = pickStatut($pdo, 'payé');

    $pdo->beginTransaction();

    $fields = [
        'praticien_id'  => $praticien_id,
        'abonnement_id' => $abonnement_id,
        'montant'       => (float) $prix,
    ];
    if (colExists($pdo, 'paiement', 'methode')) {
        $fields['methode'] = 'carte';
    }

    if (colExists($pdo, 'paiement', 'statut')) {
        $fields['statut'] = $statut;
    }

    if (colExists($pdo, 'paiement', 'currency')) {
        $fields['currency'] = (string) ($inv->currency ?? ($cfgAll['currency'] ?? 'eur'));
    }

    if (colExists($pdo, 'paiement', 'stripe_invoice_id')) {
        $fields['stripe_invoice_id'] = $invoice_id;
    }

    if ($sub_id && colExists($pdo, 'paiement', 'stripe_sub_id')) {
        $fields['stripe_sub_id'] = (string) $sub_id;
    }

    if ($eventId && colExists($pdo, 'paiement', 'stripe_event_id')) {
        $fields['stripe_event_id'] = $eventId;
    }

    $cols    = array_keys($fields);
    $holders = array_fill(0, count($cols), '?');
    $vals    = array_values($fields);

    $sql = "INSERT INTO paiement (" . implode(',', $cols) . ") VALUES (" . implode(',', $holders) . ")";
    $pdo->prepare($sql)->execute($vals);

    $pdo->commit();
}

// ---------------- Lecture payload & signature ----------------
$payload   = @file_get_contents('php://input');
$sigHeader = $_SERVER['HTTP_STRIPE_SIGNATURE'] ?? '';
if ($sigHeader === '' && function_exists('getallheaders')) {
    $all = getallheaders();
    if (isset($all['Stripe-Signature'])) {
        $sigHeader = $all['Stripe-Signature'];
    } elseif (isset($all['stripe-signature'])) {
        $sigHeader = $all['stripe-signature'];
    }

}
wh_log('HDR sig=' . ($sigHeader !== '' ? 'PRESENT' : 'ABSENT'));

try {
    $event = \Stripe\Webhook::constructEvent($payload, $sigHeader, $endpointSecret);
} catch (\UnexpectedValueException $e) {
    wh_log('Invalid payload: ' . $e->getMessage());
    http_response_code(400);
    exit('Invalid payload');
} catch (\Stripe\Exception\SignatureVerificationException $e) {
    wh_log('Invalid signature: ' . $e->getMessage());
    http_response_code(400);
    exit('Invalid signature');
}

$eventId = (string) ($event->id ?? '');
$type    = (string) ($event->type ?? '');
$obj     = $event->data->object ?? null;

// ---------------- Lookups billing_subscriptions ----------------
$lookupLocalBySubId = function (?string $subId) use ($pdo): array {
    if (! $subId || ! ($pdo instanceof PDO)) {
        return [null, null, null, null, null, null];
    }
    // pid, aid, plan, product, cps, cpe
    $st = $pdo->prepare("
    SELECT praticien_id, abonnement_id, provider_plan_id, provider_product_id, current_period_start, current_period_end
    FROM billing_subscriptions
    WHERE provider = 'stripe' AND provider_subscription_id = ?
    ORDER BY id DESC LIMIT 1
  ");
    $st->execute([$subId]);
    $row = $st->fetch(PDO::FETCH_ASSOC) ?: [];
    return [
        isset($row['praticien_id']) ? (int) $row['praticien_id'] : null,
        isset($row['abonnement_id']) ? (int) $row['abonnement_id'] : null,
        isset($row['provider_plan_id']) ? (string) $row['provider_plan_id'] : null,
        isset($row['provider_product_id']) ? (string) $row['provider_product_id'] : null,
        isset($row['current_period_start']) ? (string) $row['current_period_start'] : null,
        isset($row['current_period_end']) ? (string) $row['current_period_end'] : null,
    ];
};

$lookupLocalByCustomerId = function (?string $custId) use ($pdo): array {
    if (! $custId || ! ($pdo instanceof PDO)) {
        return [null, null, null, null, null, null, null];
    }
    // pid, aid, sub, plan, product, cps, cpe
    $st = $pdo->prepare("
    SELECT praticien_id, abonnement_id, provider_subscription_id, provider_plan_id, provider_product_id, current_period_start, current_period_end
    FROM billing_subscriptions
    WHERE provider = 'stripe' AND provider_customer_id = ?
    ORDER BY id DESC LIMIT 1
  ");
    $st->execute([$custId]);
    $row = $st->fetch(PDO::FETCH_ASSOC) ?: [];
    return [
        isset($row['praticien_id']) ? (int) $row['praticien_id'] : null,
        isset($row['abonnement_id']) ? (int) $row['abonnement_id'] : null,
        isset($row['provider_subscription_id']) ? (string) $row['provider_subscription_id'] : null,
        isset($row['provider_plan_id']) ? (string) $row['provider_plan_id'] : null,
        isset($row['provider_product_id']) ? (string) $row['provider_product_id'] : null,
        isset($row['current_period_start']) ? (string) $row['current_period_start'] : null,
        isset($row['current_period_end']) ? (string) $row['current_period_end'] : null,
    ];
};

// ---------------- Upsert billing_subscriptions (ATOMIQUE, safe DATETIME) ----------------
$upsertBilling = function (array $data) use ($pdo): void {
    if (! ($pdo instanceof PDO)) {
        return;
    }

    if (empty($data['praticien_id'])) {
        wh_log('BILL_SKIP_NO_PID sub=' . ($data['provider_subscription_id'] ?? '') . ' cust=' . ($data['provider_customer_id'] ?? '') . ' inv=' . ($data['last_invoice_id'] ?? ''));
        return;
    }

    // Colonnes à ne jamais écraser par NULL / "" (strings)
    $noNullOverwriteStrings = [
        'provider_customer_id',
        'provider_subscription_id',
        'provider_plan_id',
        'provider_product_id',
        'last_invoice_id',
    ];

    // Colonnes DATETIME à ne jamais écraser par NULL (PAS de test "")
    $noNullOverwriteDates = [
        'current_period_start',
        'current_period_end',
        'canceled_at',
        'ended_at',
    ];

    $allowedCols = [
        'praticien_id', 'abonnement_id', 'billing_mode', 'provider',
        'provider_customer_id', 'provider_subscription_id', 'provider_plan_id', 'provider_product_id',
        'status', 'current_period_start', 'current_period_end',
        'cancel_at_period_end', 'canceled_at', 'ended_at',
        'last_event_id', 'last_invoice_id',
    ];

    // Prépare $fields (colonnes existantes + filtrage)
    $fields = [];
    foreach ($allowedCols as $col) {
        if (! array_key_exists($col, $data)) {
            continue;
        }

        if (! colExists($pdo, 'billing_subscriptions', $col)) {
            continue;
        }

        $val = $data[$col];

        // Strings: skip NULL et ""
        if (in_array($col, $noNullOverwriteStrings, true)) {
            if ($val === null) {
                continue;
            }

            if (is_string($val) && trim($val) === '') {
                continue;
            }

        }

        // Dates: skip NULL (on accepte pas "" non plus, mais normalement tu n’en envoies pas)
        if (in_array($col, $noNullOverwriteDates, true)) {
            if ($val === null) {
                continue;
            }

            if (is_string($val) && trim($val) === '') {
                continue;
            }

        }

        // status: éviter ""
        if ($col === 'status' && is_string($val) && trim($val) === '') {
            continue;
        }

        $fields[$col] = $val;
    }

    if (! $fields) {
        return;
    }

    $cols         = array_keys($fields);
    $placeholders = array_fill(0, count($cols), '?');
    $vals         = array_values($fields);

    $updates = [];
    foreach ($cols as $col) {
        if ($col === 'praticien_id') {
            continue;
        }

        if (in_array($col, $noNullOverwriteDates, true)) {
            // DATETIME: seulement NULL check
            $updates[] = "$col = CASE
        WHEN VALUES($col) IS NULL THEN $col
        ELSE VALUES($col)
      END";
        } elseif (in_array($col, $noNullOverwriteStrings, true)) {
            // STRING: NULL ou "" -> garder l'existant
            $updates[] = "$col = CASE
        WHEN VALUES($col) IS NULL THEN $col
        WHEN VALUES($col) = '' THEN $col
        ELSE VALUES($col)
      END";
        } else {
            $updates[] = "$col = VALUES($col)";
        }
    }

    if (! $updates) {
        $updates[] = "last_event_id = VALUES(last_event_id)";
    }

    $sql = "INSERT INTO billing_subscriptions (" . implode(',', $cols) . ")
          VALUES (" . implode(',', $placeholders) . ")
          ON DUPLICATE KEY UPDATE " . implode(', ', $updates);

    $pdo->prepare($sql)->execute($vals);
};

// =====================================================
// 1) CHECKOUT
// =====================================================
$checkoutEvents = [
    'checkout.session.completed',
    'checkout.session.async_payment_succeeded',
];

if (in_array($type, $checkoutEvents, true) && $obj) {
    try {
        if (! ($pdo instanceof PDO)) {
            throw new RuntimeException('PDO indisponible');
        }

        /** @var \Stripe\Checkout\Session $cs */
        $cs = $obj;

        $cs_id = $cs->id ?? null;
        $mode  = (string) ($cs->mode ?? 'payment');

        // Refetch checkout: plus fiable pour customer/subscription
        $sub_id  = stripeId($cs->subscription ?? null);
        $cust_id = stripeId($cs->customer ?? null);

        if ($cs_id) {
            try {
                $csFull = $stripe->checkout->sessions->retrieve((string) $cs_id, [
                    'expand' => ['subscription'],
                ]);
                $sub_id  = stripeId($csFull->subscription ?? null) ?? $sub_id;
                $cust_id = stripeId($csFull->customer ?? null) ?? $cust_id;
                $cs      = $csFull;
            } catch (Throwable $e) {
                wh_log('WARN refetch checkout failed: ' . $e->getMessage());
            }
        }

        $md            = toArrayMeta($cs->metadata ?? null);
        $praticien_id  = isset($md['praticien_id']) ? (int) $md['praticien_id'] : null;
        $abonnement_id = isset($md['abonnement_id']) ? (int) $md['abonnement_id'] : null;

        wh_log('CHECKOUT ev=' . $eventId . ' type=' . $type . ' mode=' . $mode . ' cs=' . $cs_id . ' sub=' . ($sub_id ?: 'null') . ' cust=' . ($cust_id ?: 'null') . ' mdkeys=' . implode(',', array_keys($md)));

        // ---- ABONNEMENT AUTO ----
        if ($mode === 'subscription') {

            // BLOCK: si un MANUAL actif existe, on n'écrase rien
            if ($praticien_id) {
                $existing = getLastBilling($pdo, (int) $praticien_id);
                if ($existing && isBillingStillActive($existing) && ((string) ($existing['billing_mode'] ?? '') === 'manual')) {
                    wh_log('AUTO_BLOCKED_MANUAL_ACTIVE pid=' . $praticien_id . ' existing_end=' . ($existing['current_period_end'] ?? ''));
                    http_response_code(200);
                    echo 'ok-auto-blocked';
                    exit;
                }
            }

            $provider_plan_id    = isset($md['provider_plan_id']) ? (string) $md['provider_plan_id'] : null;
            $provider_product_id = null;

            $sub_status           = null;
            $current_period_start = null;
            $current_period_end   = null;
            $cancel_at_period_end = 0;
            $canceled_at          = null;
            $ended_at             = null;

            // Subscription: récupérer périodes + ids price/product + metadata
            if ($sub_id) {
                try {
                    $sub                  = $stripe->subscriptions->retrieve((string) $sub_id, []); // PAS d'expands inutiles
                    $sub_status           = $sub->status ?? null;
                    $cancel_at_period_end = ! empty($sub->cancel_at_period_end) ? 1 : 0;

                    $current_period_start = dtFromUnix($sub->current_period_start ?? null);
                    $current_period_end   = dtFromUnix($sub->current_period_end ?? null);
                    $canceled_at          = dtFromUnix($sub->canceled_at ?? null);
                    $ended_at             = dtFromUnix($sub->ended_at ?? null);

                    $sub_md = toArrayMeta($sub->metadata ?? null);
                    if (! $praticien_id && isset($sub_md['praticien_id'])) {
                        $praticien_id = (int) $sub_md['praticien_id'];
                    }

                    if (! $abonnement_id && isset($sub_md['abonnement_id'])) {
                        $abonnement_id = (int) $sub_md['abonnement_id'];
                    }

                    if (! $provider_plan_id && isset($sub->items->data[0]->price->id)) {
                        $provider_plan_id = (string) $sub->items->data[0]->price->id;
                    }
                    if (isset($sub->items->data[0]->price->product)) {
                        $provider_product_id = stripeId($sub->items->data[0]->price->product);
                    }
                } catch (Throwable $e) {
                    wh_log('WARN fetch subscription from checkout: ' . $e->getMessage());
                }
            }

            // Fallback DB par customer si besoin
            if ((! $praticien_id || ! $abonnement_id || ! $sub_id) && $cust_id) {
                [$pid_db, $aid_db, $sub_db, $plan_db, $prod_db, $cps_db, $cpe_db] = $lookupLocalByCustomerId($cust_id);
                if (! $praticien_id && $pid_db) {
                    $praticien_id = $pid_db;
                }

                if (! $abonnement_id && $aid_db) {
                    $abonnement_id = $aid_db;
                }

                if (! $sub_id && $sub_db) {
                    $sub_id = $sub_db;
                }

                if (! $provider_plan_id && $plan_db) {
                    $provider_plan_id = $plan_db;
                }

                if (! $provider_product_id && $prod_db) {
                    $provider_product_id = $prod_db;
                }

                if (! $current_period_start && $cps_db) {
                    $current_period_start = $cps_db;
                }

                if (! $current_period_end && $cpe_db) {
                    $current_period_end = $cpe_db;
                }

            }

            // Fallback périodes (dernier recours)
            if ((! $current_period_start || ! $current_period_end) && $abonnement_id) {
                [$cpsF, $cpeF] = periodFallbackFromPlan($pdo, (int) $abonnement_id);
                if (! $current_period_start && $cpsF) {
                    $current_period_start = $cpsF;
                }

                if (! $current_period_end && $cpeF) {
                    $current_period_end = $cpeF;
                }

            }

            wh_log('BILLING_UPSERT_FROM_CHECKOUT pid=' . ($praticien_id ?: 'null') . ' sub=' . ($sub_id ?: 'null') . ' plan=' . ($provider_plan_id ?: 'null') . ' cps=' . ($current_period_start ?: 'null') . ' cpe=' . ($current_period_end ?: 'null'));

            $upsertBilling([
                'praticien_id'             => $praticien_id,
                'abonnement_id'            => $abonnement_id,
                'billing_mode'             => 'auto',
                'provider'                 => 'stripe',
                'provider_customer_id'     => $cust_id,
                'provider_subscription_id' => $sub_id,
                'provider_plan_id'         => $provider_plan_id,
                'provider_product_id'      => $provider_product_id,
                'status'                   => $sub_status ?? 'active',
                'current_period_start'     => $current_period_start,
                'current_period_end'       => $current_period_end,
                'cancel_at_period_end'     => (int) $cancel_at_period_end,
                'canceled_at'              => $canceled_at,
                'ended_at'                 => $ended_at,
                'last_event_id'            => $eventId,
            ]);

            // IMPORTANT:
            // Ne pas activer/prolonger l'accès ici.
            // L'accès réel est accordé uniquement sur invoice.paid.

            // IMPORTANT:
            // Ne pas créer de ligne paiement ici pour un abonnement.
            // La création du paiement réel se fait uniquement sur invoice.paid.

            http_response_code(200);
            echo 'ok-checkout-subscription';
            exit;
        }

        // ---- PAIEMENT PONCTUEL (MANUAL) ----
        $amount_total = $cs->amount_total ?? null;
        $currency     = $cs->currency ?? ($configAll['currency'] ?? 'eur');

        if (! $praticien_id || ! $abonnement_id) {
            wh_log('META STILL MISSING cs=' . $cs_id);
            http_response_code(200);
            echo 'ok-meta-missing';
            exit;
        }

        // BLOCK: si un AUTO actif existe, on n'écrase rien
        $existing = getLastBilling($pdo, (int) $praticien_id);
        if ($existing && isBillingStillActive($existing) && ((string) ($existing['billing_mode'] ?? '') === 'auto')) {
            wh_log('MANUAL_BLOCKED_AUTO_ACTIVE pid=' . $praticien_id . ' existing_sub=' . ($existing['provider_subscription_id'] ?? '') . ' existing_end=' . ($existing['current_period_end'] ?? ''));
            http_response_code(200);
            echo 'ok-manual-blocked';
            exit;
        }

        $prix = is_numeric($amount_total) ? ((int) $amount_total) / 100.0 : 0.0;

        // Idempotence : event id
        if ($eventId && colExists($pdo, 'paiement', 'stripe_event_id')) {
            $st = $pdo->prepare("SELECT 1 FROM paiement WHERE stripe_event_id = ? LIMIT 1");
            $st->execute([$eventId]);
            if ((bool) $st->fetchColumn()) {
                http_response_code(200);
                echo 'ok-duplicate';
                exit;
            }
        }

        // durée abonnement
        $st = $pdo->prepare("SELECT duree_jours FROM abonnement WHERE id = ? LIMIT 1");
        $st->execute([(int) $abonnement_id]);
        $ab = $st->fetch(PDO::FETCH_ASSOC);
        if (! $ab) {
            throw new RuntimeException('BAD_PLAN');
        }

        $duree_jours     = (int) $ab['duree_jours'];
        $date_expiration = (new DateTime('now'))->modify("+$duree_jours days")->format('Y-m-d H:i:s');

        $statut = pickStatut($pdo, 'payé');

        // Périodes billing en UTC
        $cpsUtc = (new DateTime('now', new DateTimeZone('UTC')))->format('Y-m-d H:i:s');
        $cpeUtc = null;
        try {
            $cpeUtc = (new DateTime($date_expiration))->setTimezone(new DateTimeZone('UTC'))->format('Y-m-d H:i:s');
        } catch (Throwable $e) {
            $cpeUtc = (new DateTime('now', new DateTimeZone('UTC')))->modify("+$duree_jours days")->format('Y-m-d H:i:s');
        }

        $pdo->beginTransaction();

        // 1) paiement
        $fields = [
            'praticien_id'  => (int) $praticien_id,
            'abonnement_id' => (int) $abonnement_id,
            'montant'       => (float) $prix,
        ];
        if (colExists($pdo, 'paiement', 'methode')) {
            $fields['methode'] = 'carte';
        }

        if (colExists($pdo, 'paiement', 'statut')) {
            $fields['statut'] = $statut;
        }

        if ($eventId && colExists($pdo, 'paiement', 'stripe_event_id')) {
            $fields['stripe_event_id'] = $eventId;
        }

        $cols    = array_keys($fields);
        $holders = array_fill(0, count($cols), '?');
        $vals    = array_values($fields);

        $sql = "INSERT INTO paiement (" . implode(',', $cols) . ") VALUES (" . implode(',', $holders) . ")";
        $pdo->prepare($sql)->execute($vals);

        // 2) billing_subscriptions MANUAL (provider=stripe)
        $upsertBilling([
            'praticien_id'             => (int) $praticien_id,
            'abonnement_id'            => (int) $abonnement_id,
            'billing_mode'             => 'manual',
            'provider'                 => 'stripe',
            'provider_customer_id'     => $cust_id,
            'provider_subscription_id' => null,
            'status'                   => 'active',
            'current_period_start'     => $cpsUtc,
            'current_period_end'       => $cpeUtc,
            'cancel_at_period_end'     => 0,
            'canceled_at'              => null,
            'ended_at'                 => null,
            'last_event_id'            => $eventId,
        ]);

        // 3) praticien
        if (colExists($pdo, 'praticien', 'date_expiration')) {
            $pdo->prepare("UPDATE praticien SET abonnement_id = ?, date_expiration = ? WHERE id = ?")
                ->execute([(int) $abonnement_id, $date_expiration, (int) $praticien_id]);
        }

        $pdo->commit();

        http_response_code(200);
        echo 'ok';
        exit;

    } catch (Throwable $e) {
        if (isset($pdo) && $pdo instanceof PDO && $pdo->inTransaction()) {
            $pdo->rollBack();
        }

        $errInfo = ($e instanceof PDOException && isset($e->errorInfo)) ? json_encode($e->errorInfo, JSON_UNESCAPED_UNICODE) : '';
        wh_log('ERR CHECKOUT ev=' . $eventId . ' type=' . get_class($e) . ' msg=' . $e->getMessage() . ' file=' . $e->getFile() . ':' . $e->getLine() . ' info=' . $errInfo);
        http_response_code(500);
        echo 'error';
        exit;
    }
}

// =====================================================
// 2) BILLING (renouvellements)
// =====================================================
$billingEvents = [
    'invoice.paid',
    'invoice.payment_failed',
    'invoice.payment_action_required',
    'invoice_payment.paid', // type vu chez toi (non standard)
    'customer.subscription.updated',
    'customer.subscription.deleted',
];

if (in_array($type, $billingEvents, true) && $obj) {
    try {
        if (! ($pdo instanceof PDO)) {
            throw new RuntimeException('PDO indisponible');
        }

        // ---------- invoice.* ----------
        if (
            $type === 'invoice.paid' ||
            $type === 'invoice.payment_failed' ||
            $type === 'invoice.payment_action_required' ||
            $type === 'invoice_payment.paid'
        ) {

            // invoice_payment.paid => récupérer la vraie invoice
            if ($type === 'invoice_payment.paid') {
                $ip        = $obj;
                $invoiceId = stripeId($ip->invoice ?? null);
                if (! $invoiceId) {
                    wh_log('ERR invoice_payment.paid missing invoice id');
                    http_response_code(200);
                    echo 'ok-invoicepayment-missing-invoice';
                    exit;
                }
                try {
                    $obj = $stripe->invoices->retrieve((string) $invoiceId, []);
                } catch (Throwable $e) {
                    wh_log('ERR fetch invoice from invoice_payment: ' . $e->getMessage());
                    http_response_code(200);
                    echo 'ok-invoicepayment-noinvoice';
                    exit;
                }
                $type = 'invoice.paid';
            }

            /** @var \Stripe\Invoice $inv */
            $inv        = $obj;
            $invoice_id = $inv->id ?? null;

            // Refetch invoice complet pour avoir subscription + lines.price.product si possible
            if ($invoice_id) {
                try {
                    $inv = $stripe->invoices->retrieve((string) $invoice_id, [
                        'expand' => ['subscription', 'lines.data.price.product'],
                    ]);
                } catch (Throwable $e) {
                    wh_log('WARN expand invoice retrieve failed: ' . $e->getMessage());
                }
            }

            $sub_id  = stripeId($inv->subscription ?? null);
            $cust_id = stripeId($inv->customer ?? null);

            $amount_cent = null;
            if ($type === 'invoice.paid') {
                $amount_cent = is_numeric($inv->amount_paid ?? null) ? (int) $inv->amount_paid : null;
            } else {
                $amount_cent = is_numeric($inv->amount_due ?? null) ? (int) $inv->amount_due : null;
            }

            // Price/Product + période depuis lignes
            $provider_plan_id     = null;
            $provider_product_id  = null;
            $invoice_period_start = null;
            $invoice_period_end   = null;
            try {
                if (isset($inv->lines->data) && is_array($inv->lines->data) && ! empty($inv->lines->data)) {
                    $line0 = $inv->lines->data[0];

                    if (isset($line0->price)) {
                        $provider_plan_id = stripeId($line0->price);
                        if (is_object($line0->price) && isset($line0->price->product)) {
                            $provider_product_id = stripeId($line0->price->product);
                        }
                    }

                    if (isset($line0->period)) {
                        if (isset($line0->period->start)) {
                            $invoice_period_start = dtFromUnix($line0->period->start);
                        }
                        if (isset($line0->period->end)) {
                            $invoice_period_end = dtFromUnix($line0->period->end);
                        }
                    }
                }
            } catch (Throwable $e) {
                wh_log('WARN invoice lines parse: ' . $e->getMessage());
            }

            // Metadata invoice
            $md            = toArrayMeta($inv->metadata ?? null);
            $praticien_id  = isset($md['praticien_id']) ? (int) $md['praticien_id'] : null;
            $abonnement_id = isset($md['abonnement_id']) ? (int) $md['abonnement_id'] : null;

            // périodes via subscription
            $sub_status           = null;
            $cancel_at_period_end = 0;
            $current_period_start = null;
            $current_period_end   = null;
            $canceled_at          = null;
            $ended_at             = null;

            // Si Stripe a envoyé sub=null sur invoice, on tente de le récupérer via customer (DB)
            if (! $sub_id && $cust_id) {
                [, , $sub_db, $plan_db, $prod_db, $cps_db, $cpe_db] = $lookupLocalByCustomerId($cust_id);
                if ($sub_db) {
                    $sub_id = $sub_db;
                }

                if (! $provider_plan_id && $plan_db) {
                    $provider_plan_id = $plan_db;
                }

                if (! $provider_product_id && $prod_db) {
                    $provider_product_id = $prod_db;
                }

                if (! $current_period_start && $cps_db) {
                    $current_period_start = $cps_db;
                }

                if (! $current_period_end && $cpe_db) {
                    $current_period_end = $cpe_db;
                }

            }

            // Fallback Stripe direct : si sub_id toujours NULL mais cust_id connu (race condition checkout/invoice)
            if (! $sub_id && $cust_id) {
                try {
                    $list = $stripe->subscriptions->all([
                        'customer' => (string) $cust_id,
                        'status'   => 'all',
                        'limit'    => 5,
                    ]);
                    if (isset($list->data) && is_array($list->data) && ! empty($list->data)) {
                        // prendre la plus récente (Stripe renvoie généralement du plus récent au plus ancien)
                        $sub0   = $list->data[0];
                        $sub_id = stripeId($sub0->id ?? null) ?: $sub_id;
                        wh_log('WARN invoice missing sub_id; recovered via customer subs list sub=' . ($sub_id ?: 'null') . ' cust=' . $cust_id);
                    }
                } catch (Throwable $e) {
                    wh_log('WARN subscriptions->all failed for cust=' . $cust_id . ' : ' . $e->getMessage());
                }
            }

            if ($sub_id) {
                try {
                    $sub                  = $stripe->subscriptions->retrieve((string) $sub_id, []);
                    $sub_status           = $sub->status ?? null;
                    $cancel_at_period_end = ! empty($sub->cancel_at_period_end) ? 1 : 0;
                    $current_period_start = $current_period_start ?: dtFromUnix($sub->current_period_start ?? null);
                    $current_period_end   = $current_period_end ?: dtFromUnix($sub->current_period_end ?? null);
                    $canceled_at          = dtFromUnix($sub->canceled_at ?? null);
                    $ended_at             = dtFromUnix($sub->ended_at ?? null);

                    $sub_md = toArrayMeta($sub->metadata ?? null);
                    if (! $praticien_id && isset($sub_md['praticien_id'])) {
                        $praticien_id = (int) $sub_md['praticien_id'];
                    }

                    if (! $abonnement_id && isset($sub_md['abonnement_id'])) {
                        $abonnement_id = (int) $sub_md['abonnement_id'];
                    }

                    if (! $provider_plan_id && isset($sub->items->data[0]->price->id)) {
                        $provider_plan_id = (string) $sub->items->data[0]->price->id;
                    }
                    if (! $provider_product_id && isset($sub->items->data[0]->price->product)) {
                        $provider_product_id = stripeId($sub->items->data[0]->price->product);
                    }
                } catch (Throwable $e) {
                    wh_log('WARN fetch subscription from invoice: ' . $e->getMessage());
                }
            }

            // Fallback DB : by sub_id puis customer_id
            if (! $praticien_id || ! $abonnement_id) {
                [$pid_db, $aid_db, $plan_db, $prod_db, $cps_db, $cpe_db] = $lookupLocalBySubId($sub_id);
                if (! $praticien_id && $pid_db) {
                    $praticien_id = $pid_db;
                }

                if (! $abonnement_id && $aid_db) {
                    $abonnement_id = $aid_db;
                }

                if (! $provider_plan_id && $plan_db) {
                    $provider_plan_id = $plan_db;
                }

                if (! $provider_product_id && $prod_db) {
                    $provider_product_id = $prod_db;
                }

                if (! $current_period_start && $cps_db) {
                    $current_period_start = $cps_db;
                }

                if (! $current_period_end && $cpe_db) {
                    $current_period_end = $cpe_db;
                }

            }
            if (! $praticien_id && $cust_id) {
                [$pid_db, $aid_db, $sub_db, $plan_db, $prod_db, $cps_db, $cpe_db] = $lookupLocalByCustomerId($cust_id);
                if ($pid_db) {
                    $praticien_id = $pid_db;
                }

                if (! $abonnement_id && $aid_db) {
                    $abonnement_id = $aid_db;
                }

                if (! $sub_id && $sub_db) {
                    $sub_id = $sub_db;
                }

                if (! $provider_plan_id && $plan_db) {
                    $provider_plan_id = $plan_db;
                }

                if (! $provider_product_id && $prod_db) {
                    $provider_product_id = $prod_db;
                }

                if (! $current_period_start && $cps_db) {
                    $current_period_start = $cps_db;
                }

                if (! $current_period_end && $cpe_db) {
                    $current_period_end = $cpe_db;
                }

            }

            // Fallback périodes dernier recours
            if ((! $current_period_start || ! $current_period_end) && $abonnement_id) {
                [$cpsF, $cpeF] = periodFallbackFromPlan($pdo, (int) $abonnement_id);
                if (! $current_period_start && $cpsF) {
                    $current_period_start = $cpsF;
                }

                if (! $current_period_end && $cpeF) {
                    $current_period_end = $cpeF;
                }

            }
            // Pour un renouvellement, la période portée par la facture est souvent
            // la source la plus fiable pour prolonger l'accès.
            if ($invoice_period_start) {
                $current_period_start = $invoice_period_start;
            }
            if ($invoice_period_end) {
                $current_period_end = $invoice_period_end;
            }
            wh_log("BILL ev=$eventId $type inv=" . ($invoice_id ?: 'null') . " sub=" . ($sub_id ?: 'null') . " cust=" . ($cust_id ?: 'null') . " pid=" . ($praticien_id ?: 'null') . " aid=" . ($abonnement_id ?: 'null') . " cps=" . ($current_period_start ?: 'null') . " cpe=" . ($current_period_end ?: 'null') . " status=" . ($sub_status ?: ''));

            // Upsert billing_subscriptions (NE PAS écraser avec null)
            $upsertBilling([
                'praticien_id'             => $praticien_id,
                'abonnement_id'            => $abonnement_id,
                'billing_mode'             => 'auto',
                'provider'                 => 'stripe',
                'provider_customer_id'     => $cust_id,
                'provider_subscription_id' => $sub_id,
                'provider_plan_id'         => $provider_plan_id,
                'provider_product_id'      => $provider_product_id,
                'status'                   => $sub_status ?? (
                    $type === 'invoice.payment_failed'
                        ? 'payment_failed'
                        : ($type === 'invoice.payment_action_required' ? 'action_required' : 'paid')
                ),
                'current_period_start'     => $current_period_start,
                'current_period_end'       => $current_period_end,
                'cancel_at_period_end'     => (int) $cancel_at_period_end,
                'canceled_at'              => $canceled_at,
                'ended_at'                 => $ended_at,
                'last_event_id'            => $eventId,
                'last_invoice_id'          => $invoice_id,
            ]);
            if ($type === 'invoice.payment_action_required') {
                wh_log("ACTION_REQUIRED inv=" . ($invoice_id ?: 'null') . " sub=" . ($sub_id ?: 'null') . " cust=" . ($cust_id ?: 'null') . " pid=" . ($praticien_id ?: 'null'));
                http_response_code(200);
                echo 'ok-invoice-action-required';
                exit;
            }
            // invoice.paid => insert paiement + update praticien si pid/aid OK
            if ($type === 'invoice.paid') {
                if (! $praticien_id || ! $abonnement_id) {
                    wh_log('BILL_SKIP_PAYMENT_NO_PID inv=' . ($invoice_id ?: 'null') . ' sub=' . ($sub_id ?: 'null') . ' cust=' . ($cust_id ?: 'null'));
                    http_response_code(200);
                    echo 'ok-invoice-missing-meta';
                    exit;
                }

                // Idempotence par invoice_id
                if ($invoice_id && colExists($pdo, 'paiement', 'stripe_invoice_id')) {
                    $st = $pdo->prepare("SELECT 1 FROM paiement WHERE stripe_invoice_id = ? LIMIT 1");
                    $st->execute([(string) $invoice_id]);
                    if ((bool) $st->fetchColumn()) {
                        http_response_code(200);
                        echo 'ok-duplicate-invoice';
                        exit;
                    }
                }

                $prix   = is_numeric($amount_cent) ? ((int) $amount_cent) / 100.0 : 0.0;
                $statut = pickStatut($pdo, 'payé');

                $date_expiration = $invoice_period_end ?: $current_period_end;
                if (! $date_expiration) {
                    [$cpsF, $cpeF]   = periodFallbackFromPlan($pdo, (int) $abonnement_id);
                    $date_expiration = $cpeF ?: (new DateTime('now'))->modify("+30 days")->format('Y-m-d H:i:s');
                }

                $pdo->beginTransaction();

                $fields = [
                    'praticien_id'  => (int) $praticien_id,
                    'abonnement_id' => (int) $abonnement_id,
                    'montant'       => (float) $prix,
                ];
                if (colExists($pdo, 'paiement', 'methode')) {
                    $fields['methode'] = 'carte';
                }

                if (colExists($pdo, 'paiement', 'statut')) {
                    $fields['statut'] = $statut;
                }

                if ($invoice_id && colExists($pdo, 'paiement', 'stripe_invoice_id')) {
                    $fields['stripe_invoice_id'] = (string) $invoice_id;
                }

                if ($sub_id && colExists($pdo, 'paiement', 'stripe_sub_id')) {
                    $fields['stripe_sub_id'] = (string) $sub_id;
                }

                if ($eventId && colExists($pdo, 'paiement', 'stripe_event_id')) {
                    $fields['stripe_event_id'] = $eventId;
                }

                $cols    = array_keys($fields);
                $holders = array_fill(0, count($cols), '?');
                $vals    = array_values($fields);

                $sql = "INSERT INTO paiement (" . implode(',', $cols) . ") VALUES (" . implode(',', $holders) . ")";
                $pdo->prepare($sql)->execute($vals);

                if (colExists($pdo, 'praticien', 'date_expiration')) {
                    $pdo->prepare("UPDATE praticien SET abonnement_id = ?, date_expiration = ? WHERE id = ?")
                        ->execute([(int) $abonnement_id, (string) $date_expiration, (int) $praticien_id]);
                } elseif (colExists($pdo, 'praticien', 'abonnement_expire_le')) {
                    $pdo->prepare("UPDATE praticien SET abonnement_id = ?, abonnement_expire_le = ? WHERE id = ?")
                        ->execute([(int) $abonnement_id, (string) $date_expiration, (int) $praticien_id]);
                }

                $pdo->commit();

                http_response_code(200);
                echo 'ok-invoice-paid';
                exit;
            }

            http_response_code(200);
            echo($type === 'invoice.payment_failed') ? 'ok-invoice-failed' : 'ok-invoice';
            exit;
        }

        // ---------- customer.subscription.* ----------
        if ($type === 'customer.subscription.updated' || $type === 'customer.subscription.deleted') {
            /** @var \Stripe\Subscription $sub */
            $sub = $obj;

            $sub_id  = stripeId($sub->id ?? null);
            $cust_id = stripeId($sub->customer ?? null);
            $status  = $sub->status ?? null;

            $cancel_at_period_end = ! empty($sub->cancel_at_period_end) ? 1 : 0;
            $current_period_start = dtFromUnix($sub->current_period_start ?? null);
            $current_period_end   = dtFromUnix($sub->current_period_end ?? null);
            $canceled_at          = dtFromUnix($sub->canceled_at ?? null);
            $ended_at             = dtFromUnix($sub->ended_at ?? null);

            $md            = toArrayMeta($sub->metadata ?? null);
            $praticien_id  = isset($md['praticien_id']) ? (int) $md['praticien_id'] : null;
            $abonnement_id = isset($md['abonnement_id']) ? (int) $md['abonnement_id'] : null;

            $provider_plan_id    = null;
            $provider_product_id = null;
            try {
                if (isset($sub->items->data[0]->price->id)) {
                    $provider_plan_id = (string) $sub->items->data[0]->price->id;
                }

                if (isset($sub->items->data[0]->price->product)) {
                    $provider_product_id = stripeId($sub->items->data[0]->price->product);
                }

            } catch (Throwable $e) {}

            // fallback DB
            if (! $praticien_id || ! $abonnement_id || ! $provider_plan_id || ! $provider_product_id || ! $current_period_start || ! $current_period_end) {
                if ($sub_id) {
                    [$pid_db, $aid_db, $plan_db, $prod_db, $cps_db, $cpe_db] = $lookupLocalBySubId($sub_id);
                    if (! $praticien_id && $pid_db) {
                        $praticien_id = $pid_db;
                    }

                    if (! $abonnement_id && $aid_db) {
                        $abonnement_id = $aid_db;
                    }

                    if (! $provider_plan_id && $plan_db) {
                        $provider_plan_id = $plan_db;
                    }

                    if (! $provider_product_id && $prod_db) {
                        $provider_product_id = $prod_db;
                    }

                    if (! $current_period_start && $cps_db) {
                        $current_period_start = $cps_db;
                    }

                    if (! $current_period_end && $cpe_db) {
                        $current_period_end = $cpe_db;
                    }

                }
                if ($cust_id && (! $sub_id || ! $praticien_id)) {
                    [$pid_db, $aid_db, $sub_db, $plan_db, $prod_db, $cps_db, $cpe_db] = $lookupLocalByCustomerId($cust_id);
                    if (! $praticien_id && $pid_db) {
                        $praticien_id = $pid_db;
                    }

                    if (! $abonnement_id && $aid_db) {
                        $abonnement_id = $aid_db;
                    }

                    if (! $sub_id && $sub_db) {
                        $sub_id = $sub_db;
                    }

                    if (! $provider_plan_id && $plan_db) {
                        $provider_plan_id = $plan_db;
                    }

                    if (! $provider_product_id && $prod_db) {
                        $provider_product_id = $prod_db;
                    }

                    if (! $current_period_start && $cps_db) {
                        $current_period_start = $cps_db;
                    }

                    if (! $current_period_end && $cpe_db) {
                        $current_period_end = $cpe_db;
                    }

                }
            }

            // fallback périodes dernier recours
            if ((! $current_period_start || ! $current_period_end) && $abonnement_id) {
                [$cpsF, $cpeF] = periodFallbackFromPlan($pdo, (int) $abonnement_id);
                if (! $current_period_start && $cpsF) {
                    $current_period_start = $cpsF;
                }

                if (! $current_period_end && $cpeF) {
                    $current_period_end = $cpeF;
                }

            }

            wh_log("SUB ev=$eventId $type sub=" . ($sub_id ?: 'null') . " cust=" . ($cust_id ?: 'null') . " pid=" . ($praticien_id ?: 'null') . " aid=" . ($abonnement_id ?: 'null') . " cps=" . ($current_period_start ?: 'null') . " cpe=" . ($current_period_end ?: 'null') . " status=" . ((string) ($status ?? '')));

            $upsertBilling([
                'praticien_id'             => $praticien_id,
                'abonnement_id'            => $abonnement_id,
                'billing_mode'             => 'auto',
                'provider'                 => 'stripe',
                'provider_customer_id'     => $cust_id,
                'provider_subscription_id' => $sub_id,
                'provider_plan_id'         => $provider_plan_id,
                'provider_product_id'      => $provider_product_id,
                'status'                   => $status ?? ($type === 'customer.subscription.deleted' ? 'canceled' : 'updated'),
                'current_period_start'     => $current_period_start,
                'current_period_end'       => $current_period_end,
                'cancel_at_period_end'     => (int) $cancel_at_period_end,
                'canceled_at'              => $canceled_at,
                'ended_at'                 => $ended_at,
                'last_event_id'            => $eventId,
            ]);

            // IMPORTANT:
            // Ne pas modifier praticien.date_expiration ici.
            // Seul invoice.paid doit prolonger l'accès effectif.

            http_response_code(200);
            echo 'ok-sub';
            exit;
        }

    } catch (Throwable $e) {
        if (isset($pdo) && $pdo instanceof PDO && $pdo->inTransaction()) {
            $pdo->rollBack();
        }

        $errInfo = ($e instanceof PDOException && isset($e->errorInfo)) ? json_encode($e->errorInfo, JSON_UNESCAPED_UNICODE) : '';
        wh_log('ERR BILLING ev=' . $eventId . ' type=' . get_class($e) . ' msg=' . $e->getMessage() . ' file=' . $e->getFile() . ':' . $e->getLine() . ' info=' . $errInfo);
        http_response_code(500);
        echo 'error';
        exit;
    }
}

// Autres events
http_response_code(200);
echo 'ignored';