<?php

/**
 * SearchModel
 *
 */
class SearchModel
{
    /**
     * @param string searchquery
     * @return array results
     */
    public static function getResults($searchterm)
    {
        //$searchquery = urldecode($searchquery);
        $args = array('howMany' => Config::get('PPP'));

        DatabaseArgs::setArgs($args, 'search');
        $start = DatabaseArgs::getStart();
        $limit = DatabaseArgs::getHowMany();
        $prefix = Config::get('DB_PREFIX');
        $database = DatabaseFactory::getFactory()->getConnection();

        $userCoordinates = PlaceModel::getPlaceByPostalcode(Session::get('search_plz'));
        if ($userCoordinates) {
            $sql = "SELECT SQL_CALC_FOUND_ROWS
                           IFNULL((Places.lat - ".$userCoordinates->lat.") * (Places.lat - ".$userCoordinates->lat.") +
                           (Places.lon - ".$userCoordinates->lon.") * (Places.lon - ".$userCoordinates->lon."),'x') AS distance,
                           O.id                         AS id,
                           O.slug                       AS slug,
                           O.type                       AS type,
                           O.title                      AS title,
                           O.detail                     AS detail,
                           O.tags                       AS tags,
                           O.price                      AS price,
                           O.currency                   AS currency,
                           O.unit                       AS unit,
                           O.image                      AS image,
                           O.profile_id                 AS profile_id,
                           O.external                   AS external,
                           O.external_url               AS external_url,
                           O.zipcode                    AS postalcode,
                           O.modified                   AS modified
                    FROM ".$prefix."offers              AS O
                    LEFT JOIN ".$prefix."places         AS Places ON Places.plz = O.zipcode
                    WHERE (title LIKE CONCAT('%', :search, '%')
                       OR tags LIKE CONCAT('%', :search, '%')
                       OR detail LIKE CONCAT('%', :search, '%'))
                      AND (".DatabaseArgs::getWhereStmt().")
                    GROUP BY O.id
                    ORDER BY ".DatabaseArgs::getOrderBy()."
                    LIMIT :start,:limit";
        } else {

            $sql = "SELECT SQL_CALC_FOUND_ROWS
                           O.id                         AS id,
                           O.slug                       AS slug,
                           O.type                       AS type,
                           O.title                      AS title,
                           O.detail                     AS detail,
                           O.tags                       AS tags,
                           O.price                      AS price,
                           O.currency                   AS currency,
                           O.unit                       AS unit,
                           O.image                      AS image,
                           O.profile_id                 AS profile_id,
                           O.external                   AS external,
                           O.external_url               AS external_url,
                           O.modified                   AS modified
                    FROM ".$prefix."offers              AS O
                    WHERE (title LIKE CONCAT('%', :search, '%')
                       OR tags LIKE CONCAT('%', :search, '%')
                       OR detail LIKE CONCAT('%', :search, '%'))
                      AND (".DatabaseArgs::getWhereStmt().")
                    ORDER BY ".DatabaseArgs::getOrderBy()."
                    LIMIT :start,:limit";
        }
        $query = $database->prepare($sql);

        $query->bindParam(':start',     $start,             PDO::PARAM_INT);
        $query->bindParam(':limit',     $limit,             PDO::PARAM_INT);
        $query->bindParam(':search',    $searchterm,        PDO::PARAM_STR);
        $query->execute();

        $results = array();
        if ($query->rowCount() < 1) {
            $results['number'] = 0;
        } else {
            $results['number'] = $database->query("SELECT FOUND_ROWS()")->fetchColumn();
        }

        // save searchterm and number of results for statistics
        if ($searchterm != '') {
            self::archiveSearch($searchterm,$results['number']);
        }

        $o = 0; $r = 0;
        $results['offers'] = array();
        $results['requests'] = array();
        foreach ($query->fetchAll() as $offer) {
            if (!isset($offer->distance)) {
                $offer->distance = Text::get('NOT_AVAILABLE_SHORT');
            } else {
                $offer->distance = PA::getDistance($offer->postalcode);
                if ($offer->distance === false) {
                    $offer->distance = Text::get('NOT_AVAILABLE_SHORT');
                } elseif ($offer->distance < 3) {
                    $offer->distance = Text::get('OFFER_DISTANCE_CLOSE_BY_SHORT');
                }
            }

            if ($offer->type == 1) {
                $type = 'offers';
                $i = $o;
            } else {
                $type = 'requests';
                $i = $r;
            }

            $results[$type][$i]                 = new stdClass();
            $results[$type][$i]->id             = $offer->id;
            $results[$type][$i]->slug           = $offer->slug;
            $results[$type][$i]->title          = $offer->title;
            $results[$type][$i]->price          = $offer->price;
            $results[$type][$i]->currency       = $offer->currency;
            $results[$type][$i]->unit           = $offer->unit;
            $results[$type][$i]->image          = $offer->image;
            $results[$type][$i]->profile_id     = $offer->profile_id;
            $results[$type][$i]->external       = $offer->external;
            $results[$type][$i]->external_url   = $offer->external_url;
            $results[$type][$i]->distance       = $offer->distance;
            $results[$type][$i]->modified       = $offer->modified;

            if ($offer->type == 1) {
                $o++;
            } else {
                $r++;
            }
        }

        $results['howMany'] = $limit;
        $results['numberOffers'] = $o;
        $results['numberRequests'] = $r;

        return $results;
    }

