<?php

// Include PhpSpreadsheet library (make sure it's installed via Composer)
require_once('includes/load.php');
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

page_require_level(15);

// Create connection
$conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Check if required tables exist
$requiredTables = ['bom_level', 'products'];
$tablesExist = true;
$missingTables = [];

foreach ($requiredTables as $table) {
    $tableCheck = $conn->query("SHOW TABLES LIKE '$table'");
    if (!$tableCheck || $tableCheck->num_rows == 0) {
        $tablesExist = false;
        $missingTables[] = $table;
    }
}

// Initialize variables
$searchTerm = isset($_POST['search_term']) ? trim($_POST['search_term']) : '';
$results = [];
$summary = [];
$error = '';
$searchPerformed = false;

// --- BARU: Inisialisasi variabel untuk pesan update ---
$update_message = '';
$update_message_type = '';

// --- BARU: Logika untuk menangani pembaruan harga ---
if (isset($_POST['update_all_prices'])) {
    if ($tablesExist) {
        try {
            $updateSql = "
                UPDATE
                    bom_level bl
                JOIN
                    products p ON bl.child_bom = p.part_number
                SET
                    bl.buy_price = p.buy_price * bl.qty;
            ";

            if ($conn->query($updateSql) === TRUE) {
                $affectedRows = $conn->affected_rows;
                $update_message = "Success! " . $affectedRows . " component prices have been updated.";
                $update_message_type = 'success';
            } else {
                throw new Exception("Error updating prices: " . $conn->error);
            }
        } catch (Exception $e) {
            $update_message = $e->getMessage();
            $update_message_type = 'error';
        }
    } else {
        $update_message = "Cannot update prices because required tables are missing.";
        $update_message_type = 'error';
    }
}

