";print_r($record_list_data);die;

$listId = $request->input('list_id');
$columnName = $record_list_data->column_name;
// ✅ Run duplicate removal only if 'is_dialing' = 1
if ($request->has('duplicate_check') && $request->input('duplicate_check') == 1) {

    $sql_list_data = "SELECT * FROM list_header WHERE list_id = :list_id AND is_dialing = 1";
    $record_list_data = DB::connection('mysql_' . $request->auth->parent_id)
        ->selectOne($sql_list_data, ['list_id' => $request->input('list_id')]);

    // Only proceed if a dialing column exists
    if (!empty($record_list_data) && !empty($record_list_data->column_name)) {

        $listId = $request->input('list_id');
        $columnName = $record_list_data->column_name;

        $sql_delete_duplicates = "
            DELETE ld
            FROM list_data ld
            JOIN (
                SELECT MIN(id) AS keep_id, `$columnName` AS phone_number
                FROM list_data
                WHERE list_id = :list_id1
                  AND `$columnName` IS NOT NULL
                  AND `$columnName` != ''
                GROUP BY `$columnName`
            ) AS keep_rows
            ON ld.`$columnName` = keep_rows.phone_number
            AND ld.list_id = :list_id2
            AND ld.id <> keep_rows.keep_id
        ";

        DB::connection('mysql_' . $request->auth->parent_id)
            ->statement($sql_delete_duplicates, [
                'list_id1' => $listId,
                'list_id2' => $listId
            ]);
    }
}


// $sql_delete_duplicates = "
// DELETE ld
// FROM list_data ld
// JOIN (
//     SELECT MIN(id) AS keep_id, `$columnName` AS phone_number
//     FROM list_data
//     WHERE list_id = :list_id1
//       AND `$columnName` IS NOT NULL
//       AND `$columnName` != ''
//     GROUP BY `$columnName`
// ) AS keep_rows
// ON ld.`$columnName` = keep_rows.phone_number
// AND ld.list_id = :list_id2
// AND ld.id <> keep_rows.keep_id
// ";

