<?php
session_start();
include 'layouts/dbconn.php';

// Check if user is logged in
if (!isset($_SESSION['user_id'])) {
    header('Location: login.php');
    exit();
}

// Check if the request method is POST
if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    header('Location: container_deposit_refund.php');
    exit();
}

// Get user ID for tracking
$user_id = $_SESSION['user_id'];

// Collect form data
$bl = isset($_POST['bl']) ? trim($_POST['bl']) : '';
$refund_amount = isset($_POST['refund_amount']) ? floatval($_POST['refund_amount']) : 0;
$refund_date = !empty($_POST['refund_date']) ? date('Y-m-d', strtotime($_POST['refund_date'])) : null;
$remarks = isset($_POST['remarks']) ? trim($_POST['remarks']) : '';

// For debugging
error_log("Processing deposit refund for BL: $bl");
error_log("Refund Amount: $refund_amount, Refund Date: $refund_date");

// Validation
if (empty($bl)) {
    $_SESSION['error_message'] = "BL number is required.";
    header('Location: container_deposit_refund.php');
    exit();
}

if ($refund_amount <= 0) {
    $_SESSION['error_message'] = "Refund amount must be greater than zero.";
    header('Location: container_deposit_refund.php');
    exit();
}

if (empty($refund_date)) {
    $_SESSION['error_message'] = "Refund date is required.";
    header('Location: container_deposit_refund.php');
    exit();
}