    public static function getProfileResults($searchterm)
    {
        //$searchquery = urldecode($searchquery);
        $args = array('howMany' => Config::get('PPP'));

        DatabaseArgs::setArgs($args, 'search');
        $start = DatabaseArgs::getStart();
        $limit = DatabaseArgs::getHowMany();
        $prefix = Config::get('DB_PREFIX');
        $database = DatabaseFactory::getFactory()->getConnection();

        $userCoordinates = PlaceModel::getPlaceByPostalcode(Session::get('search_plz'));
        if ($userCoordinates) {

            $sql = "
                SELECT SQL_CALC_FOUND_ROWS
                    IFNULL((Places.lat - ".$userCoordinates->lat.") * (Places.lat - ".$userCoordinates->lat.") +
                    (Places.lon - ".$userCoordinates->lon.") * (Places.lon - ".$userCoordinates->lon."),'x') AS distance,
                    P.id                      AS id,
                    P.slug                    AS slug,
                    P.company_name            AS company_name,
                    P.company_claim           AS company_claim,
                    P.company_short_info      AS company_short_info,
                    P.company_info            AS company_info,
                    P.company_contact_person  AS company_contact_person,
                    P.company_logo_path       AS company_logo_path,
                    P.company_header_path     AS company_header_path,
                    P.company_address_zipcode AS company_zipcode,
                    P.company_address_city    AS company_city,
                    P.user_id                 AS user_id,
                    P.admin_featured          AS admin_featured,
                    P.other_featured          AS other_featured,
                    P.created                 AS created,
                    P.modified                AS modified
                FROM ".$prefix."profiles      AS P
                LEFT JOIN ".$prefix."places   AS Places ON Places.plz = P.company_address_zipcode
                WHERE company_name LIKE CONCAT('%', :search, '%')
                   OR company_claim LIKE CONCAT('%', :search, '%')
                   OR company_short_info LIKE CONCAT('%', :search, '%')
                   OR company_info LIKE CONCAT('%', :search, '%')
                   OR company_contact_person LIKE CONCAT('%', :search, '%')
                GROUP BY P.id
                LIMIT :start,:limit";
        } else {
            $sql = "SELECT SQL_CALC_FOUND_ROWS
                       P.id                      AS id,
                       P.slug                    AS slug,
                       P.company_name            AS company_name,
                       P.company_claim           AS company_claim,
                       P.company_short_info      AS company_short_info,
                       P.company_info            AS company_info,
                       P.company_contact_person  AS company_contact_person,
                       P.company_logo_path       AS company_logo_path,
                       P.company_header_path     AS company_header_path,
                       P.company_address_zipcode AS company_zipcode,
                       P.company_address_city    AS company_city,
                       P.user_id                 AS user_id,
                       P.admin_featured          AS admin_featured,
                       P.other_featured          AS other_featured,
                       P.created                 AS created,
                       P.modified                AS modified
                FROM ".$prefix."profiles         AS P
                WHERE company_name LIKE CONCAT('%', :search, '%')
                   OR company_claim LIKE CONCAT('%', :search, '%')
                   OR company_short_info LIKE CONCAT('%', :search, '%')
                   OR company_info LIKE CONCAT('%', :search, '%')
                   OR company_contact_person LIKE CONCAT('%', :search, '%')
                LIMIT :start,:limit";
        }
        $query = $database->prepare($sql);

        $query->bindParam(':start',     $start,             PDO::PARAM_INT);
        $query->bindParam(':limit',     $limit,             PDO::PARAM_INT);
        $query->bindParam(':search',    $searchterm,        PDO::PARAM_STR);
        $query->execute();

        $results = array();
        if ($query->rowCount() < 1) {
            $profiles['number'] = 0;
        } else {
            $profiles['number'] = $database->query("SELECT FOUND_ROWS()")->fetchColumn();
        }

        // save searchterm and number of results for statistics
        if ($searchterm != '') {
            self::archiveSearch($searchterm,$profiles['number']);
        }

        $i = 0;
        foreach ($query->fetchAll() as $profile) {
            if (!isset($profile->distance)) {
                $profile->distance = Text::get('NOT_AVAILABLE_SHORT');
            } else {
                $profile->distance = PA::getDistance($profile->company_zipcode);
                if ($profile->distance === false) {
                    $profile->distance = Text::get('NOT_AVAILABLE_SHORT');
                } elseif ($profile->distance < 3) {
                    $profile->distance = Text::get('OFFER_DISTANCE_CLOSE_BY_SHORT');
                }
            }

            $profiles['data'][$i]                 = new stdClass();
            $profiles['data'][$i]->id             = $profile->id;
            $profiles['data'][$i]->slug           = $profile->slug;
            $profiles['data'][$i]->company_name   = $profile->company_name;
            $profiles['data'][$i]->company_claim  = $profile->company_claim;
            $profiles['data'][$i]->logo_path      = $profile->company_logo_path;
            $profiles['data'][$i]->header_path    = $profile->company_header_path;
            $profiles['data'][$i]->zipcode        = $profile->company_zipcode;
            $profiles['data'][$i]->city           = $profile->company_city;
            $profiles['data'][$i]->distance       = $profile->distance;
            $profiles['data'][$i]->user_id        = $profile->user_id;
            $profiles['data'][$i]->admin_featured = $profile->admin_featured;
            $profiles['data'][$i]->other_featured = $profile->other_featured;
            $profiles['data'][$i]->created        = $profile->created;
            $profiles['data'][$i]->modified       = $profile->modified;
            $i++;
        }

        $profiles['howMany'] = $limit;

        return $profiles;
    }

