<?php
// get_color_components_surabaya.php - UPDATED VERSION
// API endpoint for getting color components with enhanced QR code management

// 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');

// Enable debug mode if requested
$debug_mode = isset($_GET['debug']) && $_GET['debug'] === '1';

// Check if color_id parameter is provided
if (!isset($_GET['color_id'])) {
    echo json_encode([
        'success' => false,
        'error' => 'Color ID is required',
        'message' => 'Parameter color_id diperlukan'
    ]);
    exit;
}

$color_id = intval($_GET['color_id']);

if ($color_id <= 0) {
    echo json_encode([
        'success' => false,
        'error' => 'Invalid Color ID',
        'message' => 'Color ID tidak valid'
    ]);
    exit;
}

$debug_info = [
    'input_color_id' => $color_id,
    'debug_mode' => $debug_mode,
    'queries_executed' => [],
    'raw_results' => []
];

try {
    // Start timing
    $start_time = microtime(true);
    
    // Get the color information with enhanced details
    $color_stmt = $conn->prepare("
        SELECT 
            mcp.id,
            mcp.code_color, 
            mcp.deskripsi, 
            mcp.status as color_status,
            mcp.kode_warna_hex,
            mcp.keterangan_warna,
            mcp.lemari,
            b.id as buyer_id,
            b.nama_buyer, 
            b.kode_buyer,
            c.id as collection_surabaya_id,
            c.nama_collection_surabaya 
        FROM master_color_panel_surabaya mcp
        LEFT JOIN buyers_surabaya b ON mcp.buyer_id = b.id
        LEFT JOIN collection_surabayas c ON mcp.collection_surabaya_id = c.id
        WHERE mcp.id = ?
    ");
    
    $color_stmt->bind_param("i", $color_id);
    $color_stmt->execute();
    $color_result = $color_stmt->get_result();
    $color_data = $color_result->fetch_assoc();
    $color_stmt->close();
    
    if ($debug_mode) {
        $debug_info['queries_executed'][] = "Color info query with JOINs";
        $debug_info['raw_results']['color_with_joins'] = $color_data;
    }
    
    if (!$color_data) {
        echo json_encode([
            'success' => false,
            'error' => 'Color not found',
            'message' => 'Warna tidak ditemukan',
            'debug' => $debug_mode ? $debug_info : null
        ]);
        exit;
    }

    // Get components with enhanced QR code information
    $components_query = "
        SELECT 
            csi.id, 
            csi.component_id, 
            csi.kode_barang, 
            csi.stock_number, 
            csi.status, 
            csi.lokasi, 
            csi.rack_no, 
            csi.qr_code, 
            csi.tanggal_pembuatan, 
            csi.tanggal_kedaluwarsa, 
            csi.keterangan,
            cc.component_name,
            cc.description as component_description,
            CASE 
                WHEN csi.qr_code IS NOT NULL AND csi.qr_code != '' THEN 1
                ELSE 0
            END as has_qr_code,
            CASE 
                WHEN csi.tanggal_kedaluwarsa < CURDATE() THEN 'expired'
                WHEN csi.tanggal_kedaluwarsa <= DATE_ADD(CURDATE(), INTERVAL 10 DAY) THEN 'expiring_soon'
                ELSE 'valid'
            END as expiry_status,
            DATEDIFF(csi.tanggal_kedaluwarsa, CURDATE()) as days_until_expiry
        FROM color_stock_items_surabaya csi
        LEFT JOIN color_components_surabaya cc ON csi.component_id = cc.id
        WHERE csi.color_id = ?
        ORDER BY cc.component_name, csi.stock_number
    ";
    
    $comp_stmt = $conn->prepare($components_query);
    $comp_stmt->bind_param("i", $color_id);
    $comp_stmt->execute();
    $comp_result = $comp_stmt->get_result();
    
    $components = [];
    $qr_statistics = [
        'total_components' => 0,
        'components_with_qr' => 0,
        'components_without_qr' => 0,
        'qr_coverage_percentage' => 0
    ];
    $status_distribution = [];
    $expiry_analysis = [
        'valid' => 0,
        'expiring_soon' => 0,
        'expired' => 0
    ];
    
    while ($row = $comp_result->fetch_assoc()) {
        // Update statistics
        $qr_statistics['total_components']++;
        
        if ($row['has_qr_code']) {
            $qr_statistics['components_with_qr']++;
        } else {
            $qr_statistics['components_without_qr']++;
        }
        
        // Status distribution
        $status = $row['status'] ?? 'Unknown';
        if (!isset($status_distribution[$status])) {
            $status_distribution[$status] = 0;
        }
        $status_distribution[$status]++;
        
        // Expiry analysis
        $expiry_status = $row['expiry_status'] ?? 'unknown';
        if (isset($expiry_analysis[$expiry_status])) {
            $expiry_analysis[$expiry_status]++;
        }
        
        // Enhanced data processing
        $row['component_name'] = $row['component_name'] ?? 'Unknown Component';
        $row['component_description'] = $row['component_description'] ?? '';
        
        // Format dates
        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 {
                $expiry_date = new DateTime($row['tanggal_kedaluwarsa']);
                $row['tanggal_kedaluwarsa_formatted'] = $expiry_date->format('d/m/Y');
            } catch (Exception $e) {
                $row['tanggal_kedaluwarsa_formatted'] = $row['tanggal_kedaluwarsa'];
            }
        } else {
            $row['tanggal_kedaluwarsa_formatted'] = null;
        }
        
        // Add QR code analysis
        $row['qr_status'] = $row['has_qr_code'] ? 'available' : 'missing';
        $row['needs_qr_generation'] = !$row['has_qr_code'];
        
        // Add bulk operation compatibility
        $row['can_bulk_qr'] = !$row['has_qr_code'] && !empty($row['kode_barang']);
        $row['requires_renewal'] = in_array($row['status'], ['Renew', 'Expired']);
        
        // Add status classification
        $row['status_category'] = match($row['status']) {
            'Tersedia' => 'available',
            'Dipinjam' => 'borrowed',
            'Rusak' => 'damaged',
            'Expired' => 'expired',
            'Renew' => 'needs_renewal',
            'Hilang' => 'lost',
            default => 'unknown'
        };
        
        // Add priority scoring for bulk operations
        $priority_score = 0;
        
        // Higher priority for items without QR codes
        if (!$row['has_qr_code']) {
            $priority_score += 10;
        }
        
        // Higher priority for items that need renewal
        if ($row['requires_renewal']) {
            $priority_score += 8;
        }
        
        // Higher priority for expiring items
        if ($row['expiry_status'] === 'expiring_soon') {
            $priority_score += 6;
        } elseif ($row['expiry_status'] === 'expired') {
            $priority_score += 9;
        }
        
        // Higher priority for available items
        if ($row['status'] === 'Tersedia') {
            $priority_score += 3;
        }
        
        $row['bulk_priority_score'] = $priority_score;
        
        // Add location information
        $row['full_location'] = trim(($row['lokasi'] ?? '') . ' ' . ($row['rack_no'] ?? ''));
        
        // Ensure numeric fields are properly typed
        $row['id'] = (int)$row['id'];
        $row['component_id'] = (int)$row['component_id'];
        $row['stock_number'] = (int)$row['stock_number'];
        $row['has_qr_code'] = (int)$row['has_qr_code'];
        $row['days_until_expiry'] = $row['days_until_expiry'] !== null ? (int)$row['days_until_expiry'] : null;
        $row['bulk_priority_score'] = (int)$row['bulk_priority_score'];
        
        $components[] = $row;
    }
    
    $comp_stmt->close();
    
    if ($debug_mode) {
        $debug_info['queries_executed'][] = $components_query;
        $debug_info['raw_results']['components'] = $components;
        $debug_info['components_count'] = count($components);
    }
    
    // Calculate QR coverage percentage
    if ($qr_statistics['total_components'] > 0) {
        $qr_statistics['qr_coverage_percentage'] = round(
            ($qr_statistics['components_with_qr'] / $qr_statistics['total_components']) * 100, 
            2
        );
    }
    
    // Sort components by bulk priority score (highest first) for bulk operations
    usort($components, function($a, $b) {
        if ($a['bulk_priority_score'] === $b['bulk_priority_score']) {
            // Secondary sort by component name
            return strcmp($a['component_name'], $b['component_name']);
        }
        return $b['bulk_priority_score'] - $a['bulk_priority_score'];
    });
    
    // Generate bulk operation recommendations
    $bulk_recommendations = [];
    
    if ($qr_statistics['components_without_qr'] > 0) {
        if ($qr_statistics['components_without_qr'] >= 5) {
            $bulk_recommendations[] = [
                'action' => 'bulk_qr_generation',
                'priority' => 'high',
                'message' => "Disarankan untuk melakukan bulk QR generation untuk {$qr_statistics['components_without_qr']} komponen",
                'affected_items' => $qr_statistics['components_without_qr']
            ];
        } else {
            $bulk_recommendations[] = [
                'action' => 'individual_qr_generation',
                'priority' => 'medium',
                'message' => "Generate QR code untuk {$qr_statistics['components_without_qr']} komponen secara individual",
                'affected_items' => $qr_statistics['components_without_qr']
            ];
        }
    }
    
    if ($expiry_analysis['expiring_soon'] > 0) {
        $bulk_recommendations[] = [
            'action' => 'renewal_needed',
            'priority' => 'high',
            'message' => "{$expiry_analysis['expiring_soon']} komponen akan segera kedaluwarsa",
            'affected_items' => $expiry_analysis['expiring_soon']
        ];
    }
    
    if ($expiry_analysis['expired'] > 0) {
        $bulk_recommendations[] = [
            'action' => 'immediate_renewal',
            'priority' => 'critical',
            'message' => "{$expiry_analysis['expired']} komponen sudah kedaluwarsa dan memerlukan pembaruan segera",
            'affected_items' => $expiry_analysis['expired']
        ];
    }
    
    // Calculate execution time
    $execution_time = microtime(true) - $start_time;
    
    // Prepare enhanced response
    $response = [
        'success' => true,
        'color_id' => $color_id,
        'color_code' => $color_data['code_color'] ?? 'Unknown',
        'description' => $color_data['deskripsi'] ?? '',
        'buyer_name' => $color_data['nama_buyer'] ?? 'Unknown Buyer',
        'buyer_code' => $color_data['kode_buyer'] ?? '',
        'collection_surabaya_name' => $color_data['nama_collection_surabaya'] ?? 'Unknown collection_surabaya',
        'color_hex' => $color_data['kode_warna_hex'] ?? '#ffffff',
        'lemari' => $color_data['lemari'] ?? '',
        'color_status' => $color_data['color_status'] ?? 'Unknown',
        'components' => $components,
        'total_components' => count($components),
        'qr_statistics' => $qr_statistics,
        'status_distribution' => $status_distribution,
        'expiry_analysis' => $expiry_analysis,
        'bulk_recommendations' => $bulk_recommendations,
        'execution_time' => round($execution_time, 4),
        'timestamp' => date('Y-m-d H:i:s')
    ];
    
    // Add performance metrics
    $response['performance'] = [
        'query_time' => round($execution_time * 1000, 2) . 'ms',
        'components_per_second' => $execution_time > 0 ? round(count($components) / $execution_time, 0) : 0,
        'memory_usage' => round(memory_get_usage(true) / 1024 / 1024, 2) . 'MB'
    ];
    
    // Add bulk operation insights
    $response['bulk_insights'] = [
        'total_actionable_items' => $qr_statistics['components_without_qr'] + $expiry_analysis['expiring_soon'] + $expiry_analysis['expired'],
        'qr_generation_candidates' => array_filter($components, function($c) { return $c['can_bulk_qr']; }),
        'renewal_candidates' => array_filter($components, function($c) { return $c['requires_renewal']; }),
        'high_priority_items' => array_filter($components, function($c) { return $c['bulk_priority_score'] >= 10; })
    ];
    
    // Only include actual data counts in bulk insights
    $response['bulk_insights']['qr_generation_count'] = count($response['bulk_insights']['qr_generation_candidates']);
    $response['bulk_insights']['renewal_count'] = count($response['bulk_insights']['renewal_candidates']);
    $response['bulk_insights']['high_priority_count'] = count($response['bulk_insights']['high_priority_items']);
    
    // Remove the arrays to keep response size manageable
    unset($response['bulk_insights']['qr_generation_candidates']);
    unset($response['bulk_insights']['renewal_candidates']);
    unset($response['bulk_insights']['high_priority_items']);
    
    if ($debug_mode) {
        $response['debug'] = $debug_info;
        $response['debug']['final_components_count'] = count($components);
        $response['debug']['qr_statistics'] = $qr_statistics;
        $response['debug']['status_distribution'] = $status_distribution;
        $response['debug']['expiry_analysis'] = $expiry_analysis;
    }
    
    // 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 get_color_components_surabaya.php: " . $e->getMessage());
    error_log("Stack trace: " . $e->getTraceAsString());
    
    $error_response = [
        'success' => false,
        'error' => 'Exception occurred',
        'message' => 'Terjadi kesalahan saat mengambil data komponen: ' . $e->getMessage(),
        'color_id' => $color_id ?? null,
        'components' => [],
        'total_components' => 0,
        'qr_statistics' => [
            'total_components' => 0,
            'components_with_qr' => 0,
            'components_without_qr' => 0,
            'qr_coverage_percentage' => 0
        ],
        'timestamp' => date('Y-m-d H:i:s')
    ];
    
    if ($debug_mode) {
        $error_response['debug'] = $debug_info ?? [];
        $error_response['debug_error'] = [
            'message' => $e->getMessage(),
            'file' => $e->getFile(),
            'line' => $e->getLine(),
            'trace' => $e->getTraceAsString()
        ];
    }
    
    echo json_encode($error_response, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);
} finally {
    // Ensure any open statements are closed
    if (isset($color_stmt) && $color_stmt instanceof mysqli_stmt) {
        $color_stmt->close();
    }
    if (isset($comp_stmt) && $comp_stmt instanceof mysqli_stmt) {
        $comp_stmt->close();
    }
}
?>