<?php
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');
// Get search query parameter
$search_query = isset($_GET['search']) ? $db->escape($_GET['search']) : '';

// Define the category ID to exclude
define('EXCLUDED_CATEGORY_ID', 7);

// Function to get projected end of month stock data (for table and calculations)
function get_projected_stock_end_month($month, $year, $search_query = '') {
    global $db;
    $results = array();

    // Base SQL to fetch all products, excluding categorie_id 7
    $sql_products = "SELECT 
                        p.id,
                        p.part_number,
                        p.new_code,
                        p.name,
                        p.quantity AS main_stock,
                        p.UoM AS uom,
                        p.buy_price
                     FROM products p
                     WHERE p.categorie_id != " . EXCLUDED_CATEGORY_ID; // Exclude category ID 7

    // Add search filter if present
    if (!empty($search_query)) {
        $sql_products .= " AND (p.part_number LIKE '%{$search_query}%' OR p.new_code LIKE '%{$search_query}%' OR p.name LIKE '%{$search_query}%')";
    }

    $sql_products .= " ORDER BY p.part_number ASC";

    $all_products = $db->query($sql_products);

    if ($all_products && $db->num_rows($all_products) > 0) {
        while ($product = $db->fetch_assoc($all_products)) {
            $part_number = $product['part_number'];
            $current_main_stock = (float)$product['main_stock'];
            $buy_price = (float)$product['buy_price'];

            // Get total stock from all locations (main + prod_loc)
            $sql_location_stock = "SELECT SUM(pl.quantity) AS location_stock FROM prod_loc pl WHERE pl.product_id = '{$product['id']}'";
            $location_data = $db->query($sql_location_stock);
            $location_stock = ($location_data && $db->num_rows($location_data) > 0) ? (float)$db->fetch_assoc($location_data)['location_stock'] : 0;
            $total_current_stock = $current_main_stock + $location_stock;

            // Get total quantity needed from material_forecasts for the selected month and year
            $sql_needed = "SELECT SUM(quantity_needed) AS total_needed
                           FROM material_forecasts
                           WHERE component = '{$part_number}'
                           AND forecast_month = '{$month}'
                           AND forecast_year = '{$year}'";
            $needed_data = $db->query($sql_needed);
            $total_needed = ($needed_data && $db->num_rows($needed_data) > 0) ? (float)$db->fetch_assoc($needed_data)['total_needed'] : 0;

            // Get total consumption for the selected month and year for this 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);
            $total_consumed = ($consumption_data && $db->num_rows($consumption_data) > 0) ? (float)$db->fetch_assoc($consumption_data)['total_consumed'] : 0;

            // Calculate remaining plan (needed - consumed)
            $remaining_plan = max(0, $total_needed - $total_consumed);

            // Projected stock at end of month: (Total Current Stock - Remaining Plan)
            $projected_eom_stock = $total_current_stock - $remaining_plan;
            
            // Calculate total value of projected stock
            $total_value = $projected_eom_stock * $buy_price;

            // Determine status
            $status = ($projected_eom_stock >= 0) ? '<span class="label label-success">Sufficient</span>' : '<span class="label label-danger">Shortage</span>';

            $results[] = array(
                'part_number' => $product['part_number'],
                'new_code' => $product['new_code'],
                'name' => $product['name'],
                'uom' => $product['uom'],
                'main_stock' => number_format($current_main_stock, 2),
                'location_stock' => number_format($location_stock, 2),
                'total_current_stock' => number_format($total_current_stock, 2),
                'total_needed' => number_format($total_needed, 2),
                'total_consumed' => number_format($total_consumed, 2),
                'remaining_plan' => number_format($remaining_plan, 2),
                'projected_eom_stock_raw' => $projected_eom_stock, // Raw for calculations
                'projected_eom_stock' => ($projected_eom_stock >= 0) ? 
                                         '<span class="text-success">+'.number_format($projected_eom_stock, 2).'</span>' : 
                                         '<span class="text-danger">'.number_format($projected_eom_stock, 2).'</span>',
                'buy_price_raw' => $buy_price, // Keep raw buy_price for calculation
                'buy_price' => number_format($buy_price, 2),
                'total_value' => number_format($total_value, 2),
                'status' => $status
            );
        }
    }
    return $results;
}

