<?php

/**
 * OfferModel
 *
 */
class OfferModel
{
    public static function checkOwnership($offer_id)
    {
        if (Session::userIsAdmin())
        {
            return true;
        } else {
            $prefix = Config::get('DB_PREFIX');
            $database = DatabaseFactory::getFactory()->getConnection();
            $sql = "SELECT U.user_id
                    FROM ".$prefix."offers AS O
                    LEFT JOIN ".$prefix."profiles AS P ON O.profile_id = P.id
                    LEFT JOIN ".$prefix."users AS U ON U.user_id = P.user_id
                    WHERE O.id = :offer_id
                    LIMIT 1";
            $query = $database->prepare($sql);
            $query->bindParam(':offer_id',   $offer_id,          PDO::PARAM_INT);
            $query->execute();
            if ($query->rowCount() == 1)
            {
                $offer = $query->fetch();
                if ($offer->user_id == Session::get('user_id'))
                {
                    return true;
                } else {
                    Session::add('feedback_negative',
                            Text::get('FEEDBACK_OFFER_INSUFFICIENT_PRIVILEGES'));
                    Redirect::to('offers/view/'.$offer_id);
                    exit();
                }
            } else {
                Session::add('feedback_negative',
                        Text::get('FEEDBACK_OFFER_INSUFFICIENT_PRIVILEGES'));
                Redirect::to('offers/view/'.$offer_id);
                exit();
            }
        }
    }

    public static function isOwner($offer_id)
    {
        if (Session::userIsAdmin())
        {
            return true;
        } else {
            $prefix = Config::get('DB_PREFIX');
            $database = DatabaseFactory::getFactory()->getConnection();
            $sql = "SELECT U.user_id
                    FROM ".$prefix."offers AS O
                    LEFT JOIN ".$prefix."profiles AS P ON O.profile_id = P.id
                    LEFT JOIN ".$prefix."users AS U ON U.user_id = P.user_id
                    WHERE O.id = :offer_id
                    LIMIT 1";
            $query = $database->prepare($sql);
            $query->bindParam(':offer_id',   $offer_id,          PDO::PARAM_INT);
            $query->execute();
            if ($query->rowCount() == 1)
            {
                $offer = $query->fetch();
                if ($offer->user_id == Session::get('user_id'))
                {
                    return true;
                } else {
                    return false;
                }
            } else {
                return false;
            }
        }
    }

    public static function featureOffer($type,$offer_id)
    {
        $type = $type.'_featured';
        $offer = self::getOffer($offer_id);
        if ($offer->{$type} == 1) {
            $feature = 0;
        } else {
            $feature = 1;
        }
        if (self::updateOffer($type, $feature, $offer_id))
        {
            Session::add('feedback_positive',
                            Text::get('FEEDBACK_OFFER_FEATURED'));
        } else {
            Session::add('feedback_negative',
                            Text::get('FEEDBACK_OFFER_FEATURE_FAILED'));
        }
    }

    /**
     *
     * @return array Offers
     */
    public static function getOffers($args)
    {
        $args = Helper::filterArgs($args,'offers');

        $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.title                      AS title,
                           O.price                      AS price,
                           O.currency                   AS currency,
                           O.unit                       AS unit,
                           O.image                      AS image,
                           O.profile_id                 AS profile_id,
                           O.admin_featured             AS admin_featured,
                           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
                    ".$args['where']."
                    ORDER BY ".$args['order']."
                    LIMIT :start,:limit";
        } else {
            $sql = "SELECT SQL_CALC_FOUND_ROWS
                           O.id                         AS id,
                           O.title                      AS title,
                           O.price                      AS price,
                           O.currency                   AS currency,
                           O.unit                       AS unit,
                           O.image                      AS image,
                           O.profile_id                 AS profile_id,
                           O.admin_featured             AS admin_featured,
                           O.external                   AS external,
                           O.external_url               AS external_url,
                           O.modified                   AS modified
                    FROM ".$prefix."offers AS O
                    ".$args['where']."
                    ORDER BY ".$args['order']."
                    LIMIT :start,:limit";
        }
        $query = $database->prepare($sql);
        $query->bindParam(':start',     $args['start'],     PDO::PARAM_INT);
        $query->bindParam(':limit',     $args['howMany'],   PDO::PARAM_INT);
        $query->execute();
        $results = array();
        $results['number'] = $database->query("SELECT FOUND_ROWS()")->fetchColumn();

