<?php
/**
  * Ahead WebSoft Technologies Pvt Ltd.
  * @copyright  Copyright 2006-2026 Ahead WebSoft Technologies Pvt Ltd.
  * @license    https://www.socialengine.com/policies
*/
class Fields_Model_DbTable_Search extends Fields_Model_DbTable_Abstract
{
  protected $_fieldSearch = array();

  protected $_rowClass = 'Fields_Model_Search';

  public function getSearch($item)
  {
    $id = $this->_getIdentity($item);

    if( !array_key_exists($id, $this->_fieldSearch) ) {
      $this->_fieldSearch[$id] = $this->fetchRow($this->select()->where('item_id = ?', $id));
    }

    return $this->_fieldSearch[$id];
  }

  public function clearValues()
  {
    $this->_fieldSearch = array();
    return $this;
  }

  public function getSearchSelect($params)
  {
    $select = $this->select();
    $parts = $this->getSearchQuery($params);
    foreach( $parts as $w => $v ) {
      $select->where($w, $v);
    }
    return $select;
  }

  public function getSearchQuery($params)
  {
    $colsMeta = $this->info('metadata');
    $metaData = Engine_Api::_()->fields()->getFieldsMeta($this->getFieldType());
    $tableName = $this->info('name');
    $parts = array();
    foreach( $params as $key => $value ) {
      if( !isset($colsMeta[$key]) ) continue;
      $colMeta = $colsMeta[$key];

      // Ignore empty values
      if( (is_scalar($value) && $value === '') ||
          (is_array($value) && empty($value)) ||
          (is_array($value) && array_key_exists('min', $value) && array_filter($value) === array() ) ) {
        continue;
      }

      // Hack for age->birthdate
      if( $key == 'birthdate' || $key == 'birthday' ) {
        if( is_array($value) &&  $value['min'] != $value['max'] ) {
          $min = null;
          $max = null;

          if( !empty($value['min']) ) {
            $max = date('Y-m-d', (time() - (365 * 24 * 60 * 60) * $value['min']));
          }
          unset($value['min']);

          if( !empty($value['max']) ) {
            $min = date('Y-m-d', (time() - (365 * 24 * 60 * 60) * $value['max'])
                  - (365 * 24 * 60 * 60)); // Hack for max-age year);
          }
          unset($value['max']);

          if( $min ) {
            $value['min'] = $min;
          }
          if( $max ) {
            $value['max'] = $max;
          }
        } else if( is_scalar($value) || $value['min'] == $value['max'] ) {
          if (!is_scalar($value)) $value = $value['min'];
          $value = array(
            'min' => date('Y-m-d', (time() - (365 * 24 * 60 * 60) * ($value + 1) - 1)),
            'max' => date('Y-m-d', (time() - (365 * 24 * 60 * 60) * $value )),
          );
        }
      }

      // Set
      if( strtoupper(substr($colMeta['DATA_TYPE'], 0, 3)) === 'SET' ) {
        preg_match('/\((.+)\)/', $colMeta['DATA_TYPE'], $m);
        if( empty($m[1]) ) continue;
        $allowed = $m[1];
        $allowed = explode(',', $m[1]);
        foreach( $allowed as &$al ) {
          $al = trim($al, '\'",');
        }
        $value = (array) $value;
        $value = array_intersect($allowed, $value);
        if( empty($value) ) continue;

        $subParts = array();
        foreach( $value as $val ) {
          $subParts[] = $this->getAdapter()->quoteInto('(FIND_IN_SET(?,`' . $tableName . '`.`' . $key . '`) > 0)', $val);
        }
        $parts[join(' ' . Zend_Db_Select::SQL_OR . ' ', $subParts)] = null;
      }

      // Range
      else if( is_array($value) && (array_key_exists('min', $value) || array_key_exists('max', $value)) ) {
        if( isset($value['min']) && $value['min'] !== '' ) {
          $parts[$key . ' >= ?'] = $value['min'];
        }
        if( isset($value['max']) && $value['max'] !== '' ) {
          $parts[$key . ' <= ?'] = $value['max'];
        }
      }
      // ENUM OR INT
      else if( strtoupper(substr($colMeta['DATA_TYPE'], 0, 4)) === 'ENUM' || strpos(strtoupper($colMeta['DATA_TYPE']), 'INT') !== FALSE ) {
        $value = is_array($value) ? $value: array($value);
        $parts[$key . ' IN (?)'] = $value;
      }

      // Substring?
      // @todo don't really like this
      else if( is_string($value) && ($value[0] == '%' || $value[strlen($value)-1] == '%') ) {
        $parts[$key . ' LIKE ?'] = $value;
      }
      else if ( is_string($value) ){
        $parts[$key . ' LIKE ?'] =  '%' . $value . '%';
      }
      // Scalar
      else if( is_scalar($value) ) {
        $parts[$key . ' = ?'] = $value;
      }
    }

    return $parts;
  }

