<?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_Collectionitems extends Engine_Db_Table
{

  protected $_rowClass = "Core_Model_Collectionitem";
  protected $_name = 'core_collectionitems';

  /**
   * Get a Zend_Db_Select for items in a collection, with optional filtering/sorting.
   * Suitable for Zend_Paginator.
   *
   * @param int $collectionId
   * @param array $params [search, sort, content_type]
   * @return Zend_Db_Table_Select
   */
  public function getItemsSelect($collectionId, array $params = [])
  {
    $collectionitemsTable = $this->info('name');
    $saveditemsTable = Engine_Api::_()->getDbtable('saveditems', 'core')->info('name');

    $select = $this->select()
      ->setIntegrityCheck(false)
      ->from($collectionitemsTable)
      ->join($saveditemsTable, "$collectionitemsTable.saveditem_id = $saveditemsTable.saveditem_id", array('content_type', 'content_id'))
      ->where("$collectionitemsTable.collection_id = ?", $collectionId);

    // Filter by content_type if provided
    if (!empty($params['content_type'])) {
      $select->where("$saveditemsTable.content_type = ?", $params['content_type']);
    }

    // Search by content (requires join with saveditems for content lookup)
    if (!empty($params['search'])) {
      $select->where("$saveditemsTable.content_type LIKE ?", '%' . $params['search'] . '%');
    }

    // Sort
    $sort = isset($params['sort']) ? $params['sort'] : 'date_newest';
    switch ($sort) {
      case 'date_oldest':
        $select->order("$collectionitemsTable.created_at ASC");
        break;
      case 'alpha_asc':
        $select->order("$saveditemsTable.content_type ASC");
        break;
      case 'alpha_desc':
        $select->order("$saveditemsTable.content_type DESC");
        break;
      case 'date_newest':
      default:
        $select->order("$collectionitemsTable.created_at DESC");
        break;
    }

    return $select;
  }

  /**
   * Find a specific mapping by saveditem_id and collection_id.
   *
   * @param int $saveditemId
   * @param int $collectionId
   * @return Core_Model_Collectionitem|null
   */
  public function getMapping($saveditemId, $collectionId)
  {
    $select = $this->select()
      ->where('saveditem_id = ?', $saveditemId)
      ->where('collection_id = ?', $collectionId)
      ->limit(1);
    return $this->fetchRow($select);
  }

  /**
   * Return array of collection_id values where saveditem_id matches.
   *
   * @param int $saveditemId
   * @return array
   */
  public function getCollectionIdsForItem($saveditemId): array
  {
    $select = $this->select()
      ->from($this->info('name'), array('collection_id'))
      ->where('saveditem_id = ?', $saveditemId);
    $rows = $this->fetchAll($select);
    $ids = array();
    foreach ($rows as $row) {
      $ids[] = (int) $row->collection_id;
    }
    return $ids;
  }

  /**
   * Count the number of items in a collection.
   *
   * @param int $collectionId
   * @return int
   */
  public function countByCollection($collectionId): int
  {
    $select = $this->select()
      ->from($this->info('name'), array('count' => new Zend_Db_Expr('COUNT(*)')))
      ->where('collection_id = ?', $collectionId);
    return (int) $select->query()->fetchColumn();
  }

  /**
   * Count the number of collections a saved item belongs to.
   *
   * @param int $saveditemId
   * @return int
   */
  public function countBySavedItem($saveditemId): int
  {
    $select = $this->select()
      ->from($this->info('name'), array('count' => new Zend_Db_Expr('COUNT(*)')))
      ->where('saveditem_id = ?', $saveditemId);
    return (int) $select->query()->fetchColumn();
  }

  /**
   * Delete all collection mappings for a specific saved item.
   *
   * @param int $saveditemId
   * @return int Number of rows deleted
   */
  public function deleteBySavedItem($saveditemId): int
  {
    return $this->delete(array(
      'saveditem_id = ?' => $saveditemId,
    ));
  }

  /**
   * Delete all collection mappings for a specific collection.
   *
   * @param int $collectionId
   * @return int Number of rows deleted
   */
  public function deleteByCollection($collectionId): int
  {
    return $this->delete(array(
      'collection_id = ?' => $collectionId,
    ));
  }

  /**
   * Delete all collection mappings for a specific user.
   *
   * @param int $userId
   * @return int Number of rows deleted
   */
  public function deleteByUser($userId): int
  {
    return $this->delete(array(
      'user_id = ?' => $userId,
    ));
  }
}
