if (!isset($_SESSION['chat_hdrid'])) {


               $res_hdr = DB::select("select id,group_name,level,description,workflow from chathdr where level = '0' and lower(term) like '%{$search}%'");

                    echo "here....";
                    exit();
            // Check if no results
            if ($res_hdr->isEmpty()) {
                echo "No matching option found. Please try a different keyword.";
                exit;
            }

            // Only one result
            if ($res_hdr->count() == 1) {
                $row_hdr = $res_hdr->first();

                session([
                    'chat_hdrid' => $row_hdr->id,
                    'chat_group' => $row_hdr->group_name,
                    'chat_level' => 1,
                    'chat_search' => '',
                    'chat_workflow' => $row_hdr->workflow,
                    'chat_qid' => ''
                ]);

                $hdrid = $row_hdr->id;

            } else {
                // Multiple results, ask user to specify
                echo "Please Specify. You want to know about...\r\n";

                foreach ($res_hdr as $row_hdr) {
                    echo $row_hdr->description . "\r\n";
                }
                exit;
            }
//Level 0

} else {


    // CONTINUE CHAT

    if ($_SESSION['chat_workflow'] == 0){

        $stmt = $conn->prepare("
            SELECT id, term, level ,description
            FROM chathdr 
            WHERE group_name = ? 
              AND level = ? 
              AND LOWER(term) LIKE LOWER(?) 
        ");


        $search = $search;

          if ($debug_on == 1){
            echo "SELECT id, term, level ,description FROM chathdr WHERE group_name = '" . $_SESSION['chat_group'] . "' and level = '" . $_SESSION['chat_level'] . "' AND LOWER(term) LIKE LOWER('$search')" . "<br>";
           } 

        $stmt->bind_param("sis", $_SESSION['chat_group'], $_SESSION['chat_level'], $search);
        $stmt->execute();
        $res_hdr = $stmt->get_result();

     }else{
           // echo "qid :" . $qid ;
            if (isset($_SESSION['chat_qid']) and $_SESSION['chat_qid'] <> ''){
                $qid = $_SESSION['chat_qid'] + 1;
            }else{
                $qid = 2;
                $_SESSION['chat_qid'] = 2;
            }

                $hdrid = $_SESSION['chat_hdrid'];

                $stmt = $conn->prepare("
                    SELECT answer,
                  IFNULL(final_level, 'No') AS final_level,
                  remarks,hdrid,
                  answer_format
           FROM chatdtl
           WHERE hdrid = '$hdrid' and q_id = '$qid'
                ");

           if ($debug_on == 1){     
                    echo "here...
                                SELECT answer,hdrid,
                              IFNULL(final_level, 'No') AS final_level,
                              remarks,
                              answer_format
                       FROM chatdtl
                       WHERE hdrid = '$hdrid' and q_id = '$qid'
                            ";
                }

        $stmt->execute();
        $res_hdr = $stmt->get_result();

    }

    if ($_SESSION['chat_search'] == ''){
        $_SESSION['chat_search'] = $search;
    }

    if ($res_hdr->num_rows === 0) {
        // Provide a more helpful message by showing the previous options
        echo "Select a valid option:\n" . ($_SESSION['chat_PrevAnswer'] ?? '');
        exit;
    }

    if ($res_hdr->num_rows == 1) {

            $row_hdr = $res_hdr->fetch_assoc();
            // echo "Herexxxxx...." . $_SESSION['chat_workflow'];
//            $_SESSION['chat_hdrid'] = $row_hdr['id'];
            if ($_SESSION['chat_workflow'] == 1){
                $hdrid = $row_hdr['hdrid'];
             }else{
                $hdrid = $row_hdr['id'];
             }
            // echo $hdrid;
    }else{

          echo "Please Specify. You want to know about...\r\n" ;
          While ($row_hdr = $res_hdr->fetch_assoc()){
                echo $row_hdr['description'] . "\r\n";
          }  
        exit;

    }        

}

// --------------------------------------------------------
// STEP 2: FETCH chatdtl RECORD FOR THIS hdrid
// --------------------------------------------------------

 if (isset($qid) and $qid <> ''){
         $stmt = $conn->prepare("
           SELECT answer,
                  IFNULL(final_level, 'No') AS final_level,
                  remarks,
                  answer_format
           FROM chatdtl
           WHERE hdrid = ? and q_id = '$qid'
           ORDER BY q_id ASC
           LIMIT 1
        ");
 }else{

         $stmt = $conn->prepare("
           SELECT answer,
                  IFNULL(final_level, 'No') AS final_level,
                  remarks,
                  answer_format
           FROM chatdtl
           WHERE hdrid = ? 
           ORDER BY q_id ASC
           LIMIT 1
        ");

 }
 
if ($debug_on == 1){     

    echo "xx  SELECT answer,
          IFNULL(final_level, 'No') AS final_level,
          remarks,
          answer_format
   FROM chatdtl
   WHERE hdrid = '$hdrid' and q_id = '$qid'
   ORDER BY q_id ASC
   LIMIT 1
" . "<br>";

}

 $stmt->bind_param("i", $hdrid);
 $stmt->execute();
 $res_detail = $stmt->get_result();

if ($res_detail->num_rows === 0) {
    echo "Sorry, I couldn't find a response for that. Please try again.";
    exit;
}

 $row2 = $res_detail->fetch_assoc();

// --------------------------------------------------------
// NOT FINAL LEVEL → SHOW ANSWER AND MOVE TO NEXT LEVEL
// --------------------------------------------------------
if ($row2['final_level'] === 'No') {
    $_SESSION['chat_level'] = $row_hdr['level'] + 1;
    $_SESSION['chat_PrevAnswer'] = $row2['answer'];
    
    echo $row2['answer'];
    exit;
}

// --------------------------------------------------------
// FINAL LEVEL → EXECUTE remarks QUERY (WHICH IS ALWAYS SAFE SELECT QUERY)
// --------------------------------------------------------
 $remarks = trim($row2['remarks']);

if ($row2['answer_format'] == 'TABLE'){
// echo "<br><br>" . $remarks . "\r\n";
// Basic security check to ensure it's a SELECT query
if (!preg_match('/^\s*select\s/i', $remarks)) {
    echo "Invalid final query.";
    exit;
}

 $result = $conn->query($remarks);

if ($result && $result->num_rows > 0) {
            // Store all data for copying and Excel download
            $allData = array();
            
            // Get column names
            $columns = array();
            while ($field = $result->fetch_field()) {
                $columns[] = $field->name;
            }
            
            // Reset result pointer to beginning
            $result->data_seek(0);
            
            // Get all rows
            while ($row = $result->fetch_assoc()) {
                $allData[] = $row;
            }
            
            // Count total records
            $totalRecords = count($allData);
            $displayedRecords = min(5, $totalRecords);
            
            // --- KEY CHANGE: Generate a unique ID for each table ---
            $tableId = 'sqlResultWrapper_' . time() . '_' . rand(1000, 9999);

            // Build HTML table with only first 5 rows
            // --- FIX: Use classes and the unique ID ---
            $table = "
        <div id='{$tableId}' 
             class='sql-result-wrapper'
             data-excel-data='" . htmlspecialchars(json_encode($allData), ENT_QUOTES, 'UTF-8') . "' >

            <table class='sql-result-table' border='1' cellpadding='6' cellspacing='0'>
                <tr>";

            // Column headers
            foreach ($columns as $column) {
                $table .= "<th>" . htmlspecialchars($column) . "</th>";
            }
            $table .= "</tr>";

            // First 5 rows only
            $rowCount = 0;
            foreach ($allData as $row) {
                if ($rowCount >= 5) break;
                
                $table .= "<tr>";
                foreach ($row as $val) {
                    $table .= "<td>" . htmlspecialchars($val) . "</td>";
                }
                $table .= "</tr>";
                $rowCount++;
            }

            $table .= "</table>
        </div>";
        echo $row2['result'];
            echo $table;
            exit;
        }

    echo $row2['answer'];
}
if ($row2['answer_format'] == 'FUNCTION'){

echo $row2['remarks'];

}

?>
</x-app-layout>