<?php
// Balance History Admin Page Content
require_once '../config.php';

// Set page variables for header
$page_title = 'Store Owner Balance History';
$page_description = 'View and manage store owner balance history and transactions (store owners only, not their agents)';

date_default_timezone_set('GMT');

// Get filter parameters
$filterAgentId = isset($_GET['agent_id']) ? (int)$_GET['agent_id'] : '';
$filterStartDate = isset($_GET['start_date']) ? $_GET['start_date'] : '';
$filterEndDate = isset($_GET['end_date']) ? $_GET['end_date'] : '';
$filterReason = isset($_GET['reason']) ? $_GET['reason'] : '';

// Build WHERE clause conditions for filtering
$params = [];
$paramTypes = '';

// Build date filter conditions (will be applied with table-specific aliases)
// Since we have 3 UNION parts, we need to add parameters 3 times if date filter is used
$dateFilterSQL = '';
$dateParamCount = 0;
// Prepare date values with time components for proper filtering
$filterStartDateTime = $filterStartDate ? $filterStartDate . ' 00:00:00' : '';
$filterEndDateTime = $filterEndDate ? $filterEndDate . ' 23:59:59' : '';

if ($filterStartDate && $filterEndDate) {
    $dateFilterSQL = "BETWEEN ? AND ?";
    $dateParamCount = 2;
} elseif ($filterStartDate) {
    $dateFilterSQL = ">= ?";
    $dateParamCount = 1;
} elseif ($filterEndDate) {
    $dateFilterSQL = "<= ?";
    $dateParamCount = 1;
}

// Add 3-day limit if no date filters
$dateLimit = '';
if ($filterStartDate === '' && $filterEndDate === '') {
    $dateLimit = "AND bh.created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)";
}

// Build owner filter condition
// Since we have 3 UNION parts, we need to add this parameter 3 times if used
$ownerFilter = '';
$ownerParamCount = 0;
if ($filterAgentId) {
    $ownerFilter = "AND s.owner_id = ?";
    $ownerParamCount = 1;
}

// Build reason filter for balance_history only (only in first UNION part)
$reasonFilter = '';
$reasonParamCount = 0;
if ($filterReason) {
    $reasonFilter = "AND bh.reason = ?";
    $reasonParamCount = 1;
}

// Build parameters array - need to add parameters for each UNION part
// Part 1: balance_history (date + owner + reason)
if ($dateParamCount > 0) {
    if ($filterStartDate && $filterEndDate) {
        $params[] = $filterStartDateTime;
        $params[] = $filterEndDateTime;
        $paramTypes .= 'ss';
    } elseif ($filterStartDate) {
        $params[] = $filterStartDateTime;
        $paramTypes .= 's';
    } elseif ($filterEndDate) {
        $params[] = $filterEndDateTime;
        $paramTypes .= 's';
    }
}
if ($ownerParamCount > 0) {
    $params[] = $filterAgentId;
    $paramTypes .= 'i';
}
if ($reasonParamCount > 0) {
    $params[] = $filterReason;
    $paramTypes .= 's';
}

// Part 2: orders (date + owner)
if ($dateParamCount > 0) {
    if ($filterStartDate && $filterEndDate) {
        $params[] = $filterStartDateTime;
        $params[] = $filterEndDateTime;
        $paramTypes .= 'ss';
    } elseif ($filterStartDate) {
        $params[] = $filterStartDateTime;
        $paramTypes .= 's';
    } elseif ($filterEndDate) {
        $params[] = $filterEndDateTime;
        $paramTypes .= 's';
    }
}
if ($ownerParamCount > 0) {
    $params[] = $filterAgentId;
    $paramTypes .= 'i';
}

// Part 3: agent_orders (date + owner)
if ($dateParamCount > 0) {
    if ($filterStartDate && $filterEndDate) {
        $params[] = $filterStartDateTime;
        $params[] = $filterEndDateTime;
        $paramTypes .= 'ss';
    } elseif ($filterStartDate) {
        $params[] = $filterStartDateTime;
        $paramTypes .= 's';
    } elseif ($filterEndDate) {
        $params[] = $filterEndDateTime;
        $paramTypes .= 's';
    }
}
if ($ownerParamCount > 0) {
    $params[] = $filterAgentId;
    $paramTypes .= 'i';
}

