<?php
/**
 * Dip Calibration Helper Functions
 * Converts dip meter readings to volume in liters using calibration charts
 * 
 * @package Petrol Pump Management System
 * @version 1.0
 */

/**
 * 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|null Volume in liters, or null if not found
 */
function getVolumeFromDip($conn, $tank_id, $dip_meter) {
    // Validate inputs
    if (!$conn || $tank_id <= 0 || $dip_meter <= 0) {
        return null;
    }
    
    // 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
    // Find lower bound (closest dip less than or equal to given dip)
    $sql_lower = "SELECT dip_meter, volume_liters FROM tank_calibration 
                  WHERE tank_id = ? AND dip_meter <= ?
                  ORDER BY dip_meter DESC LIMIT 1";
    $stmt_lower = mysqli_prepare($conn, $sql_lower);
    mysqli_stmt_bind_param($stmt_lower, "id", $tank_id, $dip_meter);
    mysqli_stmt_execute($stmt_lower);
    $lower_result = mysqli_stmt_get_result($stmt_lower);
    $lower_data = mysqli_fetch_assoc($lower_result);
    mysqli_stmt_close($stmt_lower);
    
    // Find upper bound (closest dip greater than or equal to given dip)
    $sql_upper = "SELECT dip_meter, volume_liters FROM tank_calibration 
                  WHERE tank_id = ? AND dip_meter >= ?
                  ORDER BY dip_meter ASC LIMIT 1";
    $stmt_upper = mysqli_prepare($conn, $sql_upper);
    mysqli_stmt_bind_param($stmt_upper, "id", $tank_id, $dip_meter);
    mysqli_stmt_execute($stmt_upper);
    $upper_result = mysqli_stmt_get_result($stmt_upper);
    $upper_data = mysqli_fetch_assoc($upper_result);
    mysqli_stmt_close($stmt_upper);
    
    // Case 1: 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'];
        
        // Avoid division by zero
        if ($dip_diff == 0) {
            return $lower_data['volume_liters'];
        }
        
        $ratio = ($dip_meter - $lower_data['dip_meter']) / $dip_diff;
        $interpolated_volume = $lower_data['volume_liters'] + ($ratio * $vol_diff);
        
        return round($interpolated_volume, 2);
    }
    
    // Case 2: Only lower bound exists (dip is below minimum in chart)
    if ($lower_data) {
        return $lower_data['volume_liters'];
    }
    
    // Case 3: Only upper bound exists (dip is above maximum in chart)
    if ($upper_data) {
        return $upper_data['volume_liters'];
    }
    
    // Case 4: No calibration data found for this tank
    return null;
}

/**
 * Get volume from dip using linear approximation (for tanks without calibration chart)
 * Uses cylindrical tank formula: Volume = π × r² × height
 * 
 * @param mysqli $conn Database connection
 * @param int $tank_id Tank ID
 * @param float $dip_meter Dip height in meters
 * @return float|null Estimated volume in liters
 */
function getVolumeFromDipLinear($conn, $tank_id, $dip_meter) {
    // Get tank dimensions
    $sql = "SELECT tank_length, tank_diameter, capacity FROM tanks WHERE id = ?";
    $stmt = mysqli_prepare($conn, $sql);
    mysqli_stmt_bind_param($stmt, "i", $tank_id);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    $tank = mysqli_fetch_assoc($result);
    mysqli_stmt_close($stmt);
    
    if (!$tank) {
        return null;
    }
    
    $length_m = floatval($tank['tank_length']) / 1000; // Convert mm to meters
    $diameter_m = floatval($tank['tank_diameter']) / 1000; // Convert mm to meters
    $radius_m = $diameter_m / 2;
    $capacity_liters = floatval($tank['capacity']);
    
    // If we have dimensions, calculate volume
    if ($length_m > 0 && $radius_m > 0 && $dip_meter <= $diameter_m) {
        // For horizontal cylindrical tank
        // Volume = Length × (r² × acos((r-h)/r) - (r-h) × √(2rh - h²))
        $r = $radius_m;
        $h = $dip_meter;
        
        if ($h >= $diameter_m) {
            return $capacity_liters;
        }
        
        if ($h <= 0) {
            return 0;
        }
        
        // Calculate cross-sectional area of the liquid
        $theta = 2 * acos(($r - $h) / $r);
        $area = ($r * $r * ($theta - sin($theta))) / 2;
        
        // Volume in cubic meters
        $volume_m3 = $length_m * $area;
        
        // Convert to liters (1 m³ = 1000 liters)
        $volume_liters = $volume_m3 * 1000;
        
        return round($volume_liters, 2);
    }
    
    // Fallback: linear approximation based on capacity
    $max_dip = 2.5; // Assume max dip is 2.5 meters for standard tanks
    $ratio = min(1, max(0, $dip_meter / $max_dip));
    return round($capacity_liters * $ratio, 2);
}

