<?php
// Start session
session_start();

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

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

// Check if user is logged in and is IT user
if (!is_logged_in() || !is_user_type('it')) {
    http_response_code(401);
    echo json_encode(['error' => 'Unauthorized access']);
    exit();
}

// Get the request type
$type = isset($_GET['type']) ? $_GET['type'] : '';

try {
    switch ($type) {
        case 'ongoing':
            $data = getOngoingTransactions($conn);
            break;
        case 'dipinjam':
            $data = getDipinjamItems($conn);
            break;
        case 'overdue':
            $data = getOverdueTransactions($conn);
            break;
        case 'renew':
            $data = getRenewItems($conn);
            break;
        default:
            throw new Exception('Invalid request type');
    }
    
    echo json_encode($data);
    
} catch (Exception $e) {
    http_response_code(500);
    echo json_encode(['error' => $e->getMessage()]);
}

// Function to get ongoing transactions
function getOngoingTransactions($conn) {
    $stmt = $conn->prepare("
        SELECT 
            st.reservation_number,
            bu.nama_borrower as peminjam,
            bu.nik,
            bu.lokasi_posisi,
            DATE_FORMAT(st.tanggal_transaksi, '%d-%m-%Y %H:%i') as tanggal_transaksi,
            DATE_FORMAT(st.tanggal_pengembalian, '%d-%m-%Y %H:%i') as tanggal_pengembalian,
            st.status_peminjaman,
            st.keterangan,
            st.status,
            COUNT(ti.id) as total_items
        FROM stock_transactions_surabaya st
        INNER JOIN borrower_users_surabaya bu ON st.borrower_id = bu.id
        LEFT JOIN transaction_items_surabaya ti ON st.id = ti.transaction_id
        WHERE st.status_peminjaman = 'Dipinjam'
        GROUP BY st.id
        ORDER BY st.tanggal_transaksi DESC
    ");
    
    $stmt->execute();
    $result = $stmt->get_result();
    $data = [];
    
    while ($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
    
    $stmt->close();
    return $data;
}

// Function to get items that are currently borrowed
function getDipinjamItems($conn) {
    $stmt = $conn->prepare("
        SELECT 
            csi.kode_barang,
            mcp.code_color,
            cc.component_name,
            b.nama_buyer,
            csi.lokasi,
            csi.rack_no,
            csi.keterangan,
            csi.tanggal_pembuatan,
            csi.tanggal_kedaluwarsa,
            st.reservation_number,
            bu.nama_borrower as peminjam,
            bu.nik,
            bu.lokasi_posisi,
            DATE_FORMAT(st.tanggal_transaksi, '%d-%m-%Y %H:%i') as tanggal_pinjam
        FROM color_stock_items_surabaya csi
        INNER JOIN master_color_panel_surabaya mcp ON csi.color_id = mcp.id
        INNER JOIN color_components_surabaya cc ON csi.component_id = cc.id
        INNER JOIN buyers_surabaya b ON mcp.buyer_id = b.id
        LEFT JOIN transaction_items_surabaya ti ON csi.id = ti.stock_item_id AND ti.dikembalikan = 0
        LEFT JOIN stock_transactions_surabaya st ON ti.transaction_id = st.id
        LEFT JOIN borrower_users_surabaya bu ON st.borrower_id = bu.id
        WHERE csi.status = 'Dipinjam'
        ORDER BY csi.kode_barang ASC
    ");
    
    $stmt->execute();
    $result = $stmt->get_result();
    $data = [];
    
    while ($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
    
    $stmt->close();
    return $data;
}

// Function to get overdue transactions
function getOverdueTransactions($conn) {
    $stmt = $conn->prepare("
        SELECT 
            st.reservation_number,
            bu.nama_borrower as peminjam,
            bu.nik,
            bu.lokasi_posisi,
            DATE_FORMAT(st.tanggal_pengembalian, '%d-%m-%Y %H:%i') as tanggal_pengembalian,
            COUNT(ti.id) as total_items,
            CASE 
                WHEN TIMESTAMPDIFF(DAY, st.tanggal_pengembalian, NOW()) > 0 THEN 
                    CONCAT(TIMESTAMPDIFF(DAY, st.tanggal_pengembalian, NOW()), ' hari, ',
                           TIMESTAMPDIFF(HOUR, st.tanggal_pengembalian, NOW()) % 24, ' jam')
                WHEN TIMESTAMPDIFF(HOUR, st.tanggal_pengembalian, NOW()) > 0 THEN 
                    CONCAT(TIMESTAMPDIFF(HOUR, st.tanggal_pengembalian, NOW()), ' jam, ',
                           TIMESTAMPDIFF(MINUTE, st.tanggal_pengembalian, NOW()) % 60, ' menit')
                ELSE 
                    CONCAT(TIMESTAMPDIFF(MINUTE, st.tanggal_pengembalian, NOW()), ' menit')
            END as keterlambatan,
            st.keterangan
        FROM stock_transactions_surabaya st
        INNER JOIN borrower_users_surabaya bu ON st.borrower_id = bu.id
        INNER JOIN transaction_items_surabaya ti ON st.id = ti.transaction_id
        WHERE st.status_peminjaman = 'Dipinjam' 
        AND ti.dikembalikan = 0
        AND TIMESTAMPDIFF(MINUTE, st.tanggal_pengembalian, NOW()) > 15
        GROUP BY st.id
        ORDER BY st.tanggal_pengembalian ASC
    ");
    
    $stmt->execute();
    $result = $stmt->get_result();
    $data = [];
    
    while ($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
    
    $stmt->close();
    return $data;
}

// Function to get items that need renewal
function getRenewItems($conn) {
    $stmt = $conn->prepare("
        SELECT 
            csi.kode_barang,
            mcp.code_color,
            cc.component_name,
            b.nama_buyer,
            csi.lokasi,
            csi.rack_no,
            csi.keterangan,
            DATE_FORMAT(csi.tanggal_pembuatan, '%d-%m-%Y') as tanggal_pembuatan,
            DATE_FORMAT(csi.tanggal_kedaluwarsa, '%d-%m-%Y') as tanggal_kedaluwarsa,
            CASE 
                WHEN csi.tanggal_kedaluwarsa < CURDATE() THEN 'Expired'
                WHEN csi.tanggal_kedaluwarsa <= DATE_ADD(CURDATE(), INTERVAL 30 DAY) THEN 'Akan Expired'
                ELSE 'Normal'
            END as status_expired,
            DATEDIFF(csi.tanggal_kedaluwarsa, CURDATE()) as hari_tersisa
        FROM color_stock_items_surabaya csi
        INNER JOIN master_color_panel_surabaya mcp ON csi.color_id = mcp.id
        INNER JOIN color_components_surabaya cc ON csi.component_id = cc.id
        INNER JOIN buyers_surabaya b ON mcp.buyer_id = b.id
        WHERE csi.status = 'Renew'
        ORDER BY csi.tanggal_kedaluwarsa ASC, csi.kode_barang ASC
    ");
    
    $stmt->execute();
    $result = $stmt->get_result();
    $data = [];
    
    while ($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
    
    $stmt->close();
    return $data;
}

// Log this API access
$it_id = $_SESSION['user_id'];
create_audit_log($conn, 'Dashboard Data Access', $it_id, 'it', "IT user accessed dashboard data for type: $type");
?>