// Handle Excel exports
if (isset($_POST['export_excel'])) {
    $exportType = isset($_POST['export_type']) ? $_POST['export_type'] : 'summary';

    if ($tablesExist) {
        try {
            // Create new Spreadsheet object
            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();

            if ($exportType === 'summary') {
                // Get the summary data
                $exportQuery = "
                    SELECT 
                        b.parent_bom,
                        p.name as product_name,
                        COUNT(*) as component_count,
                        COALESCE(SUM(b.qty), 0) as total_qty,
                        COALESCE(SUM(b.buy_price), 0) as total_cost,
                        MAX(b.last_update) as last_calculated
                    FROM bom_level b
                    LEFT JOIN products p ON b.parent_bom = p.part_number
                ";

                $exportParams = [];
                $exportTypes = "";

                if (!empty($searchTerm)) {
                    $exportQuery .= " WHERE b.parent_bom = ? OR b.parent_bom LIKE ? OR p.name LIKE ?";
                    $exportParams[] = $searchTerm;
                    $exportParams[] = "%" . $searchTerm . "%";
                    $exportParams[] = "%" . $searchTerm . "%";
                    $exportTypes = "sss";
                }

                $exportQuery .= " GROUP BY b.parent_bom, p.name ORDER BY b.parent_bom";

                $exportStmt = $conn->prepare($exportQuery);
                if ($exportStmt === false) {
                    throw new Exception("Prepare export failed: " . $conn->error);
                }

                if (!empty($exportTypes)) {
                    $exportStmt->bind_param($exportTypes, ...$exportParams);
                }

                if (!$exportStmt->execute()) {
                    throw new Exception("Execute export failed: " . $exportStmt->error);
                }

                $exportResult = $exportStmt->get_result();
                $exportData = $exportResult->fetch_all(MYSQLI_ASSOC);

                // Set document properties
                $spreadsheet->getProperties()
                    ->setCreator("BOM Search System")
                    ->setTitle("BOM Summary Export")
                    ->setDescription("Export of BOM Summary Data");

                // Add headers
                $sheet->setCellValue('A1', 'BOM Summary Export');
                $sheet->mergeCells('A1:F1');
                $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(14);

                if (!empty($searchTerm)) {
                    $sheet->setCellValue('A2', 'Search Term: ' . $searchTerm);
                    $sheet->mergeCells('A2:F2');
                }

                // Column headers
                $headers = ['Parent BOM', 'Product Name', 'Components', 'Total Quantity', 'Total Buy Price', 'Last Updated'];
                $sheet->fromArray($headers, null, 'A4');

                // Style for headers
                $headerStyle = [
                    'font' => ['bold' => true],
                    'fill' => [
                        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                        'startColor' => ['argb' => 'FF3498DB']
                    ],
                    'borders' => [
                        'allBorders' => [
                            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN
                        ]
                    ],
                    'alignment' => [
                        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                    ]
                ];
                $sheet->getStyle('A4:F4')->applyFromArray($headerStyle);

                // Add data
                $row = 5;
                foreach ($exportData as $item) {
                    $sheet->setCellValue('A' . $row, $item['parent_bom']);
                    $sheet->setCellValue('B' . $row, $item['product_name'] ?? 'N/A');
                    $sheet->setCellValue('C' . $row, $item['component_count']);
                    $sheet->setCellValue('D' . $row, number_format($item['total_qty'], 4));
                    $sheet->setCellValue('E' . $row, 'Rp. ' . number_format($item['total_cost'], 2));
                    $sheet->setCellValue('F' . $row, $item['last_calculated'] ?? 'N/A');
                    $row++;
                }

                // Format columns
                $sheet->getColumnDimension('A')->setWidth(20);
                $sheet->getColumnDimension('B')->setWidth(30);
                $sheet->getColumnDimension('C')->setWidth(15);
                $sheet->getColumnDimension('D')->setWidth(15);
                $sheet->getColumnDimension('E')->setWidth(20);
                $sheet->getColumnDimension('F')->setWidth(20);

                // Format numbers
                $sheet->getStyle('D5:D' . ($row - 1))->getNumberFormat()->setFormatCode('0.0000');
                $sheet->getStyle('E5:E' . ($row - 1))->getNumberFormat()->setFormatCode('"Rp. "#,##0.00');

                // Add borders to data
                $dataStyle = [
                    'borders' => [
                        'allBorders' => [
                            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN
                        ]
                    ]
                ];
                $sheet->getStyle('A4:F' . ($row - 1))->applyFromArray($dataStyle);

                $filename = "bom_summary_export_" . date('Ymd_His') . ".xlsx";
            } elseif ($exportType === 'details' && !empty($searchTerm)) {
                // Get the detailed component data
                $exportQuery = "
                    SELECT b.*, p.name as product_name 
                    FROM bom_level b
                    LEFT JOIN products p ON b.parent_bom = p.part_number
                    WHERE b.parent_bom = ? OR b.parent_bom LIKE ? OR p.name LIKE ?
                    ORDER BY b.parent_bom, b.level, b.child_bom
                ";

                $exportStmt = $conn->prepare($exportQuery);
                if ($exportStmt === false) {
                    throw new Exception("Prepare export failed: " . $conn->error);
                }

                $exactParam = $searchTerm;
                $partialParam = "%" . $searchTerm . "%";
                $nameParam = "%" . $searchTerm . "%";
                $exportStmt->bind_param("sss", $exactParam, $partialParam, $nameParam);

                if (!$exportStmt->execute()) {
                    throw new Exception("Execute export failed: " . $exportStmt->error);
                }

                $exportResult = $exportStmt->get_result();
                $exportData = $exportResult->fetch_all(MYSQLI_ASSOC);

                // Set document properties
                $spreadsheet->getProperties()
                    ->setCreator("BOM Search System")
                    ->setTitle("BOM Component Details Export")
                    ->setDescription("Export of BOM Component Details");

                // Add headers
                $sheet->setCellValue('A1', 'BOM Component Details Export');
                $sheet->mergeCells('A1:J1');
                $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(14);

                $sheet->setCellValue('A2', 'Search Term: ' . $searchTerm);
                $sheet->mergeCells('A2:J2');

                // Column headers
                $headers = ['ID', 'Parent BOM', 'Product Name', 'Child BOM', 'Phantom', 'Level', 'Quantity', 'Location', 'Buy Price', 'Last Update'];
                $sheet->fromArray($headers, null, 'A4');

                // Style for headers
                $headerStyle = [
                    'font' => ['bold' => true],
                    'fill' => [
                        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                        'startColor' => ['argb' => 'FF3498DB']
                    ],
                    'borders' => [
                        'allBorders' => [
                            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN
                        ]
                    ],
                    'alignment' => [
                        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                    ]
                ];
                $sheet->getStyle('A4:J4')->applyFromArray($headerStyle);

                // Add data
                $row = 5;
                foreach ($exportData as $item) {
                    $sheet->setCellValue('A' . $row, $item['id']);
                    $sheet->setCellValue('B' . $row, $item['parent_bom']);
                    $sheet->setCellValue('C' . $row, $item['product_name'] ?? 'N/A');
                    $sheet->setCellValue('D' . $row, $item['child_bom']);
                    $sheet->setCellValue('E' . $row, $item['phantom_bom']);
                    $sheet->setCellValue('F' . $row, $item['level']);
                    $sheet->setCellValue('G' . $row, number_format($item['qty'], 4));
                    $sheet->setCellValue('H' . $row, $item['location']);
                    $sheet->setCellValue('I' . $row, 'Rp. ' . number_format($item['buy_price'], 2));
                    $sheet->setCellValue('J' . $row, $item['last_update']);
                    $row++;
                }

                // Format columns
                $sheet->getColumnDimension('A')->setWidth(8);
                $sheet->getColumnDimension('B')->setWidth(20);
                $sheet->getColumnDimension('C')->setWidth(30);
                $sheet->getColumnDimension('D')->setWidth(20);
                $sheet->getColumnDimension('E')->setWidth(12);
                $sheet->getColumnDimension('F')->setWidth(8);
                $sheet->getColumnDimension('G')->setWidth(12);
                $sheet->getColumnDimension('H')->setWidth(15);
                $sheet->getColumnDimension('I')->setWidth(15);
                $sheet->getColumnDimension('J')->setWidth(20);

                // Format numbers
                $sheet->getStyle('G5:G' . ($row - 1))->getNumberFormat()->setFormatCode('0.0000');
                $sheet->getStyle('I5:I' . ($row - 1))->getNumberFormat()->setFormatCode('"Rp. "#,##0.00');

                // Add borders to data
                $dataStyle = [
                    'borders' => [
                        'allBorders' => [
                            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN
                        ]
                    ]
                ];
                $sheet->getStyle('A4:J' . ($row - 1))->applyFromArray($dataStyle);

                $filename = "bom_component_details_export_" . date('Ymd_His') . ".xlsx";
            }

            // Set headers for download
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="' . $filename . '"');
            header('Cache-Control: max-age=0');

            // Write file to output
            $writer = new Xlsx($spreadsheet);
            $writer->save('php://output');
            exit;
        } catch (Exception $e) {
            $error = "Export failed: " . $e->getMessage();
        }
    }
}

