<?php
$current_page = $_GET['page'] ?? 'update_database'; 
require_once __DIR__ . '/navbar.php';
$pdo = require __DIR__ . '/../config/db.php';
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}
$supplierNames = $pdo->query('SELECT name FROM suppliers ORDER BY name')->fetchAll(PDO::FETCH_COLUMN);
$formatNames = $pdo->query('SELECT name FROM formats ORDER BY name')->fetchAll(PDO::FETCH_COLUMN);
$role = $_SESSION['role'] ?? null;

if ($role !== 'admin' && $role !== 'staff' && $role !== 'other') {
    header('Location: ../ooh_project/index.php?page=login');
    exit;
}

$sites = $pdo->query('SELECT * FROM sites ORDER BY id DESC')->fetchAll();

?>
<!DOCTYPE html>
<html lang="en">
    <head>
        <style>
        body, html, .container, .card, .modal-content, .table, .form-control, .btn, .modal-title, .fw-bold, .text-primary, .text-light {
            font-family: 'Arial Unicode MS', Arial, Helvetica, sans-serif !important;
        }
        </style>
        <meta charset="UTF-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>OOH Management</title>
        <link rel="icon" type="image/x-icon" href="../ooh_project/favicon.ico">
        <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
        <script>
        window.currentUserRole = '<?= $role ?>';
        </script>
    </head>
    <body style="background-color: #efefef;">
        <div class="container" style="max-width:95vw;">
            <h2 class="mt-2 mb-3 text-center text-dark">Market Overview</h2>
            <div class="row mb-2">
            <div class="col-12 col-md-10 mx-auto">
                <div class="card card-body p-3 shadow-sm" style="background:#f8f9fa; border-radius:1rem;">
                    <div class="row g-2 align-items-center">
                        <!-- If other user, show smart search button only 100% width -->
                        <?php if ($role === 'other'): ?>
                        <div class="col-12 col-md-12 d-flex align-items-center justify-content-md-start justify-content-center mb-2 mb-md-0">
                            <span class="fw-bold text-primary me-2" style="font-size:1.15em; letter-spacing:1px;">Smart Search</span>
                            <button type="button" id="clearFiltersBtn" class="btn btn-light btn-sm border ms-1" style="padding:0.2rem 0.5rem;line-height:1;" title="Clear all filters">
                                <img src="https://cdn-icons-png.flaticon.com/512/1828/1828778.png" alt="Clear" style="width:18px;height:18px;vertical-align:middle;">
                            </button>
                        </div>
                        <?php endif; ?>
                        <!-- If admnin or staff, show add format button -->
                        <?php if ($role === 'admin' || $role === 'staff'): ?>
                        <div class="col-6 col-md-6 d-flex align-items-center justify-content-md-start justify-content-center mb-2 mb-md-0">
                            <span class="fw-bold text-primary me-2" style="font-size:1.15em; letter-spacing:1px;">Smart Search</span>
                            <button type="button" id="clearFiltersBtn" class="btn btn-light btn-sm border ms-1" style="padding:0.2rem 0.5rem;line-height:1;" title="Clear all filters">
                                <img src="https://cdn-icons-png.flaticon.com/512/1828/1828778.png" alt="Clear" style="width:18px;height:18px;vertical-align:middle;">
                            </button>
                        </div>
                        <div class="col-6 col-md-6 d-flex justify-content-end mb-2">
                            <span class="fw-bold text-success me-2" style="font-size:1.15em; letter-spacing:1px;">Add New Site</span>
                            <button type="button" class="btn btn-success" style="background-color:#feffff;padding:0.2rem 0.5rem;line-height:1;border:none;" data-bs-toggle="modal" data-bs-target="#addFormatModal" title="Add New Format">
                                <img src="https://cdn-icons-png.flaticon.com/512/4315/4315609.png" alt="Add" style="width:24px;height:24px;filter:brightness(1.2) saturate(1.5);vertical-align:middle;">
                            </button>
                        </div>
                        <?php endif; ?>
                        <div class="col-6 col-md-2">
                        <input type="text" class="form-control form-control-sm" id="filterSupplier" placeholder="By Supplier">
                        </div>
                        <div class="col-6 col-md-2">
                        <input type="text" class="form-control form-control-sm" id="filterFormat" placeholder="By Format">
                        </div>
                        <div class="col-6 col-md-2">
                        <input type="text" class="form-control form-control-sm" id="filterAddress" placeholder="By Address">
                        </div>
                        <div class="col-6 col-md-2">
                        <input type="text" class="form-control form-control-sm" id="filterDistrict" placeholder="By District">
                        </div>
                        <div class="col-6 col-md-2">
                        <input type="text" class="form-control form-control-sm" id="filterMarket" placeholder="By Market">
                        </div>
                        <div class="col-6 col-md-2">
                        <input type="text" class="form-control form-control-sm" id="filterCurrentOwn" placeholder="By Brand Owning">
                        </div>
                    </div>
                </div>
            </div>
            </div>
            <div class="table-responsive">
                <table class="table table-bordered table-hover" style="font-size:0.88rem; margin-bottom:0;">
                    <thead class="table-dark" >
                        <tr>
                            <th>Supplier</th>
                            <th>Type</th>
                            <th>Format</th>
                            <th>Size WxH (meter)</th>
                            <th>Size WxH (pixel)</th>
                            <th>Image</th>
                            <th>Location</th>
                            <th>Score</th>
                            <?php if ($role === 'admin' || $role === 'staff'): ?>
                            <th>Selling Cost</th>
                            <th>Dis. (%)</th>
                            <th>Duration</th>
                            <th>Buy Duration</th>
                            <?php endif; ?>
                            <th>Brand Owning</th>
                            <th>Owner Image</th>
                            <?php if ($role === 'admin' || $role === 'staff'): ?>
                            <th>Actions</th>
                            <?php endif; ?>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($sites as $i => $site): ?>
                        <tr style="height:28px;" class="format-row<?= $i % 2 === 0 ? ' table-row-even' : ' table-row-odd' ?>">
                            <td class="align-middle" style="padding:0.25rem 0.4rem;"><?= htmlspecialchars($site['supplier']) ?></td>
                            <td class="align-middle" style="padding:0.25rem 0.4rem;"><?= htmlspecialchars($site['type']) ?></td>
                            <td class="align-middle" style="padding:0.25rem 0.4rem;"><?= htmlspecialchars($site['format']) ?></td>
                            <td class="align-middle" style="padding:0.25rem 0.4rem;">
                                <?php
                                    $m_w = $site['size_x_m'];
                                    $m_h = $site['size_y_m'];
                                    if ($m_w !== null && $m_w !== '' && $m_h !== null && $m_h !== '') {
                                        echo htmlspecialchars($m_w) . ' x ' . htmlspecialchars($m_h);
                                    } elseif ($m_w !== null && $m_w !== '') {
                                        echo htmlspecialchars($m_w);
                                    } elseif ($m_h !== null && $m_h !== '') {
                                        echo htmlspecialchars($m_h);
                                    }
                                ?>
                            </td>
                            <td class="align-middle" style="padding:0.25rem 0.4rem;">
                                <?php
                                    $px_w = $site['size_x_p'];
                                    $px_h = $site['size_y_y'];
                                    if ($px_w !== null && $px_w !== '' && $px_h !== null && $px_h !== '') {
                                        echo htmlspecialchars($px_w) . ' x ' . htmlspecialchars($px_h);
                                    } elseif ($px_w !== null && $px_w !== '') {
                                        echo htmlspecialchars($px_w);
                                    } elseif ($px_h !== null && $px_h !== '') {
                                        echo htmlspecialchars($px_h);
                                    }
                                ?>
                            </td>
                            <td class="align-middle" style="padding:0.25rem 0.4rem;">
                                <?php if ($site['image_base']): ?>
                                    <img src="/ooh_project/media/<?= htmlspecialchars($site['image_base']) ?>" style="max-width:40px;max-height:28px;"
                                        onmouseover="showImageTooltip(this, '/ooh_project/media/<?= htmlspecialchars($site['image_base']) ?>')" onmouseout="hideImageTooltip()">
                                <?php else: ?>
                                    <span style="font-size:0.85em;">No image</span>
                                <?php endif; ?>
                            </td>
                            <td class="align-middle" style="padding:0.25rem 0.4rem;">
                                <?php
                                    $loc = '';
                                    if ($site['street_no']) $loc .= 'No. ' . $site['street_no'] . ' ';
                                    if ($site['street']) $loc .= $site['street'] . ' ';
                                    if ($site['ward']) $loc .= 'Ward ' . $site['ward'] . ' ';
                                    if ($site['district']) $loc .= 'Dist. ' . $site['district'] . ' ';
                                    if ($site['market']) $loc .= $site['market'];
                                    $mapLink = $site['format_coordination'] ? $site['format_coordination'] : 'https://www.google.com/maps/search/' . urlencode($loc);
                                ?>
                                <a href="<?= htmlspecialchars($mapLink) ?>" target="_blank" style="font-size:0.95em;text-decoration:none;color:inherit;" title="Click to view map">
                                    <?= htmlspecialchars($loc) ?>
                                </a>
                            </td>
                            <td class="align-middle" style="padding:0.25rem 0.4rem;"><?= htmlspecialchars($site['evaluate_score']) ?></td>
                            <?php if ($role === 'admin' || $role === 'staff'): ?>
                            <td class="align-middle" style="padding:0.25rem 0.4rem;"><?= number_format($site['supplier_cost']) ?></td>
                            <td class="align-middle" style="padding:0.25rem 0.4rem;"><?= htmlspecialchars($site['discount']) ?></td>
                            <td class="align-middle" style="padding:0.25rem 0.4rem;"><?= htmlspecialchars($site['duration']) ?></td>
                            <td class="align-middle" style="padding:0.25rem 0.4rem;"><?= htmlspecialchars($site['duration_buy']) ?></td>
                            <?php endif; ?>
                            <td class="align-middle" style="padding:0.25rem 0.4rem;"><?= htmlspecialchars($site['current_own']) ?></td>
                            <td class="align-middle" style="padding:0.25rem 0.4rem;">
                                <?php if (!empty($site['image_comp'])): ?>
                                    <img src="/ooh_project/media/<?= htmlspecialchars($site['image_comp']) ?>" style="max-width:40px;max-height:28px;"
                                        onmouseover="showImageTooltip(this, '/ooh_project/media/<?= htmlspecialchars($site['image_comp']) ?>')" onmouseout="hideImageTooltip()">
                                <?php else: ?>
                                    <span style="font-size:0.85em;">No image</span>
                                <?php endif; ?>
                            </td>
                            <?php if ($role === 'admin' || $role === 'staff'): ?>
                            <td class="align-middle" style="padding:0.25rem 0.4rem;">
                                <div class="d-flex align-items-center gap-1">
                                    <button class="btn btn-sm bg-warning" style="color:#fff;padding:2px 6px 2px 6px;" onclick="openEditModal(<?= $site['id'] ?>)" title="Edit">
                                        <img src="https://cdn-icons-png.flaticon.com/512/1159/1159633.png" alt="Edit" style="width:18px;height:18px;filter:brightness(1.2);">
                                    </button>
                                    <?php if ($role === 'admin'): ?>
                                    <button class="btn btn-sm bg-danger" style="color:#fff;padding:2px 6px 2px 6px;" onclick="deleteFormat(<?= $site['id'] ?>)" title="Delete">
                                        <img src="https://cdn-icons-png.flaticon.com/512/1214/1214428.png" alt="Delete" style="width:18px;height:18px;">
                                    </button>
                                    <?php endif; ?>
                                </div>
                            </td>
                            <?php endif; ?>
                        </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
                <style>
                /* Add this style block (or merge with your existing style tag) */
                .table-row-even {
                    background-color: #fff !important;
                }
                .table-row-odd {
                    background-color: #f6f6f6 !important;
                }
                .table-hover > tbody > tr.table-row-even:hover,
                .table-hover > tbody > tr.table-row-odd:hover {
                    background-color: #e9ecef !important;
                }
                .table > :not(caption) > * > * {
                    vertical-align: middle;
                    text-align: center;
                }
                .table > tbody > tr {
                    height: 100px;
                }
                .table > tbody > tr > td {
                    max-width: 100%;           /* Adjust as needed for your layout */
                    overflow: hidden;
                    text-overflow: ellipsis;
                    white-space: nowrap;
                    vertical-align: middle;
                }
                #image-tooltip {
                    position: absolute;
                    z-index: 9999;
                    display: none;
                    border: 1px solid #ccc;
                    background: #fff;
                    padding: 2px;
                    box-shadow: 0 2px 8px rgba(0,0,0,0.15);
                }
                #addFormatModal .modal-dialog {
                    max-width: 65vw;
                    width: 60vw;
                }
                #editFormatModal .modal-dialog {
                    max-width: 65vw;
                    width: 60vw;
                }
                </style>
                <div id="image-tooltip"><img id="image-tooltip-img" src="" style="max-width:300px;max-height:200px;"></div>
                <script>
                    function showImageTooltip(img, src) {
                        var tooltip = document.getElementById('image-tooltip');
                        var tooltipImg = document.getElementById('image-tooltip-img');
                        tooltipImg.src = src;
                        tooltipImg.style.maxWidth = '600px';
                        tooltipImg.style.maxHeight = '400px';
                        tooltip.style.display = 'block';
                        var rect = img.getBoundingClientRect();
                        tooltip.style.left = (rect.right + window.scrollX + 10) + 'px';
                        tooltip.style.top = (rect.top + window.scrollY - 10) + 'px';
                    }
                    function hideImageTooltip() {
                        var tooltip = document.getElementById('image-tooltip');
                        var tooltipImg = document.getElementById('image-tooltip-img');
                        tooltip.style.display = 'none';
                        tooltipImg.style.maxWidth = '300px';image_base_upload
                        tooltipImg.style.maxHeight = '200px';
                    }
                    </script>
                    <!-- ////////////////////////// EDIT MODAL //////////////////////////////////////-->
                    <script>
                    window.supplierList = <?= json_encode($supplierNames) ?>;
                    window.formatList = <?= json_encode($formatNames) ?>;
                    </script>

                    <script>
                    window.openEditModal = function(id) {
                        fetch('/ooh_project/handler/format_crud.php', {
                            method: 'POST',
                            headers: { 'Accept': 'application/json' },
                            body: new URLSearchParams({ action: 'get', id: id })
                        })
                        .then(res => res.text())
                        .then(text => {
                            let data;
                            try {
                                data = JSON.parse(text);
                            } catch (e) {
                                console.error('Raw response:', text);
                                alert('Server error. Raw response logged to console.');
                                return;
                            }
                            if (!data.success || !data.format) {
                                alert('Could not fetch format data.');
                                return;
                            }
                            const f = data.format;
                            console.log('Format object:', f);
                            try {
                                // Build the form fields (same as add, but pre-filled)
                                let html = '';
                                function formatVND(val) {
                                    if (!val && val !== 0) return '';
                                    return Number(val).toLocaleString('en-US') + ' VND';
                                }
                                function formatDiscount(val) {
                                    if (!val && val !== 0) return '';
                                    return val + ' %';
                                }

                                html += `<div id="editFormMessage" class="alert d-none"></div>`;
                                html += `<form id="editFormatFormInner" style="font-size:0.92em;">`;
                                html += `<input type="hidden" name="format_id" id="edit_format_id" value="${f.id}">`;
                                html += `<input type="hidden" name="action" value="edit">`;
                                html += `<div class='row'>`;
                                // Left column: Format Information & Location
                                html += `<div class='col-12 col-md-6'>`;
                                html += `<h4 class='mt-4 mb-1 text-primary' style='font-size:1.2em;text-align:right;'>Format Information</h4>`;
                                html += `<div class='row mb-2 align-items-center'><div class='col-5 text-end pe-1' style='font-size:0.92em;'>Supplier</div><div class='col-7'><select name='supplier' class='form-control form-control-sm' required style='font-size:0.92em;'>`;
                                html += `<option value=''>Supplier Name</option>`;
                                window.supplierList.forEach(s => html += `<option value='${s}'${f.supplier===s?' selected':''}>${s}</option>`);
                                html += `</select></div></div>`;
                                html += `<div class='row mb-2 align-items-center'><div class='col-5 text-end pe-1' style='font-size:0.92em;'>Type</div><div class='col-7'><select name='type' class='form-control form-control-sm' required style='font-size:0.92em;'><option value=''>Type</option><option value='Indoor'${f.type==='Indoor'?' selected':''}>Indoor</option><option value='Outdoor'${f.type==='Outdoor'?' selected':''}>Outdoor</option></select></div></div>`;
                                html += `<div class='row mb-2 align-items-center'><div class='col-5 text-end pe-1' style='font-size:0.92em;'>Format</div><div class='col-7'><select name='format' class='form-control form-control-sm' required style='font-size:0.92em;'>`;
                                html += `<option value=''>Format</option>`;
                                window.formatList.forEach(s => html += `<option value='${s}'${f.format===s?' selected':''}>${s}</option>`);
                                html += `</select></div></div>`;
                                html += `<div class='row mb-2 align-items-center'>
                                    <div class='col-5 text-end pe-1' style='font-size:0.92em;'>Size (meter)</div>
                                    <div class='col-3'><input type='number' step='0.01' name='size_x_m' class='form-control form-control-sm' style='font-size:0.92em;' value='${f.size_x_m??''}' placeholder='Width'></div>
                                    <div class='col-1 text-center' style='font-size:0.92em;'>x</div>
                                    <div class='col-3'><input type='number' step='0.01' name='size_y_m' class='form-control form-control-sm' style='font-size:0.92em;' value='${f.size_y_m??''}' placeholder='Height'></div>
                                </div>`;
                                html += `<div class='row mb-2 align-items-center'>
                                    <div class='col-5 text-end pe-1' style='font-size:0.92em;'>Size (pixel)</div>
                                    <div class='col-3'><input type='number' name='size_x_p' class='form-control form-control-sm' style='font-size:0.92em;' value='${f.size_x_p??''}' placeholder='Width'></div>
                                    <div class='col-1 text-center' style='font-size:0.92em;'>x</div>
                                    <div class='col-3'><input type='number' name='size_y_y' class='form-control form-control-sm' style='font-size:0.92em;' value='${f.size_y_y??''}' placeholder='Height'></div>
                                </div>`;
                                html += `<div class='row mb-2 align-items-center'><div class='col-5 text-end pe-1' style='font-size:0.92em;'>Image</div><div class='col-7'>`;
                                html += `<input type='file' name='image_base_upload' class='form-control form-control-sm' accept='image/*' style='font-size:0.92em;'>`;
                                html += `<input type='hidden' name='image_base' id='image_base_hidden' value='${f.image_base??''}'>`;
                                if (f.image_base) {
                                    html += `<div class='mt-2' style='font-size:0.92em;'>Current image file: <span style='font-size:0.95em;'>${f.image_base}</span></div>`;
                                    html += `<img src='/ooh_project/media/${f.image_base}' style='max-width:100px;max-height:80px;margin-top:4px;'>`;
                                }
                                html += `</div></div>`;
                                html += `<div class='row mb-2 align-items-center'><div class='col-5 text-end pe-1' style='font-size:0.92em;'>Programmatic Adaptation</div><div class='col-7'><select name='pdooh' class='form-control form-control-sm' style='font-size:0.92em;'><option value=''>Programmatic Enable</option><option value='Yes'${f.pdooh==='Yes'?' selected':''}>Yes</option><option value='No'${f.pdooh==='No'?' selected':''}>No</option></select></div></div>`;
                                html += `<h4 class='mt-4 mb-1 text-primary' style='font-size:1.2em;text-align:right;'>Format Location</h4>`;
                                html += `<div class='row mb-2 align-items-center'><div class='col-5 text-end pe-1' style='font-size:0.92em;'>Market</div><div class='col-7'><input type='text' placeholder="eg. Ho Chi Minh/ Ha Noi" name='market' class='form-control form-control-sm' style='font-size:0.92em;' value='${f.market??''}'></div></div>`;
                                html += `<div class='row mb-2 align-items-center'><div class='col-5 text-end pe-1' style='font-size:0.92em;'>District</div><div class='col-7'><input type='text' placeholder="eg. Tan Binh, 3, 1" name='district' class='form-control form-control-sm' style='font-size:0.92em;' value='${f.district??''}'></div></div>`;
                                html += `<div class='row mb-2 align-items-center'><div class='col-5 text-end pe-1' style='font-size:0.92em;'>Ward</div><div class='col-7'><input type='text' placeholder="eg. Ben Nghe" name='ward' class='form-control form-control-sm' style='font-size:0.92em;' value='${f.ward??''}'></div></div>`;
                                html += `<div class='row mb-2 align-items-center'><div class='col-5 text-end pe-1' style='font-size:0.92em;'>Street Name</div><div class='col-7'><input type='text' placeholder="eg. Vo Thi Sau" name='street' class='form-control form-control-sm' style='font-size:0.92em;' value='${f.street??''}'></div></div>`;
                                html += `<div class='row mb-2 align-items-center'><div class='col-5 text-end pe-1' style='font-size:0.92em;'>Street Number</div><div class='col-7'><input type='text' placeholder="eg. 156" name='street_no' class='form-control form-control-sm' style='font-size:0.92em;' value='${f.street_no??''}'></div></div>`;
                                html += `<div class='row mb-2 align-items-center'><div class='col-5 text-end pe-1' style='font-size:0.92em;'>Format Coordination</div><div class='col-7'><input type='text' name='format_coordination' class='form-control form-control-sm' style='font-size:0.92em;' value='${f.format_coordination??''}'></div></div>`;
                                html += `<div class='row mb-2 align-items-center'><div class='col-5 text-end pe-1' style='font-size:0.92em;'>Availability</div><div class='col-7'><input type='date' placeholder="Available Date" name='availability' class='form-control form-control-sm' style='font-size:0.92em;' value='${f.availability??''}'></div></div>`;
                                html += `<div class='row mb-2 align-items-center'><div class='col-5 text-end pe-1 text-danger' style='font-size:0.92em;'>Brand Owning</div><div class='col-7'><input type='text' placeholder="On-Site Competitor's Name" name='current_own' class='form-control form-control-sm' style='font-size:0.92em;' value='${f.current_own??''}'></div></div>`;
                                html += `<div class='row mb-2 align-items-center'><div class='col-5 text-end pe-1 text-danger' style='font-size:0.92em;'>Competitor Image</div><div class='col-7'>`;
                                html += `<input type='file' name='image_comp_upload' class='form-control form-control-sm' accept='image/*' style='font-size:0.92em;'>`;
                                html += `<input type='hidden' name='image_comp' id='image_comp_hidden' value='${f.image_comp??''}'>`;
                                if (f.image_comp) {
                                    html += `<div class='mt-2' style='font-size:0.92em;'>Current file: <span style='font-size:0.95em;'>${f.image_comp}</span></div>`;
                                    html += `<img src='/ooh_project/media/${f.image_comp}' style='max-width:100px;max-height:80px;margin-top:4px;'>`;
                                }
                                html += `</div>`;
                                html += `</div>`;
                                html += `</div>`;
                                // Right column: Selling, Planning, Performance
                                html += `<div class='col-12 col-md-6'>`;
                                html += `<h4 class='mt-4 mb-1 text-primary' style='font-size:1.2em;text-align:right;'>Selling Information</h4>`;
                                html += `<div class='row mb-2 align-items-center'><div class='col-5 text-end pe-1' style='font-size:0.92em;'>Supplier Cost (VND)</div><div class='col-7'><input type='text' placeholder="Ratecard Cost" name='supplier_cost' class='form-control form-control-sm' style='font-size:0.92em;' value='${formatVND(f.supplier_cost)}'></div></div>`;
                                html += `<div class='row mb-2 align-items-center'><div class='col-5 text-end pe-1' style='font-size:0.92em;'>Duration by Cost</div><div class='col-7'><input type='text' placeholder="eg. 6 months" name='duration' class='form-control form-control-sm' style='font-size:0.92em;' value='${f.duration??''}'></div></div>`;
                                html += `<div class='row mb-2 align-items-center'><div class='col-5 text-end pe-1' style='font-size:0.92em;'>Duration Buying</div><div class='col-7'><select name='duration_buy' class='form-control form-control-sm' style='font-size:0.92em;'><option value=''>Duration Buying</option><option value='1 hour/day'${f.duration_buy==='1 hour/day'?' selected':''}>1 hour/day</option><option value='2 hours/day'${f.duration_buy==='2 hours/day'?' selected':''}>2 hours/day</option><option value='3 hours/day'${f.duration_buy==='3 hours/day'?' selected':''}>3 hours/day</option></select></div></div>`;
                                html += `<div class='row mb-2 align-items-center'><div class='col-5 text-end pe-1' style='font-size:0.92em;'>Discount (%)</div><div class='col-7'><input type='text' placeholder="Discount by Duration" name='discount' class='form-control form-control-sm' style='font-size:0.92em;' value='${formatDiscount(f.discount)}'></div></div>`;
                                html += `<h4 class='mt-4 mb-1 text-primary' style='font-size:1.2em;text-align:right;'>Planning Information</h4>`;
                                html += `<div class='row mb-2 align-items-center'><div class='col-5 text-end pe-1' style='font-size:0.92em;'>Plan Cost (VND)</div><div class='col-7'><input type='text' name='plan_cost' class='form-control form-control-sm' style='font-size:0.92em;' value='${formatVND(f.plan_cost)}'></div></div>`;
                                html += `<div class='row mb-2 align-items-center'><div class='col-5 text-end pe-1' style='font-size:0.92em;'>Buying Type</div><div class='col-7'><select name='buy_type' class='form-control form-control-sm' style='font-size:0.92em;'><option value=''>Buying Type</option><option value='Direct Buy'${f.buy_type==='Direct Buy'?' selected':''}>Direct Buy</option><option value='Added Value'${f.buy_type==='Added Value'?' selected':''}>Added Value</option></select></div></div>`;
                                html += `<div class='row mb-2 align-items-center'><div class='col-5 text-end pe-1' style='font-size:0.92em;'>Site Score</div><div class='col-7'><input type='number' step='0.1' name='evaluate_score' class='form-control form-control-sm' style='font-size:0.92em;' value='${f.evaluate_score??''}'></div></div>`;
                                if (window.currentUserRole === 'admin') {
                                html += `<h4 class='mt-4 mb-1 text-primary' style='font-size:1.2em;text-align:right;'>Performance</h4>`;
                                html += `<div class='row mb-2 align-items-center'><div class='col-5 text-end pe-1' style='font-size:0.92em;'>Buy Cost (VND)</div><div class='col-7'><input type='text' name='buy_cost' class='form-control form-control-sm' style='font-size:0.92em;' value='${formatVND(f.buy_cost)}'></div></div>`;
                                html += `<div class='row mb-2 align-items-center'><div class='col-5 text-end pe-1' style='font-size:0.92em;'>P&L (%)</div><div class='col-7'><input type='text' name='profit_lost' class='form-control form-control-sm' style='font-size:0.92em;' value='${f.profit_lost??''}' readonly disabled></div></div>`;
                                }
                                html += `</div>`;
                                html += `</div>`;
                                html += `<div class='row'><div class='col-12 text-center mt-3'><button type='submit' class='btn btn-warning'>Update Site</button></div></div>`;
                                
                                html += `</form>`;
                                document.getElementById('editFormatForm').innerHTML = html;
                                var editModal = new bootstrap.Modal(document.getElementById('editFormatModal'));
                                editModal.show();
                                // Attach image upload listener for Edit modal
                                var editFormatBtn = document.querySelector('#editFormatModal button[type="submit"]');
                                var imageBaseUploading = false;
                                function attachImageUploadListener(inputName, hiddenName, imageType) {
                                    var uploadInput = document.querySelector('#editFormatModal input[name="' + inputName + '"]');
                                    if (uploadInput) {
                                        uploadInput.addEventListener('change', function(e) {
                                            var file = e.target.files[0];
                                            if (!file) return;
                                            var formData = new FormData();
                                            var formatIdInput = document.getElementById('edit_format_id');
                                            if (!formatIdInput || !formatIdInput.value || isNaN(formatIdInput.value) || parseInt(formatIdInput.value) <= 0) {
                                                alert('Format ID missing or invalid. Cannot upload image.');
                                                return;
                                            }
                                            formData.append('image', file);
                                            formData.append('format_id', formatIdInput.value);
                                            formData.append('image_type', imageType);
                                            imageBaseUploading = true;
                                            if (editFormatBtn) editFormatBtn.disabled = true;
                                            fetch('/ooh_project/handler/upload_image.php', { method: 'POST', body: formData })
                                                .then(function(response) { return response.text(); })
                                                .then(function(result) {
                                                    if (result.startsWith('success:')) {
                                                        document.getElementById(hiddenName).value = file.name;
                                                        // Show a success message
                                                        var msgEl = document.getElementById('editFormMessage');
                                                        if (msgEl) {
                                                            msgEl.className = 'alert alert-success';
                                                            msgEl.textContent = 'Image uploaded successfully!';
                                                            msgEl.classList.remove('d-none');
                                                        }
                                                    } else {
                                                        alert(result);
                                                    }
                                                    imageBaseUploading = false;
                                                    if (editFormatBtn) editFormatBtn.disabled = false;
                                                });
                                        });
                                    }
                                }
                                attachImageUploadListener('image_base_upload', 'image_base_hidden', 'image_base');
                                attachImageUploadListener('image_comp_upload', 'image_comp_hidden', 'image_comp');
                                // Attach submit event to the dynamically created form
                                document.getElementById('editFormatFormInner').addEventListener('submit', function(e) {
                                    if (imageBaseUploading) {
                                        var msgEl = document.getElementById('editFormMessage');
                                        msgEl.className = 'alert alert-warning';
                                        msgEl.textContent = 'Please wait for image upload to finish.';
                                        msgEl.classList.remove('d-none');
                                        e.preventDefault();
                                        return;
                                    }
                                    e.preventDefault();
                                    var form = e.target;

                                    // --- CLEAN FORMATTED FIELDS BEFORE SUBMIT ---
                                    // Remove VND, commas, and % from relevant fields
                                    ['supplier_cost', 'plan_cost', 'buy_cost', 'discount'].forEach(function(name) {
                                        var input = form.querySelector(`[name="${name}"]`);
                                        if (input) {
                                            let val = input.value.replace(/[^0-9.\-]/g, ''); // keep numbers, dot, minus
                                            input.value = val;
                                        }
                                    });

                                    var data = new FormData(form);
                                    fetch('/ooh_project/handler/format_crud.php', {
                                        method: 'POST',
                                        body: data
                                    })
                                    .then(res => res.json())
                                    .then(resp => {
                                        var msgEl = document.getElementById('editFormMessage');
                                        if (resp.success) {
                                            msgEl.className = 'alert alert-success';
                                            msgEl.textContent = 'Format updated successfully!';
                                            msgEl.classList.remove('d-none');
                                            setTimeout(() => { location.reload(); }, 1200);
                                        } else {
                                            msgEl.className = 'alert alert-danger';
                                            msgEl.textContent = resp.error || 'Failed to update format.';
                                            msgEl.classList.remove('d-none');
                                        }
                                    })
                                    .catch(() => {
                                        var msgEl = document.getElementById('editFormMessage');
                                        msgEl.className = 'alert alert-danger';
                                        msgEl.textContent = 'Server error.';
                                        msgEl.classList.remove('d-none');
                                    });
                                });
                            } catch (err) {
                                console.error('Error building edit form:', err);
                                alert('Error building edit form. See console for details.');
                            showMessageModal('Error building edit form. See console for details.', 'danger');
                            }
                        })
                        .catch(err => {
                            console.error('Fetch error:', err);
                            alert('Server error.');
                            showMessageModal('Server error.', 'danger');
                        });
                    }

                    window.deleteFormat = function(id) {
                        showConfirmModal('Are you sure you want to delete this format?', function(confirmed) {
                            if (!confirmed) return;
                            fetch('/ooh_project/handler/format_crud.php', {
                                method: 'POST',
                                headers: { 'Accept': 'application/json' },
                                body: new URLSearchParams({ action: 'delete', id: id })
                            })
                            .then(res => res.json())
                            .then(resp => {
                                if (resp.success) {
                                    showMessageModal('Deleted successfully!', 'success', function() { location.reload(); });
                                } else {
                                    showMessageModal(resp.error || 'Delete failed.', 'danger');
                                }
                            })
                            .catch(() => showMessageModal('Server error.', 'danger'));
                        });
                    }
                            showMessageModal('Server error. Raw response logged to console.', 'danger');
                            showMessageModal('Could not fetch format data.', 'danger');
                                            showMessageModal('Format ID missing or invalid. Cannot upload image.', 'danger');
                                                    showMessageModal(result, 'danger');
                </script>
                <script src="/ooh_project/modules/common.js"></script>

                <!-- Add Site Modal -->
                <div class="modal fade" id="addFormatModal" tabindex="-1" aria-labelledby="addFormatModalLabel" aria-hidden="true">
                <div class="modal-dialog">
                    <div class="modal-content" style="max-width:65vw; max-height:95vh;">
                    <div class="modal-header" style="padding:0.5rem 1rem; background: linear-gradient(90deg, #007bff 60%, #0056b3 100%);">
                        <h5 class="modal-title text-white fw-bold" id="addFormatModalLabel" style="text-shadow: 1px 1px 4px #333; letter-spacing: 1px;">Add Site</h5>
                        <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                    </div>
                    <div class="modal-body" style="max-width:60vw; height:90vh; overflow-x:hidden; overflow-y:auto; padding:0.5rem 1rem;">
                    <?php $addMode = true; include __DIR__ . '/update_database_form.php'; ?>
                    </div>
                    </div>
                </div>
                </div>

                <!-- Edit Site Modal -->
                <div class="modal fade" id="editFormatModal" tabindex="-1" aria-labelledby="editFormatModalLabel" aria-hidden="true">
                <div class="modal-dialog">
                    <div class="modal-content" style="max-width:65vw; max-height:95vh;">
                    <div class="modal-header" style="padding:0.5rem 1rem; background: linear-gradient(90deg, #007bff 60%, #0056b3 100%);">
                        <h5 class="modal-title text-white fw-bold" id="editFormatModalLabel" style="text-shadow: 1px 1px 4px #333; letter-spacing: 1px;">Edit Site</h5>
                        <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                    </div>
                    <div class="modal-body" style="max-width:60vw; height:90vh; overflow-x:hidden; overflow-y:auto; padding:0.5rem 1rem;">
                        <div id="editFormatForm"></div>
                    </div>
                    </div>
                </div>
                </div>
                <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
            </div>
            <!-- Active Message Modal -->
            <?php include __DIR__ . '/common.php'; ?>
            <script src="/ooh_project/modules/common.js"></script>
            <script>
            window.supplierList = <?= json_encode($supplierNames) ?>;
            window.formatList = <?= json_encode($formatNames) ?>;

            // --- FIXED FILTER FUNCTIONALITY ---
            function filterTable() {
                var supplier = document.getElementById('filterSupplier').value.trim().toLowerCase();
                var format = document.getElementById('filterFormat').value.trim().toLowerCase();
                var address = document.getElementById('filterAddress').value.trim().toLowerCase();
                var district = document.getElementById('filterDistrict').value.trim().toLowerCase();
                var market = document.getElementById('filterMarket').value.trim().toLowerCase();
                var brandOwn = document.getElementById('filterCurrentOwn').value.trim().toLowerCase();

                var rows = document.querySelectorAll('.format-row');
                rows.forEach(function(row) {
                    var cells = row.children;
                    var supplierVal = (cells[0]?.textContent || '').toLowerCase();
                    var formatVal = (cells[2]?.textContent || '').toLowerCase();
                    var addressVal = (cells[6]?.textContent || '').toLowerCase();
                    var brandOwnVal = (cells[12]?.textContent || '').toLowerCase();

                    // Extract district and market from addressVal
                    var districtVal = '';
                    var marketVal = '';
                    var districtMatch = addressVal.match(/dist\.?\s*([^\s]+)/i);
                    if (districtMatch && districtMatch[1]) districtVal = districtMatch[1].toLowerCase();

                    // Market is usually at the end (city/province)
                    var marketMatch = addressVal.match(/(?:dist\.[^ ]+\s*)?(.*)$/i);
                    if (marketMatch && marketMatch[1]) marketVal = marketMatch[1].trim().toLowerCase();

                    var show = true;
                    if (supplier && !supplierVal.includes(supplier)) show = false;
                    if (format && !formatVal.includes(format)) show = false;
                    if (address && !addressVal.includes(address)) show = false;
                    if (district && !districtVal.includes(district)) show = false;
                    if (market && !marketVal.includes(market)) show = false;
                    if (brandOwn && !brandOwnVal.includes(brandOwn)) show = false;
                    row.style.display = show ? '' : 'none';
                });
            }

            // Attach filter events robustly
            ['filterSupplier','filterFormat','filterAddress','filterDistrict','filterMarket','filterCurrentOwn'].forEach(function(id) {
                var el = document.getElementById(id);
                if (el) el.addEventListener('input', filterTable);
            });

            // Clear all filter inputs
            var clearBtn = document.getElementById('clearFiltersBtn');
            if (clearBtn) {
                clearBtn.onclick = function() {
                    ['filterSupplier','filterFormat','filterAddress','filterDistrict','filterMarket','filterCurrentOwn'].forEach(function(id) {
                        var el = document.getElementById(id);
                        if (el) el.value = '';
                    });
                    filterTable();
                };
            }
            </script>
        </div>
    </body>
