<?php
$page_title = 'Check for Duplicates';
require_once('includes/load.php');
page_require_level(3);

// Function to get duplicate records
function get_duplicates() {
    global $db;
    $sql = "SELECT resi, part_number, quantity, COUNT(*) as count, MIN(date_scan) as date_upload
            FROM awb
            GROUP BY resi, part_number, quantity
            HAVING count > 1
            ORDER BY date_upload DESC"; // Order by date_upload in descending order
    return find_by_sql($sql);
}

// Function to handle delete request
if (isset($_GET['delete'])) {
    $resi = $db->escape($_GET['resi']);
    $part_number = $db->escape($_GET['part_number']);
    $quantity = $db->escape($_GET['quantity']);

    // Delete the duplicate records
    $sql = "DELETE FROM awb 
            WHERE resi = '$resi' 
            AND part_number = '$part_number' 
            AND quantity = '$quantity'";
    $db->query($sql);

    $session->msg('s', "Duplicates deleted successfully.");
    redirect('check_double.php', false);
}

$duplicates = get_duplicates();
include_once('layouts/header.php');
?>

<div class="row">
    <div class="col-md-12">
        <?php echo display_msg($msg); ?>
    </div>
</div>

<div class="row">
    <div class="col-md-12">
        <div class="panel panel-default">
            <div class="panel-heading clearfix">
                <h3 class="panel-title">Duplicate Records</h3>
            </div>
            <div class="panel-body">
                <?php if (!empty($duplicates)): ?>
                <table class="table table-bordered">
                    <thead>
                        <tr>
                            <th class="text-center">#</th>
                            <th>Resi</th>
                            <th>Part Number</th>
                            <th>Quantity</th>
                            <th>Date Upload</th>
                            <th>Action</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($duplicates as $index => $duplicate): ?>
                            <tr>
                                <td class="text-center"><?php echo $index + 1; ?></td>
                                <td><?php echo htmlspecialchars($duplicate['resi']); ?></td>
                                <td><?php echo htmlspecialchars($duplicate['part_number']); ?></td>
                                <td><?php echo htmlspecialchars($duplicate['quantity']); ?></td>
                                <td><?php echo htmlspecialchars($duplicate['date_upload']); ?></td>
                                <td>
                                    <a href="view_details.php?resi=<?php echo urlencode($duplicate['resi']); ?>&part_number=<?php echo urlencode($duplicate['part_number']); ?>&quantity=<?php echo urlencode($duplicate['quantity']); ?>" class="btn btn-info btn-sm">
                                        View Details
                                    </a>
                                    <a href="check_double.php?delete=true&resi=<?php echo urlencode($duplicate['resi']); ?>&part_number=<?php echo urlencode($duplicate['part_number']); ?>&quantity=<?php echo urlencode($duplicate['quantity']); ?>" class="btn btn-danger btn-sm" onclick="return confirm('Are you sure you want to delete these duplicates?');">
                                        Delete
                                    </a>
                                </td>
                            </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
                <?php else: ?>
                <p>No duplicate records found.</p>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>

<?php include_once('layouts/footer.php'); ?>
