<?php

// class RecordCounter1 {
//     private $conn;
//     private $columnPrefix;
//     private $countRange;

//     public function __construct($conn,$condX,$tblX,$spcX,$spcX2) {
//         $this->conn = $conn;
//         $this->condX = $condX;
//         $this->tblX = $tblX;
//         $this->spcX = $spcX;
//         $this->spcX2 = $spcX2;
//     }

//     public function countRecords1($prefix,$range) {
//         $bCounts = [];
//         $cCounts = [];
        
//         foreach ($range as $value2) 
//         {
//             if($value2==57)
//             {
//                 $bCounts[] = 'count(case when `' . $prefix . '` != "" then w1.RESPID end) as ' . $prefix . 'b' . $value2;
//                 $cCounts[] = 'count(case when `' . $prefix . '` IN (1,3,5)  then w1.RESPID end) as ' . $prefix . 'c' . $value2;
//             }
//             else if($value2==58)
//             {
//                 $bCounts[] = 'count(case when `' . $prefix . '` != "" then w1.RESPID end) as ' . $prefix . 'b' . $value2;
//                 $cCounts[] = 'count(case when `' . $prefix . '` IN (2,4)  then w1.RESPID end) as ' . $prefix . 'c' . $value2;
//             }
//             else if($value2==59)
//             {
//                 $bCounts[] = 'count(case when `' . $prefix . '` != "" then w1.RESPID end) as ' . $prefix . 'b' . $value2;
//                 $cCounts[] = 'count(case when `' . $prefix . '` IN (2,3,4,5,6,7)  then w1.RESPID end) as ' . $prefix . 'c' . $value2;
//             }
//             else if($value2==32)
//             {
//                 $bCounts[] = 'count(case when `' . $prefix . '` != "" then w1.RESPID end) as ' . $prefix . 'b' . $value2;
//                 $cCounts[] = 'count(case when `' . $prefix . '` IN (8,9,10,11,12,13,99)  then w1.RESPID end) as ' . $prefix . 'c' . $value2;
//             }
//             else if($value2==33)
//             {
//                 $bCounts[] = 'count(case when `' . $prefix . '` != "" then w1.RESPID end) as ' . $prefix . 'b' . $value2;
//                 $cCounts[] = 'count(case when `' . $prefix . '` IN (4,5)  then w1.RESPID end) as ' . $prefix . 'c' . $value2;
//             }
//             else
//             {
//                 $bCounts[] = 'count(case when `' . $prefix . '` != "" then w1.RESPID end) as ' . $prefix . 'b' . $value2;
//                 $cCounts[] = 'count(case when `' . $prefix . '` = "'.$value2.'"  then w1.RESPID end) as ' . $prefix . 'c' . $value2;
//             }
//         }

//         $bSql = implode(', ', $bCounts);
//         $cSql = implode(', ', $cCounts);

//         $sql = "SELECT $bSql, $cSql, w1.RESPID FROM $this->tblX WHERE $this->spcX AND $this->spcX2 AND $this->condX";
//         $result = mysqli_query($this->conn, $sql);
//         $ALL = mysqli_fetch_array($result);

//         $data = [];

//         foreach($range as $value2)
//         {
//             $bCount = $ALL[$prefix . 'b' . $value2];
//             $cCount = $ALL[$prefix . 'c' . $value2];
            
//             $percentage = ($bCount > 0) ? round(($cCount / $bCount) * 100) : 0;

//             $data[] = [
//                 'bCount' => $bCount,
//                 'cCount' => $cCount,
//                 'percentage' => $percentage,
//             ];
//         }
//         return $data;
//     }
// }

// class RecordCounter1 {

//     private $conn;
//     private $condX;
//     private $tblX;
//     private $spcX;
//     private $spcX2;
//     private $trendStatus;
//     private $trendsValue;

//     public function __construct(
//         $conn,
//         $condX,
//         $tblX,
//         $spcX,
//         $spcX2,
//         $trendStatus = 'false',
//         $trendsValue = []
//     ) {
//         $this->conn        = $conn;
//         $this->condX       = $condX;
//         $this->tblX        = $tblX;
//         $this->spcX        = $spcX;
//         $this->spcX2       = $spcX2;
//         $this->trendStatus = $trendStatus;
//         $this->trendsValue = $trendsValue;
//     }

//     public function countRecords1($prefix, $range) {

//         // 🔹 NON-TREND MODE
//         if ($this->trendStatus === 'false') {
//             return $this->executeQuery($this->condX, $prefix, $range);
//         }

//         // 🔹 TREND MODE
//         $finalData = [];

//         foreach ($this->trendsValue as $trendVal) {

//             // NOTE: yaha assume hai trend column ka naam already defined hai
//             // example: trend_column = QTR / WAVE / PERIOD etc.
//             $trendCondition = $this->condX . " AND DQUARTER = " . intval($trendVal);

//             $finalData[$trendVal] = $this->executeQuery(
//                 $trendCondition,
//                 $prefix,
//                 $range
//             );
//         }

//         return $finalData;
//     }

//     // 🔁 Shared execution logic
//     private function executeQuery($condition, $prefix, $range) {

//         $bCounts = [];
//         $cCounts = [];

//         foreach ($range as $value2) {

//             // Base count (same everywhere)
//             $bCounts[] = "COUNT(CASE WHEN `{$prefix}` != '' THEN w1.RESPID END) AS {$prefix}b{$value2}";

//             // Special logic handling
//             if ($value2 == 57) {
//                 $cCounts[] = "COUNT(CASE WHEN `{$prefix}` IN (1,3,5) THEN w1.RESPID END) AS {$prefix}c{$value2}";
//             }
//             else if ($value2 == 58) {
//                 $cCounts[] = "COUNT(CASE WHEN `{$prefix}` IN (2,4) THEN w1.RESPID END) AS {$prefix}c{$value2}";
//             }
//             else if ($value2 == 59) {
//                 $cCounts[] = "COUNT(CASE WHEN `{$prefix}` IN (2,3,4,5,6,7) THEN w1.RESPID END) AS {$prefix}c{$value2}";
//             }
//             else if ($value2 == 32) {
//                 $cCounts[] = "COUNT(CASE WHEN `{$prefix}` IN (8,9,10,11,12,13,99) THEN w1.RESPID END) AS {$prefix}c{$value2}";
//             }
//             else if ($value2 == 33) {
//                 $cCounts[] = "COUNT(CASE WHEN `{$prefix}` IN (4,5) THEN w1.RESPID END) AS {$prefix}c{$value2}";
//             }
//             else {
//                 $cCounts[] = "COUNT(CASE WHEN `{$prefix}` = '{$value2}' THEN w1.RESPID END) AS {$prefix}c{$value2}";
//             }
//         }

//         $sql = "
//             SELECT 
//                 " . implode(', ', $bCounts) . ",
//                 " . implode(', ', $cCounts) . "
//             FROM {$this->tblX}
//             WHERE w1.RESPID=w2.RESPID AND {$this->spcX}
//               AND {$this->spcX2}
//               AND {$condition}
//         ";

//         $result = mysqli_query($this->conn, $sql);
//         $ALL = mysqli_fetch_assoc($result);

//         $data = [];

//         foreach ($range as $value2) {

//             $bCount = (int) $ALL[$prefix . 'b' . $value2];
//             $cCount = (int) $ALL[$prefix . 'c' . $value2];

//             $percentage = ($bCount > 0)
//                 ? round(($cCount / $bCount) * 100)
//                 : 0;

//             $data[] = [
//                 'bCount'     => $bCount,
//                 'cCount'     => $cCount,
//                 'percentage' => $percentage
//             ];
//         }

//         return $data;
//     }
// }

class RecordCounter1 {

    private $conn;
    private $condX;
    private $tblX;
    private $spcX;
    private $spcX2;
    private $trendStatus;
    private $trendsValue;

    public function __construct(
        $conn,
        $condX,
        $tblX,
        $spcX,
        $spcX2,
        $trendStatus = 'false',
        $trendsValue = []
    ) {
        $this->conn        = $conn;
        $this->condX       = $condX;
        $this->tblX        = $tblX;
        $this->spcX        = $spcX;
        $this->spcX2       = $spcX2;
        $this->trendStatus = $trendStatus;
        $this->trendsValue = $trendsValue;
    }

    public function countRecords1($prefix, $range) {

        // 🔹 NON-TREND MODE (existing behaviour)
        if ($this->trendStatus === 'false') {
            return $this->executeQuery($this->condX, $prefix, $range);
        }

        // 🔹 TREND MODE (Quarter + Half-Year)
        $finalData = [];

        foreach ($this->trendsValue as $trendVal) {

            $trendCondition = $this->buildTrendCondition($trendVal);

            $finalData[$trendVal] = $this->executeQuery(
                $trendCondition,
                $prefix,
                $range
            );
        }

        return $finalData;
    }

    /**
     * 🔧 Centralized trend condition resolver
     * 1–4  → Quarter
     * 90   → H1 (Q1+Q2)
     * 91   → H2 (Q3+Q4)
     */
    private function buildTrendCondition($trendVal) {

        // Quarter-wise
        if (in_array($trendVal, [1, 2, 3, 4], true)) {
            return $this->condX . " AND DQUARTER = " . intval($trendVal);
        }

        // Half-year H1
        if ($trendVal == 90) {
            return $this->condX . " AND DHALF IN (1)";
        }

        // Half-year H2
        if ($trendVal == 91) {
            return $this->condX . " AND DHALF IN (2)";
        }

        // Safety fallback
        return $this->condX;
    }

