<?php
/**
 * Questionnaire submission versioning (archive each coach upload).
 */

require_once __DIR__ . '/questionnaire_structure.php';

/**
 * Build display columns from a stored structure manifest (historical submissions).
 *
 * @return array{questions: array, resultColumns: array, optionTextMap: array, stringLabelCache: array}
 */
function qdb_display_context_from_manifest(PDO $pdo, array $manifest): array {
    $stringLabelCache = [];
    $resultColumns = [];
    $questions = [];
    $optionTextMap = [];

    foreach ($manifest['questions'] ?? [] as $qEntry) {
        if (!is_array($qEntry)) {
            continue;
        }
        $fullId = (string)($qEntry['questionID'] ?? '');
        if ($fullId === '') {
            continue;
        }

        $qStmt = $pdo->prepare(
            'SELECT questionID, defaultText, type, orderIndex, configJson FROM question WHERE questionID = :id'
        );
        $qStmt->execute([':id' => $fullId]);
        $qRow = $qStmt->fetch(PDO::FETCH_ASSOC);
        if (!$qRow) {
            $symptoms = $qEntry['symptoms'] ?? [];
            $qRow = [
                'questionID' => $fullId,
                'defaultText' => (string)($qEntry['questionKey'] ?? $qEntry['shortId'] ?? $fullId),
                'type' => (string)($qEntry['type'] ?? ''),
                'orderIndex' => 0,
                'configJson' => json_encode(
                    ['symptoms' => is_array($symptoms) ? $symptoms : []],
                    JSON_UNESCAPED_UNICODE
                ),
            ];
        }
        $questions[] = $qRow;

        $cfg = json_decode($qRow['configJson'] ?? '{}', true) ?: [];
        $type = $qRow['type'] ?? '';
        if (qdb_is_non_stored_answer_question_type($type)) {
            continue;
        }
        if ($type === 'glass_scale_question') {
            $symptoms = $cfg['symptoms'] ?? $qEntry['symptoms'] ?? [];
            if (is_array($symptoms) && $symptoms !== []) {
                foreach ($symptoms as $symptomKey) {
                    $sk = trim((string)$symptomKey);
                    if ($sk === '') {
                        continue;
                    }
                    $resultColumns[] = [
                        'header'     => qdb_string_german_label($pdo, $sk, $stringLabelCache),
                        'questionID' => $fullId,
                        'symptomKey' => $sk,
                        'kind'       => 'glass_symptom',
                        'question'   => $qRow,
                    ];
                }
                continue;
            }
        }

        $resultColumns[] = [
            'header'     => qdb_question_german_label($pdo, $qRow),
            'questionID' => $fullId,
            'symptomKey' => null,
            'kind'       => 'question',
            'question'   => $qRow,
        ];

        $aoStmt = $pdo->prepare('SELECT answerOptionID, defaultText FROM answer_option WHERE questionID = :qid');
        $aoStmt->execute([':qid' => $fullId]);
        $dbOptions = $aoStmt->fetchAll(PDO::FETCH_ASSOC);
        if ($dbOptions !== []) {
            foreach ($dbOptions as $ao) {
                $optionTextMap[$ao['answerOptionID']] = qdb_option_german_label(
                    $pdo,
                    $ao['answerOptionID'],
                    $ao['defaultText']
                );
            }
        } else {
            foreach ($qEntry['options'] ?? [] as $opt) {
                if (!is_array($opt)) {
                    continue;
                }
                $key = (string)($opt['key'] ?? '');
                if ($key !== '') {
                    $optionTextMap[$key] = $key;
                }
            }
        }
    }

    return [
        'questions'        => $questions,
        'resultColumns'    => $resultColumns,
        'optionTextMap'    => $optionTextMap,
        'stringLabelCache' => $stringLabelCache,
    ];
}

function qdb_next_submission_version(PDO $pdo, string $clientCode, string $questionnaireID): int {
    $stmt = $pdo->prepare(
        'SELECT COALESCE(MAX(version), 0) + 1 FROM questionnaire_submission
         WHERE clientCode = :cc AND questionnaireID = :qn'
    );
    $stmt->execute([':cc' => $clientCode, ':qn' => $questionnaireID]);
    return (int)$stmt->fetchColumn();
}

/**
 * Snapshot live answers + completion metadata after a successful submit.
 */
