<?php
// File: auto_status_update.php
// Purpose: This file is designed to be run as a daily cron job to automatically
// update item statuses based on expiry date and perform automatic renewal for expired items

// Include database connection
require_once dirname(__FILE__) . "/../config/connection.php";

// Get current date
$today = date('Y-m-d');
$log_file = dirname(__FILE__) . "/../logs/auto_update_" . date('Y-m-d') . ".log";

// Helper function to log messages
function log_message($message) {
    global $log_file;
    $timestamp = date('Y-m-d H:i:s');
    $log_entry = "[$timestamp] $message\n";
    file_put_contents($log_file, $log_entry, FILE_APPEND);
    echo $log_entry; // Also output to console/cron output
}

// Helper function to log to audit_logs_semarang table
function log_audit($conn, $action_type, $details) {
    $audit_stmt = $conn->prepare("
        INSERT INTO audit_logs_semarang 
        (action_type, user_id, user_type, details, timestamp, ip_address) 
        VALUES (?, NULL, 'system', ?, NOW(), 'CRON-JOB')
    ");
    
    if ($audit_stmt) {
        $audit_stmt->bind_param("ss", $action_type, $details);
        $audit_stmt->execute();
        $audit_stmt->close();
    }
}

log_message("Starting automatic status update process");

// 1. Update items approaching expiration (10 days before) to "Renew" status
$approaching_expiry_query = "
    UPDATE color_stock_items_semarang 
    SET status = 'Renew' 
    WHERE status = 'Tersedia'
    AND tanggal_kedaluwarsa BETWEEN '$today' AND DATE_ADD('$today', INTERVAL 10 DAY)
";

if ($conn->query($approaching_expiry_query)) {
    $affected = $conn->affected_rows;
    $message = "Updated $affected items approaching expiration to 'Renew' status";
    log_message($message);
    
    if ($affected > 0) {
        log_audit($conn, 'AUTO_STATUS_UPDATE', $message);
    }
} else {
    $error_message = "Error updating items approaching expiration: " . $conn->error;
    log_message($error_message);
    log_audit($conn, 'AUTO_UPDATE_ERROR', $error_message);
}

// 2. Set items that have passed their expiration date to "Expired" status
$mark_expired_query = "
    UPDATE color_stock_items_semarang 
    SET status = 'Expired' 
    WHERE (status = 'Tersedia' OR status = 'Renew')
    AND tanggal_kedaluwarsa < '$today'
";

if ($conn->query($mark_expired_query)) {
    $affected = $conn->affected_rows;
    $message = "Marked $affected expired items with 'Expired' status";
    log_message($message);
    
    if ($affected > 0) {
        log_audit($conn, 'AUTO_EXPIRE_ITEMS', $message);
    }
} else {
    $error_message = "Error marking expired items: " . $conn->error;
    log_message($error_message);
    log_audit($conn, 'AUTO_EXPIRE_ERROR', $error_message);
}

// 3. Automatically renew expired items
$expired_items_query = "
    SELECT id, color_id, component_id, kode_barang, stock_number, lokasi, qr_code,
           tanggal_pembuatan, tanggal_kedaluwarsa, keterangan, rack_no
    FROM color_stock_items_semarang
    WHERE status = 'Expired'
";

$result = $conn->query($expired_items_query);
$expired_count = $result->num_rows;
log_message("Found $expired_count expired items to automatically renew");

if ($expired_count > 0) {
    $renewed_count = 0;
    $failed_count = 0;
    
    while ($item = $result->fetch_assoc()) {
        // Start transaction
        $conn->begin_transaction();
        
        try {
            $stock_item_id = $item['id'];
            
            // Insert into renew_code_history_semarang
            $history_stmt = $conn->prepare("
                INSERT INTO renew_code_history_semarang 
                (original_id, color_id, component_id, kode_barang, stock_number, lokasi, qr_code, 
                tanggal_pembuatan, tanggal_kedaluwarsa, keterangan, renewed_date) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())
            ");
            
            $history_stmt->bind_param(
                "iiisisssss", 
                $stock_item_id, 
                $item['color_id'], 
                $item['component_id'], 
                $item['kode_barang'], 
                $item['stock_number'], 
                $item['lokasi'], 
                $item['qr_code'], 
                $item['tanggal_pembuatan'], 
                $item['tanggal_kedaluwarsa'], 
                $item['keterangan']
            );
            
            $history_stmt->execute();
            $history_stmt->close();
            
            // Find the next available code in the sequence
            $next_code_stmt = $conn->prepare("
                SELECT MAX(CAST(SUBSTRING(kode_barang, 4) AS UNSIGNED)) as last_code
                FROM color_stock_items_semarang
                WHERE kode_barang LIKE 'CP.%'
            ");
            $next_code_stmt->execute();
            $next_code_result = $next_code_stmt->get_result()->fetch_assoc();
            $next_code_stmt->close();
            
            $last_code = $next_code_result['last_code'];
            $new_code_number = $last_code + 1;
            $new_code = 'CP.' . str_pad($new_code_number, 4, '0', STR_PAD_LEFT);
            
            // Set new dates
            $today_date = new DateTime();
            $tanggal_pembuatan = $today_date->format('Y-m-d');
            
            $expiry_date = clone $today_date;
            $expiry_date->add(new DateInterval('P6M')); // Add 6 months
            $tanggal_kedaluwarsa = $expiry_date->format('Y-m-d');
            
            // Update the current item with new information
            $update_stmt = $conn->prepare("
                UPDATE color_stock_items_semarang 
                SET kode_barang = ?, 
                    status = 'Tersedia', 
                    tanggal_pembuatan = ?, 
                    tanggal_kedaluwarsa = ? 
                WHERE id = ?
            ");
            
            $update_stmt->bind_param("sssi", $new_code, $tanggal_pembuatan, $tanggal_kedaluwarsa, $stock_item_id);
            $update_stmt->execute();
            $update_stmt->close();
            
            // Log the automatic renew action to audit_logs_semarang
            $audit_details = "System automatically renewed expired color item ID: $stock_item_id, Old code: {$item['kode_barang']}, New code: $new_code, New expiry: $tanggal_kedaluwarsa";
            log_audit($conn, 'AUTO_RENEW_ITEM', $audit_details);
            
            // Commit transaction
            $conn->commit();
            log_message("Auto-renewed item ID: $stock_item_id with new code: $new_code");
            $renewed_count++;
            
        } catch (Exception $e) {
            // Rollback transaction on error
            $conn->rollback();
            $error_message = "Error auto-renewing item ID: $stock_item_id: " . $e->getMessage();
            log_message($error_message);
            log_audit($conn, 'AUTO_RENEW_ERROR', $error_message);
            $failed_count++;
        }
    }
    
    // Log summary to audit_logs_semarang
    $summary = "Auto-renewal process completed. Total items processed: $expired_count, Successfully renewed: $renewed_count, Failed: $failed_count";
    log_audit($conn, 'AUTO_RENEW_SUMMARY', $summary);
    log_message($summary);
    
} else {
    log_message("No expired items to process");
    log_audit($conn, 'AUTO_RENEW_SUMMARY', 'No expired items found to process');
}

$conn->close();
log_message("Automatic status update completed");