    // 🔁 Shared execution logic (original special rules preserved)
    private function executeQuery($condition, $prefix, $range) {

        $bCounts = [];
        $cCounts = [];

        foreach ($range as $value2) {

            // Base count
            if($prefix=="CM_C2")
            {
                $bCounts[] = "COUNT(CASE WHEN `{$prefix}` != '' AND `{$prefix}` != '4' THEN w1.RESPID END) AS {$prefix}b{$value2}";
            }
            else
            {
                $bCounts[] = "COUNT(CASE WHEN `{$prefix}` != '' THEN w1.RESPID END) AS {$prefix}b{$value2}";
            }
            

            // Special business logic
            if ($value2 == 57) {
                $cCounts[] =
                    "COUNT(CASE WHEN `{$prefix}` IN (1,3,5) THEN w1.RESPID END) AS {$prefix}c{$value2}";
            }
            else if ($value2 == 58) {
                $cCounts[] =
                    "COUNT(CASE WHEN `{$prefix}` IN (2,4) THEN w1.RESPID END) AS {$prefix}c{$value2}";
            }
            else if ($value2 == 59) {
                $cCounts[] =
                    "COUNT(CASE WHEN `{$prefix}` IN (2,3,4,5,6,7) THEN w1.RESPID END) AS {$prefix}c{$value2}";
            }
            else if ($value2 == 32) {
                $cCounts[] =
                    "COUNT(CASE WHEN `{$prefix}` IN (8,9,10,11,12,13,99) THEN w1.RESPID END) AS {$prefix}c{$value2}";
            }
            else if ($value2 == 33) {
                $cCounts[] =
                    "COUNT(CASE WHEN `{$prefix}` IN (4,5) THEN w1.RESPID END) AS {$prefix}c{$value2}";
            }
            else {
                $cCounts[] =
                    "COUNT(CASE WHEN `{$prefix}` = '{$value2}' THEN w1.RESPID END) AS {$prefix}c{$value2}";
            }
        }

        $sql = "
            SELECT
                " . implode(', ', $bCounts) . ",
                " . implode(', ', $cCounts) . "
            FROM {$this->tblX}
            WHERE w1.RESPID=w2.RESPID AND {$this->spcX}
              AND {$this->spcX2}
              AND {$condition}
        ";

        $result = mysqli_query($this->conn, $sql);
        $ALL = mysqli_fetch_assoc($result);

        $data = [];

        foreach ($range as $value2) {

            $bCount = (int) $ALL[$prefix . 'b' . $value2];
            $cCount = (int) $ALL[$prefix . 'c' . $value2];

            $percentage = ($bCount > 0)
                ? ($cCount / $bCount) * 100
                : 0;

            $data[] = [
                'bCount'     => $bCount,
                'cCount'     => $cCount,
                'percentage' => $percentage
            ];
        }

        return $data;
    }
}


// class RecordCounter2 {
//     private $conn;
//     private $columnPrefix;
//     private $countRange;

//     public function __construct($conn,$condX,$tblX,$spcX,$spcX2) {
//         $this->conn = $conn;
//         $this->condX = $condX;
//         $this->tblX = $tblX;
//         $this->spcX = $spcX;
//         $this->spcX2 = $spcX2;
//     }

//     public function countRecords2($prefix,$range) {
//         $bCounts = [];
//         $cCounts = [];

//         foreach ($range as $value2) 
//         {
//             $bCounts[] = 'count(case when `' . $prefix .'_' . $value2 .'` != "" then w1.RESPID end) as ' . $prefix . 'b' . $value2;
//             $cCounts[] = 'count(case when `' . $prefix .'_' . $value2 .'` = "1"  then w1.RESPID end) as ' . $prefix . 'c' . $value2;
//         }

//         $bSql = implode(', ', $bCounts);
//         $cSql = implode(', ', $cCounts);

//         $sql = "SELECT $bSql, $cSql, w1.RESPID FROM $this->tblX WHERE $this->spcX AND $this->spcX2 AND $this->condX";
//         $result = mysqli_query($this->conn, $sql);
//         $ALL = mysqli_fetch_array($result);

//         $data = [];

//         foreach($range as $value2)
//         {
//             $bCount = $ALL[$prefix . 'b' . $value2];
//             $cCount = $ALL[$prefix . 'c' . $value2];
            
//             $percentage = ($bCount > 0) ? round(($cCount / $bCount) * 100) : 0;

//             $data[] = [
//                 'bCount' => $bCount,
//                 'cCount' => $cCount,
//                 'percentage' => $percentage,
//             ];
//         }
//         return $data;
//     }
// }

// class RecordCounter2 {

//     private $conn;
//     private $condX;
//     private $tblX;
//     private $spcX;
//     private $spcX2;
//     private $trendStatus;
//     private $trendsValue;

//     public function __construct(
//         $conn,
//         $condX,
//         $tblX,
//         $spcX,
//         $spcX2,
//         $trendStatus = 'false',
//         $trendsValue = []
//     ) {
//         $this->conn         = $conn;
//         $this->condX        = $condX;
//         $this->tblX         = $tblX;
//         $this->spcX         = $spcX;
//         $this->spcX2        = $spcX2;
//         $this->trendStatus  = $trendStatus;
//         $this->trendsValue  = $trendsValue;
//     }

//     public function countRecords2($prefix, $range) {

//         // 🔹 NON-TREND MODE
//         if ($this->trendStatus === 'false') {
//             return $this->executeQuery($this->condX, $prefix, $range);
//         }

//         // 🔹 TREND MODE
//         $finalData = [];

//         foreach ($this->trendsValue as $trendVal) {

//             // yaha assume hai trend condition already prepared hai
//             // example: trendcondX = "trend_column = 1"
//             $trendCondition = $this->condX . " AND DQUARTER = " . intval($trendVal);

//             $finalData[$trendVal] = $this->executeQuery(
//                 $trendCondition,
//                 $prefix,
//                 $range
//             );
//         }

//         return $finalData;
//     }

//     // 🔁 Shared logic (single source of truth)
//     private function executeQuery($condition, $prefix, $range) {

//         $bCounts = [];
//         $cCounts = [];

//         foreach ($range as $value2) {
//             $bCounts[] = "COUNT(CASE WHEN `{$prefix}_{$value2}` != '' THEN w1.RESPID END) AS {$prefix}b{$value2}";
//             $cCounts[] = "COUNT(CASE WHEN `{$prefix}_{$value2}` = '1' THEN w1.RESPID END) AS {$prefix}c{$value2}";
//         }

//         $sql = "
//             SELECT 
//                 " . implode(', ', $bCounts) . ",
//                 " . implode(', ', $cCounts) . "
//             FROM {$this->tblX}
//             WHERE w1.RESPID=w2.RESPID AND {$this->spcX}
//               AND {$this->spcX2}
//               AND {$condition}
//         ";

//         $result = mysqli_query($this->conn, $sql);
//         $ALL = mysqli_fetch_assoc($result);

//         $data = [];

//         foreach ($range as $value2) {

//             $bCount = (int) $ALL[$prefix . 'b' . $value2];
//             $cCount = (int) $ALL[$prefix . 'c' . $value2];

//             $percentage = ($bCount > 0)
//                 ? round(($cCount / $bCount) * 100)
//                 : 0;

//             $data[] = [
//                 'bCount'     => $bCount,
//                 'cCount'     => $cCount,
//                 'percentage' => $percentage
//             ];
//         }

//         return $data;
//     }
// }

class RecordCounter2 {

    private $conn;
    private $condX;
    private $tblX;
    private $spcX;
    private $spcX2;
    private $trendStatus;
    private $trendsValue;

    public function __construct(
        $conn,
        $condX,
        $tblX,
        $spcX,
        $spcX2,
        $trendStatus = 'false',
        $trendsValue = []
    ) {
        $this->conn         = $conn;
        $this->condX        = $condX;
        $this->tblX         = $tblX;
        $this->spcX         = $spcX;
        $this->spcX2        = $spcX2;
        $this->trendStatus  = $trendStatus;
        $this->trendsValue  = $trendsValue;
    }

    public function countRecords2($prefix, $range) {

        // 🔹 NON-TREND MODE (existing behaviour)
        if ($this->trendStatus === 'false') {
            return $this->executeQuery($this->condX, $prefix, $range);
        }

        // 🔹 TREND MODE (Quarter + Half-Year)
        $finalData = [];

        foreach ($this->trendsValue as $trendVal) {

            $trendCondition = $this->buildTrendCondition($trendVal);

            $finalData[$trendVal] = $this->executeQuery(
                $trendCondition,
                $prefix,
                $range
            );
        }

        return $finalData;
    }

    /**
     * 🔧 Centralized trend condition resolver
     * 1–4  → Quarter
     * 90   → H1 (Q1+Q2)
     * 91   → H2 (Q3+Q4)
     */
    private function buildTrendCondition($trendVal) {

        // Quarter-wise
        if (in_array($trendVal, [1, 2, 3, 4], true)) {
            return $this->condX . " AND DQUARTER = " . intval($trendVal);
        }

        // Half-year H1
        if ($trendVal == 90) {
            return $this->condX . " AND DHALF IN (1)";
        }

        // Half-year H2
        if ($trendVal == 91) {
            return $this->condX . " AND DHALF IN (2)";
        }

        // Safety fallback
        return $this->condX;
    }

    // 🔁 Shared logic (single source of truth)
    private function executeQuery($condition, $prefix, $range) {

        $bCounts = [];
        $cCounts = [];

        foreach ($range as $value2) {
            $bCounts[] = "COUNT(CASE WHEN `{$prefix}_{$value2}` != '' THEN w1.RESPID END) AS {$prefix}b{$value2}";
            $cCounts[] = "COUNT(CASE WHEN `{$prefix}_{$value2}` = '1' THEN w1.RESPID END) AS {$prefix}c{$value2}";
        }

        $sql = "
            SELECT 
                " . implode(', ', $bCounts) . ",
                " . implode(', ', $cCounts) . "
            FROM {$this->tblX}
            WHERE w1.RESPID=w2.RESPID AND {$this->spcX}
              AND {$this->spcX2}
              AND {$condition}
        ";

        $result = mysqli_query($this->conn, $sql);
        $ALL = mysqli_fetch_assoc($result);

        $data = [];

        foreach ($range as $value2) {

            $bCount = (int) $ALL[$prefix . 'b' . $value2];
            $cCount = (int) $ALL[$prefix . 'c' . $value2];

            $percentage = ($bCount > 0)
                ? ($cCount / $bCount) * 100
                : 0;

            $data[] = [
                'bCount'     => $bCount,
                'cCount'     => $cCount,
                'percentage' => $percentage
            ];
        }

        return $data;
    }
}



