<?php
$page_title = 'Delete Invoice by Excel';
require_once('includes/load.php');
require 'vendor/autoload.php'; // Autoload PHPSpreadsheet

use PhpOfficePhpSpreadsheetIOFactory;

page_require_level(15); // Require level 15 access

// Initialize variables
$errors = [];
$success_msg = '';
$deleted_count = 0;

// Handle Excel upload and deletion
if (isset($_POST['delete_invoices'])) {
    if (isset($_FILES['excel_file']) && $_FILES['excel_file']['error'] == 0) {
        $excel_file = $_FILES['excel_file']['tmp_name'];
        
        try {
            $spreadsheet = IOFactory::load($excel_file);
            $sheet = $spreadsheet->getActiveSheet();
            $rows = $sheet->toArray(null, true, true, true);
            array_shift($rows); // Skip header row
            
            foreach ($rows as $row) {
                $invoice = trim($row['A']);
                
                if (!empty($invoice)) {
                    $invoice = $db->escape($invoice);
                    
                    // Delete from awb table
                    $delete_sql = "DELETE FROM awb WHERE invoice = '$invoice'";
                    if ($db->query($delete_sql)) {
                        $deleted_count += $db->affected_rows();
                    } else {
                        $errors[] = "Failed to delete invoice: $invoice. Error: " . $db->get_last_error();
                    }
                }
            }
            
            if (empty($errors)) {
                $session->msg('s', "Successfully deleted $deleted_count invoice records.");
                redirect('delete_invoice.php', false);
            }
            
        } catch (Exception $e) {
            $errors[] = "Error processing Excel file: " . $e->getMessage();
        }
    } else {
        $errors[] = "Please upload a valid Excel file.";
    }
}

include_once('layouts/header.php');
?>

<div class="row">
    <div class="col-md-12">
        <?php echo display_msg($msg); ?>
        <?php if (!empty($errors)): ?>
            <div class="alert alert-danger">
                <ul>
                    <?php foreach ($errors as $error): ?>
                        <li><?php echo htmlspecialchars($error); ?></li>
                    <?php endforeach; ?>
                </ul>
            </div>
        <?php endif; ?>
    </div>
</div>

<div class="row">
    <div class="col-md-12">
        <div class="panel panel-default">
            <div class="panel-heading clearfix">
                <h3 class="panel-title">Delete Invoices by Excel Import</h3>
            </div>
            <div class="panel-body">
                <div class="col-md-6 col-md-offset-3">
                    <form method="post" action="delete_invoice.php" enctype="multipart/form-data" class="form-horizontal">
                        <div class="form-group">
                            <label class="control-label">Excel File</label>
                            <input type="file" name="excel_file" class="form-control" accept=".xlsx, .xls" required>
                            <small class="help-block">
                                <strong>Format:</strong> Column A should contain invoice numbers to delete.<br>
                                <strong>Warning:</strong> This will permanently delete all records with matching invoices!
                            </small>
                        </div>
                        <div class="form-group text-center">
                            <button type="submit" name="delete_invoices" class="btn btn-danger">
                                <i class="fa fa-trash"></i> Delete Invoices
                            </button>
                        </div>
                    </form>
                </div>
                
                <?php if ($deleted_count > 0): ?>
                    <div class="alert alert-success text-center">
                        Successfully deleted <?php echo $deleted_count; ?> invoice records.
                    </div>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>

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