<?PHP
#
#   FILE:  SPTSearchEngine.php
#
#   Part of the Collection Workflow Integration System (CWIS)
#   Copyright 2011 Edward Almasy and Internet Scout Project
#   http://scout.wisc.edu/
#

class SPTSearchEngine extends SearchEngine {

    function SPTSearchEngine()
    {
        # create a database handle
        $DB = new Database();

        # pass database handle and config values to real search engine object
        $this->SearchEngine($DB, "Resources", "ResourceId");

        # for each field defined in schema
        $this->Schema = new MetadataSchema();
        $Fields = $this->Schema->GetFields();
        foreach ($Fields as $Field)
        {
            # determine field type for searching
            switch ($Field->Type())
            {
                case MetadataSchema::MDFTYPE_TEXT:
                case MetadataSchema::MDFTYPE_PARAGRAPH:
                case MetadataSchema::MDFTYPE_USER:
                case MetadataSchema::MDFTYPE_TREE:
                case MetadataSchema::MDFTYPE_CONTROLLEDNAME:
                case MetadataSchema::MDFTYPE_OPTION:
                case MetadataSchema::MDFTYPE_IMAGE:
                case MetadataSchema::MDFTYPE_FILE:
                case MetadataSchema::MDFTYPE_URL:
                    $FieldType = self::FIELDTYPE_TEXT;
                    break;

                case MetadataSchema::MDFTYPE_NUMBER:
                case MetadataSchema::MDFTYPE_FLAG:
                    $FieldType = self::FIELDTYPE_NUMERIC;
                    break;

                case MetadataSchema::MDFTYPE_DATE:
                    $FieldType = self::FIELDTYPE_DATERANGE;
                    break;

                case MetadataSchema::MDFTYPE_TIMESTAMP:
                    $FieldType = self::FIELDTYPE_DATE;
                    break;

                case MetadataSchema::MDFTYPE_POINT:
                    $FieldType = NULL;
                    break;

                default:
                    exit("ERROR: unknown field type "
                            .$Field->Type()." in SPTSearchEngine.php");
                    break;
            }

            if ($FieldType !== NULL)
            {
                # add field to search engine
                $this->AddField($Field->Name(), $Field->DBFieldName(), $FieldType,
                                $Field->SearchWeight(), $Field->IncludeInKeywordSearch());
            }
        }
    }

    # overloaded version of method to retrieve text from DB
    function GetFieldContent($ItemId, $FieldName)
    {
        # get resource object
        $Resource = new Resource($ItemId);

        # retrieve text (including variants) from resource object and return to caller
        return $Resource->Get($FieldName, FALSE, TRUE);
    }

