<?php
// search_code_barang.php
// API endpoint for searching code barang - UPDATED VERSION

// Include database connection
require_once "../config/connection.php";

// Set header to return JSON
header('Content-Type: application/json');
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET');
header('Access-Control-Allow-Headers: Content-Type');

// Disable caching
header('Cache-Control: no-cache, no-store, must-revalidate');
header('Pragma: no-cache');
header('Expires: 0');

// Check if search parameter is provided
if (!isset($_GET['search']) || empty(trim($_GET['search']))) {
    echo json_encode([
        'success' => false,
        'error' => 'Search parameter is required',
        'results' => []
    ]);
    exit;
}

$search_term = trim($_GET['search']);

try {
    // Start timing for performance monitoring
    $start_time = microtime(true);
    
    // Prepare search query with improved COLLATION for case insensitive search
    // Search for code barang that contains the search term (case insensitive)
    $search_pattern = '%' . $search_term . '%';
    
    // Enhanced query with better joins and error handling
    // UPDATED: Include more details for bulk operations and better QR code management
    $stmt = $conn->prepare("
        SELECT 
            csi.id,
            csi.color_id,
            csi.component_id,
            csi.kode_barang,
            csi.stock_number,
            csi.status,
            csi.lokasi,
            csi.rack_no,
            csi.tanggal_pembuatan,
            csi.tanggal_kedaluwarsa,
            csi.keterangan,
            csi.qr_code,
            CASE 
                WHEN csi.qr_code IS NOT NULL AND csi.qr_code != '' THEN 1
                ELSE 0
            END as has_qr_code,
            mcp.code_color,
            mcp.deskripsi as color_description,
            mcp.status as color_status,
            mcp.kode_warna_hex,
            mcp.lemari,
            COALESCE(b.nama_buyer, 'Unknown Buyer') as buyer_name,
            COALESCE(b.kode_buyer, '') as kode_buyer,
            COALESCE(cc.component_name, 'Unknown Component') as component_name,
            COALESCE(cc.description, '') as component_description,
            COALESCE(c.nama_collection, 'Unknown Collection') as collection_name
        FROM color_stock_items csi
        INNER JOIN master_color_panel mcp ON csi.color_id = mcp.id
        LEFT JOIN buyers b ON mcp.buyer_id = b.id
        LEFT JOIN color_components cc ON csi.component_id = cc.id
        LEFT JOIN collections c ON mcp.collection_id = c.id
        WHERE UPPER(COALESCE(csi.kode_barang, '')) LIKE UPPER(?) 
        AND COALESCE(csi.kode_barang, '') != ''
        ORDER BY 
            CASE 
                WHEN UPPER(COALESCE(csi.kode_barang, '')) = UPPER(?) THEN 1
                WHEN UPPER(COALESCE(csi.kode_barang, '')) LIKE UPPER(CONCAT(?, '%')) THEN 2
                WHEN UPPER(COALESCE(csi.kode_barang, '')) LIKE UPPER(?) THEN 3
                ELSE 4
            END,
            COALESCE(b.nama_buyer, 'Unknown Buyer'),
            COALESCE(mcp.code_color, ''),
            COALESCE(cc.component_name, ''),
            csi.stock_number
    ");
    
    // Check if prepare failed
    if (!$stmt) {
        throw new Exception("Query preparation failed: " . $conn->error);
    }
    
    // Bind parameters for different search patterns
    $exact_match = $search_term;
    $starts_with = $search_term . '%';
    $contains = $search_pattern;
    
    if (!$stmt->bind_param("ssss", $contains, $exact_match, $search_term, $contains)) {
        throw new Exception("Parameter binding failed: " . $stmt->error);
    }
    
    if (!$stmt->execute()) {
        throw new Exception("Query execution failed: " . $stmt->error);
    }
    
    $result = $stmt->get_result();
    
    if (!$result) {
        throw new Exception("Failed to get result: " . $conn->error);
    }
    
    $search_results = [];
    $qr_statistics = [
        'total_items' => 0,
        'items_with_qr' => 0,
        'items_without_qr' => 0,
        'qr_percentage' => 0
    ];
    
    while ($row = $result->fetch_assoc()) {
        // Validate that we have essential data - more flexible validation
        if (empty($row['kode_barang'])) {
            continue; // Skip rows with empty kode_barang
        }
        
        // Ensure color_id exists
        if (empty($row['color_id'])) {
            continue; // Skip rows without valid color_id
        }
        
        // Update QR statistics
        $qr_statistics['total_items']++;
        if ($row['has_qr_code']) {
            $qr_statistics['items_with_qr']++;
        } else {
            $qr_statistics['items_without_qr']++;
        }
        
        // Handle null values with defaults
        $row['buyer_name'] = $row['buyer_name'] ?? 'Unknown Buyer';
        $row['component_name'] = $row['component_name'] ?? 'Unknown Component';
        $row['collection_name'] = $row['collection_name'] ?? 'Unknown Collection';
        $row['code_color'] = $row['code_color'] ?? 'Unknown Color';
        $row['color_description'] = $row['color_description'] ?? '';
        $row['component_description'] = $row['component_description'] ?? '';
        $row['kode_buyer'] = $row['kode_buyer'] ?? '';
        $row['kode_warna_hex'] = $row['kode_warna_hex'] ?? '#ffffff';
        $row['lemari'] = $row['lemari'] ?? '';
        
        // Format dates for better display
        if ($row['tanggal_pembuatan']) {
            try {
                $creation_date = new DateTime($row['tanggal_pembuatan']);
                $row['tanggal_pembuatan_formatted'] = $creation_date->format('d/m/Y');
            } catch (Exception $e) {
                $row['tanggal_pembuatan_formatted'] = $row['tanggal_pembuatan'];
            }
        } else {
            $row['tanggal_pembuatan_formatted'] = null;
        }
        
        if ($row['tanggal_kedaluwarsa']) {
            try {
                $today = new DateTime();
                $expiry = new DateTime($row['tanggal_kedaluwarsa']);
                $row['tanggal_kedaluwarsa_formatted'] = $expiry->format('d/m/Y');
                
                // Calculate days until expiry
                $interval = $today->diff($expiry);
                $days_diff = $interval->invert ? -$interval->days : $interval->days;
                $row['days_until_expiry'] = $days_diff;
                
                // Add expiry status
                if ($days_diff < 0) {
                    $row['expiry_status'] = 'expired';
                } elseif ($days_diff <= 10) {
                    $row['expiry_status'] = 'expiring_soon';
                } else {
                    $row['expiry_status'] = 'valid';
                }
            } catch (Exception $e) {
                $row['tanggal_kedaluwarsa_formatted'] = $row['tanggal_kedaluwarsa'];
                $row['days_until_expiry'] = null;
                $row['expiry_status'] = 'unknown';
            }
        } else {
            $row['tanggal_kedaluwarsa_formatted'] = null;
            $row['days_until_expiry'] = null;
            $row['expiry_status'] = 'no_date';
        }
        
        // Add match score for relevance (exact match gets higher score)
        $code_upper = strtoupper($row['kode_barang']);
        $search_upper = strtoupper($search_term);
        
        if ($code_upper === $search_upper) {
            $row['match_score'] = 100; // Exact match
            $row['match_type'] = 'exact';
        } elseif (strpos($code_upper, $search_upper) === 0) {
            $row['match_score'] = 90; // Starts with search term
            $row['match_type'] = 'starts_with';
        } elseif (strpos($code_upper, $search_upper) !== false) {
            $row['match_score'] = 80; // Contains search term
            $row['match_type'] = 'contains';
        } else {
            $row['match_score'] = 50; // Partial match (shouldn't happen with current query)
            $row['match_type'] = 'partial';
        }
        
        // Highlight matching part in code barang for display
        try {
            $row['kode_barang_highlighted'] = str_ireplace(
                $search_term, 
                '<mark class="bg-yellow-300 px-1 rounded">' . $search_term . '</mark>', 
                $row['kode_barang']
            );
        } catch (Exception $e) {
            $row['kode_barang_highlighted'] = $row['kode_barang'];
        }
        
        // Add QR code analysis
        $row['qr_status'] = $row['has_qr_code'] ? 'available' : 'missing';
        $row['needs_qr_generation'] = !$row['has_qr_code'];
        
        // Add bulk operation flags
        $row['can_bulk_qr'] = !$row['has_qr_code']; // Can be included in bulk QR generation
        $row['requires_renewal'] = ($row['status'] === 'Renew' || $row['status'] === 'Expired');
        
        // Add additional metadata
        $row['display_name'] = $row['buyer_name'] . ' ' . $row['code_color'] . ' - ' . $row['component_name'];
        $row['full_location'] = trim($row['lokasi'] . ' ' . ($row['rack_no'] ?? ''));
        
        // Add status classification
        $row['status_category'] = match($row['status']) {
            'Tersedia' => 'available',
            'Dipinjam' => 'borrowed',
            'Rusak' => 'damaged',
            'Expired' => 'expired',
            'Renew' => 'needs_renewal',
            'Hilang' => 'lost',
            default => 'unknown'
        };
        
        // Ensure numeric fields are properly typed
        $row['id'] = (int)$row['id'];
        $row['color_id'] = (int)$row['color_id'];
        $row['component_id'] = (int)$row['component_id'];
        $row['stock_number'] = (int)$row['stock_number'];
        $row['match_score'] = (int)$row['match_score'];
        $row['has_qr_code'] = (int)$row['has_qr_code'];
        
        $search_results[] = $row;
    }
    
    $stmt->close();
    
    // Calculate QR statistics
    if ($qr_statistics['total_items'] > 0) {
        $qr_statistics['qr_percentage'] = round(
            ($qr_statistics['items_with_qr'] / $qr_statistics['total_items']) * 100, 
            2
        );
    }
    
    // Get statistics - handle empty results gracefully
    $total_results = count($search_results);
    $unique_colors = $total_results > 0 ? array_unique(array_column($search_results, 'color_id')) : [];
    $unique_buyers = $total_results > 0 ? array_unique(array_column($search_results, 'buyer_name')) : [];
    $unique_components = $total_results > 0 ? array_unique(array_column($search_results, 'component_name')) : [];
    
    // Group results by status for summary
    $status_summary = [];
    $buyer_summary = [];
    $color_summary = [];
    $qr_code_summary = [];
    
    foreach ($search_results as $result) {
        // Status summary
        $status = $result['status'] ?? 'Unknown';
        if (!isset($status_summary[$status])) {
            $status_summary[$status] = 0;
        }
        $status_summary[$status]++;
        
        // Buyer summary
        $buyer = $result['buyer_name'] ?? 'Unknown Buyer';
        if (!isset($buyer_summary[$buyer])) {
            $buyer_summary[$buyer] = 0;
        }
        $buyer_summary[$buyer]++;
        
        // Color summary
        $color_key = $result['buyer_name'] . ' - ' . $result['code_color'];
        if (!isset($color_summary[$color_key])) {
            $color_summary[$color_key] = [
                'buyer' => $result['buyer_name'],
                'color_code' => $result['code_color'],
                'color_id' => $result['color_id'],
                'count' => 0,
                'items_with_qr' => 0,
                'items_without_qr' => 0
            ];
        }
        $color_summary[$color_key]['count']++;
        
        if ($result['has_qr_code']) {
            $color_summary[$color_key]['items_with_qr']++;
        } else {
            $color_summary[$color_key]['items_without_qr']++;
        }
        
        // QR Code summary
        $qr_status = $result['has_qr_code'] ? 'with_qr' : 'without_qr';
        if (!isset($qr_code_summary[$qr_status])) {
            $qr_code_summary[$qr_status] = 0;
        }
        $qr_code_summary[$qr_status]++;
    }
    
    // Calculate execution time
    $execution_time = microtime(true) - $start_time;
    
    // Prepare enhanced response
    $response = [
        'success' => true, // Always true when search executes successfully
        'search_term' => $search_term,
        'total_results' => $total_results,
        'unique_colors' => count($unique_colors),
        'unique_buyers' => count($unique_buyers),
        'unique_components' => count($unique_components),
        'execution_time' => round($execution_time, 4),
        'timestamp' => date('Y-m-d H:i:s'),
        'qr_statistics' => $qr_statistics,
        'status_summary' => $status_summary,
        'buyer_summary' => $buyer_summary,
        'color_summary' => array_values($color_summary),
        'qr_code_summary' => $qr_code_summary,
        'results' => $search_results, // Can be empty array
        'has_results' => $total_results > 0,
        'message' => $total_results > 0 ? 
            "Ditemukan {$total_results} hasil untuk '{$search_term}'" : 
            "Tidak ditemukan hasil untuk '{$search_term}'",
        
        // Bulk operation insights
        'bulk_insights' => [
            'items_need_qr' => $qr_statistics['items_without_qr'],
            'can_bulk_generate' => $qr_statistics['items_without_qr'] > 0,
            'recommended_action' => $qr_statistics['items_without_qr'] > 5 ? 
                'bulk_qr_generation' : 
                ($qr_statistics['items_without_qr'] > 0 ? 'individual_qr_generation' : 'no_action_needed')
        ]
    ];
    
    // Add performance insights
    $response['performance'] = [
        'query_time' => round($execution_time * 1000, 2) . 'ms',
        'results_per_second' => $execution_time > 0 ? round($total_results / $execution_time, 0) : 0,
        'memory_usage' => round(memory_get_usage(true) / 1024 / 1024, 2) . 'MB'
    ];
    
    // Add debug info if requested
    if (isset($_GET['debug']) && $_GET['debug'] === '1') {
        $response['debug'] = [
            'query_executed' => true,
            'search_pattern' => $search_pattern,
            'exact_match' => $exact_match,
            'starts_with' => $starts_with,
            'contains' => $contains,
            'execution_time_detailed' => $execution_time,
            'memory_usage' => memory_get_usage(true),
            'peak_memory' => memory_get_peak_usage(true),
            'total_results_found' => $total_results,
            'qr_code_analysis' => $qr_statistics
        ];
    }
    
    // Add cache busting info if provided
    if (isset($_GET['cb'])) {
        $response['cache_bust'] = $_GET['cb'];
        $response['fresh_data'] = true;
    }
    
    echo json_encode($response, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);

} catch (Exception $e) {
    // Log error for debugging
    error_log("Error in search_code_barang.php: " . $e->getMessage());
    error_log("Stack trace: " . $e->getTraceAsString());
    
    // Return detailed error information for debugging
    $error_response = [
        'success' => false,
        'error' => 'Database error occurred',
        'message' => 'Terjadi kesalahan saat melakukan pencarian. Silakan coba lagi.',
        'results' => [],
        'total_results' => 0,
        'timestamp' => date('Y-m-d H:i:s'),
        'qr_statistics' => [
            'total_items' => 0,
            'items_with_qr' => 0,
            'items_without_qr' => 0,
            'qr_percentage' => 0
        ]
    ];
    
    // Add detailed error info if debug mode is enabled
    if (isset($_GET['debug']) && $_GET['debug'] === '1') {
        $error_response['debug_error'] = [
            'message' => $e->getMessage(),
            'file' => $e->getFile(),
            'line' => $e->getLine(),
            'trace' => $e->getTraceAsString(),
            'search_term' => isset($search_term) ? $search_term : null
        ];
    }
    
    echo json_encode($error_response, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);
} finally {
    // Ensure any open statements are closed
    if (isset($stmt) && $stmt instanceof mysqli_stmt) {
        $stmt->close();
    }
}
?>