// class RecordCounter3 {
//     private $conn;
//     private $columnPrefix;
//     private $countRange;

//     public function __construct($conn,$condX,$tblX,$spcX,$spcX2) {
//         $this->conn = $conn;
//         $this->condX = $condX;
//         $this->tblX = $tblX;
//         $this->spcX = $spcX;
//         $this->spcX2 = $spcX2;
//     }

//     public function countRecords3($prefix,$range) {
//         $bCounts = [];
//         $cCounts = [];
//         $bCounts[] = 'count(case when `' . $prefix .'_1` = "1" OR `' . $prefix .'_2` = "1" OR `' . $prefix .'_3` = "1" OR `' . $prefix .'_4` = "1" OR `' . $prefix .'_5` = "1" OR `' . $prefix .'_6` = "1" OR `' . $prefix .'_7` = "1" OR `' . $prefix .'_8` = "1" OR `' . $prefix .'_9` = "1" then w1.RESPID end) as ' . $prefix . 'b';
        
//         foreach ($range as $value2) 
//         {
//             $cCounts[] = 'count(case when `' . $prefix .'_' . $value2 .'` = "1"  then w1.RESPID end) as ' . $prefix . 'c' . $value2;
//         }

//         $bSql = implode(', ', $bCounts);
//         $cSql = implode(', ', $cCounts);

//         $sql = "SELECT $bSql, $cSql, w1.RESPID FROM $this->tblX WHERE $this->spcX AND $this->spcX2 AND $this->condX";
//         $result = mysqli_query($this->conn, $sql);
//         $ALL = mysqli_fetch_array($result);

//         $data = [];

//         foreach($range as $value2)
//         {
//             $bCount = $ALL[$prefix . 'b'];
//             $cCount = $ALL[$prefix . 'c' . $value2];
            
//             $percentage = ($bCount > 0) ? round(($cCount / $bCount) * 100) : 0;

//             $data[] = [
//                 'bCount' => $bCount,
//                 'cCount' => $cCount,
//                 'percentage' => $percentage,
//             ];
//         }
//         return $data;
//     }
// }


class RecordCounter3 {

    private $conn;
    private $condX;
    private $tblX;
    private $spcX;
    private $spcX2;
    private $trendStatus;
    private $trendsValue;

    public function __construct(
        $conn,
        $condX,
        $tblX,
        $spcX,
        $spcX2,
        $trendStatus = 'false',
        $trendsValue = []
    ) {
        $this->conn        = $conn;
        $this->condX       = $condX;
        $this->tblX        = $tblX;
        $this->spcX        = $spcX;
        $this->spcX2       = $spcX2;
        $this->trendStatus = $trendStatus;
        $this->trendsValue = $trendsValue;
    }

    public function countRecords3($prefix, $range) {

        // 🔹 NON-TREND MODE (existing behaviour)
        if ($this->trendStatus === 'false') {
            return $this->executeQuery($this->condX, $prefix, $range);
        }

        // 🔹 TREND MODE (Quarter + Half-Year)
        $finalData = [];

        foreach ($this->trendsValue as $trendVal) {

            $trendCondition = $this->buildTrendCondition($trendVal);

            $finalData[$trendVal] = $this->executeQuery(
                $trendCondition,
                $prefix,
                $range
            );
        }

        return $finalData;
    }

    /**
     * 🔧 Centralized trend condition resolver
     * 1–4  → Quarter
     * 90   → H1 (Q1+Q2)
     * 91   → H2 (Q3+Q4)
     */
    private function buildTrendCondition($trendVal) {

        // Quarter-wise
        if (in_array($trendVal, [1, 2, 3, 4], true)) {
            return $this->condX . " AND DQUARTER = " . intval($trendVal);
        }

        // Half-year H1
        if ($trendVal == 90) {
            return $this->condX . " AND DHALF IN (1)";
        }

        // Half-year H2
        if ($trendVal == 91) {
            return $this->condX . " AND DHALF IN (2)";
        }

        // Safety fallback
        return $this->condX;
    }

    // 🔁 Shared execution logic (NO CHANGE in original logic)
    private function executeQuery($condition, $prefix, $range) {

        $bCounts = [];
        $cCounts = [];

        // 🔒 ORIGINAL bCount LOGIC (as-is)
        $bCounts[] = "
            COUNT(
                CASE 
                    WHEN `{$prefix}_1` = '1'
                      OR `{$prefix}_2` = '1'
                      OR `{$prefix}_3` = '1'
                      OR `{$prefix}_4` = '1'
                      OR `{$prefix}_5` = '1'
                      OR `{$prefix}_6` = '1'
                      OR `{$prefix}_7` = '1'
                      OR `{$prefix}_8` = '1'
                      OR `{$prefix}_9` = '1'
                THEN w1.RESPID END
            ) AS {$prefix}b
        ";

        // 🔒 ORIGINAL cCount LOGIC (as-is)
        foreach ($range as $value2) {
            $cCounts[] =
                "COUNT(CASE WHEN `{$prefix}_{$value2}` = '1' THEN w1.RESPID END) AS {$prefix}c{$value2}";
        }

        $sql = "
            SELECT
                " . implode(', ', $bCounts) . ",
                " . implode(', ', $cCounts) . "
            FROM {$this->tblX}
            WHERE w1.RESPID=w2.RESPID AND {$this->spcX}
              AND {$this->spcX2}
              AND {$condition}
        ";

        $result = mysqli_query($this->conn, $sql);
        $ALL = mysqli_fetch_assoc($result);

        $data = [];

        foreach ($range as $value2) {

            $bCount = (int) $ALL[$prefix . 'b'];
            $cCount = (int) $ALL[$prefix . 'c' . $value2];

            $percentage = ($bCount > 0)
                ? ($cCount / $bCount) * 100
                : 0;

            $data[] = [
                'bCount'     => $bCount,
                'cCount'     => $cCount,
                'percentage' => $percentage
            ];
        }

        return $data;
    }
}


// class RecordCounter4 {
//     private $conn;
//     private $columnPrefix;
//     private $countRange;

//     public function __construct($conn,$condX,$tblX,$spcX) {
//         $this->conn = $conn;
//         $this->condX = $condX;
//         $this->tblX = $tblX;
//         $this->spcX = $spcX;
//     }

//     public function countRecords4($prefix,$range) {
//         $bCounts = [];
//         $cCounts = [];

//         foreach ($range as $value2) 
//         {
//             $bCounts[] = 'count(case when `' . $prefix . '` != "" then w1.RESPID end) as ' . $prefix . 'b' . $value2;
//             $cCounts[] = 'count(case when `' . $prefix . '` = "'.$value2.'"  then w1.RESPID end) as ' . $prefix . 'c' . $value2;
//         }

//         $bSql = implode(', ', $bCounts);
//         $cSql = implode(', ', $cCounts);

//         $sql = "SELECT $bSql, $cSql, w1.RESPID FROM $this->tblX WHERE $this->spcX AND $this->condX";
//         $result = mysqli_query($this->conn, $sql);
//         $ALL = mysqli_fetch_array($result);

//         $data = [];

//         foreach($range as $value2)
//         {
//             $bCount = $ALL[$prefix . 'b' . $value2];
//             $cCount = $ALL[$prefix . 'c' . $value2];
            
//             $percentage = ($bCount > 0) ? round(($cCount / $bCount) * 100) : 0;

//             $data[] = [
//                 'bCount' => $bCount,
//                 'cCount' => $cCount,
//                 'percentage' => $percentage,
//             ];
//         }
//         return $data;
//     }
// }


class RecordCounter4 {

    private $conn;
    private $condX;
    private $tblX;
    private $spcX;
    private $trendStatus;
    private $trendsValue;

    public function __construct(
        $conn,
        $condX,
        $tblX,
        $spcX,
        $trendStatus = 'false',
        $trendsValue = []
    ) {
        $this->conn        = $conn;
        $this->condX       = $condX;
        $this->tblX        = $tblX;
        $this->spcX        = $spcX;
        $this->trendStatus = $trendStatus;
        $this->trendsValue = $trendsValue;
    }

    public function countRecords4($prefix, $range) {

        // 🔹 NON-TREND MODE (existing behaviour)
        if ($this->trendStatus === 'false') {
            return $this->executeQuery($this->condX, $prefix, $range);
        }

        // 🔹 TREND MODE (Quarter + Half-Year)
        $finalData = [];

        foreach ($this->trendsValue as $trendVal) {

            $trendCondition = $this->buildTrendCondition($trendVal);

            $finalData[$trendVal] = $this->executeQuery(
                $trendCondition,
                $prefix,
                $range
            );
        }

        return $finalData;
    }

    /**
     * 🔧 Centralized trend condition resolver
     * 1–4  → Quarter
     * 90   → H1 (Q1+Q2)
     * 91   → H2 (Q3+Q4)
     */
    private function buildTrendCondition($trendVal) {

        // Quarter-wise
        if (in_array($trendVal, [1, 2, 3, 4], true)) {
            return $this->condX . " AND DQUARTER = " . intval($trendVal);
        }

        // Half-year H1
        if ($trendVal == 90) {
            return $this->condX . " AND DHALF IN (1)";
        }

        // Half-year H2
        if ($trendVal == 91) {
            return $this->condX . " AND DHALF IN (2)";
        }

        // Safety fallback
        return $this->condX;
    }

