<?php

ini_set('display_errors', 1);
error_reporting(E_ALL);
require_once('includes/load.php');

page_require_level(3);

// Get month and year parameters (default: current month/year)
$month = isset($_GET['month']) ? intval($_GET['month']) : date('m');
$year = isset($_GET['year']) ? intval($_GET['year']) : date('Y');
$search_query = isset($_GET['search']) ? $db->escape($_GET['search']) : '';

// Function to get material calculation data with consumption
function get_material_calculation($month, $year, $search_query = '') {
    global $db;
    
    $results = array();
    
    $sql_forecast = "SELECT 
                        mf.component AS part_number,
                        p.name,
                        p.UoM AS uom,
                        SUM(mf.quantity_needed) AS total_needed,
                        p.quantity AS main_stock,
                        IFNULL((
                            SELECT SUM(i.quantity) 
                            FROM inventory i 
                            WHERE i.part_number = mf.component
                        ), 0) AS location_stock,
                        (p.quantity + IFNULL((
                            SELECT SUM(i.quantity) 
                            FROM inventory i 
                            WHERE i.part_number = mf.component
                        ), 0)) AS total_stock,
                        p.buy_price AS buy_price,
                        0 AS safety_stock
                    FROM material_forecasts mf
                    JOIN products p ON mf.component = p.part_number
                    WHERE mf.forecast_month = '{$month}' 
                    AND mf.forecast_year = '{$year}'";

    if (!empty($search_query)) {
        $sql_forecast .= " AND (mf.component LIKE '%{$search_query}%' OR p.name LIKE '%{$search_query}%')";
    }

    $sql_forecast .= " GROUP BY p.id, p.part_number, p.name, p.UoM, p.quantity, p.buy_price";
    
    $forecast_data = $db->query($sql_forecast);
    
    if($forecast_data && $db->num_rows($forecast_data) > 0) {
        while($row = $db->fetch_assoc($forecast_data)) {
            $part_number = $row['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'];
            $main_stock = (float)$row['main_stock'];
            $location_stock = (float)$row['location_stock'];
            $total_stock = (float)$row['total_stock'];
            $safety_stock = (float)$row['safety_stock'];
            $buy_price = (float)$row['buy_price'];
            
            // Calculate remaining plan
            $sisa_plan = max(0, $total_needed - $total_consumed);
            
            // Calculate balance (total stock - remaining plan)
            $balance = $total_stock - $sisa_plan;

            // Calculate total amount (buy_price * balance)
            $total_amount = $buy_price * $balance;
            
            $consumption_percentage = ($total_needed > 0) ? ($total_consumed / $total_needed) * 100 : 0;
            
            // Status based on balance (without numbers)
            $status = '';
            if ($balance >= 0) {
                $status = '<span class="label label-success">Stock Cukup</span>';
            } else {
                $status = '<span class="label label-danger">Stock Kurang</span>';
            }
            
            // Format numbers
            $results[] = array(
                'part_number' => $row['part_number'],
                'name' => $row['name'],
                'uom' => $row['uom'],
                'total_needed' => number_format($total_needed, 2),
                'total_consumed' => number_format($total_consumed, 2),
                'sisa_plan' => number_format($sisa_plan, 2),
                'consumption_percentage' => number_format($consumption_percentage, 1),
                'main_stock' => number_format($main_stock, 2),
                'location_stock' => number_format($location_stock, 2),
                'total_stock' => number_format($total_stock, 2),
                'balance_raw' => $balance, // Keep raw balance for calculation
                'balance' => ($balance >= 0) ? 
                    '<span class="text-success">+'.number_format($balance, 2).'</span>' : 
                    '<span class="text-danger">'.number_format($balance, 2).'</span>',
                'buy_price_raw' => $buy_price, // Keep raw buy_price for calculation
                'buy_price' => number_format($buy_price, 2),
                'total_amount' => number_format($total_amount, 2),
                'status' => $status
            );
        }
    }
    
    return $results;
}

$material_data = get_material_calculation($month, $year, $search_query);

// Calculate Total Amount for Understocked Materials
$total_amount_understock = 0;
foreach ($material_data as $item) {
    if ($item['balance_raw'] < 0) {
        $total_amount_understock += abs($item['balance_raw']) * $item['buy_price_raw'];
    }
}

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

<style>
/* Fixed header styling */
.scrollable-table {
    max-height: 70vh;
    overflow-y: auto;
    margin-bottom: 20px;
    border: 1px solid #ddd;
}

.scrollable-table table {
    width: 100%;
    border-collapse: collapse;
}

.scrollable-table thead th {
    position: sticky;
    top: 0;
    background-color: #f5f5f5;
    z-index: 10;
    box-shadow: 0 2px 2px -1px rgba(0,0,0,0.1);
    padding: 12px 8px;
}

/* Table styling enhancements */
.scrollable-table tbody tr:nth-child(even) {
    background-color: #f9f9f9;
}

.scrollable-table tbody tr:hover {
    background-color: #f1f1f1;
}

.text-right {
    text-align: right;
}

.label {
    display: inline-block;
    padding: 3px 6px;
    border-radius: 3px;
    font-size: 12px;
    font-weight: bold;
}

.label-success {
    background-color: #5cb85c;
    color: white;
}

.label-warning {
    background-color: #f0ad4e;
    color: white;
}

.label-danger {
    background-color: #d9534f;
    color: white;
}

.text-success {
    color: #5cb85c;
}

.text-danger {
    color: #d9534f;
}

