<?php

namespace PortalPeople\netsuite\components;

use yii\base\BaseObject;

/**
 * NetSuite Search Builder
 * 
 * This class helps build complex search queries for NetSuite
 * 
 * @package PortalPeople\netsuite\components
 */
class SearchBuilder extends BaseObject
{
    /**
     * @var array Search criteria
     */
    private $criteria = [];
    
    /**
     * @var array Search columns to return
     */
    private $columns = [];
    
    /**
     * @var array Sort options
     */
    private $sort = [];
    
    /**
     * @var int Search limit
     */
    private $limit;
    
    /**
     * @var int Search offset
     */
    private $offset;
    
    /**
     * Add a search filter
     * 
     * @param string $field Field name
     * @param string $operator Search operator (is, isnot, contains, doesnotcontain, etc.)
     * @param mixed $value Search value
     * @return $this
     */
    public function where($field, $operator, $value)
    {
        $this->criteria[] = [
            'field' => $field,
            'operator' => $operator,
            'value' => $value
        ];
        
        return $this;
    }
    
    /**
     * Add an "is" filter
     * 
     * @param string $field Field name
     * @param mixed $value Value to match
     * @return $this
     */
    public function whereIs($field, $value)
    {
        return $this->where($field, 'is', $value);
    }
    
    /**
     * Add an "is not" filter
     * 
     * @param string $field Field name
     * @param mixed $value Value to exclude
     * @return $this
     */
    public function whereIsNot($field, $value)
    {
        return $this->where($field, 'isnot', $value);
    }
    
    /**
     * Add a "contains" filter
     * 
     * @param string $field Field name
     * @param string $value Value to search for
     * @return $this
     */
    public function whereContains($field, $value)
    {
        return $this->where($field, 'contains', $value);
    }
    
    /**
     * Add a "does not contain" filter
     * 
     * @param string $field Field name
     * @param string $value Value to exclude
     * @return $this
     */
    public function whereDoesNotContain($field, $value)
    {
        return $this->where($field, 'doesnotcontain', $value);
    }
    
    /**
     * Add a "starts with" filter
     * 
     * @param string $field Field name
     * @param string $value Value to search for
     * @return $this
     */
    public function whereStartsWith($field, $value)
    {
        return $this->where($field, 'startswith', $value);
    }
    
    /**
     * Add a "greater than" filter
     * 
     * @param string $field Field name
     * @param mixed $value Value to compare
     * @return $this
     */
    public function whereGreaterThan($field, $value)
    {
        return $this->where($field, 'greaterthan', $value);
    }
    
    /**
     * Add a "less than" filter
     * 
     * @param string $field Field name
     * @param mixed $value Value to compare
     * @return $this
     */
    public function whereLessThan($field, $value)
    {
        return $this->where($field, 'lessthan', $value);
    }
    
    /**
     * Add an "on or after" date filter
     * 
     * @param string $field Field name
     * @param string $date Date in YYYY-MM-DD format
     * @return $this
     */
    public function whereOnOrAfter($field, $date)
    {
        return $this->where($field, 'onorafter', $date);
    }
    
    /**
     * Add an "on or before" date filter
     * 
     * @param string $field Field name
     * @param string $date Date in YYYY-MM-DD format
     * @return $this
     */
    public function whereOnOrBefore($field, $date)
    {
        return $this->where($field, 'onorbefore', $date);
    }
    
    /**
     * Add an "is empty" filter
     * 
     * @param string $field Field name
     * @return $this
     */
    public function whereIsEmpty($field)
    {
        return $this->where($field, 'isempty', null);
    }
    
    /**
     * Add an "is not empty" filter
     * 
     * @param string $field Field name
     * @return $this
     */
    public function whereIsNotEmpty($field)
    {
        return $this->where($field, 'isnotempty', null);
    }
    
    /**
     * Select specific columns to return
     * 
     * @param array $columns Column names
     * @return $this
     */
    public function select($columns)
    {
        $this->columns = $columns;
        return $this;
    }
    
    /**
     * Add a column to select
     * 
     * @param string $column Column name
     * @return $this
     */
    public function addColumn($column)
    {
        $this->columns[] = $column;
        return $this;
    }
    