    // 🔁 Shared execution logic (NO CHANGE from original)
    private function executeQuery($condition, $prefix, $range) {

        $bCounts = [];
        $cCounts = [];

        foreach ($range as $value2) {
            $bCounts[] =
                "COUNT(CASE WHEN `{$prefix}` != '' THEN w1.RESPID END) AS {$prefix}b{$value2}";

            $cCounts[] =
                "COUNT(CASE WHEN `{$prefix}` = '{$value2}' THEN w1.RESPID END) AS {$prefix}c{$value2}";
        }

        $sql = "
            SELECT
                " . implode(', ', $bCounts) . ",
                " . implode(', ', $cCounts) . "
            FROM {$this->tblX}
            WHERE w1.RESPID=w2.RESPID AND {$this->spcX}
              AND {$condition}
        ";

        $result = mysqli_query($this->conn, $sql);
        $ALL = mysqli_fetch_assoc($result);

        $data = [];

        foreach ($range as $value2) {

            $bCount = (int) $ALL[$prefix . 'b' . $value2];
            $cCount = (int) $ALL[$prefix . 'c' . $value2];

            $percentage = ($bCount > 0)
                ? ($cCount / $bCount) * 100
                : 0;

            $data[] = [
                'bCount'     => $bCount,
                'cCount'     => $cCount,
                'percentage' => $percentage
            ];
        }

        return $data;
    }
}




class RecordCounter5 {
    private $conn;
    private $columnPrefix;
    private $countRange;

    public function __construct($conn,$condX,$tblX,$spcX,$spcX2) {
        $this->conn = $conn;
        $this->condX = $condX;
        $this->tblX = $tblX;
        $this->spcX = $spcX;
        $this->spcX2 = $spcX2;
    }

    public function countRecords5($prefix,$range) {
        $bCounts = [];
        $cCounts = [];
        
        foreach ($range as $value2) 
        {
            if($value2==41)
            {
                $bCounts[] = 'count(case when `' . $prefix . '` != "" then w1.RESPID end) as ' . $prefix . 'b' . $value2;
                $cCounts[] = 'count(case when `' . $prefix . '` IN ("18","19","20","21","22","23","24","25","26","27","28")  then w1.RESPID end) as ' . $prefix . 'c' . $value2;
            }
            else if($value2==42)
            {
                $bCounts[] = 'count(case when `' . $prefix . '` != "" then w1.RESPID end) as ' . $prefix . 'b' . $value2;
                $cCounts[] = 'count(case when `' . $prefix . '` IN (29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44)  then w1.RESPID end) as ' . $prefix . 'c' . $value2;
            }
            else if($value2==43)
            {
                $bCounts[] = 'count(case when `' . $prefix . '` != "" then w1.RESPID end) as ' . $prefix . 'b' . $value2;
                $cCounts[] = 'count(case when `' . $prefix . '` IN (45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)  then w1.RESPID end) as ' . $prefix . 'c' . $value2;
            }
            else
            {
                $bCounts[] = 'count(case when `' . $prefix . '` != "" then w1.RESPID end) as ' . $prefix . 'b' . $value2;
                $cCounts[] = 'count(case when `' . $prefix . '` IN (61,62,63,64,65)  then w1.RESPID end) as ' . $prefix . 'c' . $value2;
            }
        }

        $bSql = implode(', ', $bCounts);
        $cSql = implode(', ', $cCounts);

        $sql = "SELECT $bSql, $cSql, w1.RESPID FROM $this->tblX WHERE $this->spcX AND $this->spcX2 AND $this->condX";
        $result = mysqli_query($this->conn, $sql);
        $ALL = mysqli_fetch_array($result);

        $data = [];

        foreach($range as $value2)
        {
            $bCount = $ALL[$prefix . 'b' . $value2];
            $cCount = $ALL[$prefix . 'c' . $value2];
            
            $percentage = ($bCount > 0) ? round(($cCount / $bCount) * 100) : 0;

            $data[] = [
                'bCount' => $bCount,
                'cCount' => $cCount,
                'percentage' => $percentage,
            ];
        }
        return $data;
    }
}

class RecordCounter6 {
    private $conn;
    private $columnPrefix;
    private $countRange;

    public function __construct($conn,$condX,$tblX,$spcX,$spcX2) {
        $this->conn = $conn;
        $this->condX = $condX;
        $this->tblX = $tblX;
        $this->spcX = $spcX;
        $this->spcX2 = $spcX2;
    }

    public function countRecords6($prefix,$range,$country_range) {
        $bCounts = [];
        $cCounts = [];
        
        foreach($country_range as $key => $value)
        {
            foreach ($range as $value2) 
            {
                $bCounts[] = 'count(case when `S0` = '.$value.' AND `' . $prefix . '` != "" then w1.RESPID end) as ' . $prefix . 'b' . $value2 . $key;
                $cCounts[] = 'count(case when `S0` = '.$value.' AND `' . $prefix . '` = "'.$value2.'"  then w1.RESPID end) as ' . $prefix . 'c' . $value2 . $key;
            }
        }
        
        $bSql = implode(', ', $bCounts);
        $cSql = implode(', ', $cCounts);

        $sql = "SELECT $bSql, $cSql, w1.RESPID FROM $this->tblX WHERE $this->spcX AND $this->spcX2 AND $this->condX";
        $result = mysqli_query($this->conn, $sql);
        $ALL = mysqli_fetch_array($result);

        $data = [];

        foreach($range as $value2)
        {
            $data2 = array();
            foreach ($country_range as $key => $value)
            {
                $bCount = $ALL[$prefix . 'b' . $value2 . $key];
                $cCount = $ALL[$prefix . 'c' . $value2 . $key];
                
                $percentage = ($bCount > 0) ? round(($cCount / $bCount) * 100) : 0;
    
                $data2[] = [
                    'bCount' => $bCount,
                    'cCount' => $cCount,
                    'percentage' => $percentage,
                ];
            }
            array_push($data,$data2);
        }
        return $data;
    }
}



class RecordCounter7 {
    private $conn;
    private $columnPrefix;
    private $countRange;

    public function __construct($conn,$condX,$tblX,$spcX,$spcX2) {
        $this->conn = $conn;
        $this->condX = $condX;
        $this->tblX = $tblX;
        $this->spcX = $spcX;
        $this->spcX2 = $spcX2;
    }

    public function countRecords7($prefix,$range) {
        $bCounts = [];
        $cCounts = [];
        
        foreach ($range as $value2) 
        {
            $bCounts[] = 'count(case when `' . $prefix . '` != "" then w1.RESPID end) as ' . $prefix . 'b' . $value2;
            $cCounts[] = 'count(case when `' . $prefix . '` IN (1,3,5)  then w1.RESPID end) as ' . $prefix . 'c' . $value2;
        }

        $bSql = implode(', ', $bCounts);
        $cSql = implode(', ', $cCounts);

        $sql = "SELECT $bSql, $cSql, w1.RESPID FROM $this->tblX WHERE $this->spcX AND $this->spcX2 AND $this->condX";
        $result = mysqli_query($this->conn, $sql);
        $ALL = mysqli_fetch_array($result);

        $data = [];

        foreach($range as $value2)
        {
            $bCount = $ALL[$prefix . 'b' . $value2];
            $cCount = $ALL[$prefix . 'c' . $value2];
            
            $percentage = ($bCount > 0) ? round(($cCount / $bCount) * 100) : 0;

            $data[] = [
                'bCount' => $bCount,
                'cCount' => $cCount,
                'percentage' => $percentage,
            ];
        }
        return $data;
    }
}


class RecordCounter8 {
    private $conn;
    private $columnPrefix;
    private $countRange;

    public function __construct($conn,$condX,$tblX,$spcX,$spcX2) {
        $this->conn = $conn;
        $this->condX = $condX;
        $this->tblX = $tblX;
        $this->spcX = $spcX;
        $this->spcX2 = $spcX2;
    }

    public function countRecords8($prefix,$range) {
        $bCounts = [];
        $cCounts = [];

        $bCounts[] = 'count(case when `' . $prefix .'_1` != "" then w1.RESPID end) as ' . $prefix . 'b';
        foreach ($range as $value2) 
        {
            $cCounts[] = 'count(case when `' . $prefix .'_' . $value2 .'` = "1"  then w1.RESPID end) as ' . $prefix . 'c' . $value2;
        }

        $bSql = implode(', ', $bCounts);
        $cSql = implode(', ', $cCounts);

        $sql = "SELECT $bSql, $cSql, w1.RESPID FROM $this->tblX WHERE $this->spcX AND $this->spcX2 AND $this->condX";
        $result = mysqli_query($this->conn, $sql);
        $ALL = mysqli_fetch_array($result);

        $data = [];

        foreach($range as $value2)
        {
            $bCount = $ALL[$prefix . 'b'];
            $cCount = $ALL[$prefix . 'c' . $value2];
            
            $percentage = ($bCount > 0) ? round(($cCount / $bCount) * 100) : 0;

            $data[] = [
                'bCount' => $bCount,
                'cCount' => $cCount,
                'percentage' => $percentage,
            ];
        }
        return $data;
    }
}

// Trends

class ConsumptionStats {
    private $conn;
    private $quarter;
    private $condition;

    public function __construct($conn, $quarter, $condition) {
        $this->conn = $conn;
        $this->quarter = $quarter;
        $this->condition = $condition;
    }

    public function calculate($prefix, $range) {
        $result = [];

        foreach ($this->quarter as $key => $label) {
            $quarterIndex = $key + 1;
            $quarterCondition = "{$this->condition} AND DQUARTER = '{$quarterIndex}'";

            $ever = [];
            $consumed1Y = [];
            $consumed3M = [];
            $consumed7D = [];

            foreach ($range as $val) {
                $s11 = $prefix . $val;
                $s12 = 'S12_' . $val;

                // EVER
                $bCount = $this->getCount("SELECT COUNT(`$s11`) FROM `w1` WHERE $quarterCondition AND `$s11` IN (1,2,3)");
                $cCount = $this->getCount("SELECT COUNT(`$s11`) FROM `w1` WHERE ($s11 IN (1) OR $s12 IN (1,2,3)) AND $quarterCondition");
                $ever[] = $this->buildResult($bCount, $cCount);

                // Consumed 1Y
                $bCount = $this->getCount("SELECT COUNT(`$s11`) FROM `w1` WHERE `$s11` IN (1,2,3) AND $quarterCondition");
                $cCount = $this->getCount("SELECT COUNT(w1.RESPID) FROM `w1` WHERE ($s11 IN (1) OR $s12 IN (1,2)) AND $quarterCondition");
                $consumed1Y[] = $this->buildResult($bCount, $cCount);

                // Consumed 3M
                $bCount = $this->getCount("SELECT COUNT(`$s11`) FROM `w1` WHERE `$s11` IN (1,2,3) AND $quarterCondition");
                $cCount = $this->getCount("SELECT COUNT(w1.RESPID) FROM `w1` WHERE ($s11 IN (1) OR $s12 IN (1)) AND $quarterCondition");
                $consumed3M[] = $this->buildResult($bCount, $cCount);

                // Consumed 7D
                $bCount = $this->getCount("SELECT COUNT(`$s11`) FROM `w1` WHERE `$s11` IN (1,2,3) AND $quarterCondition");
                $cCount = $this->getCount("SELECT COUNT(w1.RESPID) FROM `w1` WHERE $s11 IN (1) AND $quarterCondition");
                $consumed7D[] = $this->buildResult($bCount, $cCount);
            }

            $result[$label] = [
                'ever' => $ever,
                'consumed1Y' => $consumed1Y,
                'consumed3M' => $consumed3M,
                'consumed7D' => $consumed7D
            ];
        }

        return $result;
    }

