<?php
$page_title = 'Production Output';
require_once('includes/load.php');
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

page_require_level(3); // Only level 3 access is allowed

// Start output buffering
ob_start();

include_once('layouts/header.php');

// Handle deletion of production output
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    if (isset($_POST['delete_id'])) {
        $delete_id = (int)$_POST['delete_id'];
        
        $query = "DELETE FROM production_output WHERE id='{$delete_id}'";
        
        if ($db->query($query)) {
            $session->msg('s', "Production output deleted successfully.");
            redirect('production_output.php', false);
        } else {
            $session->msg('d', "Failed to delete production output.");
            redirect('production_output.php', false);
        }
    }

    // Handle form submission to add production output
    if (isset($_POST['add_output'])) {
        $errors = array();

        $required_fields = array('no_order', 'part_number', 'quantity_produced', 'production_date', 'categorie_id');
        foreach ($required_fields as $field) {
            if (empty($_POST[$field])) {
                $errors[] = ucfirst(str_replace('_', ' ', $field)) . ' is required.';
            }
        }

        if (empty($errors)) {
            $no_order = $db->escape($_POST['no_order']);
            $part_number = $db->escape($_POST['part_number']);
            $categorie_id = (int)$_POST['categorie_id'];
            $quantity_produced = (int)$_POST['quantity_produced'];
            $production_date = $db->escape($_POST['production_date']);
            $input_date = date('Y-m-d');

            $query = "INSERT INTO production_output (no_order, part_number, categorie_id, quantity_produced, production_date, input_date) ";
            $query .= "VALUES ('{$no_order}', '{$part_number}', '{$categorie_id}', '{$quantity_produced}', '{$production_date}', '{$input_date}')";

            if ($db->query($query)) {
                $session->msg('s', "Production output added successfully.");
                redirect('production_output.php', false);
            } else {
                $session->msg('d', "Failed to add production output.");
                redirect('production_output.php', false);
            }
        } else {
            $session->msg('d', $errors);
            redirect('production_output.php', false);
        }
    }

    // Handle form submission to edit production output
    if (isset($_POST['edit_output'])) {
        $errors = array();

        $required_fields = array('edit_id', 'edit_no_order', 'edit_part_number', 'edit_quantity_produced', 'edit_production_date', 'edit_categorie_id');
        foreach ($required_fields as $field) {
            if (empty($_POST[$field])) {
                $errors[] = ucfirst(str_replace('_', ' ', $field)) . ' is required.';
            }
        }

        if (empty($errors)) {
            $edit_id = (int)$_POST['edit_id'];
            $no_order = $db->escape($_POST['edit_no_order']);
            $part_number = $db->escape($_POST['edit_part_number']);
            $categorie_id = (int)$_POST['edit_categorie_id'];
            $quantity_produced = (int)$_POST['edit_quantity_produced'];
            $production_date = $db->escape($_POST['edit_production_date']);

            $query = "UPDATE production_output SET 
                      no_order='{$no_order}', 
                      part_number='{$part_number}', 
                      categorie_id='{$categorie_id}', 
                      quantity_produced='{$quantity_produced}', 
                      production_date='{$production_date}' 
                      WHERE id='{$edit_id}'";

            if ($db->query($query)) {
                $session->msg('s', "Production output updated successfully.");
                redirect('production_output.php', false);
            } else {
                $session->msg('d', "Failed to update production output.");
                redirect('production_output.php', false);
            }
        } else {
            $session->msg('d', $errors);
            redirect('production_output.php', false);
        }
    }
}