try {
    // Start transaction
    $conn->begin_transaction();
    
    // Get current deposit information
    $query_data = "SELECT 
                    id,
                    shipping_line, 
                    type, 
                    container, 
                    deposit, 
                    refund_amount, 
                    balance
                  FROM container_deposit 
                  WHERE bl = ? 
                  ORDER BY id DESC 
                  LIMIT 1";
    $stmt = $conn->prepare($query_data);
    $stmt->bind_param("s", $bl);
    $stmt->execute();
    $result_data = $stmt->get_result();
    
    if (!$result_data || $result_data->num_rows === 0) {
        throw new Exception("Deposit record not found for BL: $bl");
    }
    
    $deposit_data = $result_data->fetch_assoc();
    $deposit_id = $deposit_data['id'];
    $shipping_line = $deposit_data['shipping_line'];
    $type = $deposit_data['type'];
    $container = $deposit_data['container'];
    $deposit_amount = $deposit_data['deposit'];
    $current_refund = $deposit_data['refund_amount'] ?? 0;
    $current_balance = $deposit_data['balance'] ?? null;
    
    // If balance is null, calculate it
    if ($current_balance === null) {
        $current_balance = $deposit_amount - $current_refund;
    }
    
    // Verify the refund amount is valid
    if ($refund_amount > $current_balance) {
        throw new Exception("Refund amount ($refund_amount) cannot exceed the current balance ($current_balance).");
    }
    
    // Calculate new values
    $new_refund_total = $current_refund + $refund_amount;
    $new_balance = $current_balance - $refund_amount;
    
    // Generate remarks addition with username and date
    $user_query = "SELECT username FROM users WHERE user_id = ?";
    $user_stmt = $conn->prepare($user_query);
    $user_stmt->bind_param('i', $user_id);
    $user_stmt->execute();
    $user_result = $user_stmt->get_result();
    $username = ($user_result->num_rows > 0) ? $user_result->fetch_assoc()['username'] : 'unknown';
    
    $current_date = date('Y-m-d');
    // Format remarks with the refund information, username and date
    $refund_info = "Refund of " . number_format($refund_amount, 2) . " requested on " . $refund_date;
    if (!empty($remarks)) {
        $refund_info .= ". " . $remarks;
    }
    $remarks_addition = "\n" . $refund_info . " - " . $username . " on " . $current_date;
    
    // Check if status column exists, if not add it
    $column_check = $conn->query("SHOW COLUMNS FROM container_deposit LIKE 'status'");
    if ($column_check->num_rows == 0) {
        // Add status column if it doesn't exist
        $alter_table_sql = "ALTER TABLE container_deposit ADD COLUMN status VARCHAR(20) DEFAULT 'Pending' AFTER refund_date";
        if (!$conn->query($alter_table_sql)) {
            error_log("Warning: Failed to add status column: " . $conn->error);
        }
        
        // Add other required columns
        $alter_table_sql = "ALTER TABLE container_deposit 
                           ADD COLUMN created_by INT AFTER status,
                           ADD COLUMN created_at DATETIME AFTER created_by,
                           ADD COLUMN updated_at DATETIME AFTER created_at,
                           ADD COLUMN approved_by INT AFTER updated_at,
                           ADD COLUMN approved_at DATETIME AFTER approved_by,
                           ADD COLUMN admin_remarks TEXT AFTER approved_at";
        
        if (!$conn->query($alter_table_sql)) {
            error_log("Warning: Failed to add approval columns: " . $conn->error);
        }
    }
    
    // Update the deposit record with Pending status
    $update_sql = "UPDATE container_deposit 
                  SET refund_amount = ?, 
                      balance = ?,
                      refund_date = ?,
                      status = 'Pending',
                      created_by = ?,
                      created_at = NOW(),
                      updated_at = NOW(),
                      remarks = CONCAT(IFNULL(remarks, ''), ?)
                  WHERE id = ?";
    
    $stmt = $conn->prepare($update_sql);
    $stmt->bind_param("ddsisi", $new_refund_total, $new_balance, $refund_date, $user_id, $remarks_addition, $deposit_id);
    
    if (!$stmt->execute()) {
        throw new Exception("Error updating deposit refund: " . $stmt->error);
    }
    
    // Check if any rows were affected
    if ($stmt->affected_rows == 0) {
        throw new Exception("No records were updated. The deposit record might have been modified by another user.");
    }
    
    // Create a log entry for the refund transaction
    $log_sql = "INSERT INTO deposit_refund_log 
               (deposit_id, bl, refund_amount, refund_date, processed_by, remarks, status, created_at) 
               VALUES (?, ?, ?, ?, ?, ?, 'Pending', NOW())";
    
    // Check if deposit_refund_log table exists, if not, create it
    $table_check = $conn->query("SHOW TABLES LIKE 'deposit_refund_log'");
    
    if ($table_check->num_rows == 0) {
        // Create the table
        $create_table_sql = "CREATE TABLE IF NOT EXISTS `deposit_refund_log` (
            `log_id` int NOT NULL AUTO_INCREMENT,
            `deposit_id` int NOT NULL,
            `bl` varchar(50) NOT NULL,
            `refund_amount` decimal(10,2) NOT NULL,
            `refund_date` date NOT NULL,
            `processed_by` int NOT NULL,
            `remarks` text,
            `status` varchar(20) DEFAULT 'Pending',
            `approved_by` int DEFAULT NULL,
            `approved_at` datetime DEFAULT NULL,
            `admin_remarks` text,
            `created_at` datetime NOT NULL,
            PRIMARY KEY (`log_id`),
            KEY `deposit_id` (`deposit_id`),
            KEY `processed_by` (`processed_by`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
        
        if (!$conn->query($create_table_sql)) {
            error_log("Warning: Failed to create deposit_refund_log table: " . $conn->error);
        }
    } else {
        // Check if status column exists in the log table
        $column_check = $conn->query("SHOW COLUMNS FROM deposit_refund_log LIKE 'status'");
        if ($column_check->num_rows == 0) {
            // Add status column
            $alter_log_sql = "ALTER TABLE deposit_refund_log ADD COLUMN status VARCHAR(20) DEFAULT 'Pending' AFTER remarks";
            if (!$conn->query($alter_log_sql)) {
                error_log("Warning: Failed to add status column to log table: " . $conn->error);
            }
        }
    }
    
    // Try to log the refund
    try {
        $log_stmt = $conn->prepare($log_sql);
        $log_stmt->bind_param("isdsss", $deposit_id, $bl, $refund_amount, $refund_date, $user_id, $remarks);
        $log_stmt->execute();
    } catch (Exception $log_e) {
        // Just log this error but don't fail the whole transaction
        error_log("Warning: Failed to create refund log: " . $log_e->getMessage());
    }
    
    // Create notification for admin
    /*$table_check = $conn->query("SHOW TABLES LIKE 'notifications'");
    if ($table_check->num_rows > 0) {
        $notification_message = "New container deposit refund request for BL: $bl requires approval.";
        
        $query_notification = "INSERT INTO notifications (user_id, message, type, reference_id, created_at) 
                              VALUES (?, ?, 'deposit_refund', ?, NOW())";
        
        $stmt_notification = $conn->prepare($query_notification);
        $stmt_notification->bind_param("isi", $user_id, $notification_message, $deposit_id);
        
        if (!$stmt_notification->execute()) {
            // Log error but continue
            error_log("Failed to create notification: " . $stmt_notification->error);
        }
    }*/
    
    // Commit transaction
    $conn->commit();
    
    // Success - set session message
    $_SESSION['success_message'] = "Container deposit refund of $refund_amount requested successfully and pending approval.";
    
    // Redirect back
    header('Location: container_depo_list.php');
    exit();
    
} catch (Exception $e) {
    // Rollback transaction on error
    $conn->rollback();
    
    // Log the error
    error_log("Error processing deposit refund: " . $e->getMessage());
    
    // Show error message
    $_SESSION['error_message'] = "Error: " . $e->getMessage();
    header('Location: container_deposit_refund.php');
    exit();
}
?>