// Function to calculate total current inventory amount, now filtered by search query
function get_total_current_inventory_amount($search_query = '') {
    global $db;
    $total_inventory_value = 0;

    $sql = "SELECT 
                p.id,
                p.quantity AS main_stock,
                p.buy_price
            FROM products p
            WHERE p.categorie_id != " . EXCLUDED_CATEGORY_ID; // Exclude category ID 7

    if (!empty($search_query)) {
        $sql .= " AND (p.part_number LIKE '%{$search_query}%' OR p.new_code LIKE '%{$search_query}%' OR p.name LIKE '%{$search_query}%')";
    }

    $result = $db->query($sql);

    if ($result && $db->num_rows($result) > 0) {
        while ($product = $db->fetch_assoc($result)) {
            $product_id = (int)$product['id'];
            $main_stock = (float)$product['main_stock'];
            $buy_price = (float)$product['buy_price'];

            // Get stock from prod_loc for this product
            $sql_prod_loc = "SELECT SUM(quantity) AS location_stock FROM prod_loc WHERE product_id = '{$product_id}'";
            $prod_loc_result = $db->query($sql_prod_loc);
            $location_stock = ($prod_loc_result && $db->num_rows($prod_loc_result) > 0) ? (float)$db->fetch_assoc($prod_loc_result)['location_stock'] : 0;

            $total_stock_for_product = $main_stock + $location_stock;
            $total_inventory_value += ($total_stock_for_product * $buy_price);
        }
    }
    return $total_inventory_value;
}

// Function to calculate total projected end of month inventory amount, now filtered by search query
function get_total_projected_eom_inventory_amount($month, $year, $search_query = '') {
    global $db;
    $total_projected_eom_value = 0;

    $sql_products_for_eom = "SELECT 
                                p.id,
                                p.part_number,
                                p.quantity AS main_stock,
                                p.buy_price
                             FROM products p
                             WHERE p.categorie_id != " . EXCLUDED_CATEGORY_ID; // Exclude category ID 7
    
    if (!empty($search_query)) {
        $sql_products_for_eom .= " AND (p.part_number LIKE '%{$search_query}%' OR p.new_code LIKE '%{$search_query}%' OR p.name LIKE '%{$search_query}%')";
    }

    $all_products_eom = $db->query($sql_products_for_eom);

    if ($all_products_eom && $db->num_rows($all_products_eom) > 0) {
        while ($product = $db->fetch_assoc($all_products_eom)) {
            $part_number = $product['part_number'];
            $current_main_stock = (float)$product['main_stock'];
            $buy_price = (float)$product['buy_price'];

            $sql_location_stock = "SELECT SUM(pl.quantity) AS location_stock FROM prod_loc pl WHERE pl.product_id = '{$product['id']}'";
            $location_data = $db->query($sql_location_stock);
            $location_stock = ($location_data && $db->num_rows($location_data) > 0) ? (float)$db->fetch_assoc($location_data)['location_stock'] : 0;
            $total_current_stock = $current_main_stock + $location_stock;

            $sql_needed = "SELECT SUM(quantity_needed) AS total_needed
                           FROM material_forecasts
                           WHERE component = '{$part_number}'
                           AND forecast_month = '{$month}'
                           AND forecast_year = '{$year}'";
            $needed_data = $db->query($sql_needed);
            $total_needed = ($needed_data && $db->num_rows($needed_data) > 0) ? (float)$db->fetch_assoc($needed_data)['total_needed'] : 0;

            $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);
            $total_consumed = ($consumption_data && $db->num_rows($consumption_data) > 0) ? (float)$db->fetch_assoc($consumption_data)['total_consumed'] : 0;

            $remaining_plan = max(0, $total_needed - $total_consumed);
            $projected_eom_stock = $total_current_stock - $remaining_plan;
            
            $total_projected_eom_value += ($projected_eom_stock * $buy_price);
        }
    }
    return $total_projected_eom_value;
}