// Handle Excel export
if (isset($_GET['export'])) {
    $sql_production_outputs = "
        SELECT po.*, p.name, c.name as category_name
        FROM production_output po
        LEFT JOIN products p ON po.part_number = p.part_number
        LEFT JOIN categories c ON po.categorie_id = c.id
        ORDER BY po.production_date DESC";
    
    $result_production_outputs = $db->query($sql_production_outputs);
    
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Production Outputs');

    // Set header
    $sheet->setCellValue('A1', 'ID');
    $sheet->setCellValue('B1', 'No Order');
    $sheet->setCellValue('C1', 'Part Number');
    $sheet->setCellValue('D1', 'Name');
    $sheet->setCellValue('E1', 'Category');
    $sheet->setCellValue('F1', 'Quantity Produced');
    $sheet->setCellValue('G1', 'Production Date');
    $sheet->setCellValue('H1', 'Input Date');

    // Populate data
    $row = 2;
    while ($production_output = $result_production_outputs->fetch_assoc()) {
        $sheet->setCellValue('A' . $row, $production_output['id']);
        $sheet->setCellValue('B' . $row, $production_output['no_order']);
        $sheet->setCellValue('C' . $row, $production_output['part_number']);
        $sheet->setCellValue('D' . $row, $production_output['name']);
        $sheet->setCellValue('E' . $row, $production_output['category_name']);
        $sheet->setCellValue('F' . $row, $production_output['quantity_produced']);
        
        $productionDate = \PhpOffice\PhpSpreadsheet\Shared\Date::stringToExcel($production_output['production_date']);
        $inputDate = \PhpOffice\PhpSpreadsheet\Shared\Date::stringToExcel($production_output['input_date']);
        
        $sheet->setCellValue('G' . $row, $productionDate);
        $sheet->setCellValue('H' . $row, $inputDate);
        
        $row++;
    }

    // Set date format
    $sheet->getStyle('G2:G' . ($row - 1))->getNumberFormat()->setFormatCode('yyyy-mm-dd');
    $sheet->getStyle('H2:H' . ($row - 1))->getNumberFormat()->setFormatCode('yyyy-mm-dd');

    // Set headers for download
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment; filename="production_output.xlsx"');
    header('Cache-Control: max-age=0');
    header('Cache-Control: max-age=1');
    header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
    header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
    header('Pragma: public');

    ob_end_clean();
    $writer = new Xlsx($spreadsheet);
    $writer->save('php://output');
    exit();
}

// Handle filter inputs
$filter_part_number = isset($_GET['filter_part_number']) ? $db->escape($_GET['filter_part_number']) : '';
$filter_categorie_id = isset($_GET['filter_categorie_id']) ? (int)$_GET['filter_categorie_id'] : 0;
$filter_start_date = isset($_GET['filter_start_date']) ? $db->escape($_GET['filter_start_date']) : date('Y-m-01');
$filter_end_date = isset($_GET['filter_end_date']) ? $db->escape($_GET['filter_end_date']) : date('Y-m-d');

// Get all categories for the filter dropdown
$categories = find_all('categories');

// Build the query
$sql_production_outputs = "
    SELECT po.*, p.name, c.name as category_name
    FROM production_output po
    LEFT JOIN products p ON po.part_number = p.part_number
    LEFT JOIN categories c ON po.categorie_id = c.id
    WHERE 1=1";

if ($filter_part_number != '') {
    $sql_production_outputs .= " AND po.part_number LIKE '%$filter_part_number%'";
}
if ($filter_categorie_id > 0) {
    $sql_production_outputs .= " AND po.categorie_id = $filter_categorie_id";
}
if ($filter_start_date != '' && $filter_end_date != '') {
    $sql_production_outputs .= " AND po.production_date BETWEEN '$filter_start_date' AND '$filter_end_date'";
} elseif ($filter_start_date != '') {
    $sql_production_outputs .= " AND po.production_date >= '$filter_start_date'";
} elseif ($filter_end_date != '') {
    $sql_production_outputs .= " AND po.production_date <= '$filter_end_date'";
}

$sql_production_outputs .= " ORDER BY po.production_date DESC";
$result_production_outputs = $db->query($sql_production_outputs);
$production_outputs = [];
$total_quantity_produced = 0;
while ($row_production_output = $result_production_outputs->fetch_assoc()) {
    $production_outputs[] = $row_production_output;
    $total_quantity_produced += $row_production_output['quantity_produced'];
}


$chart_sql = "SELECT 
                po.production_date,
                SUM(CASE WHEN c.name = 'Finish Goods' THEN po.quantity_produced ELSE 0 END) as finish_product,
                SUM(CASE WHEN c.name = 'WIP' THEN po.quantity_produced ELSE 0 END) as wip
              FROM production_output po
              LEFT JOIN categories c ON po.categorie_id = c.id
              WHERE po.production_date BETWEEN '$filter_start_date' AND '$filter_end_date'
              GROUP BY po.production_date
              ORDER BY po.production_date";

$chart_result = $db->query($chart_sql);
$chart_data = array();
while ($row = $chart_result->fetch_assoc()) {
    $chart_data[] = $row;
}

