<?php
// market_insights_dashboard.php
// Single-file Market Insights QBR dashboard (API + frontend)
// Requirements: connect.php must set $pdo (PDO). Session auth enforced.

session_start();
require_once __DIR__ . '/functionReports.php';

// Check authentication - support both main system and client portal
$isMainSystemUser = isset($_SESSION['empId']) && isset($_SESSION['empType']) && $_SESSION['empType'] === 'S';
$isClientPortalAdmin = isset($_SESSION['clientId']);

// If client portal user, verify they have access (admin OR insights permission)
if($isClientPortalAdmin && !$isMainSystemUser) {
   // require_once __DIR__ . '/connect.php';
//    require_once __DIR__ . '/function.php';
    
    try {
        $stmt = $pdo->prepare("SELECT userRole, permissions FROM tblclient WHERE clientId = ?");
        $stmt->execute([$_SESSION['clientId']]);
        $user = $stmt->fetch();
        
        // Check if user is admin OR has insights permission
        $hasAccess = false;
        if($user['userRole'] === 'admin') {
            $hasAccess = true;
        } else {
            // Check insights permission
            $permissions = json_decode($user['permissions'] ?? '{}', true);
            if(isset($permissions['insights']) && $permissions['insights'] === true) {
                $hasAccess = true;
            }
        }
        
        if(!$hasAccess) {
            header('Location: dashboard.php?error=access_denied');
            exit;
        }
    } catch(PDOException $e) {
        header('Location: dashboard.php?error=access_denied');
        exit;
    }
}

// If neither authenticated, redirect to login
if(!$isMainSystemUser && !$isClientPortalAdmin) {
    header('Location: login.php');
    exit;
}

function jsonResponse($data){ header('Content-Type: application/json; charset=utf-8'); echo json_encode($data); exit; }

// // Sanitize and validate date inputs
// function sanitizeDate($d, $fallback){ 
//     // Check if date is valid format YYYY-MM-DD
//     if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $d)) {
//         // Additional check: not 0000-00-00
//         if ($d !== '0000-00-00') {
//             return $d;
//         }
//     }
//     return $fallback; 
// }

// Format date for display, handling NULL and invalid dates
function formatDateForDisplay($date) {
    if (empty($date) || $date === '0000-00-00' || $date === '0000-00-00 00:00:00') {
        return 'N/A';
    }
    // If it's a datetime, extract just the date part
    if (strlen($date) > 10) {
        $date = substr($date, 0, 10);
    }
    return $date;
}

// Check if a date is valid (not NULL, not 0000-00-00)
function isValidDate($date) {
    return !empty($date) && $date !== '0000-00-00' && $date !== '0000-00-00 00:00:00';
}

// SQL helper: Generate COALESCE for date fields to handle NULL/0000-00-00
function sqlSafeDate($fieldName, $fallback = 'NULL') {
    return "CASE 
                WHEN {$fieldName} IS NULL OR {$fieldName} = '0000-00-00' OR {$fieldName} = '0000-00-00 00:00:00' 
                THEN {$fallback}
                ELSE {$fieldName}
            END";
}

$api = isset($_GET['api']);
$action = $_GET['action'] ?? null;

// NOTE: Now pulling live data from tblpatientdetails and related tables instead of marketInsights table
// Using the same logic as detailed_report_export_kabi.php

// Build the base query for live data (same as export file)
// added filter for enrollment form rec date from 2025-07-26 - RJ
function buildBaseQuery() {
    return "WITH LatestIndication AS (
                SELECT *
                FROM (
                    SELECT *,
                           ROW_NUMBER() OVER (PARTITION BY pId, fileId ORDER BY id DESC) AS rn
                    FROM tblindication
                    WHERE isDeleted = 'N'
                ) t
                WHERE rn = 1
            )
            SELECT 
                tblpatient.id,
                tblpatient.gender, 
                tblpatient.dob, 
                tblpatient.province, 
                tblpatient.postalCode, 
                tblpatient.createdDate,
                tblpatientdetails.enrolmentFormReceiveDate,
                tblpatientdetails.fileId AS patientFileId, 
                tblpatientdetails.patientId AS patientuId,
                tblpatientdetails.innomarPatientId,
                tblpatientdetails.isTransferOut,
                tblpatientdetails.medicationId AS medicationId, 
                tblwcpart5.patientWeight, 
                tblwcpart5.dopd,
                tblwcpart5.patientDrugName,
                tblwcpart5.indication, 
                tblwcpart5.currentFrequency, 
                tblwcpart5.dosage, 
                tblwcpart5.patientWeightUnit,
                tblwcpart5.prescriberName,
                wc1.wcFirstAttempt, 
                wc1.wcCompletionDate,  
                (Select title from tblwelcomecallstatus where id=wc1.wcStatus) as wc1Status,
                i.ipmctst,
                i.isHighDoseICS,
                i.isexacerbations1,
                i.isexacerbations2,
                i.eosCount,
                i.fenoLevel,
                i.scWeeks,
                i.scDosing,
                i.scWeeks2,
                i.scDosing2,
                i.totalIgE,
                i.otherAsthmaScore,
                i.nos,
                i.lastSurgeryDate,
                i.isSurgeryRequired,
                i.rationale,
                i.isSystemic,
                i.otherCrsScore,
                i.treatment,
                i.dose,
                i.dateFrom,
                i.dateTo,
                i.coexistingDiseases,
                i.testScore,
                i.isTC,
                i.tcvalue,
                i.isTCI,
                i.isTacrolimus,
                i.tracolimusWeeks,
                i.tracolimusDosing,
                i.isPime,
                i.pimeWeeks,
                i.pimeDosing,
                i.isSystemic3,
                i.systemicWeeks3,
                i.systemicDosing3,
                i.isImmuno,
                i.isMethotrexate,
                i.methotrexateWeeks,
                i.methotrexateDosing,
                i.isCyclosporine,
                i.cyclosporineWeeks,
                i.cyclosporineDosing,
                i.isOther,
                i.otherName,
                i.otherWeeks,
                i.otherDosing,
                i.isPhoto,
                i.photoWeeks,
                i.notAccessibleReason,
                i.isNoAccessToInfo,
                i.isHighDosePPI,
                i.isTopicalCorticosteroids,
                i.additionalRelevantMedication,
                CASE 
                    WHEN (tblpatientdetails.patientId like '%IDAC%') THEN 'Idacio'
                    WHEN (tblpatientdetails.patientId like '%TYEN%') THEN 'Tyenne'
                    WHEN (tblpatientdetails.patientId like '%OTUL%') THEN 'Otulfi'
                END AS Therapy
            FROM tblpatientdetails
            LEFT JOIN tblpatient 
                ON tblpatient.id = tblpatientdetails.pId
            LEFT JOIN tblwcpart1 AS wc1 
                ON tblpatientdetails.pId = wc1.pId AND tblpatientdetails.fileId = wc1.fileId
            LEFT JOIN tblwcpart5 
                ON tblpatientdetails.pId = tblwcpart5.pId AND tblpatientdetails.fileId = tblwcpart5.fileId
            LEFT JOIN LatestIndication AS i 
                ON tblpatientdetails.pId = i.pId AND tblpatientdetails.fileId = i.fileId
            WHERE tblpatient.isDeleted = 'N' 
              AND tblpatientdetails.isDeleted = 'N' 
              AND tblpatientdetails.pspId = '32'
              AND tblpatientdetails.medicationId != '0'
              AND tblpatientdetails.enrolmentFormReceiveDate >= '2025-07-26'
              AND (tblpatientdetails.isTransferOut != 'Y' OR tblpatientdetails.isTransferOut is NULL)";
}


