<?php

ini_set('display_errors', 1);
error_reporting(E_ALL);

include 'db_connect.php';

// ─────────────────────────────────────────────────────────────
// QUERY — mirrors original reference query exactly
// ss.id NOT IN applied only to displayed stores, not to user filter
// ─────────────────────────────────────────────────────────────

$sql = "
SELECT
    su.id                                        AS user_id,
    CONVERT(su.name      USING utf8mb4)
        COLLATE utf8mb4_general_ci               AS user_name,
    CONVERT(su.phone     USING utf8mb4)
        COLLATE utf8mb4_general_ci               AS user_mobile,
    CONVERT(su.email     USING utf8mb4)
        COLLATE utf8mb4_general_ci               AS user_email,
    su.emp_code,

    CONVERT(sr.name      USING utf8mb4)
        COLLATE utf8mb4_general_ci               AS role_name,

    CONVERT(eu.name      USING utf8mb4)
        COLLATE utf8mb4_general_ci               AS emp_name,
    eu.mobile1                                   AS emp_mobile,
    eu.level                                     AS emp_level,
    eu.designation                               AS emp_role,

    eu.reporting_to                              AS reporting_code,
    CONVERT(rm.name      USING utf8mb4)
        COLLATE utf8mb4_general_ci               AS reporting_name,
    rm.mobile1                                   AS reporting_mobile,

    (SELECT COUNT(*) FROM showroom_store WHERE deleted_at IS NULL)
                                                 AS total_stores,

    COUNT(DISTINCT CASE WHEN ss.id NOT IN (300,19,11) THEN ss.id END)
                                                 AS connected_stores,

    GROUP_CONCAT(
        DISTINCT CASE WHEN ss.id NOT IN (300,19,11) THEN
            CONVERT(ss.title USING utf8mb4) COLLATE utf8mb4_general_ci
        END
        ORDER BY ss.id ASC
        SEPARATOR '|'
    )                                            AS connected_store_names

FROM showroom_users su

JOIN showroom_roles sr
    ON sr.source_table_id = su.role_id

JOIN showroom_store_user ssu
    ON ssu.user_id = su.source_table_id

JOIN showroom_store ss
    ON ss.source_table_id = ssu.store_id

LEFT JOIN emp_users eu
    ON eu.emp_code = su.emp_code

LEFT JOIN emp_users rm
    ON rm.emp_code = eu.reporting_to

WHERE
    su.deleted_at IS NULL
    AND sr.source_table_id IN (1,2,3,4,7,9,10,11,19,20,21,22,23,24)
    AND su.source_table_id NOT IN (23,24,1,778,812,814,757,1282,1283,1284,450,753,754,756,813,815)
    AND ss.dealertype IS NULL
    AND ss.website_id > 0
    AND NOT EXISTS (
        SELECT 1
        FROM showroom_store_user su2
        JOIN showroom_store s2 ON s2.source_table_id = su2.store_id
        WHERE su2.user_id = su.source_table_id
          AND s2.dealertype IN ('Loyalty Store','Dealer','External Store')
    )

GROUP BY
    su.id, su.emp_code, su.name, su.phone,
    sr.name,
    eu.name, eu.mobile1, eu.level, eu.designation,
    eu.reporting_to, rm.name, rm.mobile1

UNION

SELECT
    NULL                                         AS user_id,
    eu2.name                                     AS user_name,
    eu2.mobile1                                  AS user_mobile,
    eu2.email1                                   AS user_email,
    eu2.emp_code,

    NULL                                         AS role_name,

    eu2.name                                     AS emp_name,
    eu2.mobile1                                  AS emp_mobile,
    eu2.level                                    AS emp_level,
    eu2.designation                              AS emp_role,

    eu2.reporting_to                             AS reporting_code,
    rm2.name                                     AS reporting_name,
    rm2.mobile1                                  AS reporting_mobile,

    (SELECT COUNT(*) FROM showroom_store WHERE deleted_at IS NULL)
                                                 AS total_stores,

    COUNT(DISTINCT ss2.id)                       AS connected_stores,

    GROUP_CONCAT(
        DISTINCT ss2.title
        ORDER BY ss2.id ASC
        SEPARATOR '|'
    )                                            AS connected_store_names

FROM emp_users eu2

JOIN dealer_employee_website_mapping dwm
    ON dwm.emp_code = eu2.emp_code

JOIN showroom_store ss2
    ON ss2.website_id = dwm.website_id
    AND ss2.dealertype IS NULL
    AND ss2.website_id  > 0
    AND ss2.deleted_at IS NULL

LEFT JOIN emp_users rm2
    ON rm2.emp_code = eu2.reporting_to

WHERE eu2.status = 1
    AND eu2.emp_code NOT IN (
        SELECT DISTINCT su3.emp_code
        FROM showroom_users su3
        JOIN showroom_store_user ssu3 ON ssu3.user_id = su3.source_table_id
        WHERE su3.emp_code IS NOT NULL
          AND su3.deleted_at IS NULL
    )