// Process form submission or show all if tables exist
if ($tablesExist) {
    $searchPerformed = true;

    try {
        // Get summary from bom_level table
        $summaryQuery = "
            SELECT 
                b.parent_bom,
                p.name as product_name,
                COUNT(*) as component_count,
                COALESCE(SUM(b.qty), 0) as total_qty,
                COALESCE(SUM(b.buy_price), 0) as total_cost,
                MAX(b.last_update) as last_calculated
            FROM bom_level b
            LEFT JOIN products p ON b.parent_bom = p.part_number
        ";

        $summaryParams = [];
        $summaryTypes = "";

        if (!empty($searchTerm)) {
            $summaryQuery .= " WHERE b.parent_bom = ? OR b.parent_bom LIKE ? OR p.name LIKE ?";
            $summaryParams[] = $searchTerm;
            $summaryParams[] = "%" . $searchTerm . "%";
            $summaryParams[] = "%" . $searchTerm . "%";
            $summaryTypes = "sss";
        }

        $summaryQuery .= " GROUP BY b.parent_bom, p.name ORDER BY b.parent_bom";

        $summaryStmt = $conn->prepare($summaryQuery);
        if ($summaryStmt === false) {
            throw new Exception("Prepare summary failed: " . $conn->error);
        }

        if (!empty($summaryTypes)) {
            $summaryStmt->bind_param($summaryTypes, ...$summaryParams);
        }

        if (!$summaryStmt->execute()) {
            throw new Exception("Execute summary failed: " . $summaryStmt->error);
        }

        $summaryResult = $summaryStmt->get_result();
        $summary = $summaryResult->fetch_all(MYSQLI_ASSOC);

        // Get detailed results if a search term was provided
        if (!empty($searchTerm)) {
            $stmt = $conn->prepare("
                SELECT b.*, p.name as product_name 
                FROM bom_level b
                LEFT JOIN products p ON b.parent_bom = p.part_number
                WHERE b.parent_bom = ? OR b.parent_bom LIKE ? OR p.name LIKE ?
                ORDER BY b.parent_bom, b.level, b.child_bom
            ");
            if ($stmt === false) {
                throw new Exception("Prepare failed: " . $conn->error);
            }

            $exactParam = $searchTerm;
            $partialParam = "%" . $searchTerm . "%";
            $nameParam = "%" . $searchTerm . "%";
            $stmt->bind_param("sss", $exactParam, $partialParam, $nameParam);

            if (!$stmt->execute()) {
                throw new Exception("Execute failed: " . $stmt->error);
            }

            $result = $stmt->get_result();
            $results = $result->fetch_all(MYSQLI_ASSOC);
        }
    } catch (Exception $e) {
        $error = $e->getMessage();
    }
}
?>

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>BOM Parent Search</title>
    <link href="https://fonts.googleapis.com/css2?family=Roboto:wght@300;400;500;700&display=swap" rel="stylesheet">
    <style>
        :root {
            --primary-color: #3498db;
            --secondary-color: #2980b9;
            --success-color: #27ae60;
            --success-hover: #219653;
            --danger-color: #e74c3c;
            --danger-hover: #c0392b; /* DIUBAH: Menambahkan warna hover untuk bahaya */
            --warning-color: #f39c12;
            --light-color: #ecf0f1;
            --dark-color: #2c3e50;
            --gray-color: #95a5a6;
            --border-color: #ddd;
            --shadow: 0 4px 6px rgba(0, 0, 0, 0.1);
        }

        * {
            margin: 0;
            padding: 0;
            box-sizing: border-box;
        }

        body {
            font-family: 'Roboto', sans-serif;
            line-height: 1.6;
            color: #333;
            background-color: #f5f7fa;
            padding: 20px;
        }

        .container {
            max-width: 1400px;
            margin: 0 auto;
            background: white;
            border-radius: 8px;
            box-shadow: var(--shadow);
            padding: 30px;
        }

        h1,
        h2,
        h3 {
            color: var(--dark-color);
            margin-bottom: 20px;
        }

        h1 {
            font-size: 28px;
            border-bottom: 2px solid var(--primary-color);
            padding-bottom: 10px;
            margin-bottom: 30px;
        }

        h2 {
            font-size: 22px;
            margin-top: 30px;
            color: var(--secondary-color);
        }

        .action-buttons {
            display: flex;
            gap: 15px;
            align-items: center;
            margin-bottom: 25px;
            flex-wrap: wrap;
        }

        .button-secondary,
        .export-button,
        button[type="submit"] {
            padding: 10px 20px;
            border: none;
            border-radius: 4px;
            cursor: pointer;
            font-weight: 500;
            transition: all 0.3s ease;
            color: white;
            text-decoration: none;
            font-size: 14px;
        }

        .button-secondary {
            background: var(--primary-color);
        }

        .button-secondary:hover {
            background: var(--secondary-color);
        }
        
        /* BARU: Style untuk tombol update agar berbeda */
        .update-button {
            background-color: var(--danger-color);
        }
        .update-button:hover {
            background-color: var(--danger-hover);
        }

        .export-button {
            background: var(--success-color);
        }

        .export-button:hover {
            background: var(--success-hover);
        }

        .export-details-button {
            background: var(--warning-color);
        }

        .export-details-button:hover {
            background: #e67e22;
        }

        button[type="submit"] {
            background: var(--primary-color);
        }

        button[type="submit"]:hover {
            background: var(--secondary-color);
        }

        .search-form {
            margin-bottom: 30px;
            background: #f8f9fa;
            padding: 20px;
            border-radius: 6px;
            border: 1px solid var(--border-color);
        }

        .form-group {
            margin-bottom: 15px;
        }

        label {
            display: block;
            margin-bottom: 5px;
            font-weight: 500;
        }

        input[type="text"] {
            width: 100%;
            padding: 10px;
            border: 1px solid var(--border-color);
            border-radius: 4px;
            font-size: 16px;
        }

        .message {
            padding: 15px;
            margin-bottom: 20px;
            border-radius: 4px;
            font-weight: 500;
        }

        .message.error {
            background-color: #fdecea;
            color: var(--danger-color);
            border-left: 4px solid var(--danger-color);
        }

        .message.success {
            background-color: #e8f5e9;
            color: var(--success-color);
            border-left: 4px solid var(--success-color);
        }

        .message.info {
            background-color: #e3f2fd;
            color: var(--primary-color);
            border-left: 4px solid var(--primary-color);
        }

        table {
            width: 100%;
            border-collapse: collapse;
            margin-bottom: 30px;
            box-shadow: 0 1px 3px rgba(0, 0, 0, 0.1);
        }

        th,
        td {
            padding: 12px 15px;
            text-align: left;
            border-bottom: 1px solid var(--border-color);
        }

        th {
            background-color: var(--primary-color);
            color: white;
            font-weight: 500;
            text-transform: uppercase;
            font-size: 13px;
        }

        tr:nth-child(even) {
            background-color: #f8f9fa;
        }

        tr:hover {
            background-color: #e9f7fe;
        }

        @media (max-width: 768px) {
            .container {
                padding: 15px;
            }

            table {
                display: block;
                overflow-x: auto;
            }

            .action-buttons {
                flex-direction: column;
                align-items: flex-start;
            }
        }
    </style>
</head>

<body>
    <div class="container">
        <h1>BOM Parent Search</h1>

        <!-- BARU: Tempat untuk menampilkan pesan hasil update -->
        <?php if (!empty($update_message)): ?>
            <div class="message <?php echo $update_message_type; ?>">
                <?php echo htmlspecialchars($update_message); ?>
            </div>
        <?php endif; ?>

        <?php if (!$tablesExist): ?>
            <div class="message error">
                Error: The following required tables are missing:
                <ul>
                    <?php foreach ($missingTables as $table): ?>
                        <li><?php echo htmlspecialchars($table); ?></li>
                    <?php endforeach; ?>
                </ul>
                Please check your database setup.
            </div>
        <?php else: ?>
            <div class="action-buttons">
                <!-- BARU: Tombol untuk update harga. Menggunakan form terpisah. -->
                <form method="POST" style="display: inline;" onsubmit="return confirm('Are you sure you want to update all component prices? This action cannot be undone.');">
                    <button type="submit" name="update_all_prices" class="update-button">Update All Prices</button>
                </form>

                <form method="POST" style="display: inline;">
                    <button type="submit" name="export_excel" class="export-button">Export Summary to Excel</button>
                    <input type="hidden" name="export_type" value="summary">
                    <input type="hidden" name="search_term" value="<?php echo htmlspecialchars($searchTerm); ?>">
                </form>

                <?php if (!empty($searchTerm) && !empty($results)): ?>
                    <form method="POST" style="display: inline;">
                        <button type="submit" name="export_excel" class="export-details-button">Export Component Details to Excel</button>
                        <input type="hidden" name="export_type" value="details">
                        <input type="hidden" name="search_term" value="<?php echo htmlspecialchars($searchTerm); ?>">
                    </form>
                <?php endif; ?>
            </div>

            <div class="search-form">
                <form method="POST">
                    <div class="form-group">
                        <label for="search_term">Search by Parent BOM or Product Name:</label>
                        <input type="text" id="search_term" name="search_term"
                            value="<?php echo htmlspecialchars($searchTerm); ?>"
                            placeholder="Enter parent BOM code or product name (leave empty to show all)">
                    </div>
                    <button type="submit">Search</button>
                </form>
            </div>

            <?php if (!empty($error)): ?>
                <div class="message error"><?php echo $error; ?></div>
            <?php endif; ?>

            <?php if (!empty($summary)): ?>
                <div class="summary">
                    <h2>BOM Summary <?php echo !empty($searchTerm) ? 'for "' . htmlspecialchars($searchTerm) . '"' : ''; ?></h2>
                    <table>
                        <thead>
                            <tr>
                                <th>Parent BOM</th>
                                <th>Product Name</th>
                                <th>Components</th>
                                <th>Total Quantity</th>
                                <th>Total Buy Price</th>
                                <th>Last Updated</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($summary as $item): ?>
                                <tr>
                                    <td><?php echo htmlspecialchars($item['parent_bom']); ?></td>
                                    <td><?php echo htmlspecialchars($item['product_name'] ?? 'N/A'); ?></td>
                                    <td><?php echo $item['component_count']; ?></td>
                                    <td><?php echo number_format($item['total_qty'], 4); ?></td>
                                    <td>Rp. <?php echo number_format($item['total_cost'], 2); ?></td>
                                    <td><?php echo $item['last_calculated'] ?? 'N/A'; ?></td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            <?php endif; ?>

            <?php if (!empty($results)): ?>
                <h2>Component Details for "<?php echo htmlspecialchars($searchTerm); ?>"</h2>
                <table>
                    <thead>
                        <tr>
                            <th>ID</th>
                            <th>Parent BOM</th>
                            <th>Product Name</th>
                            <th>Child BOM</th>
                            <th>Phantom</th>
                            <th>Level</th>
                            <th>Quantity</th>
                            <th>Location</th>
                            <th>Buy Price</th>
                            <th>Last Update</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($results as $row): ?>
                            <tr>
                                <td><?php echo $row['id']; ?></td>
                                <td><strong><?php echo htmlspecialchars($row['parent_bom']); ?></strong></td>
                                <td><?php echo htmlspecialchars($row['product_name'] ?? 'N/A'); ?></td>
                                <td><?php echo htmlspecialchars($row['child_bom']); ?></td>
                                <td><?php echo htmlspecialchars($row['phantom_bom']); ?></td>
                                <td><?php echo $row['level']; ?></td>
                                <td><?php echo number_format($row['qty'], 4); ?></td>
                                <td><?php echo htmlspecialchars($row['location']); ?></td>
                                <td>Rp. <?php echo number_format($row['buy_price'], 2); ?></td>
                                <td><?php echo $row['last_update']; ?></td>
                            </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            <?php elseif ($searchPerformed && empty($summary) && empty($error)): ?>
                <div class="message info">No BOM data found</div>
            <?php endif; ?>
        <?php endif; ?>
    </div>
</body>

</html>
<?php
$conn->close();
?>