function qdb_record_submission_after_submit(
    PDO $pdo,
    string $clientCode,
    string $questionnaireID,
    array $tokenRec,
    ?int $startedAt,
    ?int $completedAt,
    int $sumPoints,
    string $assignedByCoach,
    int $structureRevision = 1,
    ?array $structureManifest = null,
    ?array $questionIdsToCopy = null,
    ?int $submittedAt = null,
    ?int $programCycleNumber = null,
    ?string $programSessionID = null,
    ?int $programSessionNumber = null
): string {
    $version = qdb_next_submission_version($pdo, $clientCode, $questionnaireID);
    $submissionID = bin2hex(random_bytes(16));
    $submittedAtValue = $submittedAt ?? $completedAt ?? time();
    $snapshotJson = '{}';
    if ($structureManifest !== null) {
        $encoded = json_encode($structureManifest, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        if ($encoded !== false) {
            $snapshotJson = $encoded;
        }
    }

    $cq = $pdo->prepare(
        'SELECT status FROM completed_questionnaire WHERE clientCode = :cc AND questionnaireID = :qn'
    );
    $cq->execute([':cc' => $clientCode, ':qn' => $questionnaireID]);
    $status = $cq->fetchColumn() ?: 'completed';

    $pdo->prepare(
        'INSERT INTO questionnaire_submission (
            submissionID, clientCode, questionnaireID, version, submittedAt,
            submittedByUserID, submittedByRole, assignedByCoach,
            status, startedAt, completedAt, sumPoints,
            programCycleNumber, programSessionID, programSessionNumber,
            structureRevision, structureSnapshotJson
        ) VALUES (
            :sid, :cc, :qn, :ver, :sat,
            :uid, :role, :abc,
            :st, :sa, :ca, :sp,
            :pcn, :psid, :psn,
            :srev, :snap
        )'
    )->execute([
        ':sid'  => $submissionID,
        ':cc'   => $clientCode,
        ':qn'   => $questionnaireID,
        ':ver'  => $version,
        ':sat'  => $submittedAtValue,
        ':uid'  => $tokenRec['userID'] ?? '',
        ':role' => $tokenRec['role'] ?? '',
        ':abc'  => $assignedByCoach !== '' ? $assignedByCoach : null,
        ':st'   => $status,
        ':sa'   => $startedAt,
        ':ca'   => $completedAt,
        ':sp'   => $sumPoints,
        ':pcn'  => $programCycleNumber,
        ':psid' => $programSessionID,
        ':psn'  => $programSessionNumber,
        ':srev' => max(1, $structureRevision),
        ':snap' => $snapshotJson,
    ]);

    if ($questionIdsToCopy !== null && $questionIdsToCopy !== []) {
        $questionIdsToCopy = array_values(array_unique(array_filter($questionIdsToCopy, 'is_string')));
        $ph = implode(',', array_fill(0, count($questionIdsToCopy), '?'));
        $copy = $pdo->prepare(
            "INSERT INTO client_answer_submission (submissionID, questionID, answerOptionID, freeTextValue, numericValue, answeredAt)
             SELECT ?, questionID, answerOptionID, freeTextValue, numericValue, answeredAt
             FROM client_answer
             WHERE clientCode = ? AND questionID IN ($ph)"
        );
        $copy->execute(array_merge([$submissionID, $clientCode], $questionIdsToCopy));
    } else {
        $copy = $pdo->prepare(
            'INSERT INTO client_answer_submission (submissionID, questionID, answerOptionID, freeTextValue, numericValue, answeredAt)
             SELECT :sid, questionID, answerOptionID, freeTextValue, numericValue, answeredAt
             FROM client_answer
             WHERE clientCode = :cc AND questionID IN (
                 SELECT questionID FROM question WHERE questionnaireID = :qn
             )'
        );
        $copy->execute([':sid' => $submissionID, ':cc' => $clientCode, ':qn' => $questionnaireID]);
    }

    return $submissionID;
}

/**
 * One-time: create version 1 submissions for existing completions without archive rows.
 */
function qdb_backfill_submissions_from_live(PDO $pdo): bool {
    $count = (int)$pdo->query('SELECT COUNT(*) FROM questionnaire_submission')->fetchColumn();
    if ($count > 0) {
        return false;
    }

    $rows = $pdo->query(
        'SELECT clientCode, questionnaireID, assignedByCoach, status, startedAt, completedAt, sumPoints
         FROM completed_questionnaire'
    )->fetchAll(PDO::FETCH_ASSOC);

    if (!$rows) {
        return false;
    }

    $pdo->beginTransaction();
    try {
        foreach ($rows as $cq) {
            $clientCode = $cq['clientCode'];
            $qnID = $cq['questionnaireID'];
            $chk = $pdo->prepare(
                'SELECT 1 FROM client_answer ca
                 JOIN question q ON q.questionID = ca.questionID
                 WHERE ca.clientCode = :cc AND q.questionnaireID = :qn LIMIT 1'
            );
            $chk->execute([':cc' => $clientCode, ':qn' => $qnID]);
            if (!$chk->fetchColumn()) {
                continue;
            }

            $submissionID = bin2hex(random_bytes(16));
            $completedAt = $cq['completedAt'] ?? time();
            $pdo->prepare(
                'INSERT INTO questionnaire_submission (
                    submissionID, clientCode, questionnaireID, version, submittedAt,
                    submittedByUserID, submittedByRole, assignedByCoach,
                    status, startedAt, completedAt, sumPoints
                ) VALUES (
                    :sid, :cc, :qn, 1, :sat,
                    \'\', \'\', :abc,
                    :st, :sa, :ca, :sp
                )'
            )->execute([
                ':sid' => $submissionID,
                ':cc'  => $clientCode,
                ':qn'  => $qnID,
                ':sat' => $completedAt,
                ':abc' => $cq['assignedByCoach'],
                ':st'  => $cq['status'] ?: 'completed',
                ':sa'  => $cq['startedAt'],
                ':ca'  => $cq['completedAt'],
                ':sp'  => $cq['sumPoints'],
            ]);

            $pdo->prepare(
                'INSERT INTO client_answer_submission (submissionID, questionID, answerOptionID, freeTextValue, numericValue, answeredAt)
                 SELECT :sid, questionID, answerOptionID, freeTextValue, numericValue, answeredAt
                 FROM client_answer
                 WHERE clientCode = :cc AND questionID IN (
                     SELECT questionID FROM question WHERE questionnaireID = :qn
                 )'
            )->execute([':sid' => $submissionID, ':cc' => $clientCode, ':qn' => $qnID]);
        }
        $pdo->commit();
        return true;
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
        throw $e;
    }
}

/**
 * RBAC-scoped submission rows for one questionnaire (client, then version).
 *
 * @return list<array<string, mixed>>
 */