// UNION query to combine balance_history, orders, and agent_orders
// This shows ALL balance changes to store owners including orders placed through their stores
// NOTE: Exclude balance_history entries that correspond to orders/agent_orders to prevent duplicates
$query = "
    SELECT 
        CONCAT('bh_', bh.id) as id,
        s.owner_id as agent_id,
        bh.change_amount,
        CASE 
            WHEN bh.reason = 'order' AND bh.change_amount < 0 THEN ABS(bh.change_amount)
            ELSE NULL
        END as admin_price,
        bh.balance_after,
        COALESCE(bh.balance_before, (bh.balance_after - bh.change_amount)) as balance_before,
        bh.reason,
        bh.ref_id as ref_id,
        bh.beneficiary_number,
        bh.created_at,
        a.username,
        a.role,
        s.store_name,
        'balance_history' as source_type,
        NULL as order_id
    FROM balance_history bh
    INNER JOIN agents a ON bh.agent_id = a.id
    INNER JOIN stores s ON s.owner_id = a.id
    WHERE EXISTS (SELECT 1 FROM stores s2 WHERE s2.owner_id = a.id)
    -- Exclude balance_history entries that correspond to orders (to prevent duplicates)
    AND NOT (
        bh.reason = 'order' 
        AND bh.ref_id IS NOT NULL 
        AND (
            EXISTS (SELECT 1 FROM orders o WHERE o.id = bh.ref_id AND o.store_id = s.id)
            OR EXISTS (SELECT 1 FROM agent_orders ao WHERE ao.id = bh.ref_id AND ao.store_id = s.id)
        )
    )
    " . (!empty($dateFilterSQL) ? "AND bh.created_at $dateFilterSQL" : "") . "
    $ownerFilter
    $reasonFilter
    $dateLimit
    
    UNION ALL
    
    SELECT 
        CONCAT('ord_', o.id) as id,
        s.owner_id as agent_id,
        -1 * o.amount as change_amount,
        COALESCE(ABS(bh.change_amount), NULL) as admin_price,
        COALESCE(bh.balance_after, NULL) as balance_after,
        COALESCE(bh.balance_before, (bh.balance_after - bh.change_amount), NULL) as balance_before,
        'order' as reason,
        COALESCE(o.order_reference, CONCAT('ORD-', o.id)) as ref_id,
        o.beneficiary_number,
        o.created_at,
        a.username,
        a.role,
        s.store_name,
        'order' as source_type,
        o.id as order_id
    FROM orders o
    INNER JOIN stores s ON o.store_id = s.id
    INNER JOIN agents a ON s.owner_id = a.id
    LEFT JOIN balance_history bh ON bh.ref_id = o.id AND bh.reason = 'order' AND bh.agent_id = s.owner_id
    WHERE o.store_id IS NOT NULL
    " . (!empty($dateFilterSQL) ? "AND o.created_at $dateFilterSQL" : "") . "
    $ownerFilter
    " . (!empty($dateLimit) ? str_replace('bh.created_at', 'o.created_at', $dateLimit) : "") . "
    
    UNION ALL
    
    SELECT 
        CONCAT('ao_', ao.id) as id,
        s.owner_id as agent_id,
        -1 * ao.amount as change_amount,
        COALESCE(ABS(bh.change_amount), NULL) as admin_price,
        COALESCE(bh.balance_after, NULL) as balance_after,
        COALESCE(bh.balance_before, (bh.balance_after - bh.change_amount), NULL) as balance_before,
        'order' as reason,
        CONCAT('AO-', ao.id) as ref_id,
        ao.beneficiary_number,
        ao.created_at,
        a.username,
        a.role,
        s.store_name,
        'agent_order' as source_type,
        ao.id as order_id
    FROM agent_orders ao
    INNER JOIN stores s ON ao.store_id = s.id
    INNER JOIN agents a ON s.owner_id = a.id
    LEFT JOIN balance_history bh ON bh.ref_id = ao.id AND bh.reason = 'order' AND bh.agent_id = s.owner_id
    WHERE ao.store_id IS NOT NULL
    " . (!empty($dateFilterSQL) ? "AND ao.created_at $dateFilterSQL" : "") . "
    $ownerFilter
    " . (!empty($dateLimit) ? str_replace('bh.created_at', 'ao.created_at', $dateLimit) : "") . "
    
    ORDER BY created_at DESC
