<?php
require_once('includes/load.php');
page_require_level(3); // Sesuaikan level akses jika diperlukan, misal level 2 untuk staff PR

// Get month and year parameters from URL (default: current month/year)
$month = isset($_GET['month']) ? intval($_GET['month']) : date('m');
$year = isset($_GET['year']) ? intval($_GET['year']) : date('Y');

// Function to generate a unique PR Code
function generate_pr_code($month, $year) {
    global $db;
    $prefix = "PR-" . $year . sprintf('%02d', $month) . "-"; // e.g., PR-202506-
    
    // Get the last PR code for the current month/year to determine the sequence number
    $sql = "SELECT pr_code FROM purchase_request_header 
            WHERE for_month = '{$month}' AND for_year = '{$year}'
            ORDER BY pr_code DESC LIMIT 1";
    $result = $db->query($sql);
    $last_code = $db->fetch_assoc($result);

    $sequence_number = 1;
    if ($last_code) {
        // Extract the number part and increment
        preg_match('/-(\d+)$/', $last_code['pr_code'], $matches);
        if (isset($matches[1])) {
            $sequence_number = intval($matches[1]) + 1;
        }
    }
    
    return $prefix . sprintf('%04d', $sequence_number); // e.g., PR-202506-0001
}


// Function to get understocked materials for Purchase Request
function get_understocked_materials_for_pr($month, $year) {
    global $db;
    $understocked_items = array();

    // Query to get forecast data and product info, including buy_price
    $sql = "SELECT 
                mf.component AS part_number,
                p.new_code,
                p.name,
                p.UoM AS uom,
                p.buy_price AS buy_price,
                SUM(mf.quantity_needed) AS total_needed,
                p.quantity AS main_stock,
                IFNULL((
                    SELECT SUM(pl.quantity) 
                    FROM prod_loc pl 
                    WHERE pl.product_id = p.id
                ), 0) AS location_stock
            FROM material_forecasts mf
            JOIN products p ON mf.component = p.part_number
            WHERE mf.forecast_month = '{$month}' 
            AND mf.forecast_year = '{$year}'
            AND p.part_number NOT LIKE 'FL%'
            GROUP BY p.id, p.part_number, p.new_code, p.name, p.UoM, p.quantity, p.buy_price";
    
    $result = $db->query($sql);

    if ($result && $db->num_rows($result) > 0) {
        while ($row = $db->fetch_assoc($result)) {
            $part_number = $row['part_number'];

            // Get consumption data for the current month/year and part number
            $sql_consumption = "SELECT SUM(quantity_used) AS total_consumed
                               FROM material_consumption_records
                               WHERE component_part = '{$part_number}'
                               AND MONTH(consumption_date) = '{$month}'
                               AND YEAR(consumption_date) = '{$year}'";
            
            $consumption_data = $db->query($sql_consumption);
            $consumption = $db->fetch_assoc($consumption_data);
            $total_consumed = $consumption['total_consumed'] ?? 0;

            $total_needed = (float)$row['total_needed'];
            $total_stock = (float)$row['main_stock'] + (float)$row['location_stock'];
            $buy_price = (float)$row['buy_price'];

            $sisa_plan = max(0, $total_needed - $total_consumed);
            $balance = $total_stock - $sisa_plan;

            // Only add to list if stock is negative (understocked)
            if ($balance < 0) {
                $quantity_to_request = abs($balance); // How much is needed to cover the deficit
                $estimated_cost = $quantity_to_request * $buy_price;

                $understocked_items[] = array(
                    'part_number' => $row['part_number'],
                    'new_code' => $row['new_code'],
                    'name' => $row['name'],
                    'uom' => $row['uom'],
                    'current_stock' => number_format($total_stock, 2),
                    'balance_needed' => number_format($balance, 2), // Will be negative
                    'suggested_quantity' => number_format($quantity_to_request, 2),
                    'buy_price' => number_format($buy_price, 2),
                    'estimated_cost' => number_format($estimated_cost, 2),
                    'raw_suggested_quantity' => $quantity_to_request, // For form processing
                    'raw_buy_price' => $buy_price // For form processing
                );
            }
        }
    }
    return $understocked_items;
}

$understocked_materials = get_understocked_materials_for_pr($month, $year);

