<?php
require 'vendor/autoload.php'; // Load Composer autoloader
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;

$page_title = 'Production Output Schedule';
require_once('includes/load.php'); // Pastikan path ke file 'load.php' sudah benar
page_require_level(3); // Sesuaikan level akses sesuai kebutuhan aplikasi Anda

$msg = ''; // Variabel untuk pesan sukses/error

// --- Filter by Month and Search Functionality ---
$selected_month = isset($_GET['month']) ? $_GET['month'] : date('Y-m');
$start_date = $selected_month . '-01';
$end_date = date("Y-m-t", strtotime($start_date));

$search = '';
$search_condition = '';
if (isset($_GET['search']) && !empty($_GET['search'])) {
    $search = $db->escape($_GET['search']); // Sanitasi input pencarian
    $search_condition = " AND (ps.no_order LIKE '%{$search}%' OR
                                 ps.client LIKE '%{$search}%' OR
                                 ps.part_number LIKE '%{$search}%' OR
                                 p.name LIKE '%{$search}%')"; // Tambahkan p.name untuk pencarian nama produk
}

// --- Handle Adding New Production Output ---
if (isset($_POST['add_output'])) {
    $p_schedule_id = (int)$_POST['schedule_id'];
    $p_output_quantity = (int)$_POST['output_quantity'];
    $p_output_date = $db->escape($_POST['output_date']);

    // Validasi input
    if ($p_output_quantity <= 0 || empty($p_output_date)) {
        $msg = "Please fill all required fields and ensure quantity is positive.";
    } else {
        // Ambil informasi jadwal produksi, termasuk part_number
        $schedule_info = find_by_id('production_schedule', $p_schedule_id);

        if (!$schedule_info) {
            $msg = "Error: Production schedule not found.";
        } else {
            // Dapatkan part_number dari informasi jadwal produksi yang ditemukan
            // Ini akan disimpan langsung ke tabel output_schedule
            $p_part_number = $db->escape($schedule_info['part_number']); 

            $scheduled_quantity = (int)$schedule_info['quantity_order'];

            // Hitung total output yang sudah direkam untuk jadwal ini
            $current_output_query = "SELECT COALESCE(SUM(output_quantity), 0) AS total_output FROM output_schedule WHERE schedule_id = '{$p_schedule_id}'";
            $current_output_result = $db->query($current_output_query);
            $current_total_output = $current_output_result ? (int)$db->fetch_assoc($current_output_result)['total_output'] : 0;
            $remaining_quantity = $scheduled_quantity - $current_total_output;

            // Validasi agar output tidak melebihi sisa kuantitas
            if (($current_total_output + $p_output_quantity) > $scheduled_quantity) {
                $msg = "Error: Output quantity (" . $p_output_quantity . ") would exceed the remaining scheduled quantity (" . $remaining_quantity . ").";
            } else {
                // Insert data output baru ke tabel output_schedule, termasuk part_number
                $query = "INSERT INTO output_schedule (schedule_id, part_number, output_quantity, output_date, created_at) VALUES (
                    '{$p_schedule_id}',
                    '{$p_part_number}',  /* Ini adalah bagian yang ditambahkan */
                    '{$p_output_quantity}',
                    '{$p_output_date}',
                    NOW()
                )";
                if ($db->query($query)) {
                    $msg = "Production output recorded successfully!";
                    // Redirect untuk merefresh halaman dan melihat perubahan
                    // Tambahkan parameter month dan search agar filter tidak hilang
                    redirect('production_output_schedule.php?month=' . urlencode($selected_month) . (!empty($search) ? '&search=' . urlencode($search) : ''), false);
                } else {
                    $msg = "Failed to record production output: " . $db->error();
                }
            }
        }
    }
}

// --- Query for Production Schedules with current output status (used for both display and export) ---
// Memilih data jadwal produksi beserta total output yang sudah direkam dan informasi produk
// Perhatikan: query ini untuk tampilan jadwal, BUKAN untuk recorded outputs.
// ps.client tetap di SELECT jika suatu saat diperlukan, tetapi tidak akan ditampilkan di HTML maupun Excel berdasarkan pengaturan headerColumns.
$query_schedules = "
    SELECT
        ps.id AS schedule_id,
        ps.no_order,
        ps.client,
        ps.part_number,
        ps.quantity_order,
        ps.delivery_date,
        p.name AS product_name,
        COALESCE(SUM(os.output_quantity), 0) AS total_output_recorded
    FROM
        production_schedule ps
    LEFT JOIN
        products p ON ps.part_number = p.part_number
    LEFT JOIN
        output_schedule os ON ps.id = os.schedule_id
    WHERE
        ps.delivery_date BETWEEN '{$start_date}' AND '{$end_date}'
        {$search_condition}
    GROUP BY
        ps.id, ps.no_order, ps.client, ps.part_number, ps.quantity_order, ps.delivery_date, p.name
    ORDER BY
        ps.delivery_date ASC
