package edu.brown.cs.student.term;

import edu.brown.cs.student.term.hub.Holder;
import edu.brown.cs.student.term.profit.ProfitCalculation;
import edu.brown.cs.student.term.trade.Trade;

import java.sql.*;
import java.time.Instant;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

public class DatabaseQuerier {
  private static Connection conn = null;

  //TODO: Be prepared to overhaul this to account for IDs

  /**
   * Makes a database querier for a particular sqlite database
   *
   * @param filename - String representing filepath of database
   * @throws SQLException
   * @throws ClassNotFoundException
   */
  public DatabaseQuerier(String filename) throws SQLException,
      ClassNotFoundException {
    Class.forName("org.sqlite.JDBC");
    String urlToDB = "jdbc:sqlite:" + filename;
    // AutoClosable TRY-WITH-RESOURCES ensures database connection will be closed when it is done
    conn = DriverManager.getConnection(urlToDB);
  }

  public static Connection getConn() {
    return conn;
  }

  /**
   * Gets the names of all stocks traded between start and end ddate
   *
   * @param startDate - the start date
   * @param endDate   - the end date
   * @return a list of stock names
   * @throws SQLException
   */
  public List<String> getRecentStocks(Instant startDate, Instant endDate) throws SQLException {
    List<String> stocks = new ArrayList<>();

    PreparedStatement prep = conn.prepareStatement(
        "SELECT DISTINCT stock_name FROM trades WHERE trade_timestamp <= ? AND trade_timestamp >= ?");

    prep.setLong(1, endDate.toEpochMilli());
    prep.setLong(2, startDate.toEpochMilli());
    ResultSet rs = prep.executeQuery();

    while (rs.next()) {
      String ticker = validateTicker(rs.getString(1));
      if (!ticker.equals("")) {
        stocks.add(ticker);
      }
    }

    rs.close();
    prep.close();
    return stocks;
  }

  /**
   * Gets all the trades in by stock and buy type, ordered by time
   *
   * @param startDate - the start date of these trades
   * @param endDate   - the end date of these trades
   * @return a list of list of trades as specified above
   * @throws SQLException - if something goes wrong with connection
   */
  public List<List<Trade>> getAllTradesByStock(Instant startDate, Instant endDate)
      throws SQLException {
    List<List<Trade>> allTrades = new ArrayList<>();
    List<String> stocks = getRecentStocks(startDate, endDate);
    //get the buys and sells for each stock
    for (String stock : stocks) {
      allTrades.add(getTradeByStock(stock, 1, startDate, endDate));
      allTrades.add(getTradeByStock(stock, 0, startDate, endDate));
    }
    return allTrades;
  }

  /**
   * Gets a single stock's list of trades for that time period (either buy or sell)
   *
   * @param stock     - string name of the stock to get the trades for
   * @param isBuy     - integer whether it's a buy or sell
   * @param startDate - an Instant representing the start of the time period
   * @param endDate   - an Instant representing the end of the time period
   * @return - a list of trades for that stock
   * @throws SQLException - if issue getting connection
   */
  public List<Trade> getTradeByStock(String stock, int isBuy, Instant startDate, Instant endDate)
      throws SQLException {
    List<Trade> trades = new ArrayList<>();
    /*
    if (isValidStock(stock)) {
      return trades;
    }*/

    PreparedStatement prep = conn.prepareStatement(
        "SELECT * FROM trades WHERE (stock_name = ? AND is_buy = ?) "
            + "AND (trade_timestamp <= ? AND trade_timestamp >= ?) ORDER BY trade_timestamp");

    prep.setString(1, stock);
    prep.setInt(2, isBuy);
    prep.setLong(3, endDate.toEpochMilli());
    prep.setLong(4, startDate.toEpochMilli());
    ResultSet rs = prep.executeQuery();

    while (rs.next()) {
      trades.add(new Trade(rs.getInt(1), rs.getString(2),
          rs.getLong(4), rs.getInt(5),
          rs.getInt(6), new Holder(rs.getInt(7), rs.getString(3)),
          rs.getDouble(8)));
    }

    rs.close();
    prep.close();

    return trades;
  }

  public List<Trade> getAllTradesByHolder(Integer holder_id, Date startDate, Date endDate) {
    LinkedList<Trade> trades = new LinkedList<>();

    try {
      PreparedStatement prep;
      prep =
          conn.prepareStatement("SELECT * FROM \'trades\' WHERE holder_id = ?"
              + " AND trade_timestamp BETWEEN ? AND ?"
              + "order by trade_timestamp asc;");
      prep.setInt(1, holder_id);
      prep.setDate(2, startDate);
      prep.setDate(3, endDate);
      ResultSet rs = prep.executeQuery();

      while (rs.next()) {
        String ticker = rs.getString("stock_name");
        //if (isValidTicker(ticker)) {
          trades.addFirst(new Trade(rs.getInt("trade_id"), ticker,
              rs.getDouble("trade_timestamp"),
              rs.getInt("is_buy"),
              rs.getInt("number_of_shares"),
              new Holder(rs.getInt("holder_id"), rs.getString("holder_name")),
              rs.getDouble("share_price")));
        //}
      }
      prep.close();
    } catch (SQLException e) {
      System.out.println("ERROR: sql error getting trades");
    }
    return 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;
  }
}