    private function getCount($sql) {
        $query = mysqli_query($this->conn, $sql);
        return $query ? (int)mysqli_fetch_array($query)[0] : 0;
    }

    private function buildResult($b, $c) {
        return [
            'bCount' => $b,
            'cCount' => $c,
            'percentage' => $b == 0 ? 0 : round(($c / $b) * 100),
            'percentage2' => $b == 0 ? 0 : round(($c / $b),2)
        ];
    }
}


class CountryConsumptionStats {
    private $conn;
    private $countryList;
    private $baseCondition;

    public function __construct($conn, $countryList, $baseCondition) {
        $this->conn = $conn;
        $this->countryList = $countryList;
        $this->baseCondition = $baseCondition;
    }

    public function calculate2($prefix, $range) {
        $result = [];

        foreach ($this->countryList as $index => $countryLabel) {
            $countryCondition = ($index == 0) ? $this->baseCondition : "{$this->baseCondition} AND DCOUNTRY = '{$index}'";

            $ever = [];
            $consumed1Y = [];
            $consumed3M = [];
            $consumed7D = [];

            foreach ($range as $val) {
                $s11 = $prefix . $val;
                $s12 = 'S12_' . $val;

                // EVER
                $bCount = $this->getCount("SELECT COUNT(`$s11`) FROM `w1` WHERE $countryCondition AND `$s11` IN (1,2,3)");
                $cCount = $this->getCount("SELECT COUNT(`$s11`) FROM `w1` WHERE (`$s11` IN (1) OR `$s12` IN (1,2,3)) AND $countryCondition");
                $ever[] = $this->buildResult($bCount, $cCount);

                // Consumed 1Y
                $bCount = $this->getCount("SELECT COUNT(`$s11`) FROM `w1` WHERE `$s11` IN (1,2,3) AND $countryCondition");
                $cCount = $this->getCount("SELECT COUNT(w1.RESPID) FROM `w1` WHERE (`$s11` IN (1) OR `$s12` IN (1,2)) AND $countryCondition");
                $consumed1Y[] = $this->buildResult($bCount, $cCount);

                // Consumed 3M
                $bCount = $this->getCount("SELECT COUNT(`$s11`) FROM `w1` WHERE `$s11` IN (1,2,3) AND $countryCondition");
                $cCount = $this->getCount("SELECT COUNT(w1.RESPID) FROM `w1` WHERE (`$s11` IN (1) OR `$s12` IN (1)) AND $countryCondition");
                $consumed3M[] = $this->buildResult($bCount, $cCount);

                // Consumed 7D
                $bCount = $this->getCount("SELECT COUNT(`$s11`) FROM `w1` WHERE `$s11` IN (1,2,3) AND $countryCondition");
                $cCount = $this->getCount("SELECT COUNT(w1.RESPID) FROM `w1` WHERE `$s11` IN (1) AND $countryCondition");
                $consumed7D[] = $this->buildResult($bCount, $cCount);
            }

            $result[$countryLabel] = [
                'ever' => $ever,
                'consumed1Y' => $consumed1Y,
                'consumed3M' => $consumed3M,
                'consumed7D' => $consumed7D
            ];
        }

        return $result;
    }

    private function getCount($sql) {
        $query = mysqli_query($this->conn, $sql);
        return $query ? (int)mysqli_fetch_array($query)[0] : 0;
    }

    private function buildResult($b, $c) {
        return [
            'bCount' => $b,
            'cCount' => $c,
            'percentage' => $b == 0 ? 0 : round(($c / $b) * 100),
            'percentage2' => $b == 0 ? 0 : round(($c / $b),2)
        ];
    }
}

class QuarterWiseCounter {
    private $conn;
    private $condX;
    private $tblX;
    private $spcX;
    private $quarter;

    public function __construct($conn, $condX, $tblX, $spcX, $quarter) {
        $this->conn = $conn;
        $this->condX = $condX;
        $this->tblX = $tblX;
        $this->spcX = $spcX;
        $this->quarter = $quarter;
    }

    public function QuarterWiseCounterRecords($prefix, $range) {
        $data = [];

        foreach ($this->quarter as $qKey => $qLabel) {
            $qIndex = $qKey + 1;
            $quarterCondition = "DQUARTER = '{$qIndex}'";

            $bCounts = [];
            $cCounts = [];

            foreach ($range as $val) {
                if ($val == 57) {
                    $bCounts[] = "COUNT(CASE WHEN `$prefix` != '' THEN w1.RESPID END) AS {$prefix}b{$val}";
                    $cCounts[] = "COUNT(CASE WHEN `$prefix` IN (1,3,5) THEN w1.RESPID END) AS {$prefix}c{$val}";
                } elseif ($val == 58) {
                    $bCounts[] = "COUNT(CASE WHEN `$prefix` != '' THEN w1.RESPID END) AS {$prefix}b{$val}";
                    $cCounts[] = "COUNT(CASE WHEN `$prefix` IN (2,4) THEN w1.RESPID END) AS {$prefix}c{$val}";
                } elseif ($val == 59) {
                    $bCounts[] = "COUNT(CASE WHEN `$prefix` != '' THEN w1.RESPID END) AS {$prefix}b{$val}";
                    $cCounts[] = "COUNT(CASE WHEN `$prefix` IN (2,3,4,5,6,7) THEN w1.RESPID END) AS {$prefix}c{$val}";
                } elseif ($val == 32) {
                    $bCounts[] = "COUNT(CASE WHEN `$prefix` != '' THEN w1.RESPID END) AS {$prefix}b{$val}";
                    $cCounts[] = "COUNT(CASE WHEN `$prefix` IN (8,9,10,11,12,13,99) THEN w1.RESPID END) AS {$prefix}c{$val}";
                } elseif ($val == 33) {
                    $bCounts[] = "COUNT(CASE WHEN `$prefix` != '' THEN w1.RESPID END) AS {$prefix}b{$val}";
                    $cCounts[] = "COUNT(CASE WHEN `$prefix` IN (4,5) THEN w1.RESPID END) AS {$prefix}c{$val}";
                } elseif ($val == 60) {
                    $bCounts[] = "COUNT(CASE WHEN `$prefix` != '' THEN w1.RESPID END) AS {$prefix}b{$val}";
                    $cCounts[] = "COUNT(CASE WHEN `$prefix` IN (2,3) THEN w1.RESPID END) AS {$prefix}c{$val}";
                }elseif ($val == 54) {
                    $bCounts[] = "COUNT(CASE WHEN `$prefix` != '' THEN w1.RESPID END) AS {$prefix}b{$val}";
                    $cCounts[] = "COUNT(CASE WHEN `$prefix` IN (4,5) THEN w1.RESPID END) AS {$prefix}c{$val}";
                }elseif ($val == 55) {
                    $bCounts[] = "COUNT(CASE WHEN `$prefix` != '' THEN w1.RESPID END) AS {$prefix}b{$val}";
                    $cCounts[] = "COUNT(CASE WHEN `$prefix` IN (6,7) THEN w1.RESPID END) AS {$prefix}c{$val}";
                } else {
                    $bCounts[] = "COUNT(CASE WHEN `$prefix` != '' THEN w1.RESPID END) AS {$prefix}b{$val}";
                    $cCounts[] = "COUNT(CASE WHEN `$prefix` = '{$val}' THEN w1.RESPID END) AS {$prefix}c{$val}";
                }
            }

            $bSql = implode(', ', $bCounts);
            $cSql = implode(', ', $cCounts);

            $sql = "SELECT $bSql, $cSql FROM {$this->tblX} WHERE w1.RESPID=w2.RESPID AND {$this->spcX} AND $quarterCondition AND {$this->condX}";
            $result = mysqli_query($this->conn, $sql);
            $row = mysqli_fetch_array($result);

            $quarterData = [];
            foreach ($range as $val) {
                $bCount = $row[$prefix . 'b' . $val];
                $cCount = $row[$prefix . 'c' . $val];
                $percentage = ($bCount > 0) ? round(($cCount / $bCount) * 100) : 0;
                $percentage2 = ($bCount > 0) ? round(($cCount / $bCount),2) : 0;

                $quarterData[] = [
                    'bCount' => $bCount,
                    'cCount' => $cCount,
                    'percentage' => $percentage,
                    'percentage2' => $percentage2
                ];
            }

            $data[$qLabel] = $quarterData;
        }

        return $data;
    }
}

class CountryWiseCounter {
    private $conn;
    private $condX;
    private $tblX;
    private $spcX;
    private $countryList;

    public function __construct($conn, $condX, $tblX, $spcX, $countryList) {
        $this->conn = $conn;
        $this->condX = $condX;
        $this->tblX = $tblX;
        $this->spcX = $spcX;
        $this->countryList = $countryList;
    }