";
$result_schedules = $db->query($query_schedules);
$production_schedules = $result_schedules ? $db->while_loop($result_schedules) : [];

// --- Calculate Dashboard Totals (used for both display and export) ---
$total_planned = 0;
$total_output = 0;
foreach ($production_schedules as $schedule) {
    $total_planned += (int)$schedule['quantity_order'];
    $total_output += (int)$schedule['total_output_recorded'];
}
$total_balance = $total_planned - $total_output;

// --- Handle Export to Excel ---
if (isset($_GET['export']) && $_GET['export'] == 'true') {
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Production Schedule');

    // Gaya untuk header tabel Excel
    $headerStyle = [
        'font' => ['bold' => true, 'color' => ['argb' => 'FFFFFFFF']],
        'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['argb' => 'FF4CAF50']], // Hijau
        'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER],
        'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['argb' => 'FF000000']]],
    ];

    // Gaya untuk baris total di dashboard Excel
    $totalStyle = [
        'font' => ['bold' => true],
        'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['argb' => 'FFE0E0E0']], // Abu-abu terang
        'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['argb' => 'FF000000']]],
    ];
    
    // Gaya untuk sel data tabel Excel
    $cellStyle = [
        'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['argb' => 'FF000000']]],
        'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
    ];

    // --- Dashboard Section (di atas tabel utama di Excel) ---
    $sheet->setCellValue('A1', 'Production Overview for ' . date('F Y', strtotime($start_date)));
    $sheet->mergeCells('A1:C1');
    $sheet->getStyle('A1')->applyFromArray($headerStyle)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);

    $sheet->setCellValue('A3', 'Total Planned Quantity');
    $sheet->setCellValue('B3', 'Total Output Recorded');
    $sheet->setCellValue('C3', 'Total Remaining Balance');
    $sheet->getStyle('A3:C3')->applyFromArray($totalStyle);

    $sheet->setCellValue('A4', $total_planned);
    $sheet->setCellValue('B4', $total_output);
    $sheet->setCellValue('C4', $total_balance);

    // Set warna teks untuk balance di dashboard Excel
    if ($total_balance <= 0) {
        $sheet->getStyle('C4')->getFont()->getColor()->setARGB('FF28A745'); // Hijau untuk "Completed"
    } else {
        $sheet->getStyle('C4')->getFont()->getColor()->setARGB('FFDC3545'); // Merah untuk "Outstanding"
    }

    $sheet->getStyle('A4:C4')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $sheet->getStyle('A4:C4')->applyFromArray($cellStyle); // Terapkan border pada nilai dashboard

    // --- Header Tabel Jadwal Produksi untuk Excel ---
    $headerColumns = [
        'No.',
        'Order Number',
        'Part Number',          // Kolom Part Number ditambahkan di sini
        'Product Name',
        'Scheduled Quantity',
        'Total Output Recorded',
        'Balance',
        'Delivery Date'
    ];

    $startRow = 6; // Tabel dimulai di baris 6 setelah dashboard
    $col = 'A';
    foreach ($headerColumns as $header) {
        $sheet->setCellValue($col . $startRow, $header);
        $col++;
    }
    $sheet->getStyle('A' . $startRow . ':' . chr(ord('A') + count($headerColumns) - 1) . $startRow)->applyFromArray($headerStyle);

    // --- Data Tabel Jadwal Produksi untuk Excel ---
    $row = $startRow + 1;
    $counter = 1;
    foreach ($production_schedules as $schedule) {
        $remaining_qty = (int)$schedule['quantity_order'] - (int)$schedule['total_output_recorded'];

        $sheet->setCellValue('A' . $row, $counter++);
        $sheet->setCellValue('B' . $row, $schedule['no_order']);
        $sheet->setCellValue('C' . $row, $schedule['part_number']); // Menampilkan Part Number
        $sheet->setCellValue('D' . $row, $schedule['product_name']);
        $sheet->setCellValue('E' . $row, (int)$schedule['quantity_order']);
        $sheet->setCellValue('F' . $row, (int)$schedule['total_output_recorded']);
        $sheet->setCellValue('G' . $row, $remaining_qty);
        $sheet->setCellValue('H' . $row, date("d-m-Y", strtotime($schedule['delivery_date'])));

        // Set warna teks untuk kolom Balance di tabel Excel
        if ($remaining_qty <= 0) {
            $sheet->getStyle('G' . $row)->getFont()->getColor()->setARGB('FF28A745'); // Hijau
        } else {
            $sheet->getStyle('G' . $row)->getFont()->getColor()->setARGB('FFDC3545'); // Merah
        }
        
        $sheet->getStyle('A' . $row . ':' . 'H' . $row)->applyFromArray($cellStyle); // Terapkan border pada setiap baris data

        $row++;
    }

    // Auto-size kolom agar lebar sesuai konten
    foreach (range('A', $sheet->getHighestColumn()) as $col) {
        $sheet->getColumnDimension($col)->setAutoSize(true);
    }

    // Atur nama file Excel
    $filename = 'Production_Schedule_' . date('Y-m-d_His') . '.xlsx'; // Tambah waktu untuk nama unik

    // Header HTTP untuk download file Excel
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $filename . '"');
    header('Cache-Control: max-age=0');
    header('Cache-Control: max-age=1'); // Untuk IE 9

    // Jika Anda menggunakan SSL, mungkin perlu header ini
    header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
    header ('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
    header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
    header ('Pragma: public'); // HTTP/1.0

    $writer = new Xlsx($spreadsheet);
    $writer->save('php://output');
    exit(); // Sangat penting: Hentikan eksekusi skrip setelah file Excel dibuat
}