  public function updateSearch($spec, $values)
  {
    if( !($spec instanceof Core_Model_Item_Abstract) ) {
      throw new Fields_Model_Exception('Not an item');
    }
    if( !($values instanceof Zend_Db_Table_Rowset_Abstract) ) {
      return null;
    }

    // Prepare data
    $cols = $this->info('cols');
    $colsMeta = $this->info('metadata');
    $metaData = Engine_Api::_()->fields()->getFieldsMeta($spec);
    $searchRow = $this->getSearch($spec);
    if( null === $searchRow ) {
      $this->_fieldSearch[$spec->getIdentity()] = $searchRow = $this->createRow();
      $searchRow->item_id = $spec->getIdentity();
    }

    // Index
    $indexedValues = array();
    foreach( $values as $row ) {
      $searchField = $metaData->getRowMatching('field_id', $row->field_id);
      if( !$searchField ) continue;
      $searchCol = $this->_getSearchColumn($searchField);
      if( !engine_in_array($searchCol, $cols) ) continue;

      if( !isset($indexedValues[$searchCol]) ) {
        $indexedValues[$searchCol] = $row->value;
      } else {
        // Fixes #1393 courtesy of RadCodes
        if( !is_array($indexedValues[$searchCol]) ) {
          $indexedValues[$searchCol] = array($indexedValues[$searchCol]);
        }
        $indexedValues[$searchCol][] = $row->value;
      }
    }

    // Update
    foreach( $indexedValues as $col => $value ) {
      $colMeta = $colsMeta[$col];
      if( strtoupper(substr($colMeta['DATA_TYPE'], 0, 3)) === 'SET' ) {
        preg_match('/\((.+)\)/', $colMeta['DATA_TYPE'], $m);
        if( empty($m[1]) ) continue;
        $allowed = $m[1];
        $allowed = explode(',', $m[1]);
        foreach( $allowed as &$al ) {
          $al = trim($al, '\'",');
        }

        $value = (array) $value;
        $value = array_intersect($allowed, $value);
        $value = join(',', $value);
      } else if( is_array($value) ){
        $value = array_filter($value);
        $value = array_shift($value);
      }
      $searchRow->$col = $value;
    }

    $searchRow->save();

    return $searchRow;
  }

  public function removeItemValues($item)
  {
    if( !($item instanceof Core_Model_Item_Abstract) ) {
      throw new Fields_Model_Exception('Not an item');
    }
    
    $this->delete(array(
      'item_id = ?' => $item->getIdentity(),
    ));

    return $this;
  }