if ($api) {
    try {
        // Get therapy filter parameter
        $therapy = $_GET['therapy'] ?? 'ALL';
        $therapyCondition = '';
        $therapyParams = [];
        
        if ($therapy !== 'ALL' && !empty($therapy)) {
            $therapyCondition = ' AND `Therapy` = :therapy';
            $therapyParams[':therapy'] = $therapy;
        }
        
        if ($action === 'get_therapies') {
            // Get available therapy options from live data
            $sql = "SELECT DISTINCT 
                        CASE 
                            WHEN (tblpatientdetails.patientId like '%IDAC%') THEN 'Idacio'
                            WHEN (tblpatientdetails.patientId like '%TYEN%') THEN 'Tyenne'
                            WHEN (tblpatientdetails.patientId like '%OTUL%') THEN 'Otulfi'
                        END AS Therapy
                    FROM tblpatientdetails
                    LEFT JOIN tblpatient ON tblpatient.id = tblpatientdetails.pId
                    WHERE tblpatient.isDeleted = 'N' 
                      AND tblpatientdetails.isDeleted = 'N' 
                      AND tblpatientdetails.pspId = '32'
                      AND tblpatientdetails.medicationId != '0'
                      AND tblpatientdetails.isTransferOut != 'Y'
                    HAVING Therapy IS NOT NULL
                    ORDER BY Therapy";
            $stmt = $pdo->query($sql);
            $therapies = $stmt->fetchAll(PDO::FETCH_COLUMN);
            jsonResponse(['success'=>true,'therapies'=>$therapies]);
        }
        
        if ($action === 'overview') {
            // Basic KPIs from live data - simplified without buildBaseQuery
            $from = sanitizeDate($_GET['from'] ?? '2025-07-26', '2025-07-26');
            $to   = sanitizeDate($_GET['to']   ?? '2026-01-12', '2026-01-12');

            $sql = "SELECT
                        COUNT(DISTINCT CONCAT(pd.pId, '-', pd.fileId)) as total_patients,
                        COUNT(DISTINCT CASE WHEN r.trtStatus = 'On Drug' OR ps.StatusName = 'On Drug' THEN CONCAT(pd.pId, '-', pd.fileId) END) as on_drug,
                        COUNT(DISTINCT CASE WHEN r.trtStatus = 'Dropout' OR ps.StatusName = 'Dropout/Was Never on Drug' THEN CONCAT(pd.pId, '-', pd.fileId) END) as dropout
                    FROM tblpatientdetails pd
                    INNER JOIN tblpatient p ON p.id = pd.pId
                    LEFT JOIN tblreimbursement r ON pd.pId = r.pId AND pd.fileId = r.fileId
                    LEFT JOIN tblpatientstatus ps ON r.patientStatus = ps.StatusID AND ps.isDeleted = 'N'
                    WHERE p.isDeleted = 'N' 
                      AND pd.isDeleted = 'N' 
                      AND pd.pspId = '32'
                      AND pd.medicationId != '0'
                      AND (pd.isTransferOut != 'Y' OR pd.isTransferOut IS NULL)
                      AND COALESCE(pd.enrolmentFormReceiveDate, p.createdDate) BETWEEN :from AND :to $therapyCondition";
            $stmt = $pdo->prepare($sql);
            $params = array_merge([':from'=>$from,':to'=>$to], $therapyParams);
            $stmt->execute($params);
            $kpis = $stmt->fetch(PDO::FETCH_ASSOC);
            jsonResponse(['success'=>true,'kpis'=>$kpis,'from'=>$from,'to'=>$to]);
        }

        if ($action === 'gender_ratio') {
            $baseQuery = buildBaseQuery();
            $sql = "WITH PatientData AS ($baseQuery)
                    SELECT gender as Gender, COUNT(*) as cnt 
                    FROM PatientData 
                    WHERE 1=1 $therapyCondition 
                    GROUP BY gender";
            $stmt = $pdo->prepare($sql);
            $stmt->execute($therapyParams);
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
            jsonResponse(['success'=>true,'data'=>$rows]);
        }

        if ($action === 'pharmacy_enrollments') {
            $limit = (int)($_GET['limit'] ?? 10);
            if ($limit <= 0) $limit = 10;
            $baseQuery = buildBaseQuery();
            $sql = "WITH PatientData AS ($baseQuery)
                    SELECT COALESCE(NULLIF(TRIM(r.pharmacyName),''),'Unknown') as pharmacy, COUNT(*) as cnt
                    FROM PatientData pd
                    LEFT JOIN tblreimbursement r ON pd.id = r.pId AND pd.patientFileId = r.fileId
                    WHERE 1=1 $therapyCondition
                    GROUP BY pharmacy
                    ORDER BY cnt DESC
                    LIMIT :lim";
            $stmt = $pdo->prepare($sql);
            $stmt->bindValue(':lim',$limit,PDO::PARAM_INT);
            foreach ($therapyParams as $key => $value) {
                $stmt->bindValue($key, $value);
            }
            $stmt->execute();
            jsonResponse(['success'=>true,'data'=>$stmt->fetchAll(PDO::FETCH_ASSOC)]);
        }

        if ($action === 'status_breakdown') {
            $baseQuery = buildBaseQuery();
            $sql = "WITH PatientData AS ($baseQuery)
                    SELECT COALESCE(NULLIF(TRIM(COALESCE(ps.StatusName, r.trtStatus)),''),'Unknown') as status, COUNT(*) as cnt
                    FROM PatientData pd
                    LEFT JOIN tblreimbursement r ON pd.id = r.pId AND pd.patientFileId = r.fileId
                    LEFT JOIN tblpatientstatus ps ON r.patientStatus = ps.StatusID AND ps.isDeleted = 'N'
                    WHERE 1=1 $therapyCondition
                    GROUP BY status";
            $stmt = $pdo->prepare($sql);
            $stmt->execute($therapyParams);
            jsonResponse(['success'=>true,'data'=>$stmt->fetchAll(PDO::FETCH_ASSOC)]);
        }

        if ($action === 'province_counts') {
            $baseQuery = buildBaseQuery();
            $sql = "WITH PatientData AS ($baseQuery)
                    SELECT COALESCE(NULLIF(TRIM(province),''),'Unknown') as province, COUNT(*) as cnt
                    FROM PatientData
                    WHERE 1=1 $therapyCondition
                    GROUP BY province
                    ORDER BY cnt DESC";
            $stmt = $pdo->prepare($sql);
            $stmt->execute($therapyParams);
            jsonResponse(['success'=>true,'data'=>$stmt->fetchAll(PDO::FETCH_ASSOC)]);
        }

        if ($action === 'physician_monthly_trends') {
            // built to mirror your physician_monthly_trends endpoint but using live data
            $from = sanitizeDate($_GET['from'] ?? date('Y-01-01'), date('Y-01-01'));
            $to   = sanitizeDate($_GET['to'] ?? date('Y-m-d'), date('Y-m-d'));
            $topN = (int)($_GET['top'] ?? 10); if ($topN <= 0) $topN = 10;

            $baseQuery = buildBaseQuery();
            // 1) top prescribers by enrollments in range
            $sqlTop = "WITH PatientData AS ($baseQuery)
                       SELECT COALESCE(NULLIF(TRIM(pd.prescriberName),''),'Unknown') AS prescriber, COUNT(*) AS tot
                       FROM PatientData pd
                       WHERE COALESCE(pd.enrolmentFormReceiveDate, pd.createdDate) BETWEEN :from AND :to $therapyCondition
                       GROUP BY prescriber
                       ORDER BY tot DESC
                       LIMIT :topn";
            $stmt = $pdo->prepare($sqlTop);
            $stmt->bindValue(':from',$from);
            $stmt->bindValue(':to',$to);
            $stmt->bindValue(':topn',$topN,PDO::PARAM_INT);
            foreach ($therapyParams as $key => $value) {
                $stmt->bindValue($key, $value);
            }
            $stmt->execute();
            $topRows = $stmt->fetchAll(PDO::FETCH_COLUMN,0);

            if (empty($topRows)) jsonResponse(['success'=>true,'months'=>[], 'prescribers'=>[],'message'=>'No data for range']);

            // months vector
            $start = new DateTime($from);
            $start->modify('first day of this month');
            $end = new DateTime($to);
            $end->modify('first day of next month');
            $months = [];
            $period = new DatePeriod($start, new DateInterval('P1M'), $end);
            foreach ($period as $dt) $months[] = $dt->format('Y-m');
            $reqFromYm = substr($from,0,7); $reqToYm = substr($to,0,7);
            $months = array_values(array_filter($months, function($m) use($reqFromYm,$reqToYm){ return $m >= $reqFromYm && $m <= $reqToYm; }));

            // 3) counts
            $inPlaceholders = [];
            $bindings = [':from'=>$from,':to'=>$to];
            foreach ($topRows as $i=>$name){ $ph=':p'.$i; $inPlaceholders[]=$ph; $bindings[$ph]=$name; }

            $sqlCounts = "WITH PatientData AS ($baseQuery)
                          SELECT COALESCE(NULLIF(TRIM(pd.prescriberName),''),'Unknown') AS prescriber,
                                 DATE_FORMAT(COALESCE(pd.enrolmentFormReceiveDate, pd.createdDate), '%Y-%m') AS ym,
                                 COUNT(*) AS cnt
                          FROM PatientData pd
                          WHERE COALESCE(pd.enrolmentFormReceiveDate, pd.createdDate) BETWEEN :from AND :to $therapyCondition
                            AND COALESCE(NULLIF(TRIM(pd.prescriberName),''),'Unknown') IN (".implode(',',$inPlaceholders).")
                          GROUP BY prescriber, ym";
            $stmt = $pdo->prepare($sqlCounts);
            foreach ($bindings as $k=>$v) $stmt->bindValue($k,$v);
            foreach ($therapyParams as $key => $value) {
                $stmt->bindValue($key, $value);
            }
            $stmt->execute();
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

            $data = [];
            foreach ($topRows as $name) $data[$name] = array_fill(0, count($months), 0);
            $monthIndex = array_flip($months);
            foreach ($rows as $r){ $pres=$r['prescriber']; $ym=$r['ym']; $cnt=(int)$r['cnt']; if(!isset($data[$pres])) continue; if(isset($monthIndex[$ym])) $data[$pres][$monthIndex[$ym]] = $cnt; }

            $outPres = [];
            foreach ($data as $pres=>$arr) $outPres[]=['prescriber'=>$pres,'data'=>$arr];

            jsonResponse(['success'=>true,'months'=>$months,'prescribers'=>$outPres,'from'=>$from,'to'=>$to]);
        }

        if ($action === 'enrollment_trends') {
            // Monthly enrollment trends - simplified query without buildBaseQuery
            $from = sanitizeDate($_GET['from'] ?? date('Y-01-01'), date('Y-01-01'));
            $to   = sanitizeDate($_GET['to'] ?? date('Y-m-d'), date('Y-m-d'));
            
            // Direct query from tblpatientdetails - no complex CTEs
            $sql = "SELECT 
                        DATE_FORMAT(COALESCE(pd.enrolmentFormReceiveDate, p.createdDate), '%Y-%m') as month,
                        COUNT(DISTINCT CONCAT(pd.pId, '-', pd.fileId)) as enrollments,
                        COUNT(DISTINCT CASE WHEN COALESCE(ps.StatusName, r.trtStatus) = 'On Drug' THEN CONCAT(pd.pId, '-', pd.fileId) END) as on_drug_count,
                        COUNT(DISTINCT CASE WHEN COALESCE(ps.StatusName, r.trtStatus) = 'Dropout/Was Never on Drug' THEN CONCAT(pd.pId, '-', pd.fileId) END) as dropout_count
                    FROM tblpatientdetails pd
                    INNER JOIN tblpatient p ON p.id = pd.pId
                    LEFT JOIN tblreimbursement r ON pd.pId = r.pId AND pd.fileId = r.fileId
                    LEFT JOIN tblpatientstatus ps ON r.patientStatus = ps.StatusID AND ps.isDeleted = 'N'
                    WHERE p.isDeleted = 'N' 
                      AND pd.isDeleted = 'N' 
                      AND pd.pspId = '32'
                      AND pd.medicationId != '0'
                      AND (pd.isTransferOut != 'Y' OR pd.isTransferOut IS NULL)
                      AND COALESCE(pd.enrolmentFormReceiveDate, p.createdDate) BETWEEN :from AND :to $therapyCondition
                    GROUP BY month
                    ORDER BY month";
            $stmt = $pdo->prepare($sql);
            $params = array_merge([':from'=>$from, ':to'=>$to], $therapyParams);
            $stmt->execute($params);
            jsonResponse(['success'=>true,'data'=>$stmt->fetchAll(PDO::FETCH_ASSOC)]);
        }

        if ($action === 'retention_analysis') {
            // Patient retention analysis by enrollment cohort from live data
            $from = sanitizeDate($_GET['from'] ?? date('2025-07-26'), date('2025-07-26'));
            $to   = sanitizeDate($_GET['to'] ?? date('Y-m-d'), date('Y-m-d'));
            
            $baseQuery = buildBaseQuery();
            $sql = "WITH PatientData AS ($baseQuery)
                    SELECT 
                        DATE_FORMAT(COALESCE(pd.enrolmentFormReceiveDate, pd.createdDate), '%Y-%m') as cohort_month,
                        COUNT(*) as total_enrolled,
                        SUM(CASE WHEN COALESCE(ps.StatusName, r.trtStatus) = 'On Drug' THEN 1 ELSE 0 END) as still_active,
                        ROUND(SUM(CASE WHEN COALESCE(ps.StatusName, r.trtStatus) = 'On Drug' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as retention_rate
                    FROM PatientData pd
                    LEFT JOIN tblreimbursement r ON pd.id = r.pId AND pd.patientFileId = r.fileId
                    LEFT JOIN tblpatientstatus ps ON r.patientStatus = ps.StatusID AND ps.isDeleted = 'N'
                    WHERE COALESCE(pd.enrolmentFormReceiveDate, pd.createdDate) BETWEEN :from AND :to $therapyCondition
                    GROUP BY cohort_month 
                    ORDER BY cohort_month DESC
                    LIMIT 12";
            $stmt = $pdo->prepare($sql);
            $params = array_merge([':from'=>$from,':to'=>$to], $therapyParams);
            $stmt->execute($params);
            jsonResponse(['success'=>true,'data'=>$stmt->fetchAll(PDO::FETCH_ASSOC)]);
        }

        if ($action === 'top_prescribers_performance') {
            // Top prescribers with detailed performance metrics from live data
            $from = sanitizeDate($_GET['from'] ?? date('2025-07-26'), date('2025-07-26'));
            $to   = sanitizeDate($_GET['to'] ?? date('Y-m-d'), date('Y-m-d'));
            $limit = (int)($_GET['limit'] ?? 15);
            
            $baseQuery = buildBaseQuery();
            $sql = "WITH PatientData AS ($baseQuery)
                    SELECT 
                        COALESCE(NULLIF(TRIM(pd.prescriberName),''),'Unknown') as prescriber,
                        COUNT(*) as total_enrollments,
                        SUM(CASE WHEN COALESCE(ps.StatusName, r.trtStatus) = 'On Drug' THEN 1 ELSE 0 END) as active_patients,
                        SUM(CASE WHEN COALESCE(ps.StatusName, r.trtStatus) = 'Dropout/Was Never on Drug' THEN 1 ELSE 0 END) as dropouts,
                        ROUND(SUM(CASE WHEN COALESCE(ps.StatusName, r.trtStatus) = 'On Drug' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as retention_rate,
                        COUNT(DISTINCT pd.province) as provinces_served
                    FROM PatientData pd
                    LEFT JOIN tblreimbursement r ON pd.id = r.pId AND pd.patientFileId = r.fileId
                    LEFT JOIN tblpatientstatus ps ON r.patientStatus = ps.StatusID AND ps.isDeleted = 'N'
                    WHERE COALESCE(pd.enrolmentFormReceiveDate, pd.createdDate) BETWEEN :from AND :to $therapyCondition
                    GROUP BY prescriber 
                    HAVING total_enrollments >= 3
                    ORDER BY total_enrollments DESC, retention_rate DESC
                    LIMIT :lim";
            $stmt = $pdo->prepare($sql);
            $params = array_merge([':from'=>$from,':to'=>$to,':lim'=>$limit], $therapyParams);
            $stmt->bindValue(':lim',$limit,PDO::PARAM_INT);
            foreach ($params as $key => $value) {
                if ($key !== ':lim') {
                    $stmt->bindValue($key, $value);
                }
            }
            $stmt->execute();
            jsonResponse(['success'=>true,'data'=>$stmt->fetchAll(PDO::FETCH_ASSOC)]);
        }

        if ($action === 'geographic_analysis') {
            // Geographic distribution and performance by province from live data
            $from = sanitizeDate($_GET['from'] ?? date('2025-07-26'), date('2025-07-26'));
            $to   = sanitizeDate($_GET['to'] ?? date('Y-m-d'), date('Y-m-d'));
            
            $baseQuery = buildBaseQuery();
            $sql = "WITH PatientData AS ($baseQuery)
                    SELECT 
                        COALESCE(NULLIF(TRIM(pd.province),''),'Unknown') as province,
                        COUNT(*) as total_patients,
                        SUM(CASE WHEN COALESCE(ps.StatusName, r.trtStatus) = 'On Drug' THEN 1 ELSE 0 END) as active_patients,
                        ROUND(SUM(CASE WHEN COALESCE(ps.StatusName, r.trtStatus) = 'On Drug' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as retention_rate,
                        COUNT(DISTINCT COALESCE(NULLIF(TRIM(pd.prescriberName),''),'Unknown')) as unique_prescribers,
                        COUNT(DISTINCT COALESCE(NULLIF(TRIM(r.pharmacyName),''),'Unknown')) as unique_pharmacies
                    FROM PatientData pd
                    LEFT JOIN tblreimbursement r ON pd.id = r.pId AND pd.patientFileId = r.fileId
                    LEFT JOIN tblpatientstatus ps ON r.patientStatus = ps.StatusID AND ps.isDeleted = 'N'
                    WHERE COALESCE(pd.enrolmentFormReceiveDate, pd.createdDate) BETWEEN :from AND :to $therapyCondition
                    GROUP BY province 
                    HAVING total_patients >= 5
                    ORDER BY total_patients DESC";
            $stmt = $pdo->prepare($sql);
            $params = array_merge([':from'=>$from,':to'=>$to], $therapyParams);
            $stmt->execute($params);
            jsonResponse(['success'=>true,'data'=>$stmt->fetchAll(PDO::FETCH_ASSOC)]);
        }

        if ($action === 'pharmacy_performance') {
            // Pharmacy performance metrics from live data
            $from = sanitizeDate($_GET['from'] ?? date('2025-07-26'), date('2025-07-26'));
            $to   = sanitizeDate($_GET['to'] ?? date('Y-m-d'), date('Y-m-d'));
            $limit = (int)($_GET['limit'] ?? 20);
            
            $baseQuery = buildBaseQuery();
            $sql = "WITH PatientData AS ($baseQuery)
                    SELECT 
                        COALESCE(NULLIF(TRIM(r.pharmacyName),''),'Unknown') as pharmacy,
                        COUNT(*) as total_enrollments,
                        SUM(CASE WHEN COALESCE(ps.StatusName, r.trtStatus) = 'On Drug' THEN 1 ELSE 0 END) as active_patients,
                        ROUND(SUM(CASE WHEN COALESCE(ps.StatusName, r.trtStatus) = 'On Drug' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as retention_rate,
                        COUNT(DISTINCT COALESCE(NULLIF(TRIM(pd.province),''),'Unknown')) as provinces_served
                    FROM PatientData pd
                    LEFT JOIN tblreimbursement r ON pd.id = r.pId AND pd.patientFileId = r.fileId
                    LEFT JOIN tblpatientstatus ps ON r.patientStatus = ps.StatusID AND ps.isDeleted = 'N'
                    WHERE COALESCE(pd.enrolmentFormReceiveDate, pd.createdDate) BETWEEN :from AND :to $therapyCondition
                    GROUP BY pharmacy 
                    HAVING total_enrollments >= 3
                    ORDER BY total_enrollments DESC, retention_rate DESC
                    LIMIT :lim";
            $stmt = $pdo->prepare($sql);
            $params = array_merge([':from'=>$from,':to'=>$to,':lim'=>$limit], $therapyParams);
            $stmt->bindValue(':lim',$limit,PDO::PARAM_INT);
            foreach ($params as $key => $value) {
                if ($key !== ':lim') {
                    $stmt->bindValue($key, $value);
                }
            }
            $stmt->execute();
            jsonResponse(['success'=>true,'data'=>$stmt->fetchAll(PDO::FETCH_ASSOC)]);
        }

        if ($action === 'quarterly_comparison') {
            // Quarter-over-quarter comparison from live data
            $from = sanitizeDate($_GET['from'] ?? date('2025-07-26'), date('2025-07-26'));
            $to   = sanitizeDate($_GET['to'] ?? date('Y-m-d'), date('Y-m-d'));
            
            $baseQuery = buildBaseQuery();
            $enrollDate = "COALESCE(pd.enrolmentFormReceiveDate, pd.createdDate)";
            $sql = "WITH PatientData AS ($baseQuery)
                    SELECT 
                        CONCAT('Q', QUARTER($enrollDate), ' ', YEAR($enrollDate)) as quarter,
                        COUNT(*) as enrollments,
                        SUM(CASE WHEN COALESCE(ps.StatusName, r.trtStatus) = 'On Drug' THEN 1 ELSE 0 END) as active_patients,
                        ROUND(SUM(CASE WHEN COALESCE(ps.StatusName, r.trtStatus) = 'On Drug' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as retention_rate,
                        COUNT(DISTINCT COALESCE(NULLIF(TRIM(pd.prescriberName),''),'Unknown')) as unique_prescribers
                    FROM PatientData pd
                    LEFT JOIN tblreimbursement r ON pd.id = r.pId AND pd.patientFileId = r.fileId
                    LEFT JOIN tblpatientstatus ps ON r.patientStatus = ps.StatusID AND ps.isDeleted = 'N'
                    WHERE $enrollDate BETWEEN :from AND :to $therapyCondition
                    GROUP BY YEAR($enrollDate), QUARTER($enrollDate)
                    ORDER BY YEAR($enrollDate), QUARTER($enrollDate)";
            $stmt = $pdo->prepare($sql);
            $params = array_merge([':from'=>$from,':to'=>$to], $therapyParams);
            $stmt->execute($params);
            jsonResponse(['success'=>true,'data'=>$stmt->fetchAll(PDO::FETCH_ASSOC)]);
        }

        if ($action === 'age_demographics') {
            // Age group analysis from live data
            $from = sanitizeDate($_GET['from'] ?? date('2025-07-26'), date('2025-07-26'));
            $to   = sanitizeDate($_GET['to'] ?? date('Y-m-d'), date('Y-m-d'));
            
            $baseQuery = buildBaseQuery();
            $sql = "WITH PatientData AS ($baseQuery)
                    SELECT 
                        CASE 
                            WHEN TIMESTAMPDIFF(YEAR, pd.dob, CURDATE()) < 30 THEN 'Under 30'
                            WHEN TIMESTAMPDIFF(YEAR, pd.dob, CURDATE()) BETWEEN 30 AND 49 THEN '30-49'
                            WHEN TIMESTAMPDIFF(YEAR, pd.dob, CURDATE()) BETWEEN 50 AND 64 THEN '50-64'
                            WHEN TIMESTAMPDIFF(YEAR, pd.dob, CURDATE()) >= 65 THEN '65+'
                            ELSE 'Unknown'
                        END as age_group,
                        COUNT(*) as patient_count,
                        SUM(CASE WHEN COALESCE(ps.StatusName, r.trtStatus) = 'On Drug' THEN 1 ELSE 0 END) as active_count,
                        ROUND(SUM(CASE WHEN COALESCE(ps.StatusName, r.trtStatus) = 'On Drug' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as retention_rate
                    FROM PatientData pd
                    LEFT JOIN tblreimbursement r ON pd.id = r.pId AND pd.patientFileId = r.fileId
                    LEFT JOIN tblpatientstatus ps ON r.patientStatus = ps.StatusID AND ps.isDeleted = 'N'
                    WHERE COALESCE(pd.enrolmentFormReceiveDate, pd.createdDate) BETWEEN :from AND :to 
                      AND pd.dob IS NOT NULL $therapyCondition
                    GROUP BY age_group 
                    ORDER BY 
                        CASE age_group 
                            WHEN 'Under 30' THEN 1
                            WHEN '30-49' THEN 2
                            WHEN '50-64' THEN 3
                            WHEN '65+' THEN 4
                            ELSE 5
                        END";
            $stmt = $pdo->prepare($sql);
            $params = array_merge([':from'=>$from,':to'=>$to], $therapyParams);
            $stmt->execute($params);
            jsonResponse(['success'=>true,'data'=>$stmt->fetchAll(PDO::FETCH_ASSOC)]);
        }

        if ($action === 'program_outcomes') {
            // Detailed program outcome analysis from live data
            $from = sanitizeDate($_GET['from'] ?? date('Y-01-01'), date('Y-01-01'));
            $to   = sanitizeDate($_GET['to'] ?? date('Y-m-d'), date('Y-m-d'));
            
            $baseQuery = buildBaseQuery();
            $enrollDate = "COALESCE(pd.enrolmentFormReceiveDate, pd.createdDate)";
            $sql = "WITH PatientData AS ($baseQuery),
                    TotalCount AS (
                        SELECT COUNT(*) as total FROM PatientData pd WHERE $enrollDate BETWEEN :from2 AND :to2 $therapyCondition
                    )
                    SELECT 
                        COALESCE(ps.StatusName, r.trtStatus, 'Unknown') as status,
                        COUNT(*) as count,
                        ROUND(COUNT(*) * 100.0 / (SELECT total FROM TotalCount), 2) as percentage,
                        AVG(TIMESTAMPDIFF(YEAR, pd.dob, CURDATE())) as avg_age,
                        COUNT(CASE WHEN pd.gender = 'M' THEN 1 END) as male_count,
                        COUNT(CASE WHEN pd.gender = 'F' THEN 1 END) as female_count
                    FROM PatientData pd
                    LEFT JOIN tblreimbursement r ON pd.id = r.pId AND pd.patientFileId = r.fileId
                    LEFT JOIN tblpatientstatus ps ON r.patientStatus = ps.StatusID AND ps.isDeleted = 'N'
                    WHERE $enrollDate BETWEEN :from AND :to $therapyCondition
                    GROUP BY status
                    ORDER BY count DESC";
            $stmt = $pdo->prepare($sql);
            $params = array_merge([':from'=>$from,':to'=>$to,':from2'=>$from,':to2'=>$to], $therapyParams);
            $stmt->execute($params);
            jsonResponse(['success'=>true,'data'=>$stmt->fetchAll(PDO::FETCH_ASSOC)]);
        }

        if ($action === 'patient_journey_analysis') {
            // Analyze patient journey timelines and conversion rates from live data
            $from = sanitizeDate($_GET['from'] ?? date('Y-01-01'), date('Y-01-01'));
            $to   = sanitizeDate($_GET['to'] ?? date('Y-m-d'), date('Y-m-d'));
            
            $baseQuery = buildBaseQuery();
            // case added belwo for ondrug file calculation - 01152026
            $sql = "WITH PatientData AS ($baseQuery),
                    CorrectedData AS (
                        SELECT 
                            pd.*,
                            r.enrollmentCompleteDate,
                            r.saEapOutcome,
                            r.trtStrtDate,
                            CASE when (r.patientStatus=14 AND r.trtStrtDate IS NOT NULL AND r.trtStrtDate != '0000-00-00') then 'onDrug' END as OnDrug,
                            COALESCE(pd.enrolmentFormReceiveDate, pd.createdDate) as enrollDate,
                            -- Apply auto-correction: if wcFirstAttempt is NULL but wcCompletionDate exists, use wcCompletionDate
                            CASE 
                                WHEN (pd.wcFirstAttempt IS NULL OR pd.wcFirstAttempt = '0000-00-00 00:00:00')
                                     AND pd.wcCompletionDate IS NOT NULL 
                                     AND pd.wcCompletionDate != '0000-00-00 00:00:00'
                                THEN pd.wcCompletionDate
                                ELSE pd.wcFirstAttempt
                            END as wcFirstAttempt_corrected
                        FROM PatientData pd
                        LEFT JOIN tblreimbursement r ON pd.id = r.pId AND pd.patientFileId = r.fileId
                    )
                    SELECT 
                        COUNT(*) as total_patients,
                        COUNT(wcFirstAttempt_corrected) as calls_made,
                        COUNT(wcCompletionDate) as successful_contacts,
                        COUNT(enrollDate) as enrollments_received,
                        COUNT(enrollmentCompleteDate) as enrollments_completed,
                        COUNT(saEapOutcome) as reimbursement_approved,
                        COUNT(OnDrug) as patients_on_drug,
                        ROUND(COUNT(wcCompletionDate) * 100.0 / NULLIF(COUNT(wcFirstAttempt_corrected), 0), 2) as contact_success_rate,
                        ROUND(COUNT(enrollmentCompleteDate) * 100.0 / NULLIF(COUNT(wcCompletionDate), 0), 2) as enrollment_conversion_rate,
                        ROUND(COUNT(OnDrug) * 100.0 / NULLIF(COUNT(enrollmentCompleteDate), 0), 2) as drug_start_rate,
                        ROUND(AVG(DATEDIFF(wcCompletionDate, wcFirstAttempt_corrected)), 1) as avg_days_to_contact,
                        ROUND(AVG(DATEDIFF(enrollmentCompleteDate, wcCompletionDate)), 1) as avg_days_to_enrollment,
                        ROUND(AVG(DATEDIFF(trtStrtDate, enrollmentCompleteDate)), 1) as avg_days_to_drug_start
                    FROM CorrectedData
                    WHERE enrollDate BETWEEN :from AND :to $therapyCondition";
            $stmt = $pdo->prepare($sql);
            $params = array_merge([':from'=>$from,':to'=>$to], $therapyParams);
            $stmt->execute($params);
            jsonResponse(['success'=>true,'data'=>$stmt->fetch(PDO::FETCH_ASSOC)]);
        }

        if ($action === 'therapy_analysis') {
            // Therapy and treatment analysis from live data
            $from = sanitizeDate($_GET['from'] ?? date('2025-07-26'), date('2025-07-26'));
            $to   = sanitizeDate($_GET['to'] ?? date('Y-m-d'), date('Y-m-d'));
            
            $baseQuery = buildBaseQuery();
            $sql = "WITH PatientData AS ($baseQuery)
                    SELECT 
                        pd.Therapy,
                        COUNT(*) as patient_count,
                        SUM(CASE WHEN COALESCE(ps.StatusName, r.trtStatus) = 'On Drug' THEN 1 ELSE 0 END) as active_patients,
                        ROUND(SUM(CASE WHEN COALESCE(ps.StatusName, r.trtStatus) = 'On Drug' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as retention_rate,
                        ROUND(AVG(TIMESTAMPDIFF(YEAR, pd.dob, CURDATE())), 1) as avg_age,
                        COUNT(CASE WHEN pd.gender = 'M' THEN 1 END) as male_count,
                        COUNT(CASE WHEN pd.gender = 'F' THEN 1 END) as female_count
                    FROM PatientData pd
                    LEFT JOIN tblreimbursement r ON pd.id = r.pId AND pd.patientFileId = r.fileId
                    LEFT JOIN tblpatientstatus ps ON r.patientStatus = ps.StatusID AND ps.isDeleted = 'N'
                    WHERE COALESCE(pd.enrolmentFormReceiveDate, pd.createdDate) BETWEEN :from AND :to
                      AND pd.Therapy IS NOT NULL AND pd.Therapy != '' $therapyCondition
                    GROUP BY pd.Therapy
                    ORDER BY patient_count DESC";
            $stmt = $pdo->prepare($sql);
            $params = array_merge([':from'=>$from,':to'=>$to], $therapyParams);
            $stmt->execute($params);
            jsonResponse(['success'=>true,'data'=>$stmt->fetchAll(PDO::FETCH_ASSOC)]);
        }

        if ($action === 'payer_analysis') {
            // Insurance payer analysis from live data
            $from = sanitizeDate($_GET['from'] ?? date('Y-01-01'), date('Y-01-01'));
            $to   = sanitizeDate($_GET['to'] ?? date('Y-m-d'), date('Y-m-d'));
            
            $baseQuery = buildBaseQuery();
            $enrollDate = "COALESCE(pd.enrolmentFormReceiveDate, pd.createdDate)";
            $sql = "WITH PatientData AS ($baseQuery)
                    SELECT 
                        COALESCE(NULLIF(TRIM(r.ipal),''),'Unknown') as payer,
                        COUNT(*) as patient_count,
                        ROUND(AVG(CAST(REPLACE(REPLACE(r.coveragePercentage, '%', ''), ',', '') AS DECIMAL(5,2))), 2) as avg_coverage_pct,
                        COUNT(CASE WHEN r.reimbursementStatus LIKE '%Approved%' THEN 1 END) as approved_count,
                        COUNT(CASE WHEN r.reimbursementStatus LIKE '%Denied%' THEN 1 END) as denied_count,
                        ROUND(COUNT(CASE WHEN r.reimbursementStatus LIKE '%Approved%' THEN 1 END) * 100.0 / NULLIF(COUNT(*), 0), 2) as approval_rate
                    FROM PatientData pd
                    LEFT JOIN tblreimbursement r ON pd.id = r.pId AND pd.patientFileId = r.fileId
                    WHERE $enrollDate BETWEEN :from AND :to
                      AND r.ipal IS NOT NULL AND r.ipal != '' $therapyCondition
                    GROUP BY payer
                    HAVING patient_count >= 3
                    ORDER BY patient_count DESC
                    LIMIT 15";
            $stmt = $pdo->prepare($sql);
            $params = array_merge([':from'=>$from,':to'=>$to], $therapyParams);
            $stmt->execute($params);
            jsonResponse(['success'=>true,'data'=>$stmt->fetchAll(PDO::FETCH_ASSOC)]);
        }

        if ($action === 'physician_specialty_analysis') {
            // Physician specialty performance from live data
            $from = sanitizeDate($_GET['from'] ?? date('Y-01-01'), date('Y-01-01'));
            $to   = sanitizeDate($_GET['to'] ?? date('Y-m-d'), date('Y-m-d'));
            
            $baseQuery = buildBaseQuery();
            $enrollDate = "COALESCE(pd.enrolmentFormReceiveDate, pd.createdDate)";
            $sql = "WITH PatientData AS ($baseQuery)
                    SELECT 
                        COALESCE(NULLIF(TRIM(ph.primarySpecialty),''),'Unknown') as specialty,
                        COUNT(*) as patient_count,
                        COUNT(DISTINCT pd.prescriberName) as physician_count,
                        SUM(CASE WHEN COALESCE(ps.StatusName, r.trtStatus) = 'On Drug' THEN 1 ELSE 0 END) as active_patients,
                        ROUND(SUM(CASE WHEN COALESCE(ps.StatusName, r.trtStatus) = 'On Drug' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as retention_rate,
                        ROUND(AVG(TIMESTAMPDIFF(YEAR, pd.dob, CURDATE())), 1) as avg_patient_age
                    FROM PatientData pd
                    LEFT JOIN tblreimbursement r ON pd.id = r.pId AND pd.patientFileId = r.fileId
                    LEFT JOIN tblpatientstatus ps ON r.patientStatus = ps.StatusID AND ps.isDeleted = 'N'
                    LEFT JOIN tblphysicians ph ON pd.prescriberName = CONCAT(ph.firstName, ' ', ph.lastName)
                    WHERE $enrollDate BETWEEN :from AND :to
                      AND ph.primarySpecialty IS NOT NULL AND ph.primarySpecialty != '' $therapyCondition
                    GROUP BY specialty
                    HAVING patient_count >= 5
                    ORDER BY patient_count DESC";
            $stmt = $pdo->prepare($sql);
            $params = array_merge([':from'=>$from,':to'=>$to], $therapyParams);
            $stmt->execute($params);
            jsonResponse(['success'=>true,'data'=>$stmt->fetchAll(PDO::FETCH_ASSOC)]);
        }

        if ($action === 'treatment_history_analysis') {
            // Previous treatment analysis from live data (using tblindication)
            $from = sanitizeDate($_GET['from'] ?? date('Y-01-01'), date('Y-01-01'));
            $to   = sanitizeDate($_GET['to'] ?? date('Y-m-d'), date('Y-m-d'));
            
            $baseQuery = buildBaseQuery();
            $enrollDate = "COALESCE(pd.enrolmentFormReceiveDate, pd.createdDate)";
            
            // Build therapy condition for UNION queries
            $therapyCondition1 = $therapyCondition;
            $therapyCondition2 = $therapyCondition;
            $therapyCondition3 = $therapyCondition;
            $therapyCondition4 = $therapyCondition;
            
            if ($therapy !== 'ALL' && !empty($therapy)) {
                $therapyCondition2 = str_replace(':therapy', ':therapy2', $therapyCondition2);
                $therapyCondition3 = str_replace(':therapy', ':therapy3', $therapyCondition3);
                $therapyCondition4 = str_replace(':therapy', ':therapy4', $therapyCondition4);
            }
            
            $sql = "WITH PatientData AS ($baseQuery)
                    SELECT 
                        'Systemic Corticosteroids' as treatment_type,
                        SUM(CASE WHEN pd.scWeeks IS NOT NULL OR pd.scWeeks2 IS NOT NULL THEN 1 ELSE 0 END) as previous_users,
                        SUM(CASE WHEN pd.isSystemic = 'Yes' THEN 1 ELSE 0 END) as current_users,
                        COUNT(*) as total_patients
                    FROM PatientData pd
                    WHERE $enrollDate BETWEEN :from AND :to $therapyCondition1
                    UNION ALL
                    SELECT 
                        'Immunosuppressants' as treatment_type,
                        SUM(CASE WHEN pd.isImmuno = 'Yes' OR pd.isMethotrexate = 'Yes' OR pd.isCyclosporine = 'Yes' THEN 1 ELSE 0 END) as previous_users,
                        SUM(CASE WHEN pd.isImmuno = 'Yes' THEN 1 ELSE 0 END) as current_users,
                        COUNT(*) as total_patients
                    FROM PatientData pd
                    WHERE $enrollDate BETWEEN :from2 AND :to2 $therapyCondition2
                    UNION ALL
                    SELECT 
                        'Biologics' as treatment_type,
                        0 as previous_users,
                        0 as current_users,
                        COUNT(*) as total_patients
                    FROM PatientData pd
                    WHERE $enrollDate BETWEEN :from3 AND :to3 $therapyCondition3
                    UNION ALL
                    SELECT 
                        'Phototherapy' as treatment_type,
                        SUM(CASE WHEN pd.isPhoto = 'Yes' THEN 1 ELSE 0 END) as previous_users,
                        SUM(CASE WHEN pd.photoWeeks IS NOT NULL THEN 1 ELSE 0 END) as current_users,
                        COUNT(*) as total_patients
                    FROM PatientData pd
                    WHERE $enrollDate BETWEEN :from4 AND :to4 $therapyCondition4";
            $stmt = $pdo->prepare($sql);
            $params = [
                ':from'=>$from, ':to'=>$to,
                ':from2'=>$from, ':to2'=>$to,
                ':from3'=>$from, ':to3'=>$to,
                ':from4'=>$from, ':to4'=>$to
            ];
            // Add therapy params for each UNION query
            if ($therapy !== 'ALL' && !empty($therapy)) {
                $params[':therapy'] = $therapy;
                $params[':therapy2'] = $therapy;
                $params[':therapy3'] = $therapy;
                $params[':therapy4'] = $therapy;
            }
            $stmt->execute($params);
            jsonResponse(['success'=>true,'data'=>$stmt->fetchAll(PDO::FETCH_ASSOC)]);
        }

        if ($action === 'program_coordinator_performance') {
            // Program coordinator analysis from live data
            $from = sanitizeDate($_GET['from'] ?? date('2025-07-26'), date('2025-07-26'));
            $to   = sanitizeDate($_GET['to'] ?? date('Y-m-d'), date('Y-m-d'));
            
            $baseQuery = buildBaseQuery();
            $sql = "WITH PatientData AS ($baseQuery),
                    CorrectedData AS (
                        SELECT 
                            pd.*,
                            r.consentVFirst,
                            r.patientStatus,
                            r.trtStatus,
                            r.enrollmentCompleteDate,
                            ps.StatusName,
                            COALESCE(pd.enrolmentFormReceiveDate, pd.createdDate) as enrollDate,
                            -- Apply auto-correction: if wcFirstAttempt is NULL but wcCompletionDate exists, use wcCompletionDate
                            CASE 
                                WHEN (pd.wcFirstAttempt IS NULL OR pd.wcFirstAttempt = '0000-00-00 00:00:00')
                                     AND pd.wcCompletionDate IS NOT NULL 
                                     AND pd.wcCompletionDate != '0000-00-00 00:00:00'
                                THEN pd.wcCompletionDate
                                ELSE pd.wcFirstAttempt
                            END as wcFirstAttempt_corrected
                        FROM PatientData pd
                        LEFT JOIN tblreimbursement r ON pd.id = r.pId AND pd.patientFileId = r.fileId
                        LEFT JOIN tblpatientstatus ps ON r.patientStatus = ps.StatusID AND ps.isDeleted = 'N'
                    )
                    SELECT 
                        COALESCE(NULLIF(TRIM(consentVFirst),''),'Unassigned') as coordinator,
                        COUNT(*) as patient_count,
                        SUM(CASE WHEN COALESCE(StatusName, trtStatus) = 'On Drug' THEN 1 ELSE 0 END) as active_patients,
                        ROUND(SUM(CASE WHEN COALESCE(StatusName, trtStatus) = 'On Drug' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as retention_rate,
                        COUNT(wcCompletionDate) as successful_contacts,
                        ROUND(COUNT(wcCompletionDate) * 100.0 / NULLIF(COUNT(wcFirstAttempt_corrected), 0), 2) as contact_success_rate,
                        ROUND(AVG(DATEDIFF(enrollmentCompleteDate, wcCompletionDate)), 1) as avg_enrollment_days
                    FROM CorrectedData
                    WHERE enrollDate BETWEEN :from AND :to $therapyCondition
                    GROUP BY coordinator
                    HAVING patient_count >= 5
                    ORDER BY patient_count DESC";
            $stmt = $pdo->prepare($sql);
            $params = array_merge([':from'=>$from,':to'=>$to], $therapyParams);
            $stmt->execute($params);
            jsonResponse(['success'=>true,'data'=>$stmt->fetchAll(PDO::FETCH_ASSOC)]);
        }

        if ($action === 'dosing_analysis') {
            // Dosing patterns analysis from live data
            $from = sanitizeDate($_GET['from'] ?? date('Y-01-01'), date('Y-01-01'));
            $to   = sanitizeDate($_GET['to'] ?? date('Y-m-d'), date('Y-m-d'));
            
            $baseQuery = buildBaseQuery();
            $enrollDate = "COALESCE(pd.enrolmentFormReceiveDate, pd.createdDate)";
            $sql = "WITH PatientData AS ($baseQuery)
                    SELECT 
                        COALESCE(NULLIF(TRIM(pd.dosage),''),'Unknown') as starting_dose,
                        COALESCE(NULLIF(TRIM(pd.currentFrequency),''),'Unknown') as starting_frequency,
                        COUNT(*) as patient_count,
                        SUM(CASE WHEN COALESCE(ps.StatusName, r.trtStatus) = 'On Drug' THEN 1 ELSE 0 END) as active_patients,
                        ROUND(SUM(CASE WHEN COALESCE(ps.StatusName, r.trtStatus) = 'On Drug' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as retention_rate,
                        ROUND(AVG(TIMESTAMPDIFF(YEAR, pd.dob, CURDATE())), 1) as avg_age
                    FROM PatientData pd
                    LEFT JOIN tblreimbursement r ON pd.id = r.pId AND pd.patientFileId = r.fileId
                    LEFT JOIN tblpatientstatus ps ON r.patientStatus = ps.StatusID AND ps.isDeleted = 'N'
                    WHERE $enrollDate BETWEEN :from AND :to
                      AND pd.dosage IS NOT NULL AND pd.dosage != '' $therapyCondition
                    GROUP BY starting_dose, starting_frequency
                    HAVING patient_count >= 3
                    ORDER BY patient_count DESC
                    LIMIT 10";
            $stmt = $pdo->prepare($sql);
            $params = array_merge([':from'=>$from,':to'=>$to], $therapyParams);
            $stmt->execute($params);
            jsonResponse(['success'=>true,'data'=>$stmt->fetchAll(PDO::FETCH_ASSOC)]);
        }

        if ($action === 'raw_table') {
            // return paginated rows from live data
            $from = sanitizeDate($_GET['from'] ?? date('Y-01-01'), date('Y-01-01'));
            $to   = sanitizeDate($_GET['to'] ?? date('Y-m-d'), date('Y-m-d'));
            $limit = (int)($_GET['limit'] ?? 100);
            $offset = (int)($_GET['offset'] ?? 0);
            
            $baseQuery = buildBaseQuery();
            $enrollDate = "COALESCE(pd.enrolmentFormReceiveDate, pd.createdDate)";
            $sql = "WITH PatientData AS ($baseQuery)
                    SELECT 
                        pd.patientuId as 'Patient Number',
                        pd.Therapy,
                        pd.gender as Gender,
                        TIMESTAMPDIFF(YEAR, pd.dob, CURDATE()) as 'Patient Current Age',
                        pd.province as 'Patient Province',
                        pd.wc1Status as 'Welcome Call Status',
                        COALESCE(ps.StatusName, r.trtStatus) as 'Patient Current Program Status',
                        $enrollDate as 'Enrollment Form Received Date',
                        pd.wcFirstAttempt as 'First Outgoing Call Date',
                        pd.wcCompletionDate as 'First Successful Contact Date',
                        r.enrollmentCompleteDate as 'Enrollment Complete Date',
                        r.trtStrtDate as 'On Drug Start Date',
                        pd.prescriberName as 'Physician Name',
                        r.pharmacyName as 'Pharmacy Name',
                        pd.indication as 'Primary Diagnosis'
                    FROM PatientData pd
                    LEFT JOIN tblreimbursement r ON pd.id = r.pId AND pd.patientFileId = r.fileId
                    LEFT JOIN tblpatientstatus ps ON r.patientStatus = ps.StatusID AND ps.isDeleted = 'N'
                    WHERE $enrollDate BETWEEN :from AND :to $therapyCondition
                    ORDER BY $enrollDate DESC 
                    LIMIT :off, :lim";
            $stmt = $pdo->prepare($sql);
            $params = array_merge([':from'=>$from,':to'=>$to,':off'=>$offset,':lim'=>$limit], $therapyParams);
            $stmt->bindValue(':off',$offset,PDO::PARAM_INT);
            $stmt->bindValue(':lim',$limit,PDO::PARAM_INT);
            foreach ($params as $key => $value) {
                if (!in_array($key, [':off', ':lim'])) {
                    $stmt->bindValue($key, $value);
                }
            }
            $stmt->execute();
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
            jsonResponse(['success'=>true,'rows'=>$rows]);
        }

        jsonResponse(['success'=>false,'error'=>'Unknown action']);

    } catch (PDOException $ex){
        jsonResponse(['success'=>false,'error'=>$ex->getMessage()]);
    }
}

// Non-API: render frontend
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width,initial-scale=1">
<title>Market Insights — QBR Dashboard</title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/modern-normalize/1.1.0/modern-normalize.min.css">
<style>
body{font-family:Inter,Arial,Helvetica,sans-serif;background:#f4f6fb;margin:0;padding:18px}
.container{max-width:1200px;margin:0 auto}
.controls{display:flex;gap:8px;align-items:center;margin-bottom:12px}
.btn{padding:8px 10px;border:0;border-radius:6px;background:#2563eb;color:#fff;cursor:pointer}
.card{background:#fff;padding:12px;border-radius:8px;box-shadow:0 6px 18px rgba(20,20,50,0.06);margin-bottom:12px}
.grid{display:grid;grid-template-columns:repeat(3,1fr);gap:12px}
.kpi{display:flex;flex-direction:column;gap:6px}
.kpi strong{font-size:20px}
.charts{display:grid;grid-template-columns:2fr 1fr;gap:12px}
.table-export{margin-top:12px}
table{font-size:14px}
table th{background:#f8fafc;font-weight:600;text-align:left}
table td{border-bottom:1px solid #e2e8f0}
table tr:hover{background:#f8fafc}
.performance-metric{color:#059669;font-weight:600}
.retention-high{color:#059669}
.retention-medium{color:#d97706}
.retention-low{color:#dc2626}
.description{font-size:13px;color:#64748b;margin:8px 0 12px 0;line-height:1.4;font-style:italic}
.card h4{margin:0 0 4px 0;color:#1e293b;font-size:16px}
.insights{background:#f0f9ff;border-left:4px solid #0ea5e9;padding:12px;margin:12px 0;border-radius:4px}
.insights h5{margin:0 0 8px 0;color:#0c4a6e;font-size:14px;font-weight:600}
.insights ul{margin:4px 0 0 0;padding-left:16px;font-size:13px;color:#0c4a6e}
.insights li{margin-bottom:4px}
.insight-positive{color:#059669}
.insight-warning{color:#d97706}
.insight-negative{color:#dc2626}
.summary-metric{text-align:center}
.metric-value{font-size:28px;font-weight:bold;margin-bottom:4px}
.metric-label{font-size:12px;opacity:0.9;text-transform:uppercase;letter-spacing:0.5px}
.action-item{background:#f8fafc;padding:8px 12px;margin:4px 0;border-radius:4px;border-left:3px solid #3b82f6}
.controls { background: white;  padding: 10px 20px;    width: 100%;    position: sticky;    top: 0px;}
@media(max-width:900px){.grid{grid-template-columns:1fr}.charts{grid-template-columns:1fr}}
</style>
</head>
<body>
<div class="container">
    <h2>Quarterly Business Review</h2>
    
    <div id="data-quality-notice" style="display:none; background: #fef3c7; border-left: 4px solid #f59e0b; padding: 12px; margin-bottom: 12px; border-radius: 4px;">
        <strong style="color: #92400e;">⚠️ Data Quality Notice:</strong>
        <span style="color: #78350f; font-size: 14px;">Some metrics may show inconsistencies due to different date filters across data sources. Percentages >100% indicate data quality issues that should be reviewed.</span>
        <a href="dataQualityAudit.php" style="margin-left: 12px; color: #92400e; font-weight: 600; text-decoration: underline;">View Full Audit Report →</a>
    </div>
    
    <div class="controls">
        <label>Program 
            <select id="therapy" style="padding:6px 8px;border:1px solid #d1d5db;border-radius:4px;background:#fff;">
                <option value="ALL">ALL</option>
            </select>
        </label>
        <label>From <input type="date" id="from" min="2025-07-26" value="2025-07-26"></label>
        <label>To <input type="date" id="to" value="2026-01-12"></label>
        <label>Top N Physicians <input type="number" id="topN" value="10" min="1" style="width:70px"></label>
        <button class="btn" id="load">Load</button>
        <button class="btn" id="exportPNG">Export Chart PNG</button>
        <button class="btn" id="exportXLS">Export Table → Excel</button>
    </div>

    <!-- Executive Summary Section -->
    <div class="card" id="executive-summary" style="background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: white; margin-bottom: 20px;">
        <h3 style="color: white; margin: 0 0 12px 0;">📋 Executive Summary</h3>
        <div class="grid" style="grid-template-columns: repeat(4, 1fr); gap: 20px;">
            <div class="summary-metric">
                <div class="metric-value" id="summary-patients">—</div>
                <div class="metric-label">Total Patients</div>
            </div>
            <div class="summary-metric">
                <div class="metric-value" id="summary-retention">—</div>
                <div class="metric-label">Retention Rate</div>
            </div>
            <div class="summary-metric">
                <div class="metric-value" id="summary-growth">—</div>
                <div class="metric-label">Recent Growth</div>
            </div>
            <div class="summary-metric">
                <div class="metric-value" id="summary-partners">—</div>
                <div class="metric-label">Active Partners</div>
            </div>
        </div>
        <div id="executive-highlights" style="margin-top: 16px; padding-top: 16px; border-top: 1px solid rgba(255,255,255,0.3);">
            <div id="executive-highlights-content"></div>
        </div>
    </div>

    <!-- Success Stories & Challenges -->
    <div class="grid" style="grid-template-columns: 1fr 1fr; gap: 12px; margin-bottom: 12px;">
        <div class="card" style="border-left: 4px solid #10b981;">
            <h4 style="color: #059669;">🎉 Key Successes</h4>
            <ul id="success-highlights" style="margin: 8px 0; padding-left: 20px; color: #065f46;"></ul>
        </div>
        <div class="card" style="border-left: 4px solid #f59e0b;">
            <h4 style="color: #d97706;">⚠️ Areas for Improvement</h4>
            <ul id="improvement-areas" style="margin: 8px 0; padding-left: 20px; color: #92400e;"></ul>
        </div>
    </div>

    <!-- Action Plan -->
    <div class="card" style="border-left: 4px solid #3b82f6; margin-bottom: 12px;">
        <h4 style="color: #1d4ed8;">🎯 Strategic Action Plan</h4>
        <div id="action-plan" style="margin: 8px 0;"></div>
    </div>

    
    <div class="grid">
        <div class="card kpi" id="kpis">
            <h4>Key Performance Indicators</h4>
            <p class="description">Core metrics showing overall program performance and patient engagement within the selected date range.</p>
            <div><small>Total patients</small><strong id="total_patients">—</strong></div>
            <div><small>On drug</small><strong id="on_drug">—</strong></div>
            <!--<div><small>Dropouts</small><strong id="dropout">—</strong></div>-->
            <div><small>Dropout/Was Never on Drug</small><strong id="dropout">—</strong></div>
            <div class="insights" id="kpi-insights" style="display:none;">
                <h5>📊 Key Insights</h5>
                <ul id="kpi-insights-list"></ul>
            </div>
        </div>
        <div class="card">
            <h4>Gender Distribution</h4>
            <p class="description">Patient demographics by gender, helping identify target population characteristics and potential gender-specific trends in program enrollment.</p>
            <canvas id="genderChart" height="180"></canvas>
            <div class="insights" id="gender-insights" style="display:none;">
                <h5>👥 Demographic Insights</h5>
                <ul id="gender-insights-list"></ul>
            </div>
        </div>
        <div class="card">
            <h4>Top Pharmacy Partners</h4>
            <p class="description">Leading pharmacy partners by enrollment volume, indicating key distribution channels and partnership effectiveness.</p>
            <canvas id="pharmChart" height="180"></canvas>
            <div class="insights" id="pharmacy-insights" style="display:none;">
                <h5>🏪 Partnership Insights</h5>
                <ul id="pharmacy-insights-list"></ul>
            </div>
        </div>
    </div>

    <div class="card charts">
        <div>
            <h4>Top Prescriber Monthly Trends</h4>
            <p class="description">Monthly enrollment patterns for top prescribers, revealing seasonal trends, prescriber engagement cycles, and growth opportunities. Use this to identify high-performing physicians and understand enrollment momentum.</p>
            <canvas id="physChart" height="260"></canvas>
            <div class="insights" id="prescriber-trends-insights" style="display:none;">
                <h5>👨‍⚕️ Prescriber Trend Insights</h5>
                <ul id="prescriber-trends-insights-list"></ul>
            </div>
        </div>
        <div>
            <h4>Patient Program Status</h4>
            <p class="description">Current distribution of patient statuses across the program, providing insight into retention rates and program effectiveness.</p>
            <canvas id="statusChart" height="260"></canvas>
            <h4 style="margin-top:12px">Geographic Distribution</h4>
            <p class="description">Patient enrollment by province, highlighting regional market penetration and identifying expansion opportunities.</p>
            <canvas id="provChart" height="200"></canvas>
        </div>
    </div>

    <!-- New QBR Insights Sections -->
    <div class="grid">
        <div class="card">
            <h4>Monthly Enrollment Trends</h4>
            <p class="description">Total enrollments vs. active patients over time, showing program growth trajectory and patient retention patterns. The gap between lines indicates dropout rates.</p>
            <canvas id="enrollmentChart" height="200"></canvas>
            <div class="insights" id="enrollment-insights" style="display:none;">
                <h5>📈 Enrollment Insights</h5>
                <ul id="enrollment-insights-list"></ul>
            </div>
        </div>
        <div class="card">
            <h4>Cohort Retention Analysis</h4>
            <p class="description">Retention rates by enrollment month cohort, helping identify which time periods produced the most engaged patients and seasonal retention patterns.</p>
            <canvas id="retentionChart" height="200"></canvas>
            <div class="insights" id="retention-insights" style="display:none;">
                <h5>🎯 Retention Insights</h5>
                <ul id="retention-insights-list"></ul>
            </div>
        </div>
        <div class="card">
            <h4>Patient Age Demographics</h4>
            <p class="description">Age group distribution of enrolled patients, providing insights into target demographics and helping tailor program messaging and outreach strategies.</p>
            <canvas id="ageChart" height="200"></canvas>
        </div>
    </div>

    <div class="card charts">
        <div>
            <h4>Quarterly Performance Comparison</h4>
            <p class="description">Quarter-over-quarter enrollment volumes and retention rates, enabling trend analysis and performance benchmarking. The dual-axis shows both volume growth and quality metrics.</p>
            <canvas id="quarterlyChart" height="260"></canvas>
            <div class="insights" id="quarterly-insights" style="display:none;">
                <h5>📊 Quarterly Insights</h5>
                <ul id="quarterly-insights-list"></ul>
            </div>
        </div>
        <div>
            <h4>Provincial Market Analysis</h4>
            <p class="description">Patient enrollment by province, revealing market penetration and regional performance. Use this to identify underserved markets and expansion opportunities.</p>
            <canvas id="geoChart" height="260"></canvas>
        </div>
    </div>

    <div class="card">
        <h4>Top Prescriber Performance Scorecard</h4>
        <p class="description">Detailed performance metrics for top prescribers, including enrollment volume, patient retention rates, and geographic reach. Green retention rates (≥80%) indicate excellent performance, orange (60-79%) shows good performance, and red (<60%) highlights areas needing attention. Use this data to recognize top performers, identify coaching opportunities, and plan targeted support initiatives.</p>
        <div style="overflow-x:auto;">
            <table id="prescribersTable" border="1" cellpadding="8" style="border-collapse:collapse;width:100%;min-width:800px;">
                <thead>
                    <tr>
                        <th>Prescriber</th>
                        <th>Total Enrollments</th>
                        <th>Active Patients</th>
                        <th>Dropout/Was Never on Drug</th>
                        <th>Retention Rate</th>
                        <th>Provinces Served</th>
                    </tr>
                </thead>
                <tbody></tbody>
            </table>
        </div>
    </div>

    <div class="card">
        <h4>Pharmacy Partner Performance Analysis</h4>
        <p class="description">Key pharmacy partners ranked by enrollment volume and retention performance. This data helps identify our most valuable distribution partners and highlights opportunities for partnership expansion or improvement. Color-coded retention rates help quickly identify high-performing (green ≥80%), moderate (orange 60-79%), and underperforming (red <60%) pharmacy partners.</p>
        <div style="overflow-x:auto;">
            <table id="pharmacyTable" border="1" cellpadding="8" style="border-collapse:collapse;width:100%;min-width:700px;">
                <thead>
                    <tr>
                        <th>Pharmacy</th>
                        <th>Total Enrollments</th>
                        <th>Active Patients</th>
                        <th>Retention Rate</th>
                        <th>Provinces Served</th>
                    </tr>
                </thead>
                <tbody></tbody>
            </table>
        </div>
    </div>

    <!-- New Advanced Insights -->
    <div class="grid">
        <div class="card">
            <h4>Patient Journey Conversion Funnel</h4>
            <p class="description">End-to-end patient journey analysis showing conversion rates at each stage from initial contact to drug start. Critical for identifying bottlenecks and optimization opportunities.</p>
            <canvas id="journeyChart" height="200"></canvas>
            <div class="insights" id="journey-insights" style="display:none;">
                <h5>🔄 Journey Insights</h5>
                <ul id="journey-insights-list"></ul>
            </div>
        </div>
        <div class="card">
            <h4>Therapy Distribution</h4>
            <p class="description">Breakdown of patients by therapy type, showing which treatments are most commonly prescribed and their respective retention rates.</p>
            <canvas id="therapyChart" height="200"></canvas>
            <div class="insights" id="therapy-insights" style="display:none;">
                <h5>💊 Therapy Insights</h5>
                <ul id="therapy-insights-list"></ul>
            </div>
        </div>
    </div>

    <div class="grid">
        <div class="card">
            <h4>Physician Specialty Performance</h4>
            <p class="description">Performance analysis by physician specialty, revealing which medical specialties achieve the best patient outcomes and retention rates.</p>
            <canvas id="specialtyChart" height="200"></canvas>
            <div class="insights" id="specialty-insights" style="display:none;">
                <h5>🩺 Specialty Insights</h5>
                <ul id="specialty-insights-list"></ul>
            </div>
        </div>
     <!--   <div class="card">
            <h4>Insurance Payer Analysis</h4>
            <p class="description">Payer approval rates and coverage analysis, critical for understanding reimbursement landscape and identifying high-value payer relationships.</p>
            <canvas id="payerChart" height="200"></canvas>
            <div class="insights" id="payer-insights" style="display:none;">
                <h5>💳 Payer Insights</h5>
                <ul id="payer-insights-list"></ul>
            </div>
        </div> -->
    </div>

    <div class="card">
        <h4>Program Coordinator Performance</h4>
        <p class="description">Individual coordinator performance metrics including patient load, retention rates, and contact success rates. Use this data for performance management and resource allocation.</p>
        <div style="overflow-x:auto;">
            <table id="coordinatorTable" border="1" cellpadding="8" style="border-collapse:collapse;width:100%;min-width:900px;">
                <thead>
                    <tr>
                        <th>Coordinator</th>
                        <th>Patient Count</th>
                        <th>Active Patients</th>
                        <th>Retention Rate</th>
                        <th>Contact Success Rate</th>
                        <th>Avg Enrollment Days</th>
                    </tr>
                </thead>
                <tbody></tbody>
            </table>
        </div>
        <div class="insights" id="coordinator-insights" style="display:none;">
            <h5>👥 Coordinator Insights</h5>
            <ul id="coordinator-insights-list"></ul>
        </div>
    </div>

    <!-- <div class="card">
        <h4>Treatment History Analysis</h4>
        <p class="description">Analysis of patients' previous and current treatment patterns, providing insights into treatment switching behavior and therapy positioning.</p>
        <canvas id="treatmentHistoryChart" height="300"></canvas>
        <div class="insights" id="treatment-insights" style="display:none;">
            <h5>🔬 Treatment History Insights</h5>
            <ul id="treatment-insights-list"></ul>
        </div>
    </div> -->

    <!-- <div class="card table-export">
        <h3>Detailed Patient Data Export</h3>
        <p class="description">Complete patient enrollment data (first 200 records) for detailed analysis and reporting. This raw data can be exported to Excel for further analysis, custom reporting, or integration with other business intelligence tools. Use the export button above to download the full dataset.</p>
        <table id="rawTable" border="1" cellpadding="6" style="border-collapse:collapse;width:100%"><thead></thead><tbody></tbody></table>
    </div> -->
</div>

<script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.0/dist/chart.umd.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
<script>
const apiBase = window.location.pathname + '?api=1&action=';
let physChart, genderChart, pharmChart, statusChart, provChart;
function qs(obj){ return Object.keys(obj).map(k=>encodeURIComponent(k)+'='+encodeURIComponent(obj[k])).join('&'); }
function monthLabelFromYm(ym){ const [y,mo]=ym.split('-'); const m=parseInt(mo,10); const names=['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec']; return (names[m-1]||mo)+' '+y; }

async function loadData(){
    const from = document.getElementById('from').value;
    const to = document.getElementById('to').value;
    const top = document.getElementById('topN').value || 10;
    const therapy = document.getElementById('therapy').value || 'ALL';
    
    // Reset data quality flag
    window.dataQualityIssues = false;

    try {
        // KPIs
        const k = await fetch(apiBase + 'overview&' + qs({from,to,therapy}))
            .then(r => r.text())
            .then(text => {
                try { return JSON.parse(text); } 
                catch(e) { console.error('Overview JSON error:', text); return {success: false}; }
            });
        if (!k.success){ alert('Overview error: '+(k.error||k.message)); return; }
        document.getElementById('total_patients').innerText = k.kpis.total_patients;
        document.getElementById('on_drug').innerText = k.kpis.on_drug;
        document.getElementById('dropout').innerText = k.kpis.dropout;
        generateKPIInsights(k.kpis);
        
        // Store KPIs for executive summary
        window.dashboardData = { kpis: k.kpis };

        // gender
        const g = await fetch(apiBase + 'gender_ratio&' + qs({therapy}))
            .then(r => r.text())
            .then(text => {
                try { return JSON.parse(text); } 
                catch(e) { console.error('Gender JSON error:', text); return {success: false, data: []}; }
            });
        if (g.success && g.data) {
            const genders = g.data.map(r=>r.Gender||'Unknown'); 
            const gvals = g.data.map(r=>+r.cnt);
            renderPie('genderChart', genders, gvals, chart => { genderChart = chart; });
            generateGenderInsights(g.data);
            window.dashboardData.genderData = g.data;
        }

        // pharmacies
        const p = await fetch(apiBase + 'pharmacy_enrollments&' + qs({limit:10,therapy}))
            .then(r => r.text())
            .then(text => {
                try { return JSON.parse(text); } 
                catch(e) { console.error('Pharmacy JSON error:', text); return {success: false, data: []}; }
            });
        if (p.success && p.data) {
            const pnames = p.data.map(x=>x.pharmacy); 
            const pvals = p.data.map(x=>+x.cnt);
            renderBar('pharmChart', pnames, pvals, chart => { pharmChart = chart; });
            generatePharmacyInsights(p.data);
            window.dashboardData.pharmacyData = p.data;
        }

        // status breakdown
        const s = await fetch(apiBase + 'status_breakdown&' + qs({therapy}))
            .then(r => r.text())
            .then(text => {
                try { return JSON.parse(text); } 
                catch(e) { console.error('Status JSON error:', text); return {success: false, data: []}; }
            });
        if (s.success && s.data) {
            const sNames = s.data.map(x=>x.status); 
            const sVals = s.data.map(x=>+x.cnt);
            renderDoughnut('statusChart', sNames, sVals, chart=>{statusChart=chart;});
        }

        // provinces
        const prov = await fetch(apiBase + 'province_counts&' + qs({therapy}))
            .then(r => r.text())
            .then(text => {
                try { return JSON.parse(text); } 
                catch(e) { console.error('Province JSON error:', text); return {success: false, data: []}; }
            });
        if (prov.success && prov.data) {
            const provNames = prov.data.slice(0,10).map(x=>x.province); 
            const provVals = prov.data.slice(0,10).map(x=>+x.cnt);
            renderBar('provChart', provNames, provVals, chart=>{provChart=chart;});
        }

        // physician trends
        const phys = await fetch(apiBase + 'physician_monthly_trends&' + qs({from,to,top,therapy}))
            .then(r => r.text())
            .then(text => {
                try { return JSON.parse(text); } 
                catch(e) { console.error('Physician trends JSON error:', text); return {success: false}; }
            });
        if (!phys.success){ console.warn('physician trends error', phys); }
        renderPhysChart(phys.months || [], phys.prescribers || []);

        // raw table
        const raw = await fetch(apiBase + 'raw_table&' + qs({limit:200}))
            .then(r => r.text())
            .then(text => {
                try { return JSON.parse(text); } 
                catch(e) { console.error('Raw table JSON error:', text); return {success: false, rows: []}; }
            });
        // renderTable(raw.rows || []);

        // New QBR insights
        await loadQBRInsights(from, to);
        
        // Generate executive summary after all data is loaded
        generateExecutiveSummary();
        
        // Show data quality notice if issues were detected
        // if (window.dataQualityIssues) {
        //     document.getElementById('data-quality-notice').style.display = 'block';
        // }
    } catch (error) {
        console.error('Error in loadData:', error);
        alert('Error loading dashboard data. Check console for details.');
    }
}

async function loadQBRInsights(from, to) {
    try {
        // Enrollment trends
        const enrollment = await fetch(apiBase + 'enrollment_trends&' + qs({from,to}))
            .then(r => r.text())
            .then(text => {
                try { return JSON.parse(text); } 
                catch(e) { console.error('Enrollment trends JSON error:', text); return {success: false}; }
            });
        if (enrollment.success && enrollment.data) {
            const months = enrollment.data.map(d => monthLabelFromYm(d.month));
            const enrollments = enrollment.data.map(d => +d.enrollments);
            const onDrug = enrollment.data.map(d => +d.on_drug_count);
            renderEnrollmentTrends(months, enrollments, onDrug);
            generateEnrollmentInsights(enrollment.data);
            window.dashboardData.enrollmentData = enrollment.data;
        }

        // Retention analysis
        const retention = await fetch(apiBase + 'retention_analysis')
            .then(r => r.text())
            .then(text => {
                try { return JSON.parse(text); } 
                catch(e) { console.error('Retention JSON error:', text); return {success: false}; }
            });
        if (retention.success && retention.data) {
            const cohorts = retention.data.map(d => monthLabelFromYm(d.cohort_month));
            const rates = retention.data.map(d => +d.retention_rate);
            renderRetentionChart(cohorts, rates);
            generateRetentionInsights(retention.data);
        }

        // Age demographics
        const age = await fetch(apiBase + 'age_demographics')
            .then(r => r.text())
            .then(text => {
                try { return JSON.parse(text); } 
                catch(e) { console.error('Age demographics JSON error:', text); return {success: false}; }
            });
        if (age.success && age.data) {
            const ageGroups = age.data.map(d => d.age_group);
            const ageCounts = age.data.map(d => +d.patient_count);
            renderPie('ageChart', ageGroups, ageCounts);
        }

        // Quarterly comparison
        const quarterly = await fetch(apiBase + 'quarterly_comparison')
            .then(r => r.text())
            .then(text => {
                try { return JSON.parse(text); } 
                catch(e) { console.error('Quarterly JSON error:', text); return {success: false}; }
            });
        if (quarterly.success && quarterly.data) {
            const quarters = quarterly.data.map(d => d.quarter);
            const qEnrollments = quarterly.data.map(d => +d.enrollments);
            const qRetention = quarterly.data.map(d => +d.retention_rate);
            renderQuarterlyChart(quarters, qEnrollments, qRetention);
            generateQuarterlyInsights(quarterly.data);
            window.dashboardData.quarterlyData = quarterly.data;
        }

        // Geographic analysis
        const geo = await fetch(apiBase + 'geographic_analysis')
            .then(r => r.text())
            .then(text => {
                try { return JSON.parse(text); } 
                catch(e) { console.error('Geographic JSON error:', text); return {success: false}; }
            });
        if (geo.success && geo.data) {
            const provinces = geo.data.map(d => d.province);
            const geoPatients = geo.data.map(d => +d.total_patients);
            renderBar('geoChart', provinces, geoPatients);
            window.dashboardData.geoData = geo.data;
        }

        // Top prescribers performance
        const prescribers = await fetch(apiBase + 'top_prescribers_performance&' + qs({from,to,limit:15}))
            .then(r => r.text())
            .then(text => {
                try { return JSON.parse(text); } 
                catch(e) { console.error('Prescribers JSON error:', text); return {success: false}; }
            });
        if (prescribers.success && prescribers.data) {
            renderPrescribersTable(prescribers.data);
            window.dashboardData.prescriberData = prescribers.data;
        }

        // Pharmacy performance
        const pharmacy = await fetch(apiBase + 'pharmacy_performance&' + qs({limit:15}))
            .then(r => r.text())
            .then(text => {
                try { return JSON.parse(text); } 
                catch(e) { console.error('Pharmacy JSON error:', text); return {success: false}; }
            });
        if (pharmacy.success && pharmacy.data) {
            renderPharmacyTable(pharmacy.data);
        }

        // New advanced insights
        await loadAdvancedInsights(from, to);
    } catch (error) {
        console.error('Error loading QBR insights:', error);
    }
}

function renderPie(canvasId, labels, data, cb){ const ctx=document.getElementById(canvasId).getContext('2d'); if (window[canvasId+'Obj']) window[canvasId+'Obj'].destroy(); window[canvasId+'Obj'] = new Chart(ctx,{type:'pie',data:{labels, datasets:[{data}]}, options:{responsive:true}}); if(cb)cb(window[canvasId+'Obj']); }
function renderBar(canvasId, labels, data, cb){ const ctx=document.getElementById(canvasId).getContext('2d'); if (window[canvasId+'Obj']) window[canvasId+'Obj'].destroy(); window[canvasId+'Obj'] = new Chart(ctx,{type:'bar',data:{labels,datasets:[{label:'Count',data}]}, options:{indexAxis:'y',responsive:true}}); if(cb)cb(window[canvasId+'Obj']); }
function renderDoughnut(canvasId, labels, data, cb){ const ctx=document.getElementById(canvasId).getContext('2d'); if (window[canvasId+'Obj']) window[canvasId+'Obj'].destroy(); window[canvasId+'Obj'] = new Chart(ctx,{type:'doughnut',data:{labels,datasets:[{data}]}, options:{responsive:true}}); if(cb)cb(window[canvasId+'Obj']); }

function renderPhysChart(months, prescribers){ 
    const ctx=document.getElementById('physChart').getContext('2d'); 
    if (physChart) physChart.destroy();
    const labels = months.map(m=>monthLabelFromYm(m)); 
    const datasets = (prescribers||[]).map((p,i)=>({
        label:p.prescriber, 
        data:p.data, 
        borderColor:`hsl(${(i*37)%360} 70% 45%)`, 
        backgroundColor:`hsla(${(i*37)%360} 70% 45% / .12)`, 
        tension:.25, 
        fill:false, 
        pointRadius:3
    }));
    physChart = new Chart(ctx, {
        type: 'line',
        data: { labels, datasets },
        options: {
            responsive: true,
            maintainAspectRatio: true,
            aspectRatio: 2,
            plugins: {
                legend: { position: 'right', labels: { boxWidth: 10 } }
            },
            scales: { y: { beginAtZero: true } }
        }
    });
//}es:{y:{beginAtZero:true}}}});
}

function renderTable(rows){ const thead=document.querySelector('#rawTable thead'); const tbody=document.querySelector('#rawTable tbody'); thead.innerHTML=''; tbody.innerHTML=''; if(!rows||rows.length===0){ thead.innerHTML='<tr><th>No data</th></tr>'; return; }
    // build header from first row fields
    const cols = Object.keys(rows[0]);
    const tr=document.createElement('tr'); tr.innerHTML = '<th>' + cols.map(c=>''+c).join('</th><th>') + '</th>'; thead.appendChild(tr);
    rows.forEach(r=>{ const row=document.createElement('tr'); row.innerHTML = cols.map(c=>'<td>'+escapeHtml(r[c]===null?'':r[c])+'</td>').join(''); tbody.appendChild(row); });
}

function escapeHtml(s){ return (s+'').replace(/[&<>]/g,c=>({'&':'&amp;','<':'&lt;','>':'&gt;'}[c])); }

function renderEnrollmentTrends(months, enrollments, onDrug) {
    const ctx = document.getElementById('enrollmentChart').getContext('2d');
    if (window.enrollmentChartObj) window.enrollmentChartObj.destroy();
    
    window.enrollmentChartObj = new Chart(ctx, {
        type: 'line',
        data: {
            labels: months,
            datasets: [
                {
                    label: 'Total Enrollments',
                    data: enrollments,
                    borderColor: '#2563eb',
                    backgroundColor: 'rgba(37, 99, 235, 0.1)',
                    tension: 0.3
                },
                {
                    label: 'Active Patients',
                    data: onDrug,
                    borderColor: '#059669',
                    backgroundColor: 'rgba(5, 150, 105, 0.1)',
                    tension: 0.3
                }
            ]
        },
        options: {
            responsive: true,
            scales: { y: { beginAtZero: true } }
        }
    });
}

function renderRetentionChart(cohorts, rates) {
    const ctx = document.getElementById('retentionChart').getContext('2d');
    if (window.retentionChartObj) window.retentionChartObj.destroy();
    
    window.retentionChartObj = new Chart(ctx, {
        type: 'bar',
        data: {
            labels: cohorts,
            datasets: [{
                label: 'Retention Rate (%)',
                data: rates,
                backgroundColor: 'rgba(168, 85, 247, 0.8)',
                borderColor: '#a855f7',
                borderWidth: 1
            }]
        },
        options: {
            responsive: true,
            scales: { 
                y: { 
                    beginAtZero: true,
                    max: 100,
                    ticks: {
                        callback: function(value) {
                            return value + '%';
                        }
                    }
                } 
            }
        }
    });
}

function renderQuarterlyChart(quarters, enrollments, retention) {
    const ctx = document.getElementById('quarterlyChart').getContext('2d');
    if (window.quarterlyChartObj) window.quarterlyChartObj.destroy();
    
    window.quarterlyChartObj = new Chart(ctx, {
        type: 'bar',
        data: {
            labels: quarters,
            datasets: [
                {
                    label: 'Enrollments',
                    data: enrollments,
                    backgroundColor: 'rgba(59, 130, 246, 0.8)',
                    borderColor: '#3b82f6',
                    borderWidth: 1,
                    yAxisID: 'y'
                },
                {
                    label: 'Retention Rate (%)',
                    data: retention,
                    type: 'line',
                    borderColor: '#ef4444',
                    backgroundColor: 'rgba(239, 68, 68, 0.1)',
                    tension: 0.3,
                    yAxisID: 'y1'
                }
            ]
        },
        options: {
            responsive: true,
            scales: {
                y: {
                    type: 'linear',
                    display: true,
                    position: 'left',
                    beginAtZero: true
                },
                y1: {
                    type: 'linear',
                    display: true,
                    position: 'right',
                    beginAtZero: true,
                    max: 100,
                    grid: {
                        drawOnChartArea: false,
                    },
                    ticks: {
                        callback: function(value) {
                            return value + '%';
                        }
                    }
                }
            }
        }
    });
}

function renderPrescribersTable(data) {
    const tbody = document.querySelector('#prescribersTable tbody');
    tbody.innerHTML = '';
    
    data.forEach(row => {
        const retentionClass = row.retention_rate >= 80 ? 'retention-high' : 
                              row.retention_rate >= 60 ? 'retention-medium' : 'retention-low';
        const tr = document.createElement('tr');
        tr.innerHTML = `
            <td><strong>${escapeHtml(row.prescriber)}</strong></td>
            <td class="performance-metric">${row.total_enrollments}</td>
            <td>${row.active_patients}</td>
            <td>${row.dropouts}</td>
            <td class="${retentionClass}"><strong>${row.retention_rate}%</strong></td>
            <td>${row.provinces_served}</td>
        `;
        tbody.appendChild(tr);
    });
}

function renderPharmacyTable(data) {
    const tbody = document.querySelector('#pharmacyTable tbody');
    tbody.innerHTML = '';
    
    data.forEach(row => {
        const retentionClass = row.retention_rate >= 80 ? 'retention-high' : 
                              row.retention_rate >= 60 ? 'retention-medium' : 'retention-low';
        const tr = document.createElement('tr');
        tr.innerHTML = `
            <td><strong>${escapeHtml(row.pharmacy)}</strong></td>
            <td class="performance-metric">${row.total_enrollments}</td>
            <td>${row.active_patients}</td>
            <td class="${retentionClass}"><strong>${row.retention_rate}%</strong></td>
            <td>${row.provinces_served}</td>
        `;
        tbody.appendChild(tr);
    });
}

document.getElementById('load').addEventListener('click', loadData);
document.getElementById('therapy').addEventListener('change', loadData);

// export functions
 document.getElementById('exportPNG').addEventListener('click', ()=>{
     const canvas = document.querySelector('#physChart'); if(!canvas){ alert('Load first'); return; }
     const url = canvas.toDataURL('image/png'); const a=document.createElement('a'); a.href=url; a.download='market_insights_physician_trends.png'; a.click();
 });
 document.getElementById('exportXLS').addEventListener('click', ()=>{
     const table = document.getElementById('rawTable'); const wb = XLSX.utils.table_to_book(table,{sheet:'Market Insights'}); XLSX.writeFile(wb,'market_insights.xlsx');
 });

// Insight generation functions
function generateKPIInsights(kpis) {
    const insights = [];
    const total = +kpis.total_patients;
    const onDrug = +kpis.on_drug;
    const dropout = +kpis.dropout;
    const retentionRate = total > 0 ? ((onDrug / total) * 100).toFixed(1) : 0;
    
    if (retentionRate >= 80) {
        insights.push(`<li class="insight-positive">Excellent retention rate of ${retentionRate}% indicates strong program effectiveness</li>`);
    } else if (retentionRate >= 60) {
        insights.push(`<li class="insight-warning">Moderate retention rate of ${retentionRate}% - opportunity for improvement</li>`);
    } else {
        insights.push(`<li class="insight-negative">Low retention rate of ${retentionRate}% requires immediate attention</li>`);
    }
    
    if (total > 0) {
        insights.push(`<li>Total program enrollment: ${total.toLocaleString()} patients</li>`);
        insights.push(`<li>${onDrug} patients currently active (${retentionRate}%)</li>`);
        if (dropout > 0) {
            insights.push(`<li class="insight-warning">${dropout} patients have dropped out - analyze reasons for improvement</li>`);
        }
    }
    
    showInsights('kpi-insights', insights);
}

function generateGenderInsights(data) {
    const insights = [];
    const total = data.reduce((sum, item) => sum + (+item.cnt), 0);
    
    if (total > 0) {
        const genderBreakdown = data.map(item => ({
            gender: item.Gender || 'Unknown',
            count: +item.cnt,
            percentage: ((+item.cnt / total) * 100).toFixed(1)
        })).sort((a, b) => b.count - a.count);
        
        const topGender = genderBreakdown[0];
        insights.push(`<li>${topGender.gender} patients represent ${topGender.percentage}% of enrollments (${topGender.count} patients)</li>`);
        
        if (genderBreakdown.length > 1) {
            const secondGender = genderBreakdown[1];
            const ratio = (topGender.count / secondGender.count).toFixed(0);
            insights.push(`<li>Gender ratio: ${ratio}:1 (${topGender.gender}:${secondGender.gender})</li>`);
        }
        
        if (topGender.percentage > 70) {
            insights.push(`<li class="insight-warning">Consider targeted outreach to underrepresented gender groups</li>`);
        } else if (Math.abs(50 - topGender.percentage) < 10) {
            insights.push(`<li class="insight-positive">Well-balanced gender distribution supports broad market appeal</li>`);
        }
    }
    
    showInsights('gender-insights', insights);
}

function generatePharmacyInsights(data) {
    const insights = [];
    const total = data.reduce((sum, item) => sum + (+item.cnt), 0);
    
    if (data.length > 0) {
        const topPharmacy = data[0];
        const topPercentage = ((+topPharmacy.cnt / total) * 100).toFixed(1);
        
        insights.push(`<li><strong>${topPharmacy.pharmacy}</strong> leads with ${topPharmacy.cnt} enrollments (${topPercentage}%)</li>`);
        
        if (data.length >= 3) {
            const top3Total = data.slice(0, 3).reduce((sum, item) => sum + (+item.cnt), 0);
            const top3Percentage = ((top3Total / total) * 100).toFixed(1);
            insights.push(`<li>Top 3 pharmacies account for ${top3Percentage}% of all enrollments</li>`);
        }
        
        if (topPercentage > 40) {
            insights.push(`<li class="insight-warning">High dependency on single pharmacy partner - consider diversification</li>`);
        } else if (topPercentage < 20 && data.length > 5) {
            insights.push(`<li class="insight-positive">Well-distributed pharmacy network reduces dependency risk</li>`);
        }
        
        if (data.length > 8) {
            insights.push(`<li class="insight-positive">Strong pharmacy network with ${data.length} active partners</li>`);
        }
    }
    
    showInsights('pharmacy-insights', insights);
}

function generateEnrollmentInsights(enrollmentData) {
    const insights = [];
    
    if (enrollmentData.length > 1) {
        const recent = enrollmentData.slice(-3);
        const earlier = enrollmentData.slice(0, -3);
        
        if (recent.length > 0 && earlier.length > 0) {
            const recentAvg = recent.reduce((sum, d) => sum + (+d.enrollments), 0) / recent.length;
            const earlierAvg = earlier.reduce((sum, d) => sum + (+d.enrollments), 0) / earlier.length;
            const growth = ((recentAvg - earlierAvg) / earlierAvg * 100).toFixed(1);
            
            if (growth > 10) {
                insights.push(`<li class="insight-positive">Strong growth trend: ${growth}% increase in recent months</li>`);
            } else if (growth < -10) {
                insights.push(`<li class="insight-negative">Declining trend: ${Math.abs(growth)}% decrease in recent months</li>`);
            } else {
                insights.push(`<li>Stable enrollment pattern with ${Math.abs(growth)}% variation</li>`);
            }
        }
        
        const totalEnrollments = enrollmentData.reduce((sum, d) => sum + (+d.enrollments), 0);
        const totalActive = enrollmentData.reduce((sum, d) => sum + (+d.on_drug_count), 0);
        const overallRetention = ((totalActive / totalEnrollments) * 100).toFixed(1);
        
        insights.push(`<li>Overall retention rate: ${overallRetention}% across all periods</li>`);
        
        const peak = enrollmentData.reduce((max, d) => (+d.enrollments > +max.enrollments) ? d : max);
        insights.push(`<li>Peak enrollment: ${peak.enrollments} patients in ${monthLabelFromYm(peak.month)}</li>`);
    }
    
    showInsights('enrollment-insights', insights);
}

function generateRetentionInsights(retentionData) {
    const insights = [];
    
    if (retentionData.length > 0) {
        const rates = retentionData.map(d => +d.retention_rate);
        const avgRetention = (rates.reduce((sum, rate) => sum + rate, 0) / rates.length).toFixed(1);
        const maxRetention = Math.max(...rates);
        const minRetention = Math.min(...rates);
        
        insights.push(`<li>Average cohort retention: ${avgRetention}%</li>`);
        
        if (maxRetention - minRetention > 20) {
            insights.push(`<li class="insight-warning">High retention variability (${minRetention}% - ${maxRetention}%) suggests seasonal factors</li>`);
        }
        
        const bestCohort = retentionData.find(d => +d.retention_rate === maxRetention);
        if (bestCohort) {
            insights.push(`<li class="insight-positive">Best performing cohort: ${monthLabelFromYm(bestCohort.cohort_month)} (${maxRetention}%)</li>`);
        }
        
        if (avgRetention > 75) {
            insights.push(`<li class="insight-positive">Strong overall retention indicates effective patient support</li>`);
        } else if (avgRetention < 60) {
            insights.push(`<li class="insight-negative">Low retention rates require program optimization</li>`);
        }
    }
    
    showInsights('retention-insights', insights);
}

function generateQuarterlyInsights(quarterlyData) {
    const insights = [];
    
    if (quarterlyData.length > 1) {
        const latest = quarterlyData[quarterlyData.length - 1];
        const previous = quarterlyData[quarterlyData.length - 2];
        
        const enrollmentGrowth = (((+latest.enrollments - +previous.enrollments) / +previous.enrollments) * 100).toFixed(1);
        const retentionChange = (+latest.retention_rate - +previous.retention_rate).toFixed(1);
        
        if (enrollmentGrowth > 0) {
            insights.push(`<li class="insight-positive">Quarter-over-quarter growth: +${enrollmentGrowth}% enrollments</li>`);
        } else {
            insights.push(`<li class="insight-negative">Quarter-over-quarter decline: ${enrollmentGrowth}% enrollments</li>`);
        }
        
        if (retentionChange > 0) {
            insights.push(`<li class="insight-positive">Retention improved by ${retentionChange} percentage points</li>`);
        } else if (retentionChange < 0) {
            insights.push(`<li class="insight-warning">Retention declined by ${Math.abs(retentionChange)} percentage points</li>`);
        }
        
        const totalEnrollments = quarterlyData.reduce((sum, d) => sum + (+d.enrollments), 0);
        insights.push(`<li>Total enrollments across all quarters: ${totalEnrollments.toLocaleString()}</li>`);
        
        const bestQuarter = quarterlyData.reduce((max, d) => (+d.enrollments > +max.enrollments) ? d : max);
        insights.push(`<li>Peak quarter: ${bestQuarter.quarter} with ${bestQuarter.enrollments} enrollments</li>`);
    }
    
    showInsights('quarterly-insights', insights);
}

function showInsights(containerId, insights) {
    const container = document.getElementById(containerId);
    const list = document.getElementById(containerId + '-list');
    
    if (insights.length > 0) {
        list.innerHTML = insights.join('');
        container.style.display = 'block';
    } else {
        container.style.display = 'none';
    }
}

function generateExecutiveSummary() {
    const data = window.dashboardData || {};
    const kpis = data.kpis || {};
    
    // Update summary metrics
    const total = +kpis.total_patients || 0;
    const onDrug = +kpis.on_drug || 0;
    const retentionRate = total > 0 ? ((onDrug / total) * 100).toFixed(1) : 0;
    
    document.getElementById('summary-patients').textContent = total.toLocaleString();
    document.getElementById('summary-retention').textContent = retentionRate + '%';
    
    // Calculate growth from enrollment data if available
    if (data.enrollmentData && data.enrollmentData.length > 2) {
        const recent = data.enrollmentData.slice(-2);
        const growth = recent.length === 2 ? 
            (((+recent[1].enrollments - +recent[0].enrollments) / +recent[0].enrollments) * 100).toFixed(1) : 0;
        document.getElementById('summary-growth').textContent = growth + '%';
    } else {
        document.getElementById('summary-growth').textContent = 'N/A';
    }
    
    // Count active partners from pharmacy data
    const partnerCount = data.pharmacyData ? data.pharmacyData.length : 0;
    document.getElementById('summary-partners').textContent = partnerCount;
    
    // Generate executive highlights
    generateExecutiveHighlights(data);
    generateSuccessesAndChallenges(data);
    generateActionPlan(data);
}

function generateExecutiveHighlights(data) {
    const highlights = [];
    const kpis = data.kpis || {};
    const total = +kpis.total_patients || 0;
    const onDrug = +kpis.on_drug || 0;
    const retentionRate = total > 0 ? ((onDrug / total) * 100).toFixed(1) : 0;
    
    // Always show patient base info
    if (total > 0) {
        if (total > 300) {
            highlights.push(`✅ Strong patient base with ${total.toLocaleString()} total enrollments`);
        } else if (total > 100) {
            highlights.push(`📊 Growing patient base with ${total.toLocaleString()} total enrollments`);
        } else {
            highlights.push(`📊 Patient base: ${total.toLocaleString()} total enrollments`);
        }
    }
    
    // Retention rate highlight
    if (retentionRate >= 70) {
        highlights.push(`✅ Strong retention rate of ${retentionRate}%`);
    } else if (retentionRate >= 50) {
        highlights.push(`📊 Retention rate: ${retentionRate}%`);
    } else if (retentionRate > 0) {
        highlights.push(`⚠️ Retention rate: ${retentionRate}% - improvement opportunity`);
    }
    
    // Pharmacy network
    if (data.pharmacyData && data.pharmacyData.length > 0) {
        if (data.pharmacyData.length > 8) {
            highlights.push(`✅ Robust distribution network with ${data.pharmacyData.length} pharmacy partners`);
        } else {
            highlights.push(`📊 ${data.pharmacyData.length} active pharmacy partners`);
        }
    }
    
    // Gender balance
    if (data.genderData && total > 0) {
        const balanced = data.genderData.every(g => {
            const pct = (+g.cnt / total) * 100;
            return pct >= 35 && pct <= 65;
        });
        if (balanced) {
            highlights.push(`✅ Well-balanced patient demographics`);
        }
    }
    
    // Top prescriber performance
    if (data.prescriberData && data.prescriberData.length > 0) {
        const topRetention = Math.max(...data.prescriberData.map(p => +p.retention_rate));
        if (topRetention >= 80) {
            highlights.push(`✅ Top prescribers achieving ${topRetention.toFixed(1)}% retention`);
        } else if (topRetention >= 60) {
            highlights.push(`📊 Top prescriber retention: ${topRetention.toFixed(1)}%`);
        }
    }
    
    // Enrollment growth
    if (data.enrollmentData && data.enrollmentData.length > 2) {
        const recent = data.enrollmentData.slice(-2);
        if (recent.length === 2) {
            const growth = (((+recent[1].enrollments - +recent[0].enrollments) / +recent[0].enrollments) * 100).toFixed(1);
            if (growth > 10) {
                highlights.push(`✅ Strong growth: +${growth}% recent month`);
            } else if (growth > 0) {
                highlights.push(`📈 Positive growth: +${growth}%`);
            }
        }
    }
    
    // Fallback message if no highlights
    if (highlights.length === 0) {
        highlights.push(`📊 Program active with ${total} patients and ${retentionRate}% retention`);
    }
    
    document.getElementById('executive-highlights-content').innerHTML = highlights.join(' • ');
}

function generateSuccessesAndChallenges(data) {
    const successes = [];
    const challenges = [];
    const kpis = data.kpis || {};
    const total = +kpis.total_patients || 0;
    const retentionRate = total > 0 ? ((+kpis.on_drug / total) * 100).toFixed(1) : 0;
    
    // Successes
    if (data.pharmacyData && data.pharmacyData.length >= 10) {
        successes.push(`Strong pharmacy network with ${data.pharmacyData.length} active partners`);
    }
    
    if (data.prescriberData) {
        const highPerformers = data.prescriberData.filter(p => +p.retention_rate >= 80).length;
        if (highPerformers > 0) {
            successes.push(`${highPerformers} prescribers achieving 80%+ retention rates`);
        }
        
        const multiProvince = data.prescriberData.filter(p => +p.provinces_served > 1).length;
        if (multiProvince > 0) {
            successes.push(`${multiProvince} prescribers serving multiple provinces`);
        }
    }
    
    if (total >= 300) {
        successes.push(`Solid patient base of ${total.toLocaleString()} enrolled patients`);
    }
    
    if (data.enrollmentData && data.enrollmentData.length > 1) {
        const recent = data.enrollmentData.slice(-3);
        const hasGrowth = recent.some((d, i) => i > 0 && +d.enrollments > +recent[i-1].enrollments);
        if (hasGrowth) {
            successes.push(`Positive enrollment momentum in recent months`);
        }
    }
    
    // Challenges
    if (retentionRate < 70) {
        challenges.push(`Overall retention rate of ${retentionRate}% below industry benchmark`);
    }
    
    if (data.prescriberData) {
        const lowPerformers = data.prescriberData.filter(p => +p.retention_rate < 60).length;
        if (lowPerformers > 0) {
            challenges.push(`${lowPerformers} prescribers with retention rates below 60%`);
        }
    }
    
    if (data.pharmacyData && data.pharmacyData.length > 0 && total > 0) {
        const topPharmacyCount = +data.pharmacyData[0].total_enrollments || +data.pharmacyData[0].cnt || 0;
        let topPharmacyShare = ((topPharmacyCount / total) * 100).toFixed(1);
        
        // Flag data quality issues
        if (topPharmacyShare > 100) {
            window.dataQualityIssues = true;
            challenges.push(`Data quality issue: Pharmacy metrics exceed total patients`);
        } else if (topPharmacyShare > 35) {
            challenges.push(`High dependency on single pharmacy partner (${topPharmacyShare}%)`);
        }
    }
    
    if (data.quarterlyData && data.quarterlyData.length > 1) {
        const latest = data.quarterlyData[data.quarterlyData.length - 1];
        const previous = data.quarterlyData[data.quarterlyData.length - 2];
        const decline = +latest.enrollments < +previous.enrollments;
        if (decline) {
            challenges.push(`Quarter-over-quarter enrollment decline requires attention`);
        }
    }
    
    // Default messages if no specific data
    if (successes.length === 0) {
        successes.push('Established patient program with active enrollment');
        successes.push('Multi-partner distribution network in place');
    }
    
    if (challenges.length === 0) {
        challenges.push('Continue monitoring retention rates for optimization opportunities');
    }
    
    document.getElementById('success-highlights').innerHTML = successes.map(s => `<li>${s}</li>`).join('');
    document.getElementById('improvement-areas').innerHTML = challenges.map(c => `<li>${c}</li>`).join('');
}

function generateActionPlan(data) {
    const actions = [];
    const kpis = data.kpis || {};
    const retentionRate = +kpis.total_patients > 0 ? ((+kpis.on_drug / +kpis.total_patients) * 100).toFixed(1) : 0;
    
    // Retention improvement actions
    if (retentionRate < 70) {
        actions.push({
            priority: 'High',
            action: 'Implement retention improvement program',
            details: 'Analyze dropout reasons, enhance patient support, and develop targeted interventions'
        });
    }
    
    // Prescriber performance actions
    if (data.prescriberData) {
        const lowPerformers = data.prescriberData.filter(p => +p.retention_rate < 60).length;
        if (lowPerformers > 0) {
            actions.push({
                priority: 'Medium',
                action: 'Prescriber education and support program',
                details: `Focus on ${lowPerformers} prescribers with <60% retention rates through training and resources`
            });
        }
        
        const topPerformers = data.prescriberData.filter(p => +p.retention_rate >= 80);
        if (topPerformers.length > 0) {
            actions.push({
                priority: 'Medium',
                action: 'Best practice sharing initiative',
                details: `Leverage insights from ${topPerformers.length} high-performing prescribers to improve overall outcomes`
            });
        }
    }
    
    // Geographic expansion
    if (data.geoData && data.geoData.length < 5) {
        actions.push({
            priority: 'Low',
            action: 'Geographic expansion strategy',
            details: 'Identify and target underserved provinces for market expansion'
        });
    }
    
    // Partnership diversification
    if (data.pharmacyData && data.pharmacyData.length > 0) {
        const topShare = (+data.pharmacyData[0].cnt / +kpis.total_patients * 100);
        if (topShare > 35) {
            actions.push({
                priority: 'Medium',
                action: 'Partnership diversification',
                details: 'Reduce dependency risk by expanding pharmacy partner network'
            });
        }
    }
    
    // Default actions if no specific issues identified
    if (actions.length === 0) {
        actions.push({
            priority: 'Medium',
            action: 'Continuous improvement monitoring',
            details: 'Maintain regular performance reviews and optimization initiatives'
        });
    }
    
    const actionHTML = actions.map(a => 
        `<div class="action-item">
            <strong>[${a.priority} Priority]</strong> ${a.action}<br>
            <small style="color: #64748b;">${a.details}</small>
        </div>`
    ).join('');
    
    document.getElementById('action-plan').innerHTML = actionHTML;
}

async function loadAdvancedInsights(from, to) {
    const therapy = document.getElementById('therapy').value || 'ALL';
    
    try {
        // Patient Journey Analysis
        const journey = await fetch(apiBase + 'patient_journey_analysis&' + qs({from, to, therapy}))
            .then(r => r.text())
            .then(text => {
                try { return JSON.parse(text); } 
                catch(e) { console.error('Journey JSON error:', text); return {success: false}; }
            });
        if (journey.success && journey.data) {
            renderJourneyFunnel(journey.data);
            generateJourneyInsights(journey.data);
        }

        // Therapy Analysis
        const therapyAnalysis = await fetch(apiBase + 'therapy_analysis&' + qs({from, to, therapy}))
            .then(r => r.text())
            .then(text => {
                try { return JSON.parse(text); } 
                catch(e) { console.error('Therapy JSON error:', text); return {success: false}; }
            });
        if (therapyAnalysis.success && therapyAnalysis.data) {
            renderTherapyChart(therapyAnalysis.data);
            generateTherapyInsights(therapyAnalysis.data);
        }

        // Physician Specialty Analysis
        const specialty = await fetch(apiBase + 'physician_specialty_analysis&' + qs({from, to, therapy}))
            .then(r => r.text())
            .then(text => {
                try { return JSON.parse(text); } 
                catch(e) { console.error('Specialty JSON error:', text); return {success: false}; }
            });
        if (specialty.success && specialty.data) {
            renderSpecialtyChart(specialty.data);
            generateSpecialtyInsights(specialty.data);
        }
        
        // Payer Analysis
        // below commented - geting response data as null + div already hidden - 01152026
        // const payer = await fetch(apiBase + 'payer_analysis&' + qs({from, to, therapy}))
        //     .then(r => r.text())
        //     .then(text => {
        //         try { return JSON.parse(text); } 
        //         catch(e) { console.error('Payer JSON error:', text); return {success: false}; }
        //     });
        // if (payer.success && payer.data) {
        //     renderPayerChart(payer.data);
        //     generatePayerInsights(payer.data);
        // }

        // Program Coordinator Performance
        const coordinator = await fetch(apiBase + 'program_coordinator_performance&' + qs({from, to, therapy}))
            .then(r => r.text())
            .then(text => {
                try { return JSON.parse(text); } 
                catch(e) { console.error('Coordinator JSON error:', text); return {success: false}; }
            });
        if (coordinator.success && coordinator.data) {
            renderCoordinatorTable(coordinator.data);
            generateCoordinatorInsights(coordinator.data);
        }

        // Treatment History Analysis
        // const treatmentHistory = await fetch(apiBase + 'treatment_history_analysis')
        //     .then(r => r.text())
        //     .then(text => {
        //         try { return JSON.parse(text); } 
        //         catch(e) { console.error('Treatment history JSON error:', text); return {success: false}; }
        //     });
        // if (treatmentHistory.success && treatmentHistory.data) {
        //     renderTreatmentHistoryChart(treatmentHistory.data);
        //     generateTreatmentInsights(treatmentHistory.data);
        // }

    } catch (error) {
        console.error('Error loading advanced insights:', error);
    }
}

function renderJourneyFunnel(data) {
    const ctx = document.getElementById('journeyChart').getContext('2d');
    if (window.journeyChartObj) window.journeyChartObj.destroy();
    
    const stages = ['Enrollments Received','Calls Made', 'Successful Contacts', 'Enrollments Completed', 'Reimbursement Approved', 'On Drug'];
    const values = [+data.enrollments_received,+data.calls_made, +data.successful_contacts, +data.enrollments_completed, +data.reimbursement_approved, +data.patients_on_drug];
    
    window.journeyChartObj = new Chart(ctx, {
        type: 'bar',
        data: {
            labels: stages,
            datasets: [{
                label: 'Patient Count',
                data: values,
                backgroundColor: ['#9c27b0','#ef4444', '#f97316', '#eab308', '#22c55e', '#3b82f6'],
                borderWidth: 1
            }]
        },
        options: {
            responsive: true,
            scales: { y: { beginAtZero: true } }
        }
    });
}

function renderTherapyChart(data) {
    const ctx = document.getElementById('therapyChart').getContext('2d');
    if (window.therapyChartObj) window.therapyChartObj.destroy();
    
    const therapies = data.map(d => d.Therapy);
    const counts = data.map(d => +d.patient_count);
    
    window.therapyChartObj = new Chart(ctx, {
        type: 'doughnut',
        data: {
            labels: therapies,
            datasets: [{
                data: counts,
                backgroundColor: ['#3b82f6', '#10b981', '#f59e0b', '#ef4444', '#8b5cf6', '#06b6d4']
            }]
        },
        options: { responsive: true }
    });
}

function renderSpecialtyChart(data) {
    const ctx = document.getElementById('specialtyChart').getContext('2d');
    if (window.specialtyChartObj) window.specialtyChartObj.destroy();
    
    const specialties = data.map(d => d.specialty);
    const counts = data.map(d => +d.patient_count);
    
    window.specialtyChartObj = new Chart(ctx, {
        type: 'bar',
        data: {
            labels: specialties,
            datasets: [{
                label: 'Patient Count',
                data: counts,
                backgroundColor: 'rgba(59, 130, 246, 0.8)',
                borderColor: '#3b82f6',
                borderWidth: 1
            }]
        },
        options: {
            responsive: true,
            indexAxis: 'y',
            scales: { x: { beginAtZero: true } }
        }
    });
}

function renderPayerChart(data) {
    const ctx = document.getElementById('payerChart').getContext('2d');
    if (window.payerChartObj) window.payerChartObj.destroy();
    
    const payers = data.slice(0, 8).map(d => d.payer);
    const approvalRates = data.slice(0, 8).map(d => +d.approval_rate);
    
    window.payerChartObj = new Chart(ctx, {
        type: 'bar',
        data: {
            labels: payers,
            datasets: [{
                label: 'Approval Rate (%)',
                data: approvalRates,
                backgroundColor: 'rgba(16, 185, 129, 0.8)',
                borderColor: '#10b981',
                borderWidth: 1
            }]
        },
        options: {
            responsive: true,
            scales: { 
                y: { 
                    beginAtZero: true,
                    max: 100,
                    ticks: {
                        callback: function(value) {
                            return value + '%';
                        }
                    }
                } 
            }
        }
    });
}

function renderCoordinatorTable(data) {
    const tbody = document.querySelector('#coordinatorTable tbody');
    tbody.innerHTML = '';
    
    data.forEach(row => {
        const retentionClass = row.retention_rate >= 80 ? 'retention-high' : 
                              row.retention_rate >= 60 ? 'retention-medium' : 'retention-low';
        
        // Cap contact success rate at 100% and flag data quality issues
        let contactRate = +row.contact_success_rate;
        let contactDisplay = contactRate;
        if (contactRate > 100) {
            window.dataQualityIssues = true;
            contactDisplay = `${contactRate}% ⚠️`;
        } else {
            contactDisplay = `${contactRate}%`;
        }
        
        const contactClass = contactRate >= 80 ? 'retention-high' : 
                             contactRate >= 60 ? 'retention-medium' : 'retention-low';
        
        const tr = document.createElement('tr');
        tr.innerHTML = `
            <td><strong>${escapeHtml(row.coordinator)}</strong></td>
            <td class="performance-metric">${row.patient_count}</td>
            <td>${row.active_patients}</td>
            <td class="${retentionClass}"><strong>${row.retention_rate}%</strong></td>
            <td class="${contactClass}"><strong>${contactDisplay}</strong></td>
            <td>${row.avg_enrollment_days || 'N/A'} days</td>
        `;
        tbody.appendChild(tr);
    });
}

function renderTreatmentHistoryChart(data) {
    const ctx = document.getElementById('treatmentHistoryChart').getContext('2d');
    if (window.treatmentHistoryChartObj) window.treatmentHistoryChartObj.destroy();
    
    const treatments = data.map(d => d.treatment_type);
    const previousUsers = data.map(d => +d.previous_users);
    const currentUsers = data.map(d => +d.current_users);
    
    window.treatmentHistoryChartObj = new Chart(ctx, {
        type: 'bar',
        data: {
            labels: treatments,
            datasets: [
                {
                    label: 'Previous Users',
                    data: previousUsers,
                    backgroundColor: 'rgba(239, 68, 68, 0.8)',
                    borderColor: '#ef4444',
                    borderWidth: 1
                },
                {
                    label: 'Current Users',
                    data: currentUsers,
                    backgroundColor: 'rgba(34, 197, 94, 0.8)',
                    borderColor: '#22c55e',
                    borderWidth: 1
                }
            ]
        },
        options: {
            responsive: true,
            scales: { y: { beginAtZero: true } }
        }
    });
}

// Insight generation functions for new charts
function generateJourneyInsights(data) {
    const insights = [];
    let contactRate = +data.contact_success_rate;
    let enrollmentRate = +data.enrollment_conversion_rate;
    let drugStartRate = +data.drug_start_rate;
    
    // Cap rates at 100% and show data quality warning if exceeded
    if (contactRate > 100) {
        insights.push(`<li class="insight-warning">Contact success rate data quality issue detected (${contactRate}% > 100%)</li>`);
        contactRate = Math.min(contactRate, 100);
    }
    
    if (contactRate >= 80 && contactRate <= 100) {
        insights.push(`<li class="insight-positive">Excellent contact success rate of ${contactRate}%</li>`);
    } else if (contactRate < 60) {
        insights.push(`<li class="insight-negative">Low contact success rate of ${contactRate}% needs improvement</li>`);
    }
    
    if (enrollmentRate >= 70) {
        insights.push(`<li class="insight-positive">Strong enrollment conversion rate of ${enrollmentRate}%</li>`);
    } else if (enrollmentRate < 50) {
        insights.push(`<li class="insight-warning">Enrollment conversion rate of ${enrollmentRate}% below target</li>`);
    }
    
    if (data.avg_days_to_contact) {
        const avgDays = +data.avg_days_to_contact;
        if (avgDays <= 2) {
            insights.push(`<li class="insight-positive">Fast response time: ${avgDays} days to first contact</li>`);
        } else if (avgDays > 5) {
            insights.push(`<li class="insight-warning">Slow response time: ${avgDays} days to first contact</li>`);
        }
    }
    
    showInsights('journey-insights', insights);
}

function generateTherapyInsights(data) {
    const insights = [];
    const total = data.reduce((sum, d) => sum + (+d.patient_count), 0);
    
    if (data.length > 0) {
        const topTherapy = data[0];
        const topPercentage = ((+topTherapy.patient_count / total) * 100).toFixed(1);
        insights.push(`<li><strong>${topTherapy.Therapy}</strong> leads with ${topPercentage}% of patients</li>`);
        
        const highRetention = data.filter(d => +d.retention_rate >= 70);
        if (highRetention.length > 0) {
            insights.push(`<li class="insight-positive">${highRetention.length} therapies achieving 70%+ retention rates</li>`);
        }
        
        const avgAge = data.reduce((sum, d) => sum + (+d.avg_age), 0) / data.length;
        insights.push(`<li>Average patient age across therapies: ${avgAge.toFixed(1)} years</li>`);
    }
    
    showInsights('therapy-insights', insights);
}

function generateSpecialtyInsights(data) {
    const insights = [];
    
    if (data.length > 0) {
        const topSpecialty = data[0];
        insights.push(`<li><strong>${topSpecialty.specialty}</strong> leads with ${topSpecialty.patient_count} patients</li>`);
        
        const highPerformers = data.filter(d => +d.retention_rate >= 75);
        if (highPerformers.length > 0) {
            insights.push(`<li class="insight-positive">${highPerformers.length} specialties with 75%+ retention rates</li>`);
        }
        
        const totalPhysicians = data.reduce((sum, d) => sum + (+d.physician_count), 0);
        insights.push(`<li>Total active physicians across specialties: ${totalPhysicians}</li>`);
    }
    
    showInsights('specialty-insights', insights);
}

function generatePayerInsights(data) {
    const insights = [];
    
    if (data.length > 0) {
        const avgApproval = data.reduce((sum, d) => sum + (+d.approval_rate), 0) / data.length;
        insights.push(`<li>Average payer approval rate: ${avgApproval.toFixed(1)}%</li>`);
        
        const highApproval = data.filter(d => +d.approval_rate >= 80);
        if (highApproval.length > 0) {
            insights.push(`<li class="insight-positive">${highApproval.length} payers with 80%+ approval rates</li>`);
        }
        
        const topPayer = data[0];
        insights.push(`<li><strong>${topPayer.payer}</strong> covers ${topPayer.patient_count} patients with ${topPayer.approval_rate}% approval rate</li>`);
    }
    
    showInsights('payer-insights', insights);
}

function generateCoordinatorInsights(data) {
    const insights = [];
    
    if (data.length > 0) {
        const avgRetention = data.reduce((sum, d) => sum + (+d.retention_rate), 0) / data.length;
        insights.push(`<li>Average coordinator retention rate: ${avgRetention.toFixed(1)}%</li>`);
        
        const topPerformer = data.reduce((max, d) => (+d.retention_rate > +max.retention_rate) ? d : max);
        insights.push(`<li class="insight-positive">Top performer: <strong>${topPerformer.coordinator}</strong> (${topPerformer.retention_rate}% retention)</li>`);
        
        const totalPatients = data.reduce((sum, d) => sum + (+d.patient_count), 0);
        const avgLoad = (totalPatients / data.length).toFixed(1);
        insights.push(`<li>Average patient load per coordinator: ${avgLoad} patients</li>`);
        
        // Check for data quality issues in contact success rates
        const hasInvalidRates = data.some(d => +d.contact_success_rate > 100);
        if (hasInvalidRates) {
            insights.push(`<li class="insight-warning">⚠️ Contact success rates >100% indicate data quality issues - review call tracking</li>`);
        }
    }
    
    showInsights('coordinator-insights', insights);
}

function generateTreatmentInsights(data) {
    const insights = [];
    
    data.forEach(treatment => {
        const previousPct = ((+treatment.previous_users / +treatment.total_patients) * 100).toFixed(1);
        const currentPct = ((+treatment.current_users / +treatment.total_patients) * 100).toFixed(1);
        
        insights.push(`<li><strong>${treatment.treatment_type}:</strong> ${previousPct}% previous, ${currentPct}% current usage</li>`);
    });
    
    const biologicsData = data.find(d => d.treatment_type === 'Biologics');
    if (biologicsData && +biologicsData.previous_users > 0) {
        insights.push(`<li class="insight-positive">Strong biologics experience base with ${biologicsData.previous_users} previous users</li>`);
    }
    
    showInsights('treatment-insights', insights);
}

// Load therapy options
async function loadTherapyOptions() {
    try {
        const response = await fetch(apiBase + 'get_therapies');
        const data = await response.json();
        if (data.success && data.therapies) {
            const therapySelect = document.getElementById('therapy');
            // Clear existing options except ALL
            therapySelect.innerHTML = '<option value="ALL">ALL</option>';
            // Add therapy options
            data.therapies.forEach(therapy => {
                const option = document.createElement('option');
                option.value = therapy;
                option.textContent = therapy;
                therapySelect.appendChild(option);
            });
        }
    } catch (error) {
        console.error('Error loading therapy options:', error);
    }
}

// Initialize page
async function initializePage() {
    await loadTherapyOptions();
    loadData();
}

// auto-load
initializePage();
</script>

<!-- Formula Documentation Section -->
<div class="card" style="margin-top: 20px; border-left: 4px solid #6366f1;">
    <h3 style="color: #4338ca; margin-bottom: 16px;">📊 Calculation Formulas & Methodology</h3>
    <p class="description">This section documents how key metrics are calculated in the Market Insights dashboard. All calculations are based on data from the marketInsights table with enrollment dates converted from mm/dd/yyyy format using STR_TO_DATE function.</p>
    
    <div style="display: grid; grid-template-columns: 1fr 1fr; gap: 20px; margin-top: 16px;">
        <div>
            <h4 style="color: #1e293b; margin-bottom: 12px;">Core KPI Calculations</h4>
            <div style="background: #f8fafc; padding: 12px; border-radius: 6px; margin-bottom: 12px;">
                <strong>Retention Rate:</strong><br>
                <code style="background: #e2e8f0; padding: 2px 4px; border-radius: 3px;">
                    (Active Patients / Total Patients) × 100
                </code><br>
                <small style="color: #64748b;">Where Active = 'On Drug' status patients</small>
            </div>
            
            <div style="background: #f8fafc; padding: 12px; border-radius: 6px; margin-bottom: 12px;">
                <strong>Growth Rate (Recent):</strong><br>
                <code style="background: #e2e8f0; padding: 2px 4px; border-radius: 3px;">
                    ((Current Month - Previous Month) / Previous Month) × 100
                </code><br>
                <small style="color: #64748b;">Based on monthly enrollment trends</small>
            </div>
            
            <div style="background: #f8fafc; padding: 12px; border-radius: 6px; margin-bottom: 12px;">
                <strong>Contact Success Rate:</strong><br>
                <code style="background: #e2e8f0; padding: 2px 4px; border-radius: 3px;">
                    (Successful Contacts / Total Outgoing Calls) × 100
                </code><br>
                <small style="color: #64748b;">From patient journey analysis</small>
            </div>
            
            <div style="background: #f8fafc; padding: 12px; border-radius: 6px; margin-bottom: 12px;">
                <strong>Enrollment Conversion Rate:</strong><br>
                <code style="background: #e2e8f0; padding: 2px 4px; border-radius: 3px;">
                    (Completed Enrollments / Successful Contacts) × 100
                </code><br>
                <small style="color: #64748b;">Conversion from contact to enrollment</small>
            </div>
        </div>
        
        <div>
            <h4 style="color: #1e293b; margin-bottom: 12px;">Advanced Metrics</h4>
            <div style="background: #f8fafc; padding: 12px; border-radius: 6px; margin-bottom: 12px;">
                <strong>Cohort Retention Rate:</strong><br>
                <code style="background: #e2e8f0; padding: 2px 4px; border-radius: 3px;">
                    (Still Active in Cohort / Total Enrolled in Cohort) × 100
                </code><br>
                <small style="color: #64748b;">Grouped by enrollment month</small>
            </div>
            
            <div style="background: #f8fafc; padding: 12px; border-radius: 6px; margin-bottom: 12px;">
                <strong>Payer Approval Rate:</strong><br>
                <code style="background: #e2e8f0; padding: 2px 4px; border-radius: 3px;">
                    (Approved Decisions / Total Decisions) × 100
                </code><br>
                <small style="color: #64748b;">Based on Payer Decision 1 field</small>
            </div>
            
            <div style="background: #f8fafc; padding: 12px; border-radius: 6px; margin-bottom: 12px;">
                <strong>Average Days to Contact:</strong><br>
                <code style="background: #e2e8f0; padding: 2px 4px; border-radius: 3px;">
                    AVG(DATEDIFF(First Successful Contact, First Outgoing Call))
                </code><br>
                <small style="color: #64748b;">Time between first call and successful contact</small>
            </div>
            
            <div style="background: #f8fafc; padding: 12px; border-radius: 6px; margin-bottom: 12px;">
                <strong>Quarter-over-Quarter Change:</strong><br>
                <code style="background: #e2e8f0; padding: 2px 4px; border-radius: 3px;">
                    ((Current Quarter - Previous Quarter) / Previous Quarter) × 100
                </code><br>
                <small style="color: #64748b;">Quarterly enrollment comparison</small>
            </div>
        </div>
    </div>
    
    <div style="margin-top: 20px;">
        <h4 style="color: #1e293b; margin-bottom: 12px;">Data Quality & Validation Notes</h4>
        <div style="background: #fef3c7; border: 1px solid #f59e0b; padding: 12px; border-radius: 6px;">
            <h5 style="color: #92400e; margin: 0 0 8px 0;">⚠️ Formula Accuracy Assessment:</h5>
            <ul style="margin: 0; padding-left: 20px; color: #92400e;">
                <li><strong>Retention Rate:</strong> ✅ Correct - Uses proper 'On Drug' status count</li>
                <li><strong>Contact Success Rate:</strong> ⚠️ May show >100% - Check for data quality issues</li>
                <li><strong>Date Calculations:</strong> ✅ Correct - Properly converts mm/dd/yyyy format</li>
                <li><strong>Cohort Analysis:</strong> ✅ Correct - Groups by enrollment month accurately</li>
                <li><strong>Geographic Grouping:</strong> ✅ Correct - Handles NULL/empty provinces as 'Unknown'</li>
            </ul>
        </div>
    </div>
    
    <div style="margin-top: 16px;">
        <h4 style="color: #1e293b; margin-bottom: 12px;">SQL Query Examples</h4>
        <div style="background: #1e293b; color: #e2e8f0; padding: 12px; border-radius: 6px; font-family: monospace; font-size: 12px; overflow-x: auto;">
            <div style="margin-bottom: 12px;">
                <strong style="color: #60a5fa;">-- Retention Rate Calculation</strong><br>
                SELECT COUNT(*) as total_patients,<br>
                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM(`Patient Current Program Status` = 'On Drug') as on_drug,<br>
                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ROUND(SUM(`Patient Current Program Status` = 'On Drug') * 100.0 / COUNT(*), 2) as retention_rate<br>
                FROM marketInsights<br>
                WHERE STR_TO_DATE(`Enrollment Form Received Date`,'%m/%d/%Y') BETWEEN :from AND :to
            </div>
            
            <div style="margin-bottom: 12px;">
                <strong style="color: #60a5fa;">-- Cohort Retention Analysis</strong><br>
                SELECT DATE_FORMAT(STR_TO_DATE(`Enrollment Form Received Date`,'%m/%d/%Y'), '%Y-%m') as cohort_month,<br>
                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;COUNT(*) as total_enrolled,<br>
                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM(CASE WHEN `Patient Current Program Status` = 'On Drug' THEN 1 ELSE 0 END) as still_active,<br>
                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ROUND(SUM(CASE WHEN `Patient Current Program Status` = 'On Drug' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as retention_rate<br>
                FROM marketInsights<br>
                GROUP BY cohort_month<br>
                ORDER BY cohort_month DESC
            </div>
        </div>
    </div>
    
    <div style="margin-top: 16px; padding: 12px; background: #ecfdf5; border: 1px solid #10b981; border-radius: 6px;">
        <h5 style="color: #065f46; margin: 0 0 8px 0;">✅ Recommendations for Improved Accuracy:</h5>
        <ul style="margin: 0; padding-left: 20px; color: #065f46; font-size: 14px;">
            <li>Contact success rates >100% suggest data quality issues - review call logging process</li>
            <li>Consider adding data validation for date fields to prevent calculation errors</li>
            <li>Implement null handling for missing coordinator assignments</li>
            <li>Add validation for retention rate calculations to ensure they don't exceed 100%</li>
            <li>Consider time-based retention analysis (30-day, 90-day, 180-day retention)</li>
        </ul>
    </div>
</div>

</body>
</html>