// Jika bukan permintaan ekspor, lanjutkan untuk menampilkan HTML
include_once('layouts/header.php'); // Pastikan path ke header.php sudah benar
?>

<div class="row">
    <div class="col-md-12">
        <?php echo display_msg($msg); // Menampilkan pesan dari variabel $msg ?>
    </div>
</div>

<div class="row">
    <div class="col-md-12">
        <div class="panel panel-default">
            <div class="panel-heading">
                <h3 class="panel-title">Production Overview for <?php echo date('F Y', strtotime($start_date)); ?></h3>
            </div>
            <div class="panel-body">
                <div class="col-md-4">
                    <div class="well well-sm">
                        <h4 class="text-center">Total Planned Quantity</h4>
                        <p class="text-center h2 text-primary"><?php echo number_format($total_planned); ?></p>
                    </div>
                </div>
                <div class="col-md-4">
                    <div class="well well-sm">
                        <h4 class="text-center">Total Output Recorded</h4>
                        <p class="text-center h2 text-success"><?php echo number_format($total_output); ?></p>
                    </div>
                </div>
                <div class="col-md-4">
                    <div class="well well-sm">
                        <h4 class="text-center">Total Remaining Balance</h4>
                        <p class="text-center h2 <?php echo ($total_balance <= 0) ? 'text-success' : 'text-danger'; ?>"><?php echo number_format($total_balance); ?></p>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

---

