path: root/src/main/java/edu/brown/cs/student/term/profit/ProfitCalculation.java

package edu.brown.cs.student.term.profit;


import edu.brown.cs.student.term.profit.StockHolding;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;


import java.io.IOException;
import java.net.URI;
import java.net.http.HttpClient;
import java.net.http.HttpRequest;
import java.net.http.HttpResponse;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.LinkedList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.*;

public class ProfitCalculation {
  private Connection conn;
  private String person;
  private Date startTime;
  private Date endTime;
  private boolean tablesFilled;

  private String BASE_URL = "https://data.alpaca.markets/v1";

  private String API_KEY = "PKT53Z9QW0TMSSC9XNPQ";
  private String SECRET_KEY = "udvetWCvwyVmZgrjgCPfX3W0nprKBrbunh5wNnCv";

  //map of stock to list of buy orders, first element in list is oldest
  private Map<String, LinkedList<OrderTuple>> buyHistoryMap;

  //map of stock to list of buy orders, first element in list is oldest
  private Map<String, LinkedList<OrderTuple>> sellHistoryMap;

  //map of stock to gains from sell orders
  private Map<String, Double> realizedGainsMap;

  //map of stock to gains from increases in value of holdings
  private Map<String, Double> unrealizedGainsMap;

  //map to store current prices of stocks -- to avoid repeated api calls
  private Map<String, Double> currentStockPrices;

  private double moneyInput;

  /**
   * constructor for ProfitCalculation.
   *
   * @param conn      - database connection, with trade data.
   * @param person    - person of interest to calculate profit for.
   * @param startTime - start of period to look at.
   * @param endTime   - end of period to look at.
   */
  public ProfitCalculation(Connection conn, String person, Date startTime, Date endTime) {
    this.conn = conn;
    this.person = person;
    this.startTime = startTime;
    this.endTime = endTime;
    buyHistoryMap = new HashMap<>();
    sellHistoryMap = new HashMap<>();
    realizedGainsMap = new HashMap<>();
    unrealizedGainsMap = new HashMap<>();
    currentStockPrices = new HashMap<>();
    tablesFilled = false;
  }


  /**
   * This method fills the maps of sell and buy orders with lists of oldest - new trades.
   */

  private String validateTicker(String ticker) {
    //this is cleaning some improperly formatted tickers
    ticker = ticker.replaceAll("[^a-zA-Z0-9]", "").toUpperCase();
    if (ticker.contains("[0-9]") ||
        ticker.length() > 5 ||
        ticker.length() < 2 ||
        ticker.contains("NONE")) {
      return "";
    }

    return ticker;
  }

  private void organizeOrders(Integer id) {
    //get a list of trades for a person to consider
    try {
      PreparedStatement prep;
      if (id == -1) {
        //search by name
        prep =
            conn.prepareStatement("SELECT * FROM \'trades\' WHERE holder_name= ? "
                + " AND trade_timestamp BETWEEN ? AND ? "
                + "order by trade_timestamp asc;");
        prep.setString(1, this.person);
      } else {
        //search by id
        prep =
            conn.prepareStatement("SELECT * FROM \'trades\' WHERE holder_id = ? "
                + " AND trade_timestamp BETWEEN ? AND ? "
                + "order by trade_timestamp asc;");
        prep.setInt(1, id);
      }
      prep.setDate(2, startTime);
      prep.setDate(3, endTime);
      ResultSet rs = prep.executeQuery();

      while (rs.next()) {
        String ticker = rs.getString("stock_name");
        ticker = validateTicker(ticker);
        if (ticker.equals("")) {
          continue;
        }
        int shares = rs.getInt("number_of_shares");
        double price = rs.getDouble("share_price");
        OrderTuple order = new OrderTuple(shares, price, rs.getDate("trade_timestamp"));

        //one element list for first time ticker is seen.
        LinkedList<OrderTuple> oneElement = new LinkedList<OrderTuple>();
        oneElement.addLast(order);

        //for buy orders, build up buy history
        if (rs.getInt("is_buy") != 0) {
          moneyInput += shares * price;
          if (buyHistoryMap.containsKey(ticker)) {
            buyHistoryMap.get(ticker).addLast(order);
          } else {
            buyHistoryMap.put(ticker, oneElement);
          }
        } else {
          //ignore sell orders for which we do not have buys for
          if (sellHistoryMap.containsKey(ticker)) {
            sellHistoryMap.get(ticker).addLast(order);
          } else {
            sellHistoryMap.put(ticker, oneElement);
          }

        }

      }
      prep.close();
    } catch (SQLException e) {
      System.out.println("ERROR: sql error getting trades");
    }
  }

