<?php
// ─────────────────────────────────────────────────────────────
// TIMEZONE — always IST
// ─────────────────────────────────────────────────────────────
date_default_timezone_set('Asia/Kolkata');

// ─────────────────────────────────────────────────────────────
// NO CACHE — always fresh report
// ─────────────────────────────────────────────────────────────
header('Cache-Control: no-store, no-cache, must-revalidate, max-age=0');
header('Cache-Control: post-check=0, pre-check=0', false);
header('Pragma: no-cache');
header('Expires: Thu, 01 Jan 1970 00:00:00 GMT');

include 'db_connect.php'; // provides $conn (mysqli) + enc() + dec()

// ─────────────────────────────────────────────────────────────
// MASTER ACCESS KEY
// ─────────────────────────────────────────────────────────────
define('MASTER_MOBILE', '9717795048');

// ─────────────────────────────────────────────────────────────
// DECRYPT ACCESS PARAMS
// ─────────────────────────────────────────────────────────────
$raw_source1 = trim($_GET['source1'] ?? '');
$raw_source3 = trim($_GET['source3'] ?? '');

$access_mobile   = $raw_source1 ? dec($raw_source1) : '';
$access_store_id = $raw_source3 ? dec($raw_source3) : '';

$isMaster = ($access_mobile === MASTER_MOBILE);

// ─────────────────────────────────────────────────────────────
// DEFAULT DATE RANGE
// Before 5 PM IST → Yesterday, After 5 PM → Today
// ─────────────────────────────────────────────────────────────
$currentHour  = (int)date('H');
$defaultRange = ($currentHour < 17) ? 'yesterday' : 'today';

$range       = $_GET['range'] ?? $defaultRange;
$storeFilter = $_GET['store'] ?? '';

// ── Fixed date ranges — no custom calendar ─────────────────────
$today     = date('Y-m-d');
$yesterday = date('Y-m-d', strtotime('-1 day'));

$thisWeekMon  = date('Y-m-d', strtotime('monday this week'));
$prevWeekMon  = date('Y-m-d', strtotime('monday last week'));
$prevWeekSun  = date('Y-m-d', strtotime('sunday last week'));

$thisMonthStart = date('Y-m-01');

// Financial year: starts 1 Apr
$currentMonth = (int)date('n');
$fyYear       = ($currentMonth >= 4) ? (int)date('Y') : (int)date('Y') - 1;
$ytdStart     = $fyYear . '-04-01';

// Last 12 complete months (not including current month)
$last12End   = date('Y-m-t', strtotime('last month'));
$last12Start = date('Y-m-01', strtotime('-12 months', strtotime($last12End)));

switch ($range) {
    case 'today':
        $dateFrom = $dateTo = $today;          break;
    case 'yesterday':
        $dateFrom = $dateTo = $yesterday;       break;
    case 'this_week':
        $dateFrom = $thisWeekMon;
        $dateTo   = $today;                     break;
    case 'prev_week':
        $dateFrom = $prevWeekMon;
        $dateTo   = $prevWeekSun;               break;
    case 'this_month':
        $dateFrom = $thisMonthStart;
        $dateTo   = $today;                     break;
    case 'ytd':
        $dateFrom = $ytdStart;
        $dateTo   = $today;                     break;
    case 'last12':
        $dateFrom = $last12Start;
        $dateTo   = $last12End;                 break;
    default:
        // Handle individual month keys: month_2026_06
        if (strpos($range, 'month_') === 0) {
            $parts    = explode('_', $range); // ['month','2026','06']
            $mYear    = $parts[1] ?? date('Y');
            $mMonth   = $parts[2] ?? date('m');
            $dateFrom = $mYear . '-' . $mMonth . '-01';
            $dateTo   = date('Y-m-t', strtotime($dateFrom));
        } else {
            $range    = $defaultRange;
            $dateFrom = $dateTo = ($defaultRange === 'today') ? $today : $yesterday;
        }
}

$dtFrom = $dateFrom . ' 00:00:00';
$dtTo   = $dateTo   . ' 23:59:59';

// Dropdown display labels
$rangeLabels = [
    'today'      => 'Today',
    'yesterday'  => 'Yesterday (' . date('d M', strtotime($yesterday)) . ')',
    //'this_week'  => 'This Week (' . date('d M', strtotime($thisWeekMon)) . ' - ' . date('d M', strtotime($today)) . ')',
    //'prev_week'  => 'Previous Week (' . date('d M', strtotime($prevWeekMon)) . ' - ' . date('d M', strtotime($prevWeekSun)) . ')',
    'this_month' => 'This Month (' . date('M Y') . ')',
    'ytd'        => 'Year to Date (' . date('d M Y', strtotime($ytdStart)) . ' - Today)',
];

// Add last 12 individual months (most recent first, excluding current month)
// Use 1st of current month as base to avoid end-of-month skipping
$baseMonth = date('Y-m-01');
for ($mi = 1; $mi <= 12; $mi++) {
    $mDate  = date('Y-m-01', strtotime("-{$mi} months", strtotime($baseMonth)));
    $mKey   = 'month_' . date('Y_m', strtotime($mDate));
    $rangeLabels[$mKey] = date('M Y', strtotime($mDate));
}


// ─────────────────────────────────────────────────────────────
// FIND ACCESSIBLE STORES
// ─────────────────────────────────────────────────────────────
$accessibleStores = [];

