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

// Load PhpSpreadsheet library
require_once 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

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

// Function to calculate material requirements
function calculate_consumption($month, $year) {
    global $db;
    
    $consumption_data = [];
    
    $productions = $db->query("
        SELECT po.id, po.part_number, po.quantity_produced, po.production_date, p.name AS product_name
        FROM production_output po
        JOIN products p ON po.part_number = p.part_number
        WHERE MONTH(po.production_date) = '{$month}' 
        AND YEAR(po.production_date) = '{$year}'
        ORDER BY po.production_date
    ");
    
    if ($db->num_rows($productions) > 0) {
        while ($production = $db->fetch_assoc($productions)) {
            $components = $db->query("
                SELECT bl.child_bom, bl.qty, p.name, p.uom
                FROM bom_level bl
                JOIN products p ON bl.child_bom = p.part_number
                WHERE bl.parent_bom = '{$production['part_number']}'
            ");
            
            if ($db->num_rows($components) > 0) {
                while ($component = $db->fetch_assoc($components)) {
                    $consumption_data[] = [
                        'production_id' => $production['id'],
                        'production_date' => $production['production_date'],
                        'product_part' => $production['part_number'],
                        'product_name' => $production['product_name'],
                        'component_part' => $component['child_bom'],
                        'component_name' => $component['name'],
                        'uom' => $component['uom'],
                        'quantity_needed' => $component['qty'] * $production['quantity_produced'],
                        'quantity_produced' => $production['quantity_produced']
                    ];
                }
            }
        }
    }
    
    return $consumption_data;
}

// Function to save consumption records (with update behavior)
function save_consumption_records($month, $year, $data) {
    global $db;
    
    // 1. First delete existing records for this period
    $db->query("DELETE FROM material_consumption_records 
               WHERE MONTH(consumption_date) = '{$month}' 
               AND YEAR(consumption_date) = '{$year}'");
    
    // 2. Insert new records
    $saved = 0;
    $user_id = current_user()['id'];
    
    foreach ($data as $record) {
        $sql = "INSERT INTO material_consumption_records (
                  production_id, product_part, component_part, component_name,
                  uom, quantity_used, consumption_date, recorded_by
                ) VALUES (
                  '{$record['production_id']}',
                  '{$db->escape($record['product_part'])}',
                  '{$db->escape($record['component_part'])}',
                  '{$db->escape($record['component_name'])}',
                  '{$db->escape($record['uom'])}',
                  '{$record['quantity_needed']}',
                  '{$record['production_date']}',
                  '{$user_id}'
                )";
        
        if ($db->query($sql)) {
            $saved++;
        }
    }
    
    return $saved;
}

// Process Excel export
if (isset($_GET['export_excel'])) {
    $month = intval($_GET['month']);
    $year = intval($_GET['year']);
    
    $consumptions = calculate_consumption($month, $year);
    
    if (!empty($consumptions)) {
        // Create new Spreadsheet object
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        
        // Set document properties
        $spreadsheet->getProperties()
            ->setCreator(current_user()['name'])
            ->setTitle("Material Consumption Report - ".date('F Y', mktime(0,0,0,$month,1,$year)));
        
        // Set headers
        $sheet->setCellValue('A1', 'Production Date');
        $sheet->setCellValue('B1', 'Product Part');
        $sheet->setCellValue('C1', 'Product Name');
        $sheet->setCellValue('D1', 'Component Part');
        $sheet->setCellValue('E1', 'Component Name');
        $sheet->setCellValue('F1', 'UOM');
        $sheet->setCellValue('G1', 'Qty Produced');
        $sheet->setCellValue('H1', 'Qty Needed');
        
        // Add data
        $row = 2;
        foreach ($consumptions as $c) {
            $sheet->setCellValue('A'.$row, $c['production_date']);
            $sheet->setCellValue('B'.$row, $c['product_part']);
            $sheet->setCellValue('C'.$row, $c['product_name']);
            $sheet->setCellValue('D'.$row, $c['component_part']);
            $sheet->setCellValue('E'.$row, $c['component_name']);
            $sheet->setCellValue('F'.$row, $c['uom']);
            $sheet->setCellValue('G'.$row, $c['quantity_produced']);
            $sheet->setCellValue('H'.$row, $c['quantity_needed']);
            $row++;
        }
        
        // Auto size columns
        foreach (range('A', 'H') as $col) {
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }
        
        // Set headers for download
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="Material_Consumption_'.date('F_Y', mktime(0,0,0,$month,1,$year)).'.xlsx"');
        header('Cache-Control: max-age=0');
        
        // Create Excel file and send to browser
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
        exit;
    } else {
        $session->msg('d', "No data to export");
        redirect("consume_output.php?month={$month}&year={$year}", false);
    }
}

// Process form submission
if (isset($_POST['save_consumption'])) {
    $month = intval($_POST['month']);
    $year = intval($_POST['year']);
    
    $consumptions = calculate_consumption($month, $year);
    
    if (!empty($consumptions)) {
        $saved = save_consumption_records($month, $year, $consumptions);
        
        if ($saved > 0) {
            $session->msg('s', "Successfully updated {$saved} consumption records for ".date('F Y', mktime(0,0,0,$month,1,$year)));
        } else {
            $session->msg('d', "Failed to update consumption records");
        }
    } else {
        $session->msg('d', "No consumption data to save");
    }
    
    redirect("consume_output.php?month={$month}&year={$year}", false);
}

// Calculate consumption data for display
$consumptions = calculate_consumption($month, $year);

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

<div class="row">
    <div class="col-md-12">
        <div class="panel panel-default">
            <div class="panel-heading">
                <strong>Material Consumption Calculation - <?= date('F Y', mktime(0,0,0,$month,1,$year)) ?></strong>
            </div>
            <div class="panel-body">
                <form 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="<?= $m ?>" <?= ($month == $m) ? 'selected' : '' ?>>
                                    <?= 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'); $y >= date('Y')-5; $y--): ?>
                                <option value="<?= $y ?>" <?= ($year == $y) ? 'selected' : '' ?>>
                                    <?= $y ?>
                                </option>
                            <?php endfor; ?>
                        </select>
                    </div>
                    <button type="submit" class="btn btn-primary">Calculate</button>
                    
                    <?php if (!empty($consumptions)): ?>
                    <a href="?export_excel=1&month=<?= $month ?>&year=<?= $year ?>" class="btn btn-success ml-2">
                        <i class="glyphicon glyphicon-download"></i> Export to Excel
                    </a>
                    <?php endif; ?>
                </form>
                
                <?php if (!empty($consumptions)): ?>
                <form method="post" class="mt-3">
                    <input type="hidden" name="month" value="<?= $month ?>">
                    <input type="hidden" name="year" value="<?= $year ?>">
                    <button type="submit" name="save_consumption" class="btn btn-success">
                        <i class="glyphicon glyphicon-floppy-disk"></i> Update Consumption Records
                    </button>
                    <small class="text-muted ml-2">Note: This will replace existing records for this period</small>
                    
                    <table class="table table-bordered table-striped mt-3">
                        <thead>
                            <tr>
                                <th>Production Date</th>
                                <th>Product</th>
                                <th>Component</th>
                                <th>Component Name</th>
                                <th>UOM</th>
                                <th>Qty Produced</th>
                                <th>Qty Needed</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($consumptions as $c): ?>
                            <tr>
                                <td><?= $c['production_date'] ?></td>
                                <td><?= $c['product_part'] ?> - <?= $c['product_name'] ?></td>
                                <td><?= $c['component_part'] ?></td>
                                <td><?= $c['component_name'] ?></td>
                                <td><?= $c['uom'] ?></td>
                                <td class="text-right"><?= number_format($c['quantity_produced'], 2) ?></td>
                                <td class="text-right"><?= number_format($c['quantity_needed'], 4) ?></td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </form>
                <?php else: ?>
                <div class="alert alert-info mt-3">
                    No production data found for <?= date('F Y', mktime(0,0,0,$month,1,$year)) ?>.
                </div>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>

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