/* 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 metrocard;

import org.voltdb.SQLStmt;
import org.voltdb.VoltProcedure;
import org.voltdb.VoltTable;
import org.voltdb.VoltType;
import org.voltdb.types.TimestampType;

public class CardSwipe extends VoltProcedure {

    public final SQLStmt checkCard = new SQLStmt(
        "SELECT enabled, card_type, balance, expires, name, phone, email, notify FROM cards WHERE card_id = ?;");

    public final SQLStmt chargeCard = new SQLStmt(
        "UPDATE cards SET balance = ? WHERE card_id = ?;");

    public final SQLStmt checkStationFare = new SQLStmt(
        "SELECT fare, name FROM stations WHERE station_id = ?;");

    public final SQLStmt insertActivity = new SQLStmt(
        "INSERT INTO activity (card_id, date_time, station_id, activity_code, amount) VALUES (?,?,?,?,?);");

    public final SQLStmt exportActivity = new SQLStmt(
        "INSERT INTO card_alert_export (card_id, export_time, station_name, name, phone, email, notify, alert_message) VALUES (?,?,?,?,?,?,?,?);");

    // for returning results as a VoltTable
    final VoltTable resultTemplate = new VoltTable(
            new VoltTable.ColumnInfo("card_accepted",VoltType.TINYINT),
            new VoltTable.ColumnInfo("message",VoltType.STRING));

    public VoltTable buildResult(int accepted, String msg) {
        VoltTable r = resultTemplate.clone(64);
        r.addRow(accepted, msg);
        return r;
    }

    public static String intToCurrency(int i) {
        return String.format("%d.%02d", i/100, i%100);
    }

    public VoltTable run(int cardId, int stationId) throws VoltAbortException {

        // check station fare, card status, get card owner's particulars
        voltQueueSQL(checkCard, EXPECT_ZERO_OR_ONE_ROW, cardId);
        voltQueueSQL(checkStationFare, EXPECT_ONE_ROW, stationId);
        VoltTable[] checks = voltExecuteSQL();
        VoltTable cardInfo = checks[0];
        VoltTable stationInfo = checks[1];

        // check that card exists
        if (cardInfo.getRowCount() == 0) {
            return buildResult(0,"Card Invalid");
        }

        // card exists, so advanceRow to read the record
        cardInfo.advanceRow();
        int enabled = (int)cardInfo.getLong(0);
        int cardType = (int)cardInfo.getLong(1);
        int balance = (int)cardInfo.getLong(2);
        TimestampType expires = cardInfo.getTimestampAsTimestamp(3);
        String owner = cardInfo.getString(4);
        String phone = cardInfo.getString(5);
        String email = cardInfo.getString(6);
        int notify = (int)cardInfo.getLong(7);

        // read station fare
        stationInfo.advanceRow();
        int fare = (int)stationInfo.getLong(0);
        String stationName = stationInfo.getString(1);

        // if card is disabled
        if (enabled == 0) {
                return buildResult(0,"Card Disabled");
        }

        // check balance or expiration for valid cards
        if (cardType == 0) { // pay per ride
                if (balance > fare) {
                        // charge the fare
                        voltQueueSQL(chargeCard, balance-fare,cardId);
                        voltQueueSQL(insertActivity, cardId, getTransactionTime(), stationId, 1, fare);
                        voltExecuteSQL(true);
                        return buildResult(1,"Remaining Balance: "+intToCurrency(balance-fare));
                } else {
                        // insufficient balance
                        voltQueueSQL(insertActivity, cardId, getTransactionTime(), stationId, 0, 0);
                        if (notify != 0) {  // only export if notify is 1 or 2 -- email or text
                            voltQueueSQL(exportActivity, cardId, getTransactionTime().getTime(), stationName, owner, phone, email, notify, "Insufficient Balance");
                        }
                        voltExecuteSQL(true);
                        return buildResult(0,"Card has insufficient balance: "+intToCurrency(balance));
                }
        }
        else { // unlimited card (e.g. monthly or weekly pass)
                if (expires.compareTo(new TimestampType(getTransactionTime())) > 0) {
                        voltQueueSQL(insertActivity, cardId, getTransactionTime(), stationId, 1, 0);
                        voltExecuteSQL(true);
                        return buildResult(1,"Card Expires: " + expires.toString());
                } else {
                        voltQueueSQL(insertActivity, cardId, getTransactionTime(), stationId, 0, 0);
                        voltExecuteSQL(true);
                        return buildResult(0,"Card Expired");
                }
        }
    }

}