<?php
// includes/checklist_helper.php
//
// BUG FIX: view_appliance_job.php's fetch query and approve_job.php's
// count query used DIFFERENT boolean logic for the same thing —
//   view_appliance_job.php: Category = 'Appliance' AND (item_name matches OR generic)
//   approve_job.php:        Category = 'Appliance' OR  item_name matches
// For a Misc item whose checklist rows were tagged Category='Misc' (not
// 'Appliance'), the AND-query found zero rows to even show/save, while the
// OR-query still counted them as "expected" — hence "0 of 22 saved" even
// after saving. One shared function now powers every place that needs to
// know "which checklist items apply to this job", so this can't drift again.

/**
 * @return array<int> MasterCheckID values applicable to this job
 */
function getApplicableChecklistIds(PDO $pdo, string $category, string $itemName): array
{
    $categoryUpper = strtoupper(trim($category));

    // FIX (docx item 6, "Category Mismatch — checklist not coming to the
    // equipment"): the item-specific half of these queries used a strict
    // `item_name = ?` match. Any stray leading/trailing whitespace, or a
    // case difference, between how an item name was typed in add_job.php
    // (via the Master Bank selector) vs. manage_checklists.php's item filter
    // silently produced zero matching rows — exactly this symptom, and with
    // no visible error anywhere. Hardened to a case/whitespace-insensitive
    // comparison on both sides so a formatting difference alone can no
    // longer break the checklist link.
    $itemNameNormalized = mb_strtolower(trim($itemName));

    if (in_array($categoryUpper, ['APPLIANCE', 'MISC'], true)) {
        $stmt = $pdo->prepare(
            "SELECT MasterCheckID FROM tbl_master_checklists
             WHERE IsActive = 1 AND (
                 (Category IN ('Appliance', 'Misc') AND (item_name IS NULL OR item_name = ''))
                 OR LOWER(TRIM(item_name)) = ?
             )"
        );
        $stmt->execute([$itemNameNormalized]);
    } else {
        // LLG (and anything else defaulting to the LLG path)
        $stmt = $pdo->prepare(
            "SELECT MasterCheckID FROM tbl_master_checklists
             WHERE IsActive = 1 AND (
                 (Category = 'LLG' AND (item_name = 'LLG' OR item_name IS NULL OR item_name = ''))
                 OR LOWER(TRIM(item_name)) = ?
             )"
        );
        $stmt->execute([$itemNameNormalized]);
    }

    return array_map('intval', array_column($stmt->fetchAll(PDO::FETCH_ASSOC), 'MasterCheckID'));
}

/**
 * Full checklist rows (with any saved Result/Remarks for this job) for
 * display in an edit form — built from the same ID list above.
 */
function getChecklistItemsForDisplay(PDO $pdo, int $jobId, string $category, string $itemName): array
{
    $ids = getApplicableChecklistIds($pdo, $category, $itemName);
    if (empty($ids)) return [];

    $placeholders = implode(',', array_fill(0, count($ids), '?'));
    $stmt = $pdo->prepare(
        "SELECT m.MasterCheckID, m.CheckItem, m.DisplayOrder, c.Result, c.Remarks
         FROM tbl_master_checklists m
         LEFT JOIN tbl_job_checklists c ON c.MasterCheckID = m.MasterCheckID AND c.JobID = ?
         WHERE m.MasterCheckID IN ($placeholders)
         ORDER BY m.DisplayOrder ASC"
    );
    $stmt->execute(array_merge([$jobId], $ids));
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
