<?php
/**
 * Database Helper
 * 
 * Provides utilities for working with WordPress $wpdb, particularly
 * handling NULL values which wpdb converts to empty strings/zeros.
 *
 * @since 6.5.0
 */

if (!defined('ABSPATH')) exit;

class FS_DB_Helper {

    /**
     * Fix NULL values after a wpdb insert or update.
     * 
     * WordPress $wpdb->insert() and $wpdb->update() convert PHP null values
     * to empty strings, which MySQL casts to 0 for numeric (bigint) columns
     * instead of storing SQL NULL. This method runs a follow-up query to 
     * properly set those columns to NULL.
     *
     * @param string $table  Full table name (including prefix)
     * @param int    $id     Row ID to update
     * @param array  $fields Associative array of field_name => value pairs.
     *                       Only fields where value is null/empty will be set to NULL.
     * @return void
     */
    public static function fix_null_fields($table, $id, $fields) {
        global $wpdb;

        $null_columns = array();
        foreach ($fields as $field => $value) {
            if ($value === null || $value === '' || $value === 0 || $value === '0') {
                // Only include if the field is supposed to be nullable
                $null_columns[] = "`{$field}` = NULL";
            }
        }

        if (!empty($null_columns)) {
            $set_clause = implode(', ', $null_columns);
            $wpdb->query($wpdb->prepare(
                "UPDATE {$table} SET {$set_clause} WHERE id = %d",
                $id
            ));
        }
    }

    /**
     * Prepare nullable foreign key value for wpdb insert/update data arrays.
     * 
     * Returns intval for truthy values, null for falsy values.
     * Use in combination with fix_null_fields() after the insert/update.
     *
     * @param mixed $value The value to process
     * @return int|null
     */
    public static function nullable_int($value) {
        return !empty($value) ? intval($value) : null;
    }
}