// --- Handle Form Submission for Purchase Request ---
if (isset($_POST['create_pr']) && !empty($_POST['pr_items'])) {
    $pr_items_selected = $_POST['pr_items']; // Items from the form
    $current_date = date('Y-m-d H:i:s');
    $user_id = $_SESSION['user_id']; // Assuming user ID is stored in session
    
    // Filter out items that were not checked or have 0 quantity
    $pr_items_to_process = array_filter($pr_items_selected, function($item) {
        return isset($item['selected']) && $item['selected'] == '1' && (float)$item['quantity'] > 0;
    });

    if (empty($pr_items_to_process)) {
        $session->msg('w', "Tidak ada material yang dipilih atau kuantitas 0 untuk dibuat Purchase Request.");
        redirect('purchase_request_plan.php?month=' . $month . '&year=' . $year, false);
    }

    $db->begin(); // Start transaction

    try {
        // 1. Insert into purchase_request_header
        $pr_code = generate_pr_code($month, $year);
        $sql_insert_header = "INSERT INTO purchase_request_header (
                                pr_code, 
                                request_date, 
                                requested_by_user_id, 
                                status, 
                                for_month, 
                                for_year
                            ) VALUES (
                                '{$db->escape($pr_code)}', 
                                '{$current_date}', 
                                '{$db->escape($user_id)}', 
                                'Pending', 
                                '{$db->escape($month)}', 
                                '{$db->escape($year)}'
                            )";
        
        if (!$db->query($sql_insert_header)) {
            throw new Exception("Failed to create Purchase Request Header: " . $db->error()); // Added $db->error() for more detail
        }
        $pr_header_id = $db->insert_id(); // Get the ID of the newly inserted header

        // 2. Insert into purchase_request_items_plan
        foreach ($pr_items_to_process as $item_data) {
            $part_number = $db->escape($item_data['part_number']);
            $quantity = (float)$item_data['quantity'];
            $price_per_unit = (float)$item_data['buy_price'];
            // total_item_price is a GENERATED ALWAYS column, no need to insert

            $sql_insert_item = "INSERT INTO purchase_request_items_plan (
                                pr_header_id, 
                                part_number, 
                                requested_quantity, 
                                unit_price
                            ) VALUES (
                                '{$pr_header_id}', 
                                '{$part_number}', 
                                '{$quantity}', 
                                '{$price_per_unit}'
                            )";
            if (!$db->query($sql_insert_item)) {
                throw new Exception("Failed to insert item {$part_number} into Purchase Request items: " . $db->error()); // Added $db->error()
            }
        }
        
        $db->commit(); // Commit transaction
        $session->msg('s', "Purchase Request **{$pr_code}** for " . date('F Y', mktime(0, 0, 0, $month, 1, $year)) . " created successfully!");
        redirect('purchase_request_plan.php?month=' . $month . '&year=' . $year, false); // Redirect back to refresh
    } catch (Exception $e) {
        $db->rollback(); // Rollback transaction on error
        $session->msg('d', "Failed to create Purchase Request: " . $e->getMessage());
        redirect('purchase_request_plan.php?month=' . $month . '&year=' . $year, false); // Redirect back to show error
    }
}


include_once('layouts/header.php');
?>

<div class="row">
    <div class="col-md-12">
        <?php echo display_msg($session->msg); ?>
        
        <div class="panel panel-info">
            <div class="panel-heading">
                <strong><i class="glyphicon glyphicon-shopping-cart"></i> Create Purchase Request - <?php echo date('F Y', mktime(0, 0, 0, $month, 1, $year)); ?></strong>
            </div>
            <div class="panel-body">
                <?php if (!empty($understocked_materials)) : ?>
                    <form action="purchase_request_plan.php?month=<?php echo $month; ?>&year=<?php echo $year; ?>" method="post">
                        <div class="alert alert-warning" role="alert">
                            Review the suggested quantities below. You can adjust the "Quantity to Request" for each item before submitting the Purchase Request.
                        </div>
                        <div class="table-responsive">
                            <table class="table table-bordered table-striped">
                                <thead>
                                    <tr>
                                        <th class="text-center" style="width: 50px;">Select</th>
                                        <th>Part Number</th>
                                        <th>New Code</th>
                                        <th>Material Name</th>
                                        <th>UOM</th>
                                        <th class="text-right">Current Stock</th>
                                        <th class="text-right">Balance Needed</th>
                                        <th class="text-right">Quantity to Request</th>
                                        <th class="text-right">Buy Price (Rp)</th>
                                        <th class="text-right">Estimated Cost (Rp)</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php foreach ($understocked_materials as $item) : ?>
                                        <tr>
                                            <td class="text-center">
                                                <input type="checkbox" name="pr_items[<?php echo $db->escape($item['part_number']); ?>][selected]" value="1" checked>
                                                <input type="hidden" name="pr_items[<?php echo $db->escape($item['part_number']); ?>][part_number]" value="<?php echo remove_junk($item['part_number']); ?>">
                                                <input type="hidden" name="pr_items[<?php echo $db->escape($item['part_number']); ?>][buy_price]" value="<?php echo $item['raw_buy_price']; ?>">
                                            </td>
                                            <td><?php echo remove_junk($item['part_number']); ?></td>
                                            <td><?php echo remove_junk($item['new_code']); ?></td>
                                            <td><?php echo remove_junk($item['name']); ?></td>
                                            <td><?php echo remove_junk($item['uom']); ?></td>
                                            <td class="text-right"><?php echo $item['current_stock']; ?></td>
                                            <td class="text-right text-danger"><?php echo $item['balance_needed']; ?></td>
                                            <td class="text-right">
                                                <input type="number" step="0.01" min="0" class="form-control text-right" name="pr_items[<?php echo $db->escape($item['part_number']); ?>][quantity]" value="<?php echo $item['raw_suggested_quantity']; ?>">
                                            </td>
                                            <td class="text-right"><?php echo $item['buy_price']; ?></td>
                                            <td class="text-right"><?php echo $item['estimated_cost']; ?></td>
                                        </tr>
                                    <?php endforeach; ?>
                                </tbody>
                            </table>
                        </div>
                        <div class="clearfix">
                            <button type="submit" name="create_pr" class="btn btn-success pull-right"><i class="glyphicon glyphicon-plus-sign"></i> Create Selected Purchase Request</button>
                            <a href="material_calculation.php?month=<?php echo $month; ?>&year=<?php echo $year; ?>" class="btn btn-default pull-left">Cancel & Back to Material Calculation</a>
                        </div>
                    </form>
                <?php else : ?>
                    <div class="alert alert-info text-center" role="alert">
                        Tidak ada material yang kekurangan stok untuk bulan **<?php echo date('F Y', mktime(0, 0, 0, $month, 1, $year)); ?>**.
                        Anda bisa kembali ke halaman kalkulasi material.
                    </div>
                    <div class="text-center">
                        <a href="material_calculation.php?month=<?php echo $month; ?>&year=<?php echo $year; ?>" class="btn btn-default">Back to Material Calculation</a>
                    </div>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>

<?php include_once('layouts/footer.php'); ?>