    # overloaded version of method to retrieve resource/phrase match list
    function SearchFieldForPhrases($FieldName, $Phrase)
    {
        # normalize and escape search phrase for use in SQL query
        $SearchPhrase = strtolower(addslashes($Phrase));

        # query DB for matching list based on field type
        $Field = $this->Schema->GetFieldByName($FieldName);
        switch ($Field->Type())
        {
            case MetadataSchema::MDFTYPE_TEXT:
            case MetadataSchema::MDFTYPE_PARAGRAPH:
            case MetadataSchema::MDFTYPE_FILE:
            case MetadataSchema::MDFTYPE_URL:
                $QueryString = "SELECT DISTINCT ResourceId FROM Resources "
                        ."WHERE POSITION('".$SearchPhrase."'"
                            ." IN LOWER(`".$Field->DBFieldName()."`)) ";
                break;

            case MetadataSchema::MDFTYPE_IMAGE:
                $QueryString = "SELECT DISTINCT ResourceId FROM Resources "
                        ."WHERE POSITION('".$SearchPhrase."'"
                            ." IN LOWER(`".$Field->DBFieldName()."AltText`)) ";
                break;

            case MetadataSchema::MDFTYPE_CONTROLLEDNAME:
                $NameTableSize = $this->DB->Query("SELECT COUNT(*) AS NameCount"
                        ." FROM ControlledNames", "NameCount");
                $QueryString = "SELECT DISTINCT ResourceNameInts.ResourceId "
                        ."FROM ResourceNameInts, ControlledNames "
                        ."WHERE POSITION('".$SearchPhrase."' IN LOWER(ControlledName)) "
                        ."AND ControlledNames.ControlledNameId"
                                ." = ResourceNameInts.ControlledNameId "
                        ."AND ControlledNames.FieldId = ".$Field->Id();
                $SecondQueryString = "SELECT DISTINCT ResourceNameInts.ResourceId "
                        ."FROM ResourceNameInts, ControlledNames, VariantNames "
                        ."WHERE POSITION('".$SearchPhrase."' IN LOWER(VariantName)) "
                        ."AND VariantNames.ControlledNameId"
                                ." = ResourceNameInts.ControlledNameId "
                        ."AND ControlledNames.ControlledNameId"
                                ." = ResourceNameInts.ControlledNameId "
                        ."AND ControlledNames.FieldId = ".$Field->Id();
                break;

            case MetadataSchema::MDFTYPE_OPTION:
                $QueryString = "SELECT DISTINCT ResourceNameInts.ResourceId "
                        ."FROM ResourceNameInts, ControlledNames "
                        ."WHERE POSITION('".$SearchPhrase."' IN LOWER(ControlledName)) "
                        ."AND ControlledNames.ControlledNameId = ResourceNameInts.ControlledNameId "
                        ."AND ControlledNames.FieldId = ".$Field->Id();
                break;

            case MetadataSchema::MDFTYPE_TREE:
                $QueryString = "SELECT DISTINCT ResourceClassInts.ResourceId "
                        ."FROM ResourceClassInts, Classifications "
                        ."WHERE POSITION('".$SearchPhrase."' IN LOWER(ClassificationName)) "
                        ."AND Classifications.ClassificationId = ResourceClassInts.ClassificationId "
                        ."AND Classifications.FieldId = ".$Field->Id();
                break;

            case MetadataSchema::MDFTYPE_USER:
                $UserId = $this->DB->Query("SELECT UserId FROM APUsers "
                                           ."WHERE POSITION('".$SearchPhrase."' IN LOWER(UserName)) "
                                           ."OR POSITION('".$SearchPhrase."' IN LOWER(RealName))", "UserId");
                if ($UserId != NULL)
                {
                    $QueryString = "SELECT DISTINCT ResourceId FROM Resources "
                                     ."WHERE `".$Field->DBFieldName()."` = ".$UserId;
                }
                break;

            case MetadataSchema::MDFTYPE_NUMBER:
                if ($SearchPhrase > 0)
                {
                    $QueryString = "SELECT DISTINCT ResourceId FROM Resources "
                                     ."WHERE `".$Field->DBFieldName()."` = ".(int)$SearchPhrase;
                }
                break;

            case MetadataSchema::MDFTYPE_FLAG:
            case MetadataSchema::MDFTYPE_DATE:
            case MetadataSchema::MDFTYPE_TIMESTAMP:
                # (these types not yet handled by search engine for phrases)
                break;
        }

        # build match list based on results returned from DB
        if (isset($QueryString))
        {
            $this->DMsg(7, "Performing phrase search query (<i>".$QueryString."</i>)");
            if ($this->DebugLevel > 9) {  $StartTime = microtime(TRUE);  }
            $this->DB->Query($QueryString);
            if ($this->DebugLevel > 9)
            {
                $EndTime = microtime(TRUE);
                if (($StartTime - $EndTime) > 0.1)
                {
                    printf("SE:  Query took %.2f seconds<br>\n",
                            ($EndTime - $StartTime));
                }
            }
            $MatchList = $this->DB->FetchColumn("ResourceId");
            if (isset($SecondQueryString))
            {
                $this->DMsg(7, "Performing second phrase search query"
                        ." (<i>".$SecondQueryString."</i>)");
                if ($this->DebugLevel > 9) {  $StartTime = microtime(TRUE);  }
                $this->DB->Query($SecondQueryString);
                if ($this->DebugLevel > 9)
                {
                    $EndTime = microtime(TRUE);
                    if (($StartTime - $EndTime) > 0.1)
                    {
                        printf("SE:  query took %.2f seconds<br>\n",
                                ($EndTime - $StartTime));
                    }
                }
                $MatchList = $MatchList + $this->DB->FetchColumn("ResourceId");
            }
        }
        else
        {
            $MatchList = array();
        }

        # return list of matching resources to caller
        return $MatchList;
    }