    public function CountryWiseCounterRecords($prefix, $range) {
        $data = [];

        foreach ($this->countryList as $index => $countryLabel) {
            // If index = 0, skip DCOUNTRY condition (i.e., show total)
            $countryCondition = ($index == 0)
                ? $this->condX
                : "{$this->condX} AND DCOUNTRY = '{$index}'";

            $bCounts = [];
            $cCounts = [];

            foreach ($range as $val) {
                if ($val == 57) {
                    $bCounts[] = "COUNT(CASE WHEN `$prefix` != '' THEN w1.RESPID END) AS {$prefix}b{$val}";
                    $cCounts[] = "COUNT(CASE WHEN `$prefix` IN (1,3,5) THEN w1.RESPID END) AS {$prefix}c{$val}";
                } elseif ($val == 58) {
                    $bCounts[] = "COUNT(CASE WHEN `$prefix` != '' THEN w1.RESPID END) AS {$prefix}b{$val}";
                    $cCounts[] = "COUNT(CASE WHEN `$prefix` IN (2,4) THEN w1.RESPID END) AS {$prefix}c{$val}";
                } elseif ($val == 59) {
                    $bCounts[] = "COUNT(CASE WHEN `$prefix` != '' THEN w1.RESPID END) AS {$prefix}b{$val}";
                    $cCounts[] = "COUNT(CASE WHEN `$prefix` IN (2,3,4,5,6,7) THEN w1.RESPID END) AS {$prefix}c{$val}";
                } elseif ($val == 32) {
                    $bCounts[] = "COUNT(CASE WHEN `$prefix` != '' THEN w1.RESPID END) AS {$prefix}b{$val}";
                    $cCounts[] = "COUNT(CASE WHEN `$prefix` IN (8,9,10,11,12,13,99) THEN w1.RESPID END) AS {$prefix}c{$val}";
                } elseif ($val == 33) {
                    $bCounts[] = "COUNT(CASE WHEN `$prefix` != '' THEN w1.RESPID END) AS {$prefix}b{$val}";
                    $cCounts[] = "COUNT(CASE WHEN `$prefix` IN (4,5) THEN w1.RESPID END) AS {$prefix}c{$val}";
                } elseif ($val == 60) {
                    $bCounts[] = "COUNT(CASE WHEN `$prefix` != '' THEN w1.RESPID END) AS {$prefix}b{$val}";
                    $cCounts[] = "COUNT(CASE WHEN `$prefix` IN (2,3) THEN w1.RESPID END) AS {$prefix}c{$val}";
                }elseif ($val == 54) {
                    $bCounts[] = "COUNT(CASE WHEN `$prefix` != '' THEN w1.RESPID END) AS {$prefix}b{$val}";
                    $cCounts[] = "COUNT(CASE WHEN `$prefix` IN (4,5) THEN w1.RESPID END) AS {$prefix}c{$val}";
                }elseif ($val == 55) {
                    $bCounts[] = "COUNT(CASE WHEN `$prefix` != '' THEN w1.RESPID END) AS {$prefix}b{$val}";
                    $cCounts[] = "COUNT(CASE WHEN `$prefix` IN (6,7) THEN w1.RESPID END) AS {$prefix}c{$val}";
                } else {
                    $bCounts[] = "COUNT(CASE WHEN `$prefix` != '' THEN w1.RESPID END) AS {$prefix}b{$val}";
                    $cCounts[] = "COUNT(CASE WHEN `$prefix` = '{$val}' THEN w1.RESPID END) AS {$prefix}c{$val}";
                }
            }

            $bSql = implode(', ', $bCounts);
            $cSql = implode(', ', $cCounts);

            $sql = "SELECT $bSql, $cSql FROM {$this->tblX} WHERE w1.RESPID=w2.RESPID AND {$this->spcX} AND {$countryCondition}";
            $result = mysqli_query($this->conn, $sql);
            $row = mysqli_fetch_array($result);

            $countryData = [];
            foreach ($range as $val) {
                $bCount = $row[$prefix . 'b' . $val];
                $cCount = $row[$prefix . 'c' . $val];
                $percentage = ($bCount > 0) ? round(($cCount / $bCount) * 100) : 0;
                $percentage2 = ($bCount > 0) ? round(($cCount / $bCount),2) : 0;

                $countryData[] = [
                    'bCount' => $bCount,
                    'cCount' => $cCount,
                    'percentage' => $percentage,
                    'percentage2' => $percentage2
                ];
            }

            $data[$countryLabel] = $countryData;
        }

        return $data;
    }
}

class QuarterWiseCounter2 {
    private $conn;
    private $condX;
    private $tblX;
    private $spcX;
    private $spcX2;
    private $quarter;

    public function __construct($conn, $condX, $tblX, $spcX, $quarter) {
        $this->conn = $conn;
        $this->condX = $condX;
        $this->tblX = $tblX;
        $this->spcX = $spcX;
        $this->quarter = $quarter;
    }

    public function QuarterWiseCounterRecords2($prefix, $range) {
        $data = [];

        foreach ($this->quarter as $qKey => $qLabel) {
            $qIndex = $qKey + 1;
            $quarterCondition = "DQUARTER = '{$qIndex}'";

            $bCounts = [];
            $cCounts = [];

            foreach ($range as $val) {
                $col = "{$prefix}_{$val}";
                $bCounts[] = "COUNT(CASE WHEN `$col` != '' THEN w1.RESPID END) AS {$prefix}b{$val}";
                $cCounts[] = "COUNT(CASE WHEN `$col` = '1' THEN w1.RESPID END) AS {$prefix}c{$val}";
            }

            $bSql = implode(', ', $bCounts);
            $cSql = implode(', ', $cCounts);

            $sql = "SELECT $bSql, $cSql FROM {$this->tblX} WHERE w1.RESPID=w2.RESPID AND {$this->spcX} AND $quarterCondition AND {$this->condX}";
            $result = mysqli_query($this->conn, $sql);
            $row = mysqli_fetch_array($result);

            $quarterData = [];
            foreach ($range as $val) {
                $bCount = $row[$prefix . 'b' . $val];
                $cCount = $row[$prefix . 'c' . $val];
                $percentage = ($bCount > 0) ? round(($cCount / $bCount) * 100) : 0;
                $percentage2 = ($bCount > 0) ? round(($cCount / $bCount),2) : 0;

                $quarterData[] = [
                    'bCount' => $bCount,
                    'cCount' => $cCount,
                    'percentage' => $percentage,
                    'percentage2' => $percentage2
                ];
            }

            $data[$qLabel] = $quarterData;
        }

        return $data;
    }
}

class CountryWiseCounter2 {
    private $conn;
    private $condX;
    private $tblX;
    private $spcX;
    private $spcX2;
    private $countryList;

    public function __construct($conn, $condX, $tblX, $spcX, $countryList) {
        $this->conn = $conn;
        $this->condX = $condX;
        $this->tblX = $tblX;
        $this->spcX = $spcX;
        $this->countryList = $countryList;
    }

    public function CountryWiseCounterRecords2($prefix, $range) {
        $data = [];

        foreach ($this->countryList as $index => $countryLabel) {
            // If index = 0, skip DCOUNTRY condition (total)
            $countryCondition = ($index == 0)
                ? $this->condX
                : "{$this->condX} AND DCOUNTRY = '{$index}'";

            $bCounts = [];
            $cCounts = [];

            foreach ($range as $val) {
                $col = "{$prefix}_{$val}";
                $bCounts[] = "COUNT(CASE WHEN `$col` != '' THEN w1.RESPID END) AS {$prefix}b{$val}";
                $cCounts[] = "COUNT(CASE WHEN `$col` = '1' THEN w1.RESPID END) AS {$prefix}c{$val}";
            }

            $bSql = implode(', ', $bCounts);
            $cSql = implode(', ', $cCounts);

            $sql = "SELECT $bSql, $cSql FROM {$this->tblX} WHERE w1.RESPID=w2.RESPID AND {$this->spcX} AND {$countryCondition}";
            $result = mysqli_query($this->conn, $sql);
            $row = mysqli_fetch_array($result);

            $countryData = [];
            foreach ($range as $val) {
                $bCount = $row[$prefix . 'b' . $val];
                $cCount = $row[$prefix . 'c' . $val];
                $percentage = ($bCount > 0) ? round(($cCount / $bCount) * 100) : 0;
                $percentage2 = ($bCount > 0) ? round(($cCount / $bCount),2) : 0;

                $countryData[] = [
                    'bCount' => $bCount,
                    'cCount' => $cCount,
                    'percentage' => $percentage,
                    'percentage2' => $percentage2
                ];
            }

            $data[$countryLabel] = $countryData;
        }

        return $data;
    }
}

class QuarterWiseCounter3 {
    private $conn;
    private $condX;
    private $tblX;
    private $spcX;
    private $spcX2;
    private $quarter;

    public function __construct($conn, $condX, $tblX, $spcX, $quarter) {
        $this->conn = $conn;
        $this->condX = $condX;
        $this->tblX = $tblX;
        $this->spcX = $spcX;
        $this->quarter = $quarter;
    }

    public function QuarterWiseCounterRecords3($prefix, $range) {
        $data = [];

        foreach ($this->quarter as $qKey => $qLabel) {
            $qIndex = $qKey + 1;
            $quarterCondition = "DQUARTER = '{$qIndex}'";

            $bCounts = [];
            $cCounts = [];
            $bCounts[] = 'count(case when `' . $prefix .'_1` = "1" OR `' . $prefix .'_2` = "1" OR `' . $prefix .'_3` = "1" OR `' . $prefix .'_4` = "1" OR `' . $prefix .'_5` = "1" OR `' . $prefix .'_6` = "1" OR `' . $prefix .'_7` = "1" OR `' . $prefix .'_8` = "1" OR `' . $prefix .'_9` = "1" then w1.RESPID end) as ' . $prefix . 'b';
            foreach ($range as $val) {
                $col = "{$prefix}_{$val}";
                $cCounts[] = "COUNT(CASE WHEN `$col` = '1' THEN w1.RESPID END) AS {$prefix}c{$val}";
            }

            $bSql = implode(', ', $bCounts);
            $cSql = implode(', ', $cCounts);

            $sql = "SELECT $bSql, $cSql FROM {$this->tblX} WHERE w1.RESPID=w2.RESPID AND {$this->spcX} AND $quarterCondition AND {$this->condX}";
            $result = mysqli_query($this->conn, $sql);
            $row = mysqli_fetch_array($result);

            $quarterData = [];
            foreach ($range as $val) {
                $bCount = $row[$prefix . 'b'];
                $cCount = $row[$prefix . 'c' . $val];
                $percentage = ($bCount > 0) ? round(($cCount / $bCount) * 100) : 0;
                $percentage2 = ($bCount > 0) ? round(($cCount / $bCount),2) : 0;

                $quarterData[] = [
                    'bCount' => $bCount,
                    'cCount' => $cCount,
                    'percentage' => $percentage,
                    'percentage2' => $percentage2
                ];
            }

            $data[$qLabel] = $quarterData;
        }

        return $data;
    }
}