GROUP BY
    eu2.emp_code, eu2.name, eu2.mobile1, eu2.email1,
    eu2.level, eu2.designation, eu2.reporting_to,
    rm2.name, rm2.mobile1

ORDER BY
    emp_level ASC,
    user_name ASC
";

$res = $conn->query($sql);

if (!$res) {
    die("Query Failed: " . $conn->error);
}

$rawRows = [];
while ($row = $res->fetch_assoc()) {
    $rawRows[] = $row;
}

// Deduplicate by mobile number — keep first occurrence (showroom_users takes priority)
$rows = [];
$seenMobiles = [];
foreach ($rawRows as $row) {
    $mobile = trim($row['user_mobile'] ?? $row['emp_mobile'] ?? '');
    // Clean mobile to digits only for comparison
    $cleanMobile = preg_replace('/[^0-9]/', '', $mobile);
    $cleanMobile = $cleanMobile ? substr($cleanMobile, -10) : '';

    if ($cleanMobile && isset($seenMobiles[$cleanMobile])) {
        continue; // skip duplicate
    }

    if ($cleanMobile) {
        $seenMobiles[$cleanMobile] = true;
    }

    $rows[] = $row;
}

// Unique values for dropdowns
$allRoles  = array_values(array_unique(array_filter(array_column($rows, 'role_name'))));
sort($allRoles);

// Collect all store names from pipe-separated field
$allStores = [];
foreach ($rows as $r) {
    if (!empty($r['connected_store_names'])) {
        foreach (explode('|', $r['connected_store_names']) as $s) {
            $s = trim($s);
            if ($s && !in_array($s, $allStores)) {
                $allStores[] = $s;
            }
        }
    }
}
sort($allStores);

