<?php
// No authentication required - Public API for dashboard
require_once "config/connection.php";

// Set timezone
date_default_timezone_set('Asia/Jakarta');

// Set JSON header
header('Content-Type: application/json');

try {
    // Get all copy panel items that have been borrowed (pernah_dipinjam = 1 AND item_type = 'copy_panel')
    // Master Color cannot be borrowed, so we filter them out
    $query = "
        SELECT 
            csi.id,
            csi.kode_barang,
            csi.status,
            csi.rack_no,
            csi.tanggal_pembuatan,
            csi.tanggal_kedaluwarsa,
            csi.pernah_dipinjam,
            csi.item_type,
            cc.component_name,
            mcp.code_color as panel_code,
            mcp.panel_type,
            mc.code_color as master_code,
            mc.name_color as master_name,
            b.nama_buyer,
            
            -- Get latest borrowing info from stock_transactions
            (
                SELECT st.reservation_number
                FROM transaction_items ti
                JOIN stock_transactions st ON ti.transaction_id = st.id
                WHERE ti.stock_item_id = csi.id
                AND st.status = 'Disetujui'
                ORDER BY st.tanggal_transaksi DESC
                LIMIT 1
            ) as reservation_number,
            
            (
                SELECT bu.nama_borrower
                FROM transaction_items ti
                JOIN stock_transactions st ON ti.transaction_id = st.id
                JOIN borrower_users bu ON st.borrower_id = bu.id
                WHERE ti.stock_item_id = csi.id
                AND st.status = 'Disetujui'
                ORDER BY st.tanggal_transaksi DESC
                LIMIT 1
            ) as peminjam,
            
            (
                SELECT st.tanggal_transaksi
                FROM transaction_items ti
                JOIN stock_transactions st ON ti.transaction_id = st.id
                WHERE ti.stock_item_id = csi.id
                AND st.status = 'Disetujui'
                AND st.status_peminjaman = 'Dipinjam'
                ORDER BY st.tanggal_transaksi DESC
                LIMIT 1
            ) as tanggal_peminjaman_terakhir,
            
            -- Calculate remaining days (90 days - 10 buffer - elapsed days)
            CASE 
                WHEN csi.tanggal_kedaluwarsa IS NOT NULL THEN
                    DATEDIFF(csi.tanggal_kedaluwarsa, NOW()) - 10
                ELSE NULL
            END as sisa_hari_adjusted,
            
            -- Calculate actual remaining days from kedaluwarsa
            CASE 
                WHEN csi.tanggal_kedaluwarsa IS NOT NULL THEN
                    DATEDIFF(csi.tanggal_kedaluwarsa, NOW())
                ELSE NULL
            END as sisa_hari_actual
            
        FROM color_stock_items csi
        JOIN color_components cc ON csi.component_id = cc.id
        JOIN master_color_panel mcp ON csi.color_id = mcp.id
        LEFT JOIN master_color mc ON mcp.master_color_id = mc.id
        JOIN buyers b ON mcp.buyer_id = b.id
        WHERE csi.pernah_dipinjam = 1 
        AND csi.item_type = 'copy_panel'
        ORDER BY 
            CASE 
                WHEN DATEDIFF(csi.tanggal_kedaluwarsa, NOW()) < 0 THEN 0
                WHEN DATEDIFF(csi.tanggal_kedaluwarsa, NOW()) <= 10 THEN 1
                WHEN csi.status = 'Dipinjam' THEN 2
                ELSE 3
            END,
            csi.tanggal_kedaluwarsa ASC
    ";
    
    $result = $conn->query($query);
    
    if (!$result) {
        throw new Exception("Query failed: " . $conn->error);
    }
    
    $items = [];
    
    while ($row = $result->fetch_assoc()) {
        // Calculate lama pinjam (days since first borrowed)
        $lama_pinjam = 0;
        if (!empty($row['tanggal_peminjaman_terakhir'])) {
            $tgl_pinjam = new DateTime($row['tanggal_peminjaman_terakhir']);
            $now = new DateTime('now', new DateTimeZone('Asia/Jakarta'));
            $interval = $tgl_pinjam->diff($now);
            $lama_pinjam = $interval->days;
        }
        
        // Use adjusted remaining days (90 - 10 buffer - elapsed)
        $sisa_hari = (int)$row['sisa_hari_adjusted'];
        
        // Format item data
        $item = [
            'id' => (int)$row['id'],
            'kode_barang' => $row['kode_barang'],
            'status' => $row['status'],
            'rack_no' => $row['rack_no'],
            'component_name' => $row['component_name'],
            'panel_code' => $row['panel_code'],
            'panel_type' => $row['panel_type'],
            'master_code' => $row['master_code'],
            'master_name' => $row['master_name'],
            'buyer_name' => $row['nama_buyer'],
            'reservation_number' => $row['reservation_number'],
            'peminjam' => $row['peminjam'],
            'tanggal_pembuatan' => $row['tanggal_pembuatan'],
            'tanggal_kedaluwarsa' => $row['tanggal_kedaluwarsa'],
            'lama_pinjam' => $lama_pinjam,
            'sisa_hari' => $sisa_hari,
            'sisa_hari_actual' => (int)$row['sisa_hari_actual'],
            'item_type' => $row['item_type'],
            'item_type_label' => 'Copy Panel' // Always copy panel in this query
        ];
        
        $items[] = $item;
    }
    
    // Return JSON response
    echo json_encode([
        'success' => true,
        'items' => $items,
        'total' => count($items),
        'timestamp' => date('Y-m-d H:i:s'),
        'note' => 'Only copy panel items are shown. Master Color items cannot be borrowed.'
    ], JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);
    
} catch (Exception $e) {
    error_log("Error in get_monitoring_data2.php: " . $e->getMessage());
    
    echo json_encode([
        'success' => false,
        'error' => $e->getMessage(),
        'items' => [],
        'total' => 0
    ], JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);
}

$conn->close();
?>