// Prepare data for Chart.js
$labels = array();
$finish_product_data = array();
$wip_data = array();

foreach ($chart_data as $row) {
    $labels[] = $row['production_date'];
    $finish_product_data[] = (int)$row['finish_product'];
    $wip_data[] = (int)$row['wip'];
    
}
?>

<div class="col-md-12">
    <div class="panel panel-default">
        <div class="panel-heading">
            <strong>
                <span class="glyphicon glyphicon-stats"></span>
                <span>Daily Production Output by Category</span>
            </strong>
        </div>
        <div class="panel-body">
            <canvas id="productionChart" height="100"></canvas>
        </div>
    </div>
</div>

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
document.addEventListener('DOMContentLoaded', function() {
    const ctx = document.getElementById('productionChart').getContext('2d');
    const productionChart = new Chart(ctx, {
        type: 'bar',
        data: {
            labels: <?php echo json_encode($labels); ?>,
            datasets: [
                {
                    label: 'Finish Product',
                    data: <?php echo json_encode($finish_product_data); ?>,
                    backgroundColor: 'rgba(54, 162, 235, 0.7)',
                    borderColor: 'rgba(54, 162, 235, 1)',
                    borderWidth: 1
                },
                {
                    label: 'WIP',
                    data: <?php echo json_encode($wip_data); ?>,
                    backgroundColor: 'rgba(255, 99, 132, 0.7)',
                    borderColor: 'rgba(255, 99, 132, 1)',
                    borderWidth: 1
                }
            ]
        },
        options: {
            responsive: true,
            scales: {
                x: {
                    stacked: false,
                    title: {
                        display: true,
                        text: 'Production Date'
                    }
                },
                y: {
                    stacked: false,
                    beginAtZero: true,
                    title: {
                        display: true,
                        text: 'Quantity Produced'
                    }
                }
            },
            plugins: {
                title: {
                    display: true,
                    text: 'Daily Production Output by Category'
                },
                tooltip: {
                    mode: 'index',
                    intersect: false
                }
            }
        }
    });
});
</script>

<div class="row">
    <div class="col-md-12">
        <?php echo display_msg($msg); ?>
        <div class="panel panel-default">
            <div class="panel-heading clearfix">
                <strong>
                    <span class="glyphicon glyphicon-th"></span>
                    <span>Production Outputs</span>
                </strong>
                <button type="button" class="btn btn-primary pull-right" data-toggle="modal" data-target="#addModal">
                    <span class="glyphicon glyphicon-plus"></span> Add Production Output
                </button>
            </div>
            <div class="panel-body">
                <div class="alert alert-info">
                    <strong>Total Quantity Produced:</strong> <?php echo htmlspecialchars($total_quantity_produced); ?>
                </div>
                <form class="form-inline" method="get" action="production_output.php">
                    <div class="form-group">
                        <label for="filter_part_number">Part Number</label>
                        <input type="text" class="form-control" id="filter_part_number" name="filter_part_number" 
                               value="<?php echo htmlspecialchars($filter_part_number); ?>">
                    </div>
                    <div class="form-group">
                        <label for="filter_categorie_id">Category</label>
                        <select class="form-control" id="filter_categorie_id" name="filter_categorie_id">
                            <option value="0">All Categories</option>
                            <?php foreach ($categories as $category): ?>
                                <option value="<?php echo $category['id']; ?>" <?php echo $filter_categorie_id == $category['id'] ? 'selected' : ''; ?>>
                                    <?php echo htmlspecialchars($category['name']); ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    <div class="form-group">
                        <label for="filter_start_date">Start Date</label>
                        <input type="date" class="form-control" id="filter_start_date" name="filter_start_date" 
                               value="<?php echo htmlspecialchars($filter_start_date); ?>">
                    </div>
                    <div class="form-group">
                        <label for="filter_end_date">End Date</label>
                        <input type="date" class="form-control" id="filter_end_date" name="filter_end_date" 
                               value="<?php echo htmlspecialchars($filter_end_date); ?>">
                    </div>
                    <button type="submit" class="btn btn-primary">Filter</button>
                    <a href="production_output.php?export=true" class="btn btn-success">Export to Excel</a>
                    <a href="production_output.php" class="btn btn-default">Reset Filters</a>
                </form>

                <div class="table-responsive" style="margin-top: 20px;">
                    <table class="table table-bordered table-striped">
                        <thead>
                            <tr>
                                <th>ID</th>
                                <th>No Order</th>
                                <th>Part Number</th>
                                <th>Name</th>
                                <th>Category</th>
                                <th>Quantity Produced</th>
                                <th>Production Date</th>
                                <th>Input Date</th>
                                <th>Actions</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($production_outputs as $production_output) : ?>
                                <tr>
                                    <td><?php echo htmlspecialchars($production_output['id']); ?></td>
                                    <td><?php echo htmlspecialchars($production_output['no_order']); ?></td>
                                    <td><?php echo htmlspecialchars($production_output['part_number']); ?></td>
                                    <td><?php echo htmlspecialchars($production_output['name']); ?></td>
                                    <td><?php echo htmlspecialchars($production_output['category_name'] ?? ''); ?></td>
                                    <td><?php echo htmlspecialchars($production_output['quantity_produced']); ?></td>
                                    <td><?php echo htmlspecialchars($production_output['production_date']); ?></td>
                                    <td><?php echo htmlspecialchars($production_output['input_date']); ?></td>
                                    <td>
                                        <button class="btn btn-warning btn-xs edit-btn" 
                                                data-id="<?php echo $production_output['id']; ?>" 
                                                data-no_order="<?php echo $production_output['no_order']; ?>" 
                                                data-part_number="<?php echo $production_output['part_number']; ?>" 
                                                data-categorie_id="<?php echo $production_output['categorie_id']; ?>" 
                                                data-quantity_produced="<?php echo $production_output['quantity_produced']; ?>" 
                                                data-production_date="<?php echo $production_output['production_date']; ?>"
                                                data-toggle="modal" data-target="#editModal">
                                            <span class="glyphicon glyphicon-edit"></span> Edit
                                        </button>
                                        <form method="post" action="production_output.php" style="display:inline;">
                                            <input type="hidden" name="delete_id" value="<?php echo $production_output['id']; ?>">
                                            <button type="submit" class="btn btn-danger btn-xs" 
                                                    onclick="return confirm('Are you sure you want to delete this item?');">
                                                <span class="glyphicon glyphicon-trash"></span> Delete
                                            </button>
                                        </form>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