?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Showroom Users</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
<link href="https://cdn.datatables.net/1.13.8/css/dataTables.bootstrap5.min.css" rel="stylesheet">
<style>
body { background:#f5f7fb; }
.page-title { font-size:26px; font-weight:700; }
.table td { vertical-align:middle; font-size:13px; }
.table th { font-size:13px; white-space:nowrap; }
.badge-level { font-size:11px; }
.store-box { max-width:280px; }
.store-badge {
    display:inline-block;
    background:#e8f0fe;
    color:#1a3a7a;
    border:1px solid #c5d5f5;
    border-radius:4px;
    padding:2px 7px;
    font-size:11px;
    margin:2px 2px 2px 0;
    white-space:nowrap;
}
.filter-bar {
    background:#fff;
    border:1px solid #dee2e6;
    border-radius:8px;
    padding:12px 16px;
    margin-bottom:16px;
}
</style>
</head>
<body>

<div class="container-fluid py-4">
<div class="card shadow-sm border-0">
<div class="card-body">

    <div class="d-flex justify-content-between align-items-center mb-3">
        <div>
            <div class="page-title">Showroom Users</div>
            <div class="text-muted">Total: <strong><?= count($rows) ?></strong> users</div>
        </div>
    </div>

    <!-- ── Dropdowns ───────────────────────────────────────── -->
    <div class="filter-bar">
    <div class="row g-2 align-items-end">

        <div class="col-md-3 col-sm-6">
            <label class="form-label fw-semibold mb-1" style="font-size:12px;">ROLE</label>
            <select id="filterRole" class="form-select form-select-sm">
                <option value="">All Roles</option>
                <?php foreach ($allRoles as $r): ?>
                    <option value="<?= htmlspecialchars($r) ?>"><?= htmlspecialchars($r) ?></option>
                <?php endforeach; ?>
            </select>
        </div>

        <div class="col-md-4 col-sm-6">
            <label class="form-label fw-semibold mb-1" style="font-size:12px;">STORE</label>
            <select id="filterStore" class="form-select form-select-sm">
                <option value="">All Stores</option>
                <?php foreach ($allStores as $s): ?>
                    <option value="<?= htmlspecialchars($s) ?>"><?= htmlspecialchars($s) ?></option>
                <?php endforeach; ?>
            </select>
        </div>

        <div class="col-md-2 col-sm-6">
            <button class="btn btn-sm btn-secondary w-100" id="btnReset">Reset</button>
        </div>

    </div>
    </div>

    <!-- ── Table ───────────────────────────────────────────── -->
    <div class="table-responsive">
    <table id="usersTable" class="table table-bordered table-hover align-middle">
    <thead class="table-dark">
    <tr>
        <th>User ID</th>
        <th>Name</th>
        <th>Mobile</th>
        <th>Email</th>
        <th>Emp Code</th>
        <th>Role</th>
        <th>Level</th>
        <th>Designation</th>
        <th>Reporting Code</th>
        <th>Reporting Name</th>
        <th>Reporting Mobile</th>
        <th>Total Stores</th>
        <th>Connected Stores</th>
        <th>Connected Store Names</th>
    </tr>
    </thead>
    <tbody>

    <?php foreach ($rows as $row):

        $empCode   = $row['emp_code']        ?? '';
        $empMobile = $row['emp_mobile']       ?? $row['user_mobile'] ?? '';
        $empName   = $row['emp_name']         ?? $row['user_name']   ?? '';
        $level     = $row['emp_level']        ?? '';
        $desig     = $row['emp_role']         ?? '';
        $role      = $row['role_name']        ?? '';
        $repCode   = $row['reporting_code']   ?? '';
        $repName   = $row['reporting_name']   ?? '';
        $repMobile = $row['reporting_mobile'] ?? '';

        // Store names as array for data attribute filtering
        $storeNames = [];
        if (!empty($row['connected_store_names'])) {
            $storeNames = array_filter(array_map('trim', explode('|', $row['connected_store_names'])));
        }

    ?>
    <tr
        data-role="<?= htmlspecialchars($role ?? '') ?>"
        data-stores="<?= htmlspecialchars(implode('|||', $storeNames)) ?>"
    >

        <td><?= htmlspecialchars($row['user_id'] ?? '—') ?></td>

        <td>
             <a href="https://dashboard.kajariaceramics.com/kajaria_app/app_report.php?source1=<?= enc($empMobile) ?>"
               class="fw-bold text-decoration-none" target="_blank">
                <?= htmlspecialchars($empName ?? '') ?>
            </a>
        </td>

        <td>
            <?php if ($empMobile): ?>
                <a href="https://dashboard.kajariaceramics.com/kajaria_app/app_report.php?source1=<?= enc($empMobile) ?>"
                   class="text-decoration-none" target="_blank">
                    <?= htmlspecialchars($empMobile ?? '') ?>
                </a>
            <?php else: ?>
                <span class="text-muted">—</span>
            <?php endif; ?>
        </td>

        <td><small><?= htmlspecialchars($row['user_email'] ?? '') ?></small></td>

        <td>
            <?php if ($empCode): ?>
                <a href="app_report.php?source1=<?= enc($empCode) ?>"
                   class="text-decoration-none" target="_blank">
                    <?= htmlspecialchars($empCode ?? '') ?>
                </a>
            <?php else: ?>
                <span class="text-muted">—</span>
            <?php endif; ?>
        </td>

        <td><?= $role ? htmlspecialchars($role ?? '') : '<span class="text-muted">—</span>' ?></td>

        <td>
            <?php if ($level): ?>
                <span class="badge bg-primary badge-level"><?= htmlspecialchars($level ?? '') ?></span>
            <?php else: ?>
                <span class="text-muted">—</span>
            <?php endif; ?>
        </td>

        <td><?= $desig ? htmlspecialchars($desig) : '<span class="text-muted">—</span>' ?></td>

        <td><?= $repCode ? htmlspecialchars($repCode) : '<span class="text-muted">—</span>' ?></td>

        <td><?= $repName ? htmlspecialchars($repName) : '<span class="text-muted">—</span>' ?></td>

        <td>
            <?php if ($repMobile): ?>
                <a href="app_report.php?source2=<?= enc($repMobile) ?>"
                   class="text-decoration-none" target="_blank">
                    <?= htmlspecialchars($repMobile ?? '') ?>
                </a>
            <?php else: ?>
                <span class="text-muted">—</span>
            <?php endif; ?>
        </td>

        <td class="text-center">
            <span class="badge bg-secondary"><?= (int)$row['total_stores'] ?></span>
        </td>

        <td class="text-center">
            <span class="badge bg-success"><?= (int)$row['connected_stores'] ?></span>
        </td>

        <td class="store-box">
            <?php if (!empty($storeNames)): ?>
                <?php foreach ($storeNames as $store): ?>
                    <span class="store-badge"><?= htmlspecialchars($store ?? '') ?></span>
                <?php endforeach; ?>
            <?php else: ?>
                <span class="text-muted">—</span>
            <?php endif; ?>
        </td>

    </tr>
    <?php endforeach; ?>

    </tbody>
    </table>
    </div>

</div>
</div>
</div>

<script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"></script>
<script src="https://cdn.datatables.net/1.13.8/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.13.8/js/dataTables.bootstrap5.min.js"></script>

<script>
$(document).ready(function () {

    var table = $('#usersTable').DataTable({
        pageLength: 100,
        lengthMenu: [[50, 100, 250, 500, -1], [50, 100, 250, 500, "All"]],
        ordering:   true,
        searching:  true,
        responsive: true,
        columnDefs: [{ targets: [13], orderable: false }]
    });

    // Custom filter
    $.fn.dataTable.ext.search.push(function (settings, data, dataIndex) {
        if (settings.nTable.id !== 'usersTable') return true;

        var row       = $(table.row(dataIndex).node());
        var roleVal   = $('#filterRole').val();
        var storeVal  = $('#filterStore').val();

        if (roleVal && row.data('role') !== roleVal) return false;

        if (storeVal) {
            var stores = (row.data('stores') || '').split('|||');
            if (stores.indexOf(storeVal) === -1) return false;
        }

        return true;
    });

    $('#filterRole, #filterStore').on('change', function () {
        table.draw();
    });

    $('#btnReset').on('click', function () {
        $('#filterRole, #filterStore').val('');
        table.search('').draw();
    });

});
</script>

</body>
</html>