<?php
/**
  * Ahead WebSoft Technologies Pvt Ltd.
  * @copyright  Copyright 2006-2026 Ahead WebSoft Technologies Pvt Ltd.
  * @license    https://www.socialengine.com/policies
*/

class Core_Model_DbTable_Collections extends Engine_Db_Table
{

  protected $_rowClass = "Core_Model_Collection";

  /**
   * Get a select object for paginated collections listing.
   * Default sort: updated_at DESC (most recently updated first).
   * Supports params: search (case-insensitive LIKE), page, limit.
   *
   * @param int $userId
   * @param array $params
   * @return Zend_Db_Table_Select
   */
  public function getCollectionsSelect($userId, array $params = [])
  {
    $select = $this->select()
      ->from($this->info('name'))
      ->where('user_id = ?', $userId)
      ->order('updated_at DESC');

    // Search filter: case-insensitive name matching
    if (!empty($params['search'])) {
      $select->where('LOWER(name) LIKE ?', '%' . strtolower(trim($params['search'])) . '%');
    }

    return $select;
  }

  /**
   * Get a collection by name for a specific user (case-insensitive match).
   *
   * @param int $userId
   * @param string $name
   * @return Core_Model_Savecollection|null
   */
  public function getByName($userId, $name)
  {
    $select = $this->select()
      ->from($this->info('name'))
      ->where('user_id = ?', $userId)
      ->where('LOWER(name) = LOWER(?)', $name)
      ->limit(1);

    return $this->fetchRow($select);
  }

  /**
   * Get collections ordered by usage count (most used first).
   * Secondary sort by created_at DESC.
   *
   * @param int $userId
   * @param int $limit
   * @return Zend_Db_Table_Rowset_Abstract
   */
  public function getByUsageOrder($userId, $limit = 10)
  {
    $select = $this->select()
      ->from($this->info('name'))
      ->where('user_id = ?', $userId)
      ->order('usage_count DESC')
      ->order('created_at DESC')
      ->limit($limit);

    return $this->fetchAll($select);
  }

  /**
   * Delete all collections for a given user.
   *
   * @param int $userId
   * @return int Number of rows deleted
   */
  public function deleteByUser($userId): int
  {
    return $this->delete(['user_id = ?' => $userId]);
  }
}