        $offers = array();

        $i = 0;
        foreach ($query->fetchAll() as $offer)
        {
            if (!isset($offer->distance))
            {
                $offer->distance = '[N/A]';
                $offer->postalcode = '[N/A]';
            }
            else
            {
                $offer->distance = PA::getDistance($offer->postalcode);
                if ($offer->distance === false)
                {
                    $offer->distance = '[N/A]';
                }
                elseif ($offer->distance < 3)
                {
                    $offer->distance = 'Umgebung*';
                }
            }
            $offers[$i]                 = new stdClass();
            $offers[$i]->id             = $offer->id;
            $offers[$i]->title          = $offer->title;
            $offers[$i]->price          = $offer->price;
            $offers[$i]->currency       = $offer->currency;
            $offers[$i]->unit           = $offer->unit;
            $offers[$i]->image          = $offer->image;
            $offers[$i]->profile_id     = $offer->profile_id;
            $offers[$i]->admin_featured = $offer->admin_featured;
            $offers[$i]->postalcode     = $offer->postalcode;
            $offers[$i]->distance       = $offer->distance;
            $offers[$i]->external       = $offer->external;
            $offers[$i]->external_url   = $offer->external_url;
            $offers[$i]->modified       = $offer->modified;
            $i++;
        }

        $results['offers'] = $offers;