  /**
   * This method processes the sell orders in the sellHistoryMap to get realized gains.
   */
  private void getRealizedGains() {
    for (String ticker : sellHistoryMap.keySet()) {
      //use FIFO selling
      LinkedList<OrderTuple> sells = sellHistoryMap.get(ticker);
      LinkedList<OrderTuple> buys = buyHistoryMap.get(ticker);
      double realizedGain = 0;
      if (sells != null && buys != null) {
        //process each sell order (unless all buy orders are "drained"
        for (OrderTuple sell : sells) {
          //stop if buys are empty, stop if buy happened after sell
          if (buys.isEmpty()) {
            break;
          }

          int sharesToSell = sell.getShares();

          //sell off through list of buys
          while (sharesToSell > 0 && !buys.isEmpty()) {
            //dont sell from buys which didn't exist at the time.
            if (sell.getDate().after(buys.getFirst().getDate())
                || sell.getDate().equals(buys.getFirst().getDate())) {
              OrderTuple buyBundle = buys.removeFirst();
              int sharesAtBundlePrice;
              //the buy has more shares than we want to sell
              if (buyBundle.getShares() > sharesToSell) {
                sharesAtBundlePrice = sharesToSell;
                sharesToSell = 0;
                //add back the holdings that were not sold
                buyBundle.setShares(buyBundle.getShares() - sharesAtBundlePrice);
                buys.addFirst(buyBundle);
              } else {
                sharesToSell -= buyBundle.getShares();
                sharesAtBundlePrice = buyBundle.getShares();
              }
              realizedGain += sharesAtBundlePrice * (sell.getCost() - buyBundle.getCost());
            } else {
              break;
            }


          }
        }
      }

      realizedGainsMap.put(ticker, realizedGain);
    }
  }

  /**
   * get the change in value of stocks which are still held.
   */
  private void getUnrealizedGains() {

    //calculate change in value of holdings
    for (String ticker : buyHistoryMap.keySet()) {
      double unrealizedGains = 0;

      double currentPrice = getCurrentPrice(ticker);
      if (currentPrice != -1) {
        LinkedList<OrderTuple> stockHistory = buyHistoryMap.get(ticker);
        for (OrderTuple order : stockHistory) {
          unrealizedGains += order.getShares() * (currentPrice - order.getCost());
        }
      }
      unrealizedGainsMap.put(ticker, unrealizedGains);
    }
  }

  private final class OrderTuple {
    private int shares;
    private double cost;
    private Date date;

    private OrderTuple(int shares, double cost, Date date) {
      this.shares = shares;
      this.cost = cost;
      this.date = date;
    }

    public double getCost() {
      return cost;
    }

    public int getShares() {
      return shares;
    }

    public Date getDate() {
      return date;
    }

    public void setShares(int shares) {
      this.shares = shares;
    }
  }

  private double getCurrentPrice(String ticker) {
    if (currentStockPrices.containsKey(ticker)) {
      return currentStockPrices.get(ticker);
    } else {
      SimpleDateFormat localDateFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSXXX");
      String url = "https://data.alpaca.markets/v1/bars/"
          + "day?"
          + "symbols=" + ticker
          + "&start=" + localDateFormat.format(startTime)
          + "&end=" + localDateFormat.format(endTime);

      HttpClient client = HttpClient.newHttpClient();
      HttpRequest request = HttpRequest.newBuilder()
          .uri(URI.create(url)).setHeader("APCA-API-KEY-ID", API_KEY)
          .setHeader("APCA-API-SECRET-KEY", SECRET_KEY)
          .build();

      HttpResponse<String> response = null;
      try {
        response = client.send(request,
            HttpResponse.BodyHandlers.ofString());
      } catch (Exception e) {
        System.out.println("ERROR: error getting price for profit calculation");
      }

      JSONArray object = new JSONObject(response.body()).getJSONArray(ticker);
      try {
        double endPrice = object.getJSONObject(object.length() - 1).getDouble("c");
        currentStockPrices.put(ticker, endPrice);
        return endPrice;
      } catch (JSONException e) {
        currentStockPrices.put(ticker, -1.0);
        return -1.0;
      }
    }


  }

  public double calculateGainsSingle(Integer id) {

    if (!tablesFilled) {
      organizeOrders(id);
      getRealizedGains();
      getUnrealizedGains();
      tablesFilled = true;
    }


    double gains = 0;

    for (double value : realizedGainsMap.values()) {
      gains += value;
    }

    for (double value: unrealizedGainsMap.values()) {
      gains += value;
    }

    return gains;
  }

