<?php
session_start();
if(!isset($_SESSION['username'])) {
    header("Location: ../login.php");
    exit();
}

include('../includes/db.php');

$success_message = "";
$error_message = "";
$calculation_result = null;

// Get all fuel types
$fuel_sql = "SELECT * FROM fuel_types ORDER BY name";
$fuel_result = mysqli_query($conn, $fuel_sql);

// Get current stock for selected fuel type (via AJAX-free POST approach)
$current_stock = 0;
$selected_fuel_id = isset($_POST['fuel_type_id']) ? $_POST['fuel_type_id'] : '';
$old_rate = 0;

if($selected_fuel_id) {
    // Get current stock from tanks table for this fuel type
    $stock_sql = "SELECT SUM(current_stock) as total_stock FROM tanks WHERE fuel_type_id = '$selected_fuel_id'";
    $stock_result = mysqli_query($conn, $stock_sql);
    $stock_data = mysqli_fetch_assoc($stock_result);
    $current_stock = $stock_data['total_stock'] ? $stock_data['total_stock'] : 0;
    
    // Get current sale price as old rate
    $rate_sql = "SELECT sale_price FROM fuel_types WHERE id = '$selected_fuel_id'";
    $rate_result = mysqli_query($conn, $rate_sql);
    $rate_data = mysqli_fetch_assoc($rate_result);
    $old_rate = $rate_data['sale_price'] ? $rate_data['sale_price'] : 0;
}

// Handle calculation request
if($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['calculate'])) {
    $fuel_type_id = mysqli_real_escape_string($conn, $_POST['fuel_type_id']);
    $new_rate = mysqli_real_escape_string($conn, $_POST['new_rate']);
    
    // Get current stock
    $stock_sql = "SELECT SUM(current_stock) as total_stock FROM tanks WHERE fuel_type_id = '$fuel_type_id'";
    $stock_result = mysqli_query($conn, $stock_sql);
    $stock_data = mysqli_fetch_assoc($stock_result);
    $current_stock = $stock_data['total_stock'] ? $stock_data['total_stock'] : 0;
    
    // Get old rate
    $rate_sql = "SELECT sale_price FROM fuel_types WHERE id = '$fuel_type_id'";
    $rate_result = mysqli_query($conn, $rate_sql);
    $rate_data = mysqli_fetch_assoc($rate_result);
    $old_rate = $rate_data['sale_price'];
    
    // Calculate gain/loss
    $rate_diff = $new_rate - $old_rate;
    $gain_loss = $rate_diff * $current_stock;
    
    $calculation_result = [
        'fuel_type_id' => $fuel_type_id,
        'old_rate' => $old_rate,
        'new_rate' => $new_rate,
        'current_stock' => $current_stock,
        'gain_loss' => $gain_loss,
        'rate_diff' => $rate_diff
    ];
}

// Handle save request
if($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['save_revision'])) {
    $fuel_type_id = mysqli_real_escape_string($conn, $_POST['fuel_type_id']);
    $old_rate = mysqli_real_escape_string($conn, $_POST['old_rate']);
    $new_rate = mysqli_real_escape_string($conn, $_POST['new_rate']);
    $stock_quantity = mysqli_real_escape_string($conn, $_POST['stock_quantity']);
    $gain_loss = mysqli_real_escape_string($conn, $_POST['gain_loss']);
    $status = ($gain_loss > 0) ? 'GAIN' : (($gain_loss < 0) ? 'LOSS' : 'NEUTRAL');
    $date_time = date('Y-m-d H:i:s');
    
    mysqli_begin_transaction($conn);
    
    try {
        // Insert rate revision record
        $insert_sql = "INSERT INTO rate_revisions (fuel_type_id, old_rate, new_rate, stock_quantity, gain_loss, status, date_time) 
                       VALUES ('$fuel_type_id', '$old_rate', '$new_rate', '$stock_quantity', '$gain_loss', '$status', '$date_time')";
        mysqli_query($conn, $insert_sql);
        
        // Update fuel type sale price
        $update_sql = "UPDATE fuel_types SET sale_price = '$new_rate' WHERE id = '$fuel_type_id'";
        mysqli_query($conn, $update_sql);
        
        mysqli_commit($conn);
        $success_message = "Rate revision saved successfully!";
        
        // Clear calculation result
        $calculation_result = null;
        
    } catch (Exception $e) {
        mysqli_rollback($conn);
        $error_message = "Error: " . $e->getMessage();
    }
}