function qdb_fetch_questionnaire_submissions_scoped(
    PDO $pdo,
    string $questionnaireID,
    array $tokenRec
): array {
    [$rbacClause, $rbacParams] = rbac_client_filter($tokenRec, 'cl');

    $sql = "
        SELECT qs.submissionID, qs.version, qs.submittedAt, qs.submittedByUserID, qs.submittedByRole,
               qs.completedAt AS submissionCompletedAt, qs.sumPoints AS submissionSumPoints,
               qs.startedAt AS submissionStartedAt, qs.status AS submissionStatus,
               qs.structureRevision, qs.structureSnapshotJson,
               qs.programCycleNumber, qs.programSessionID, qs.programSessionNumber,
               cl.clientCode, cl.coachID,
               co.username AS coachUsername,
               sv.username AS supervisorUsername
        FROM questionnaire_submission qs
        INNER JOIN client cl ON cl.clientCode = qs.clientCode
        LEFT JOIN coach co ON co.coachID = cl.coachID
        LEFT JOIN supervisor sv ON sv.supervisorID = co.supervisorID
        WHERE qs.questionnaireID = :qnid AND ($rbacClause)
        ORDER BY cl.clientCode ASC, qs.version ASC
    ";
    $params = array_merge([':qnid' => $questionnaireID], $rbacParams);
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);

    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

/** @return array<string, string> */
function qdb_submission_submitter_username_map(PDO $pdo): array {
    $userMap = [];
    $userStmt = $pdo->query('SELECT userID, username FROM users');
    foreach ($userStmt->fetchAll(PDO::FETCH_ASSOC) as $u) {
        $userMap[$u['userID']] = $u['username'];
    }
    return $userMap;
}

/**
 * Resolve per-submission question columns from structure snapshot or questionnaire defaults.
 *
 * @return array{submissionColumns: array, submissionOptionMap: array, submissionQuestionIDs: list<string>}
 */
function qdb_submission_display_context(
    PDO $pdo,
    array $submissionRow,
    array $defaultQuestions,
    array $defaultResultColumns,
    array $defaultOptionTextMap
): array {
    $snap = json_decode($submissionRow['structureSnapshotJson'] ?? '{}', true) ?: [];
    if (!empty($snap['questions'])) {
        $snapCtx = qdb_display_context_from_manifest($pdo, $snap);
        return [
            'submissionColumns'     => $snapCtx['resultColumns'],
            'submissionOptionMap'   => $snapCtx['optionTextMap'],
            'submissionQuestionIDs' => array_column($snapCtx['questions'], 'questionID'),
        ];
    }
    return [
        'submissionColumns'     => $defaultResultColumns,
        'submissionOptionMap'   => $defaultOptionTextMap,
        'submissionQuestionIDs' => array_column($defaultQuestions, 'questionID'),
    ];
}

/**
 * Build CSV rows for all submission versions of one questionnaire (RBAC-scoped).
 *
 * @return list<array<string, string>>
 */
function qdb_export_all_versions_rows(
    PDO $pdo,
    string $questionnaireID,
    array $questions,
    array $resultColumns,
    array $optionTextMap,
    array $tokenRec
): array {
    $submissions = qdb_fetch_questionnaire_submissions_scoped($pdo, $questionnaireID, $tokenRec);
    $userMap = qdb_submission_submitter_username_map($pdo);

    $rows = [];
    foreach ($submissions as $s) {
        $ctx = qdb_submission_display_context($pdo, $s, $questions, $resultColumns, $optionTextMap);

        $row = [
            'clientCode'           => $s['clientCode'],
            'version'              => (string)(int)$s['version'],
            'programCycleNumber'   => $s['programCycleNumber'] !== null ? (string)(int)$s['programCycleNumber'] : '',
            'programSessionNumber' => $s['programSessionNumber'] !== null ? (string)(int)$s['programSessionNumber'] : '',
            'programSessionID'     => $s['programSessionID'] ?? '',
            'startedAt'            => $s['submissionStartedAt'] ? date('Y-m-d H:i', (int)$s['submissionStartedAt']) : '',
            'completedAt'          => $s['submissionCompletedAt'] ? date('Y-m-d H:i', (int)$s['submissionCompletedAt']) : '',
            'submittedAt'          => $s['submittedAt'] ? date('Y-m-d H:i', (int)$s['submittedAt']) : '',
            'coach'                => $s['coachUsername'] ?? $s['coachID'],
            'supervisor'           => $s['supervisorUsername'] ?? '',
            'status'               => $s['submissionStatus'] ?? '',
            'sumPoints'            => $s['submissionSumPoints'] ?? '',
            'submissionID'         => $s['submissionID'],
            'structureRevision'    => (string)max(1, (int)($s['structureRevision'] ?? 1)),
            'submittedByRole'      => $s['submittedByRole'] ?? '',
            'submittedBy'          => $userMap[$s['submittedByUserID'] ?? ''] ?? ($s['submittedByUserID'] ?? ''),
        ];

        $answerMap = qdb_load_client_answer_map(
            $pdo,
            $s['clientCode'],
            $ctx['submissionQuestionIDs'],
            'client_answer_submission',
            $s['submissionID']
        );

        foreach ($ctx['submissionColumns'] as $col) {
            $qid = $col['questionID'];
            $a = $answerMap[$qid] ?? null;
            $row[$col['header']] = qdb_results_column_cell_value($col, $a, $ctx['submissionOptionMap']);
        }
        $rows[] = $row;
    }

    return $rows;
}

/**
 * Submission rows for the results API (all versions, grouped by client in sort order).
 *
 * @return list<array<string, mixed>>
 */