/**
 * Calculate difference between dip volume and system stock
 * 
 * @param float $dip_volume Volume from dip reading in liters
 * @param float $system_stock Current system stock in liters
 * @return array Array with difference, status, class, icon, and message
 */
function calculateDipDifference($dip_volume, $system_stock) {
    $difference = $dip_volume - $system_stock;
    $abs_difference = abs($difference);
    
    if ($difference < 0) {
        return [
            'difference' => $difference,
            'abs_difference' => $abs_difference,
            'status' => 'LOSS',
            'status_class' => 'status-loss',
            'status_icon' => 'fa-arrow-down',
            'color' => '#dc3545',
            'bg_color' => '#f8d7da',
            'message' => "⚠️ LOSS: Physical dip is LESS than system stock by " . number_format($abs_difference, 2) . " Liters",
            'short_message' => "Loss of " . number_format($abs_difference, 2) . " L"
        ];
    } elseif ($difference > 0) {
        return [
            'difference' => $difference,
            'abs_difference' => $abs_difference,
            'status' => 'GAIN',
            'status_class' => 'status-gain',
            'status_icon' => 'fa-arrow-up',
            'color' => '#28a745',
            'bg_color' => '#d4edda',
            'message' => "✅ GAIN: Physical dip is MORE than system stock by " . number_format($abs_difference, 2) . " Liters",
            'short_message' => "Gain of " . number_format($abs_difference, 2) . " L"
        ];
    } else {
        return [
            'difference' => $difference,
            'abs_difference' => $abs_difference,
            'status' => 'OK',
            'status_class' => 'status-ok',
            'status_icon' => 'fa-check',
            'color' => '#ffc107',
            'bg_color' => '#fff3cd',
            'message' => "✓ PERFECT: Dip reading matches system stock exactly",
            'short_message' => "Exact match"
        ];
    }
}

/**
 * Get tank calibration status
 * 
 * @param mysqli $conn Database connection
 * @param int $tank_id Tank ID
 * @return array Status information
 */
function getTankCalibrationStatus($conn, $tank_id) {
    $sql = "SELECT COUNT(*) as calibration_count, 
                   MIN(dip_meter) as min_dip, 
                   MAX(dip_meter) as max_dip,
                   MIN(volume_liters) as min_volume,
                   MAX(volume_liters) as max_volume
            FROM tank_calibration 
            WHERE tank_id = ?";
    $stmt = mysqli_prepare($conn, $sql);
    mysqli_stmt_bind_param($stmt, "i", $tank_id);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    $data = mysqli_fetch_assoc($result);
    mysqli_stmt_close($stmt);
    
    $has_calibration = ($data['calibration_count'] > 0);
    
    return [
        'has_calibration' => $has_calibration,
        'calibration_count' => intval($data['calibration_count']),
        'min_dip' => $has_calibration ? floatval($data['min_dip']) : null,
        'max_dip' => $has_calibration ? floatval($data['max_dip']) : null,
        'min_volume' => $has_calibration ? floatval($data['min_volume']) : null,
        'max_volume' => $has_calibration ? floatval($data['max_volume']) : null
    ];
}

/**
 * Insert or update calibration data for a tank
 * 
 * @param mysqli $conn Database connection
 * @param int $tank_id Tank ID
 * @param float $dip_meter Dip height in meters
 * @param float $volume_liters Volume in liters
 * @return bool Success or failure
 */
function saveCalibrationData($conn, $tank_id, $dip_meter, $volume_liters) {
    // Check if entry already exists
    $check_sql = "SELECT id FROM tank_calibration WHERE tank_id = ? AND dip_meter = ?";
    $check_stmt = mysqli_prepare($conn, $check_sql);
    mysqli_stmt_bind_param($check_stmt, "id", $tank_id, $dip_meter);
    mysqli_stmt_execute($check_stmt);
    $check_result = mysqli_stmt_get_result($check_stmt);
    
    if (mysqli_num_rows($check_result) > 0) {
        // Update existing
        $row = mysqli_fetch_assoc($check_result);
        mysqli_stmt_close($check_stmt);
        
        $update_sql = "UPDATE tank_calibration SET volume_liters = ? WHERE id = ?";
        $update_stmt = mysqli_prepare($conn, $update_sql);
        mysqli_stmt_bind_param($update_stmt, "di", $volume_liters, $row['id']);
        $result = mysqli_stmt_execute($update_stmt);
        mysqli_stmt_close($update_stmt);
        
        return $result;
    } else {
        mysqli_stmt_close($check_stmt);
        
        // Insert new
        $insert_sql = "INSERT INTO tank_calibration (tank_id, dip_meter, volume_liters) VALUES (?, ?, ?)";
        $insert_stmt = mysqli_prepare($conn, $insert_sql);
        mysqli_stmt_bind_param($insert_stmt, "idd", $tank_id, $dip_meter, $volume_liters);
        $result = mysqli_stmt_execute($insert_stmt);
        mysqli_stmt_close($insert_stmt);
        
        return $result;
    }
}