// Function to calculate total value for projected shortages, now filtered by search query
function get_total_value_shortage($month, $year, $search_query = '') {
    global $db;
    $total_value_shortage = 0;

    $sql_forecast = "SELECT 
                        mf.component AS part_number,
                        p.id,
                        p.quantity AS main_stock,
                        IFNULL((SELECT SUM(pl.quantity) FROM prod_loc pl WHERE pl.product_id = p.id), 0) AS location_stock,
                        SUM(mf.quantity_needed) AS total_needed,
                        p.buy_price
                    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.categorie_id != " . EXCLUDED_CATEGORY_ID;
    
    if (!empty($search_query)) {
        $sql_forecast .= " AND (p.part_number LIKE '%{$search_query}%' OR p.new_code LIKE '%{$search_query}%' OR p.name LIKE '%{$search_query}%')";
    }

    $sql_forecast .= " GROUP BY p.id, p.part_number, 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'];
            $total_needed = (float)$row['total_needed'];
            $main_stock = (float)$row['main_stock'];
            $location_stock = (float)$row['location_stock'];
            $total_current_stock = $main_stock + $location_stock;
            $buy_price = (float)$row['buy_price'];

            $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);
            $total_consumed = ($consumption_data && $db->num_rows($consumption_data) > 0) ? (float)$db->fetch_assoc($consumption_data)['total_consumed'] : 0;

            $remaining_plan = max(0, $total_needed - $total_consumed);
            $projected_eom_stock = $total_current_stock - $remaining_plan;

            if ($projected_eom_stock < 0) {
                $total_value_shortage += abs($projected_eom_stock) * $buy_price;
            }
        }
    }
    return $total_value_shortage;
}


$projected_stock_data = get_projected_stock_end_month($month, $year, $search_query); // Table data is filtered by search query
$total_current_inventory_amount = get_total_current_inventory_amount($search_query); // Dashboard totals now filtered
$total_projected_eom_inventory_amount = get_total_projected_eom_inventory_amount($month, $year, $search_query); // Dashboard totals now filtered
$total_value_shortage_dashboard = get_total_value_shortage($month, $year, $search_query); // Dashboard totals now filtered


// Data for Chart.js
$chart_labels = json_encode(['Current Inventory', 'Projected EOM Inventory', 'Projected Shortage Cost']);
$chart_data = json_encode([
    round($total_current_inventory_amount),
    round($total_projected_eom_inventory_amount),
    round($total_value_shortage_dashboard)
]);


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

<style>
/* Styling for fixed header table */
.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;
}

/* General table styling */
.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-danger {
    background-color: #d9534f;
    color: white;
}

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

.text-danger {
    color: #d9534f;
}

/* Dashboard styles */
.dashboard-card {
    font-size: 1.2em;
    font-weight: bold;
    padding: 15px;
    border-radius: 4px;
    text-align: center;
    margin-bottom: 15px; /* Add some space between cards */
    height: 100%; /* Ensure cards in a row have equal height */
    display: flex;
    flex-direction: column;
    justify-content: center;
}

.dashboard-card.inventory-value {
    background-color: #d9edf7; /* Info blue */
    border: 1px solid #bce8f1;
    color: #31708f;
}

.dashboard-card.projected-eom-value {
    background-color: #d1ecf1; /* Light blue/cyan for projection */
    border: 1px solid #bee5eb;
    color: #0c5460;
}

.dashboard-card.shortage-cost {
    background-color: #f2dede; /* Danger red */
    border: 1px solid #ebccd1;
    color: #a94442;
}

.dashboard-card.shortage-none {
    background-color: #dff0d8; /* Success green */
    border-color: #d6e9c6;
    color: #3c763d;
}

.chart-container {
    width: 100%; /* Make chart container full width */
    height: 250px; /* Set a fixed height for the chart */
    margin-bottom: 20px;
    background-color: #fff;
    border: 1px solid #ddd;
    border-radius: 4px;
    padding: 15px;
    box-sizing: border-box; /* Include padding in height/width */
}
</style>