function qdb_results_submission_entries(
    PDO $pdo,
    string $questionnaireID,
    array $questions,
    array $resultColumns,
    array $optionTextMap,
    array $tokenRec
): array {
    $submissions = qdb_fetch_questionnaire_submissions_scoped($pdo, $questionnaireID, $tokenRec);
    $userMap = qdb_submission_submitter_username_map($pdo);
    $entries = [];

    foreach ($submissions as $s) {
        $ctx = qdb_submission_display_context($pdo, $s, $questions, $resultColumns, $optionTextMap);
        $answerMap = qdb_load_client_answer_map(
            $pdo,
            $s['clientCode'],
            $ctx['submissionQuestionIDs'],
            'client_answer_submission',
            $s['submissionID']
        );

        $answers = [];
        foreach ($answerMap as $qid => $a) {
            $answers[$qid] = [
                'answerOptionID' => $a['answerOptionID'],
                'freeTextValue'  => $a['freeTextValue'],
                'numericValue'   => $a['numericValue'] !== null ? (float)$a['numericValue'] : null,
                'answeredAt'     => null,
            ];
        }

        $entries[] = [
            'submissionID'         => $s['submissionID'],
            'version'              => (int)$s['version'],
            'clientCode'           => $s['clientCode'],
            'coachID'              => $s['coachID'],
            'coachUsername'        => $s['coachUsername'],
            'supervisorUsername'   => $s['supervisorUsername'],
            'status'               => $s['submissionStatus'] ?? '',
            'sumPoints'            => $s['submissionSumPoints'] !== null ? (int)$s['submissionSumPoints'] : null,
            'startedAt'            => $s['submissionStartedAt'] !== null ? (int)$s['submissionStartedAt'] : null,
            'completedAt'          => $s['submissionCompletedAt'] !== null ? (int)$s['submissionCompletedAt'] : null,
            'submittedAt'          => $s['submittedAt'] !== null ? (int)$s['submittedAt'] : null,
            'programCycleNumber'   => $s['programCycleNumber'] !== null ? (int)$s['programCycleNumber'] : null,
            'programSessionID'     => $s['programSessionID'],
            'programSessionNumber' => $s['programSessionNumber'] !== null ? (int)$s['programSessionNumber'] : null,
            'structureRevision'    => max(1, (int)($s['structureRevision'] ?? 1)),
            'submittedByRole'      => $s['submittedByRole'] ?? '',
            'submittedBy'          => $userMap[$s['submittedByUserID'] ?? ''] ?? ($s['submittedByUserID'] ?? ''),
            'answers'              => $answers,
        ];
    }

    return $entries;
}

/**
 * @return array{questions: array, resultColumns: array, optionTextMap: array}
 */
function qdb_export_questionnaire_context(PDO $pdo, string $questionnaireID): array {
    $qStmt = $pdo->prepare(
        'SELECT questionID, defaultText, type, orderIndex, configJson FROM question WHERE questionnaireID = :id ORDER BY orderIndex'
    );
    $qStmt->execute([':id' => $questionnaireID]);
    $questions = $qStmt->fetchAll(PDO::FETCH_ASSOC);
    $questionIDs = array_column($questions, 'questionID');
    $resultColumns = qdb_results_export_columns($questions, $questionnaireID);

    $optionTextMap = [];
    foreach ($questionIDs as $qid) {
        $aoStmt = $pdo->prepare('SELECT answerOptionID, defaultText FROM answer_option WHERE questionID = :qid');
        $aoStmt->execute([':qid' => $qid]);
        foreach ($aoStmt->fetchAll(PDO::FETCH_ASSOC) as $ao) {
            $optionTextMap[$ao['answerOptionID']] = $ao['defaultText'];
        }
    }

    return [
        'questions'      => $questions,
        'resultColumns'  => $resultColumns,
        'optionTextMap'  => $optionTextMap,
    ];
}

/**
 * Current (live) response rows for one questionnaire, RBAC-scoped.
 *
 * @return list<array<string, string>>
 */