/**
 * Batch import calibration data from array
 * 
 * @param mysqli $conn Database connection
 * @param int $tank_id Tank ID
 * @param array $data Array of [dip_meter => volume_liters]
 * @return array Result with success count and errors
 */
function batchImportCalibration($conn, $tank_id, $data) {
    $success_count = 0;
    $errors = [];
    
    foreach ($data as $dip_meter => $volume_liters) {
        if (saveCalibrationData($conn, $tank_id, $dip_meter, $volume_liters)) {
            $success_count++;
        } else {
            $errors[] = "Failed for dip: $dip_meter";
        }
    }
    
    return [
        'success' => $success_count,
        'errors' => $errors,
        'total' => count($data)
    ];
}

/**
 * Get the nearest calibration point for a given dip
 * 
 * @param mysqli $conn Database connection
 * @param int $tank_id Tank ID
 * @param float $dip_meter Dip height in meters
 * @return array|null Nearest calibration point
 */
function getNearestCalibrationPoint($conn, $tank_id, $dip_meter) {
    // Try to get the closest point (either above or below)
    $sql = "SELECT dip_meter, volume_liters, 
            ABS(dip_meter - ?) as diff
            FROM tank_calibration 
            WHERE tank_id = ?
            ORDER BY diff ASC 
            LIMIT 1";
    $stmt = mysqli_prepare($conn, $sql);
    mysqli_stmt_bind_param($stmt, "di", $dip_meter, $tank_id);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    $data = mysqli_fetch_assoc($result);
    mysqli_stmt_close($stmt);
    
    return $data;
}

/**
 * Validate if dip reading is within calibration range
 * 
 * @param mysqli $conn Database connection
 * @param int $tank_id Tank ID
 * @param float $dip_meter Dip height in meters
 * @return array Validation result
 */
function validateDipRange($conn, $tank_id, $dip_meter) {
    $status = getTankCalibrationStatus($conn, $tank_id);
    
    if (!$status['has_calibration']) {
        return [
            'valid' => false,
            'warning' => true,
            'message' => 'No calibration data available for this tank. Volume will be estimated linearly.'
        ];
    }
    
    if ($dip_meter < $status['min_dip']) {
        return [
            'valid' => true,
            'warning' => true,
            'message' => 'Dip reading is below minimum calibration range (' . number_format($status['min_dip'], 3) . 'm). Volume will be extrapolated from minimum point.'
        ];
    }
    
    if ($dip_meter > $status['max_dip']) {
        return [
            'valid' => true,
            'warning' => true,
            'message' => 'Dip reading exceeds maximum calibration range (' . number_format($status['max_dip'], 3) . 'm). Volume will be extrapolated from maximum point.'
        ];
    }
    
    return [
        'valid' => true,
        'warning' => false,
        'message' => 'Dip reading is within calibration range.'
    ];
}

/**
 * Convert centimeters to meters
 * 
 * @param float $cm Centimeters
 * @return float Meters
 */
function cmToMeters($cm) {
    return $cm / 100;
}

/**
 * Convert meters to centimeters
 * 
 * @param float $m Meters
 * @return float Centimeters
 */
function metersToCm($m) {
    return $m * 100;
}

/**
 * Format dip reading for display
 * 
 * @param float $dip_meter Dip in meters
 * @param string $format 'meters', 'cm', or 'both'
 * @return string Formatted string
 */
function formatDipReading($dip_meter, $format = 'both') {
    if ($format == 'meters') {
        return number_format($dip_meter, 3) . ' m';
    } elseif ($format == 'cm') {
        return number_format($dip_meter * 100, 1) . ' cm';
    } else {
        return number_format($dip_meter, 3) . ' m (' . number_format($dip_meter * 100, 1) . ' cm)';
    }
}
?>