<?php

namespace Drupal\collector_systems\Controller;

use Drupal\Core\Config\ConfigFactoryInterface;
use Drupal\Core\Controller\ControllerBase;
use Drupal\Core\Database\Database;
use Drupal\Core\Database\Query\SelectInterface;
use Drupal\Core\Database\Query\Condition;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\DependencyInjection\ContainerInterface;
use Twig\Environment;
use Drupal\collector_systems\Csconstants;


class AjaxRequestsController extends ControllerBase
{

  /**
   * The Twig environment.
   *
   * @var \Twig\Environment
   */
  protected $twig;

  public function __construct(Environment $twig)
  {
    $this->twig = $twig;
  }

  public static function create(ContainerInterface $container)
  {
    return new static(
      $container->get('twig')
    );
  }


  public function groupLevelObjects_searching_page()
  {

    $groupLevelSearchHtml = "";
    $Spage = strip_tags($_POST['pagename']);

    $groupTypeId = $_POST['groupTypeId'];
    $groupLevelTopCount = $_POST['groupLevelTopCount'];
    $groupLevelSkipCount = $_POST['groupLevelSkipCount'] ?? 0;
    $groupLevelOrderBy = $_POST['groupLevelOrderBy'];
    $groupLevelSearch = trim($_POST['searchWord']);

    $collectionLeftExtent = $_POST['collectionLeftExtent'];
    $collectionRightExtent = $_POST['collectionRightExtent'];

    $groupLevelPageNo = $_POST['groupLevelPageNoValue'] ?? 1;

    $loadsec = 1;
    $customized_fields = $this->getCommaSeparatedFieldsForSearch();
    $qSearch = $groupLevelSearch;

    $customized_fields_array = explode(',', $customized_fields);
    $connection = Database::getConnection();

    // Table names
    $object_table = $connection->prefixTables('collector_systems_objects');
    $collection_table = 'collector_systems_collections';
    $artist_table = 'collector_systems_artists';

    if ($Spage == "artist-detail") {
      $artistId = $groupTypeId;
      // Fetch artist details from the database

      // Fetch objects where ArtistId
      $query = $connection->select($object_table, 'o')
        ->fields('o') // Specify the fields you want to select
        ->condition('o.ArtistId', $artistId);

      // search filter
      if ($qSearch !== NULL && count($customized_fields_array) > 0) {
        // Apply search conditions if needed.
        if (!empty($customized_fields_array) && !empty($qSearch)) {
          $escaped_search = '%' . $connection->escapeLike($qSearch) . '%';
          $or_condition_group = $query->orConditionGroup();

          // Get the column names for objects table
          $object_columns = $this->cs_get_table_columns($object_table);
          // we only need to search objects table columns field because selected fields data are from objects table only.
          foreach ($customized_fields_array as $field) {
            if (in_array($field, $object_columns, true)) {
              $or_condition_group->condition("o.$field", $escaped_search, 'LIKE');
            }
            else {
              // Optional: Log or ignore unknown fields
              \Drupal::logger('collector_systems')->warning("Unknown search field: @field", ['@field' => $field]);
            }
          }

          $query->condition($or_condition_group);
        }
        
      }

      $query->range($groupLevelSkipCount, $groupLevelTopCount);

      //sorting
      $this->query_sort_objects_list($groupLevelOrderBy, $qSearch, $query);


      $object_details = $query->execute()->fetchAllAssoc('ObjectId');

      // Count
      $obj_count = count($object_details);

      if ($obj_count > 0) {
        foreach ($object_details as $value) {
          // Calculate delay time based on $loadsec.
          if ($loadsec == 1) {
            $delaytm = '0.01s';
          } elseif ($loadsec == 2) {
            $delaytm = '0.03s';
          } else {
            $delaytm = '0.05s';
          }

          // Get the function from the Twig environment and call it.
          $function = $this->twig->getFunction('getObjectslistHtml')->getCallable();

          // Start output buffering
          ob_start();

          // Call the function. Its output will be captured by the output buffer
          call_user_func($function, $value, $groupLevelOrderBy, $groupLevelPageNo, $qSearch, $delaytm, 'https://cdn.collectorsystems.com/images/noimage300.png');

          // Get the contents of the output buffer (i.e., the output of your function)
          $functionOutput = ob_get_clean();

          $groupLevelSearchHtml .= $functionOutput;
        }
      } else {
        $groupLevelSearchHtml .= '<div class="cs-theme-nodata">No results found. Please try another search.</div>';
      }
      $groupLevelSearchHtml.= '<input type="hidden" id="hdnTotalGroupLevelObjectCount" value="'.$obj_count.'"></input>';
    } else if ($Spage == "exhibition-detail") {

      $exhibitionID = $groupTypeId;
      // Fetch artist details from the database
      $connection = Database::getConnection();
      $database = \Drupal::database();

      //Fetch Objects Where ExhibitionId
      $exhibitionObj_table = 'collector_systems_exhibition_objects';
      $object_table = 'collector_systems_objects';

      $query = \Drupal::database()->select($exhibitionObj_table, 'eo');
      $query->fields('eo');
      $query->join($object_table, 'o', 'eo.ObjectId = o.ObjectId');
      $query->fields('o');
      $query->condition('eo.ExhibitionId', $exhibitionID);


      // search filter
      if ($qSearch !== NULL && count($customized_fields_array) > 0) {
        // Apply search conditions if needed.
        if (!empty($customized_fields_array) && !empty($qSearch)) {
          $escaped_search = '%' . $connection->escapeLike($qSearch) . '%';
          $or_condition_group = $query->orConditionGroup();

          // Get the column names for objects table
          $object_columns = $this->cs_get_table_columns($object_table);
          // we only need to search objects table columns field because selected fields data are from objects table only.
          foreach ($customized_fields_array as $field) {
            if (in_array($field, $object_columns, true)) {
              $or_condition_group->condition("o.$field", $escaped_search, 'LIKE');
            }
            else {
              // Optional: Log or ignore unknown fields
              \Drupal::logger('collector_systems')->warning("Unknown search field: @field", ['@field' => $field]);
            }
          }

          $query->condition($or_condition_group);
        }
        
      }


      $query->range($groupLevelSkipCount, $groupLevelTopCount);

      //sorting
      $this->query_sort_objects_list($groupLevelOrderBy, $qSearch, $query);

      $result = $query->execute();

      $object_details = $result->fetchAllAssoc('ObjectId');

      //Count
      $count_query = $query->countQuery();
      $obj_count = $count_query->execute()->fetchField();

      if ($obj_count > 0) {
        foreach ($object_details as $value) {
          // Calculate delay time based on $loadsec.
          if ($loadsec == 1) {
            $delaytm = '0.01s';
          } elseif ($loadsec == 2) {
            $delaytm = '0.03s';
          } else {
            $delaytm = '0.05s';
          }

          // Get the function from the Twig environment and call it.
          $function = $this->twig->getFunction('getObjectslistHtml')->getCallable();

          // Start output buffering
          ob_start();

          // Call the function. Its output will be captured by the output buffer
          call_user_func($function, $value, $groupLevelOrderBy, $groupLevelPageNo, $qSearch, $delaytm, 'https://cdn.collectorsystems.com/images/noimage300.png');

          // Get the contents of the output buffer (i.e., the output of your function)
          $functionOutput = ob_get_clean();

          $groupLevelSearchHtml .= $functionOutput;
        }
      } else {
        $groupLevelSearchHtml .= '<div class="cs-theme-nodata">No results found. Please try another search.</div>';
      }
      $groupLevelSearchHtml.= '<input type="hidden" id="hdnTotalGroupLevelObjectCount" value="'.$obj_count.'"></input>';
    } else if ($Spage == "group-detail") {

      $groupID = $groupTypeId;
      // Fetch artist details from the database
      $connection = Database::getConnection();
      $database = \Drupal::database();

      //Fetch Objects Where GroupId
      $groupObj_table = "collector_systems_group_objects";
      $query = $database->select($groupObj_table, 'go')
        ->fields('go')
        ->condition('GroupId', $groupID);
      $object_details = $query->execute()->fetchAssoc();


      $object_table = "collector_systems_objects";
      $query = $database->select($groupObj_table, 'eo');
      $query->fields('eo');
      $query->condition('eo.GroupId', $groupID);
      $query->join($object_table, 'o', 'eo.ObjectId = o.ObjectId');
      $query->fields('o');

      // search filter
      if ($qSearch !== NULL && count($customized_fields_array) > 0) {
        // Apply search conditions if needed.
        if (!empty($customized_fields_array) && !empty($qSearch)) {
          $escaped_search = '%' . $connection->escapeLike($qSearch) . '%';
          $or_condition_group = $query->orConditionGroup();

          // Get the column names for objects table
          $object_columns = $this->cs_get_table_columns($object_table);
          // we only need to search objects table columns field because selected fields data are from objects table only.
          foreach ($customized_fields_array as $field) {
            if (in_array($field, $object_columns, true)) {
              $or_condition_group->condition("o.$field", $escaped_search, 'LIKE');
            }
            else {
              // Optional: Log or ignore unknown fields
              \Drupal::logger('collector_systems')->warning("Unknown search field: @field", ['@field' => $field]);
            }
          }

          $query->condition($or_condition_group);
        }
        
      }

      $query->range($groupLevelSkipCount, $groupLevelTopCount);


      //sorting
      $this->query_sort_objects_list($groupLevelOrderBy, $qSearch, $query);


      //Count
      $count_query = $query->countQuery();
      $obj_count = $count_query->execute()->fetchField();

      $object_details = $query->execute()->fetchAllAssoc('ObjectId');

      if ($obj_count > 0) {
        foreach ($object_details as $value) {
          // Calculate delay time based on $loadsec.
          if ($loadsec == 1) {
            $delaytm = '0.01s';
          } elseif ($loadsec == 2) {
            $delaytm = '0.03s';
          } else {
            $delaytm = '0.05s';
          }

          // Get the function from the Twig environment and call it.
          $function = $this->twig->getFunction('getObjectslistHtml')->getCallable();

          // Start output buffering
          ob_start();

          // Call the function. Its output will be captured by the output buffer
          call_user_func($function, $value, $groupLevelOrderBy, $groupLevelPageNo, $qSearch, $delaytm, 'https://cdn.collectorsystems.com/images/noimage300.png');

          // Get the contents of the output buffer (i.e., the output of your function)
          $functionOutput = ob_get_clean();

          $groupLevelSearchHtml .= $functionOutput;
        }
      } else {
        $groupLevelSearchHtml .= '<div class="cs-theme-nodata">No results found. Please try another search.</div>';
      }
      $groupLevelSearchHtml.= '<input type="hidden" id="hdnTotalGroupLevelObjectCount" value="'.$obj_count.'"></input>';
    } else if ($Spage == "collection-detail") {

      $collectionID = $groupTypeId;

      $database = \Drupal::database();
      // collection object details.
      $object_table = 'collector_systems_objects';
      $connection = \Drupal::database();

      $connection = \Drupal::database();
      $extent = $connection->select('collector_systems_collections', 'c')
      ->fields('c', ['LeftExtent', 'RightExtent'])
      ->condition('CollectionId', $collectionID)
      ->execute()
      ->fetchAssoc();

      $leftExtent = $extent['LeftExtent'];
      $rightExtent = $extent['RightExtent'];
      

      $query = $connection->select($object_table, 'o');
      $query->innerJoin('collector_systems_collections', 'c', 'o.CollectionId = c.CollectionId');

      // Apply value bounds to LeftExtent and RightExtent
      $query->condition('c.LeftExtent', $leftExtent, '>=');
      $query->condition('c.RightExtent', $rightExtent, '<=');

      $query->fields('o');
      $query->fields('c');

      // search filter
      if ($qSearch !== NULL && count($customized_fields_array) > 0) {
        // Apply search conditions if needed.
        if (!empty($customized_fields_array) && !empty($qSearch)) {
          $escaped_search = '%' . $connection->escapeLike($qSearch) . '%';
          $or_condition_group = $query->orConditionGroup();

          // Get the column names for objects table
          $object_columns = $this->cs_get_table_columns($object_table);
          // we only need to search objects table columns field because selected fields data are from objects table only.
          foreach ($customized_fields_array as $field) {
            if (in_array($field, $object_columns, true)) {
              $or_condition_group->condition("o.$field", $escaped_search, 'LIKE');
            }
            else {
              // Optional: Log or ignore unknown fields
              \Drupal::logger('collector_systems')->warning("Unknown search field: @field", ['@field' => $field]);
            }
          }

          $query->condition($or_condition_group);
        }
        
      }

      $count_query = $query->countQuery();
      $query->range($groupLevelSkipCount, $groupLevelTopCount);
      
      //start sort
      if ($groupLevelOrderBy === "Title%20desc" && $qSearch !== NULL) {

        $query->orderBy('Title', 'DESC');
      }
      else if($groupLevelOrderBy === "Title%20asc" && $qSearch !== NULL)
      {

        $query->orderBy('Title', 'ASC');
      }
      else if($groupLevelOrderBy === "InventoryNumber%20asc" && $qSearch !== NULL)
      {
          $query->orderBy('InventoryNumber', 'ASC');
      }
      else if($groupLevelOrderBy === "InventoryNumber%20desc" && $qSearch !== NULL)
      {

        $query->orderBy('InventoryNumber', 'DESC');
      }
      else if($groupLevelOrderBy === "ObjectDate%20desc" && $qSearch !== NULL)
      {

        $query->orderBy('ObjectDate', 'DESC');
      }
      else if($groupLevelOrderBy === "ObjectDate%20asc" && $qSearch !== NULL)
      {

        $query->orderBy('ObjectDate', 'ASC');
      }
      else if($groupLevelOrderBy === "Collection/CollectionName%20asc" && $qSearch !== NULL){
        $query->orderBy('c.CollectionName', 'ASC');

      }
      else if($groupLevelOrderBy === "Collection/CollectionName%20desc" && $qSearch !== NULL){
        $query->orderBy('c.CollectionName', 'DESC');
      }
      //end sort

      $object_details = $query->execute()->fetchAllAssoc('ObjectId');

      //Count
      $obj_count = $count_query->execute()->fetchField();


      if ($obj_count > 0) {
        foreach ($object_details as $value) {
          // Calculate delay time based on $loadsec.
          if ($loadsec == 1) {
            $delaytm = '0.01s';
          } elseif ($loadsec == 2) {
            $delaytm = '0.03s';
          } else {
            $delaytm = '0.05s';
          }

          // Get the function from the Twig environment and call it.
          $function = $this->twig->getFunction('getObjectslistHtml')->getCallable();

          // Start output buffering
          ob_start();

          // Call the function. Its output will be captured by the output buffer
          call_user_func($function, $value, $groupLevelOrderBy, $groupLevelPageNo, $qSearch, $delaytm, 'https://cdn.collectorsystems.com/images/noimage300.png');

          // Get the contents of the output buffer (i.e., the output of your function)
          $functionOutput = ob_get_clean();

          $groupLevelSearchHtml .= $functionOutput;
        }
      } else {
        $groupLevelSearchHtml .= '<div class="cs-theme-nodata">No results found. Please try another search.</div>';
      }
      $groupLevelSearchHtml.= '<input type="hidden" id="hdnTotalGroupLevelObjectCount" value="'.$obj_count.'"></input>';
    }

    return new JsonResponse(['groupLevelSearchHtml' => $groupLevelSearchHtml]);
  }






