<?php
// File: includes/dip_calibration.php

/**
 * Get volume in liters from dip height using calibration chart
 * 
 * @param mysqli $conn Database connection
 * @param int $tank_id Tank ID
 * @param float $dip_meter Dip height in meters (e.g., 1.250)
 * @return float Volume in liters, or null if not found
 */
function getVolumeFromDip($conn, $tank_id, $dip_meter) {
    // First, try exact match
    $sql = "SELECT volume_liters FROM tank_calibration 
            WHERE tank_id = ? AND dip_meter = ?";
    $stmt = mysqli_prepare($conn, $sql);
    mysqli_stmt_bind_param($stmt, "id", $tank_id, $dip_meter);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    
    if(mysqli_num_rows($result) > 0) {
        $row = mysqli_fetch_assoc($result);
        mysqli_stmt_close($stmt);
        return floatval($row['volume_liters']);
    }
    mysqli_stmt_close($stmt);
    
    // If exact match not found, find nearest values for interpolation
    $sql = "SELECT dip_meter, volume_liters FROM tank_calibration 
            WHERE tank_id = ? AND dip_meter <= ?
            ORDER BY dip_meter DESC LIMIT 1";
    $stmt = mysqli_prepare($conn, $sql);
    mysqli_stmt_bind_param($stmt, "id", $tank_id, $dip_meter);
    mysqli_stmt_execute($stmt);
    $lower = mysqli_stmt_get_result($stmt);
    
    $sql2 = "SELECT dip_meter, volume_liters FROM tank_calibration 
             WHERE tank_id = ? AND dip_meter >= ?
             ORDER BY dip_meter ASC LIMIT 1";
    $stmt2 = mysqli_prepare($conn, $sql2);
    mysqli_stmt_bind_param($stmt2, "id", $tank_id, $dip_meter);
    mysqli_stmt_execute($stmt2);
    $upper = mysqli_stmt_get_result($stmt2);
    
    $lower_data = mysqli_fetch_assoc($lower);
    $upper_data = mysqli_fetch_assoc($upper);
    
    mysqli_stmt_close($stmt);
    mysqli_stmt_close($stmt2);
    
    // If we have both lower and upper bounds, interpolate
    if($lower_data && $upper_data) {
        $dip_diff = $upper_data['dip_meter'] - $lower_data['dip_meter'];
        $vol_diff = $upper_data['volume_liters'] - $lower_data['volume_liters'];
        $ratio = ($dip_meter - $lower_data['dip_meter']) / $dip_diff;
        return $lower_data['volume_liters'] + ($ratio * $vol_diff);
    }
    
    // If only lower bound exists (dip is below minimum)
    if($lower_data) {
        return $lower_data['volume_liters'];
    }
    
    // If only upper bound exists (dip is above maximum)
    if($upper_data) {
        return $upper_data['volume_liters'];
    }
    
    // No calibration data found - return null
    return null;
}

/**
 * Calculate stock difference based on dip reading
 */
function calculateDipDifference($dip_volume, $system_stock) {
    $difference = $dip_volume - $system_stock;
    
    if($difference < 0) {
        return [
            'difference' => $difference,
            'status' => 'LOSS',
            'status_class' => 'status-loss',
            'status_icon' => 'fa-arrow-down',
            'message' => 'Physical dip is LESS than system stock - Loss detected!'
        ];
    } elseif($difference > 0) {
        return [
            'difference' => $difference,
            'status' => 'GAIN',
            'status_class' => 'status-gain',
            'status_icon' => 'fa-arrow-up',
            'message' => 'Physical dip is MORE than system stock - Gain detected!'
        ];
    } else {
        return [
            'difference' => $difference,
            'status' => 'OK',
            'status_class' => 'status-ok',
            'status_icon' => 'fa-check',
            'message' => 'Perfect match! Dip equals system stock.'
        ];
    }
}
?>