  public function checkSearchIndex($field)
  {
    // Note: this is called on create and edit

    // Note: and now delete
    if( !$field->search ) {
      $this->deleteFieldSearch($field);
      return $this;
    }

    $this->setMetadataCacheInClass(false);
    $this->setDefaultMetadataCache(null);
    // Prepare table info
    $name = $this->info('name');
    $cols = $this->info('cols');

    // Make column name
    $searchCol = null;
    if( !empty($field->alias) ) {
      $searchCol = $field->alias;
    } else {
      $searchCol = sprintf('field_%d', $field->field_id);
    }

    // Get sql params
    $sqlParams = null;
    
    // Pull sql params from field class
    $class = 'Fields_Form_Element_'.Engine_Api::_()->fields()->inflectFieldType($field->type);
    if( @class_exists($class) && method_exists($class, 'getSearchSqlParams') ) {
      $sqlParams = call_user_func(array($class, 'getSearchSqlParams'));
    }

    // Infer sql params from field type
    else {
      $info = Engine_Api::_()->fields()->getFieldInfo($field->type);
      $genericType = null;
      if( !empty($info['base']) ) {
        $genericType = $info['base'];
      } else {
        $genericType = $field->type;
      }
      switch( $genericType ) {
        case 'text':
          $sqlParams = array(
            'type' => 'varchar',
            'length' => '255',
            'charset' => 'default',
            'collate' => 'default',
          );
          break;
        case 'textarea':
          $sqlParams = array(
            //'type' => 'text',
            'type' => 'varchar',
            'length' => '255',
            'charset' => 'default',
            'collate' => 'default',
          );
          break;
        case 'select':
        case 'radio':
          // Note: this won't work for options that have text keys (some of the pre-made ones)
          $option_ids = $field->getOptionIds();
          if( !empty($option_ids) ) {
            $sqlParams = array(
              'type' => 'enum',
              'length' => $option_ids,
            );
          } else if( !empty($info['multiOptions']) ) {
            $sqlParams = array(
              'type' => 'enum',
              'length' => array_keys($info['multiOptions']),
            );
          } else {
            $sqlParams = array(
              'type' => 'varchar',
              'length' => '255',
              'charset' => 'default',
              'collate' => 'default',
            );
          }
          break;
        case 'multiselect':
        case 'multi_checkbox':
          $option_ids = $field->getOptionIds();
          if( !empty($option_ids) ) {
            $sqlParams = array(
              'type' => 'set',
              'length' => $option_ids,
            );
          } else if( !empty($info['multiOptions']) ) {
            $sqlParams = array(
              'type' => 'set',
              'length' => array_keys($info['multiOptions']),
            );
          } else {
            $sqlParams = array(
              'type' => 'varchar',
              'length' => '255',
              'charset' => 'default',
              'collate' => 'default',
            );
          }
          break;
        case 'checkbox':
          $sqlParams = array(
            'type' => 'tinyint',
            'length' => 1,
          );
          break;
        case 'integer':
          $sqlParams = array(
            'type' => 'int',
          );
          break;
        case 'float':
          $sqlParams = array(
            'type' => 'float',
          );
          break;
        case 'date':
          $sqlParams = array(
            'type' => 'date',
          );
          break;
        case 'datetime':
          $sqlParams = array(
            'type' => 'datetime',
          );
          break;
      }
    }

    // Exit if no params
    if( empty($sqlParams) ) {
      return $this;
    }

    
    // Action
    $sqlParams['column'] = $searchCol;

    // Create
    $exists = false;
    $tmp_e = null;
    if( !engine_in_array($searchCol, $cols) ) {
      try {
        $alterSql = $this->_constructAlterQuery($sqlParams, 'create');
        $indexSql = $this->_constructIndexQuery($sqlParams, 'create');
        
        $this->getAdapter()->query($alterSql);
        $this->getAdapter()->query($indexSql);

        $this->flushMetaData();

        $exists = true;

        // Populating Data for newly created column
        $valueTable = Engine_Api::_()->fields()->getTable($this->getFieldType(), 'values');
        $select = $valueTable->select()
          ->from($valueTable, array('item_id' => 'item_id', 'field_value' => new Zend_Db_Expr('GROUP_CONCAT(DISTINCT value ORDER BY value)')))
          ->where('field_id = ?', $field->field_id)
          ->group('item_id');

        foreach( $valueTable->fetchAll($select) as $fieldVal ) {
          if( empty($fieldVal->field_value) ) {
            continue;
          }
          $this->update(array(
            $searchCol => $fieldVal->field_value,
            ), array(
            'item_id = ?' => $fieldVal->item_id,
          ));
        }
      } catch( Exception $e ) {
        $tmp_e = $e;
        throw $e; // Debug
      }
    }

    // Edit
    else {
      try {
        $sql = $this->_constructAlterQuery($sqlParams, 'edit');
        $this->getAdapter()->query($sql);
        $sql = $this->_constructIndexQuery($sqlParams, 'edit');
        $this->getAdapter()->query($sql);

        $this->flushMetaData();

        $exists = true;

      } catch( Exception $e ) {
        $tmp_e = $e;
        throw $e; // Debug
      }
    }

    // Check if exception was throw during alter
    if( !$exists ) {
      $field->search = 0;
      $field->save();
      if( $tmp_e instanceof Exception ) throw $tmp_e;
    }

    return $this;
  }

  public function deleteFieldSearch($field)
  {
    // Note: this is called on delete (duh)

    // Note: and also on check
    if( $field->search ) {
      return $this;
    }


    // Prepare table info
    $name = $this->info('name');
    $cols = $this->info('cols');

    // Make column name
    $searchCol = null;
    if( !empty($field->alias) ) {
      $searchCol = $field->alias;
    } else {
      $searchCol = sprintf('field_%d', $field->field_id);
    }

    // Don't delete if any other column with the same alias exist
    if( !empty($field->alias) ) {
      $sameAliasedFields = Engine_Api::_()->fields()->getFieldsMeta($this->getFieldType())->getRowsMatching('alias', $field->alias);
      if( engine_count($sameAliasedFields) > 1 ) {
        return $this;
      }
    }
    
    // Only delete if the col exists (duh)
    if( engine_in_array($searchCol, $cols) ) {

      try {
        $sql = $this->_constructIndexQuery($searchCol, 'delete');
        $this->getAdapter()->query($sql);
        $sql = $this->_constructAlterQuery($searchCol, 'delete');
        $this->getAdapter()->query($sql);

        $this->flushMetaData();

      } catch( Exception $e ) {
        $exists = true;
        $tmp_e = $e;
        throw $e; // Debug
      }

    }

    return $this;
  }