  public function getCommaSeperatedFieldsForListPageObject()
  {
    $db = \Drupal::database();

    $tblnm = "collector_systems_clsobjects_fields";
    $settblnm = $tblnm;

    $query = $db->select($settblnm, 'c')
      ->fields('c', ['fieldname'])
      ->condition('fieldtype', 'ObjectList');

    $result = $query->execute()->fetchAllAssoc('fieldname');

    $values = implode(',', array_keys($result));

    return $values;
  }

  function query_sort_objects_list($groupLevelOrderBy, $qSearch, $query){
    $connection = Database::getConnection();

    $collection_table =  $connection->prefixTables('collector_systems_collections');

    //for sorting
    if ($groupLevelOrderBy === "Title%20desc" && $qSearch !== NULL) {

      $query->orderBy('Title', 'DESC');
    }
    else if($groupLevelOrderBy === "Title%20asc" && $qSearch !== NULL)
    {

      $query->orderBy('Title', 'ASC');
    }
    else if($groupLevelOrderBy === "InventoryNumber%20asc" && $qSearch !== NULL)
    {
        $query->orderBy('InventoryNumber', 'ASC');
    }
    else if($groupLevelOrderBy === "InventoryNumber%20desc" && $qSearch !== NULL)
    {

      $query->orderBy('InventoryNumber', 'DESC');
    }
    else if($groupLevelOrderBy === "ObjectDate%20desc" && $qSearch !== NULL)
    {

      $query->orderBy('ObjectDate', 'DESC');
    }
    else if($groupLevelOrderBy === "ObjectDate%20asc" && $qSearch !== NULL)
    {

      $query->orderBy('ObjectDate', 'ASC');
    }
    else if($groupLevelOrderBy === "Collection/CollectionName%20asc" && $qSearch !== NULL){
      $query->fields('o')
      ->fields('c', ['CollectionName'])
      ->join($collection_table, 'c', 'o.CollectionId = c.CollectionId');

      $query->orderBy('c.CollectionName', 'ASC');

    }
    else if($groupLevelOrderBy === "Collection/CollectionName%20desc" && $qSearch !== NULL){
      $query->fields('o')
      ->fields('c', ['CollectionName'])
      ->join($collection_table, 'c', 'o.CollectionId = c.CollectionId');

      $query->orderBy('c.CollectionName', 'DESC');
    }
  }