function qdb_export_current_rows(
    PDO $pdo,
    string $questionnaireID,
    array $questions,
    array $resultColumns,
    array $optionTextMap,
    array $tokenRec
): array {
    [$rbacClause, $rbacParams] = rbac_client_filter($tokenRec, 'cl');
    $questionIDs = array_column($questions, 'questionID');

    $sql = "
        SELECT cl.clientCode, cl.coachID,
               co.username AS coachUsername,
               sv.username AS supervisorUsername,
               cq.status, cq.sumPoints, cq.startedAt, cq.completedAt,
               cq.programCycleNumber, cq.programSessionID, cq.programSessionNumber
        FROM client cl
        INNER JOIN completed_questionnaire cq ON cq.clientCode = cl.clientCode AND cq.questionnaireID = :qnid
        LEFT JOIN coach co ON co.coachID = cl.coachID
        LEFT JOIN supervisor sv ON sv.supervisorID = co.supervisorID
        WHERE $rbacClause
        ORDER BY cl.clientCode
    ";
    $params = array_merge([':qnid' => $questionnaireID], $rbacParams);
    $cStmt = $pdo->prepare($sql);
    $cStmt->execute($params);
    $clients = $cStmt->fetchAll(PDO::FETCH_ASSOC);

    $qPlaceholders = !empty($questionIDs) ? implode(',', array_fill(0, count($questionIDs), '?')) : "'__none__'";
    $answerStmt = $pdo->prepare("
        SELECT questionID, answerOptionID, freeTextValue, numericValue
        FROM client_answer
        WHERE clientCode = ? AND questionID IN ($qPlaceholders)
    ");

    $rows = [];
    foreach ($clients as $c) {
        $row = [
            'clientCode'           => $c['clientCode'],
            'programCycleNumber'   => $c['programCycleNumber'] !== null ? (string)(int)$c['programCycleNumber'] : '',
            'programSessionNumber' => $c['programSessionNumber'] !== null ? (string)(int)$c['programSessionNumber'] : '',
            'programSessionID'     => $c['programSessionID'] ?? '',
            'startedAt'            => $c['startedAt'] ? date('Y-m-d H:i', (int)$c['startedAt']) : '',
            'completedAt'          => $c['completedAt'] ? date('Y-m-d H:i', (int)$c['completedAt']) : '',
            'coach'                => $c['coachUsername'] ?? $c['coachID'],
            'supervisor'           => $c['supervisorUsername'] ?? '',
            'status'               => $c['status'],
            'sumPoints'            => $c['sumPoints'],
        ];

        $bindParams = array_merge([$c['clientCode']], $questionIDs);
        $answerStmt->execute($bindParams);
        $answerMap = [];
        foreach ($answerStmt->fetchAll(PDO::FETCH_ASSOC) as $a) {
            $answerMap[$a['questionID']] = $a;
        }

        foreach ($resultColumns as $col) {
            $qid = $col['questionID'];
            $a = $answerMap[$qid] ?? null;
            $row[$col['header']] = qdb_results_column_cell_value($col, $a, $optionTextMap);
        }
        $rows[] = $row;
    }

    return $rows;
}

function qdb_export_safe_basename(string $name): string {
    $safe = preg_replace('/[^a-zA-Z0-9_-]+/', '_', $name);
    return $safe !== '' ? $safe : 'questionnaire';
}

/** @param array<string, true> $used */
function qdb_export_unique_zip_name(array &$used, string $base): string {
    $name = $base;
    $n = 2;
    while (isset($used[$name])) {
        $name = preg_replace('/(\.csv)$/i', "_{$n}$1", $base, 1, $count);
        if (!$count) {
            $name = $base . '_' . $n;
        }
        $n++;
    }
    $used[$name] = true;
    return $name;
}

/**
 * @param list<array<string, string>> $rows
 * @param list<string> $fallbackHeader
 */
function qdb_export_rows_to_csv_string(array $rows, array $fallbackHeader = []): string {
    $fp = fopen('php://temp', 'r+');
    if ($fp === false) {
        return '';
    }
    fwrite($fp, "\xEF\xBB\xBF");
    if (!empty($rows)) {
        fputcsv($fp, array_keys($rows[0]), ',', '"', '\\');
        foreach ($rows as $row) {
            fputcsv($fp, array_values($row), ',', '"', '\\');
        }
    } elseif ($fallbackHeader !== []) {
        fputcsv($fp, $fallbackHeader, ',', '"', '\\');
    }
    rewind($fp);
    $csv = stream_get_contents($fp);
    fclose($fp);
    return $csv !== false ? $csv : '';
}

function qdb_export_current_csv_fallback_header(array $resultColumns): array {
    $header = [
        'clientCode', 'programCycleNumber', 'programSessionNumber', 'programSessionID',
        'startedAt', 'completedAt', 'coach', 'supervisor', 'status', 'sumPoints',
    ];
    foreach ($resultColumns as $col) {
        $header[] = $col['header'];
    }
    return $header;
}

function qdb_export_all_versions_csv_fallback_header(array $resultColumns): array {
    $header = [
        'clientCode', 'version', 'programCycleNumber', 'programSessionNumber', 'programSessionID',
        'startedAt', 'completedAt', 'submittedAt',
        'coach', 'supervisor', 'status', 'sumPoints',
        'submissionID', 'structureRevision', 'submittedByRole', 'submittedBy',
    ];
    foreach ($resultColumns as $col) {
        $header[] = $col['header'];
    }
    return $header;
}

/**
 * Build a ZIP of CSV exports for every questionnaire. Returns path to a temp file (caller must unlink).
 */
function qdb_build_server_export_zip(PDO $pdo, array $tokenRec, bool $allVersions): string {
    if (!class_exists('ZipArchive')) {
        json_error('SERVER_ERROR', 'ZIP export is not available on this server', 500);
    }

    $qnRows = $pdo->query(
        'SELECT questionnaireID, name, version FROM questionnaire ORDER BY orderIndex, name'
    )->fetchAll(PDO::FETCH_ASSOC);

    $tmpZip = tempnam(sys_get_temp_dir(), 'qdb_export_');
    if ($tmpZip === false) {
        json_error('SERVER_ERROR', 'Could not create export file', 500);
    }

    $zip = new ZipArchive();
    if ($zip->open($tmpZip, ZipArchive::OVERWRITE) !== true) {
        @unlink($tmpZip);
        json_error('SERVER_ERROR', 'Could not create ZIP archive', 500);
    }

    $usedNames = [];
    foreach ($qnRows as $qn) {
        $qnID = $qn['questionnaireID'];
        $ctx = qdb_export_questionnaire_context($pdo, $qnID);
        $questions = $ctx['questions'];
        $resultColumns = $ctx['resultColumns'];
        $optionTextMap = $ctx['optionTextMap'];

        if ($allVersions) {
            $rows = qdb_export_all_versions_rows($pdo, $qnID, $questions, $resultColumns, $optionTextMap, $tokenRec);
            $fallback = qdb_export_all_versions_csv_fallback_header($resultColumns);
            $base = qdb_export_safe_basename($qn['name']) . '_all_versions.csv';
        } else {
            $rows = qdb_export_current_rows($pdo, $qnID, $questions, $resultColumns, $optionTextMap, $tokenRec);
            $fallback = qdb_export_current_csv_fallback_header($resultColumns);
            $ver = preg_replace('/[^a-zA-Z0-9_.-]+/', '_', (string)($qn['version'] ?? ''));
            $base = qdb_export_safe_basename($qn['name']) . ($ver !== '' ? "_v{$ver}" : '') . '.csv';
        }

        $entryName = qdb_export_unique_zip_name($usedNames, $base);
        $csv = qdb_export_rows_to_csv_string($rows, $fallback);
        $zip->addFromString($entryName, $csv);
    }

    $readme = $allVersions
        ? "NAT-AS Ressourcenbarometer — all questionnaire response exports (every upload version).\nOne CSV per questionnaire.\n"
        : "NAT-AS Ressourcenbarometer — all questionnaire response exports (current data).\nOne CSV per questionnaire.\n";
    $zip->addFromString('README.txt', $readme);
    $zip->close();

    return $tmpZip;
}

/**
 * Delete uploads, answers, and completions for client codes (does not delete client rows).
 *
 * @param list<string> $clientCodes
 * @return array{submissions: int, followup_notes: int, client_answers: int, completed_questionnaires: int}
 */
function qdb_delete_client_response_data(PDO $pdo, array $clientCodes): array {
    $clientCodes = array_values(array_unique(array_filter(
        $clientCodes,
        static fn($c) => is_string($c) && $c !== ''
    )));
    $deleted = [
        'submissions'              => 0,
        'followup_notes'           => 0,
        'client_answers'           => 0,
        'completed_questionnaires' => 0,
    ];
    if ($clientCodes === []) {
        return $deleted;
    }

    foreach (array_chunk($clientCodes, 200) as $chunk) {
        $ph = implode(',', array_fill(0, count($chunk), '?'));
        if (qdb_table_exists($pdo, 'questionnaire_submission')) {
            $stmt = $pdo->prepare("DELETE FROM questionnaire_submission WHERE clientCode IN ($ph)");
            $stmt->execute($chunk);
            $deleted['submissions'] += $stmt->rowCount();
        }
        if (qdb_table_exists($pdo, 'client_followup_note')) {
            $stmt = $pdo->prepare("DELETE FROM client_followup_note WHERE clientCode IN ($ph)");
            $stmt->execute($chunk);
            $deleted['followup_notes'] += $stmt->rowCount();
        }
        $stmt = $pdo->prepare("DELETE FROM client_answer WHERE clientCode IN ($ph)");
        $stmt->execute($chunk);
        $deleted['client_answers'] += $stmt->rowCount();
        $stmt = $pdo->prepare("DELETE FROM completed_questionnaire WHERE clientCode IN ($ph)");
        $stmt->execute($chunk);
        $deleted['completed_questionnaires'] += $stmt->rowCount();
    }

    return $deleted;
}

/**
 * Remove submission rows assigned to coaches (FK before coach delete).
 *
 * @param list<string> $coachIDs
 */
function qdb_delete_submissions_for_coaches(PDO $pdo, array $coachIDs): int {
    if (!qdb_table_exists($pdo, 'questionnaire_submission')) {
        return 0;
    }
    $coachIDs = array_values(array_unique(array_filter(
        $coachIDs,
        static fn($id) => is_string($id) && $id !== ''
    )));
    if ($coachIDs === []) {
        return 0;
    }
    $total = 0;
    foreach (array_chunk($coachIDs, 200) as $chunk) {
        $ph = implode(',', array_fill(0, count($chunk), '?'));
        $stmt = $pdo->prepare("DELETE FROM questionnaire_submission WHERE assignedByCoach IN ($ph)");
        $stmt->execute($chunk);
        $total += $stmt->rowCount();
    }
    return $total;
}

/**
 * @param array<string, array<string, mixed>> $answerMap questionID => answer row
 * @param array<string, array<string, mixed>>|null $compareMap live answers to diff against
 * @return list<array{label: string, value: string, changedFromLive: bool}>
 */
function qdb_answers_display_rows(
    PDO $pdo,
    array $resultColumns,
    array $answerMap,
    array $optionTextMap,
    array &$stringLabelCache,
    ?array $compareMap = null
): array {
    $rows = [];
    foreach ($resultColumns as $col) {
        $qid = $col['questionID'];
        $a = $answerMap[$qid] ?? null;
        $value = qdb_display_column_cell_value($pdo, $col, $a, $optionTextMap, $stringLabelCache);
        $changedFromLive = false;
        if ($compareMap !== null) {
            $liveValue = qdb_display_column_cell_value(
                $pdo,
                $col,
                $compareMap[$qid] ?? null,
                $optionTextMap,
                $stringLabelCache
            );
            $changedFromLive = $value !== $liveValue;
        }
        $rows[] = [
            'label'            => $col['header'],
            'value'            => $value,
            'changedFromLive'  => $changedFromLive,
        ];
    }
    return $rows;
}

/**
 * @param list<string> $questionIDs
 * @return array<string, array<string, mixed>>
 */
function qdb_load_client_answer_map(
    PDO $pdo,
    string $clientCode,
    array $questionIDs,
    string $table = 'client_answer',
    ?string $submissionID = null
): array {
    if ($questionIDs === []) {
        return [];
    }
    $allowed = ['client_answer', 'client_answer_submission'];
    if (!in_array($table, $allowed, true)) {
        return [];
    }

    $ph = implode(',', array_fill(0, count($questionIDs), '?'));
    if ($table === 'client_answer_submission') {
        if ($submissionID === null || $submissionID === '') {
            return [];
        }
        $sql = "SELECT questionID, answerOptionID, freeTextValue, numericValue
                FROM client_answer_submission
                WHERE submissionID = ? AND questionID IN ($ph)";
        $params = array_merge([$submissionID], $questionIDs);
    } else {
        $sql = "SELECT questionID, answerOptionID, freeTextValue, numericValue
                FROM client_answer
                WHERE clientCode = ? AND questionID IN ($ph)";
        $params = array_merge([$clientCode], $questionIDs);
    }

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $map = [];
    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
        $map[$row['questionID']] = $row;
    }
    return $map;
}