<!-- Add Modal -->
<div class="modal fade" id="addModal" tabindex="-1" role="dialog" aria-labelledby="addModalLabel">
    <div class="modal-dialog" role="document">
        <div class="modal-content">
            <div class="modal-header">
                <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                    <span aria-hidden="true">&times;</span>
                </button>
                <h4 class="modal-title" id="addModalLabel">Add Production Output</h4>
            </div>
            <form method="post" action="production_output.php">
                <div class="modal-body">
                    <div class="form-group">
                        <label for="no_order">No Order</label>
                        <input type="text" class="form-control" id="no_order" name="no_order" required>
                    </div>
                    <div class="form-group">
                        <label for="part_number">Part Number</label>
                        <input type="text" class="form-control" id="part_number" name="part_number" required 
                               onchange="getCategoryId(this.value)" onblur="getCategoryId(this.value)">
                        <input type="hidden" id="categorie_id" name="categorie_id">
                        <small id="category_info" class="form-text text-muted"></small>
                    </div>
                    <div class="form-group">
                        <label for="quantity_produced">Quantity Produced</label>
                        <input type="number" class="form-control" id="quantity_produced" name="quantity_produced" required>
                    </div>
                    <div class="form-group">
                        <label for="production_date">Production Date</label>
                        <input type="date" class="form-control" id="production_date" name="production_date" required>
                    </div>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
                    <button type="submit" class="btn btn-primary" name="add_output">Save changes</button>
                </div>
            </form>
        </div>
    </div>
</div>

