/* This file is part of VoltDB.
 * Copyright (C) 2008-2017 VoltDB Inc.
 *
 * Permission is hereby granted, free of charge, to any person obtaining
 * a copy of this software and associated documentation files (the
 * "Software"), to deal in the Software without restriction, including
 * without limitation the rights to use, copy, modify, merge, publish,
 * distribute, sublicense, and/or sell copies of the Software, and to
 * permit persons to whom the Software is furnished to do so, subject to
 * the following conditions:
 *
 * The above copyright notice and this permission notice shall be
 * included in all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
 * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
 * MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
 * IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY CLAIM, DAMAGES OR
 * OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,
 * ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
 * OTHER DEALINGS IN THE SOFTWARE.
 */

package geospatial;

import org.voltdb.SQLStmt;
import org.voltdb.VoltProcedure;
import org.voltdb.VoltTable;

/**
 * Get statistics on ad requests for the most recent time period
 * in seconds, specified by the caller.
 *
 * These SQL statements make use of a materialized view so they
 * can execute data quickly.
 */
public class GetStats extends VoltProcedure {

    /*
     * Find out the ratio of successful bids to unsuccessful ones.
     */
    final SQLStmt selectHitsAndNonHits = new SQLStmt(
            "select "
            + "  case when advertiser_id is not null "
            + "    then 1 else 0 end as hit_or_no_hit, "
            + "  sum(request_count) "
            + "from requests_by_second_by_advertiser "
            + "where ts_second > dateadd(second, ?, current_timestamp) "
            + "group by hit_or_no_hit "
            + "order by hit_or_no_hit");

    /*
     * Find the top 5 advertisers of the past <n> seconds, ordered
     * by the sum of all the winning bids.
     */
    final SQLStmt selectRecentAdvertisers = new SQLStmt(
            "select "
            + "  adv.name as advertiser, "
            + "  sum(vw.sum_of_ad_revenue) total_revenue, "
            + "  sum(vw.request_count) as num_requests "
            + "from requests_by_second_by_advertiser as vw "
            + "inner join advertisers as adv "
            + "on vw.advertiser_id = adv.id "
            + "where ts_second > dateadd(second, ?, current_timestamp) "
            + "group by adv.name "
            + "order by total_revenue desc "
            + "limit 5");

    public VoltTable[] run(long numSeconds) {
        voltQueueSQL(selectHitsAndNonHits, -numSeconds);
        voltQueueSQL(selectRecentAdvertisers, -numSeconds);
        return voltExecuteSQL(true);
    }
}