/**
 * Full client response detail for the web clients list (RBAC-scoped).
 */
function qdb_client_detail(PDO $pdo, array $tokenRec, string $clientCode): array {
    $clientCode = trim($clientCode);
    if ($clientCode === '') {
        json_error('INVALID_FIELD', 'clientCode is required', 400);
    }

    [$rbacClause, $rbacParams] = rbac_client_filter($tokenRec, 'cl', 'all');
    $stmt = $pdo->prepare(
        "SELECT cl.clientCode, cl.coachID, cl.archived, cl.archivedAt,
                co.username AS coachUsername,
                sv.username AS supervisorUsername
         FROM client cl
         LEFT JOIN coach co ON co.coachID = cl.coachID
         LEFT JOIN supervisor sv ON sv.supervisorID = co.supervisorID
         WHERE cl.clientCode = :cc AND $rbacClause"
    );
    $stmt->execute(array_merge([':cc' => $clientCode], $rbacParams));
    $client = $stmt->fetch(PDO::FETCH_ASSOC);
    if (!$client) {
        json_error('NOT_FOUND', 'Client not found', 404);
    }

    $qnStmt = $pdo->prepare(
        "SELECT q.questionnaireID, q.name, q.version AS questionnaireVersion, q.orderIndex
         FROM questionnaire q
         WHERE q.questionnaireID IN (
             SELECT questionnaireID FROM completed_questionnaire WHERE clientCode = :cc
             UNION
             SELECT questionnaireID FROM questionnaire_submission WHERE clientCode = :cc2
         )
         ORDER BY q.orderIndex, q.name"
    );
    $qnStmt->execute([':cc' => $clientCode, ':cc2' => $clientCode]);
    $qnRows = $qnStmt->fetchAll(PDO::FETCH_ASSOC);

    $questionnaires = [];
    foreach ($qnRows as $qn) {
        $qnID = $qn['questionnaireID'];
        $ctx = qdb_display_questionnaire_context($pdo, $qnID);
        $questionIDs = array_column($ctx['questions'], 'questionID');
        $resultColumns = $ctx['resultColumns'];
        $optionTextMap = $ctx['optionTextMap'];
        $stringLabelCache = $ctx['stringLabelCache'];

        $cqStmt = $pdo->prepare(
            'SELECT status, sumPoints, startedAt, completedAt
             FROM completed_questionnaire
             WHERE clientCode = :cc AND questionnaireID = :qn'
        );
        $cqStmt->execute([':cc' => $clientCode, ':qn' => $qnID]);
        $cq = $cqStmt->fetch(PDO::FETCH_ASSOC) ?: [];

        $liveMap = qdb_load_client_answer_map($pdo, $clientCode, $questionIDs);
        $currentAnswers = qdb_answers_display_rows(
            $pdo,
            $resultColumns,
            $liveMap,
            $optionTextMap,
            $stringLabelCache,
            null
        );

        $subStmt = $pdo->prepare(
            'SELECT submissionID, version, submittedAt, status, sumPoints, completedAt,
                    structureRevision, structureSnapshotJson
             FROM questionnaire_submission
             WHERE clientCode = :cc AND questionnaireID = :qn
             ORDER BY version DESC'
        );
        $subStmt->execute([':cc' => $clientCode, ':qn' => $qnID]);
        $submissions = [];
        foreach ($subStmt->fetchAll(PDO::FETCH_ASSOC) as $s) {
            $snap = json_decode($s['structureSnapshotJson'] ?? '{}', true) ?: [];
            if (!empty($snap['questions'])) {
                $snapCtx = qdb_display_context_from_manifest($pdo, $snap);
                $versionColumns = $snapCtx['resultColumns'];
                $versionOptionMap = $snapCtx['optionTextMap'];
                $versionStringCache = $snapCtx['stringLabelCache'];
                $versionQuestionIDs = array_column($snapCtx['questions'], 'questionID');
            } else {
                $versionColumns = $resultColumns;
                $versionOptionMap = $optionTextMap;
                $versionStringCache = $stringLabelCache;
                $versionQuestionIDs = $questionIDs;
            }

            $subMap = qdb_load_client_answer_map(
                $pdo,
                $clientCode,
                $versionQuestionIDs,
                'client_answer_submission',
                $s['submissionID']
            );
            $versionAnswers = qdb_answers_display_rows(
                $pdo,
                $versionColumns,
                $subMap,
                $versionOptionMap,
                $versionStringCache,
                $liveMap
            );
            $changedCount = count(array_filter(
                $versionAnswers,
                static fn($r) => !empty($r['changedFromLive'])
            ));
            $submissions[] = [
                'submissionID'      => $s['submissionID'],
                'version'           => (int)$s['version'],
                'structureRevision' => max(1, (int)($s['structureRevision'] ?? 1)),
                'submittedAt'       => $s['submittedAt'] ? date('Y-m-d H:i', (int)$s['submittedAt']) : '',
                'status'            => $s['status'] ?? '',
                'sumPoints'         => $s['sumPoints'] !== null ? (int)$s['sumPoints'] : null,
                'completedAt'       => $s['completedAt'] ? date('Y-m-d H:i', (int)$s['completedAt']) : '',
                'changedCount'      => $changedCount,
                'answers'           => $versionAnswers,
            ];
        }

        $questionnaires[] = [
            'questionnaireID'      => $qnID,
            'name'                 => $qn['name'],
            'questionnaireVersion' => $qn['questionnaireVersion'] ?? '',
            'status'               => $cq['status'] ?? '',
            'sumPoints'            => isset($cq['sumPoints']) ? (int)$cq['sumPoints'] : null,
            'startedAt'            => !empty($cq['startedAt']) ? date('Y-m-d H:i', (int)$cq['startedAt']) : '',
            'completedAt'          => !empty($cq['completedAt']) ? date('Y-m-d H:i', (int)$cq['completedAt']) : '',
            'submissionCount'      => count($submissions),
            'currentAnswers'       => $currentAnswers,
            'submissions'          => $submissions,
        ];
    }

    return [
        'client' => [
            'clientCode'         => $client['clientCode'],
            'coachID'            => $client['coachID'] ?? '',
            'coachUsername'      => $client['coachUsername'] ?? '',
            'supervisorUsername' => $client['supervisorUsername'] ?? '',
        ],
        'questionnaires' => $questionnaires,
        'scoringProfiles' => qdb_client_scoring_profile_results($pdo, $clientCode),
    ];
}

