<?php
// Start session
session_start();

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

// Redirect if not IT
if (!is_logged_in() || !is_user_type('it')) {
    header('Content-Type: application/json');
    echo json_encode(['error' => 'Unauthorized access - IT access required']);
    exit();
}

// Get renew history with additional information from all related tables
$history_query = "
    SELECT 
        rch.*,
        DATE_FORMAT(rch.renewed_date, '%d %b %Y %H:%i') AS formatted_renewed_date,
        csi.kode_barang AS new_code,
        mcp.code_color,
        cc.component_name,
        b.nama_buyer,
        b.kode_buyer,
        mcp.lemari,
        csi.rack_no,
        csi.lokasi AS current_location,
        csi.status AS current_status
    FROM 
        renew_code_history rch
    JOIN 
        color_stock_items csi ON rch.original_id = csi.id
    JOIN 
        master_color_panel mcp ON rch.color_id = mcp.id
    JOIN 
        color_components cc ON rch.component_id = cc.id
    JOIN 
        buyers b ON mcp.buyer_id = b.id
    ORDER BY 
        rch.renewed_date DESC
    LIMIT 100
";

$history_result = $conn->query($history_query);
$history_items = [];
$stats = [
    'total_renewals' => 0,
    'renewals_this_month' => 0,
    'renewals_today' => 0,
    'most_renewed_buyer' => '',
    'buyer_counts' => []
];

if ($history_result) {
    $today = date('Y-m-d');
    $this_month = date('Y-m');
    
    while ($row = $history_result->fetch_assoc()) {
        // Add responsive-specific data formatting
        if (strlen($row['component_name']) > 15) {
            $row['component_name_short'] = substr($row['component_name'], 0, 15) . '...';
        } else {
            $row['component_name_short'] = $row['component_name'];
        }
        
        // Format dates for better display
        $row['formatted_renewed_date_short'] = date('d/m/y H:i', strtotime($row['renewed_date']));
        
        // Calculate days since renewal
        $renewed_date = new DateTime($row['renewed_date']);
        $now = new DateTime();
        $interval = $now->diff($renewed_date);
        $row['days_since_renewal'] = $interval->days;
        
        // Add renewal period info
        if ($row['days_since_renewal'] == 0) {
            $row['renewal_period'] = 'Hari ini';
        } elseif ($row['days_since_renewal'] == 1) {
            $row['renewal_period'] = 'Kemarin';
        } elseif ($row['days_since_renewal'] <= 7) {
            $row['renewal_period'] = $row['days_since_renewal'] . ' hari yang lalu';
        } elseif ($row['days_since_renewal'] <= 30) {
            $weeks = floor($row['days_since_renewal'] / 7);
            $row['renewal_period'] = $weeks . ' minggu yang lalu';
        } else {
            $months = floor($row['days_since_renewal'] / 30);
            $row['renewal_period'] = $months . ' bulan yang lalu';
        }
        
        // Collect statistics
        $stats['total_renewals']++;
        
        if (strpos($row['renewed_date'], $today) !== false) {
            $stats['renewals_today']++;
        }
        
        if (strpos($row['renewed_date'], $this_month) !== false) {
            $stats['renewals_this_month']++;
        }
        
        // Count by buyer
        if (!isset($stats['buyer_counts'][$row['nama_buyer']])) {
            $stats['buyer_counts'][$row['nama_buyer']] = 0;
        }
        $stats['buyer_counts'][$row['nama_buyer']]++;
        
        $history_items[] = $row;
    }
    
    // Find most renewed buyer
    if (!empty($stats['buyer_counts'])) {
        $max_count = max($stats['buyer_counts']);
        foreach ($stats['buyer_counts'] as $buyer => $count) {
            if ($count == $max_count) {
                $stats['most_renewed_buyer'] = $buyer . ' (' . $count . ' items)';
                break;
            }
        }
    }
}

// Log the access using create_audit_log
$it_id = $_SESSION['user_id'];
create_audit_log($conn, 'View Renew History', $it_id, 'it', 'IT user viewed color renew history');

// Get summary of upcoming expirations
$upcoming_query = "
    SELECT 
        COUNT(CASE WHEN DATEDIFF(tanggal_kedaluwarsa, CURDATE()) BETWEEN 0 AND 7 THEN 1 END) as expiring_week,
        COUNT(CASE WHEN DATEDIFF(tanggal_kedaluwarsa, CURDATE()) BETWEEN 0 AND 30 THEN 1 END) as expiring_month,
        COUNT(CASE WHEN tanggal_kedaluwarsa < CURDATE() THEN 1 END) as already_expired
    FROM color_stock_items
    WHERE status IN ('Tersedia', 'Dipinjam')
";

$upcoming_result = $conn->query($upcoming_query);
$upcoming_stats = $upcoming_result->fetch_assoc();

// Get additional statistics for the renewal history
$additional_stats_query = "
    SELECT 
        COUNT(DISTINCT rch.color_id) as total_colors_renewed,
        COUNT(DISTINCT rch.component_id) as total_components_renewed,
        COUNT(DISTINCT mcp.buyer_id) as total_buyers_affected,
        AVG(DATEDIFF(rch.renewed_date, rch.tanggal_kedaluwarsa)) as avg_days_overdue
    FROM renew_code_history rch
    JOIN master_color_panel mcp ON rch.color_id = mcp.id
    WHERE rch.renewed_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
";

$additional_result = $conn->query($additional_stats_query);
$additional_stats = $additional_result->fetch_assoc();

// Get code generation statistics
$code_stats_query = "
    SELECT 
        MIN(CAST(kode_barang AS UNSIGNED)) as min_code,
        MAX(CAST(kode_barang AS UNSIGNED)) as max_code,
        COUNT(*) as total_codes_generated
    FROM color_stock_items
    WHERE kode_barang REGEXP '^[0-9]+
";

$code_result = $conn->query($code_stats_query);
$code_stats = $code_result->fetch_assoc();

// Prepare response with enhanced data
$response = [
    'history' => $history_items,
    'stats' => $stats,
    'upcoming' => $upcoming_stats,
    'additional_stats' => $additional_stats,
    'code_stats' => $code_stats,
    'generated_at' => date('Y-m-d H:i:s'),
    'database_info' => [
        'total_items_in_system' => 0,
        'items_by_status' => []
    ]
];

// Get additional database information
$db_info_query = "
    SELECT 
        status,
        COUNT(*) as count
    FROM color_stock_items
    GROUP BY status
";

$db_info_result = $conn->query($db_info_query);
$total_items = 0;
while ($status_row = $db_info_result->fetch_assoc()) {
    $response['database_info']['items_by_status'][$status_row['status']] = $status_row['count'];
    $total_items += $status_row['count'];
}
$response['database_info']['total_items_in_system'] = $total_items;

// Add performance metrics
$response['performance'] = [
    'query_execution_time' => number_format((microtime(true) - $_SERVER['REQUEST_TIME_FLOAT']) * 1000, 2) . 'ms',
    'memory_usage' => number_format(memory_get_usage() / 1024 / 1024, 2) . 'MB'
];

// Return JSON response
header('Content-Type: application/json');
echo json_encode($response);
exit();
?>