";

$stmt = $conn->prepare($query);
if (!empty($params)) {
    $stmt->bind_param($paramTypes, ...$params);
}
$stmt->execute();
$result = $stmt->get_result();

// Get all store owners for dropdown (only store owners, not their agents)
$agentsQuery = "SELECT DISTINCT a.id, a.username, a.role, s.store_name 
                FROM agents a 
                INNER JOIN stores s ON s.owner_id = a.id 
                ORDER BY a.username";
$agentsResult = mysqli_query($conn, $agentsQuery);
$agents = [];
while ($agent = mysqli_fetch_assoc($agentsResult)) {
    $agents[] = $agent;
}
?>

<!-- Balance History Content -->
<div class="space-y-6">

        <!-- Filters -->
        <div class="bg-gray-800 rounded-lg shadow-lg border border-gray-700 p-6 mb-6">
            <h2 class="text-xl font-semibold mb-4 text-gray-100">Filters</h2>
            <div class="bg-blue-50 border border-blue-200 rounded-lg p-3 mb-4">
                <div class="flex items-center">
                    <i class="fas fa-info-circle text-blue-600 mr-2"></i>
                    <p class="text-blue-800 text-sm">
                        <strong>Note:</strong> This page shows ALL balance changes for <strong>store owners</strong>, including:
                        <ul class="list-disc list-inside mt-2 space-y-1">
                            <li>Balance history records (payments, adjustments, etc.)</li>
                            <li>Customer orders placed through their store (from <code>orders</code> table)</li>
                            <li>Agent orders placed through their store (from <code>agent_orders</code> table)</li>
                        </ul>
                        Only records from the last 3 days are displayed by default. Use date filters to view older records.
                    </p>
                </div>
            </div>
            <form method="GET" class="grid grid-cols-1 md:grid-cols-5 gap-4">
                <input type="hidden" name="page" value="balance_history_admin">
                <div>
                    <label class="block text-sm font-medium text-gray-300 mb-2">Store Owner</label>
                    <select name="agent_id" class="w-full bg-gray-700 border border-gray-600 text-gray-100 rounded-md px-3 py-2 focus:border-blue-500 focus:ring-1 focus:ring-blue-500">
                        <option value="">All Store Owners</option>
                        <?php foreach ($agents as $agent): ?>
                            <option value="<?= $agent['id'] ?>" <?= $filterAgentId == $agent['id'] ? 'selected' : '' ?>>
                                <?= htmlspecialchars($agent['username']) ?> (<?= htmlspecialchars($agent['store_name']) ?>)
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>
                
                <div>
                    <label class="block text-sm font-medium text-gray-300 mb-2">Start Date</label>
                    <input type="date" name="start_date" value="<?= htmlspecialchars($filterStartDate) ?>" 
                           class="w-full bg-gray-700 border border-gray-600 text-gray-100 rounded-md px-3 py-2 focus:border-blue-500 focus:ring-1 focus:ring-blue-500">
                </div>
                
                <div>
                    <label class="block text-sm font-medium text-gray-300 mb-2">End Date</label>
                    <input type="date" name="end_date" value="<?= htmlspecialchars($filterEndDate) ?>" 
                           class="w-full bg-gray-700 border border-gray-600 text-gray-100 rounded-md px-3 py-2 focus:border-blue-500 focus:ring-1 focus:ring-blue-500">
                </div>
                
                <div>
                    <label class="block text-sm font-medium text-gray-300 mb-2">Reason</label>
                    <select name="reason" class="w-full bg-gray-700 border border-gray-600 text-gray-100 rounded-md px-3 py-2 focus:border-blue-500 focus:ring-1 focus:ring-blue-500">
                        <option value="">All Reasons</option>
                        <option value="order" <?= $filterReason === 'order' ? 'selected' : '' ?>>Order</option>
                        <option value="payment" <?= $filterReason === 'payment' ? 'selected' : '' ?>>Payment</option>
                        <option value="manual_adjustment" <?= $filterReason === 'manual_adjustment' ? 'selected' : '' ?>>Manual Adjustment</option>
                    </select>
                </div>
                
                <div class="flex items-end">
                    <button type="submit" class="bg-blue-600 hover:bg-blue-700 text-white font-bold py-2 px-4 rounded mr-2 transition-colors">
                        <i class="fas fa-search mr-2"></i>Filter
                    </button>
                    <a href="?page=balance_history_admin" class="bg-gray-600 hover:bg-gray-700 text-white font-bold py-2 px-4 rounded transition-colors">
                        <i class="fas fa-times mr-2"></i>Clear
                    </a>
                </div>
            </form>

            <!-- Quick Date Range Buttons -->
            <div class="mt-4">
                <label class="block text-sm font-medium text-gray-300 mb-2">Quick Date Ranges:</label>
                <div class="flex flex-wrap gap-2">
                    <button type="button" onclick="setDateRange('today')" class="bg-green-600 hover:bg-green-700 text-white text-sm px-3 py-1 rounded transition-colors">Today</button>
                    <button type="button" onclick="setDateRange('yesterday')" class="bg-green-600 hover:bg-green-700 text-white text-sm px-3 py-1 rounded transition-colors">Yesterday</button>
                    <button type="button" onclick="setDateRange('last7days')" class="bg-green-600 hover:bg-green-700 text-white text-sm px-3 py-1 rounded transition-colors">Last 7 Days</button>
                    <button type="button" onclick="setDateRange('last30days')" class="bg-green-600 hover:bg-green-700 text-white text-sm px-3 py-1 rounded transition-colors">Last 30 Days</button>
                    <button type="button" onclick="setDateRange('thisMonth')" class="bg-green-600 hover:bg-green-700 text-white text-sm px-3 py-1 rounded transition-colors">This Month</button>
                    <button type="button" onclick="setDateRange('lastMonth')" class="bg-green-600 hover:bg-green-700 text-white text-sm px-3 py-1 rounded transition-colors">Last Month</button>
                </div>
            </div>
        </div>

        <!-- Export Button -->
        <div class="mb-4">
            <button onclick="exportToCSV()" class="bg-green-600 hover:bg-green-700 text-white font-bold py-2 px-4 rounded transition-colors">
                <i class="fas fa-download mr-2"></i>Export to CSV
            </button>
        </div>

        <!-- Balance History Table -->
        <div class="bg-gray-800 rounded-lg shadow-lg border border-gray-700 overflow-hidden">
            <div class="p-4">
                    <div class="flex items-center justify-between mb-4">
                    <h3 class="text-lg font-semibold text-gray-100">Store Owner Balance History</h3>
                    <div class="flex items-center space-x-2">
                        <span class="px-3 py-1 bg-blue-100 text-blue-800 rounded-full text-sm font-medium">
                            Showing: <?= $result->num_rows ?> store owner records (Last 3 days)
                        </span>
                        <button onclick="exportBalanceHistory()" class="bg-green-500 hover:bg-green-700 text-white font-bold py-2 px-4 rounded text-sm">
                            <i class="fas fa-download mr-2"></i>Export CSV
                        </button>
                    </div>
                </div>
            </div>
            
            <div class="overflow-x-auto p-4 table-container">
                <table id="balanceHistoryTable" class="min-w-full divide-y divide-gray-700 bg-gray-800 text-gray-100 display" style="width:100%">
                    <thead class="bg-gray-700">
                        <tr>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-300 uppercase tracking-wider">ID</th>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-300 uppercase tracking-wider">Store Owner</th>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-300 uppercase tracking-wider">Source</th>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-300 uppercase tracking-wider">Change Amount</th>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-300 uppercase tracking-wider">Admin Price</th>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-300 uppercase tracking-wider">Balance Before</th>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-300 uppercase tracking-wider">Balance After</th>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-300 uppercase tracking-wider">Reason</th>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-300 uppercase tracking-wider">Beneficiary Number</th>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-300 uppercase tracking-wider">Date</th>
                        </tr>
                    </thead>
                    <tbody class="bg-gray-800 divide-y divide-gray-700">
                        <?php 
                        // Reset result pointer to beginning
                        $result->data_seek(0);
                        $rowCount = 0;
                        while ($row = $result->fetch_assoc()): 
                            $rowCount++;
                            $changeAmount = isset($row['change_amount']) && $row['change_amount'] !== null ? (float)$row['change_amount'] : 0.00;
                            $adminPrice = isset($row['admin_price']) && $row['admin_price'] !== null ? (float)$row['admin_price'] : null;
                            $balanceBefore = isset($row['balance_before']) && $row['balance_before'] !== null ? (float)$row['balance_before'] : null;
                            $balanceAfter = isset($row['balance_after']) && $row['balance_after'] !== null ? (float)$row['balance_after'] : null;
                            $sourceType = $row['source_type'] ?? 'balance_history';
                        ?>
                            <tr class="hover:bg-gray-700 transition-colors">
                                <td class="px-6 py-4 whitespace-nowrap text-sm text-gray-100"><?= htmlspecialchars($row['id'] ?? '') ?></td>
                                <td class="px-6 py-4 whitespace-nowrap">
                                    <div class="text-sm font-medium text-gray-100"><?= htmlspecialchars($row['username'] ?? 'N/A') ?></div>
                                    <div class="text-sm text-gray-400"><?= htmlspecialchars($row['store_name'] ?? 'Store Owner') ?></div>
                                    <div class="text-xs text-gray-500">ID: <?= $row['agent_id'] ?? 'N/A' ?></div>
                                </td>
                                <td class="px-6 py-4 whitespace-nowrap">
                                    <?php if ($sourceType === 'order'): ?>
                                        <span class="px-2 py-1 text-xs font-semibold rounded-full bg-blue-900/50 text-blue-300 border border-blue-700">
                                            <i class="fas fa-shopping-cart mr-1"></i>Customer Order
                                        </span>
                                    <?php elseif ($sourceType === 'agent_order'): ?>
                                        <span class="px-2 py-1 text-xs font-semibold rounded-full bg-indigo-900/50 text-indigo-300 border border-indigo-700">
                                            <i class="fas fa-user-tie mr-1"></i>Agent Order
                                        </span>
                                    <?php else: ?>
                                        <span class="px-2 py-1 text-xs font-semibold rounded-full bg-gray-700 text-gray-300 border border-gray-600">
                                            <i class="fas fa-history mr-1"></i>Balance History
                                        </span>
                                    <?php endif; ?>
                                </td>
                                <td class="px-6 py-4 whitespace-nowrap text-sm">
                                    <span class="<?= $changeAmount >= 0 ? 'text-green-400' : 'text-red-400' ?> font-medium">
                                        <?= $changeAmount >= 0 ? '+' : '' ?>GH₵ <?= number_format(abs($changeAmount), 2) ?>
                                    </span>
                                </td>
                                <td class="px-6 py-4 whitespace-nowrap text-sm text-gray-100 font-medium">
                                    <?= $adminPrice !== null ? 'GH₵ ' . number_format($adminPrice, 2) : '-' ?>
                                </td>
                                <td class="px-6 py-4 whitespace-nowrap text-sm text-gray-100 font-medium">
                                    <?= $balanceBefore !== null ? 'GH₵ ' . number_format($balanceBefore, 2) : '-' ?>
                                </td>
                                <td class="px-6 py-4 whitespace-nowrap text-sm text-gray-100 font-medium">
                                    <?= $balanceAfter !== null ? 'GH₵ ' . number_format($balanceAfter, 2) : '-' ?>
                                </td>
                                <td class="px-6 py-4 whitespace-nowrap">
                                    <span class="inline-flex px-2 py-1 text-xs font-semibold rounded-full
                                        <?= ($row['reason'] ?? '') === 'order' ? 'bg-red-900/50 text-red-300 border border-red-700' : 
                                            (($row['reason'] ?? '') === 'payment' ? 'bg-green-900/50 text-green-300 border border-green-700' : 'bg-yellow-900/50 text-yellow-300 border border-yellow-700') ?>">
                                        <?= ucfirst(str_replace('_', ' ', $row['reason'] ?? 'unknown')) ?>
                                    </span>
                                </td>
                                <td class="px-6 py-4 whitespace-nowrap text-sm text-gray-100">
                                    <?= !empty($row['beneficiary_number']) ? htmlspecialchars($row['beneficiary_number']) : '-' ?>
                                </td>
                                <td class="px-6 py-4 whitespace-nowrap text-sm text-gray-100" data-sort="<?= !empty($row['created_at']) ? strtotime($row['created_at']) : 0 ?>">
                                    <?php 
                                    if (!empty($row['created_at'])) {
                                        try {
                                            $date = new DateTime($row['created_at']);
                                            echo $date->format('M j, Y g:i A');
                                        } catch (Exception $e) {
                                            echo htmlspecialchars($row['created_at']);
                                        }
                                    } else {
                                        echo '-';
                                    }
                                    ?>
                                </td>
                            </tr>
                        <?php endwhile; ?>
                        <?php if ($rowCount === 0): ?>
                            <tr>
                                <td colspan="10" class="px-6 py-8 text-center text-gray-400">
                                    <i class="fas fa-inbox text-4xl mb-2"></i>
                                    <p class="text-lg">No balance history records found</p>
                                    <p class="text-sm mt-1">Try adjusting your filters or date range</p>
                                </td>
                            </tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>

    <!-- DataTables CSS - Additional styles for this page -->
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/2.4.2/css/buttons.dataTables.min.css">

    <style>
    /* DataTables Dark Theme Overrides */
    .dataTables_wrapper .dataTables_length,
    .dataTables_wrapper .dataTables_filter,
    .dataTables_wrapper .dataTables_info,
    .dataTables_wrapper .dataTables_processing,
    .dataTables_wrapper .dataTables_paginate {
        color: #f9fafb !important;
    }
    
    .dataTables_wrapper .dataTables_length select,
    .dataTables_wrapper .dataTables_filter input {
        background-color: #374151 !important;
        border-color: #4b5563 !important;
        color: #f9fafb !important;
    }
    
    .dataTables_wrapper .dataTables_paginate .paginate_button {
        color: #f9fafb !important;
        background-color: #374151 !important;
        border-color: #4b5563 !important;
    }
    
    .dataTables_wrapper .dataTables_paginate .paginate_button:hover {
        background-color: #4b5563 !important;
        color: #f9fafb !important;
    }
    
    .dataTables_wrapper .dataTables_paginate .paginate_button.current {
        background-color: #3b82f6 !important;
        color: white !important;
    }
    
    .dataTables_wrapper .dataTables_paginate .paginate_button.disabled {
        color: #6b7280 !important;
    }
    </style>

    <!-- DataTables JS - Additional scripts for this page -->
    <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/buttons/2.4.2/js/dataTables.buttons.min.js"></script>
    <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/buttons/2.4.2/js/buttons.html5.min.js"></script>
    <script type="text/javascript" charset="utf8" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
    <script type="text/javascript" charset="utf8" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
    <script type="text/javascript" charset="utf8" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>

    <script>
        function showToast(msg, type = 'success') {
            alert(msg);
        }


        $(document).ready(function() {
            console.log('Initializing Balance History DataTable...');
            
            // Check if jQuery and DataTables are available
            if (typeof $ === 'undefined') {
                console.error('jQuery is not loaded');
                alert('jQuery is not loaded - DataTables cannot initialize');
                return;
            }
            
            if (typeof $.fn.DataTable === 'undefined') {
                console.error('DataTables is not loaded');
                alert('DataTables is not loaded - Please check script includes');
                return;
            }

            // Initialize DataTable with comprehensive features
            try {
                // Check if table exists and has data
                const $table = $('#balanceHistoryTable');
                if ($table.length === 0) {
                    console.error('Table #balanceHistoryTable not found');
                    return;
                }
                
                // Check if table has rows
                const rowCount = $table.find('tbody tr').length;
                if (rowCount === 0) {
                    console.warn('Table has no data rows');
                }
                
                // Verify column count matches
                const headerCols = $table.find('thead tr th').length;
                const firstRowCols = $table.find('tbody tr:first td').length;
                
                if (headerCols !== firstRowCols && firstRowCols > 0) {
                    console.error('Column mismatch: Header has ' + headerCols + ' columns, but rows have ' + firstRowCols + ' columns');
                }
                
                $table.DataTable({
                    responsive: true,
                    pageLength: 25,
                    order: [[8, 'desc']], // Sort by date descending (column 8 - Date column, 0-based index)
                    dom: 'Bfrtip',
                    language: {
                        search: "Search:",
                        lengthMenu: "Show _MENU_ entries",
                        info: "Showing _START_ to _END_ of _TOTAL_ entries",
                        paginate: {
                            first: "First",
                            last: "Last",
                            next: "Next",
                            previous: "Previous"
                        }
                    },
                    buttons: [
                        {
                            extend: 'excel',
                            text: 'Export Excel',
                            className: 'btn btn-success',
                            title: 'Balance History Export'
                        },
                        {
                            extend: 'pdf',
                            text: 'Export PDF',
                            className: 'btn btn-danger',
                            title: 'Balance History Export'
                        },
                        {
                            extend: 'csv',
                            text: 'Export CSV',
                            className: 'btn btn-info',
                            title: 'Balance History Export'
                        }
                    ],
                    columnDefs: [
                        {
                            targets: [8], // Date column (column index 8, 0-based: 0=ID, 1=Store Owner, 2=Source, 3=Change Amount, 4=Balance Before, 5=Balance After, 6=Reason, 7=Beneficiary Number, 8=Date)
                            type: 'date',
                            render: function(data, type, row) {
                                // Use data-sort attribute if available for proper sorting
                                if (type === 'sort' || type === 'type') {
                                    const $cell = $(row[8]).closest('td');
                                    const sortValue = $cell.data('sort');
                                    return sortValue ? sortValue : data;
                                }
                                return data;
                            }
                        },
                        {
                            targets: [0], // ID column - make it non-searchable and non-sortable if needed
                            orderable: true,
                            searchable: true
                        }
                    ],
                    drawCallback: function(settings) {
                        console.log('DataTable redrawn');
                        // Verify all rows have correct number of cells
                        const api = this.api();
                        const columns = api.columns().count();
                        api.rows().every(function() {
                            const row = this.node();
                            const cells = $(row).find('td').length;
                            if (cells !== columns) {
                                console.error('Row has ' + cells + ' cells but table expects ' + columns + ' columns');
                            }
                        });
                    },
                    initComplete: function() {
                        console.log('DataTable initialization complete');
                    }
                });
                
                console.log('Balance History DataTable initialized successfully');
                
                // Add visual indicator that DataTables is working
                $('#balanceHistoryTable').closest('.table-container').prepend('<div class="bg-green-100 text-green-800 p-2 mb-2 rounded">✓ DataTables Active - 25 rows per page</div>');
                
            } catch (error) {
                console.error('Error initializing DataTable:', error);
                $('#balanceHistoryTable').closest('.table-container').prepend('<div class="bg-red-100 text-red-800 p-2 mb-2 rounded">✗ DataTables Failed: ' + error.message + '</div>');
            }
        });

        function setDateRange(range) {
            const today = new Date();
            let startDate = '';
            let endDate = '';

            switch(range) {
                case 'today':
                    startDate = endDate = today.toISOString().split('T')[0];
                    break;
                case 'yesterday':
                    const yesterday = new Date(today);
                    yesterday.setDate(yesterday.getDate() - 1);
                    startDate = endDate = yesterday.toISOString().split('T')[0];
                    break;
                case 'last7days':
                    const last7 = new Date(today);
                    last7.setDate(last7.getDate() - 7);
                    startDate = last7.toISOString().split('T')[0];
                    endDate = today.toISOString().split('T')[0];
                    break;
                case 'last30days':
                    const last30 = new Date(today);
                    last30.setDate(last30.getDate() - 30);
                    startDate = last30.toISOString().split('T')[0];
                    endDate = today.toISOString().split('T')[0];
                    break;
                case 'thisMonth':
                    startDate = new Date(today.getFullYear(), today.getMonth(), 1).toISOString().split('T')[0];
                    endDate = today.toISOString().split('T')[0];
                    break;
                case 'lastMonth':
                    const lastMonth = new Date(today.getFullYear(), today.getMonth() - 1, 1);
                    startDate = lastMonth.toISOString().split('T')[0];
                    endDate = new Date(today.getFullYear(), today.getMonth(), 0).toISOString().split('T')[0];
                    break;
            }

            document.querySelector('input[name="start_date"]').value = startDate;
            document.querySelector('input[name="end_date"]').value = endDate;
            document.querySelector('form').submit();
        }

        function exportBalanceHistory() {
            // Use DataTables built-in export functionality
            const table = $('#balanceHistoryTable').DataTable();
            
            // Show loading state
            const button = event.target;
            const originalText = button.innerHTML;
            button.innerHTML = '<i class="fas fa-spinner fa-spin mr-2"></i>Exporting...';
            button.disabled = true;
            
            try {
                // Trigger CSV export using DataTables buttons
                table.button('.buttons-csv').trigger();
                showToast('Balance history exported successfully', 'success');
            } catch (error) {
                console.error('Export error:', error);
                showToast('Export failed. Please try again.', 'error');
            } finally {
                // Restore button state
                button.innerHTML = originalText;
                button.disabled = false;
            }
        }

        function exportToCSV() {
            // Fallback export function for compatibility
            const table = document.getElementById('balanceHistoryTable');
            const rows = table.querySelectorAll('tbody tr');
            let csv = 'ID,Store Owner,Store Owner ID,Store Name,Change Amount,Balance Before,Balance After,Reason,Beneficiary Number,Date\n';

            rows.forEach(row => {
                const cells = row.querySelectorAll('td');
                const id = cells[0].textContent.trim();
                const ownerName = cells[1].querySelector('.text-sm.font-medium').textContent.trim();
                const storeName = cells[1].querySelector('.text-sm.text-gray-400').textContent.trim();
                const ownerId = ownerName; // We'll need to get this from data attribute or parse differently
                const changeAmount = cells[2].textContent.trim();
                const balanceBefore = cells[3].textContent.trim();
                const balanceAfter = cells[4].textContent.trim();
                const reason = cells[5].textContent.trim();
                const beneficiaryNumber = cells[6].textContent.trim();
                const date = cells[7].textContent.trim();

                csv += `"${id}","${ownerName}","${ownerId}","${storeName}","${changeAmount}","${balanceBefore}","${balanceAfter}","${reason}","${beneficiaryNumber}","${date}"\n`;
            });

            const blob = new Blob([csv], { type: 'text/csv' });
            const url = window.URL.createObjectURL(blob);
            const a = document.createElement('a');
            a.href = url;
            a.download = 'store_owner_balance_history_' + new Date().toISOString().split('T')[0] + '.csv';
            a.click();
            window.URL.revokeObjectURL(url);
        }
    </script>
</div>