// Get all rate revisions for display
$revisions_sql = "SELECT rr.*, ft.name as fuel_name 
                  FROM rate_revisions rr 
                  LEFT JOIN fuel_types ft ON rr.fuel_type_id = ft.id 
                  ORDER BY rr.id DESC";
$revisions_result = mysqli_query($conn, $revisions_sql);
?>

<?php include('../includes/log.php'); ?>
<?php include('../includes/sd.php'); ?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Rate Gain/Loss Module</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
    <style>
        @import url('https://fonts.googleapis.com/css2?family=Poppins:wght@300;400;500;600;700;800&display=swap');
        
        * {
            font-family: 'Poppins', sans-serif;
        }
        
        h1, h2, h3, h4, h5, h6, .card-header h4, .table th {
            font-family: 'Times New Roman', serif;
        }
        
        body {
            background: #f5f5f5;
        }
        
        .main-container {
            padding: 20px;
        }
        
        /* Page Header */
        .page-header {
            background: linear-gradient(135deg, #A04657 0%, #6b2a36 100%);
            border-radius: 15px;
            padding: 25px 30px;
            margin-bottom: 30px;
            color: #fff;
        }
        
        .page-header h2 {
            margin: 0 0 8px 0;
            font-size: 28px;
            color: #fff;
        }
        
        .page-header p {
            margin: 0;
            opacity: 0.95;
            font-weight: 500;
        }
        
        /* Cards */
        .form-card, .table-card {
            background: #fff;
            border-radius: 15px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.05);
            overflow: hidden;
            margin-bottom: 30px;
        }
        
        .card-header-custom {
            background: linear-gradient(135deg, #A04657 0%, #6b2a36 100%);
            padding: 15px 20px;
            color: #fff;
        }
        
        .card-header-custom h4 {
            margin: 0;
            font-size: 18px;
        }
        
        .card-header-custom i {
            margin-right: 10px;
        }
        
        .card-body-custom {
            padding: 25px;
        }
        
        /* Form Controls */
        .form-label {
            font-weight: 600;
            color: #333;
            margin-bottom: 8px;
        }
        
        .form-label i {
            color: #A04657;
            margin-right: 8px;
        }
        
        .form-control, .form-select {
            border: 2px solid #e0e0e0;
            border-radius: 10px;
            padding: 10px 15px;
            font-weight: 500;
            transition: all 0.3s;
        }
        
        .form-control:focus, .form-select:focus {
            border-color: #A04657;
            box-shadow: 0 0 0 3px rgba(160,70,87,0.1);
        }
        
        /* Result Card */
        .result-card {
            background: linear-gradient(135deg, #f8f9fa 0%, #e9ecef 100%);
            border-radius: 15px;
            padding: 20px;
            margin-top: 20px;
            border-left: 5px solid #A04657;
        }
        
        .result-title {
            font-weight: 700;
            font-size: 18px;
            margin-bottom: 15px;
            color: #A04657;
        }
        
        .result-item {
            display: flex;
            justify-content: space-between;
            padding: 10px 0;
            border-bottom: 1px solid #dee2e6;
        }
        
        .result-item:last-child {
            border-bottom: none;
        }
        
        .result-label {
            font-weight: 600;
            color: #555;
        }
        
        .result-value {
            font-weight: 700;
            font-size: 18px;
        }
        
        .gain {
            color: #28a745;
        }
        
        .loss {
            color: #dc3545;
        }
        
        .neutral {
            color: #ffc107;
        }
        
        .gain-bg {
            background: #d4edda;
            color: #155724;
            padding: 5px 12px;
            border-radius: 20px;
            font-weight: 600;
            display: inline-block;
        }
        
        .loss-bg {
            background: #f8d7da;
            color: #721c24;
            padding: 5px 12px;
            border-radius: 20px;
            font-weight: 600;
            display: inline-block;
        }
        
        .neutral-bg {
            background: #fff3cd;
            color: #856404;
            padding: 5px 12px;
            border-radius: 20px;
            font-weight: 600;
            display: inline-block;
        }
        
        /* Buttons */
        .btn-calculate {
            background: linear-gradient(135deg, #A04657 0%, #6b2a36 100%);
            color: #fff;
            border: none;
            padding: 12px 30px;
            border-radius: 10px;
            font-weight: 600;
            font-size: 16px;
            cursor: pointer;
            transition: all 0.3s;
            width: 100%;
        }
        
        .btn-calculate:hover {
            transform: translateY(-2px);
            box-shadow: 0 5px 15px rgba(160,70,87,0.3);
            color: #fff;
        }
        
        .btn-save {
            background: #28a745;
            color: #fff;
            border: none;
            padding: 12px 30px;
            border-radius: 10px;
            font-weight: 600;
            font-size: 16px;
            cursor: pointer;
            transition: all 0.3s;
            width: 100%;
        }
        
        .btn-save:hover {
            background: #218838;
            transform: translateY(-2px);
        }
        
        /* Alerts */
        .alert-custom {
            padding: 15px 20px;
            border-radius: 10px;
            margin-bottom: 20px;
            display: flex;
            align-items: center;
            gap: 10px;
            font-weight: 500;
        }
        
        .alert-success {
            background: #d4edda;
            color: #155724;
            border-left: 4px solid #28a745;
        }
        
        .alert-danger {
            background: #f8d7da;
            color: #721c24;
            border-left: 4px solid #dc3545;
        }
        
        /* Table */
        .table {
            margin-bottom: 0;
        }
        
        .table th {
            background: #f8f9fa;
            font-weight: 700;
            color: #444;
            border-bottom: 2px solid #dee2e6;
        }
        
        .table td {
            vertical-align: middle;
            font-weight: 500;
        }
        
        .badge-gain {
            background: #28a745;
            color: #fff;
            padding: 5px 10px;
            border-radius: 20px;
            font-size: 12px;
            font-weight: 600;
        }
        
        .badge-loss {
            background: #dc3545;
            color: #fff;
            padding: 5px 10px;
            border-radius: 20px;
            font-size: 12px;
            font-weight: 600;
        }
        
        .badge-neutral {
            background: #ffc107;
            color: #856404;
            padding: 5px 10px;
            border-radius: 20px;
            font-size: 12px;
            font-weight: 600;
        }
        
        /* Stock Info */
        .stock-info {
            background: #e3f2fd;
            padding: 12px 15px;
            border-radius: 10px;
            margin-top: 10px;
            font-weight: 600;
            color: #1565c0;
        }
        
        .required {
            color: #dc3545;
        }
        
        /* Responsive */
        @media (max-width: 768px) {
            .main-container {
                padding: 15px;
            }
            
            .page-header {
                padding: 20px;
            }
            
            .page-header h2 {
                font-size: 22px;
            }
            
            .result-value {
                font-size: 16px;
            }
            
            .table {
                font-size: 12px;
            }
            
            .btn-calculate, .btn-save {
                padding: 10px 20px;
                font-size: 14px;
            }
        }
        
        /* Scrollbar */
        ::-webkit-scrollbar {
            width: 8px;
            height: 8px;
        }
        
        ::-webkit-scrollbar-track {
            background: #f1f1f1;
        }
        
        ::-webkit-scrollbar-thumb {
            background: #A04657;
            border-radius: 4px;
        }
        
        ::-webkit-scrollbar-thumb:hover {
            background: #6b2a36;
        }
    </style>
</head>
<body>
<div class="main-container">
    <div class="container-fluid">
        <!-- Page Header -->
        <div class="page-header">
            <div>
                <h2><i class="fas fa-chart-line"></i> Rate Gain / Loss Module</h2>
                <p>Manage fuel rate revisions and calculate profit/loss based on current stock</p>
            </div>
            <div>
                <i class="fas fa-gas-pump" style="font-size: 48px; opacity: 0.5;"></i>
            </div>
        </div>
        
        <!-- Alerts -->
        <?php if($success_message): ?>
            <div class="alert-custom alert-success">
                <i class="fas fa-check-circle fa-lg"></i>
                <?php echo $success_message; ?>
            </div>
        <?php endif; ?>
        
        <?php if($error_message): ?>
            <div class="alert-custom alert-danger">
                <i class="fas fa-exclamation-triangle fa-lg"></i>
                <?php echo $error_message; ?>
            </div>
        <?php endif; ?>
        
        <div class="row">
            <!-- Rate Revision Form -->
            <div class="col-lg-5">
                <div class="form-card">
                    <div class="card-header-custom">
                        <h4><i class="fas fa-plus-circle"></i> New Rate Revision</h4>
                    </div>
                    <div class="card-body-custom">
                        <form method="POST" action="">
                            <div class="mb-3">
                                <label class="form-label">
                                    <i class="fas fa-oil-can"></i> Select Fuel Type <span class="required">*</span>
                                </label>
                                <select name="fuel_type_id" id="fuel_type_id" class="form-select" required>
                                    <option value="">-- Select Fuel Type --</option>
                                    <?php 
                                    mysqli_data_seek($fuel_result, 0);
                                    while($fuel = mysqli_fetch_assoc($fuel_result)): 
                                        $selected = ($selected_fuel_id == $fuel['id']) ? 'selected' : '';
                                    ?>
                                        <option value="<?php echo $fuel['id']; ?>" <?php echo $selected; ?>>
                                            <?php echo htmlspecialchars($fuel['name']); ?> 
                                            (Rs <?php echo number_format($fuel['sale_price'], 2); ?>/L)
                                        </option>
                                    <?php endwhile; ?>
                                </select>
                            </div>
                            
                            <div class="mb-3">
                                <label class="form-label">
                                    <i class="fas fa-tag"></i> Current Rate (Old Rate)
                                </label>
                                <input type="text" class="form-control" id="old_rate" value="Rs <?php echo number_format($old_rate, 2); ?>" readonly>
                                <input type="hidden" name="old_rate_hidden" id="old_rate_hidden" value="<?php echo $old_rate; ?>">
                            </div>
                            
                            <div class="mb-3">
                                <label class="form-label">
                                    <i class="fas fa-arrow-up"></i> New Rate <span class="required">*</span>
                                </label>
                                <input type="number" step="0.01" name="new_rate" id="new_rate" class="form-control" 
                                       placeholder="Enter new rate per liter" required>
                            </div>
                            
                            <div class="stock-info">
                                <i class="fas fa-database"></i> Current Stock Available: 
                                <strong><?php echo number_format($current_stock, 2); ?> Liters</strong>
                            </div>
                            
                            <div class="mt-4">
                                <button type="submit" name="calculate" class="btn-calculate">
                                    <i class="fas fa-calculator"></i> Calculate Gain/Loss
                                </button>
                            </div>
                        </form>
                        
                        <!-- Calculation Result -->
                        <?php if($calculation_result): ?>
                            <div class="result-card">
                                <div class="result-title">
                                    <i class="fas fa-chart-line"></i> Calculation Result
                                </div>
                                <div class="result-item">
                                    <span class="result-label">Fuel Type:</span>
                                    <span class="result-value">
                                        <?php 
                                        $fuel_name_sql = "SELECT name FROM fuel_types WHERE id = '{$calculation_result['fuel_type_id']}'";
                                        $fuel_name_result = mysqli_query($conn, $fuel_name_sql);
                                        $fuel_name_data = mysqli_fetch_assoc($fuel_name_result);
                                        echo htmlspecialchars($fuel_name_data['name']);
                                        ?>
                                    </span>
                                </div>
                                <div class="result-item">
                                    <span class="result-label">Old Rate:</span>
                                    <span class="result-value">Rs <?php echo number_format($calculation_result['old_rate'], 2); ?>/L</span>
                                </div>
                                <div class="result-item">
                                    <span class="result-label">New Rate:</span>
                                    <span class="result-value">Rs <?php echo number_format($calculation_result['new_rate'], 2); ?>/L</span>
                                </div>
                                <div class="result-item">
                                    <span class="result-label">Rate Difference:</span>
                                    <span class="result-value <?php echo $calculation_result['rate_diff'] > 0 ? 'gain' : ($calculation_result['rate_diff'] < 0 ? 'loss' : 'neutral'); ?>">
                                        Rs <?php echo number_format($calculation_result['rate_diff'], 2); ?>/L
                                        <?php if($calculation_result['rate_diff'] > 0): ?>
                                            <i class="fas fa-arrow-up"></i>
                                        <?php elseif($calculation_result['rate_diff'] < 0): ?>
                                            <i class="fas fa-arrow-down"></i>
                                        <?php endif; ?>
                                    </span>
                                </div>
                                <div class="result-item">
                                    <span class="result-label">Current Stock:</span>
                                    <span class="result-value"><?php echo number_format($calculation_result['current_stock'], 2); ?> Liters</span>
                                </div>
                                <div class="result-item">
                                    <span class="result-label">Total Gain/Loss:</span>
                                    <span class="result-value <?php echo $calculation_result['gain_loss'] > 0 ? 'gain' : ($calculation_result['gain_loss'] < 0 ? 'loss' : 'neutral'); ?>">
                                        <strong>Rs <?php echo number_format(abs($calculation_result['gain_loss']), 2); ?></strong>
                                        <?php if($calculation_result['gain_loss'] > 0): ?>
                                            <span class="gain-bg"><i class="fas fa-chart-line"></i> GAIN</span>
                                        <?php elseif($calculation_result['gain_loss'] < 0): ?>
                                            <span class="loss-bg"><i class="fas fa-chart-line"></i> LOSS</span>
                                        <?php else: ?>
                                            <span class="neutral-bg"><i class="fas fa-minus"></i> NEUTRAL</span>
                                        <?php endif; ?>
                                    </span>
                                </div>
                                
                                <form method="POST" action="" class="mt-3">
                                    <input type="hidden" name="fuel_type_id" value="<?php echo $calculation_result['fuel_type_id']; ?>">
                                    <input type="hidden" name="old_rate" value="<?php echo $calculation_result['old_rate']; ?>">
                                    <input type="hidden" name="new_rate" value="<?php echo $calculation_result['new_rate']; ?>">
                                    <input type="hidden" name="stock_quantity" value="<?php echo $calculation_result['current_stock']; ?>">
                                    <input type="hidden" name="gain_loss" value="<?php echo $calculation_result['gain_loss']; ?>">
                                    <button type="submit" name="save_revision" class="btn-save">
                                        <i class="fas fa-save"></i> Confirm & Save Revision
                                    </button>
                                </form>
                            </div>
                        <?php endif; ?>
                    </div>
                </div>
            </div>
            
            <!-- Rate Revision History -->
            <div class="col-lg-7">
                <div class="table-card">
                    <div class="card-header-custom">
                        <h4><i class="fas fa-history"></i> Rate Revision History</h4>
                    </div>
                    <div class="card-body-custom">
                        <div class="table-responsive">
                            <table class="table table-hover">
                                <thead>
                                    <tr>
                                        <th>ID</th>
                                        <th>Fuel Type</th>
                                        <th>Old Rate</th>
                                        <th>New Rate</th>
                                        <th>Stock (Liters)</th>
                                        <th>Gain/Loss</th>
                                        <th>Date & Time</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php if(mysqli_num_rows($revisions_result) > 0): ?>
                                        <?php while($revision = mysqli_fetch_assoc($revisions_result)): ?>
                                            <tr>
                                                <td>#<?php echo $revision['id']; ?></td>
                                                <td>
                                                    <strong><?php echo htmlspecialchars($revision['fuel_name']); ?></strong>
                                                </td>
                                                <td>Rs <?php echo number_format($revision['old_rate'], 2); ?></td>
                                                <td>Rs <?php echo number_format($revision['new_rate'], 2); ?></td>
                                                <td><?php echo number_format($revision['stock_quantity'], 2); ?></td>
                                                <td>
                                                    <?php if($revision['gain_loss'] > 0): ?>
                                                        <span class="badge-gain">
                                                            <i class="fas fa-arrow-up"></i> +Rs <?php echo number_format($revision['gain_loss'], 2); ?>
                                                        </span>
                                                    <?php elseif($revision['gain_loss'] < 0): ?>
                                                        <span class="badge-loss">
                                                            <i class="fas fa-arrow-down"></i> -Rs <?php echo number_format(abs($revision['gain_loss']), 2); ?>
                                                        </span>
                                                    <?php else: ?>
                                                        <span class="badge-neutral">
                                                            Rs 0.00
                                                        </span>
                                                    <?php endif; ?>
                                                </td>
                                                <td>
                                                    <i class="fas fa-calendar-alt"></i>
                                                    <?php echo date('d-m-Y', strtotime($revision['date_time'])); ?>
                                                    <br>
                                                    <small><?php echo date('H:i:s', strtotime($revision['date_time'])); ?></small>
                                                </td>
                                            </tr>
                                        <?php endwhile; ?>
                                    <?php else: ?>
                                        <tr>
                                            <td colspan="7" class="text-center py-5">
                                                <i class="fas fa-chart-line" style="font-size: 48px; color: #ccc;"></i>
                                                <p class="mt-3 mb-0">No rate revisions recorded yet</p>
                                                <small class="text-muted">Create a new rate revision to see history here</small>
                                            </td>
                                        </tr>
                                    <?php endif; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<script>
    // Auto-submit when fuel type changes to fetch stock
    document.getElementById('fuel_type_id').addEventListener('change', function() {
        this.form.submit();
    });
    
    // Highlight new rate field
    const newRateField = document.getElementById('new_rate');
    if(newRateField) {
        newRateField.addEventListener('focus', function() {
            this.style.borderColor = '#A04657';
        });
    }
</script>

<?php include('../includes/ft.php'); ?>
</body>
</html>