<div class="row">
    <div class="col-md-12">
        <div class="panel panel-default">
            <div class="panel-heading clearfix">
                <div class="pull-left">
                    <h3 class="panel-title">Production Schedules for Output - <?php echo date('F Y', strtotime($start_date)); ?></h3>
                </div>
                <div class="pull-right">
                    <a href="production_output_schedule.php?export=true&month=<?php echo htmlspecialchars($selected_month); ?>&search=<?php echo htmlspecialchars($search); ?>" class="btn btn-success">
                        <span class="glyphicon glyphicon-download-alt"></span> Export to Excel
                    </a>
                    <a href="recorded_outputs.php" class="btn btn-info">
                        <span class="glyphicon glyphicon-list-alt"></span> Recorded Outputs
                    </a>
                </div>
            </div>
            <div class="panel-body">
                <form method="GET" action="production_output_schedule.php" class="form-inline mb-3">
                    <div class="form-group">
                        <label for="month">Select Month:</label>
                        <input type="month" name="month" id="month" class="form-control" value="<?php echo htmlspecialchars($selected_month); ?>">
                    </div>
                    <button type="submit" class="btn btn-primary">Filter</button>
                    <div class="form-group pull-right">
                        <div class="input-group">
                            <input type="text" class="form-control" name="search" placeholder="Search..." value="<?php echo htmlspecialchars($search); ?>">
                            <span class="input-group-btn">
                                <button class="btn btn-default" type="submit">Search</button>
                            </span>
                        </div>
                    </div>
                </form>

                <div class="table-responsive">
                    <table class="table table-bordered table-striped">
                        <thead>
                            <tr>
                                <th class="text-center">#</th>
                                <th>Order Number</th>
                                <th>Part Number</th> <th>Product Name</th>
                                <th class="text-center">Scheduled Quantity</th>
                                <th class="text-center">Total Output Recorded</th>
                                <th class="text-center">Balance</th>
                                <th class="text-center">Delivery Date</th>
                                <th class="text-center">Actions</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (count($production_schedules) > 0): ?>
                                <?php $counter_display = 1; // Counter terpisah untuk tampilan tabel
                                foreach ($production_schedules as $schedule) :
                                    $remaining_qty = (int)$schedule['quantity_order'] - (int)$schedule['total_output_recorded'];
                                ?>
                                    <tr>
                                        <td class="text-center"><?php echo $counter_display++; ?></td>
                                        <td><?php echo remove_junk($schedule['no_order']); ?></td>
                                        <td><?php echo remove_junk($schedule['part_number']); ?></td> <td><?php echo remove_junk($schedule['product_name']); ?></td>
                                        <td class="text-center"><?php echo (int)$schedule['quantity_order']; ?></td>
                                        <td class="text-center"><?php echo (int)$schedule['total_output_recorded']; ?></td>
                                        <td class="text-center <?php echo ($remaining_qty <= 0) ? 'text-success' : 'text-danger'; ?>">
                                            <?php echo number_format($remaining_qty); ?>
                                        </td>
                                        <td class="text-center"><?php echo date("d-m-Y", strtotime($schedule['delivery_date'])); ?></td>
                                        <td class="text-center">
                                            <?php if ($remaining_qty > 0): ?>
                                                <button type="button" class="btn btn-success btn-xs" data-toggle="modal" data-target="#addOutputModal"
                                                        data-schedule-id="<?php echo (int)$schedule['schedule_id']; ?>"
                                                        data-no-order="<?php echo htmlspecialchars(remove_junk($schedule['no_order'])); ?>"
                                                        data-part-number="<?php echo htmlspecialchars(remove_junk($schedule['part_number'])); ?>"
                                                        data-remaining-qty="<?php echo $remaining_qty; ?>">
                                                    <span class="glyphicon glyphicon-plus"></span> Add Output
                                                </button>
                                            <?php else: ?>
                                                <span class="label label-success">Completed</span>
                                            <?php endif; ?>
                                        </td>
                                    </tr>
                                <?php endforeach; ?>
                            <?php else: ?>
                                <tr>
                                    <td colspan="9" class="text-center">No production schedules found for the selected criteria.</td>
                                </tr>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

<div class="modal fade" id="addOutputModal" tabindex="-1" role="dialog" aria-labelledby="addOutputModalLabel">
    <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="addOutputModalLabel">Record Production Output</h4>
            </div>
            <form method="POST" action="production_output_schedule.php?month=<?php echo htmlspecialchars($selected_month); ?><?php echo !empty($search) ? '&search=' . urlencode($search) : ''; ?>">
                <div class="modal-body">
                    <input type="hidden" name="schedule_id" id="modal_schedule_id">
                    <div class="form-group">
                        <label for="modal_no_order">Order Number</label>
                        <input type="text" class="form-control" id="modal_no_order" readonly>
                    </div>
                    <div class="form-group">
                        <label for="modal_part_number">Part Number</label>
                        <input type="text" class="form-control" id="modal_part_number" readonly>
                    </div>
                    <div class="form-group">
                        <label for="modal_remaining_qty">Remaining Quantity</label>
                        <input type="text" class="form-control" id="modal_remaining_qty" readonly>
                    </div>
                    <div class="form-group">
                        <label for="output_quantity">Output Quantity <span class="text-danger">*</span></label>
                        <input type="number" class="form-control" name="output_quantity" id="output_quantity" min="1" required>
                    </div>
                    <div class="form-group">
                        <label for="output_date">Output Date <span class="text-danger">*</span></label>
                        <input type="date" class="form-control" name="output_date" id="output_date" value="<?php echo date('Y-m-d'); ?>" required>
                    </div>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
                    <button type="submit" name="add_output" class="btn btn-primary">Record Output</button>
                </div>
            </form>
        </div>
    </div>
</div>

<?php include_once('layouts/footer.php'); // Pastikan path ke footer.php sudah benar ?>

<script>
$(document).ready(function() {
    $('#addOutputModal').on('show.bs.modal', function (event) {
        var button = $(event.relatedTarget); // Tombol yang memicu modal
        var scheduleId = button.data('schedule-id');
        var noOrder = button.data('no-order');
        var partNumber = button.data('part-number');
        var remainingQty = button.data('remaining-qty');

        var modal = $(this);
        modal.find('#modal_schedule_id').val(scheduleId);
        modal.find('#modal_no_order').val(noOrder);
        modal.find('#modal_part_number').val(partNumber);
        modal.find('#modal_remaining_qty').val(remainingQty);
        modal.find('#output_quantity').attr('max', remainingQty); // Set max value untuk input kuantitas output
        modal.find('#output_quantity').val(remainingQty); // Mengisi default dengan sisa kuantitas
    });
});
</script>