.total-amount-display {
    font-size: 1.2em;
    font-weight: bold;
    margin-top: 15px;
    margin-bottom: 15px;
    padding: 10px;
    background-color: #f2dede;
    border: 1px solid #ebccd1;
    color: #a94442;
    border-radius: 4px;
    text-align: center;
}
</style>

<div class="row">
    <div class="col-md-12">
        <form action="material_calculation.php" method="get" class="form-inline">
            <div class="form-group">
                <label for="month">Month:</label>
                <select name="month" id="month" class="form-control">
                    <?php for ($m = 1; $m <= 12; $m++) : ?>
                        <option value="<?php echo $m; ?>" <?php echo ($month == $m) ? 'selected' : ''; ?>>
                            <?php echo date('F', mktime(0, 0, 0, $m, 1)); ?>
                        </option>
                    <?php endfor; ?>
                </select>
            </div>
            <div class="form-group">
                <label for="year">Year:</label>
                <select name="year" id="year" class="form-control">
                    <?php for ($y = date('Y') + 2; $y >= 2000; $y--) : ?>
                        <option value="<?php echo $y; ?>" <?php echo ($year == $y) ? 'selected' : ''; ?>>
                            <?php echo $y; ?>
                        </option>
                    <?php endfor; ?>
                </select>
            </div>
            <div class="form-group">
                <label for="search">Search:</label>
                <input type="text" name="search" id="search" class="form-control" placeholder="Part No, Name" value="<?php echo htmlspecialchars($search_query); ?>">
            </div>
            <button type="submit" class="btn btn-primary">Filter</button>
            <a href="consume_output.php?month=<?php echo $month; ?>&year=<?php echo $year; ?>" 
               class="btn btn-info ml-2">
                <i class="glyphicon glyphicon-transfer"></i> View Consumption Details
            </a>
        </form>
        
        <div class="panel panel-info mt-3">
            <div class="panel-heading">
                <strong>Material Calculation - <?php echo date('F Y', mktime(0, 0, 0, $month, 1, $year)); ?></strong>
            </div>
            <div class="panel-body">
                <div class="btn-group mb-3">
                    <button id="export-excel" class="btn btn-success">Export to Excel</button>
                    <a href="mrp_forecasting.php?month=<?php echo $month; ?>&year=<?php echo $year; ?>" 
                       class="btn btn-default">
                        Back to Forecasting
                    </a>
                </div>
                
                <?php if ($total_amount_understock > 0) : ?>
                    <div class="total-amount-display">
                        Total Estimated Cost for Understocked Materials: Rp <?php echo number_format($total_amount_understock, 2); ?>
                    </div>
                <?php else : ?>
                    <div class="total-amount-display alert-success" style="background-color: #dff0d8; border-color: #d6e9c6; color: #3c763d;">
                        All materials are sufficiently stocked. No estimated cost for understocked materials.
                    </div>
                <?php endif; ?>

                <div class="scrollable-table">
                    <table id="material-calculation" class="table table-bordered table-striped">
                        <thead>
                            <tr>
                                <th>Part Number</th>
                                <th>Material Name</th>
                                <th>UOM</th>
                                <th class="text-right">Plan Need</th>
                                <th class="text-right">Consumed</th>
                                <th class="text-right">Sisa Plan</th>
                                <th class="text-right">%</th>
                                <th class="text-right">WH Stock</th>
                                <th class="text-right">Prod Stock</th>
                                <th class="text-right">Total Stock</th>
                                <th class="text-right">Balance</th>
                                <th class="text-right">Buy Price</th>
                                <th class="text-right">Total Amount</th>
                                <th>Status</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!empty($material_data)) : ?>
                                <?php foreach ($material_data as $item) : ?>
                                    <tr>
                                        <td><?php echo remove_junk($item['part_number']); ?></td>
                                        <td><?php echo remove_junk($item['name']); ?></td>
                                        <td><?php echo remove_junk($item['uom']); ?></td>
                                        <td class="text-right"><?php echo $item['total_needed']; ?></td>
                                        <td class="text-right"><?php echo $item['total_consumed']; ?></td>
                                        <td class="text-right"><?php echo $item['sisa_plan']; ?></td>
                                        <td class="text-right <?php echo ($item['consumption_percentage'] > 100) ? 'text-danger' : ''; ?>">
                                            <?php echo $item['consumption_percentage']; ?>%
                                        </td>
                                        <td class="text-right"><?php echo $item['main_stock']; ?></td>
                                        <td class="text-right"><?php echo $item['location_stock']; ?></td>
                                        <td class="text-right"><?php echo $item['total_stock']; ?></td>
                                        <td class="text-right"><?php echo $item['balance']; ?></td>
                                        <td class="text-right"><?php echo $item['buy_price']; ?></td>
                                        <td class="text-right"><?php echo $item['total_amount']; ?></td>
                                        <td><?php echo $item['status']; ?></td>
                                    </tr>
                                <?php endforeach; ?>
                            <?php else : ?>
                                <tr>
                                    <td colspan="14" class="text-center">No forecast data available for <?php echo date('F Y', mktime(0, 0, 0, $month, 1, $year)); ?> or no results match your search.</td>
                                </tr>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
<script>
document.getElementById('export-excel').addEventListener('click', function() {
    let table = document.getElementById('material-calculation');
    let wb = XLSX.utils.table_to_book(table, { sheet: "Material Calculation" });
    XLSX.writeFile(wb, 'Material_Calculation_<?php echo $month."_".$year; ?>.xlsx');
});
</script>

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