</html>

<!-- Place this script before </body> to enable image upload for both Add and Edit modals -->
<script>
// Robust image upload event for Add & Edit Modal using event delegation
document.addEventListener('change', function(e) {
    var input = e.target;
    // For Add Modal: Format Image
    if (input && input.name === 'image_base_upload' && input.closest('#addFormatModal')) {
        var file = input.files[0];
        if (!file) return;
        var formData = new FormData();
        formData.append('image', file);
        formData.append('image_type', 'image_base');
        fetch('/ooh_project/handler/upload_image.php', {
            method: 'POST',
            body: formData
        })
        .then(response => response.text())
        .then(result => {
            if (result.startsWith('success:')) {
                var hidden = document.getElementById('image_base_hidden');
                if (hidden) hidden.value = file.name;
                var msgEl = document.getElementById('formMessage');
                if (msgEl) {
                    msgEl.className = 'alert alert-success';
                    msgEl.textContent = 'Image uploaded successfully!';
                    msgEl.classList.remove('d-none');
                }
            } else {
                alert(result);
            }
        });
    }
    // For Add Modal: Competitor Image
    if (input && input.name === 'image_comp_upload' && input.closest('#addFormatModal')) {
        var file = input.files[0];
        if (!file) return;
        var formData = new FormData();
        formData.append('image', file);
        formData.append('image_type', 'image_comp');
        fetch('/ooh_project/handler/upload_image.php', {
            method: 'POST',
            body: formData
        })
        .then(response => response.text())
        .then(result => {
            if (result.startsWith('success:')) {
                var hidden = document.getElementById('image_comp_hidden');
                if (hidden) hidden.value = file.name;
                var msgEl = document.getElementById('formMessage');
                if (msgEl) {
                    msgEl.className = 'alert alert-success';
                    msgEl.textContent = 'Image uploaded successfully!';
                    msgEl.classList.remove('d-none');
                }
            } else {
                alert(result);
            }
        });
    }
    // For Edit Modal: Format Image
    if (input && input.name === 'image_base_upload' && input.closest('#editFormatModal')) {
        var file = input.files[0];
        if (!file) return;
        var formData = new FormData();
        var formatIdInput = document.getElementById('edit_format_id');
        if (!formatIdInput || !formatIdInput.value || isNaN(formatIdInput.value) || parseInt(formatIdInput.value) <= 0) {
            alert('Format ID missing or invalid. Cannot upload image.');
            return;
        }
        formData.append('image', file);
        formData.append('format_id', formatIdInput.value);
        formData.append('image_type', 'image_base');
        fetch('/ooh_project/handler/upload_image.php', {
            method: 'POST',
            body: formData
        })
        .then(response => response.text())
        .then(function(result) {
            if (result.startsWith('success:')) {
                var msgEl = document.getElementById('editFormMessage');
                var matches = result.match(/^success:(?:tmp\/)?(.+)$/);
                if (matches && matches[1]) {
                    // Use the correct hidden input ID directly
                    var hidden = document.getElementById('image_base_hidden');
                    if (hidden) hidden.value = matches[1].trim();
                }
                if (msgEl) {
                    msgEl.className = 'alert alert-success';
                    msgEl.textContent = 'Image uploaded successfully!';
                    msgEl.classList.remove('d-none');
                }
            } else {
                alert(result);
            }
            imageBaseUploading = false;
            if (editFormatBtn) editFormatBtn.disabled = false;
        });
    }
    // For Edit Modal: Competitor Image
    if (input && input.name === 'image_comp_upload' && input.closest('#editFormatModal')) {
        var file = input.files[0];
        if (!file) return;
        var formData = new FormData();
        var formatIdInput = document.getElementById('edit_format_id');
        if (!formatIdInput || !formatIdInput.value || isNaN(formatIdInput.value) || parseInt(formatIdInput.value) <= 0) {
            alert('Format ID missing or invalid. Cannot upload image.');
            return;
        }
        formData.append('image', file);
        formData.append('format_id', formatIdInput.value);
        formData.append('image_type', 'image_comp');
        fetch('/ooh_project/handler/upload_image.php', {
            method: 'POST',
            body: formData
        })
        .then(response => response.text())
        .then(result => {
            if (result.startsWith('success:')) {
                var hidden = document.getElementById('image_comp_hidden');
                if (hidden) hidden.value = file.name;
                var msgEl = document.getElementById('editFormMessage');
                if (msgEl) {
                    msgEl.className = 'alert alert-success';
                    msgEl.textContent = 'Image uploaded successfully!';
                    msgEl.classList.remove('d-none');
                }
            } else {
                alert(result);
            }
        });
    }
});
</script>