<!-- Edit Modal -->
<div class="modal fade" id="editModal" tabindex="-1" role="dialog" aria-labelledby="editModalLabel">
    <div class="modal-dialog" role="document">
        <div class="modal-content">
            <div class="modal-header">
                <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                    <span aria-hidden="true">&times;</span>
                </button>
                <h4 class="modal-title" id="editModalLabel">Edit Production Output</h4>
            </div>
            <form method="post" action="production_output.php">
                <div class="modal-body">
                    <input type="hidden" name="edit_id" id="edit_id">
                    <div class="form-group">
                        <label for="edit_no_order">No Order</label>
                        <input type="text" class="form-control" id="edit_no_order" name="edit_no_order" required>
                    </div>
                    <div class="form-group">
                        <label for="edit_part_number">Part Number</label>
                        <input type="text" class="form-control" id="edit_part_number" name="edit_part_number" required 
                               onchange="getEditCategoryId(this.value)" onblur="getEditCategoryId(this.value)">
                        <input type="hidden" id="edit_categorie_id" name="edit_categorie_id">
                        <small id="edit_category_info" class="form-text text-muted"></small>
                    </div>
                    <div class="form-group">
                        <label for="edit_quantity_produced">Quantity Produced</label>
                        <input type="number" class="form-control" id="edit_quantity_produced" name="edit_quantity_produced" required>
                    </div>
                    <div class="form-group">
                        <label for="edit_production_date">Production Date</label>
                        <input type="date" class="form-control" id="edit_production_date" name="edit_production_date" required>
                    </div>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
                    <button type="submit" class="btn btn-primary" name="edit_output">Save changes</button>
                </div>
            </form>
        </div>
    </div>
</div>

<script>
// Fungsi untuk mendapatkan categorie_id (form tambah)
function getCategoryId(partNumber) {
    if(partNumber) {
        fetch(`get_category_id.php?part_number=${encodeURIComponent(partNumber)}`)
            .then(response => response.json())
            .then(data => {
                if(data.success) {
                    document.getElementById('categorie_id').value = data.categorie_id;
                    // Tampilkan info kategori
                    fetch(`get_category_name.php?id=${data.categorie_id}`)
                        .then(response => response.json())
                        .then(catData => {
                            if(catData.success) {
                                document.getElementById('category_info').textContent = `Category: ${catData.category_name}`;
                            } else {
                                document.getElementById('category_info').textContent = 'Category not found';
                            }
                        });
                } else {
                    document.getElementById('categorie_id').value = '';
                    document.getElementById('category_info').textContent = 'Part number not found!';
                }
            })
            .catch(error => {
                console.error('Error:', error);
                document.getElementById('category_info').textContent = 'Error fetching data';
            });
    }
}

// Fungsi untuk form edit
function getEditCategoryId(partNumber) {
    if(partNumber) {
        fetch(`get_category_id.php?part_number=${encodeURIComponent(partNumber)}`)
            .then(response => response.json())
            .then(data => {
                if(data.success) {
                    document.getElementById('edit_categorie_id').value = data.categorie_id;
                    // Tampilkan info kategori
                    fetch(`get_category_name.php?id=${data.categorie_id}`)
                        .then(response => response.json())
                        .then(catData => {
                            if(catData.success) {
                                document.getElementById('edit_category_info').textContent = `Category: ${catData.category_name}`;
                            } else {
                                document.getElementById('edit_category_info').textContent = 'Category not found';
                            }
                        });
                } else {
                    document.getElementById('edit_categorie_id').value = '';
                    document.getElementById('edit_category_info').textContent = 'Part number not found!';
                }
            })
            .catch(error => {
                console.error('Error:', error);
                document.getElementById('edit_category_info').textContent = 'Error fetching data';
            });
    }
}

// Edit button click event
document.addEventListener('DOMContentLoaded', function() {
    const editButtons = document.querySelectorAll('.edit-btn');
    
    editButtons.forEach(button => {
        button.addEventListener('click', function() {
            const id = this.getAttribute('data-id');
            const no_order = this.getAttribute('data-no_order');
            const part_number = this.getAttribute('data-part_number');
            const categorie_id = this.getAttribute('data-categorie_id');
            const quantity_produced = this.getAttribute('data-quantity_produced');
            const production_date = this.getAttribute('data-production_date');

            document.getElementById('edit_id').value = id;
            document.getElementById('edit_no_order').value = no_order;
            document.getElementById('edit_part_number').value = part_number;
            document.getElementById('edit_categorie_id').value = categorie_id;
            document.getElementById('edit_quantity_produced').value = quantity_produced;
            document.getElementById('edit_production_date').value = production_date;

            // Set category info saat edit
            if(categorie_id) {
                fetch(`get_category_name.php?id=${categorie_id}`)
                    .then(response => response.json())
                    .then(data => {
                        if(data.success) {
                            document.getElementById('edit_category_info').textContent = `Category: ${data.category_name}`;
                        } else {
                            document.getElementById('edit_category_info').textContent = 'Category not found';
                        }
                    });
            }
        });
    });
});
</script>

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