class CountryWiseCounter3 {
    private $conn;
    private $condX;
    private $tblX;
    private $spcX;
    private $spcX2;
    private $countryList;

    public function __construct($conn, $condX, $tblX, $spcX, $countryList) {
        $this->conn = $conn;
        $this->condX = $condX;
        $this->tblX = $tblX;
        $this->spcX = $spcX;
        $this->countryList = $countryList;
    }

    public function CountryWiseCounterRecords3($prefix, $range) {
        $data = [];

        foreach ($this->countryList as $index => $countryLabel) {
            // If index = 0, skip DCOUNTRY condition (total)
            $countryCondition = ($index == 0)
                ? $this->condX
                : "{$this->condX} AND DCOUNTRY = '{$index}'";

            $bCounts = [];
            $cCounts = [];
            $bCounts[] = 'count(case when `' . $prefix .'_1` = "1" OR `' . $prefix .'_2` = "1" OR `' . $prefix .'_3` = "1" OR `' . $prefix .'_4` = "1" OR `' . $prefix .'_5` = "1" OR `' . $prefix .'_6` = "1" OR `' . $prefix .'_7` = "1" OR `' . $prefix .'_8` = "1" OR `' . $prefix .'_9` = "1" then w1.RESPID end) as ' . $prefix . 'b';
            foreach ($range as $val) {
                $col = "{$prefix}_{$val}";
                $cCounts[] = "COUNT(CASE WHEN `$col` = '1' THEN w1.RESPID END) AS {$prefix}c{$val}";
            }

            $bSql = implode(', ', $bCounts);
            $cSql = implode(', ', $cCounts);

            $sql = "SELECT $bSql, $cSql FROM {$this->tblX} WHERE w1.RESPID=w2.RESPID AND {$this->spcX} AND {$countryCondition}";
            $result = mysqli_query($this->conn, $sql);
            $row = mysqli_fetch_array($result);

            $countryData = [];
            foreach ($range as $val) {
                $bCount = $row[$prefix . 'b'];
                $cCount = $row[$prefix . 'c' . $val];
                $percentage = ($bCount > 0) ? round(($cCount / $bCount) * 100) : 0;
                $percentage2 = ($bCount > 0) ? round(($cCount / $bCount),2) : 0;

                $countryData[] = [
                    'bCount' => $bCount,
                    'cCount' => $cCount,
                    'percentage' => $percentage,
                    'percentage2' => $percentage2
                ];
            }

            $data[$countryLabel] = $countryData;
        }

        return $data;
    }
}


class CountryWiseCounterX {
    private $conn;
    private $condX;
    private $tblX;
    private $spcX;
    private $countryList;

    public function __construct($conn, $condX, $tblX, $spcX, $countryList) {
        $this->conn = $conn;
        $this->condX = $condX;
        $this->tblX = $tblX;
        $this->spcX = $spcX;
        $this->countryList = $countryList;
    }

    public function CountryWiseCounterRecordsX($prefix, $range) {
        $data = [];

        // Per-country value-to-range mapping
        $countryRangeMap = [
            0 => 90,
            1 => 91,
            2 => 92,
            3 => 93,
            4 => 94,
            5 => 95,
            6 => 96,
        ];

        $specialRangeMap = [
            90 => ['M' => [103, 104, 105,203, 204, 205,303, 304, 305,403, 404, 405, 406,503, 504, 505, 506,603, 604, 605], 'H' => [106, 107, 108,206, 207, 208,306, 307, 308, 309,407, 408, 409, 410,507, 508, 509, 510,606, 607, 608]],
            91 => ['M' => [103, 104, 105], 'H' => [106, 107, 108]],
            92 => ['M' => [203, 204, 205], 'H' => [206, 207, 208]],
            93 => ['M' => [303, 304, 305], 'H' => [306, 307, 308, 309]],
            94 => ['M' => [403, 404, 405, 406], 'H' => [407, 408, 409, 410]],
            95 => ['M' => [503, 504, 505, 506], 'H' => [507, 508, 509, 510]],
            96 => ['M' => [603, 604, 605], 'H' => [606, 607, 608]],
        ];

        foreach ($this->countryList as $index => $countryLabel) {
            $countryCondition = ($index == 0)
                ? $this->condX
                : "{$this->condX} AND DCOUNTRY = '{$index}'";

            $countryData = [];

            // Determine the specific value (91–96) for this country
            if (isset($countryRangeMap[$index])) {
                // Only the mapped value for this country
                $rangeToUse = [$countryRangeMap[$index]];
            } else {
                $rangeToUse = []; // No mapping, skip
            }

            foreach ($rangeToUse as $val) {
                if (isset($specialRangeMap[$val])) {
                    foreach (['M', 'H'] as $type) {
                        $valueSet = implode(",", $specialRangeMap[$val][$type]);

                        $bSql = "COUNT(CASE WHEN `$prefix` != '' THEN w1.RESPID END) AS {$prefix}b{$val}_$type";
                        $cSql = "COUNT(CASE WHEN `$prefix` IN ($valueSet) THEN w1.RESPID END) AS {$prefix}c{$val}_$type";

                        $sql = "SELECT $bSql, $cSql FROM {$this->tblX} WHERE w1.RESPID=w2.RESPID AND {$this->spcX} AND {$countryCondition}";
                        $result = mysqli_query($this->conn, $sql);
                        $row = mysqli_fetch_array($result);

                        $bCount = $row["{$prefix}b{$val}_$type"];
                        $cCount = $row["{$prefix}c{$val}_$type"];
                        $percentage = ($bCount > 0) ? round(($cCount / $bCount) * 100) : 0;
                        $percentage2 = ($bCount > 0) ? round(($cCount / $bCount), 2) : 0;

                        $countryData[] = [
                            'label' => "{$val}-{$type}",
                            'bCount' => $bCount,
                            'cCount' => $cCount,
                            'percentage' => $percentage,
                            'percentage2' => $percentage2
                        ];
                    }
                }
            }

            $data[$countryLabel] = $countryData;
        }

        return $data;
    }
}

class RecordCounter2_1 {

    private $conn;
    private $condX;
    private $tblX;
    private $spcX;
    private $spcX2;
    private $trendStatus;
    private $trendsValue;

    public function __construct(
        $conn,
        $condX,
        $tblX,
        $spcX,
        $spcX2,
        $trendStatus = 'false',
        $trendsValue = []
    ) {
        $this->conn         = $conn;
        $this->condX        = $condX;
        $this->tblX         = $tblX;
        $this->spcX         = $spcX;
        $this->spcX2        = $spcX2;
        $this->trendStatus  = $trendStatus;
        $this->trendsValue  = $trendsValue;
    }

    public function countRecords2_1($prefix, $range) {

        // 🔹 NON TREND MODE
        if ($this->trendStatus === 'false') {
            return $this->executeQuery($this->condX, $prefix, $range);
        }

        // 🔹 TREND MODE
        $finalData = [];

        foreach ($this->trendsValue as $trendVal) {

            $trendCondition = $this->buildTrendCondition($trendVal);

            $finalData[$trendVal] = $this->executeQuery(
                $trendCondition,
                $prefix,
                $range
            );
        }

        return $finalData;
    }

    /**
     * 🔹 Trend Condition Builder
     * 1–4  = Quarter
     * 90   = H1
     * 91   = H2
     */
    private function buildTrendCondition($trendVal) {

        if (in_array($trendVal, [1,2,3,4], true)) {
            return $this->condX . " AND DQUARTER = " . intval($trendVal);
        }

        if ($trendVal == 90) {
            return $this->condX . " AND DHALF = 1";
        }

        if ($trendVal == 91) {
            return $this->condX . " AND DHALF = 2";
        }

        return $this->condX;
    }

    private function executeQuery($condition, $prefix, $range) {

        if($prefix=="CH_F3")
        {
            $columns = [
                "{$prefix}a",
                "{$prefix}b_2",
                "{$prefix}b_3",
                "{$prefix}b_4",
                "{$prefix}b_5",
                "{$prefix}b_6",
                "{$prefix}b_7",
                "{$prefix}b_8",
                "{$prefix}b_9",
                "{$prefix}b_10",
                "{$prefix}b_11",
                "{$prefix}b_12",
                "{$prefix}b_13",
                "{$prefix}b_14"
            ];
        }
        else if($prefix=="CH_F5")
        {
            $columns = [
                "{$prefix}a",
                "{$prefix}b_2",
                "{$prefix}b_3",
                "{$prefix}b_4",
                "{$prefix}b_5",
                "{$prefix}b_6",
                "{$prefix}b_7",
                "{$prefix}b_8",
                "{$prefix}b_9",
                "{$prefix}b_10",
                "{$prefix}b_11",
                "{$prefix}b_12",
                "{$prefix}b_13",
                "{$prefix}b_14"
            ];
        }
        

        $valueCountsSQL = [];

        foreach ($range as $val) {

            $colSumParts = [];

            foreach ($columns as $col) {
                $colSumParts[] = "SUM(CASE WHEN `$col` = $val THEN 1 ELSE 0 END)";
            }

            $valueCountsSQL[] = "(" . implode(" + ", $colSumParts) . ") AS count_$val";
        }

        $sql = "
            SELECT
                " . implode(",", $valueCountsSQL) . "
            FROM {$this->tblX}
            WHERE w1.RESPID=w2.RESPID AND {$this->spcX}
            AND {$this->spcX2}
            AND {$condition}
        ";

        $result = mysqli_query($this->conn, $sql);
        $row = mysqli_fetch_assoc($result);

        $counts = [];
        $base = 0;

        foreach ($range as $val) {
            $counts[$val] = (int)$row["count_$val"];
            $base += $counts[$val];   // base = total mentions
        }

        $data = [];

        foreach ($range as $val) {

            $percentage = ($base > 0)
                ? ($counts[$val] / $base) * 100
                : 0;

            $data[] = [
                'value'      => $val,
                'bCount'  => $base,
                'cCount'      => $counts[$val],
                'percentage' => $percentage
            ];
        }

        return $data;
    }
    
}

