<?php

ini_set('display_errors', 1);
error_reporting(E_ALL);

require_once('includes/load.php');
page_require_level(3);

// Get parameters
$part_number = isset($_GET['part_number']) ? $db->escape($_GET['part_number']) : '';
$date_from = isset($_GET['date_from']) ? $db->escape($_GET['date_from']) : date('Y-m-01');
$date_to = isset($_GET['date_to']) ? $db->escape($_GET['date_to']) : date('Y-m-t');

// Function to get material detail calculation
function get_material_detail_calculation($part_number, $date_from, $date_to) {
    global $db;
    
    $results = array();
    
    // 1. Get product information
    $sql_product = "SELECT p.part_number, p.name, p.UoM, p.quantity AS current_stock
                    FROM products p
                    WHERE p.part_number = '{$part_number}'";
    $product_info = $db->query($sql_product);
    
    if(!$product_info || $db->num_rows($product_info) == 0) {
        return false;
    }
    
    $product_data = $db->fetch_assoc($product_info);
    
    // 2. Get location stock
    $sql_location = "SELECT SUM(quantity) AS location_stock 
                     FROM prod_loc 
                     WHERE part_number = '{$part_number}'";
    $location_data = $db->query($sql_location);
    $location_stock = $db->fetch_assoc($location_data)['location_stock'] ?? 0;
    
    $total_stock = $product_data['current_stock'] + $location_stock;
    
    // 3. Get requirements by date
    $sql_requirements = "SELECT 
                            ps.delivery_date AS requirement_date,
                            ps.no_order,
                            ps.part_number AS parent_part,
                            p.name AS parent_name,
                            ps.quantity_order AS required_qty,
                            bl.qty AS component_qty,
                            (ps.quantity_order * bl.qty) AS total_required
                          FROM production_schedule ps
                          JOIN bom_level bl ON ps.part_number = bl.parent_bom
                          JOIN products p ON ps.part_number = p.part_number
                          WHERE bl.child_bom = '{$part_number}'
                          AND ps.delivery_date BETWEEN '{$date_from}' AND '{$date_to}'
                          ORDER BY ps.delivery_date ASC";
    
    $requirements = $db->query($sql_requirements);
    
    // 4. Calculate cumulative requirements
    $cumulative_required = 0;
    $daily_requirements = array();
    
    if($requirements && $db->num_rows($requirements) > 0) {
        while($row = $db->fetch_assoc($requirements)) {
            $cumulative_required += $row['total_required'];
            
            $daily_requirements[] = array(
                'date' => $row['requirement_date'],
                'no_order' => $row['no_order'],
                'parent_part' => $row['parent_part'],
                'parent_name' => $row['parent_name'],
                'required_qty' => $row['required_qty'],
                'component_qty' => $row['component_qty'],
                'total_required' => $row['total_required'],
                'cumulative_required' => $cumulative_required,
                'projected_stock' => $total_stock - $cumulative_required
            );
        }
    }
    
    return array(
        'product_info' => $product_data,
        'location_stock' => $location_stock,
        'total_stock' => $total_stock,
        'requirements' => $daily_requirements,
        'total_required' => $cumulative_required,
        'projected_balance' => $total_stock - $cumulative_required
    );
}

// Get calculation data if part number is provided
$calculation_data = array();
if(!empty($part_number)) {
    $calculation_data = get_material_detail_calculation($part_number, $date_from, $date_to);
}

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