        if ($query->rowCount() < 1)
        {
            $results['number'] = 0;
        }
        return $results;
    }

    /**
     *
     * @return array Offers
     */
    public static function getOffersWithProfile($args)
    {
        $args = Helper::filterArgs($args);
        $prefix = Config::get('DB_PREFIX');
        $database = DatabaseFactory::getFactory()->getConnection();

        $sql = "SELECT SQL_CALC_FOUND_ROWS
                       O.id                     AS id,
                       O.profile_id             AS profile_id,
                       O.title                  AS title,
                       O.status                 AS status,
                       O.external               AS external,
                       O.external_platform_id   AS external_platform_id,
                       P.company_name           AS company_name,
                       O.created                AS created,
                       O.modified               AS modified
                FROM ".$prefix."offers          AS O
                LEFT JOIN ".$prefix."profiles   AS P ON O.profile_id = P.id
                ".$args['where']."
                ORDER BY ".$args['order']."
                LIMIT :start,:limit";
        $query = $database->prepare($sql);
        $query->bindParam(':start',     $args['start'],     PDO::PARAM_INT);
        $query->bindParam(':limit',     $args['howMany'],   PDO::PARAM_INT);
        $query->execute();
        $results = array();
        $results['number'] = $database->query("SELECT FOUND_ROWS()")->fetchColumn();

        $offers = array();

        $i = 0;
        foreach ($query->fetchAll() as $offer)
        {
            $offers[$i]                         = new stdClass();
            $offers[$i]->id                     = $offer->id;
            $offers[$i]->profile_id             = $offer->profile_id;
            $offers[$i]->title                  = $offer->title;
            $offers[$i]->status                 = $offer->status;
            $offers[$i]->external               = $offer->external;
            $offers[$i]->external_platform_id   = $offer->external_platform_id;
            $offers[$i]->company_name           = $offer->company_name;
            $offers[$i]->created                = $offer->created;
            $offers[$i]->modified               = $offer->modified;
            $i++;
        }

        $results['offers'] = $offers;

        if ($query->rowCount() < 1)
        {
            $results['number'] = 0;
        }
        return $results;
    }

    public static function getOffer($offer_id)
    {
        $prefix = Config::get('DB_PREFIX');
        $database = DatabaseFactory::getFactory()->getConnection();

        $sql = "SELECT id,
                       type,
                       title,
                       detail,
                       tags,
                       price,
                       currency,
                       unit,
                       image,
                       user_featured,
                       admin_featured,
                       profile_id,
                       cat1 AS category1,
                       cat2 AS category2,
                       cat3 AS category3,
                       zipcode,
                       status,
                       external,
                       external_platform_id,
                       created,
                       modified
                FROM ".$prefix."offers
                WHERE id = :offer_id
                LIMIT 1";
        $query = $database->prepare($sql);
        $query->bindParam(':offer_id', $offer_id, PDO::PARAM_INT);
        if ($query->execute() && $query->rowCount() > 0)
        {
            $offer = $query->fetch();
            if ($offer->status == 0) { $offer->status = 3; }
            return $offer;
        } else {
            return false;
        }
    }

    public static function saveOffer($offer_id)
    {
        $values = array('offerType'                     => 'type',
                        'offerCategory1'                => 'cat1',
                        'offerCategory2'                => 'cat2',
                        'offerZipcode'                  => 'zipcode',
                        'offerTitle'                    => 'title',
                        'offerDetail'                   => 'detail',
                        'offerTags'                     => 'tags',
                        'offerPrice'                    => 'price',
                        'offerCurrency'                 => 'currency',
                        'offerUnit'                     => 'unit',
                        'offerStatus'                   => 'status',
                        'offerFeatured'                 => 'user_featured');

        $i = 0; $saved = 0;
        foreach($values as $postName => $DBName)
        {
            switch ($postName) {
                case 'offerCategory1':
                    $value = Text::get('CATEGORY2')[Request::post('offerCategory2', true)][1];
                    break;
                case 'offerZipcode':
                    $profile_id = self::getProfileIDByOfferID($offer_id);
                    $value = UserProfileModel::getUserProfile($profile_id)->company_address_zipcode;
                    break;
                case 'offerPrice':
                    switch (Request::post('offerUnit', true)) {
                        case 1:
                            $value = Helper::convertPriceForDB(999);
                            break;
                        case 2:
                            $value = Helper::convertPriceForDB(0);
                            break;
                        default:
                            $dec = Text::get('CURRENCY')[Request::post('offerCurrency', true)][4];
                            $value = Helper::convertPriceForDB(Request::post($postName, true),$dec);
                            break;
                    }
                    break;
                default:
                    $value = Request::post($postName, true);
                    break;
            }

            if (self::updateOffer($DBName,
                                  $value,
                                  $offer_id)) {
                $saved++;
            }
            $i++;
        }
        if ($i == $saved)
        {
            Session::add('feedback_positive',
                            Text::get('FEEDBACK_OFFER_SAVED'));
        } else {
            Session::add('feedback_negative',
                            Text::get('FEEDBACK_OFFER_SAVING_FAILED'));
        }
    }

    public static function saveImage($offer_id,$imagePath)
    {
        return self::updateOffer('image',$imagePath,$offer_id);
    }

    public static function getEmptyOffer($profile_id)
    {
        $prefix = Config::get('DB_PREFIX');
        $database = DatabaseFactory::getFactory()->getConnection();

        $sql = "SELECT id,
                       type,
                       title,
                       detail,
                       tags,
                       price,
                       currency,
                       unit,
                       image,
                       user_featured,
                       admin_featured,
                       profile_id,
                       cat1 AS category1,
                       cat2 AS category2,
                       cat3 AS category3,
                       status,
                       created,
                       modified
                FROM ".$prefix."offers
                WHERE profile_id = :profile_id
                  AND title IS NULL
                  AND detail IS NULL
                  AND tags IS NULL
                  AND price IS NULL
                  AND image IS NULL
                  AND user_featured = 0
                  AND status = 0
                LIMIT 1";

        $query = $database->prepare($sql);
        $query->bindParam(':profile_id',       $profile_id,               PDO::PARAM_INT);
        $query->execute();
        if ($query->rowCount() == 1)
        {
            $result = $query->fetch();
            $result->status = 3;
            $result->price = 0;
            return $result;
        } else {
            return self::createEmptyOffer($profile_id);
        }
    }

    public static function createEmptyOffer($profile_id)
    {
        $prefix = Config::get('DB_PREFIX');
        $database = DatabaseFactory::getFactory()->getConnection();

        $sql = "INSERT INTO ".$prefix."offers
                    (profile_id,created)
                VALUES
                    (:profile_id, NOW())";
        $query = $database->prepare($sql);
        $query->bindParam(':profile_id',    $profile_id,    PDO::PARAM_INT);
        if ($query->execute())
        {
            $offer                  = new stdClass();
            $offer->id              = $database->lastInsertId();
            $offer->type            = 1;
            $offer->title           = '';
            $offer->detail          = '';
            $offer->tags            = '';
            $offer->price           = 0;
            $offer->currency        = 1;
            $offer->unit            = 0;
            $offer->image           = NULL;
            $offer->user_featured   = 0;
            $offer->profile_id      = $profile_id;
            $offer->category1       = 1;
            $offer->category2       = 0;
            $offer->category3       = 0;
            $offer->status          = 3;
            return $offer;
        } else {
            return false;
        }
    }

    public static function getProfileIDByOfferID($offer_id)
    {
        $prefix = Config::get('DB_PREFIX');
        $database = DatabaseFactory::getFactory()->getConnection();
        $sql = "SELECT profile_id
                FROM ".$prefix."offers
                WHERE id = :offer_id
                LIMIT 1";
        $query = $database->prepare($sql);
        $query->bindParam(':offer_id',      $offer_id,              PDO::PARAM_INT);
        $query->execute();
        return $query->fetch()->profile_id;
    }

    public static function updateOffer($db_name,$value,$offer_id)
    {
        $prefix = Config::get('DB_PREFIX');
        $database = DatabaseFactory::getFactory()->getConnection();

        $sql = "UPDATE ".$prefix."offers
                SET ".$db_name." = :value,
                    modified = NOW()
                WHERE id = :offer_id
                LIMIT 1";
        $query = $database->prepare($sql);
        $query->bindParam(':value',         $value,                 PDO::PARAM_STR);
        $query->bindParam(':offer_id',      $offer_id,              PDO::PARAM_INT);
        if ($query->execute())
        {
            return true;
        } else {
            return false;
        }
    }

    public static function updateOfferZipcodes($profile_id,$zipcode)
    {
        $prefix = Config::get('DB_PREFIX');
        $database = DatabaseFactory::getFactory()->getConnection();

        $sql = "UPDATE ".$prefix."offers
                SET zipcode = :zipcode,
                    modified = NOW()
                WHERE profile_id = :profile_id";
        $query = $database->prepare($sql);
        $query->bindParam(':zipcode',       $zipcode,               PDO::PARAM_STR);
        $query->bindParam(':profile_id',    $profile_id,            PDO::PARAM_INT);
        if ($query->execute()) {
            return true;
        } else {
            return false;
        }
    }

    public static function deleteOffer($offer_id)
    {
        $prefix = Config::get('DB_PREFIX');
        $database = DatabaseFactory::getFactory()->getConnection();
        $sql = "SELECT image
                FROM ".$prefix."offers
                WHERE id = :offer_id
                LIMIT 1";
        $query = $database->prepare($sql);
        $query->bindParam(':offer_id',      $offer_id,              PDO::PARAM_INT);
        $query->execute();
        $offer = $query->fetch();
        $img = array();
        if ($offer->image != null)
        {
            $img['offer'] = $offer->image;
        }
        UploadModel::deleteImages($img,false);
        $sql = "DELETE
                FROM ".$prefix."offers
                WHERE id = :offer_id
                LIMIT 1";
        $query = $database->prepare($sql);
        $query->bindParam(':offer_id',      $offer_id,              PDO::PARAM_INT);
        if ($query->execute() && $query->rowCount() > 0) {
            return true;
        } else {
            return false;
        }
    }
}