function qdb_client_scoring_profile_results(PDO $pdo, string $clientCode): array {
    require_once __DIR__ . '/scoring.php';
    $stmt = $pdo->prepare(
        'SELECT r.profileID, r.weightedTotal, r.band, r.computedAt, r.questionnaireSnapshot,
                r.coachBand, r.coachReviewedAt, r.coachReviewedByUserID,
                sp.name, sp.greenMin, sp.greenMax, sp.yellowMin, sp.yellowMax, sp.redMin
         FROM client_scoring_profile_result r
         JOIN scoring_profile sp ON sp.profileID = r.profileID
         WHERE r.clientCode = :cc
         ORDER BY sp.name'
    );
    $stmt->execute([':cc' => $clientCode]);
    $out = [];
    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
        $bands = qdb_normalize_scoring_bands($row);
        $coachBand = trim((string)($row['coachBand'] ?? ''));
        $out[] = [
            'profileID'       => $row['profileID'],
            'name'            => $row['name'],
            'weightedTotal'   => (float)$row['weightedTotal'],
            'band'            => $row['band'],
            'calculatedBand'  => $row['band'],
            'coachBand'       => $coachBand !== '' ? $coachBand : null,
            'effectiveBand'   => qdb_effective_scoring_band($row),
            'pendingReview'   => $coachBand === '',
            'greenMin'        => $bands['greenMin'],
            'greenMax'        => $bands['greenMax'],
            'yellowMin'       => $bands['yellowMin'],
            'yellowMax'       => $bands['yellowMax'],
            'redMin'          => $bands['redMin'],
            'computedAt'      => $row['computedAt'] ? date('Y-m-d H:i', (int)$row['computedAt']) : '',
            'coachReviewedAt' => !empty($row['coachReviewedAt'])
                ? date('Y-m-d H:i', (int)$row['coachReviewedAt']) : '',
            'snapshot'        => json_decode($row['questionnaireSnapshot'] ?? '{}', true) ?: [],
        ];
    }
    return $out;
}