<div class="row">
    <div class="col-md-12">
        <form action="projection_stock_end_month.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, New Code, Name" value="<?php echo htmlspecialchars($search_query); ?>">
            </div>
            <button type="submit" class="btn btn-primary">Filter</button>
        </form>
        
        <div class="panel panel-info mt-3">
            <div class="panel-heading">
                <strong>Projected End of Month Stock - <?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>
                </div>

                <div class="row mb-3">
                    <div class="col-md-4">
                        <div class="dashboard-card inventory-value">
                            Total Current Inventory Value: <br> **Rp <?php echo number_format($total_current_inventory_amount, 2); ?>**
                        </div>
                    </div>
                    <div class="col-md-4">
                        <div class="dashboard-card projected-eom-value">
                            Projected EOM Inventory Value: <br> **Rp <?php echo number_format($total_projected_eom_inventory_amount, 2); ?>**
                        </div>
                    </div>
                    <div class="col-md-4">
                        <?php if ($total_value_shortage_dashboard > 0) : ?>
                            <div class="dashboard-card shortage-cost">
                                Total Estimated Cost for Projected Shortages: <br> **Rp <?php echo number_format($total_value_shortage_dashboard, 2); ?>**
                            </div>
                        <?php else : ?>
                            <div class="dashboard-card shortage-none">
                                No Projected Shortages for This Month!
                            </div>
                        <?php endif; ?>
                    </div>
                </div>
                <div class="row">
                    <div class="col-md-12">
                        <div class="chart-container">
                            <canvas id="inventoryOverviewChart"></canvas>
                        </div>
                    </div>
                </div>
                <div class="scrollable-table">
                    <table id="projected-stock-table" class="table table-bordered table-striped">
                        <thead>
                            <tr>
                                <th>Part Number</th>
                                <th>New Code</th>
                                <th>Material Name</th>
                                <th>UOM</th>
                                <th class="text-right">WH Stock</th>
                                <th class="text-right">Prod Stock</th>
                                <th class="text-right">Total Current Stock</th>
                                <th class="text-right">Total Plan Need (This Month)</th>
                                <th class="text-right">Total Consumed (This Month)</th>
                                <th class="text-right">Remaining Plan</th>
                                <th class="text-right">Projected EOM Stock</th>
                                <th class="text-right">Buy Price</th>
                                <th class="text-right">Total Value (Projected EOM Stock * Buy Price)</th>
                                <th>Status</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!empty($projected_stock_data)) : ?>
                                <?php foreach ($projected_stock_data as $item) : ?>
                                    <tr>
                                        <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['main_stock']; ?></td>
                                        <td class="text-right"><?php echo $item['location_stock']; ?></td>
                                        <td class="text-right"><?php echo $item['total_current_stock']; ?></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['remaining_plan']; ?></td>
                                        <td class="text-right"><?php echo $item['projected_eom_stock']; ?></td>
                                        <td class="text-right"><?php echo $item['buy_price']; ?></td>
                                        <td class="text-right"><?php echo $item['total_value']; ?></td>
                                        <td><?php echo $item['status']; ?></td>
                                    </tr>
                                <?php endforeach; ?>
                            <?php else : ?>
                                <tr>
                                    <td colspan="14" class="text-center">No product data available 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 src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
document.getElementById('export-excel').addEventListener('click', function() {
    let table = document.getElementById('projected-stock-table');
    let wb = XLSX.utils.table_to_book(table, { sheet: "Projected EOM Stock" });
    XLSX.writeFile(wb, 'Projected_EOM_Stock_<?php echo $month."_".$year; ?>.xlsx');
});

// Chart.js initialization
const ctx = document.getElementById('inventoryOverviewChart').getContext('2d');
const inventoryOverviewChart = new Chart(ctx, {
    type: 'bar', // You can change this to 'pie', 'doughnut', or 'line'
    data: {
        labels: <?php echo $chart_labels; ?>,
        datasets: [{
            label: 'Amount (Rp)',
            data: <?php echo $chart_data; ?>,
            backgroundColor: [
                'rgba(54, 162, 235, 0.7)', // Blue for Current Inventory
                'rgba(75, 192, 192, 0.7)', // Green for Projected EOM Inventory
                'rgba(255, 99, 132, 0.7)'  // Red for Projected Shortage Cost
            ],
            borderColor: [
                'rgba(54, 162, 235, 1)',
                'rgba(75, 192, 192, 1)',
                'rgba(255, 99, 132, 1)'
            ],
            borderWidth: 1
        }]
    },
    options: {
        responsive: true,
        maintainAspectRatio: false, // Allow chart to fill container
        plugins: {
            legend: {
                display: false // Hide dataset legend as labels are self-explanatory
            },
            title: {
                display: true,
                text: 'Inventory Value Overview',
                font: {
                    size: 16
                }
            },
            tooltip: {
                callbacks: {
                    label: function(context) {
                        let label = context.dataset.label || '';
                        if (label) {
                            label += ': ';
                        }
                        // Format the number as currency (Rp)
                        return label + 'Rp ' + context.raw.toLocaleString('id-ID');
                    }
                }
            }
        },
        scales: {
            y: {
                beginAtZero: true,
                title: {
                    display: true,
                    text: 'Amount (Rp)'
                },
                ticks: {
                    // Add currency formatting to y-axis labels
                    callback: function(value, index, values) {
                        return 'Rp ' + value.toLocaleString('id-ID');
                    }
                }
            }
        }
    }
});
</script>

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