<?php
// check_merged_stocks.php - Check what stock items were merged
require 'db.php';

// db.php already starts the session, so we just check if logged in
if (!isset($_SESSION['loggedin']) || $_SESSION['loggedin'] !== true) {
    header('Location: index.php');
    exit;
}

header('Content-Type: text/html; charset=utf-8');

// Get filter parameters
$dateFrom = $_GET['date_from'] ?? date('Y-m-d', strtotime('-30 days'));
$dateTo = $_GET['date_to'] ?? date('Y-m-d');
$searchTerm = $_GET['search'] ?? '';
$limit = (int)($_GET['limit'] ?? 200);
?>
<!DOCTYPE html>
<html>
<head>
    <title>Check Merged Stocks - Detailed Report</title>
    <style>
        body { font-family: Arial, sans-serif; padding: 20px; background: #f5f5f5; }
        .container { max-width: 1400px; margin: 0 auto; background: white; padding: 20px; border-radius: 8px; box-shadow: 0 2px 10px rgba(0,0,0,0.1); }
        h1 { color: #e74c3c; margin-bottom: 10px; }
        h2 { color: #2c3e50; margin-top: 30px; border-bottom: 2px solid #3498db; padding-bottom: 10px; }
        .filters { background: #f8f9fa; padding: 20px; border-radius: 8px; margin: 20px 0; }
        .filter-row { display: flex; gap: 15px; margin-bottom: 15px; flex-wrap: wrap; align-items: end; }
        .filter-group { flex: 1; min-width: 200px; }
        .filter-group label { display: block; margin-bottom: 5px; font-weight: bold; color: #555; }
        .filter-group input, .filter-group select { width: 100%; padding: 8px; border: 1px solid #ddd; border-radius: 4px; }
        .btn { padding: 10px 20px; background: #3498db; color: white; border: none; border-radius: 4px; cursor: pointer; font-size: 14px; }
        .btn:hover { background: #2980b9; }
        .btn-secondary { background: #95a5a6; }
        .btn-secondary:hover { background: #7f8c8d; }
        table { width: 100%; border-collapse: collapse; margin-top: 20px; font-size: 13px; }
        th, td { padding: 12px; text-align: left; border-bottom: 1px solid #ddd; }
        th { background: #3498db; color: white; position: sticky; top: 0; }
        tr:hover { background: #f9f9f9; }
        .warning { background: #fff3cd; padding: 15px; border-left: 4px solid #ffc107; margin: 20px 0; border-radius: 4px; }
        .info { background: #d1ecf1; padding: 15px; border-left: 4px solid #17a2b8; margin: 20px 0; border-radius: 4px; }
        .success { background: #d4edda; padding: 15px; border-left: 4px solid #28a745; margin: 20px 0; border-radius: 4px; }
        .stats { display: flex; gap: 20px; margin: 20px 0; flex-wrap: wrap; }
        .stat-box { flex: 1; min-width: 200px; background: #f8f9fa; padding: 15px; border-radius: 8px; border-left: 4px solid #3498db; }
        .stat-box h3 { margin: 0 0 10px 0; color: #2c3e50; font-size: 14px; }
        .stat-box .number { font-size: 24px; font-weight: bold; color: #3498db; }
        .merged-names { max-width: 400px; font-size: 11px; color: #666; }
        .merged-names span { display: inline-block; background: #e9ecef; padding: 2px 6px; margin: 2px; border-radius: 3px; }
    </style>
    <script>
        function setDateRange(days) {
            const today = new Date();
            const fromDate = new Date(today);
            fromDate.setDate(today.getDate() - days);
            
            document.querySelector('input[name="date_from"]').value = fromDate.toISOString().split('T')[0];
            document.querySelector('input[name="date_to"]').value = today.toISOString().split('T')[0];
        }
    </script>
</head>
<body>
    <div class="container">
        <h1>🔍 Merged Stock Items - Detailed Report</h1>
        
        <div class="warning">
            <strong>⚠️ Warning:</strong> This shows all merge operations from activity logs. 
            If items were incorrectly merged, you may need to restore from backup.
        </div>

        <!-- Filters -->
        <div class="filters">
            <form method="GET" action="">
                <div class="filter-row">
                    <div class="filter-group">
                        <label>Date From:</label>
                        <input type="date" name="date_from" value="<?php echo htmlspecialchars($dateFrom); ?>" max="<?php echo date('Y-m-d'); ?>">
                    </div>
                    <div class="filter-group">
                        <label>Date To:</label>
                        <input type="date" name="date_to" value="<?php echo htmlspecialchars($dateTo); ?>" max="<?php echo date('Y-m-d'); ?>">
                    </div>
                    <div class="filter-group">
                        <label>Search (Item Name/User):</label>
                        <input type="text" name="search" value="<?php echo htmlspecialchars($searchTerm); ?>" placeholder="e.g. iPhone, Battery, username...">
                    </div>
                    <div class="filter-group">
                        <label>Limit Results:</label>
                        <select name="limit">
                            <option value="50" <?php echo $limit == 50 ? 'selected' : ''; ?>>50</option>
                            <option value="100" <?php echo $limit == 100 ? 'selected' : ''; ?>>100</option>
                            <option value="200" <?php echo $limit == 200 ? 'selected' : ''; ?>>200</option>
                            <option value="500" <?php echo $limit == 500 ? 'selected' : ''; ?>>500</option>
                            <option value="1000" <?php echo $limit == 1000 ? 'selected' : ''; ?>>1000</option>
                        </select>
                    </div>
                </div>
                <div class="filter-row">
                    <button type="submit" class="btn">🔍 Search</button>
                    <button type="button" class="btn btn-secondary" onclick="window.location.href='check_merged_stocks.php'">🔄 Reset</button>
                    <button type="button" class="btn btn-secondary" onclick="setDateRange(7)">Last 7 Days</button>
                    <button type="button" class="btn btn-secondary" onclick="setDateRange(30)">Last 30 Days</button>
                    <button type="button" class="btn btn-secondary" onclick="setDateRange(90)">Last 90 Days</button>
                    <button type="button" class="btn btn-secondary" onclick="setDateRange(365)">Last Year</button>
                </div>
            </form>
        </div>

        <?php
        try {
            // Build query with filters
            $sql = "SELECT * FROM activity_logs WHERE action = 'MERGE_DUPLICATES'";
            $params = [];
            
            if (!empty($dateFrom)) {
                $sql .= " AND DATE(created_at) >= ?";
                $params[] = $dateFrom;
            }
            
            if (!empty($dateTo)) {
                $sql .= " AND DATE(created_at) <= ?";
                $params[] = $dateTo;
            }
            
            if (!empty($searchTerm)) {
                $sql .= " AND (description LIKE ? OR user LIKE ?)";
                $searchParam = '%' . $searchTerm . '%';
                $params[] = $searchParam;
                $params[] = $searchParam;
            }
            
            $sql .= " ORDER BY created_at DESC LIMIT ?";
            $params[] = $limit;
            
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            $merges = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            // Get statistics
            $totalMerges = count($merges);
            $totalItemsMerged = 0;
            foreach ($merges as $merge) {
                if (preg_match('/Merged (\d+) duplicates/', $merge['description'], $matches)) {
                    $totalItemsMerged += (int)$matches[1];
                }
            }
            
            // Get date range info
            $stmtCount = $pdo->prepare("SELECT COUNT(*) FROM activity_logs WHERE action = 'MERGE_DUPLICATES' AND DATE(created_at) >= ? AND DATE(created_at) <= ?");
            $stmtCount->execute([$dateFrom, $dateTo]);
            $totalInRange = $stmtCount->fetchColumn();
            
            // Display statistics
            echo "<div class='stats'>";
            echo "<div class='stat-box'><h3>Total Merges in Range</h3><div class='number'>$totalInRange</div></div>";
            echo "<div class='stat-box'><h3>Showing Results</h3><div class='number'>$totalMerges</div></div>";
            echo "<div class='stat-box'><h3>Total Items Merged</h3><div class='number'>$totalItemsMerged</div></div>";
            echo "</div>";
            
            if (empty($merges)) {
                echo "<div class='info'>No merge operations found for the selected date range and filters.</div>";
            } else {
                echo "<h2>Merge Operations History</h2>";
                echo "<table>";
                echo "<tr><th>Date/Time</th><th>User</th><th>Merged Items</th><th>Before Merge (Names & Stock)</th><th>After Merge (Result)</th><th>Raw Description</th></tr>";
                foreach ($merges as $merge) {
                    $dateTime = date('Y-m-d H:i:s', strtotime($merge['created_at']));
                    $description = $merge['description']; // Don't escape yet, we need to parse it
                    $descriptionEscaped = htmlspecialchars($description);
                    
                    // Parse detailed merge information
                    $baseItem = null;
                    $mergedItems = [];
                    $resultItem = null;
                    
                    // Check if description contains detailed merge info (new format)
                    if (strpos($description, 'BASE:') !== false) {
                        // New detailed format - split by | but be careful with the pipe character
                        $parts = explode(' | ', $description);
                        foreach ($parts as $part) {
                            $part = trim($part);
                            if (preg_match('/BASE:\s*(.+?)\s*\(Gombak:\s*(\d+),\s*SR:\s*(\d+)\)/', $part, $match)) {
                                $baseItem = [
                                    'name' => trim($match[1]),
                                    'gombak' => (int)$match[2],
                                    'sr' => (int)$match[3]
                                ];
                            } elseif (preg_match('/MERGED:\s*(.+?)\s*\(Gombak:\s*(\d+),\s*SR:\s*(\d+)\)/', $part, $match)) {
                                $mergedItems[] = [
                                    'name' => trim($match[1]),
                                    'gombak' => (int)$match[2],
                                    'sr' => (int)$match[3]
                                ];
                            } elseif (preg_match('/RESULT:\s*(.+?)\s*\(Gombak:\s*(\d+),\s*SR:\s*(\d+)\)/', $part, $match)) {
                                $resultItem = [
                                    'name' => trim($match[1]),
                                    'gombak' => (int)$match[2],
                                    'sr' => (int)$match[3]
                                ];
                            }
                        }
                    } else {
                        // Old format - try to extract what we can
                        if (preg_match('/Merged (\d+) duplicates into: (.+?)(?:\s*\||$)/', $description, $match)) {
                            $mergedCount = (int)$match[1];
                            $resultName = trim($match[2]);
                            $resultItem = ['name' => $resultName, 'gombak' => '?', 'sr' => '?'];
                        }
                    }
                    
                    // Count merged items
                    $mergedCount = count($mergedItems);
                    if ($mergedCount == 0 && isset($match[1])) {
                        $mergedCount = (int)$match[1];
                    }
                    
                    echo "<tr>";
                    echo "<td><strong>" . htmlspecialchars($dateTime) . "</strong></td>";
                    echo "<td>" . htmlspecialchars($merge['user']) . "</td>";
                    echo "<td><strong style='color: #e74c3c;'>$mergedCount</strong> items</td>";
                    
                    // Before Merge column
                    echo "<td style='font-size: 11px; max-width: 400px;'>";
                    if ($baseItem) {
                        echo "<div style='background: #e8f5e9; padding: 5px; margin: 2px 0; border-radius: 3px;'><strong>Base:</strong> " . htmlspecialchars($baseItem['name']) . " <span style='color: #666;'>(G: {$baseItem['gombak']}, SR: {$baseItem['sr']})</span></div>";
                    }
                    foreach ($mergedItems as $item) {
                        echo "<div style='background: #fff3cd; padding: 5px; margin: 2px 0; border-radius: 3px;'><strong>Merged:</strong> " . htmlspecialchars($item['name']) . " <span style='color: #666;'>(G: {$item['gombak']}, SR: {$item['sr']})</span></div>";
                    }
                    if (empty($baseItem) && empty($mergedItems)) {
                        echo "<span style='color: #999;'>Details not available (old merge format)</span>";
                    }
                    echo "</td>";
                    
                    // After Merge column
                    echo "<td style='font-size: 11px;'>";
                    if ($resultItem) {
                        echo "<div style='background: #d1ecf1; padding: 8px; border-radius: 3px; border-left: 3px solid #17a2b8;'>";
                        echo "<strong>" . htmlspecialchars($resultItem['name']) . "</strong><br>";
                        echo "<span style='color: #666;'>Gombak: {$resultItem['gombak']} | SR: {$resultItem['sr']}</span>";
                        echo "</div>";
                    } else {
                        echo "<span style='color: #999;'>N/A</span>";
                    }
                    echo "</td>";
                    
                    // Raw Description column (for debugging)
                    echo "<td style='font-size: 10px; max-width: 300px; word-wrap: break-word; color: #666;'>";
                    echo "<details><summary style='cursor: pointer; color: #3498db;'>View Raw</summary>";
                    echo "<pre style='background: #f8f9fa; padding: 5px; margin-top: 5px; border-radius: 3px; white-space: pre-wrap;'>" . $descriptionEscaped . "</pre>";
                    echo "</details>";
                    echo "</td>";
                    
                    echo "</tr>";
                }
                echo "</table>";
            }
            
            // Check current stock for iPhone batteries
            echo "<h2 style='margin-top: 40px;'>Current iPhone Battery Stock:</h2>";
            $stmt = $pdo->prepare("SELECT * FROM stock_list WHERE LOWER(part_name) LIKE '%iphone%' AND LOWER(part_name) LIKE '%battery%' ORDER BY part_name");
            $stmt->execute();
            $batteries = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            if (empty($batteries)) {
                echo "<div class='warning'><strong>⚠️ No iPhone battery items found!</strong> They may have been merged incorrectly.</div>";
            } else {
                echo "<table>";
                echo "<tr><th>ID</th><th>Part Name</th><th>Type</th><th>Category</th><th>Stock Gombak</th><th>Stock SR</th><th>Cost</th></tr>";
                foreach ($batteries as $battery) {
                    echo "<tr>";
                    echo "<td>" . htmlspecialchars($battery['id']) . "</td>";
                    echo "<td><strong>" . htmlspecialchars($battery['part_name']) . "</strong></td>";
                    echo "<td>" . htmlspecialchars($battery['type']) . "</td>";
                    echo "<td>" . htmlspecialchars($battery['category'] ?? 'N/A') . "</td>";
                    echo "<td>" . htmlspecialchars($battery['stock_gombak']) . "</td>";
                    echo "<td>" . htmlspecialchars($battery['stock_sr']) . "</td>";
                    echo "<td>RM " . htmlspecialchars($battery['cost']) . "</td>";
                    echo "</tr>";
                }
                echo "</table>";
            }
            
            // Check for hidden/orphaned stock items
            echo "<h2 style='margin-top: 40px;'>🔍 Hidden/Orphaned Stock Items Check</h2>";
            echo "<div class='info'>Checking for stock items that exist in database but may not be displaying...</div>";
            
            // Get all stock items
            $stmtAll = $pdo->query("SELECT COUNT(*) as total FROM stock_list");
            $totalInDb = $stmtAll->fetch(PDO::FETCH_ASSOC)['total'];
            
            // Check for items with missing or invalid data
            $stmtIssues = $pdo->query("
                SELECT id, part_name, type, category, stock_gombak, stock_sr, cost, created_at 
                FROM stock_list 
                WHERE part_name IS NULL 
                   OR part_name = '' 
                   OR type IS NULL 
                   OR type = ''
                ORDER BY id DESC
            ");
            $itemsWithIssues = $stmtIssues->fetchAll(PDO::FETCH_ASSOC);
            
            // Check for items that might be hidden due to category issues
            $stmtCategory = $pdo->query("
                SELECT id, part_name, type, category, stock_gombak, stock_sr, cost 
                FROM stock_list 
                WHERE category IS NULL 
                   OR category = ''
                ORDER BY part_name
            ");
            $itemsNoCategory = $stmtCategory->fetchAll(PDO::FETCH_ASSOC);
            
            // Check for items with zero stock in both locations (might be hidden)
            $stmtZeroStock = $pdo->query("
                SELECT id, part_name, type, category, stock_gombak, stock_sr, cost 
                FROM stock_list 
                WHERE (stock_gombak = 0 OR stock_gombak IS NULL) 
                  AND (stock_sr = 0 OR stock_sr IS NULL)
                ORDER BY part_name
                LIMIT 50
            ");
            $itemsZeroStock = $stmtZeroStock->fetchAll(PDO::FETCH_ASSOC);
            
            // Display summary
            echo "<div class='stats'>";
            echo "<div class='stat-box'><h3>Total Items in Database</h3><div class='number'>$totalInDb</div></div>";
            echo "<div class='stat-box'><h3>Items with Data Issues</h3><div class='number' style='color: " . (count($itemsWithIssues) > 0 ? "#e74c3c" : "#2ecc71") . ";'>" . count($itemsWithIssues) . "</div></div>";
            echo "<div class='stat-box'><h3>Items Missing Category</h3><div class='number' style='color: " . (count($itemsNoCategory) > 0 ? "#f39c12" : "#2ecc71") . ";'>" . count($itemsNoCategory) . "</div></div>";
            echo "<div class='stat-box'><h3>Items with Zero Stock</h3><div class='number' style='color: #95a5a6;'>" . count($itemsZeroStock) . "</div></div>";
            echo "</div>";
            
            // Show items with critical issues
            if (!empty($itemsWithIssues)) {
                echo "<div class='warning' style='margin-top: 20px;'>";
                echo "<h3>⚠️ Items with Critical Data Issues (May Not Display):</h3>";
                echo "<table>";
                echo "<tr><th>ID</th><th>Part Name</th><th>Type</th><th>Category</th><th>Stock Gombak</th><th>Stock SR</th><th>Issue</th></tr>";
                foreach ($itemsWithIssues as $item) {
                    $issues = [];
                    if (empty($item['part_name'])) $issues[] = "Missing name";
                    if (empty($item['type'])) $issues[] = "Missing type";
                    
                    echo "<tr>";
                    echo "<td>" . htmlspecialchars($item['id']) . "</td>";
                    echo "<td><strong>" . htmlspecialchars($item['part_name'] ?: '[EMPTY]') . "</strong></td>";
                    echo "<td>" . htmlspecialchars($item['type'] ?: '[EMPTY]') . "</td>";
                    echo "<td>" . htmlspecialchars($item['category'] ?? 'N/A') . "</td>";
                    echo "<td>" . htmlspecialchars($item['stock_gombak'] ?? 0) . "</td>";
                    echo "<td>" . htmlspecialchars($item['stock_sr'] ?? 0) . "</td>";
                    echo "<td style='color: #e74c3c;'><strong>" . implode(", ", $issues) . "</strong></td>";
                    echo "</tr>";
                }
                echo "</table>";
                echo "</div>";
            }
            
            // Show items missing category
            if (!empty($itemsNoCategory)) {
                echo "<div class='warning' style='margin-top: 20px;'>";
                echo "<h3>⚠️ Items Missing Category (May Not Group Properly):</h3>";
                echo "<table>";
                echo "<tr><th>ID</th><th>Part Name</th><th>Type</th><th>Stock Gombak</th><th>Stock SR</th><th>Cost</th></tr>";
                foreach ($itemsNoCategory as $item) {
                    echo "<tr>";
                    echo "<td>" . htmlspecialchars($item['id']) . "</td>";
                    echo "<td><strong>" . htmlspecialchars($item['part_name']) . "</strong></td>";
                    echo "<td>" . htmlspecialchars($item['type']) . "</td>";
                    echo "<td>" . htmlspecialchars($item['stock_gombak']) . "</td>";
                    echo "<td>" . htmlspecialchars($item['stock_sr']) . "</td>";
                    echo "<td>RM " . htmlspecialchars($item['cost']) . "</td>";
                    echo "</tr>";
                }
                echo "</table>";
                echo "</div>";
            }
            
            // Show items with zero stock (might be hidden)
            if (!empty($itemsZeroStock)) {
                echo "<div class='info' style='margin-top: 20px;'>";
                echo "<h3>ℹ️ Items with Zero Stock in Both Locations (Showing first 50):</h3>";
                echo "<p>These items exist in the database but have no stock. They may not appear in stock lists if filtered by stock quantity.</p>";
                echo "<table>";
                echo "<tr><th>ID</th><th>Part Name</th><th>Type</th><th>Category</th><th>Cost</th></tr>";
                foreach ($itemsZeroStock as $item) {
                    echo "<tr>";
                    echo "<td>" . htmlspecialchars($item['id']) . "</td>";
                    echo "<td><strong>" . htmlspecialchars($item['part_name']) . "</strong></td>";
                    echo "<td>" . htmlspecialchars($item['type']) . "</td>";
                    echo "<td>" . htmlspecialchars($item['category'] ?? 'N/A') . "</td>";
                    echo "<td>RM " . htmlspecialchars($item['cost']) . "</td>";
                    echo "</tr>";
                }
                echo "</table>";
                echo "</div>";
            }
            
            // Check for recently added items that might not be showing
            // First check if created_at/updated_at columns exist
            $hasCreatedAt = false;
            $hasUpdatedAt = false;
            try {
                $stmtCols = $pdo->query("SHOW COLUMNS FROM stock_list");
                $columns = $stmtCols->fetchAll(PDO::FETCH_COLUMN);
                $hasCreatedAt = in_array('created_at', $columns);
                $hasUpdatedAt = in_array('updated_at', $columns);
            } catch (Exception $e) {
                // Columns might not exist, that's okay - use ID-based approach
                $hasCreatedAt = false;
                $hasUpdatedAt = false;
            }
            
            $recentItems = [];
            // If no date columns, show items with highest IDs (likely most recent)
            // This is safer and works regardless of table structure
            try {
                $stmtRecent = $pdo->query("
                    SELECT id, part_name, type, category, stock_gombak, stock_sr, cost
                    FROM stock_list 
                    ORDER BY id DESC
                    LIMIT 50
                ");
                $recentItems = $stmtRecent->fetchAll(PDO::FETCH_ASSOC);
            } catch (Exception $e) {
                // If query fails, just skip this section
                $recentItems = [];
            }
            
            if (!empty($recentItems)) {
                echo "<div class='success' style='margin-top: 20px;'>";
                echo "<h3>✅ Recently Added/Updated Items (Last 7 Days):</h3>";
                echo "<table>";
                echo "<tr><th>ID</th><th>Part Name</th><th>Type</th><th>Category</th><th>Stock Gombak</th><th>Stock SR</th><th>Last Updated</th></tr>";
                foreach ($recentItems as $item) {
                    echo "<tr>";
                    echo "<td>" . htmlspecialchars($item['id']) . "</td>";
                    echo "<td><strong>" . htmlspecialchars($item['part_name']) . "</strong></td>";
                    echo "<td>" . htmlspecialchars($item['type']) . "</td>";
                    echo "<td>" . htmlspecialchars($item['category'] ?? 'N/A') . "</td>";
                    echo "<td>" . htmlspecialchars($item['stock_gombak']) . "</td>";
                    echo "<td>" . htmlspecialchars($item['stock_sr']) . "</td>";
                    echo "<td>ID: " . htmlspecialchars($item['id']) . "</td>";
                    echo "</tr>";
                }
                echo "</table>";
                echo "</div>";
            }
            
            // Check if database_dump.sql exists
            if (file_exists('database_dump.sql')) {
                $fileSize = filesize('database_dump.sql');
                $fileDate = date('Y-m-d H:i:s', filemtime('database_dump.sql'));
                echo "<div class='info' style='margin-top: 40px;'>";
                echo "<strong>💾 Backup Found:</strong> database_dump.sql<br>";
                echo "Size: " . number_format($fileSize / 1024, 2) . " KB<br>";
                echo "Last Modified: $fileDate<br>";
                echo "You may be able to restore from this backup if needed.";
                echo "</div>";
            } else {
                echo "<div class='warning' style='margin-top: 40px;'>";
                echo "<strong>⚠️ No database_dump.sql found.</strong> Check if you have other backups.";
                echo "</div>";
            }
            
        } catch (Exception $e) {
            echo "<div class='warning'><strong>Error:</strong> " . htmlspecialchars($e->getMessage()) . "</div>";
        }
        ?>
        
        <div style="margin-top: 40px; padding: 20px; background: #f8f9fa; border-radius: 8px;">
            <h3>What to do if data was lost:</h3>
            <ol>
                <li>Use the filters above to search for merge operations from any date range</li>
                <li>Check if you have a recent backup (database_dump.sql or other backup files)</li>
                <li>Check the activity logs above to see what was merged</li>
                <li>If you have a backup, restore the stock_list table from the backup</li>
                <li>The merge function has been fixed to only merge exact matches (no more fuzzy matching)</li>
            </ol>
        </div>
    </div>
</body>
</html>