/**
 * Active scoring profiles + per-client results for the clients list (RBAC-scoped).
 *
 * @return array{profiles: list<array{profileID: string, name: string}>, byClient: array<string, array<string, array{band: string, weightedTotal: float, name: string}>>}
 */
function qdb_clients_scoring_summary_for_list(PDO $pdo, array $tokenRec): array {
    require_once __DIR__ . '/scoring.php';
    [$rbacClause, $rbacParams] = rbac_client_filter($tokenRec, 'cl');

    $profiles = $pdo->query(
        "SELECT profileID, name FROM scoring_profile WHERE isActive = 1 ORDER BY name"
    )->fetchAll(PDO::FETCH_ASSOC);

    if ($profiles === []) {
        return ['profiles' => [], 'byClient' => []];
    }

    $stmt = $pdo->prepare(
        "SELECT r.clientCode, r.profileID, r.band, r.coachBand, r.weightedTotal, sp.name
         FROM client_scoring_profile_result r
         INNER JOIN scoring_profile sp ON sp.profileID = r.profileID AND sp.isActive = 1
         INNER JOIN client cl ON cl.clientCode = r.clientCode
         WHERE ($rbacClause)"
    );
    $stmt->execute($rbacParams);

    $byClient = [];
    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
        $cc = (string)$row['clientCode'];
        $coachBand = trim((string)($row['coachBand'] ?? ''));
        $byClient[$cc][$row['profileID']] = [
            'band'           => (string)$row['band'],
            'calculatedBand' => (string)$row['band'],
            'coachBand'      => $coachBand !== '' ? $coachBand : null,
            'effectiveBand'  => qdb_effective_scoring_band($row),
            'pendingReview'  => $coachBand === '',
            'weightedTotal'  => (float)$row['weightedTotal'],
            'name'           => (string)$row['name'],
        ];
    }

    return [
        'profiles' => array_map(static fn(array $p) => [
            'profileID' => (string)$p['profileID'],
            'name'      => (string)$p['name'],
        ], $profiles),
        'byClient' => $byClient,
    ];
}

/**
 * @return list<array{profileID: string, name: string, band: ?string, weightedTotal: ?float}>
 */
function qdb_client_scoring_dots_for_client(string $clientCode, array $summary): array {
    $out = [];
    foreach ($summary['profiles'] as $profile) {
        $result = $summary['byClient'][$clientCode][$profile['profileID']] ?? null;
        $out[] = [
            'profileID'      => $profile['profileID'],
            'name'           => $profile['name'],
            'band'           => $result['calculatedBand'] ?? null,
            'calculatedBand' => $result['calculatedBand'] ?? null,
            'coachBand'      => $result['coachBand'] ?? null,
            'effectiveBand'  => $result['effectiveBand'] ?? null,
            'pendingReview'  => $result['pendingReview'] ?? false,
            'weightedTotal'  => $result !== null ? $result['weightedTotal'] : null,
        ];
    }
    return $out;
}