  public function getImagesCountData(){
    $collector_systemsts_get_api_data = \Drupal::service('collector_systems.collector_systemsts_get_api_data');

    $object_images_db_count = $this->getCsGetDbImageTypeCount('object_images');
    $object_images_api_count = $collector_systemsts_get_api_data->getApiImageTypeCount('objects_images');

    // other images api count.
    $artists_images_api_count =  $collector_systemsts_get_api_data->getApiImageTypeCount('artists_images');
    $collections_images_api_count =  $collector_systemsts_get_api_data->getApiImageTypeCount('collections_images');
    $exhibitions_images_api_count =  $collector_systemsts_get_api_data->getApiImageTypeCount('exhibitions_images');
    $groups_images_api_count =  $collector_systemsts_get_api_data->getApiImageTypeCount('groups_images');
    $other_images_api_count = $artists_images_api_count + $collections_images_api_count + $exhibitions_images_api_count + $groups_images_api_count;
    


    // other images db count.
    $artists_images_db_count =  $this->getCsGetDbImageTypeCount('artists_images');
    $collections_images_db_count =  $this->getCsGetDbImageTypeCount('collections_images');
    $exhibitions_images_db_count =  $this->getCsGetDbImageTypeCount('exhibitions_images');
    $groups_images_db_count =  $this->getCsGetDbImageTypeCount('groups_images');
    $other_images_db_count = $artists_images_db_count + $collections_images_db_count + $exhibitions_images_db_count + $groups_images_db_count;
    
    $other_images_db_count = $artists_images_db_count + $collections_images_db_count + $exhibitions_images_db_count + $groups_images_db_count;


    $response = [
        'object_images_api_count' => $object_images_api_count,
        'object_images_db_count' => $object_images_db_count,
        'other_images_api_count' => $other_images_api_count,
        'other_images_db_count' => $other_images_db_count,
        'all_details_count' => [
            'object_images_db_count' => $object_images_db_count,
            'object_images_api_count' => $object_images_api_count,
            'artists_images_db_count'=> $artists_images_db_count,
            'artists_images_api_count'=> $artists_images_api_count,
            'collections_images_db_count' => $collections_images_db_count,
            'collections_images_api_count' => $collections_images_api_count,
            'groups_images_api_count' => $groups_images_api_count,
            'groups_images_db_count' => $groups_images_db_count,
            'exhibitions_images_db_count' => $exhibitions_images_db_count,
            'exhibitions_images_api_count' => $exhibitions_images_api_count
        ]

    ];

    return new JsonResponse($response);

  }

