ISP-Billing/delete_orphaned_customers_comprehensive.php

<?php
/**
 * Comprehensive Script to Identify and Delete Orphaned Customers
 * 
 * This script identifies customers that have no relationships in other tables
 * and provides a safe way to delete them with confirmation.
 * 
 * An orphaned customer is one that has:
 * - No records in tbl_user_recharges
 * - No records in tbl_transactions
 * - No records in tbl_customers_fields
 * - No records in tbl_payment_gateway
 */

include "init.php";

echo "=== COMPREHENSIVE ORPHANED CUSTOMER DETECTION SCRIPT ===\n";
echo "Date: " . date('Y-m-d H:i:s') . "\n\n";

// Function to check if a customer has any relationships
function isCustomerOrphaned($customerId, $username) {
    $relationships = [];
    
    // Check tbl_user_recharges
    $recharges = ORM::for_table('tbl_user_recharges')->where('customer_id', $customerId)->count();
    if ($recharges > 0) {
        $relationships[] = "tbl_user_recharges ($recharges records)";
    }
    
    // Check tbl_transactions
    $transactions = ORM::for_table('tbl_transactions')->where('username', $username)->count();
    if ($transactions > 0) {
        $relationships[] = "tbl_transactions ($transactions records)";
    }
    
    // Check tbl_customers_fields
    $fields = ORM::for_table('tbl_customers_fields')->where('customer_id', $customerId)->count();
    if ($fields > 0) {
        $relationships[] = "tbl_customers_fields ($fields records)";
    }
    
    // Check tbl_payment_gateway
    $payments = ORM::for_table('tbl_payment_gateway')->where('username', $username)->count();
    if ($payments > 0) {
        $relationships[] = "tbl_payment_gateway ($payments records)";
    }
    
    return [
        'is_orphaned' => empty($relationships),
        'relationships' => $relationships
    ];
}

// Get all customers
echo "Scanning all customers for orphaned records...\n";
$allCustomers = ORM::for_table('tbl_customers')->find_many();

$orphanedCustomers = [];
$totalCustomers = count($allCustomers);

echo "Total customers in database: $totalCustomers\n\n";

foreach ($allCustomers as $customer) {
    $result = isCustomerOrphaned($customer['id'], $customer['username']);
    
    if ($result['is_orphaned']) {
        $orphanedCustomers[] = [
            'id' => $customer['id'],
            'username' => $customer['username'],
            'fullname' => $customer['fullname'],
            'email' => $customer['email'],
            'status' => $customer['status'],
            'created_at' => $customer['created_at'] ?? 'Unknown'
        ];
    }
}

echo "=== ORPHANED CUSTOMERS FOUND ===\n";
echo "Orphaned customers: " . count($orphanedCustomers) . "\n\n";

if (empty($orphanedCustomers)) {
    echo "✅ No orphaned customers found! All customers have relationships in other tables.\n";
    exit(0);
}

// Display orphaned customers
echo "Orphaned customers details:\n";
echo str_repeat("-", 120) . "\n";
printf("%-5s %-20s %-30s %-30s %-15s %-20s\n", "ID", "Username", "Full Name", "Email", "Status", "Created");
echo str_repeat("-", 120) . "\n";

foreach ($orphanedCustomers as $customer) {
    printf("%-5s %-20s %-30s %-30s %-15s %-20s\n", 
        $customer['id'], 
        substr($customer['username'], 0, 20),
        substr($customer['fullname'], 0, 30),
        substr($customer['email'], 0, 30),
        $customer['status'],
        $customer['created_at']
    );
}

echo str_repeat("-", 120) . "\n\n";

// Ask for confirmation
echo "⚠️  WARNING: This will permanently delete " . count($orphanedCustomers) . " orphaned customers!\n";
echo "These customers have no relationships in any other tables.\n\n";

echo "Do you want to proceed with deletion? (yes/no): ";
$handle = fopen("php://stdin", "r");
$confirmation = trim(fgets($handle));
fclose($handle);

if (strtolower($confirmation) !== 'yes') {
    echo "❌ Deletion cancelled by user.\n";
    exit(0);
}

echo "\n=== PROCEEDING WITH DELETION ===\n";

$deletedCount = 0;
$errorCount = 0;

foreach ($orphanedCustomers as $customer) {
    try {
        echo "Deleting: ID {$customer['id']} - {$customer['username']} ({$customer['fullname']})... ";
        
        // Double-check: Verify no relationships exist (safety check)
        $result = isCustomerOrphaned($customer['id'], $customer['username']);
        
        if (!$result['is_orphaned']) {
            echo "❌ HAS RELATIONS - SKIPPING\n";
            $errorCount++;
            continue;
        }
        
        // Get the customer record and delete it
        $customerRecord = ORM::for_table('tbl_customers')->find_one($customer['id']);
        if ($customerRecord) {
            $customerRecord->delete();
            echo "✅ DELETED\n";
            $deletedCount++;
        } else {
            echo "❌ NOT FOUND\n";
            $errorCount++;
        }
        
    } catch (Exception $e) {
        echo "❌ ERROR: " . $e->getMessage() . "\n";
        $errorCount++;
    }
}

echo "\n=== DELETION SUMMARY ===\n";
echo "Total orphaned customers found: " . count($orphanedCustomers) . "\n";
echo "Successfully deleted: $deletedCount\n";
echo "Errors/Skipped: $errorCount\n";

if ($deletedCount > 0) {
    echo "\n✅ Deletion completed successfully!\n";
    echo "Removed $deletedCount orphaned customers from the database.\n";
} else {
    echo "\n⚠️ No customers were deleted.\n";
}

echo "\n=== VERIFICATION ===\n";
$remainingCustomers = ORM::for_table('tbl_customers')->count();
echo "Remaining customers in database: $remainingCustomers\n";

echo "\n=== SCRIPT COMPLETE ===\n";
?>