    /**
     * Set sort order
     * 
     * @param string $field Field to sort by
     * @param string $direction Sort direction (ASC or DESC)
     * @return $this
     */
    public function orderBy($field, $direction = 'ASC')
    {
        $this->sort[] = [
            'field' => $field,
            'direction' => strtoupper($direction)
        ];
        
        return $this;
    }
    
    /**
     * Set result limit
     * 
     * @param int $limit Maximum number of results
     * @return $this
     */
    public function limit($limit)
    {
        $this->limit = $limit;
        return $this;
    }
    
    /**
     * Set result offset
     * 
     * @param int $offset Number of results to skip
     * @return $this
     */
    public function offset($offset)
    {
        $this->offset = $offset;
        return $this;
    }
    
    /**
     * Build the search query
     * 
     * @return array Search query structure
     */
    public function build()
    {
        $query = [];
        
        if (!empty($this->criteria)) {
            $query['filters'] = $this->criteria;
        }
        
        if (!empty($this->columns)) {
            $query['columns'] = $this->columns;
        }
        
        if (!empty($this->sort)) {
            $query['sort'] = $this->sort;
        }
        
        return $query;
    }
    
    /**
     * Get search options (limit, offset)
     * 
     * @return array Search options
     */
    public function getOptions()
    {
        $options = [];
        
        if ($this->limit !== null) {
            $options['limit'] = $this->limit;
        }
        
        if ($this->offset !== null) {
            $options['offset'] = $this->offset;
        }
        
        return $options;
    }
    
    /**
     * Build a SuiteQL query
     * 
     * @param string $table Table name
     * @return string SuiteQL query
     */
    public function buildSuiteQL($table)
    {
        $select = empty($this->columns) ? '*' : implode(', ', $this->columns);
        $query = "SELECT {$select} FROM {$table}";
        
        if (!empty($this->criteria)) {
            $whereClauses = [];
            foreach ($this->criteria as $criterion) {
                $field = $criterion['field'];
                $operator = $criterion['operator'];
                $value = $criterion['value'];
                
                switch ($operator) {
                    case 'is':
                        $whereClauses[] = "{$field} = '{$value}'";
                        break;
                    case 'isnot':
                        $whereClauses[] = "{$field} != '{$value}'";
                        break;
                    case 'contains':
                        $whereClauses[] = "{$field} LIKE '%{$value}%'";
                        break;
                    case 'doesnotcontain':
                        $whereClauses[] = "{$field} NOT LIKE '%{$value}%'";
                        break;
                    case 'startswith':
                        $whereClauses[] = "{$field} LIKE '{$value}%'";
                        break;
                    case 'greaterthan':
                        $whereClauses[] = "{$field} > '{$value}'";
                        break;
                    case 'lessthan':
                        $whereClauses[] = "{$field} < '{$value}'";
                        break;
                    case 'onorafter':
                        $whereClauses[] = "{$field} >= '{$value}'";
                        break;
                    case 'onorbefore':
                        $whereClauses[] = "{$field} <= '{$value}'";
                        break;
                    case 'isempty':
                        $whereClauses[] = "{$field} IS NULL";
                        break;
                    case 'isnotempty':
                        $whereClauses[] = "{$field} IS NOT NULL";
                        break;
                }
            }
            
            if (!empty($whereClauses)) {
                $query .= ' WHERE ' . implode(' AND ', $whereClauses);
            }
        }
        
        if (!empty($this->sort)) {
            $orderClauses = [];
            foreach ($this->sort as $sortItem) {
                $orderClauses[] = $sortItem['field'] . ' ' . $sortItem['direction'];
            }
            $query .= ' ORDER BY ' . implode(', ', $orderClauses);
        }
        
        if ($this->limit !== null) {
            $query .= ' LIMIT ' . $this->limit;
        }
        
        if ($this->offset !== null) {
            $query .= ' OFFSET ' . $this->offset;
        }
        
        return $query;
    }
    
    /**
     * Reset the search builder
     * 
     * @return $this
     */
    public function reset()
    {
        $this->criteria = [];
        $this->columns = [];
        $this->sort = [];
        $this->limit = null;
        $this->offset = null;
        
        return $this;
    }
}