  public function flushOptionSearch($option)
  {
    
  }


  protected function _getIdentity($item)
  {
    $id = null;
    if( $item instanceof Core_Model_Item_Abstract ) {
      if( $item->getType() != $this->_fieldType ) {
        throw new Fields_Model_Exception('field type does not match item type');
      }
      $id = $item->getIdentity();
    } else if( is_numeric($item) ) {
      $id = $item;
    } else {
      throw new Fields_Model_Exception('invalid item');
    }
    return $id;
  }

  protected function _constructAlterQuery($params, $method = 'create')
  {
    // Construct
    $sql = '';
    $name = $this->info('name');

    // Check column
    $column = null;
    if( is_string($params) ) {
      $column = $params;
    } else if( isset($params['column']) && is_string($params['column']) ) {
      $column = $params['column'];
    }

    if( !$column || ($method !== 'delete' && !is_array($params)) ) {
      throw new Fields_Model_Exception('Invalid alter params');
    }
    
    // Only main part for create/edit
    if( $method != 'delete' ) {

      // Check params
      if( empty($params['type']) || !is_string($params['type']) ) {
        throw new Fields_Model_Exception('Invalid alter params');
      }

      // Column type
      $sql .= $params['type'];

      // Column length
      if( !empty($params['length']) ) {
        if( is_array($params['length']) ) {
          $sql .= '(\'' . join("','", $params['length']) . '\')';
        } else if( is_numeric($params['length']) || is_string($params['length']) ) {
          $sql .= '('.$params['length'].')';
        } else {
          // ignore for now
        }
      }

      // Column charset
      if( !empty($params['charset']) ) {
        if( $params['charset'] == 'default' || $params['charset'] === true ) {
          $sql .= ' CHARACTER SET utf8mb4';
        } else if( is_string($params['charset']) ) {
          $sql .= ' CHARACTER SET ' . $params['charset'];
        }
      }

      // Column collate
      if( !empty($params['collate']) ) {
        if( $params['collate'] == 'default' || $params['collate'] === true ) {
          $sql .= ' COLLATE utf8mb4_unicode_ci';
        } else if( is_string($params['collate']) ) {
          $sql .= ' COLLATE ' . $params['collate'];
        }
      }

      // Column null
      $sql .= ' ' . ( !isset($params['null']) || $params['null'] ? 'NULL' : 'NOT NULL' );

      // Column default
      if( isset($params['default']) ) {
          $sql .= ' default \''.$params['default'].'\'';
      } else {
        if( isset($params['null']) && !$params['null'] ) {
          $sql .= ' default \'\'';
        }
      }

    }

    // Make full query
    switch( $method ) {
      case 'create':
        $sql = "ALTER TABLE `{$name}` ADD COLUMN `$column` {$sql}";
        break;
      case 'edit':
        $sql = "ALTER TABLE `{$name}` CHANGE COLUMN `$column` `$column` {$sql}";
        break;
      case 'delete':
        $sql = "ALTER TABLE `{$name}` DROP COLUMN `$column`";
        break;
      default:
        throw new Fields_Model_Exception('invalid operation');
        break;
    }

    return $sql;
  }

  protected function _constructIndexQuery($column, $method = 'create')
  {
    if( is_array($column) && !empty($column['column']) && is_string($column['column']) ) {
      $column = $column['column'];
    } else if( !is_string($column) ) {
      throw new Fields_Model_Exception('invalid column name');
    }

    $sql = '';
    $name = $this->info('name');
    
    switch( $method ) {
      case 'create':
        $sql = "ALTER TABLE `{$name}` ADD INDEX ( `{$column}` )";
        break;
      case 'edit':
        // Do we need to do this?
        $sql = "ALTER TABLE `{$name}` DROP INDEX `{$column}`,  ADD INDEX `{$column}` ( `{$column}` )";
        break;
      case 'delete':
        $sql = "ALTER TABLE `{$name}` DROP INDEX `{$column}`";
        break;
      default:
        throw new Fields_Model_Exception('invalid operation');
        break;
    }
    
    return $sql;
  }

  protected function _getSearchColumn($field)
  {
    if( !($field instanceof Fields_Model_Meta) ) {
      throw new Fields_Model_Exception('not a field');
    } else if( !empty($field->alias) ) {
      return $field->alias;
    } else {
      return sprintf('field_%d', $field->field_id);
    }
  }
}