  public List<StockHolding> getHoldingsList(Integer id) {
    if (conn == null) {
      System.out.println("ERROR: No database connection");
      return new LinkedList<>();
    }

    if (!tablesFilled) {
      organizeOrders(id);
      getRealizedGains();
      getUnrealizedGains();
      tablesFilled = true;
    }

    List<StockHolding> holdings = new LinkedList<>();

    for (String key : buyHistoryMap.keySet()) {
      double realizedGains = 0;
      double unrealizedGains = 0;
      if (unrealizedGainsMap.containsKey(key)) {
        unrealizedGains = unrealizedGainsMap.get(key);
      }
      if (realizedGainsMap.containsKey(key)) {
        realizedGains = realizedGainsMap.get(key);
      }

      int shares = 0;
      for (OrderTuple order : buyHistoryMap.get(key)) {
        shares += order.getShares();
      }
      holdings.add(new StockHolding(key, realizedGains, unrealizedGains, shares));
    }
    return holdings;
  }

  /**
   * return percent change in SPY (SP 500) over the time period.
   */
  public double compareToSP500() {
    SimpleDateFormat localDateFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSXXX");
    String url = "https://data.alpaca.markets/v1/bars/"
        + "day?"
        + "symbols=SPY"
        + "&start=" + localDateFormat.format(startTime)
        + "&end=" + localDateFormat.format(endTime);

    HttpClient client = HttpClient.newHttpClient();
    HttpRequest request = HttpRequest.newBuilder()
        .uri(URI.create(url)).setHeader("APCA-API-KEY-ID", API_KEY)
        .setHeader("APCA-API-SECRET-KEY", SECRET_KEY)
        .build();

    HttpResponse<String> response = null;
    try {
      response = client.send(request,
          HttpResponse.BodyHandlers.ofString());
    } catch (IOException e) {
      e.printStackTrace();
    } catch (InterruptedException e) {
      e.printStackTrace();
    }

    JSONArray object = new JSONObject(response.body()).getJSONArray("SPY");

    //get close price of start SPY at start time
    double startPrice = object.getJSONObject(0).getDouble("c");
    double endPrice = object.getJSONObject(object.length() - 1).getDouble("c");
    //get percent change
    //end - start /start
    return ((endPrice - startPrice) / startPrice);

  }

  /**
   * get a map for all people in the timeframe of holder_id to percent gain.
   *
   * @return a map of holder_id to percent gain
   */
  public Map<Integer, Double> getProfitMap() {
    Map<Integer, Double> profitMap = new HashMap<>();
    if (conn == null) {
      System.out.println("ERROR: no database connection");
      return profitMap;
    }
    try {
      PreparedStatement prep;
      long START = System.currentTimeMillis();
      prep =
          conn.prepareStatement(
              "SELECT * From trades GROUP BY holder_id having max(is_buy) = 1;");
      ResultSet rs = prep.executeQuery();

      long QUERY = System.currentTimeMillis();
      //System.out.println((QUERY - START) + " query time");

      //set of all people who have made both buy and sell orders
      Set<Integer> people = new HashSet<>();

      while (rs.next()) {
//        int id = rs.getInt("holder_id");
//        this.person = rs.getString("holder_name");
//        resetClass();
//
//
//
//        double gain = this.calculateGains();
//        if (moneyInput == 0) {
//          profitMap.put(id, 0.0);
//        } else {
//          profitMap.put(id, gain / moneyInput);
//        }
        people.add(rs.getInt("holder_id"));

      }

      profitMap = calculateGains(people);

      long LOOP = System.currentTimeMillis();
      //System.out.println((LOOP - QUERY) + " loop");

    } catch (SQLException throwables) {
      System.out.println("ERROR: SQl error in profit calculation");
    }
    return profitMap;
  }