class RecordCounter1_1 {

    private $conn;
    private $condX;
    private $tblX;
    private $spcX;
    private $spcX2;
    private $trendStatus;
    private $trendsValue;

    public function __construct(
        $conn,
        $condX,
        $tblX,
        $spcX,
        $spcX2,
        $trendStatus = 'false',
        $trendsValue = []
    ) {
        $this->conn        = $conn;
        $this->condX       = $condX;
        $this->tblX        = $tblX;
        $this->spcX        = $spcX;
        $this->spcX2       = $spcX2;
        $this->trendStatus = $trendStatus;
        $this->trendsValue = $trendsValue;
    }

    public function countRecords1_1($prefix, $range) {

        if ($this->trendStatus === 'false') {
            return $this->executeQuery($this->condX, $prefix, $range);
        }

        $finalData = [];

        foreach ($this->trendsValue as $trendVal) {

            $trendCondition = $this->buildTrendCondition($trendVal);

            $finalData[$trendVal] = $this->executeQuery(
                $trendCondition,
                $prefix,
                $range
            );
        }

        return $finalData;
    }

    /**
     * 🔹 Trend Logic (UNCHANGED)
     */
    private function buildTrendCondition($trendVal) {

        if (in_array($trendVal, [1,2,3,4], true)) {
            return $this->condX . " AND DQUARTER = " . intval($trendVal);
        }

        if ($trendVal == 90) {
            return $this->condX . " AND DHALF = 1";
        }

        if ($trendVal == 91) {
            return $this->condX . " AND DHALF = 2";
        }

        return $this->condX;
    }

    /**
     * 🔥 NEW MR EXECUTION LOGIC
     */
    private function executeQuery($condition, $prefix, $range) {

        // 🔹 Dynamic column structure
        if($prefix=="CH_F4")
        {
            $columns = [
                "{$prefix}",
                "{$prefix}a_2",
                "{$prefix}a_3",
                "{$prefix}a_4",
                "{$prefix}a_5",
                "{$prefix}a_6",
                "{$prefix}a_7",
                "{$prefix}a_8",
                "{$prefix}a_9",
                "{$prefix}a_10",
                "{$prefix}a_11",
                "{$prefix}a_12",
                "{$prefix}a_13",
                "{$prefix}a_14"
            ];
        }
        else if($prefix=="CH_F6")    
        {
            $columns = [
                "{$prefix}",
                "{$prefix}a_2",
                "{$prefix}a_3",
                "{$prefix}a_4",
                "{$prefix}a_5",
                "{$prefix}a_6",
                "{$prefix}a_7",
                "{$prefix}a_8",
                "{$prefix}a_9",
                "{$prefix}a_10",
                "{$prefix}a_11",
                "{$prefix}a_12",
                "{$prefix}a_13",
                "{$prefix}a_14"
            ];
        }
        else if($prefix=="CH_F7")    
        {
            $columns = [
                "{$prefix}",
                "{$prefix}a_2",
                "{$prefix}a_3",
                "{$prefix}a_4",
                "{$prefix}a_5",
                "{$prefix}a_6",
                "{$prefix}a_7",
                "{$prefix}a_8",
                "{$prefix}a_9",
                "{$prefix}a_10",
                "{$prefix}a_11",
                "{$prefix}a_12",
                "{$prefix}a_13",
                "{$prefix}a_14"
            ];
        }
        else if($prefix=="CH_F8")    
        {
            $columns = [
                "{$prefix}",
                "{$prefix}a_2",
                "{$prefix}a_3",
                "{$prefix}a_4",
                "{$prefix}a_5",
                "{$prefix}a_6",
                "{$prefix}a_7",
                "{$prefix}a_8",
                "{$prefix}a_9",
                "{$prefix}a_10",
                "{$prefix}a_11",
                "{$prefix}a_12",
                "{$prefix}a_13",
                "{$prefix}a_14"
            ];
        }

        $valueCountsSQL = [];

        foreach ($range as $value2) {

            $colParts = [];

            // 🔹 Special Business Rules
            if ($value2 == 57) {
                foreach ($columns as $col) {
                    $colParts[] = "SUM(CASE WHEN `$col` IN (1,3,5) THEN 1 ELSE 0 END)";
                }
            }
            else if ($value2 == 58) {
                foreach ($columns as $col) {
                    $colParts[] = "SUM(CASE WHEN `$col` IN (2,4) THEN 1 ELSE 0 END)";
                }
            }
            else if ($value2 == 59) {
                foreach ($columns as $col) {
                    $colParts[] = "SUM(CASE WHEN `$col` IN (2,3,4,5,6,7) THEN 1 ELSE 0 END)";
                }
            }
            else if ($value2 == 32) {
                foreach ($columns as $col) {
                    $colParts[] = "SUM(CASE WHEN `$col` IN (8,9,10,11,12,13,99) THEN 1 ELSE 0 END)";
                }
            }
            else if ($value2 == 33) {
                foreach ($columns as $col) {
                    $colParts[] = "SUM(CASE WHEN `$col` IN (4,5) THEN 1 ELSE 0 END)";
                }
            }
            else {
                foreach ($columns as $col) {
                    $colParts[] = "SUM(CASE WHEN `$col` = '$value2' THEN 1 ELSE 0 END)";
                }
            }

            // 🔹 Add all column sums together
            $valueCountsSQL[] =
                "(" . implode(" + ", $colParts) . ") AS count_$value2";
        }

        $sql = "
            SELECT
                " . implode(",", $valueCountsSQL) . "
            FROM {$this->tblX}
            WHERE w1.RESPID = w2.RESPID
              AND {$this->spcX}
              AND {$this->spcX2}
              AND {$condition}
        ";

        $result = mysqli_query($this->conn, $sql);
        $row = mysqli_fetch_assoc($result);

        $counts = [];
        $base = 0;

        // 🔹 Base = Sum of all individual counts
        foreach ($range as $value2) {
            $counts[$value2] = (int)$row["count_$value2"];
            $base += $counts[$value2];
        }

        $data = [];

        foreach ($range as $value2) {

            $percentage = ($base > 0)
                ? ($counts[$value2] / $base) * 100
                : 0;

            $data[] = [
                'bCount'     => $base,
                'cCount'     => $counts[$value2],
                'percentage' => $percentage
            ];
        }

        return $data;
    }
}







class RecordCounterCH_R2_OR {

    private $conn;
    private $condX;
    private $tblX;
    private $spcX;
    private $spcX2;
    private $trendStatus;
    private $trendsValue;

    public function __construct(
        $conn,
        $condX,
        $tblX,
        $spcX,
        $spcX2,
        $trendStatus = 'false',
        $trendsValue = []
    ) {
        $this->conn         = $conn;
        $this->condX        = $condX;
        $this->tblX         = $tblX;
        $this->spcX         = $spcX;
        $this->spcX2        = $spcX2;
        $this->trendStatus  = $trendStatus;
        $this->trendsValue  = $trendsValue;
    }

    public function countCH_R2($prefix, $range) {

        if ($this->trendStatus === 'false') {
            return $this->executeQuery($prefix, $this->condX, $range);
        }

        $finalData = [];

        foreach ($this->trendsValue as $trendVal) {

            $trendCondition = $this->buildTrendCondition($trendVal);

            $finalData[$trendVal] = $this->executeQuery(
                $prefix,
                $trendCondition,
                $range
            );
        }

        return $finalData;
    }

    private function buildTrendCondition($trendVal) {

        if (in_array($trendVal, [1,2,3,4], true)) {
            return $this->condX . " AND DQUARTER = " . intval($trendVal);
        }

        if ($trendVal == 90) {
            return $this->condX . " AND DHALF IN (1)";
        }

        if ($trendVal == 91) {
            return $this->condX . " AND DHALF IN (2)";
        }

        return $this->condX;
    }

    /**
     * 🔥 Final Logic
     * cCount → CH_R2_x = 1 OR CH_R2a = x
     * bCount → CH_R1 = 1  (Base)
     */
    private function executeQuery($prefix, $condition, $range) {

        $baseCount = "
            COUNT(
                CASE 
                    WHEN CH_R1 = '1'
                    THEN w1.RESPID
                END
            ) AS baseTotal
        ";

        $cCounts = [];

        foreach ($range as $value) {

            $cCounts[] = "
                COUNT(
                    CASE 
                        WHEN (
                            {$prefix}_{$value} = '1'
                            OR {$prefix}a = '{$value}'
                        )
                        THEN w1.RESPID
                    END
                ) AS c{$value}
            ";
        }

        $sql = "
            SELECT 
                {$baseCount},
                " . implode(', ', $cCounts) . "
            FROM {$this->tblX}
            WHERE w1.RESPID = w2.RESPID
              AND {$this->spcX}
              AND {$this->spcX2}
              AND {$condition}
        ";

        $result = mysqli_query($this->conn, $sql);
        $ALL = mysqli_fetch_assoc($result);

        $bCount = (int) $ALL['baseTotal'];

        $data = [];

        foreach ($range as $value) {

            $cCount = (int) $ALL['c' . $value];

            $percentage = ($bCount > 0)
                ? ($cCount / $bCount) * 100
                : 0;

            $data[] = [
                'bCount'     => $bCount,
                'cCount'     => $cCount,
                'percentage' => $percentage
            ];
        }

        return $data;
    }
}


?>