<?php
// ============================================================
//  Member journey helpers
// ============================================================

/**
 * Ensure every step in the member's tier template has a progress row.
 * Creates missing rows with status = 'not_started'.
 */
function journey_ensure_progress(int $member_id, string $tier): void {
    // Find all active steps for this tier
    $steps = db_all(
        'SELECT id FROM journey_steps WHERE tier=:t AND active=1',
        ['t' => $tier]
    );
    foreach ($steps as $s) {
        db_exec(
            'INSERT IGNORE INTO member_journey (member_id, step_id, status)
             VALUES (:m, :s, \'not_started\')',
            ['m' => $member_id, 's' => $s['id']]
        );
    }
}

/**
 * Load the member's journey (steps for their tier with their progress).
 * Returns array of rows: id (step id), name, description, sort_order,
 * status, started_at, completed_at, notes, progress_id.
 */
function journey_load(int $member_id, string $tier): array {
    journey_ensure_progress($member_id, $tier);
    return db_all(
        'SELECT js.id, js.name, js.description, js.sort_order,
                COALESCE(mj.status, \'not_started\') AS status,
                mj.started_at, mj.completed_at, mj.notes, mj.id AS progress_id
           FROM journey_steps js
           LEFT JOIN member_journey mj
             ON mj.step_id = js.id AND mj.member_id = :m
           WHERE js.tier = :t AND js.active = 1
           ORDER BY js.sort_order, js.id',
        ['m' => $member_id, 't' => $tier]
    );
}

/**
 * Update a member's progress for a specific step.
 * status must be one of: not_started | in_progress | done
 */
function journey_update_progress(int $member_id, int $step_id, string $status, ?string $notes = null): void {
    if (!in_array($status, ['not_started','in_progress','done'], true)) return;

    $started   = ($status === 'in_progress' || $status === 'done') ? 'NOW()' : 'NULL';
    $completed = ($status === 'done') ? 'NOW()' : 'NULL';

    // Insert-or-update
    $existing = db_row(
        'SELECT id, started_at FROM member_journey WHERE member_id=:m AND step_id=:s',
        ['m' => $member_id, 's' => $step_id]
    );

    if ($existing) {
        // Keep original started_at if already set and we're moving to done
        $keep_started = !empty($existing['started_at']) && $status !== 'not_started';
        $sql = "UPDATE member_journey
                   SET status = :st,
                       started_at   = " . ($keep_started ? 'started_at' : ($status === 'not_started' ? 'NULL' : 'NOW()')) . ",
                       completed_at = " . ($status === 'done' ? 'NOW()' : 'NULL') . "
               " . ($notes !== null ? ', notes = :notes' : '') . "
                 WHERE id = :id";
        $params = ['st' => $status, 'id' => $existing['id']];
        if ($notes !== null) $params['notes'] = $notes;
        db_exec($sql, $params);
    } else {
        $sql = "INSERT INTO member_journey (member_id, step_id, status, started_at, completed_at, notes)
                VALUES (:m, :s, :st,
                        " . ($status !== 'not_started' ? 'NOW()' : 'NULL') . ",
                        " . ($status === 'done' ? 'NOW()' : 'NULL') . ",
                        :notes)";
        db_exec($sql, [
            'm' => $member_id, 's' => $step_id, 'st' => $status,
            'notes' => $notes
        ]);
    }
}

/**
 * Compute progress percentage for a list of step rows.
 * "done" = 100%, "in_progress" = 50%, "not_started" = 0%, then averaged.
 */
function journey_percent(array $rows): int {
    if (empty($rows)) return 0;
    $total = 0;
    foreach ($rows as $r) {
        if ($r['status'] === 'done')        $total += 100;
        elseif ($r['status'] === 'in_progress') $total += 50;
    }
    return (int)round($total / count($rows));
}

/**
 * Short counts: [done, in_progress, not_started]
 */
function journey_counts(array $rows): array {
    $counts = ['done'=>0, 'in_progress'=>0, 'not_started'=>0];
    foreach ($rows as $r) {
        $k = $r['status'] ?? 'not_started';
        if (isset($counts[$k])) $counts[$k]++;
    }
    return $counts;
}

/**
 * Bulk: load progress summaries for many members in one query.
 * Returns map: member_id => ['percent'=>int, 'done'=>int, 'total'=>int, 'in_progress'=>int].
 */
function journey_summaries_for_members(array $member_ids): array {
    if (empty($member_ids)) return [];

    // 1) How many steps each member's tier has
    $placeholders = implode(',', array_fill(0, count($member_ids), '?'));
    $pdo = db();

    $stmt = $pdo->prepare("
        SELECT m.id, m.tier,
               (SELECT COUNT(*) FROM journey_steps js
                  WHERE js.tier = m.tier AND js.active = 1) AS total_steps
          FROM members m
         WHERE m.id IN ($placeholders)
    ");
    $stmt->execute(array_values($member_ids));
    $tier_data = $stmt->fetchAll();

    // 2) Progress counts
    $stmt2 = $pdo->prepare("
        SELECT mj.member_id, mj.status, COUNT(*) AS c
          FROM member_journey mj
          JOIN journey_steps js ON js.id = mj.step_id AND js.active = 1
         WHERE mj.member_id IN ($placeholders)
         GROUP BY mj.member_id, mj.status
    ");
    $stmt2->execute(array_values($member_ids));
    $progress = $stmt2->fetchAll();

    $by_member = [];
    foreach ($tier_data as $t) {
        $by_member[(int)$t['id']] = [
            'total'       => (int)$t['total_steps'],
            'done'        => 0,
            'in_progress' => 0,
            'not_started' => 0,
            'tier'        => $t['tier'],
        ];
    }
    foreach ($progress as $p) {
        $mid = (int)$p['member_id'];
        if (isset($by_member[$mid][$p['status']])) {
            $by_member[$mid][$p['status']] = (int)$p['c'];
        }
    }
    foreach ($by_member as &$row) {
        $total = max(1, $row['total']);
        $row['percent'] = (int)round(
            ($row['done'] * 100 + $row['in_progress'] * 50) / $total
        );
    }
    return $by_member;
}