  private Map<Integer, Double> calculateGains(Set<Integer> people) {
    Map<Integer, Double> gainsMap = new HashMap<>();

    //map of stock to list of buy orders, first element in list is oldest
    Map<Integer, Map<String, LinkedList<OrderTuple>>> sellMap = new HashMap<>();
    //map of stock to list of buy orders, first element in list is oldest
    Map<Integer, Map<String, LinkedList<OrderTuple>>> buyMap = new HashMap<>();
    //money input
    Map<Integer, Double> moneyInMap = new HashMap<>();


    try {
      PreparedStatement prep;
      prep =
          conn.prepareStatement("SELECT * FROM \'trades\'"
              + " WHERE NOT number_of_shares = 0 AND trade_timestamp BETWEEN ? AND ? "
              + "order by trade_timestamp asc;");
      prep.setDate(1, startTime);
      prep.setDate(2, endTime);
      ResultSet rs = prep.executeQuery();
      while (rs.next()) {
        if (people.contains(rs.getInt("holder_id"))) {
          String ticker = rs.getString("stock_name");
          ticker = validateTicker(ticker);
          if (ticker.equals("")) {
            continue;
          }
          int shares = rs.getInt("number_of_shares");
          double price = rs.getDouble("share_price");
          int holder_id = rs.getInt("holder_id");
          if (!buyMap.containsKey(holder_id)) {
            buyMap.put(holder_id, new HashMap<>());
          }
          if (!sellMap.containsKey(holder_id)) {
            sellMap.put(holder_id, new HashMap<>());
          }


          OrderTuple order = new OrderTuple(shares, price, rs.getDate("trade_timestamp"));

          //one element list for first time ticker is seen.
          LinkedList<OrderTuple> oneElement = new LinkedList<OrderTuple>();
          oneElement.addLast(order);

          //for buy orders, build up buy history
          if (rs.getInt("is_buy") != 0) {

            if (moneyInMap.containsKey(holder_id)) {
              moneyInMap.put(holder_id, moneyInMap.get(holder_id) + shares * price);
            } else {
              moneyInMap.put(holder_id, shares * price);
            }


            if (buyMap.get(holder_id).containsKey(ticker)) {
              buyMap.get(holder_id).get(ticker).addLast(order);
            } else {
              buyMap.get(holder_id).put(ticker, oneElement);
            }
          } else {
            //ignore sell orders for which we do not have buys for
            if (sellMap.get(holder_id).containsKey(ticker)) {
              sellMap.get(holder_id).get(ticker).addLast(order);
            } else {
              sellMap.get(holder_id).put(ticker, oneElement);
            }

          }
        }
      }
    } catch (SQLException e) {
      System.out.println("ERROR: sql error getting trades");
    }


    //part 2 doing math...
    for (Integer person : people) {
      this.buyHistoryMap = buyMap.get(person);
      this.sellHistoryMap = sellMap.get(person);
      if (sellHistoryMap == null) {
        continue;
      }

      for (String ticker : sellHistoryMap.keySet()) {
        //use FIFO selling
        LinkedList<OrderTuple> sells = sellHistoryMap.get(ticker);
        LinkedList<OrderTuple> buys = buyHistoryMap.get(ticker);
        double realizedGain = 0;
        if (sells != null && buys != null) {
          //process each sell order (unless all buy orders are "drained"
          for (OrderTuple sell : sells) {
            //stop if buys are empty, stop if buy happened after sell
            if (buys.isEmpty()) {
              break;
            }

            int sharesToSell = sell.getShares();

            //sell off through list of buys
            while (sharesToSell > 0 && !buys.isEmpty()) {
              //dont sell from buys which didn't exist at the time.
              if (sell.getDate().after(buys.getFirst().getDate())
                  || sell.getDate().equals(buys.getFirst().getDate())) {
                OrderTuple buyBundle = buys.removeFirst();
                int sharesAtBundlePrice;
                //the buy has more shares than we want to sell
                if (buyBundle.getShares() > sharesToSell) {
                  sharesAtBundlePrice = sharesToSell;
                  sharesToSell = 0;
                  //add back the holdings that were not sold
                  buyBundle.setShares(buyBundle.getShares() - sharesAtBundlePrice);
                  buys.addFirst(buyBundle);
                } else {
                  sharesToSell -= buyBundle.getShares();
                  sharesAtBundlePrice = buyBundle.getShares();
                }
                realizedGain += sharesAtBundlePrice * (sell.getCost() - buyBundle.getCost());
              } else {
                break;
              }


            }
          }
        }

        if (gainsMap.containsKey(person)) {
          gainsMap.put(person, gainsMap.get(person) + realizedGain);
        } else {
          gainsMap.put(person, realizedGain);
        }

      }

      //percent gain
      if (gainsMap.containsKey(person) && moneyInMap.containsKey(person)) {
        gainsMap.put(person, gainsMap.get(person) / moneyInMap.get(person));
      } else {
        gainsMap.put(person, 0.0);
      }


    }

    return gainsMap;
  }

  public double getMoneyInput() {
    return this.moneyInput;
  }

  private void resetClass() {
    tablesFilled = false;
    moneyInput = 0;
    buyHistoryMap = new HashMap<>();
    sellHistoryMap = new HashMap<>();
    realizedGainsMap = new HashMap<>();
    unrealizedGainsMap = new HashMap<>();
    tablesFilled = false;
  }

}