    private static function archiveSearch($searchterm, $results)
    {
        $prefix = Config::get('DB_PREFIX');
        $database = DatabaseFactory::getFactory()->getConnection();
        $sql = "INSERT INTO ".$prefix."searches
                    (searchterm, results, created)
                VALUES
                    (:searchterm, :results, NOW())";
        $query = $database->prepare($sql);
        $query->bindParam(':searchterm',    $searchterm,    PDO::PARAM_STR);
        $query->bindParam(':results',       $results,       PDO::PARAM_INT);
        $query->execute();
    }

    public static function clean()
    {
        $qclean = strtolower(Request::post('search',true));

        Session::set('search_plz',
            Request::post('search_zip',true));

        if (Request::post('custom_search',true) == 1) {
            Session::set('search_offers',
                Request::post('search_offers',true) == 1 ? 'y' : 'n');
            Session::set('search_requests',
                Request::post('search_requests',true) == 1 ? 'y' : 'n');
            Session::set('search_internal',
                Request::post('search_intern',true) == 1 ? 'y' : 'n');
            Session::set('search_external',
                Request::post('search_extern',true) == 1 ? 'y' : 'n');
        }

        $qclean = htmlspecialchars($qclean);

        // Remove multiple adjacent spaces
        while (strstr($qclean, "  ")) {
           $qclean = str_replace("  ", " ", $qclean);
        }

        // Replace single spaces with a URL friendly plus sign
        $qclean = str_replace(" ", "+", $qclean);

        // If validation has passed, return search value
        if ($qclean != '') {
           return $qclean;
        }
    }
}
