<?php
require 'vendor/autoload.php';

$dotenv = Dotenv\Dotenv::createImmutable(__DIR__);
$dotenv->load();

$pdo = new PDO(
    'mysql:host='.$_ENV['DB_HOST'].';dbname='.$_ENV['DB_DATABASE'],
    $_ENV['DB_USERNAME'],
    $_ENV['DB_PASSWORD']
);

echo "=== Removing Duplicate Invoice #30 ===\n\n";

// Show invoice details before deletion
$stmt = $pdo->query('SELECT id, invoice_number, amount, paid_at, created_at FROM invoices WHERE id = 30');
$inv = $stmt->fetch();

if ($inv) {
    echo "Invoice to delete:\n";
    echo "  ID: {$inv['id']}\n";
    echo "  Invoice Number: {$inv['invoice_number']}\n";
    echo "  Amount: \${$inv['amount']}\n";
    echo "  Paid At: {$inv['paid_at']}\n";
    echo "  Created At: {$inv['created_at']}\n\n";
    
    // Delete associated transactions first
    $stmt = $pdo->prepare('DELETE FROM transactions WHERE invoice_id = 30');
    $stmt->execute();
    $txnDeleted = $stmt->rowCount();
    echo "Deleted $txnDeleted transaction(s) linked to invoice #30\n";
    
    // Delete the invoice
    $stmt = $pdo->prepare('DELETE FROM invoices WHERE id = 30');
    $stmt->execute();
    echo "Deleted invoice #30\n\n";
    
    echo "✓ Duplicate invoice removed successfully!\n\n";
} else {
    echo "Invoice #30 not found.\n\n";
}

echo "=== Remaining Invoices for User 17 ===\n";
$stmt = $pdo->query('
    SELECT id, invoice_number, amount, paid_at 
    FROM invoices 
    WHERE user_id = 17 
    ORDER BY id ASC
');

$count = 0;
while ($inv = $stmt->fetch()) {
    $count++;
    echo "Invoice #{$inv['id']}: {$inv['invoice_number']} - \${$inv['amount']} - Paid: {$inv['paid_at']}\n";
}

echo "\nTotal Invoices: $count\n";