    # search field for records that meet comparison
    function SearchFieldsForComparisonMatches($FieldNames, $Operators, $Values)
    {
        # use SQL keyword appropriate to current search logic for combining operations
        $CombineWord = ($this->DefaultSearchLogic == self::LOGIC_AND) ? " AND " : " OR ";

        # for each comparison
        foreach ($FieldNames as $Index => $FieldName)
        {
            $Operator = $Operators[$Index];
            $Value = $Values[$Index];

            # determine query based on field type
            $Field = $this->Schema->GetFieldByName($FieldName);
            if ($Field != NULL)
            {
                switch ($Field->Type())
                {
                    case MetadataSchema::MDFTYPE_TEXT:
                    case MetadataSchema::MDFTYPE_PARAGRAPH:
                    case MetadataSchema::MDFTYPE_NUMBER:
                    case MetadataSchema::MDFTYPE_FLAG:
                    case MetadataSchema::MDFTYPE_USER:
                    case MetadataSchema::MDFTYPE_URL:
                        if (isset($Queries["Resources"]))
                        {
                            $Queries["Resources"] .= $CombineWord;
                        }
                        else
                        {
                            $Queries["Resources"] = "SELECT DISTINCT ResourceId FROM Resources WHERE ";
                        }
                        if ($Field->Type() == MetadataSchema::MDFTYPE_USER)
                        {
                            $User = new SPTUser($Value);
                            $Value = $User->Id();
                        }
                        $Queries["Resources"] .= "`".$Field->DBFieldName()."` ".$Operator." '".addslashes($Value)."' ";
                        break;

                    case MetadataSchema::MDFTYPE_CONTROLLEDNAME:
                        $QueryIndex = "ResourceNameInts".$Field->Id();
                        if (!isset($Queries[$QueryIndex]["A"]))
                        {
                            $Queries[$QueryIndex]["A"] =
                                    "SELECT DISTINCT ResourceId"
                                    ." FROM ResourceNameInts, ControlledNames "
                                    ." WHERE ControlledNames.FieldId = ".$Field->Id()
                                    ." AND ( ";
                            $CloseQuery[$QueryIndex]["A"] = TRUE;
                        }
                        else
                        {
                            $Queries[$QueryIndex]["A"] .= $CombineWord;
                        }
                        $Queries[$QueryIndex]["A"] .=
                                "((ResourceNameInts.ControlledNameId"
                                        ." = ControlledNames.ControlledNameId"
                                ." AND ControlledName "
                                        .$Operator." '".addslashes($Value)."'))";
                        if (!isset($Queries[$QueryIndex]["B"]))
                        {
                            $Queries[$QueryIndex]["B"] =
                                    "SELECT DISTINCT ResourceId"
                                    . " FROM ResourceNameInts, ControlledNames,"
                                            ." VariantNames "
                                    ." WHERE ControlledNames.FieldId = ".$Field->Id()
                                    ." AND ( ";
                            $CloseQuery[$QueryIndex]["B"] = TRUE;
                        }
                        else
                        {
                            $Queries[$QueryIndex]["B"] .= $CombineWord;
                        }
                        $Queries[$QueryIndex]["B"] .=
                                "((ResourceNameInts.ControlledNameId"
                                        ." = ControlledNames.ControlledNameId"
                                ." AND ResourceNameInts.ControlledNameId"
                                        ." = VariantNames.ControlledNameId"
                                ." AND VariantName "
                                        .$Operator." '".addslashes($Value)."'))";
                        break;

                    case MetadataSchema::MDFTYPE_OPTION:
                        $QueryIndex = "ResourceNameInts".$Field->Id();
                        if (!isset($Queries[$QueryIndex]))
                        {
                            $Queries[$QueryIndex] =
                                    "SELECT DISTINCT ResourceId FROM ResourceNameInts, ControlledNames "
                                    ." WHERE ControlledNames.FieldId = ".$Field->Id()
                                    ." AND ( ";
                            $CloseQuery[$QueryIndex] = TRUE;
                        }
                        else
                        {
                            $Queries[$QueryIndex] .= $CombineWord;
                        }
                        $Queries[$QueryIndex] .= "(ResourceNameInts.ControlledNameId = ControlledNames.ControlledNameId"
                                                       ." AND ControlledName ".$Operator." '".addslashes($Value)."')";
                        break;

                    case MetadataSchema::MDFTYPE_TREE:
                        $QueryIndex = "ResourceClassInts".$Field->Id();
                        if (!isset($Queries[$QueryIndex]))
                        {
                            $Queries[$QueryIndex] = "SELECT DISTINCT ResourceId FROM ResourceClassInts, Classifications "
                                                 ." WHERE ResourceClassInts.ClassificationId = Classifications.ClassificationId"
                                                 ." AND Classifications.FieldId = ".$Field->Id()." AND ( ";
                            $CloseQuery[$QueryIndex] = TRUE;
                        }
                        else
                        {
                            $Queries[$QueryIndex] .= $CombineWord;
                        }
                        $Queries[$QueryIndex] .= " ClassificationName ".$Operator." '".addslashes($Value)."'";
                        break;

                    case MetadataSchema::MDFTYPE_TIMESTAMP:
                        # if value appears to have time component or text description
                        if (strpos($Value, ":")
                                || strstr($Value, "day")
                                || strstr($Value, "week")
                                || strstr($Value, "month")
                                || strstr($Value, "year")
                                || strstr($Value, "hour")
                                || strstr($Value, "minute"))
                        {
                            if (isset($Queries["Resources"]))
                            {
                                $Queries["Resources"] .= $CombineWord;
                            }
                            else
                            {
                                $Queries["Resources"] = "SELECT DISTINCT ResourceId"
                                        ." FROM Resources WHERE ";
                            }

                            # flip operator if necessary
                            if (strstr($Value, "ago"))
                            {
                                $OperatorFlipMap = array(
                                        "<" => ">=",
                                        ">" => "<=",
                                        "<=" => ">",
                                        ">=" => "<",
                                        );
                                $Operator = isset($OperatorFlipMap[$Operator])
                                        ? $OperatorFlipMap[$Operator] : $Operator;
                            }

                            # use strtotime method to build condition
                            $TimestampValue = strtotime($Value);
                            if (($TimestampValue !== FALSE) && ($TimestampValue != -1))
                            {
                                if ((date("H:i:s", $TimestampValue) == "00:00:00")
                                        && (strpos($Value, "00:00") === FALSE)
                                        && ($Operator == "<="))
                                {
                                    $NormalizedValue =
                                            date("Y-m-d", $TimestampValue)." 23:59:59";
                                }
                                else
                                {
                                    $NormalizedValue = date("Y-m-d H:i:s", $TimestampValue);
                                }
                            }
                            else
                            {
                                $NormalizedValue = addslashes($Value);
                            }
                            $Queries["Resources"] .=
                                    " ( `".$Field->DBFieldName()."` "
                                    .$Operator
                                    ." '".$NormalizedValue."' ) ";
                        }
                        else
                        {
                            # use Date object method to build condition
                            $Date = new Date($Value);
                            if ($Date->Precision())
                            {
                                if (isset($Queries["Resources"]))
                                {
                                    $Queries["Resources"] .= $CombineWord;
                                }
                                else
                                {
                                    $Queries["Resources"] = "SELECT DISTINCT ResourceId"
                                            ." FROM Resources WHERE ";
                                }
                                $Queries["Resources"] .= " ( ".$Date->SqlCondition(
                                        $Field->DBFieldName(), NULL, $Operator)." ) ";
                            }
                        }
                        break;

                    case MetadataSchema::MDFTYPE_DATE:
                        $Date = new Date($Value);
                        if ($Date->Precision())
                        {
                            if (isset($Queries["Resources"]))
                            {
                                $Queries["Resources"] .= $CombineWord;
                            }
                            else
                            {
                                $Queries["Resources"] = "SELECT DISTINCT ResourceId"
                                        ." FROM Resources WHERE ";
                            }
                            $Queries["Resources"] .= " ( ".$Date->SqlCondition(
                                    $Field->DBFieldName()."Begin",
                                    $Field->DBFieldName()."End", $Operator)." ) ";
                        }
                        break;

                    case MetadataSchema::MDFTYPE_IMAGE:
                    case MetadataSchema::MDFTYPE_FILE:
                        # (these types not yet handled by search engine for comparisons)
                        break;
                }
            }
        }

        # if queries found
        if (isset($Queries))
        {
            # for each assembled query
            foreach ($Queries as $QueryIndex => $Query)
            {
                # if query has multiple parts
                if (is_array($Query))
                {
                    # for each part of query
                    $ResourceIds = array();
                    foreach ($Query as $PartIndex => $PartQuery)
                    {
                        # add closing paren if query was flagged to be closed
                        if (isset($CloseQuery[$QueryIndex][$PartIndex])) 
                                {  $PartQuery .= " ) ";  }

                        # perform query and retrieve IDs
                        $this->DMsg(5, "Performing comparison query (<i>"
                                .$PartQuery."</i>)");
                        $this->DB->Query($PartQuery);
                        $ResourceIds = $ResourceIds
                                + $this->DB->FetchColumn("ResourceId");
                        $this->DMsg(5, "Comparison query produced <i>"
                                .count($ResourceIds)."</i> results");
                    }
                }
                else
                {
                    # add closing paren if query was flagged to be closed
                    if (isset($CloseQuery[$QueryIndex])) {  $Query .= " ) ";  }

                    # perform query and retrieve IDs
                    $this->DMsg(5, "Performing comparison query (<i>".$Query."</i>)");
                    $this->DB->Query($Query);
                    $ResourceIds = $this->DB->FetchColumn("ResourceId");
                    $this->DMsg(5, "Comparison query produced <i>"
                            .count($ResourceIds)."</i> results");
                }

                # if we already have some results
                if (isset($Results))
                {
                    # if search logic is set to AND
                    if ($this->DefaultSearchLogic == self::LOGIC_AND)
                    {
                        # remove anything from results that was not returned from query
                        $Results = array_intersect($Results, $ResourceIds);
                    }
                    else
                    {
                        # add values returned from query to results
                        $Results = array_unique(array_merge($Results, $ResourceIds));
                    }
                }
                else
                {
                    # set results to values returned from query
                    $Results = $ResourceIds;
                }
            }
        }
        else
        {
            # initialize results to empty list
            $Results = array();
        }

        # return results to caller
        return $Results;
    }

    static function GetItemIdsSortedByField($FieldName, $SortDescending)
    {
        $RFactory = new ResourceFactory();
        return $RFactory->GetResourceIdsSortedBy($FieldName, !$SortDescending);
    }

    static function QueueUpdateForItem($ItemId,
            $Priority = ApplicationFramework::PRIORITY_LOW)
    {
        global $AF;
        $AF->QueueUniqueTask(array(__CLASS__, "RunUpdateForItem"),
                array(intval($ItemId)), $Priority);
    }

    static function RunUpdateForItem($ItemId)
    {
        # check that resource still exists
        $RFactory = new ResourceFactory();
        if (!$RFactory->ItemExists($ItemId)) {  return;  }

        # update search data for resource
        $SearchEngine = new SPTSearchEngine();
        $SearchEngine->UpdateForItem($ItemId);
    }

    private $Schema;

    # functions for backward compatability w/ old SPT code
    function UpdateForResource($ItemId) {  $this->UpdateForItem($ItemId);  }
}