<div class="row">
    <div class="col-md-12">
        <div class="panel panel-default">
            <div class="panel-heading">
                <strong>Material Detail Calculation</strong>
            </div>
            <div class="panel-body">
                <form action="material_detail_calculation.php" method="get" class="form-horizontal">
                    <div class="form-group">
                        <label for="part_number" class="col-sm-2 control-label">Part Number:</label>
                        <div class="col-sm-4">
                            <input type="text" id="part_number" name="part_number" class="form-control" 
                                   value="<?php echo remove_junk($part_number); ?>" required>
                        </div>
                    </div>
                    <div class="form-group">
                        <label for="date_from" class="col-sm-2 control-label">Date From:</label>
                        <div class="col-sm-2">
                            <input type="date" id="date_from" name="date_from" class="form-control" 
                                   value="<?php echo remove_junk($date_from); ?>">
                        </div>
                        <label for="date_to" class="col-sm-1 control-label">To:</label>
                        <div class="col-sm-2">
                            <input type="date" id="date_to" name="date_to" class="form-control" 
                                   value="<?php echo remove_junk($date_to); ?>">
                        </div>
                    </div>
                    <div class="form-group">
                        <div class="col-sm-offset-2 col-sm-4">
                            <button type="submit" class="btn btn-primary">Calculate</button>
                            <a href="material_detail_calculation.php" class="btn btn-default">Reset</a>
                        </div>
                    </div>
                </form>
            </div>
        </div>
        
        <?php if(!empty($part_number)) : ?>
            <?php if($calculation_data === false) : ?>
                <div class="alert alert-danger">
                    Part number <?php echo remove_junk($part_number); ?> not found.
                </div>
            <?php else : ?>
                <div class="panel panel-info">
                    <div class="panel-heading">
                        <strong>Material Summary</strong>
                    </div>
                    <div class="panel-body">
                        <div class="row">
                            <div class="col-md-4">
                                <table class="table table-bordered">
                                    <tr>
                                        <th width="40%">Part Number</th>
                                        <td><?php echo remove_junk($calculation_data['product_info']['part_number']); ?></td>
                                    </tr>
                                    <tr>
                                        <th>Material Name</th>
                                        <td><?php echo remove_junk($calculation_data['product_info']['name']); ?></td>
                                    </tr>
                                    <tr>
                                        <th>UoM</th>
                                        <td><?php echo remove_junk($calculation_data['product_info']['UoM']); ?></td>
                                    </tr>
                                </table>
                            </div>
                            <div class="col-md-4">
                                <table class="table table-bordered">
                                    <tr>
                                        <th width="60%">Current Stock (Main)</th>
                                        <td class="text-right"><?php echo number_format($calculation_data['product_info']['current_stock'], 2); ?></td>
                                    </tr>
                                    <tr>
                                        <th>Location Stock</th>
                                        <td class="text-right"><?php echo number_format($calculation_data['location_stock'], 2); ?></td>
                                    </tr>
                                    <tr>
                                        <th>Total Available</th>
                                        <td class="text-right"><strong><?php echo number_format($calculation_data['total_stock'], 2); ?></strong></td>
                                    </tr>
                                </table>
                            </div>
                            <div class="col-md-4">
                                <table class="table table-bordered">
                                    <tr>
                                        <th width="60%">Total Requirements</th>
                                        <td class="text-right"><?php echo number_format($calculation_data['total_required'], 2); ?></td>
                                    </tr>
                                    <tr>
                                        <th>Projected Balance</th>
                                        <td class="text-right <?php echo ($calculation_data['projected_balance'] < 0) ? 'text-danger' : 'text-success'; ?>">
                                            <strong><?php echo number_format($calculation_data['projected_balance'], 2); ?></strong>
                                        </td>
                                    </tr>
                                </table>
                            </div>
                        </div>
                    </div>
                </div>
                
                <div class="panel panel-info">
                    <div class="panel-heading">
                        <strong>Detailed Requirements</strong>
                    </div>
                    <div class="panel-body">
                        <?php if(!empty($calculation_data['requirements'])) : ?>
                            <div class="text-right">
                                <button id="export-excel" class="btn btn-success mb-3">
                                    <i class="glyphicon glyphicon-export"></i> Export to Excel
                                </button>
                            </div>
                        <?php endif; ?>
                        
                        <table id="requirements-table" class="table table-bordered table-striped">
                            <thead>
                                <tr>
                                    <th>Date</th>
                                    <th>Order No</th>
                                    <th>Parent Part</th>
                                    <th>Parent Name</th>
                                    <th class="text-right">Parent Qty</th>
                                    <th class="text-right">Component Qty</th>
                                    <th class="text-right">Total Required</th>
                                    <th class="text-right">Cumulative Required</th>
                                    <th class="text-right">Projected Stock</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php if(!empty($calculation_data['requirements'])) : ?>
                                    <?php foreach($calculation_data['requirements'] as $requirement) : ?>
                                        <tr>
                                            <td><?php echo $requirement['date']; ?></td>
                                            <td><?php echo $requirement['no_order']; ?></td>
                                            <td><?php echo $requirement['parent_part']; ?></td>
                                            <td><?php echo $requirement['parent_name']; ?></td>
                                            <td class="text-right"><?php echo number_format($requirement['required_qty'], 2); ?></td>
                                            <td class="text-right"><?php echo number_format($requirement['component_qty'], 4); ?></td>
                                            <td class="text-right"><?php echo number_format($requirement['total_required'], 2); ?></td>
                                            <td class="text-right"><?php echo number_format($requirement['cumulative_required'], 2); ?></td>
                                            <td class="text-right <?php echo ($requirement['projected_stock'] < 0) ? 'text-danger' : ''; ?>">
                                                <?php echo number_format($requirement['projected_stock'], 2); ?>
                                            </td>
                                        </tr>
                                    <?php endforeach; ?>
                                <?php else : ?>
                                    <tr>
                                        <td colspan="9" class="text-center">No requirements found for this period.</td>
                                    </tr>
                                <?php endif; ?>
                            </tbody>
                        </table>
                    </div>
                </div>
            <?php endif; ?>
        <?php endif; ?>
    </div>
</div>

<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
<script>
document.addEventListener('DOMContentLoaded', function() {
    // Add click event to export button
    document.getElementById('export-excel')?.addEventListener('click', function() {
        let table = document.getElementById('requirements-table');
        let wb = XLSX.utils.table_to_book(table, { sheet: "Requirements" });
        XLSX.writeFile(wb, 'Material_Details_<?php echo $part_number; ?>_<?php echo date("Ymd"); ?>.xlsx');
    });
});
</script>

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