<?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
$it_id = $_SESSION['user_id'];
log_activity($conn, $it_id, 'it', 'View Renew History', '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();

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

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