  public function saveCheckBoxOptionsDataType(){
    if(!isset($_POST['checkboxes'])){
     return new JsonResponse([
        'success' => False,
        'message' => 'Missing data.'
      ]);
    }

    $checkbox_groups= $_POST['checkboxes']['groups'];
    $checkbox_collections = $_POST['checkboxes']['collections'];
    $checkbox_exhibitions = $_POST['checkboxes']['exhibitions'];
    $checkbox_artists = $_POST['checkboxes']['artists'];

    \Drupal::configFactory()->getEditable('collector_systems.settings')
    ->set('checkboxes.groups', $checkbox_groups)
    ->set('checkboxes.collections', $checkbox_collections)
    ->set('checkboxes.exhibitions', $checkbox_exhibitions)
    ->set('checkboxes.artists', $checkbox_artists)
    ->save();


    return new JsonResponse([
      'success' => True,
      'message' => 'Checkboxes settings saved successfully.'
    ]);

  }

  /**
   * Get image count for a given image type.
   *
   * @param string $image_type
   *   One of: object_images, artists_images, collections_images, exhibitions_images, groups_images.
   *
   * @return int|null
   *   The count of image records, or NULL on error.
  */
  function getCsGetDbImageTypeCount($image_type) {
    $connection = \Drupal::database();

    try {
      switch ($image_type) {
        case 'object_images':
          return $connection->query("SELECT COUNT(*) FROM {collector_systems_thumb_images}")->fetchField();

        case 'artists_images':
          return $connection->query("
            SELECT COUNT(*) 
            FROM {collector_systems_artists} 
            WHERE 
              (ArtistPhotoAttachment IS NOT NULL AND ArtistPhotoAttachment != '') 
              OR 
              (ImagePath IS NOT NULL AND ImagePath != '')
          ")->fetchField();

        case 'collections_images':
          return $connection->query("
            SELECT COUNT(*) 
            FROM {collector_systems_collections} 
            WHERE 
              (CollectionImageAttachment IS NOT NULL AND CollectionImageAttachment != '') 
              OR 
              (ImagePath IS NOT NULL AND ImagePath != '')
          ")->fetchField();

        case 'exhibitions_images':
          return $connection->query("
            SELECT COUNT(*) 
            FROM {collector_systems_exhibitions} 
            WHERE 
              (ExhibitionImageAttachment IS NOT NULL AND ExhibitionImageAttachment != '') 
              OR 
              (ImagePath IS NOT NULL AND ImagePath != '')
          ")->fetchField();

        case 'groups_images':
          return $connection->query("
            SELECT COUNT(*) 
            FROM {collector_systems_groups} 
            WHERE 
              (GroupImageAttachment IS NOT NULL AND GroupImageAttachment != '') 
              OR 
              (ImagePath IS NOT NULL AND ImagePath != '')
          ")->fetchField();

        default:
          return NULL;
      }

    } catch (\Exception $e) {
      \Drupal::logger('collector_systems')->error('Error GetDbImageCount: @message', ['@message' => $e->getMessage()]);
      return NULL;
    }
  }

  /**
   * Provides count for different data types
  */
  public function getTotalCountData() {
    $apiCountForGroup = $this->GetApiGroupCount();
    $DbCountForGroup = $this-> GetDbGroupCount(  );
    $DbCountForObject  = $this->GetDbObjectCount();
    $apiCountForObject = $this->GetApiObjectCount( );
    $apiCountForArtist = $this->GetApiArtistCount();
    $DbCountForArtist= $this->GetDbArtistCount( );
    $DbcollectionCount = $this-> GetDbcollectionCount( );
    $ApicollectionCount = $this-> GetApicollectionCount( );

    $DbExhibitionsCount = $this-> GetDbExhibitionsCount( );
    $ApiExhibitionsCount = $this-> GetApiExhibitionsCount( );

    // $this->save_image_directory();
    $response = [
      'DbCountForGroup' => $DbCountForGroup,
      'apiCountForGroup' => $apiCountForGroup,
      'DbCountForObject' => $DbCountForObject,
      'apiCountForObject' => $apiCountForObject,
      'DbCountForArtist' => $DbCountForArtist,
      'apiCountForArtist' => $apiCountForArtist,
      'DbcollectionCount' => $DbcollectionCount,
      'ApicollectionCount' => $ApicollectionCount,
      'DbExhibitionsCount' => $DbExhibitionsCount,
      'ApiExhibitionsCount' => $ApiExhibitionsCount,

    ];

    return new JsonResponse($response);
  }



  //for goup
  function GetApiGroupCount()
  {
    $config = \Drupal::config('collector_systems.settings');
    $subsKey = $config->get('subscription_key');
    $subAcntId = $config->get('account_guid');
    $subsId = $config->get('subscription_id');

    $wordforsearch="Groups";
    $url = csconstants::Public_API_URL.$subAcntId.'/'.$wordforsearch.'?$count=true&$filter=SubscriptionId%20eq%20'.$subsId.'&$select=GroupId';
    $curl = curl_init($url);
    curl_setopt($curl, CURLOPT_URL, $url);
    curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);

    $headers = array(
    "Accept: application/json",
    "Ocp-Apim-Subscription-Key:$subsKey ",
    "Cache-Control:no-cache",
    );
    curl_setopt($curl, CURLOPT_HTTPHEADER, $headers);
    //for debug only!
    curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, false);
    curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);

    $data = curl_exec($curl);
    curl_close($curl);

    $httpcode = curl_getinfo($curl, CURLINFO_HTTP_CODE);
    if($httpcode == 403)
    {
        exit();
    }

    $data = json_decode($data, TRUE);
    return $data['@odata.count'];
  }
  function GetDbGroupCount(){
    try {

      $database = Database::getConnection();
      $table_name = 'collector_systems_groups';
      $query = $database->select($table_name, 'g');
      $count = $query->countQuery()->execute()->fetchField();
      return $count;
    }catch (\Exception $e) {
      \Drupal::logger('collector_systems')->error('Database query error: @message', ['@message' => $e->getMessage()]);
      // If an exception is thrown (e.g., table not found), return 0.
      return 0;
    }

  }



  //for object
  function GetApiObjectCount()
  {

    $config = \Drupal::config('collector_systems.settings');
    $subsKey = $config->get('subscription_key');
    $subAcntId = $config->get('account_guid');
    $subsId = $config->get('subscription_id');

      $wordforsearch="Objects";
      $url = csconstants::Public_API_URL.$subAcntId.'/'.$wordforsearch.'?$count=true&$filter=SubscriptionId%20eq%20'.$subsId.'&$select=ObjectId';
      $curl = curl_init($url);
      curl_setopt($curl, CURLOPT_URL, $url);
      curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);

      $headers = array(
      "Accept: application/json",
      "Ocp-Apim-Subscription-Key:$subsKey ",
      "Cache-Control:no-cache",
      );
      curl_setopt($curl, CURLOPT_HTTPHEADER, $headers);
      //for debug only!
      curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, false);
      curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);

      $data = curl_exec($curl);
      curl_close($curl);

      $httpcode = curl_getinfo($curl, CURLINFO_HTTP_CODE);
      if($httpcode == 403)
      {
          exit();
      }

    $data = json_decode($data, TRUE);
    return $data['@odata.count'];
  }

  function GetDbObjectCount(){
    try{
      $database = \Drupal::database();

      $table_name = 'collector_systems_objects';
      $query = $database->select($table_name, 'c');
      $count2 = $query->countQuery()->execute()->fetchField();

      return $count2;

    }catch (\Exception $e) {
      \Drupal::logger('collector_systems')->error('Database query error: @message', ['@message' => $e->getMessage()]);
      // If an exception is thrown (e.g., table not found), return 0.
      return 0;
    }

  }


  //for Artist
  function GetApiArtistCount()
  {
      $config = \Drupal::config('collector_systems.settings');
      $subsKey = $config->get('subscription_key');
      $subAcntId = $config->get('account_guid');
      $subsId = $config->get('subscription_id');

      $wordforsearch="Artists";
      $url = csconstants::Public_API_URL.$subAcntId.'/'.$wordforsearch.'?$count=true&$filter=SubscriptionId%20eq%20'.$subsId.'&$select=ArtistId';
      $curl = curl_init($url);
      curl_setopt($curl, CURLOPT_URL, $url);
      curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);

      $headers = array(
      "Accept: application/json",
      "Ocp-Apim-Subscription-Key:$subsKey ",
      "Cache-Control:no-cache",
      );
      curl_setopt($curl, CURLOPT_HTTPHEADER, $headers);
      //for debug only!
      curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, false);
      curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);

      $data = curl_exec($curl);
      curl_close($curl);

      $httpcode = curl_getinfo($curl, CURLINFO_HTTP_CODE);
      if($httpcode == 403)
      {
          // get_template_part( 403 );
          exit();
      }

    $data = json_decode($data, TRUE);
    return $data['@odata.count'];
  }

  function GetDbArtistCount(){
    try{
      $database = \Drupal::database();

      $table_name = 'collector_systems_artists';
      $query = $database->select($table_name, 'c');
      $count2 = $query->countQuery()->execute()->fetchField();

      return $count2;
    }catch (\Exception $e) {
      \Drupal::logger('collector_systems')->error('Database query error: @message', ['@message' => $e->getMessage()]);
      // If an exception is thrown (e.g., table not found), return 0.
      return 0;
    }
  }

  //for collection
  function GetApicollectionCount()
  {
      $config = \Drupal::config('collector_systems.settings');
      $subsKey = $config->get('subscription_key');
      $subAcntId = $config->get('account_guid');
      $subsId = $config->get('subscription_id');

      $wordforsearch="Collections";
      $url = csconstants::Public_API_URL.$subAcntId.'/'.$wordforsearch.'?$count=true&$filter=SubscriptionId%20eq%20'.$subsId.'&$select=CollectionId';
      $curl = curl_init($url);
      curl_setopt($curl, CURLOPT_URL, $url);
      curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);

      $headers = array(
      "Accept: application/json",
      "Ocp-Apim-Subscription-Key:$subsKey ",
      "Cache-Control:no-cache",
      );
      curl_setopt($curl, CURLOPT_HTTPHEADER, $headers);
      //for debug only!
      curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, false);
      curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);

      $data = curl_exec($curl);
      curl_close($curl);

      $httpcode = curl_getinfo($curl, CURLINFO_HTTP_CODE);
      if($httpcode == 403)
      {
          exit();
      }

    $data = json_decode($data, TRUE);
    return $data['@odata.count'];
  }

  function GetDbcollectionCount(){
    try{
      $table_name = 'collector_systems_collections';
      $database = \Drupal::database();
      $query = $database->select($table_name, 'c');
      $count2 = $query->countQuery()->execute()->fetchField();
      return $count2;
    }catch (\Exception $e) {
      \Drupal::logger('collector_systems')->error('Database query error: @message', ['@message' => $e->getMessage()]);
      // If an exception is thrown (e.g., table not found), return 0.
      return 0;
    }
  }
  //for collection
  function GetApiExhibitionsCount()
  {
      $config = \Drupal::config('collector_systems.settings');
      $subsKey = $config->get('subscription_key');
      $subAcntId = $config->get('account_guid');
      $subsId = $config->get('subscription_id');

      $wordforsearch="Exhibitions";
      $url = csconstants::Public_API_URL.$subAcntId.'/'.$wordforsearch.'?$count=true&$filter=SubscriptionId%20eq%20'.$subsId.'&$select=ExhibitionId';
      $curl = curl_init($url);
      curl_setopt($curl, CURLOPT_URL, $url);
      curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);

      $headers = array(
      "Accept: application/json",
      "Ocp-Apim-Subscription-Key:$subsKey ",
      "Cache-Control:no-cache",
      );
      curl_setopt($curl, CURLOPT_HTTPHEADER, $headers);
      //for debug only!
      curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, false);
      curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);

      $data = curl_exec($curl);
      curl_close($curl);

      $httpcode = curl_getinfo($curl, CURLINFO_HTTP_CODE);
      if($httpcode == 403)
      {
          exit();
      }

    $data = json_decode($data, TRUE);
    return $data['@odata.count'];
  }

  function GetDbExhibitionsCount(){
    try{
      $database = \Drupal::database();

      $table_name =  'collector_systems_exhibitions'; // Use $wpdb->prefix to get the table prefix defined by WordPress

        $query = $database->select($table_name, 'c');
        $count2 = $query->countQuery()->execute()->fetchField();
        return $count2;
    }catch (\Exception $e) {
      \Drupal::logger('collector_systems')->error('Database query error: @message', ['@message' => $e->getMessage()]);
      // If an exception is thrown (e.g., table not found), return 0.
      return 0;
    }
  }

  /**
  * Get comma separated field names for search.
  * @return string
  */
  public function getCommaSeparatedFieldsForSearch(){
    $db = \Drupal::database();

    $tblnm = "collector_systems_clsobjects_fields";
    $settblnm = $tblnm;

    $query = $db->select($settblnm, 'c')
      ->fields('c', ['fieldname']);
      // ->condition('fieldtype', 'ObjectList');
    $result = $query->execute()->fetchAllAssoc('fieldname');

    $values = implode(',', array_keys($result));

    return $values;

  }

  /**
  * Get column names for a given database table (prefix-aware, Drupal 10+).
  *
  * @param string $table_name
  *   The base table name (without prefix), e.g. 'collector_systems_objects'.
  *
  * @return array
  *   A simple array of column names.
  */
  function cs_get_table_columns($table_name) {
    $connection = \Drupal::database();
    $prefix = $connection->getPrefix();
    $prefixed_table = $prefix . $table_name;

    try {
      // Use SHOW COLUMNS — faster and no INFORMATION_SCHEMA permission issues.
      $result = $connection->query("SHOW COLUMNS FROM `$prefixed_table`")->fetchAll();
      return array_map(static fn($row) => $row->Field, $result);
    }
    catch (\Exception $e) {
      \Drupal::logger('collector_systems')->error(
        'Error fetching columns for table @table: @message',
        ['@table' => $prefixed_table, '@message' => $e->getMessage()]
      );
      return [];
    }
  }

}