// DB::connection('mysql_' . $request->auth->parent_id)
//     ->statement($sql_delete_duplicates, [
//         'list_id1' => $listId,
//         'list_id2' => $listId
//     ]);




                //TODO: Think about having separate Job to do this.
                /*if($boolIsDialingFound && $strIsDialSelectedColumn != ''){
                    $strSql = "UPDATE client_8.list_data SET ".$strIsDialSelectedColumn." = REPLACE(".$strIsDialSelectedColumn.", '(', ''), ".$strIsDialSelectedColumn." = REPLACE(".$strIsDialSelectedColumn.", ')', ''), ".$strIsDialSelectedColumn." = REPLACE(".$strIsDialSelectedColumn.", '-', '') WHERE list_id = :list_id";
                    DB::connection('mysql_' . $request->auth->parent_id)->update($strSql, [ 'list_id' => $request->input('list_id')]);
                }*/
            }
            if ($saveRecord) {
                return array(
                    'success' => 'true',
                    'message' => 'Lists updated successfully.'
                );
            } else {
                return array(
                    'success' => 'false',
                    'message' => 'Lists update failed.'
                );
            }
        } catch (\Throwable $e) {
            Log::error("Lists.editList.error", [
                "message" => $e->getMessage(),
                "file" => $e->getFile(),
                "line" => $e->getLine()
            ]);
            return array(
                'success' => 'false',
                'message' => $e->getMessage()
            );
        }
    }

    /*
     * Add List
     * @param object $request
     * @return array
     */

    public function addList($request, $filePath)
    {
        ini_set('max_execution_time', 1800);
        ini_set('memory_limit', '-1');


        $dataBase = 'mysql_' . $request->auth->parent_id;
        $campaignId = $request->input('campaign');
        $duplicate_check = $request->input('duplicate_check');        
        $is_dialing = $request->input('is_dialing');

        try {
            $reader = Excel::toArray(new Excel(), $filePath);
        } catch (\Exception $e) {
            return array(
                'success' => 'false',
                'message' => 'Unable to read excel.'
            );
        }

        if (!empty($reader)) {
            ////////////////////////////
              $query_1 = [];        // ✅ FIX
           $query = "INSERT INTO list (title, is_dialing, duplicate_check) 
          VALUES (:title, :is_dialing, :duplicate_check)";

            $add = DB::connection($dataBase)->insert($query, [
                'title' => $request->input('title'),
                'is_dialing' => $request->input('is_dialing', 0),
                'duplicate_check' => $request->input('duplicate_check', 0),
            ]);

            if ($add == 1) {
                $sql = "SELECT id FROM list ORDER BY id DESC";
                $record = DB::connection($dataBase)->selectOne($sql, array());
                $data = (array) $record;
                $list_id = $data['id'];
                $listId = $list_id;

                $query = "INSERT INTO campaign_list (campaign_id, list_id) VALUE (:campaign_id, :list_id)";
                DB::connection($dataBase)->insert($query, array('campaign_id' => $campaignId, 'list_id' => $list_id));

                $date_array = array();
                $header_list = [];
                ////////////////////////////
                foreach ($reader as $row) {
                    if (is_array($row)) {
                        foreach ($row as $key => $value) {
                            if ($key == 0) {
                                $np = 0;
                                foreach ($value as $em => $ep) {
                                    $ncr = ++$np;
                                    $column_name = 'option_' . $ncr;
                                    if ($ncr > 30) {
                                        continue;
                                    }
                                    //$header_list[] = array('list_id'=>$list_id , 'column_name'=>$column_name , 'header'=>$ep );
                                    $h_list['list_id'] = $list_id;
                                    $h_list['column_name'] = $column_name;
                                    if (empty($ep)) {
                                        $ep = null;
                                    }
                                    $h_list['header'] = $ep;
                                    //	if(empty($column_name) && empty($ep)){ continue; }
                                    $check_date = strlen(strrchr(strtolower($ep), "date"));

                                    if (strpos(strtolower($ep), 'date')) {
                                        $date_array[$ncr] = $ncr;
                                    }
                                    if (!empty($h_list['header'])) {
                                        $header_list[] = $h_list;
                                    }

                                    // $query[] = "INSERT INTO list_header (list_id, column_name, header) VALUE ($list_id, $column_name , $ep)";
                                }
                            } else {
                                $var_element[] = 'list_id';
                                $var_data[] = $list_id;
                                // $list_data['list_id']=$list_id;
                                $list_data = array("list_id" => $list_id);
                                if (empty($value[2]) && empty($value[3]) && empty($value[4])) {
                                    continue;
                                }
                                $k = 0;
                                foreach ($value as $emt => $ept) {
                                    $r = ++$k;
                                    if ($r > 30) {
                                        continue;
                                    }
                                    $var_element[] = 'option_' . $r;
                                    if (!empty($date_array[$r])) {
                                        if (is_int($ept)) {
                                            // +1 day difference added with date
                                            $ept = date("Y-m-d", (($ept - 25569) * 86400));
                                            $ept = date('Y-m-d', strtotime('+1 day', strtotime($ept)));
                                        }
                                    }

                                    $var_data[] = $ept;
                                    $list_data['option_' . $r] = $ept;
                                }
                                if (count($list_data) > 0) {
                                    $query_1[] = $list_data;
                                }
                                unset($var_data);
                                unset($var_element);
                                unset($list_data);
                            }
                            # code...
                        }
                    }
                }
            }
        } else {
            return array(
                'success' => 'false',
                'message' => 'Failed list upload process, File is empty',
                'list_id' => '',
                'campaign_id' => $campaignId
            );
        }

        if (count($query_1) > 0) {
            $save_data = true;
            foreach (array_chunk($header_list, 2000) as $t) {
                $save_data &= DB::connection($dataBase)->table('list_header')->insert($t);
            }
            foreach (array_chunk($query_1, 2000) as $t1) {
                $save_data &= DB::connection($dataBase)->table('list_data')->insert($t1);
            }

            DB::connection($dataBase)->table('list_data')->where('option_1', '=', '')->delete();

            $data = [
                "action" => "List added",
                "listId" => $listId,
                "listName" => $request->input('title'),
                "records" => count($query_1),
                "columns" => $header_list
            ];
            dispatch(new ListAddedNotificationJob($request->auth->parent_id, $campaignId, $data))->onConnection("database");

            return array(
                'success' => 'true',
                'message' => 'List added successfully.',
                'list_id' => $listId,
                'campaign_id' => $campaignId
            );
        }

        return array(
            'success' => 'false',
            'message' => 'Lists are not added. Unable to add data in list table'
        );
    }


    function getLeadCount($request)
    {
        try {
            $dataBase = 'mysql_' . $request->auth->parent_id;
            $sql = "SELECT count(1) as rowCount FROM list_data ";
            $record = DB::connection($dataBase)->selectOne($sql);
            $response = (array) $record;
            $leadCount = $response['rowCount'];
            if ($leadCount > 0) {
                return array(
                    'success' => 'true',
                    'message' => 'Lead count',
                    'data' => $leadCount
                );
            } else {
                return array(
                    'success' => 'true',
                    'message' => 'User count not found',
                    'data' => 0
                );
            }
        } catch (Exception $e) {
            Log::log($e->getMessage());
        } catch (InvalidArgumentException $e) {
            Log::log($e->getMessage());
        }
    }



    function updateCampaignListStatus($request)
    {
        $listId = $request->input('listId');
        $campaign_id = $request->input('campaign_id');
        $status = $request->input('status');
        $saveRecord = CampaignList::on('mysql_' . $request->auth->parent_id)->where('campaign_id', $campaign_id)->where('list_id', $listId)->update(array('status' => $status));

        $deleteFromLeadTemp = "DELETE FROM lead_temp WHERE list_id = :list_id and campaign_id = :campaign_id";
        $save_2 = DB::connection('mysql_' . $request->auth->parent_id)->delete($deleteFromLeadTemp, array('list_id' => $listId, 'campaign_id' => $campaign_id));

        $saveRecord = Lists::on('mysql_' . $request->auth->parent_id)->where('id', $listId)->update(array('is_active' => $status));
        if ($saveRecord > 0) {
            return array(
                'success' => 'true',
                'message' => 'Campaign List status updated successfully'
            );
        } else {
            return array(
                'success' => 'true',
                'message' => 'Campaign List update failed'
            );
        }
    }

    function updateListStatus($request)
    {
        $listId = $request->input('listId');
        $status = $request->input('status');

        $saveRecord = CampaignList::on('mysql_' . $request->auth->parent_id)->where('list_id', $listId)->update(array('status' => $status));

        $deleteFromLeadTemp = "DELETE FROM lead_temp WHERE list_id = :list_id";
        $save_2 = DB::connection('mysql_' . $request->auth->parent_id)->delete($deleteFromLeadTemp, array('list_id' => $listId));

        $saveRecord = Lists::on('mysql_' . $request->auth->parent_id)->where('id', $listId)->update(array('is_active' => $status));
        if ($saveRecord > 0) {
            return array(
                'success' => 'true',
                'message' => 'List status updated successfully'
            );
        } else {
            return array(
                'success' => 'true',
                'message' => 'List update failed'
            );
        }
    }

    /**
     * Get Lead data and header
     * @param type $lead_id
     * @param type $parent_id
     * @return type
     */
    function getLeadDataForEditPage($lead_id, $parent_id)
    {
        try {
            $leadDataArr = $inLabelArr = $inLeadArr = $finalLeadArr = $temp = [];
            $sql = "(SELECT * FROM list_data WHERE id = $lead_id) UNION (SELECT * FROM list_data_archive WHERE id = $lead_id)";
            $record = DB::connection('mysql_' . $parent_id)->select($sql);
            $listData = (array) $record;

            if (!empty($listData)) {
                $list_id = $listData[0]->list_id;
                foreach ($listData[0] as $key => $val) {
                    $inLeadArr[$key] = $val;
                }
            } else { //if no lead id found then get from list table having type = 2
                $sql = "SELECT id FROM list WHERE type = 2";
                $record = DB::connection('mysql_' . $parent_id)->select($sql);
                $list = (array) $record;
                $list_id = $list[0]->id;
            }

            if ($list_id > 0) {
                $sql = "SELECT id, title FROM label ORDER BY label.id ASC"; //get all labels
                $labels = DB::connection('mysql_' . $parent_id)->select($sql);

                //get all list_header columun (option_1,option_2)
                $sql = $sql = "SELECT list_header.is_dialing, list_header.column_name, label.title, label.id "
                    . "FROM list_header inner join label on label.id = list_header.label_id  "
                    . "WHERE list_header.list_id IN(" . $list_id . ") group by label.title ORDER BY label.id ASC";
                $listHeaders = DB::connection('mysql_' . $parent_id)->select($sql);

                //intermidiate label array
                foreach ($labels as $lab) {
                    $inLabelArr[$lab->id] = $lab->title;
                }
                //Create lead array from intermidiate List header array
                foreach ($listHeaders as $header) {
                    $temp['id'] = $header->id;
                    $temp['title'] = $header->title;
                    $temp['is_dialing'] = $header->is_dialing;
                    $temp['column_name'] = $header->column_name;
                    $temp['value'] = isset($inLeadArr[$header->column_name]) ? $inLeadArr[$header->column_name] : '';
                    $leadDataArr[$header->id] = $temp;
                    $temp = [];
                }
                //Create final lead array from  Lead array
                foreach ($inLabelArr as $key => $val) {
                    if (isset($leadDataArr[$key])) {
                        $finalLeadArr[$key] = $leadDataArr[$key];
                    } else {
                        $temp['id'] = $key;
                        $temp['title'] = $val;
                        $temp['value'] = '';
                        $temp['is_dialing'] = 0;
                        $finalLeadArr[$key] = $temp;
                    }
                    $temp = [];
                }
            }
            return ['leadData' => (array) $finalLeadArr];
        } catch (Exception $e) {
            Log::log($e->getMessage());
        } catch (InvalidArgumentException $e) {
            Log::log($e->getMessage());
        }
    }


    function getLeadDataForEditPage_copy($lead_id, $parent_id)
    {
        try {
            $leadDataArr = $inLabelArr = $inLeadArr = $finalLeadArr = $temp = [];


            $connectionName = 'mysql_' . $parent_id;
            $dbName = DB::connection($connectionName)->getDatabaseName();

            // Get column names dynamically using information_schema
            $listDataCols = DB::connection($connectionName)
                ->table('information_schema.columns')
                ->where('table_schema', $dbName)
                ->where('table_name', 'list_data')
                ->orderBy('ordinal_position')
                ->pluck('column_name')
                ->toArray();

            $archiveCols = DB::connection($connectionName)
                ->table('information_schema.columns')
                ->where('table_schema', $dbName)
                ->where('table_name', 'list_data_archive')
                ->orderBy('ordinal_position')
                ->pluck('column_name')
                ->toArray();

            // Step 2: Find the difference
            $missingCols = array_diff($listDataCols, $archiveCols);

            // Step 3: Build select statements
            $listDataSelect = implode(', ', $listDataCols);

            $archiveSelect = collect($listDataCols)->map(function ($col) use ($archiveCols) {
                return in_array($col, $archiveCols) ? $col : "NULL AS $col";
            })->implode(', ');

            $sql = "(SELECT $listDataSelect FROM list_data WHERE id = $lead_id) UNION (SELECT $archiveSelect FROM list_data_archive WHERE id = $lead_id)";
            $record = DB::connection('mysql_' . $parent_id)->select($sql);
            $listData = (array) $record;

            if (!empty($listData)) {
                $list_id = $listData[0]->list_id;
                foreach ($listData[0] as $key => $val) {
                    $inLeadArr[$key] = $val;
                }
            } else { //if no lead id found then get from list table having type = 2
                $sql = "SELECT id FROM list WHERE type = 2";
                $record = DB::connection('mysql_' . $parent_id)->select($sql);
                $list = (array) $record;
                $list_id = $list[0]->id;
            }

            if ($list_id > 0) {
                $sql = "SELECT id, title FROM label ORDER BY label.id ASC"; //get all labels
                $labels = DB::connection('mysql_' . $parent_id)->select($sql);

                //get all list_header columun (option_1,option_2)
                $sql = $sql = "SELECT list_header.is_dialing, list_header.column_name, label.title, label.id "
                    . "FROM list_header inner join label on label.id = list_header.label_id  "
                    . "WHERE list_header.list_id IN(" . $list_id . ") group by label.title ORDER BY label.id ASC";
                $listHeaders = DB::connection('mysql_' . $parent_id)->select($sql);

                //intermidiate label array
                foreach ($labels as $lab) {
                    $inLabelArr[$lab->id] = $lab->title;
                }
                //Create lead array from intermidiate List header array
                foreach ($listHeaders as $header) {
                    $temp['id'] = $header->id;
                    $temp['title'] = $header->title;
                    $temp['is_dialing'] = $header->is_dialing;
                    $temp['column_name'] = $header->column_name;
                    $temp['value'] = isset($inLeadArr[$header->column_name]) ? $inLeadArr[$header->column_name] : '';
                    $leadDataArr[$header->id] = $temp;
                    $temp = [];
                }
                //Create final lead array from  Lead array
                foreach ($inLabelArr as $key => $val) {
                    if (isset($leadDataArr[$key])) {
                        $finalLeadArr[$key] = $leadDataArr[$key];
                    } else {
                        $temp['id'] = $key;
                        $temp['title'] = $val;
                        $temp['value'] = '';
                        $temp['is_dialing'] = 0;
                        $finalLeadArr[$key] = $temp;
                    }
                    $temp = [];
                }
            }
            return ['leadData' => (array) $finalLeadArr];
        } catch (Exception $e) {
            Log::log($e->getMessage());
        } catch (InvalidArgumentException $e) {
            Log::log($e->getMessage());
        }
    }

    /**
     * Update / create lead / list data
     * @param type $request
     * @param type $parent_id
     * @return type
     */
    function updateLeadData($request, $parent_id)
    {
        $newColCnt = 0;
        $leadId = $request->input('lead_id');
        $number = $request->input('number');
        $arrLabelId = $request->input('label_id');
        $arrLabelVal = $request->input('label_value');;
        $list_id = $this->getListFromListData($leadId, $parent_id);
        //get all list_header columun (option_1,option_2)
        $sql = "SELECT list_header.is_dialing, list_header.column_name, label.title, label.id "
            . "FROM list_header inner join label on label.id = list_header.label_id  "
            . "WHERE list_header.list_id = $list_id group by label.title ORDER BY label.id ASC";
        $listHeaders = DB::connection('mysql_' . $parent_id)->select($sql);

        //Create lead array from intermidiate List header array
        $leadDataArr = [];
        foreach ($listHeaders as $header) {
            if (($key = array_search($header->column_name, $this->column_name)) !== false) {
                unset($this->column_name[$key]);
            }
            $leadDataArr[$header->id] = $header->column_name;
        }
        $this->column_name = array_values($this->column_name);

        if ($leadId == 0) {
            $query = "INSERT INTO list_data (list_id) VALUE (:list_id)";
            DB::connection('mysql_' . $parent_id)->insert($query, array('list_id' => $list_id));
            $query = "SELECT id FROM list_data ORDER BY id DESC LIMIT 1";
            $lead = DB::connection('mysql_' . $parent_id)->select($query);
            if (isset($lead[0]->id)) {
                $leadId = $lead[0]->id;
                $query = "UPDATE cdr SET lead_id = $leadId WHERE number = :number";
                DB::connection('mysql_' . $parent_id)->update($query, array('number' => $number));
                $query = "UPDATE cdr_archive SET lead_id = $leadId WHERE number = :number";
                DB::connection('mysql_' . $parent_id)->update($query, array('number' => $number));
            }
        }

        for ($i = 0; $i < count($arrLabelId); $i++) {
            if (isset($arrLabelVal[$i]) && $arrLabelVal[$i] != '') {
                if (isset($leadDataArr[$arrLabelId[$i]])) {
                    $query = "UPDATE list_data SET " . $leadDataArr[$arrLabelId[$i]] . " = :option_value WHERE id = :lead_id";
                    DB::connection('mysql_' . $parent_id)->update($query, array('option_value' => $arrLabelVal[$i], 'lead_id' => $leadId));
                } else {
                    $query = "INSERT INTO list_header (column_name, list_id, label_id) VALUE (:column_name, :list_id, :label)";
                    DB::connection('mysql_' . $parent_id)->insert($query, array('column_name' => $this->column_name[$newColCnt], 'list_id' => $list_id, 'label' => $arrLabelId[$i]));
                    usleep(25000);
                    $query = "UPDATE list_data SET " . $this->column_name[$newColCnt] . " = :option_value WHERE id = :lead_id";
                    DB::connection('mysql_' . $parent_id)->update($query, array('option_value' => $arrLabelVal[$i], 'lead_id' => $leadId));
                    $newColCnt++;
                }
            }
        }

        return array(
            'success' => 'true',
            'message' => 'Lead has been updated successfully'
        );
    }


    function updateLeadData_copy($request, $parent_id)
    {
        $newColCnt = 0;
        $leadId = $request->input('lead_id');
        $number = $request->input('number');
        $arrLabelId = $request->input('label_id');
        $arrLabelVal = $request->input('label_value');;
        $list_id = $this->getListFromListData_copy($leadId, $parent_id);
        //get all list_header columun (option_1,option_2)
        $sql = "SELECT list_header.is_dialing, list_header.column_name, label.title, label.id "
            . "FROM list_header inner join label on label.id = list_header.label_id  "
            . "WHERE list_header.list_id = $list_id group by label.title ORDER BY label.id ASC";
        $listHeaders = DB::connection('mysql_' . $parent_id)->select($sql);

        //Create lead array from intermidiate List header array
        $leadDataArr = [];
        foreach ($listHeaders as $header) {
            if (($key = array_search($header->column_name, $this->column_name)) !== false) {
                unset($this->column_name[$key]);
            }
            $leadDataArr[$header->id] = $header->column_name;
        }
        $this->column_name = array_values($this->column_name);

        if ($leadId == 0) {
            $query = "INSERT INTO list_data (list_id) VALUE (:list_id)";
            DB::connection('mysql_' . $parent_id)->insert($query, array('list_id' => $list_id));
            $query = "SELECT id FROM list_data ORDER BY id DESC LIMIT 1";
            $lead = DB::connection('mysql_' . $parent_id)->select($query);
            if (isset($lead[0]->id)) {
                $leadId = $lead[0]->id;
                $query = "UPDATE cdr SET lead_id = $leadId WHERE number = :number";
                DB::connection('mysql_' . $parent_id)->update($query, array('number' => $number));
                $query = "UPDATE cdr_archive SET lead_id = $leadId WHERE number = :number";
                DB::connection('mysql_' . $parent_id)->update($query, array('number' => $number));
            }
        }

        for ($i = 0; $i < count($arrLabelId); $i++) {
            if (isset($arrLabelVal[$i]) && $arrLabelVal[$i] != '') {
                if (isset($leadDataArr[$arrLabelId[$i]])) {
                    $query = "UPDATE list_data SET " . $leadDataArr[$arrLabelId[$i]] . " = :option_value WHERE id = :lead_id";
                    DB::connection('mysql_' . $parent_id)->update($query, array('option_value' => $arrLabelVal[$i], 'lead_id' => $leadId));
                } else {
                    $query = "INSERT INTO list_header (column_name, list_id, label_id) VALUE (:column_name, :list_id, :label)";
                    DB::connection('mysql_' . $parent_id)->insert($query, array('column_name' => $this->column_name[$newColCnt], 'list_id' => $list_id, 'label' => $arrLabelId[$i]));
                    usleep(25000);
                    $query = "UPDATE list_data SET " . $this->column_name[$newColCnt] . " = :option_value WHERE id = :lead_id";
                    DB::connection('mysql_' . $parent_id)->update($query, array('option_value' => $arrLabelVal[$i], 'lead_id' => $leadId));
                    $newColCnt++;
                }
            }
        }

        return array(
            'success' => 'true',
            'message' => 'Lead has been updated successfully'
        );
    }


    function changeDisposition($request, $parent_id)
    {

        $cdr_id = $request->input('cdr_id');
        $disposition_id = $request->input('disposition_id');

        $query = "UPDATE cdr SET disposition_id = $disposition_id WHERE id = :id";
        DB::connection('mysql_' . $parent_id)->update($query, array('id' => $cdr_id));
        $query = "UPDATE cdr_archive SET disposition_id = $disposition_id WHERE id = :id";
        DB::connection('mysql_' . $parent_id)->update($query, array('id' => $cdr_id));

        return array(
            'success' => 'true',
            'message' => 'disposition has been updated successfully'
        );
    }

    /**
     * get lead / list
     * if no lead id found then get from list table having type = 2
     * @param type $leadId
     * @param type $parent_id
     * @return type
     */
    function getListFromListData($lead_id, $parent_id)
    {
        $sql = "(SELECT * FROM list_data WHERE id = $lead_id) "
            . "UNION (SELECT * FROM list_data_archive WHERE id = $lead_id)";
        $record = DB::connection('mysql_' . $parent_id)->select($sql);
        $listData = (array) $record;

        if (!empty($listData)) {
            $list_id = $listData[0]->list_id;
        } else { //if no lead id found then get from list table having type = 2
            $sql = "SELECT id FROM list WHERE type = 2";
            $record = DB::connection('mysql_' . $parent_id)->select($sql);
            $list = (array) $record;
            $list_id = $list[0]->id;
        }

        return $list_id;
    }


    function getListFromListData_copy($lead_id, $parent_id)
    {

        $connectionName = 'mysql_' . $parent_id;
        $dbName = DB::connection($connectionName)->getDatabaseName();

        // Get column names dynamically using information_schema
        $listDataCols = DB::connection($connectionName)
            ->table('information_schema.columns')
            ->where('table_schema', $dbName)
            ->where('table_name', 'list_data')
            ->orderBy('ordinal_position')
            ->pluck('column_name')
            ->toArray();

        $archiveCols = DB::connection($connectionName)
            ->table('information_schema.columns')
            ->where('table_schema', $dbName)
            ->where('table_name', 'list_data_archive')
            ->orderBy('ordinal_position')
            ->pluck('column_name')
            ->toArray();

        // Step 2: Find the difference
        $missingCols = array_diff($listDataCols, $archiveCols);

        // Step 3: Build select statements
        $listDataSelect = implode(', ', $listDataCols);

        $archiveSelect = collect($listDataCols)->map(function ($col) use ($archiveCols) {
            return in_array($col, $archiveCols) ? $col : "NULL AS $col";
        })->implode(', ');


        $sql = "(SELECT  $listDataSelect  FROM list_data WHERE id = $lead_id) "
            . "UNION (SELECT $archiveSelect  FROM list_data_archive WHERE id = $lead_id)";
        $record = DB::connection('mysql_' . $parent_id)->select($sql);
        $listData = (array) $record;

        if (!empty($listData)) {
            $list_id = $listData[0]->list_id;
        } else { //if no lead id found then get from list table having type = 2
            $sql = "SELECT id FROM list WHERE type = 2";
            $record = DB::connection('mysql_' . $parent_id)->select($sql);
            $list = (array) $record;
            $list_id = $list[0]->id;
        }

        return $list_id;
    }
}