if ($isMaster || (empty($access_mobile) && empty($access_store_id))) {
    // Master access or no params — show ALL stores
    $res = $conn->query("
        SELECT ss.source_table_id, ss.title, ss.address, ss.city, ss.state, ss.web_id,
               dm.sap_code, dm.website_id AS uid
        FROM showroom_store ss
        LEFT JOIN dc_dealer_master dm ON dm.website_id = ss.website_id
        WHERE ss.deleted_at IS NULL
          AND ss.dealertype IS NULL
          AND ss.website_id > 0
        ORDER BY ss.title ASC
    ");
    while ($r = $res->fetch_assoc()) $accessibleStores[] = $r;

} elseif (!empty($access_store_id)) {
    $stmt = $conn->prepare("
        SELECT ss.source_table_id, ss.title, ss.address, ss.city, ss.state, ss.web_id,
               dm.sap_code, dm.website_id AS uid
        FROM showroom_store ss
        LEFT JOIN dc_dealer_master dm ON dm.website_id = ss.website_id
        WHERE ss.source_table_id = ?
          AND ss.deleted_at IS NULL
        LIMIT 1
    ");
    $stmt->bind_param("s", $access_store_id);
    $stmt->execute();
    $r = $stmt->get_result()->fetch_assoc();
    if ($r) $accessibleStores[] = $r;

} else {
    // Employee mobile via showroom_users
    $stmt = $conn->prepare("
        SELECT DISTINCT ss.source_table_id, ss.title, ss.address, ss.city, ss.state, ss.web_id,
               dm.sap_code, dm.website_id AS uid
        FROM showroom_users su
        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 dc_dealer_master dm ON dm.website_id = ss.website_id
        WHERE su.phone = ?
          AND su.deleted_at IS NULL
          AND ss.deleted_at IS NULL
          AND ss.dealertype IS NULL
          AND ss.website_id > 0
        ORDER BY ss.title ASC
    ");
    $stmt->bind_param("s", $access_mobile);
    $stmt->execute();
    $res = $stmt->get_result();
    while ($r = $res->fetch_assoc()) $accessibleStores[] = $r;

    // Fallback: emp_users + dealer_employee_website_mapping
    if (empty($accessibleStores)) {
        $stmt2 = $conn->prepare("
            SELECT DISTINCT ss.source_table_id, ss.title, ss.address, ss.city, ss.state, ss.web_id,
                   dm.sap_code, dm.website_id AS uid
            FROM emp_users eu
            JOIN dealer_employee_website_mapping dwm ON dwm.emp_code = eu.emp_code
            JOIN showroom_store ss ON ss.website_id = dwm.website_id
            LEFT JOIN dc_dealer_master dm ON dm.website_id = ss.website_id
            WHERE eu.mobile1 = ?
              AND eu.status = 1
              AND ss.deleted_at IS NULL
              AND ss.dealertype IS NULL
              AND ss.website_id > 0
            ORDER BY ss.title ASC
        ");
        $stmt2->bind_param("s", $access_mobile);
        $stmt2->execute();
        $res2 = $stmt2->get_result();
        while ($r = $res2->fetch_assoc()) $accessibleStores[] = $r;
    }
}

if (empty($accessibleStores)) {
    die("No store access found for this link.");
}

// ─────────────────────────────────────────────────────────────
// STORE FILTER
// ─────────────────────────────────────────────────────────────
$multiStore = count($accessibleStores) > 1;

if ($multiStore && !empty($storeFilter)) {
    $filtered = array_filter($accessibleStores, function($s) use ($storeFilter) { return $s['source_table_id'] == $storeFilter; });
    $activeStores = !empty($filtered) ? array_values($filtered) : $accessibleStores;
} else {
    $activeStores = $accessibleStores;
}

$storeIds    = array_column($activeStores, 'source_table_id');
$storeInList = implode(',', array_map('intval', $storeIds));
$currentStore = $activeStores[0];

// ─────────────────────────────────────────────────────────────
// HELPERS
// ─────────────────────────────────────────────────────────────
function q($conn, $sql, $types = '', $params = []) {
    if ($types && $params) {
        $stmt = $conn->prepare($sql);
        $stmt->bind_param($types, ...$params);
        $stmt->execute();
        return $stmt->get_result();
    }
    return $conn->query($sql);
}

function pct($val, $total) {
    if (!$total || !$val) return '—';
    return number_format($val / $total * 100, 1) . '%';
}

function fmt($val) {
    return ($val === null || $val == 0) ? '—' : $val;
}

// ─────────────────────────────────────────────────────────────
// MODAL DETAIL QUERY
// Returns rows: date, order_number, customer_name, profession, pdf_url
// Accepts optional extra WHERE condition to filter per section
// ─────────────────────────────────────────────────────────────
function getDetailRows($conn, string $storeInList, string $dtFrom, string $dtTo, string $extraWhere = ''): array
{
    $sql = "
        SELECT
            DATE_FORMAT(so.updated_at, '%d %b %Y')   AS order_date,
            so.source_table_id                        AS order_number,
            sc.name                                   AS customer_name,
            COALESCE(sc.profession, '—')              AS profession,
            CONCAT('https://dealerapp.kajariaeternity.in/storage/app/public/order_',
                   so.source_table_id, '.pdf')        AS pdf_url,
            ROUND(
                COALESCE(
                    (SELECT AVG(of2.qty) FROM showroom_order_final of2
                     WHERE of2.order_id = so.source_table_id
                       AND of2.qty > 0),
                    0
                ) * 0.092903, 1
            )                                         AS qty_sqm,

            -- Project name (only relevant when type = Project)
            sc.type                                   AS customer_type,
            sc.project_name                           AS project_name,

            -- Attended by (user_id)
            att.name                                  AS attended_name,
            att.phone                                 AS attended_mobile,

            -- Referred to sales (referred_by)
            ref.name                                  AS referred_name,
            ref.phone                                 AS referred_mobile,

            -- Referral info
            sc.referral_type                          AS referral_type,
            sc.referred_from                          AS referred_from,

            -- Companion
            (SELECT GROUP_CONCAT(
                        CONCAT(cd.name, ' (', cd.profession, ')')
                        SEPARATOR ', '
                    )
             FROM showroom_companion_detail cd
             WHERE cd.customer_id = sc.source_table_id
            )                                         AS companions,

            -- Store name (useful for combined reports)
            ss.title                                  AS store_name,

            -- Role 2 user (or role 1 fallback) for this store, excluding system users
            (SELECT CONCAT(su2.name, ' (', COALESCE(su2.phone,''), ')')
             FROM showroom_users su2
             JOIN showroom_store_user ssu2 ON ssu2.user_id = su2.source_table_id
             WHERE ssu2.store_id       = ss.source_table_id
               AND su2.role_id         IN (2,1)
               AND su2.deleted_at      IS NULL
               AND su2.source_table_id NOT IN (23,24,26,1,778,812,814,757,1282,1283,1284,450,753,754,756,813,815)
             ORDER BY su2.role_id ASC
             LIMIT 1
            )                                         AS store_head

        FROM showroom_customer sc
        JOIN showroom_order so
            ON so.customer_id = sc.source_table_id
            AND so.store_id   = sc.store_id
            AND so.status     = 1
            AND so.updated_at BETWEEN ? AND ?
        JOIN showroom_store ss
            ON ss.source_table_id = sc.store_id
        LEFT JOIN showroom_users att
            ON att.source_table_id = sc.user_id
        LEFT JOIN showroom_users ref
            ON ref.source_table_id = sc.referred_by
        WHERE sc.store_id IN ($storeInList)
          AND sc.deleted_at IS NULL
          $extraWhere
        ORDER BY so.updated_at DESC
    ";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param('ss', $dtFrom, $dtTo);
    $stmt->execute();
    return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
}

// ── Detail data loaded lazily via AJAX in get_detail_data.php ──

// ─────────────────────────────────────────────────────────────
// METRICS — read from pre-built summary table (showroom_report_summary)
// Grouped data from showroom_report_grouped for longer ranges
// Falls back to live queries only if no summary data found
// ─────────────────────────────────────────────────────────────

$webIds    = array_column($activeStores, 'web_id');
$webIdList = implode("','", array_map('addslashes', $webIds));

// Determine if we use grouped table or live for sections
// Short ranges (today/yesterday/this_week/prev_week) = live grouped queries (fast, small range)
// Longer ranges (this_month/ytd/last12) = pre-built monthly grouped table
$useGroupedTable = in_array($range, ['this_month', 'ytd', 'last12']) || strpos($range, 'month_') === 0;

// ── READ SUMMARY COUNTS from pre-built table ──────────────────
$summaryStmt = $conn->prepare("
    SELECT
        COALESCE(SUM(total_walkins),  0) AS total_walkins,
        COALESCE(SUM(projects),       0) AS projects,
        COALESCE(SUM(retails),        0) AS retails,
        COALESCE(SUM(look_feel),      0) AS look_feel,
        COALESCE(SUM(repeat_clients), 0) AS repeat_clients,
        COALESCE(SUM(outstation),     0) AS outstation,
        COALESCE(SUM(kerovit),        0) AS kerovit,
        COALESCE(SUM(total_qty),      0) AS total_qty
    FROM showroom_report_summary
    WHERE store_id IN ($storeInList)
      AND report_date BETWEEN ? AND ?
");
$summaryStmt->bind_param('ss', $dateFrom, $dateTo);
$summaryStmt->execute();
$summaryRow = $summaryStmt->get_result()->fetch_assoc();

$hasSummaryData = ($summaryRow && (int)$summaryRow['total_walkins'] > 0);

// For Last 12 months — fetch month-by-month breakdown
$last12Months = [];
if ($range === 'last12') {
    $mStmt = $conn->prepare("
        SELECT DATE_FORMAT(report_date, '%Y-%m') AS month_key,
               DATE_FORMAT(report_date, '%b %Y') AS month_label,
               SUM(total_walkins) AS walkins,
               SUM(total_qty)     AS qty
        FROM showroom_report_summary
        WHERE store_id IN ($storeInList)
          AND report_date BETWEEN ? AND ?
        GROUP BY DATE_FORMAT(report_date, '%Y-%m')
        ORDER BY month_key ASC
    ");
    $mStmt->bind_param('ss', $dateFrom, $dateTo);
    $mStmt->execute();
    $last12Months = $mStmt->get_result()->fetch_all(MYSQLI_ASSOC);
}

if ($hasSummaryData) {
    $totalWalkins  = (int)$summaryRow['total_walkins'];
    $projects      = (int)$summaryRow['projects'];
    $retails       = (int)$summaryRow['retails'];
    $lookAndFeel   = (int)$summaryRow['look_feel'];
    $repeatClients = (int)$summaryRow['repeat_clients'];
    $outstation    = (int)$summaryRow['outstation'];
    $kerovit       = (int)$summaryRow['kerovit'];
    // summary table stores sqm directly (cron converts before storing)
    $rawQty   = (float)$summaryRow['total_qty'];
    $totalQty = ($rawQty > 0) ? number_format(round($rawQty), 0) : null;
    $cacheAge      = '';
} else {
    // ── LIVE FALLBACK ─────────────────────────────────────────
    $cacheAge = ' <small class="text-secondary" style="font-size:11px;">(live)</small>';

    $stmt = $conn->prepare("
        SELECT
            COUNT(DISTINCT sc.source_table_id)                                               AS total_walkins,
            COUNT(DISTINCT CASE WHEN sc.type = 'Project'  THEN sc.source_table_id END)       AS projects,
            COUNT(DISTINCT CASE WHEN sc.type = 'Retailer' THEN sc.source_table_id END)       AS retails,
            COUNT(DISTINCT CASE WHEN sc.customer_status = 1 THEN sc.source_table_id END)     AS outstation,
            COUNT(DISTINCT CASE WHEN so.referrd_to_kerovit = 1 THEN sc.source_table_id END)  AS kerovit,
            COUNT(DISTINCT CASE
                WHEN EXISTS (
                    SELECT 1 FROM showroom_customer sc2
                    JOIN showroom_order so2 ON so2.customer_id = sc2.source_table_id
                        AND so2.store_id = sc2.store_id AND so2.status = 1
                    WHERE sc2.mobile = sc.mobile AND sc2.store_id IN ($storeInList)
                      AND sc2.deleted_at IS NULL
                      AND (so2.updated_at < ? OR so2.updated_at > ?)
                ) THEN sc.mobile END)                                                        AS repeat_clients
        FROM showroom_customer sc
        JOIN showroom_order so ON so.customer_id = sc.source_table_id
            AND so.store_id = sc.store_id AND so.status = 1
            AND so.updated_at BETWEEN ? AND ?
        WHERE sc.store_id IN ($storeInList) AND sc.deleted_at IS NULL
    ");
    $stmt->bind_param('ssss', $dtFrom, $dtTo, $dtFrom, $dtTo);
    $stmt->execute();
    $counts = $stmt->get_result()->fetch_assoc();
    $totalWalkins  = (int)$counts['total_walkins'];
    $projects      = (int)$counts['projects'];
    $retails       = (int)$counts['retails'];
    $outstation    = (int)$counts['outstation'];
    $kerovit       = (int)$counts['kerovit'];
    $repeatClients = (int)$counts['repeat_clients'];

    $lfRes = $conn->query("
        SELECT COUNT(DISTINCT ta.source_table_id) AS cnt
        FROM showroom_tblenquiry_app ta
        WHERE ta.lead_visit = 1 AND ta.deleted_at IS NULL
          AND ta.created_at BETWEEN '$dtFrom' AND '$dtTo'
          AND ta.store_code IN ('$webIdList')
    ");
    $lookAndFeel = $lfRes ? (int)$lfRes->fetch_assoc()['cnt'] : 0;

    $stmtQ = $conn->prepare("
        SELECT COALESCE(SUM(total_quantity), 0) AS grand_total
        FROM (
            SELECT of2.customer_mobile, of2.category, of2.sub_category, of2.quantity AS total_quantity
            FROM showroom_order_final of2
            JOIN showroom_order so ON so.source_table_id = of2.order_id
            WHERE so.store_id IN ($storeInList) AND so.status = 1
              AND of2.created_at BETWEEN ? AND ?
            GROUP BY of2.customer_mobile, of2.quantity, of2.category, of2.sub_category
        ) AS grouped_result
    ");
    $stmtQ->bind_param('ss', $dtFrom, $dtTo);
    $stmtQ->execute();
    // quantity is in sqft — divide by 10.7639 to convert to sqm
    $rawQty   = (float)$stmtQ->get_result()->fetch_assoc()['grand_total'];
    $totalQty = ($rawQty > 0) ? number_format(round($rawQty / 10.7639), 0) : null;
}

// ── GROUPED SECTIONS (Attended, Referred, Profession, Source, Reference) ──────
if ($useGroupedTable && $hasSummaryData) {
    // Read from pre-built monthly grouped table — aggregate months in PHP
    $monthsInRange = [];
    $cur = strtotime(date('Y-m-01', strtotime($dateFrom)));
    $end = strtotime(date('Y-m-01', strtotime($dateTo)));
    while ($cur <= $end) {
        $monthsInRange[] = date('Y-m-01', $cur);
        $cur = strtotime('+1 month', $cur);
    }
    $monthList = implode("','", $monthsInRange);

    $gRes = $conn->query("
        SELECT attended_json, referred_json, profession_json, reference_json, source_json
        FROM showroom_report_grouped
        WHERE store_id IN ($storeInList) AND report_month IN ('$monthList')
    ");

    $attMap  = []; $refMap  = []; $profMap = []; $refTypeMap = []; $srcMap = [];
    while ($gRow = $gRes->fetch_assoc()) {
        foreach (json_decode($gRow['attended_json'],   true) ?: [] as $r) $attMap[$r['name']]          = ($attMap[$r['name']]          ?? 0) + $r['cnt'];
        foreach (json_decode($gRow['referred_json'],   true) ?: [] as $r) $refMap[$r['name']]          = ($refMap[$r['name']]          ?? 0) + $r['cnt'];
        foreach (json_decode($gRow['profession_json'], true) ?: [] as $r) $profMap[$r['profession']]   = ($profMap[$r['profession']]   ?? 0) + $r['cnt'];
        foreach (json_decode($gRow['reference_json'],  true) ?: [] as $r) $refTypeMap[$r['ref_type']]  = ($refTypeMap[$r['ref_type']]  ?? 0) + $r['cnt'];
        foreach (json_decode($gRow['source_json'],     true) ?: [] as $r) $srcMap[$r['src']]           = ($srcMap[$r['src']]           ?? 0) + $r['cnt'];
    }
    arsort($attMap);  arsort($refMap); arsort($profMap); arsort($refTypeMap); arsort($srcMap);

    $attendedRows   = array_map(function($n,$c){return['name'=>$n,'cnt'=>$c];}, array_keys($attMap),     array_values($attMap));
    $referredRows   = array_map(function($n,$c){return['name'=>$n,'cnt'=>$c];}, array_keys($refMap),     array_values($refMap));
    $professionRows = array_map(function($p,$c){return['profession'=>$p,'cnt'=>$c];}, array_keys($profMap), array_values($profMap));
    $referenceRows  = array_map(function($r,$c){return['ref_type'=>$r,'cnt'=>$c];}, array_keys($refTypeMap), array_values($refTypeMap));
    $sourceRows     = array_map(function($s,$c){return['src'=>$s,'cnt'=>$c];}, array_keys($srcMap),      array_values($srcMap));
    $totalReference = array_sum(array_column($referenceRows, 'cnt'));

} else {
    // ── Live grouped queries (short ranges — fast) ─────────────
    $stmtRef = $conn->prepare("
        SELECT su.name, COUNT(DISTINCT sc.source_table_id) AS cnt
        FROM showroom_customer sc
        JOIN showroom_order so ON so.customer_id = sc.source_table_id AND so.store_id = sc.store_id
            AND so.status = 1 AND so.updated_at BETWEEN ? AND ?
        JOIN showroom_users su ON su.source_table_id = sc.referred_by
        WHERE sc.store_id IN ($storeInList) AND sc.deleted_at IS NULL AND sc.referred_by IS NOT NULL
        GROUP BY sc.referred_by, su.name ORDER BY cnt DESC
    ");
    $stmtRef->bind_param('ss', $dtFrom, $dtTo);
    $stmtRef->execute();
    $referredRows = $stmtRef->get_result()->fetch_all(MYSQLI_ASSOC);

    $stmtAll = $conn->prepare("
        SELECT su_att.name AS att_name, sc.profession, sc.referral_type, sc.source
        FROM showroom_customer sc
        JOIN showroom_order so ON so.customer_id = sc.source_table_id AND so.store_id = sc.store_id
            AND so.status = 1 AND so.updated_at BETWEEN ? AND ?
        LEFT JOIN showroom_users su_att ON su_att.source_table_id = sc.user_id
        WHERE sc.store_id IN ($storeInList) AND sc.deleted_at IS NULL
    ");
    $stmtAll->bind_param('ss', $dtFrom, $dtTo);
    $stmtAll->execute();
    $allRows = $stmtAll->get_result()->fetch_all(MYSQLI_ASSOC);

    $attMap = []; $profMap = []; $refTypeMap = []; $srcMap = [];
    foreach ($allRows as $r) {
        if (!empty($r['att_name']))     $attMap[$r['att_name']]               = ($attMap[$r['att_name']]               ?? 0) + 1;
        $pk = $r['profession'] ?: 'Not Specified';
        $profMap[$pk] = ($profMap[$pk] ?? 0) + 1;
        if (!empty($r['referral_type'])) $refTypeMap[$r['referral_type']]     = ($refTypeMap[$r['referral_type']]      ?? 0) + 1;
        $sk = $r['source'] ?: 'Walk-in';
        $srcMap[$sk] = ($srcMap[$sk] ?? 0) + 1;
    }
    arsort($attMap); arsort($profMap); arsort($refTypeMap); arsort($srcMap);

    $attendedRows   = array_map(function($n,$c){return['name'=>$n,'cnt'=>$c];}, array_keys($attMap),     array_values($attMap));
    $professionRows = array_map(function($p,$c){return['profession'=>$p,'cnt'=>$c];}, array_keys($profMap), array_values($profMap));
    $referenceRows  = array_map(function($r,$c){return['ref_type'=>$r,'cnt'=>$c];}, array_keys($refTypeMap), array_values($refTypeMap));
    $sourceRows     = array_map(function($s,$c){return['src'=>$s,'cnt'=>$c];}, array_keys($srcMap),      array_values($srcMap));
    $totalReference = array_sum(array_column($referenceRows, 'cnt'));
}
// ─────────────────────────────────────────────────────────────
// TOP 10 SIZES & PRODUCTS (single store only, not combined)
// ─────────────────────────────────────────────────────────────
$topSizes       = [];
$topProducts    = [];
$productsBySize = [];

if (!$multiStore || !empty($storeFilter)) {
    $stmtSizes = $conn->prepare("
        SELECT of2.sizeInCm AS size_desc,
               COUNT(DISTINCT of2.order_id) AS order_count,
               ROUND(SUM(of2.quantity) / 10.7639) AS total_sqm,
               SUM(of2.quantity) AS total_sqft
        FROM showroom_order_final of2
        JOIN showroom_order so ON so.source_table_id = of2.order_id
        WHERE so.store_id IN ($storeInList) AND so.status = 1
          AND so.updated_at BETWEEN ? AND ?
          AND of2.sizeInCm IS NOT NULL AND of2.sizeInCm != ''
        GROUP BY of2.sizeInCm
        ORDER BY order_count DESC
        LIMIT 10
    ");
    $stmtSizes->bind_param('ss', $dtFrom, $dtTo);
    $stmtSizes->execute();
    $topSizes = $stmtSizes->get_result()->fetch_all(MYSQLI_ASSOC);

    $stmtProds = $conn->prepare("
        SELECT of2.name AS product_name,
               of2.sizeInCm AS size_desc,
               MAX(of2.seriesDescription) AS series_desc,
               COUNT(DISTINCT of2.order_id) AS order_count,
               ROUND(SUM(of2.quantity) / 10.7639) AS total_sqm,
               SUM(of2.quantity) AS total_sqft
        FROM showroom_order_final of2
        JOIN showroom_order so ON so.source_table_id = of2.order_id
        WHERE so.store_id IN ($storeInList) AND so.status = 1
          AND so.updated_at BETWEEN ? AND ?
          AND of2.name IS NOT NULL AND of2.name != ''
        GROUP BY of2.name, of2.sizeInCm
        ORDER BY order_count DESC
        LIMIT 10
    ");
    $stmtProds->bind_param('ss', $dtFrom, $dtTo);
    $stmtProds->execute();
    $topProducts = $stmtProds->get_result()->fetch_all(MYSQLI_ASSOC);

    // Products per size — for accordion in Top 10 Sizes table
    $stmtSizeProds = $conn->prepare("
        SELECT of2.sizeInCm AS size_desc,
               of2.name AS product_name,
               MAX(of2.seriesDescription) AS series_desc,
               COUNT(DISTINCT of2.order_id) AS order_count
        FROM showroom_order_final of2
        JOIN showroom_order so ON so.source_table_id = of2.order_id
        WHERE so.store_id IN ($storeInList) AND so.status = 1
          AND so.updated_at BETWEEN ? AND ?
          AND of2.sizeInCm IS NOT NULL AND of2.sizeInCm != ''
          AND of2.name IS NOT NULL AND of2.name != ''
        GROUP BY of2.sizeInCm, of2.name
        ORDER BY of2.sizeInCm, order_count DESC
    ");
    $stmtSizeProds->bind_param('ss', $dtFrom, $dtTo);
    $stmtSizeProds->execute();

    // Group by size into associative array
    $productsBySize = [];
    foreach ($stmtSizeProds->get_result()->fetch_all(MYSQLI_ASSOC) as $row) {
        $size = $row['size_desc'];
        if (!isset($productsBySize[$size])) $productsBySize[$size] = [];
        if (count($productsBySize[$size]) < 10) { // max 10 per size
            $productsBySize[$size][] = [
                'name'    => $row['product_name'],
                'cnt'     => $row['order_count'],
                'series'  => $row['series_desc'] ?? '',
            ];
        }
    }
}

// QR Standee count (source = 'QR' or 'qr' — combined report only)
$qrStandeeCount = 0;
if ($multiStore && empty($storeFilter)) {
    $stmtQR = $conn->prepare("
        SELECT COUNT(DISTINCT sc.source_table_id) AS cnt
        FROM showroom_customer sc
        JOIN showroom_order so ON so.customer_id = sc.source_table_id
            AND so.store_id = sc.store_id AND so.status = 1
            AND so.updated_at BETWEEN ? AND ?
        WHERE sc.store_id IN ($storeInList)
          AND sc.deleted_at IS NULL
          AND LOWER(sc.source) IN ('qr','qr standee','qr_standee','qr scan')
    ");
    $stmtQR->bind_param('ss', $dtFrom, $dtTo);
    $stmtQR->execute();
    $qrStandeeCount = (int)$stmtQR->get_result()->fetch_assoc()['cnt'];
}

// ─────────────────────────────────────────────────────────────
// RENDER HELPERS
// ─────────────────────────────────────────────────────────────
// Format large numbers compactly for metric cards
function fmtMetric($val) {
    if ($val === null || $val === '' || $val === '—') return '—';
    $n = (float) str_replace(',', '', $val);
    if ($n >= 10000000) return number_format($n / 10000000, 1) . 'Cr';
    if ($n >= 100000)   return number_format($n / 100000, 1) . 'L';
    if ($n >= 10000)    return number_format($n / 1000, 1) . 'K';
    return is_string($val) ? $val : number_format($n);
}
function renderCards(array $rows, int $total, string $nameKey = 'name', string $section = ''): string
{
    if (empty($rows)) return '<div class="text-secondary small ps-1 pb-2">No data for selected period.</div>';
    $vd = $section ? "<span class='text-warning view-details' data-section='{$section}' style='cursor:pointer;font-size:.85rem;'>View Details</span>" : '';
    $html = '<div class="walkin-row">';
    foreach ($rows as $r) {
        $name = htmlspecialchars($r[$nameKey] ?? 'Unknown');
        $cnt  = (int)$r['cnt'];
        $pct  = $total ? number_format($cnt / $total * 100, 1) : 0;
        $init = mb_strtoupper(mb_substr($name, 0, 1));
        $html .= "<div class='walkin-col'><div class='glass p-3 text-center'>
            <div class='avatar'>{$init}</div>
            <div class='small'>{$name}</div>
            <div class='fs-2 fw-bold'>{$cnt}</div>
            <div class='sub'>({$pct}%)</div>
        </div></div>";
    }
    $html .= '</div>';
    return $html;
}

function sectionHeader(string $title, string $section): string
{
    return "<div class='section-title'>
        <strong>{$title}</strong>
        <span class='text-warning view-details' data-section='{$section}' style='cursor:pointer;font-size:.85rem;'>View Details</span>
    </div>";
}

// ── Graph data (separate include — remove to disable graphs)
include __DIR__ . '/report_graph.php';
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>Experience Centre Walk-ins Report</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.11.3/font/bootstrap-icons.css" rel="stylesheet">
    <style>
        body { background:radial-gradient(circle at top,#1d2740,#0b0d13 60%); color:#fff; font-family:Arial,sans-serif; padding-bottom:30px; }
        .wrap { max-width:760px; margin:auto; padding:18px; }
        .glass { background:linear-gradient(180deg,#1a1f2cbf,#131722f7); border:1px solid #3a3f4d; border-radius:18px; box-shadow:0 8px 30px rgba(0,0,0,.35); }
        .metric { font-size:2rem; font-weight:700; line-height:1; }
        .sub { color:#aeb5c4; font-size:.88rem; }
        .form-control,.form-select { background:#0f1320 !important; color:#fff !important; border-color:#414758 !important; }
        .icon { width:54px; height:54px; border-radius:50%; display:flex; align-items:center; justify-content:center; background:#3a2d0b; color:#ffc83d; font-size:24px; }
        .avatar { width:52px; height:52px; border-radius:50%; background:#6f42c1; display:flex; align-items:center; justify-content:center; margin:0 auto 8px; font-size:22px; }
        .walkin-row { display:flex; gap:12px; overflow-x:auto; -webkit-overflow-scrolling:touch; scroll-snap-type:x proximity; padding-bottom:8px; margin-top:.5rem; }
        .walkin-row::-webkit-scrollbar { height:6px; }
        .walkin-row::-webkit-scrollbar-thumb { background:#414758; border-radius:3px; }
        .walkin-col { flex:0 0 42%; scroll-snap-align:start; }
        input[type="date"] { cursor:pointer; }
        input[type="date"]::-webkit-calendar-picker-indicator { cursor:pointer; }
        @media(min-width:768px) { .walkin-row { overflow-x:visible; flex-wrap:wrap; } .walkin-col { flex:1 1 0; min-width:130px; } }
        .section-title { display:flex; justify-content:space-between; align-items:center; margin-bottom:4px; }
        .btn-filter { background:#ffc83d; border:none; color:#000; font-weight:600; padding:6px 16px; border-radius:8px; }
        .btn-filter:hover { background:#e6b200; }
        /* DataTables dark theme inside modal */
        #detailModal .dataTables_wrapper { color:#ddd; padding:0 12px; }
        #detailModal .dataTables_filter input,
        #detailModal .dataTables_length select { background:#0f1320 !important; color:#fff !important; border-color:#414758 !important; border-radius:6px; padding:4px 8px; }
        #detailModal .dataTables_info,
        #detailModal .dataTables_length { color:#aeb5c4; font-size:12px; }
        #detailModal .page-link { background:#1a1f2c; border-color:#3a3f4d; color:#fff; }
        #detailModal .page-item.active .page-link { background:#ffc83d; border-color:#ffc83d; color:#000; }
        #detailModal .page-link:hover { background:#2a2f3d; }
        #detailModal td.child { background:#1a1f2c !important; }
        #detailModal td.child ul.dtr-details { width:100%; }
        #detailModal td.child ul.dtr-details li { border-color:#3a3f4d; display:flex; padding:6px 0; }
        #detailModal td.child ul.dtr-details li span.dtr-title { color:#ffc83d; min-width:120px; font-weight:600; }
        #detailModal td.child ul.dtr-details li span.dtr-data { color:#ddd; }
        #detailModal tr.child:hover > td { background:transparent !important; }
        /* Modal dark theme */
        .modal-content { background:#131722; border:1px solid #3a3f4d; color:#fff; }
        .modal-header { border-bottom:1px solid #3a3f4d; }
        .modal-footer { border-top:1px solid #3a3f4d; }
        .table-dark-custom { color:#ddd; }
        .table-dark-custom thead th { background:#1a1f2c; color:#ffc83d; border-color:#3a3f4d; font-size:13px; }
        .table-dark-custom tbody td { border-color:#2a2f3d; font-size:13px; vertical-align:middle; }
        .table-dark-custom tbody tr:hover { background:#1e2433; }
        .btn-close-white { filter:invert(1); }
        .metric-card { cursor:pointer; }
        .metric-card:hover .metric { color:#ffc83d; }
        /* Loader overlay */
        #pageLoader { display:none; position:fixed; inset:0; background:rgba(0,0,0,.6);
            z-index:9999; align-items:center; justify-content:center; flex-direction:column; }
        #pageLoader.show { display:flex; }
        .loader-spinner { width:48px; height:48px; border:4px solid #414758;
            border-top-color:#ffc83d; border-radius:50%; animation:spin .8s linear infinite; }
        @keyframes spin { to { transform:rotate(360deg); } }
        /* Carousel */
        .carousel-cards { display:flex; gap:12px; overflow-x:auto; -webkit-overflow-scrolling:touch;
            scroll-snap-type:x proximity; padding-bottom:8px; margin-top:.5rem; }
        .carousel-cards::-webkit-scrollbar { height:6px; }
        .carousel-cards::-webkit-scrollbar-thumb { background:#414758; border-radius:3px; }
        .carousel-card { flex:0 0 42%; scroll-snap-align:start; }
        /* Top 10 table */
        .top10-table { width:100%; border-collapse:collapse; font-size:13px; }
        .top10-table td { padding:6px 8px; border-bottom:1px solid #2a2f3d; color:#ddd; }
        .top10-table td:first-child { color:#ffc83d; font-weight:600; width:28px; }
        .top10-table td:first-child { color:#ffc83d; font-weight:600; width:28px; }	
		.subproduct {width:80% !important;}
        .top10-table td:last-child { text-align:right; color:#aeb5c4; }
        .size-row:hover { background:rgba(255,200,61,0.05); }
        .size-row.open { background:rgba(255,200,61,0.08); }
    </style>
</head>
<body>
<div class="wrap">

    <!-- Header -->
    <div class="d-flex justify-content-between align-items-start mb-3">
        <div>
		<img src="kajaria-white-logo.png">
            <h2 class="mb-0">
                <?php if ($multiStore && !empty($storeFilter)): ?>
                    <a href="?<?= $raw_source1 ? 'source1=' . urlencode($raw_source1) : 'source3=' . urlencode($raw_source3) ?>&range=<?= urlencode($range) ?>"
                       style="color:#fff;text-decoration:none;border-bottom:1px dashed #ffc83d;">
                        &#8592; Experience Centre Walk-ins Report
                    </a>
                <?php else: ?>
                    Experience Centre Walk-ins Report
                <?php endif; ?>
            </h2>
            <div style="color:#eee;font-size:14px;">
                <?php if ($multiStore && empty($storeFilter)): ?>
                    Combined Report - <?= count($accessibleStores) ?> Stores
                <?php else: ?>
                    <?= htmlspecialchars($currentStore['title'] ?? '') ?>
                    <?php if (!empty($currentStore['city'])): ?>
                        — <?= htmlspecialchars($currentStore['city']) ?>, <?= htmlspecialchars($currentStore['state'] ?? '') ?>
                    <?php endif; ?>
                <?php endif; ?>
                <?php if ($isMaster): ?><span class="badge bg-warning text-dark ms-2">Admin</span><?php endif; ?>
                <?= $cacheAge ?>
            </div>
        </div>
    </div>

    <!-- Filters -->
    <form method="GET" id="filterForm">
        <?php if ($raw_source1): ?><input type="hidden" name="source1" value="<?= htmlspecialchars($raw_source1) ?>"><?php endif; ?>
        <?php if ($raw_source3): ?><input type="hidden" name="source3" value="<?= htmlspecialchars($raw_source3) ?>"><?php endif; ?>

        <div class="row g-3 mb-3">

            <?php if ($multiStore): ?>
            <div class="col-12">
                <div class="glass p-3">
                    <div class="small text-secondary mb-2">Select a Showroom</div>
                    <select name="store" class="form-select" onchange="showLoader(); document.getElementById('filterForm').submit()">
                        <option value="">All Stores (Combined)</option>
                        <?php foreach ($accessibleStores as $s): ?>
                            <option value="<?= $s['source_table_id'] ?>" <?= ($storeFilter == $s['source_table_id']) ? 'selected' : '' ?>>
                                <?= htmlspecialchars($s['title']) ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>
            </div>
            <?php endif; ?>


            <div class="col-md-6">
                <div class="glass p-3">
                    <div class="small text-secondary mb-1">Date Range</div>
                    <select name="range" class="form-select mb-2"
                            onchange="showLoader(); document.getElementById('filterForm').submit()">
                        <?php foreach ($rangeLabels as $val => $label): ?>
                        <option value="<?= $val ?>"
                            <?= $range===$val ? 'selected' : '' ?>
                            <?= $val === 'ytd' ? 'disabled hidden style="display:none"' : '' ?>
                        ><?= $label ?></option>
                        <?php endforeach; ?>
                    </select>
                    <div class="sub mt-1">
                        <?= date('d M Y', strtotime($dateFrom)) ?>
                        <?= ($dateFrom !== $dateTo) ? ' - ' . date('d M Y', strtotime($dateTo)) : '' ?>
                    </div>
                </div>
            </div>
        </div>
    </form>

    <div class="row g-3">

        <!-- Total Quantity -->
        <div class="col-6">
            <div class="glass p-3 h-100 metric-card" onclick="openGraphModal('qty')" title="View trend">
                <div class="d-flex gap-3">
                    <div class="icon"><i class="bi bi-layers"></i></div>
                    <div>
                        <div>Total Quantity <small class="text-warning" style="font-size:.7rem;">&#9656; Trend</small></div>
                        <div class="metric"><?= fmtMetric($totalQty) ?></div>
                        <div class="sub"><?= $totalQty ?? '—' ?> sq.mtrs.</div>
                    </div>
                </div>
            </div>
        </div>

        <!-- Total Walk-ins — click card = graph, View Details = modal -->
        <div class="col-6">
            <div class="glass p-3 h-100 metric-card" onclick="openGraphModal('walkins')" style="cursor:pointer;" title="View trend">
                <div class="d-flex gap-3">
                    <div class="icon"><i class="bi bi-people-fill"></i></div>
                    <div>
                        <div>Total Walk-ins <small class="text-warning" style="font-size:.7rem;">&#9656; Trend</small></div>
                        <div class="metric"><?= $totalWalkins ?></div>
                    </div>
                </div>
            </div>
        </div>

        <!-- Projects -->
        <div class="col-6">
            <div class="glass p-3 h-100 metric-card" onclick="openModal('projects','Projects')">
                <div class="d-flex gap-3">
                    <div class="icon"><i class="bi bi-building"></i></div>
                    <div>
                        <div>Projects <span class="text-warning" style="font-size:.75rem;">View Details</span></div>
                        <div class="metric"><?= $projects ?></div>
                        <div class="sub"><?= pct($projects, $totalWalkins) ?> of Walk-ins</div>
                    </div>
                </div>
            </div>
        </div>

        <!-- Retails -->
        <div class="col-6">
            <div class="glass p-3 h-100 metric-card" onclick="openModal('retails','Retails')">
                <div class="d-flex gap-3">
                    <div class="icon"><i class="bi bi-shop"></i></div>
                    <div>
                        <div>Retails <span class="text-warning" style="font-size:.75rem;">View Details</span></div>
                        <div class="metric"><?= $retails ?></div>
                        <div class="sub"><?= pct($retails, $totalWalkins) ?> of Walk-ins</div>
                    </div>
                </div>
            </div>
        </div>

        <!-- Repeat Clients (before Look & Feel) -->
        <div class="col-6">
            <div class="glass p-3 h-100 metric-card" onclick="openModal('repeat','Repeat Clients')">
                <div class="d-flex gap-3">
                    <div class="icon"><i class="bi bi-arrow-repeat"></i></div>
                    <div>
                        <div>Repeat Clients <span class="text-warning" style="font-size:.75rem;">View Details</span></div>
                        <div class="metric"><?= $repeatClients ?></div>
                        <div class="sub"><?= pct($repeatClients, $totalWalkins) ?> of Walk-ins</div>
                    </div>
                </div>
            </div>
        </div>

        <!-- Look & Feel -->
        <div class="col-6">
            <div class="glass p-3 h-100">
                <div class="d-flex gap-3">
                    <div class="icon"><i class="bi bi-palette2"></i></div>
                    <div>
                        <div>Look and Feel</div>
                        <div class="metric"><?= $lookAndFeel ?></div>
                        <div class="sub"><?= pct($lookAndFeel, $totalWalkins) ?> of Walk-ins</div>
                    </div>
                </div>
            </div>
        </div>

        <?php if ($range === 'last12' && !empty($last12Months)): ?>
        <!-- Last 12 Months — month-by-month breakdown -->
        <div class="col-12">
            <div class="glass p-3">
                <div class="section-title mb-2"><strong>Monthly Breakdown — Last 12 Months</strong></div>
                <table class="top10-table">
                    <tr style="color:#ffc83d;font-size:12px;">
                        <td>Month</td><td style="text-align:right;">Walk-ins</td><td style="text-align:right;">Qty (sq.m)</td>
                    </tr>
                    <?php foreach($last12Months as $m): ?>
                    <tr>
                        <td style="color:#ddd;font-weight:normal;"><?= htmlspecialchars($m['month_label']) ?></td>
                        <td style="text-align:right;color:#ddd;"><?= (int)$m['walkins'] ?></td>
                        <td style="text-align:right;color:#ddd;"><?= $m['qty'] > 0 ? number_format(round($m['qty'])) : '—' ?></td>
                    </tr>
                    <?php endforeach; ?>
                </table>
            </div>
        </div>
        <?php endif; ?>

        <?php
        $isCombined = $multiStore && empty($storeFilter);
        ?>

        <?php if (!$isCombined): ?>
        <!-- SINGLE STORE VIEW -->

        <!-- Clients' Profession -->
        <div class="col-12">
            <div class="glass p-3">
                <?= sectionHeader("Clients' Profession", 'profession') ?>
                <?php
                $profRows2 = array_map(function($r){return['name'=>$r['profession'],'cnt'=>$r['cnt']];}, $professionRows);
                usort($profRows2, function($a,$b){ return strcmp($a['name'], $b['name']); });
                ?>
                <div class="carousel-cards">
                <?php foreach($profRows2 as $r): $init=mb_strtoupper(mb_substr($r['name'],0,1)); ?>
                    <div class="carousel-card"><div class="glass p-3 text-center">
                        <div class="avatar"><?= $init ?></div>
                        <div class="small"><?= htmlspecialchars($r['name']) ?></div>
                        <div class="fs-2 fw-bold"><?= $r['cnt'] ?></div>
                        <div class="sub">(<?= pct($r['cnt'],$totalWalkins) ?>)</div>
                    </div></div>
                <?php endforeach; ?>
                </div>
            </div>
        </div>


        <!-- Clients' from Reference -->
        <div class="col-12">
            <div class="glass p-3">
                <?= sectionHeader("Client Source", 'reference') ?>
                <?php $refRows2 = array_map(function($r){return['name'=>$r['ref_type'],'cnt'=>$r['cnt']];}, $referenceRows); ?>
                <div class="carousel-cards">
                <?php foreach($refRows2 as $r): $init=mb_strtoupper(mb_substr($r['name'],0,1)); ?>
                    <div class="carousel-card"><div class="glass p-3 text-center">
                        <div class="avatar"><?= $init ?></div>
                        <div class="small"><?= htmlspecialchars($r['name']) ?></div>
                        <div class="fs-2 fw-bold"><?= $r['cnt'] ?></div>
                        <div class="sub">(<?= pct($r['cnt'],$totalWalkins) ?>)</div>
                    </div></div>
                <?php endforeach; ?>
                <?php if(empty($refRows2)): ?><div class="text-secondary small ps-1">No data.</div><?php endif; ?>
                </div>
            </div>
        </div>
		
		
        <!-- Client Source - Have hidden for now -->
       <!-- <div class="col-12">
            <div class="glass p-3">
                <?= sectionHeader("Client Source", 'source') ?>
                <?php
                $srcRows2 = array_map(function($r){return['name'=>$r['src'],'cnt'=>$r['cnt']];}, $sourceRows);
                usort($srcRows2, function($a, $b) {
                    $aOnline = (stripos($a['name'], 'Online Advertisement') !== false);
                    $bOnline = (stripos($b['name'], 'Online Advertisement') !== false);
                    if ($aOnline && !$bOnline) return -1;
                    if (!$aOnline && $bOnline) return 1;
                    return strcmp($a['name'], $b['name']);
                });
                ?>
                <div class="carousel-cards">
                <?php foreach($srcRows2 as $r): $init=mb_strtoupper(mb_substr($r['name'],0,1)); ?>
                    <div class="carousel-card"><div class="glass p-3 text-center">
                        <div class="avatar"><?= $init ?></div>
                        <div class="small"><?= htmlspecialchars($r['name']) ?></div>
                        <div class="fs-2 fw-bold"><?= $r['cnt'] ?></div>
                        <div class="sub">(<?= pct($r['cnt'],$totalWalkins) ?>)</div>
                    </div></div>
                <?php endforeach; ?>
                </div>
            </div>
        </div> -->

        <!-- Top 10 Selling Sizes -->
        <?php if (!empty($topSizes)): ?>
        <div class="col-12">
            <div class="glass p-3">
                <div class="section-title">
                    <strong>Top 10 Preferred Sizes & Products</strong>
                    <!--<small class="text-secondary" style="font-size:11px;">tap a size to see products</small>-->
                </div>
                <table class="top10-table mt-2" id="sizesTable">
                    <!--<tr style="font-size:11px;color:#aeb5c4;">
                        <td></td>
                        <td>Size</td>
                        <td style="text-align:right;"></td>
                        <td style="width:20px;"></td>
                    </tr> -->
                <?php foreach($topSizes as $i=>$s):
                    $sizeKey = 'size_' . $i;
                    $sizeProds = $productsBySize[$s['size_desc']] ?? [];
                ?>
                    <!-- Size row — clickable -->
                    <tr class="size-row" onclick="toggleSizeAccordion('<?= $sizeKey ?>')"
                        style="cursor:pointer;">
                        <td><?= $i+1 ?></td>
                        <td><?= htmlspecialchars($s['size_desc']) ?></td>
                        <td style="text-align:right;"><!-- <?= number_format($s['order_count']) ?>--></td>
                        <td style="text-align:right;color:#ffc83d;" id="arrow_<?= $sizeKey ?>">&#9654;</td>
                    </tr>
                    <!-- Accordion row — hidden by default -->
                    <tr id="<?= $sizeKey ?>" style="display:none;">
                        <td colspan="4" style="padding:6px 8px 10px 28px;">
                            <?php if (!empty($sizeProds)): ?>
                            <table style="width:100%;border-collapse:collapse;font-size:12px;table-layout:fixed;">
                                <colgroup>
                                    <col style="width:auto;">
                                    <col style="width:20px;">
                                </colgroup>
                                <tr style="color:#aeb5c4;font-size:11px;">
                                    <td style="padding:4px 0;text-align:left;">Product Name</td>
                                    <td style="text-align:right;padding:4px 0;">Orders</td>
                                </tr>
                                <?php foreach($sizeProds as $j=>$sp): ?>
                                <tr style="border-top:1px solid #2a2f3d;">
                                    <td class="subproduct" style="padding:5px 4px 5px 0;word-break:break-word;text-align:left;">
                                        <span style="color:#ddd;"><?= htmlspecialchars($sp['name']) ?></span>
                                        <?php if (!empty($sp['series'])): ?>
                                        <br><small style="color:#c3c4c7;font-size:10px;"><?= htmlspecialchars($sp['series']) ?></small>
                                        <?php endif; ?>
                                    </td>
                                    <td style="text-align:right;color:#ffc83d;font-weight:600;vertical-align:middle;"><?= number_format($sp['cnt']) ?></td>
                                </tr>
                                <?php endforeach; ?>
                            </table>
                            <?php else: ?>
                            <span style="color:#aeb5c4;font-size:12px;">No product data available.</span>
                            <?php endif; ?>
                        </td>
                    </tr>
                <?php endforeach; ?>
                </table>
            </div>
        </div>
        <?php endif; ?>

        <!-- Top 10 Selling Products -->
        <?php if (!empty($topProducts)): ?>
        <div class="col-12" style="display:none;">
            <div class="glass p-3">
                <div class="section-title"><strong>Top 10 Preferred Products</strong></div>
                <table class="top10-table mt-2">
                    <tr style="font-size:11px;color:#aeb5c4;">
                        <td></td><td></td><td></td>
                        <td style="text-align:right;display:none;">Times Ordered</td>
                    </tr>
                <?php foreach($topProducts as $i=>$p): ?>
                    <tr>
                        <td><?= $i+1 ?></td>
                        <td>
                            <?= htmlspecialchars($p['product_name']) ?>
                            <?php if (!empty($p['series_desc'])): ?>
                            <br><small style="color:#c3c4c7;font-size:10px;"><?= htmlspecialchars($p['series_desc']) ?></small>
                            <?php endif; ?>
                        </td>
                        <td style="color:#aeb5c4;font-size:11px;white-space:nowrap;"><?= htmlspecialchars($p['size_desc'] ?? '') ?></td>
                        <td style="text-align:right;display:none;"><?= number_format($p['order_count']) ?></td>
                    </tr>
                <?php endforeach; ?>
                </table>
            </div>
        </div>
        <?php endif; ?>

        <!-- Referred to Sales Team (carousel) -->
        <div class="col-12">
            <div class="glass p-3">
                <?= sectionHeader('Referred to Sales Team', 'referred') ?>
                <div class="carousel-cards">
                <?php foreach($referredRows as $r): $init=mb_strtoupper(mb_substr($r['name'],0,1)); ?>
                    <div class="carousel-card"><div class="glass p-3 text-center">
                        <div class="avatar"><?= $init ?></div>
                        <div class="small"><?= htmlspecialchars($r['name']) ?></div>
                        <div class="fs-2 fw-bold"><?= $r['cnt'] ?></div>
                        <div class="sub">(<?= pct($r['cnt'],$totalWalkins) ?>)</div>
                    </div></div>
                <?php endforeach; ?>
                <?php if(empty($referredRows)): ?><div class="text-secondary small ps-1">No data.</div><?php endif; ?>
                </div>
            </div>
        </div>

        <!-- Attended by Employee (carousel) -->
        <div class="col-12">
            <div class="glass p-3">
                <?= sectionHeader('Attended by Employee', 'attended') ?>
                <div class="carousel-cards">
                <?php foreach($attendedRows as $r): $init=mb_strtoupper(mb_substr($r['name'],0,1)); ?>
                    <div class="carousel-card"><div class="glass p-3 text-center">
                        <div class="avatar"><?= $init ?></div>
                        <div class="small"><?= htmlspecialchars($r['name']) ?></div>
                        <div class="fs-2 fw-bold"><?= $r['cnt'] ?></div>
                        <div class="sub">(<?= pct($r['cnt'],$totalWalkins) ?>)</div>
                    </div></div>
                <?php endforeach; ?>
                <?php if(empty($attendedRows)): ?><div class="text-secondary small ps-1">No data.</div><?php endif; ?>
                </div>
            </div>
        </div>


        <!-- Outstation Clients -->
        <div class="col-6 metric-card" onclick="openModal('outstation','Outstation Clients')">
            <div class="glass p-3 text-center h-100">
                <div>Outstation Clients <span class="text-warning" style="font-size:.75rem;"><br>View Details</span></div>
                <div class="metric" style="font-size:2rem"><?= $outstation ?></div>
                <div class="sub"><?= pct($outstation, $totalWalkins) ?></div>
            </div>
        </div>

        <!-- Interested in Kerovit -->
        <div class="col-6 metric-card" onclick="openModal('kerovit','Interested in Kerovit')">
            <div class="glass p-3 text-center h-100">
                <div>Interested in Kerovit <span class="text-warning" style="font-size:.75rem;"><br>View Details</span></div>
                <div class="metric" style="font-size:2rem"><?= $kerovit ?></div>
                <div class="sub"><?= pct($kerovit, $totalWalkins) ?></div>
            </div>
        </div>

        <?php else: ?>
        <!-- COMBINED REPORT VIEW -->

        <!-- Clients' Profession — alphabetical order -->
        <div class="col-12">
            <div class="glass p-3">
                <?= sectionHeader("Clients' Profession", 'profession') ?>
                <?php
                $profRows2 = array_map(function($r){return['name'=>$r['profession'],'cnt'=>$r['cnt']];}, $professionRows);
                usort($profRows2, function($a,$b){ return strcmp($a['name'], $b['name']); });
                ?>
                <div class="carousel-cards">
                <?php foreach($profRows2 as $r): $init=mb_strtoupper(mb_substr($r['name'],0,1)); ?>
                    <div class="carousel-card"><div class="glass p-3 text-center">
                        <div class="avatar"><?= $init ?></div>
                        <div class="small"><?= htmlspecialchars($r['name']) ?></div>
                        <div class="fs-2 fw-bold"><?= $r['cnt'] ?></div>
                        <div class="sub">(<?= pct($r['cnt'],$totalWalkins) ?>)</div>
                    </div></div>
                <?php endforeach; ?>
                <?php if(empty($profRows2)): ?><div class="text-secondary small ps-1">No data.</div><?php endif; ?>
                </div>
            </div>
        </div>

        <!-- QR Standee — only if has data -->
        <?php if ($qrStandeeCount > 0): ?>
        <div class="col-6">
            <div class="glass p-3 text-center h-100">
                <div>QR Standee Scans</div>
                <div class="metric" style="font-size:2rem"><?= $qrStandeeCount ?></div>
                <div class="sub"><?= pct($qrStandeeCount, $totalWalkins) ?></div>
            </div>
        </div>
        <?php endif; ?>

        <!-- Client Source (renamed from Clients' from Reference) -->
        <div class="col-12">
            <div class="glass p-3">
                <?= sectionHeader("Client Source", 'reference') ?>
                <?php
                $refRows2 = array_map(function($r){return['name'=>$r['ref_type'],'cnt'=>$r['cnt']];}, $referenceRows);
                // Sort: Online Advertisement first, rest alphabetically
                usort($refRows2, function($a, $b) {
                    $aOnline = (stripos($a['name'], 'Online Advertisement') !== false);
                    $bOnline = (stripos($b['name'], 'Online Advertisement') !== false);
                    if ($aOnline && !$bOnline) return -1;
                    if (!$aOnline && $bOnline) return 1;
                    return strcmp($a['name'], $b['name']);
                });
                ?>
                <div class="carousel-cards">
                <?php foreach($refRows2 as $r): $init=mb_strtoupper(mb_substr($r['name'],0,1)); ?>
                    <div class="carousel-card"><div class="glass p-3 text-center">
                        <div class="avatar"><?= $init ?></div>
                        <div class="small"><?= htmlspecialchars($r['name']) ?></div>
                        <div class="fs-2 fw-bold"><?= $r['cnt'] ?></div>
                        <div class="sub">(<?= pct($r['cnt'],$totalWalkins) ?>)</div>
                    </div></div>
                <?php endforeach; ?>
                <?php if(empty($refRows2)): ?><div class="text-secondary small ps-1">No data.</div><?php endif; ?>
                </div>
            </div>
        </div>

        <!-- Interested in Kerovit (combined view) -->
        <div class="col-6 metric-card" onclick="openModal('kerovit','Interested in Kerovit')">
            <div class="glass p-3 text-center h-100">
                <div>Interested in Kerovit <span class="text-warning" style="font-size:.75rem;"><br>View Details</span></div>
                <div class="metric" style="font-size:2rem"><?= $kerovit ?></div>
                <div class="sub"><?= pct($kerovit, $totalWalkins) ?></div>
            </div>
        </div>

        <?php endif; ?>

        <!-- Store Details — SAP Code & UID -->
        <?php if (!$multiStore || !empty($storeFilter)): ?>
        <div class="col-12">
            <div class="glass p-3">
                <div class="small text-secondary mb-2">Store Details</div>
                <div class="row text-center">
                    <div class="col border-end">
                        <div class="sub">SAP Code</div>
                        <div class="text-warning fs-5 fw-bold"><?= htmlspecialchars($currentStore['sap_code'] ?? '—') ?></div>
                    </div>
                    <div class="col">
                        <div class="sub">UID</div>
                        <div class="text-warning fs-5 fw-bold"><?= htmlspecialchars($currentStore['uid'] ?? '—') ?></div>
                    </div>
                </div>
            </div>
        </div>
        <?php endif; ?>
    </div>
</div>

<!-- Page Loader -->
<div id="pageLoader">
    <div class="loader-spinner mb-3"></div>
    <div style="color:#aeb5c4;font-size:.9rem;">Loading report...</div>
</div>

<!-- ── Detail Modal ───────────────────────────────────────── -->
<div class="modal fade" id="detailModal" tabindex="-1">
    <div class="modal-dialog modal-xl modal-dialog-scrollable">
        <div class="modal-content">
            <div class="modal-header py-2">
                <div>
                    <h6 class="modal-title text-warning mb-0" id="modalTitle">Details</h6>
                    <div class="text-secondary" style="font-size:12px;" id="modalCount"></div>
                </div>
                <button type="button" class="btn-close btn-close-white" data-bs-dismiss="modal"></button>
            </div>
            <div class="modal-body p-0">
                <div id="detailEmpty" class="text-center text-secondary py-4" style="display:none;">
                    <i class="bi bi-inbox fs-1 d-block mb-2"></i>No records found.
                </div>
                <table class="table table-dark-custom mb-0" id="detailTable" style="width:100%">
                    <thead>
                        <tr>
                            <th>Order No.</th>
                            <th>Date</th>
                            <th>Customer</th>
                            <th>Profession</th>
                            <th>Attended By</th>
                            <th>Referred To</th>
                            <th>Referral</th>
                            <?php if ($multiStore && empty($storeFilter)): ?><th>Showroom</th><?php endif; ?>
                            <th>PDF</th>
                        </tr>
                    </thead>
                    <tbody id="detailBody"></tbody>
                </table>
            </div>
        </div>
    </div>
</div>

<!-- CSS -->
<link rel="stylesheet" href="https://cdn.datatables.net/1.13.8/css/dataTables.bootstrap5.min.css">
<link rel="stylesheet" href="https://cdn.datatables.net/responsive/2.5.0/css/responsive.bootstrap5.min.css">

<!-- JS — load order matters: jQuery first, then Bootstrap, then DataTables -->
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"></script>
<script src="https://code.jquery.com/jquery-3.7.1.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 src="https://cdn.datatables.net/responsive/2.5.0/js/dataTables.responsive.min.js"></script>
<script src="https://cdn.datatables.net/responsive/2.5.0/js/responsive.bootstrap5.min.js"></script>

<script>
var dtInstance   = null;
var AJAX_BASE    = 'get_detail_data.php';
var STORES_PARAM = <?= json_encode($storeInList) ?>;
var DATE_FROM    = <?= json_encode($dateFrom) ?>;
var DATE_TO      = <?= json_encode($dateTo) ?>;
var WEB_IDS      = <?= json_encode(implode(',', array_column($activeStores, 'web_id'))) ?>;
var IS_COMBINED  = <?= json_encode($multiStore && empty($storeFilter)) ?>;

function openModal(section, title) {

    var tbody = document.getElementById('detailBody');
    var empty = document.getElementById('detailEmpty');
    var table = document.getElementById('detailTable');

    document.getElementById('modalTitle').textContent = title;
    document.getElementById('modalCount').textContent = 'Loading...';

    if (dtInstance) { dtInstance.destroy(); dtInstance = null; }
    tbody.innerHTML = '';
    empty.style.display = 'none';
    table.style.display = 'none';

    // Show modal immediately, fetch data in background
    var modalEl = document.getElementById('detailModal');
    var modal   = bootstrap.Modal.getInstance(modalEl) || new bootstrap.Modal(modalEl);
    modal.show();

    var url = AJAX_BASE + '?section=' + section
        + '&stores='    + encodeURIComponent(STORES_PARAM)
        + '&date_from=' + encodeURIComponent(DATE_FROM)
        + '&date_to='   + encodeURIComponent(DATE_TO)
        + '&web_ids='   + encodeURIComponent(WEB_IDS);

    fetch(url)
        .then(function(r) {
            if (!r.ok) throw new Error('HTTP ' + r.status + ': ' + r.statusText);
            return r.text();
        })
        .then(function(text) {
            var rows;
            try { rows = JSON.parse(text); } catch(e) {
                console.error('JSON parse error:', e, 'Response:', text.substring(0, 200));
                document.getElementById('modalCount').textContent = 'Error: Invalid response';
                return;
            }
            if (!Array.isArray(rows)) {
                console.error('Unexpected response:', rows);
                rows = [];
            }
            document.getElementById('modalCount').textContent = rows.length + ' record(s)';

            if (rows.length === 0) {
                empty.style.display = 'block';
                table.style.display = 'none';
                return;
            }

            empty.style.display = 'none';
            table.style.display = '';

            rows.forEach(function(r) {
            var pdf      = r.pdf_url
                ? '<a href="' + r.pdf_url + '" target="_blank" class="text-warning" onclick="event.stopPropagation();"><i class="bi bi-file-earmark-pdf"></i> PDF</a>'
                : '—';
            var attended = r.attended_name ? r.attended_name + (r.attended_mobile ? '<br><small style="opacity:.7">' + r.attended_mobile + '</small>' : '') : '—';
            var referred = r.referred_name ? r.referred_name + (r.referred_mobile ? '<br><small style="opacity:.7">' + r.referred_mobile + '</small>' : '') : '—';
            var referral = r.referral_type ? r.referral_type + (r.referred_from   ? '<br><small style="opacity:.7">' + r.referred_from   + '</small>' : '') : '—';

            var row = '<tr>'
                + '<td>' + (r.order_number  || '—') + '</td>'
                + '<td>' + (r.order_date    || '—') + '</td>'
                + '<td>' + (r.customer_name || '—') + '</td>'
                + '<td>' + (r.profession    || '—') + '</td>'
                + '<td>' + attended + '</td>'
                + '<td>' + referred + '</td>'
                + '<td>' + referral + '</td>';

            if (IS_COMBINED) {
                row += '<td>' + (r.store_name || '—') + '</td>';
            }
            row += '<td>' + pdf + '</td></tr>';
            tbody.innerHTML += row;
            });

            // Init DataTable after data is rendered
            dtInstance = $('#detailTable').DataTable({
                responsive:  true,
                pageLength:  25,
                lengthMenu:  [[10, 25, 50, -1], ['10', '25', '50', 'All']],
                order:       [[3, 'desc']],
                columnDefs: [
                    { targets: [0,1,2],  responsivePriority: 1 },
                    { targets: [3],      responsivePriority: 2 },
                    { targets: [4,5,6],  responsivePriority: 3, orderable: false },
                    { targets: IS_COMBINED ? [7] : [],  responsivePriority: 4, orderable: false },
                    { targets: IS_COMBINED ? [8] : [7], responsivePriority: 5, orderable: false }
                ],
                language: {
                    search:       'Search:',
                    lengthMenu:   'Show _MENU_ records',
                    zeroRecords:  'No matching records found',
                    info:         'Showing _START_ to _END_ of _TOTAL_ records',
                    infoEmpty:    'No records available',
                    infoFiltered: '(filtered from _MAX_ total records)'
                }
            });

            // Clicking any td (not a link) triggers the dtr-control toggle
            $('#detailTable tbody').on('click', 'td:not(.dtr-control)', function (e) {
                if ($(e.target).closest('a').length) return; // let PDF links work normally
                $(this).closest('tr').find('td.dtr-control').trigger('click');
            });
            $('#detailTable tbody').css('cursor', 'pointer');

        })
        .catch(function(err) {
            document.getElementById('modalCount').textContent = 'Error loading data';
            console.error('Detail fetch error:', err);
        });
}

// View Details click handler
document.addEventListener('click', function(e) {
    var el = e.target.closest('.view-details');
    if (!el) return;
    e.stopPropagation();
    var section = el.getAttribute('data-section');
    var titles = {
        walkins:    'Total Walk-ins',    projects:  'Projects',
        retails:    'Retails',           look_feel: 'Look and Feel',
        repeat:     'Repeat Clients',    referred:  'Referred to Sales Team',
        attended:   'Attended by Employee',
        profession: "Clients Profession",
        reference:  "Clients from Reference",
        source:     'Client Source',     outstation:'Outstation Clients',
        kerovit:    'Interested in Kerovit'
    };
    openModal(section, titles[section] || section);
});

function showLoader() {
    document.getElementById('pageLoader').classList.add('show');
}

function toggleSizeAccordion(key) {
    var row   = document.getElementById(key);
    var arrow = document.getElementById('arrow_' + key);
    var sizeRow = arrow ? arrow.closest('tr') : null;

    if (!row) return;

    var isOpen = row.style.display !== 'none';

    // Close all open accordions first
    document.querySelectorAll('[id^="size_"]').forEach(function(el) {
        el.style.display = 'none';
    });
    document.querySelectorAll('[id^="arrow_size_"]').forEach(function(el) {
        el.textContent = '►'; // right arrow
    });
    document.querySelectorAll('.size-row').forEach(function(el) {
        el.classList.remove('open');
    });

    // Toggle clicked one
    if (!isOpen) {
        row.style.display = '';
        if (arrow) arrow.textContent = '▼'; // down arrow
        if (sizeRow) sizeRow.classList.add('open');
    }
}

document.querySelectorAll('input[type="date"]').forEach(function(input) {
    input.addEventListener('click', function() {
        if (typeof this.showPicker === 'function') {
            try { this.showPicker(); } catch(e) {}
        }
    });
});
</script>

<?php renderGraphModal(); ?>
</body>
</html>