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

// Check if user is logged in and has admin role
if (!isset($_SESSION['user_id']) || $_SESSION['role_id'] != 1) {
    $_SESSION['error_message'] = "You don't have permission to delete files.";
    header('Location: index.php');
    exit();
}

// Get the file ID from the request
if (!isset($_GET['file_id']) || empty($_GET['file_id'])) {
    $_SESSION['error_message'] = "Invalid request. File ID is missing.";
    header('Location: delete_record.php');
    exit();
}

$file_id = intval($_GET['file_id']);

// Insert into admin_logs table
$log_sql = "INSERT INTO admin_logs (user_id, action, details, ip_address, created_at) 
            VALUES (?, 'file_delete_attempt', ?, ?, NOW())";
$log_stmt = $conn->prepare($log_sql);
$details = "User attempted to delete file ID: $file_id";
$log_stmt->bind_param('iss', $user_id, $details, $ip_address);
$log_stmt->execute();

// Begin transaction
$conn->begin_transaction();

try {
    // First, get the file number and check if file exists
    $sql_get_file = "SELECT file_number, `local/transit` FROM files WHERE id = ?";
    $stmt_get_file = $conn->prepare($sql_get_file);
    $stmt_get_file->bind_param('i', $file_id);
    $stmt_get_file->execute();
    $result_file = $stmt_get_file->get_result();
    
    if ($result_file->num_rows === 0) {
        throw new Exception("File not found.");
    }
    
    $file_data = $result_file->fetch_assoc();
    $file_number = $file_data['file_number'];
    $is_local_file = ($file_data['local/transit'] == 1);
    
    // Double-check that the file has no related records in proforma, pre_proforma, or invoices
    $check_sql = "
        SELECT 
            (SELECT COUNT(*) FROM pre_proforma WHERE file_number = ?) +
            (SELECT COUNT(*) FROM proforma WHERE file_number = ?) +
            (SELECT COUNT(*) FROM invoices WHERE file_number = ?) as record_count
    ";
    
    $check_stmt = $conn->prepare($check_sql);
    $check_stmt->bind_param('sss', $file_number, $file_number, $file_number);
    $check_stmt->execute();
    $result_check = $check_stmt->get_result();
    $record_count = $result_check->fetch_assoc()['record_count'];
    
    if ($record_count > 0) {
        throw new Exception("This file cannot be deleted because it has related financial records.");
    }
    
    // Get user information
    $user_id = $_SESSION['user_id'];
    $sql_user = "SELECT full_name FROM users WHERE user_id = ?";
    $stmt_user = $conn->prepare($sql_user);
    $stmt_user->bind_param('i', $user_id);
    $stmt_user->execute();
    $result_user = $stmt_user->get_result();
    $deleted_by = $result_user->fetch_assoc()['full_name'];
    
    // Log the deletion in deleted_records table
    $sql_log = "INSERT INTO deleted_records (file_number, date_deleted, deleted_by) VALUES (?, NOW(), ?)";
    $stmt_log = $conn->prepare($sql_log);
    $stmt_log->bind_param('ss', $file_number, $deleted_by);
    $stmt_log->execute();
    
    // Delete related records in other tables first
    
    // 1. Delete container details
    $sql_delete_containers = "DELETE FROM container_details WHERE file_number = ?";
    $stmt_delete_containers = $conn->prepare($sql_delete_containers);
    $stmt_delete_containers->bind_param('s', $file_number);
    $stmt_delete_containers->execute();
    
    // 2. Delete charges
    $sql_delete_charges = "DELETE FROM charges WHERE file_number = ?";
    $stmt_delete_charges = $conn->prepare($sql_delete_charges);
    $stmt_delete_charges->bind_param('s', $file_number);
    $stmt_delete_charges->execute();
    
    // 3. Delete delivery_returns
    $sql_delete_delivery = "DELETE FROM delivery_returns WHERE file_number = ?";
    $stmt_delete_delivery = $conn->prepare($sql_delete_delivery);
    $stmt_delete_delivery->bind_param('s', $file_number);
    $stmt_delete_delivery->execute();
    
    // 4. Delete shipping_line records
    $sql_delete_shipping = "DELETE FROM shipping_line WHERE file_no = ?";
    $stmt_delete_shipping = $conn->prepare($sql_delete_shipping);
    $stmt_delete_shipping->bind_param('s', $file_number);
    $stmt_delete_shipping->execute();
    
    // 5. Delete shipment_data records
    $sql_delete_shipment = "DELETE FROM shipment_data WHERE file_no = ?";
    $stmt_delete_shipment = $conn->prepare($sql_delete_shipment);
    $stmt_delete_shipment->bind_param('s', $file_number);
    $stmt_delete_shipment->execute();
    
    // 6. Delete truck_details records
    $sql_delete_truck = "DELETE FROM truck_details WHERE file_no = ?";
    $stmt_delete_truck = $conn->prepare($sql_delete_truck);
    $stmt_delete_truck->bind_param('s', $file_number);
    $stmt_delete_truck->execute();
    
    // 7. Delete notifications
    $sql_delete_notifications = "DELETE FROM notifications WHERE file_number = ?";
    $stmt_delete_notifications = $conn->prepare($sql_delete_notifications);
    $stmt_delete_notifications->bind_param('s', $file_number);
    $stmt_delete_notifications->execute();
    
    // 8. Delete pending_approvals
    $sql_delete_approvals = "DELETE FROM pending_approvals WHERE file_number = ?";
    $stmt_delete_approvals = $conn->prepare($sql_delete_approvals);
    $stmt_delete_approvals->bind_param('s', $file_number);
    $stmt_delete_approvals->execute();
    
    // 9. Check for and delete loose_cargo_items if they exist
    $check_loose_table = $conn->query("SHOW TABLES LIKE 'loose_cargo_items'");
    if ($check_loose_table->num_rows > 0) {
        $sql_delete_loose = "DELETE FROM loose_cargo_items WHERE file_number = ?";
        $stmt_delete_loose = $conn->prepare($sql_delete_loose);
        $stmt_delete_loose->bind_param('s', $file_number);
        $stmt_delete_loose->execute();
    }
    
    // Finally, delete the main file record
    $sql_delete_file = "DELETE FROM files WHERE id = ?";
    $stmt_delete_file = $conn->prepare($sql_delete_file);
    $stmt_delete_file->bind_param('i', $file_id);
    $stmt_delete_file->execute();
    
    // Update local_file_counter for local files
    if ($is_local_file) {
        // Increment the counter by 1
        $sql_update_counter = "UPDATE settings SET setting_value = setting_value - 1 WHERE setting_key = 'local_file_counter'";
        $conn->query($sql_update_counter);
        
        // If the counter update fails, log it but don't fail the whole transaction
        if ($conn->error) {
            error_log("Failed to update local_file_counter: " . $conn->error);
        }
    }
    
    if ($stmt_delete_file->execute()) {
        // Log successful deletion
        $success_log_sql = "INSERT INTO admin_logs (user_id, action, details, ip_address, created_at) 
                            VALUES (?, 'file_delete_success', ?, ?, NOW())";
        $success_log_stmt = $conn->prepare($success_log_sql);
        $success_details = "User successfully deleted file: $file_number";
        $success_log_stmt->bind_param('iss', $user_id, $success_details, $ip_address);
        $success_log_stmt->execute();
    }
    
    // Commit the transaction
    $conn->commit();
    
    $_SESSION['success_message'] = "File {$file_number} has been successfully deleted.";
    header('Location: delete_record.php');
    exit();
    
} catch (Exception $e) {
    // Rollback the transaction on error
    $conn->rollback();
    
    $_SESSION['error_message'] = "Error deleting file: " . $e->getMessage();
    header('Location: delete_record.php');
    exit();
} finally {
    $conn->close();
}
?>