import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;

import java.io.IOException;
import java.sql.*;
import java.text.NumberFormat;
import java.text.ParseException;
import java.util.HashSet;
import java.util.Set;

public class BestBuy_PopulateProducts {

  public static void main(String[] args) throws IOException, ParseException, SQLException {

    // fetch data from Best Buy
    final String TABLET_CATEGORY = "tablet";
    final String DESKTOP_CATEGORY = "desktop";
    final String LAPTOP_CATEGORY = "laptop";
    Product[] tabletsList = getProductsByCategory(TABLET_CATEGORY);
    Product[] desktopsList = getProductsByCategory(DESKTOP_CATEGORY);
    Product[] laptopsList = getProductsByCategory(LAPTOP_CATEGORY);
    logProducts(tabletsList, desktopsList, laptopsList);

    // insert into tables
    Connection connection = null;
    Statement statement = null;
    try {
      connection = initializeConnection();
      statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

      // do not insert if `products` not empty
      if (isProductTableEmpty(statement)) {
        // insert into `company`
        insertIntoCompanyTable(tabletsList, statement);
        insertIntoCompanyTable(desktopsList, statement);
        insertIntoCompanyTable(laptopsList, statement);

        // insert into `product`
        insertIntoProduct(tabletsList, statement);
        insertIntoProduct(desktopsList, statement);
        insertIntoProduct(laptopsList, statement);
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      if (statement != null) statement.close();
      if (connection != null) connection.close();
    }
  }

  public static boolean isProductTableEmpty(Statement statement) throws SQLException {
    final String TABLENAME = "product";
    String selectQuery = "SELECT COUNT(*) AS total FROM " + TABLENAME + ";";
    ResultSet resultSet = getSelection(selectQuery, statement);

    int count = 0;
    if (resultSet.next())
      count = resultSet.getInt("total");
    return count == 0;
  }

  public static void insertIntoProduct(Product[] productsList, Statement statement) {
    final String TABLENAME = "product";
    for (Product product: productsList) {
      String insertQuery = "INSERT INTO " + TABLENAME +
              " (product_ID, product_name, price, description, category, discount, cname) VALUES(" +
              "\'" + product.product_ID + "\', " +
              "\'" + product.product_name + "\', " +
              product.price + ", " +
              "\'" + product.description + "\', " +
              "\'" + product.category + "\', " +
              product.discount + ", " +
              "\'" + product.cname + "\') ON CONFLICT DO NOTHING;";
      runQuery(insertQuery, statement);
    }
  }

  public static Set insertIntoCompanyTable(Product[] productsList, Statement statement) {
    final String TABLENAME = "company";
    Set<String> companyNames = new HashSet<String>();
    for (Product product: productsList)
      companyNames.add(product.cname);
    for (String company: companyNames) {
      String insertQuery = "INSERT INTO " + TABLENAME + " (cname) VALUES(\'" + company + "\') ON CONFLICT DO NOTHING;";
      runQuery(insertQuery, statement);
    }
    return companyNames;
  }

  /**
   * Use this for SELECTION queries
   */
  public static ResultSet getSelection(String sqlSelect, Statement statement) {
    ResultSet resultSet = null;
    try {
      log(sqlSelect);
      resultSet = statement.executeQuery(sqlSelect);
    } catch (SQLException e) {
      logSqlException(e);
    }
    return resultSet;
  }

  public static void runQueries(String[] sqlQueries, Statement statement) {
    for (String sqlQuery: sqlQueries)
      runQuery(sqlQuery, statement);
  }

  /**
   * Run SQL query, return true if successful
   */
  public static boolean runQuery(String sqlQuery, Statement statement) {
    try {
      log(sqlQuery);
      statement.executeQuery(sqlQuery);
    } catch (SQLException e) {
      logSqlException(e);
      return false;
    }
    return true;
  }

  /**
   * Register & return the connection
   * @return
   */
  public static Connection initializeConnection() {
    final String DATABASE_URL = System.getenv("COMP421_URL");
    final String DATABASE_USER = System.getenv("COMP421_USER");
    final String DATABASE_PASS = System.getenv("COMP421_PASS");
    Connection connection = null;

    try {
      DriverManager.registerDriver(new org.postgresql.Driver());
      connection = DriverManager.getConnection(
              DATABASE_URL,
              DATABASE_USER,
              DATABASE_PASS
      );
      log("CONNECTION SUCCESSFUL");
    } catch (Exception e) {
      e.printStackTrace();
      log("TERMINATING PROGRAM");
      System.exit(0);
    }
    return connection;
  }

  /**
   * Given a category:
   *  - tablet / desktop / laptop
   *
   * Fetch from the BestBuy website:
   *  - 1st page of products
   */
  public static Product[] getProductsByCategory(String category) throws IOException, ParseException {
    Document document = getProductsPageByCategory(category);
    Elements LISTING_ITEMS = document.getElementsByClass("listing-item");

    // get category - from arguments
    int i = 0, limit = 20;
    Product[] productsList = new Product[limit];          // limit to 20 for now - do not overpopulate relation
    for (Element item: LISTING_ITEMS) {
      double price = getPriceFromItem(item);
      String productName = getProductNameFromItem(item);
      String companyName = productName.split(" ", 2)[0];  // get company name - from product name
      int sku = getSkuFromItem(item);                     // get sku (id)
      double discount = getDiscountFromElement(item);

      if (!validateProduct(price, companyName, sku)) continue;

      // description - there is no description to fetch from the dom
      // for now, just mention if its on sale or not
      String description = (discount > 0.0) ? "On sale!" : "";

      productsList[i] = new Product(sku, productName, price, description, category, companyName, discount);
      if (++i == limit) break;
    }
    return productsList;
  }

  public static boolean validateProduct(double price, String companyName, int sku) {
    return price > 0.0 && !companyName.toLowerCase().equals("refurbished") && sku > 0;
  }

  public static double getDiscountFromElement(Element item) throws ParseException {
    double discount = 0.0;
    if (item != null) {
      Element discountWrapperElement = item.getElementsByClass("prod-saving").first(); // todo: null check first()
      if (discountWrapperElement != null) {
        Element discountElement = discountWrapperElement.select("span").last(); // todo: null check last()
        if (discountElement != null) {
          String discountS = discountElement.text();
          if (discountS != null) {
            NumberFormat format = NumberFormat.getCurrencyInstance();
            Number number = format.parse(discountS);
            discount = number.doubleValue();
          }
        }
      }
    }
    return discount;
  }

  public static int getSkuFromItem(Element item) {
    int sku = 0;
    if (item != null) {
      Element skuWrapperElement = item.getElementsByClass("compare-checkbox").first(); // todo: null check first()
      if (skuWrapperElement != null) {
        Element skuInputElement = skuWrapperElement.select("input").first(); // todo: null check first()
        if (skuInputElement != null) {
          String skuS = skuInputElement.attr("data-sku");
          if (skuS != null) {
            sku = Integer.parseInt(skuS);
          }
        }
      }
    }
    return sku;
  }

  public static String getProductNameFromItem(Element item) {
    String productName = "";
    if (item != null) {
      Element productNameWrapperElement = item.getElementsByClass("prod-title").first(); // todo: null check first()
      if (productNameWrapperElement != null) {
        Element productNameElement = productNameWrapperElement.select("a").first(); // todo: null check first()
        if (productNameElement != null) {
          productName = productNameElement.text();
          productName = productName.length() >= 70 ? productName.substring(0, 66) + "..." : productName;
        }
      }
    }
    return productName;
  }

  public static double getPriceFromItem(Element item) throws ParseException {
    double priceD = 0.0;
    if (item != null) {
      Element priceElement = item.getElementsByClass("amount").first(); // todo: null check first()
      if (priceElement != null) {
        String price = priceElement.text();
        if (price != null) {
          NumberFormat format = NumberFormat.getCurrencyInstance();
          Number number = format.parse(price);
          priceD = number.doubleValue();
        }
      }
    }
    return priceD;
  }

  /**
   * Unused - use if we want to display images on the GUI
   */
  public static String getImageFromItem(Element item) {
    String image = "";
    if (item != null) {
      Element thumbnailElement = item.select("img").first(); // todo: null check first()
      if (thumbnailElement != null) {
        image = thumbnailElement.absUrl("src");
      }
    }
    return image;
  }

  public static Document getProductsPageByCategory(String category) throws IOException {
    final String BASE_URL = "https://www.bestbuy.ca/en-ca/category/";
    final String productsPageUrl = BASE_URL + getCategoryUrl(category);
    log("URL: " + productsPageUrl);
    return Jsoup.connect(productsPageUrl).get();
  }

  /**
   * tablets:     https://www.bestbuy.ca/en-ca/category/tablets-ipads/30297.aspx?
   * desktops:    https://www.bestbuy.ca/en-ca/category/desktop-computers/20213.aspx?
   * laptops:     https://www.bestbuy.ca/en-ca/category/laptops-macbooks/20352.aspx?
   */
  public static String getCategoryUrl(String category) {
    String categoryUrl = "";
    switch (category) {
      case "tablet":
        categoryUrl = "tablets-ipads/30297.aspx?";
        break;
      case "desktop":
        categoryUrl = "desktop-computers/20213.aspx?";
        break;
      case "laptop":
        categoryUrl = "laptops-macbooks/20352.aspx?";
        break;
    }
    return categoryUrl;
  }

  public static void logProducts(Product[] tabletsList, Product[] desktopsList, Product[] laptopsList) {
    log("TABLETS: ");
    for (Product tablet: tabletsList)
      log(tablet.toString());

    log("DESKTOPS: ");
    for (Product desktop: desktopsList)
      log(desktop.toString());

    log("LAPTOPS: ");
    for (Product laptop: laptopsList)
      log(laptop.toString());
  }

  /**
   * Print resultSet
   */
  public static void logResultSet(ResultSet resultSet) {
    if (resultSet == null) return;
    try {
      ResultSetMetaData RSMD = resultSet.getMetaData();
      final int NUMBER_COLUMNS = RSMD.getColumnCount();
      for (int i = 1; i <= NUMBER_COLUMNS; i++)
        System.out.print(RSMD.getColumnName(i) + "\t\t");

      while (resultSet.next()) {
        log();
        for (int i = 1; i <= NUMBER_COLUMNS; i++)
          System.out.print(resultSet.getString(i) + "\t\t");
      }
      log();
    } catch (SQLException e) {
      logSqlException(e);
    }
  }

  public static void logSqlException(SQLException e) {
    if (!ignoreSqlWarnings(e)) {
      log("ERROR CODE:"); log(e.getErrorCode());
      log("SQL STATE:"); log(e.getSQLState());
    }
  }

  public static boolean ignoreSqlWarnings(SQLException e) {
    switch (e.getSQLState()) {
      // no data
      case "02000":
      case "02001":
        return true;
    }
    return false;
  }

  public static void log() { System.out.println(); }
  public static void log(String s) { System.out.println(s); }
  public static void log(int n) { System.out.println(n); }

}

class Product {
  public int product_ID;
  public String product_name;
  public double price;
  public double discount;
  public String description;  // similar to toString()
  public String category;
  public String cname;        // company name

  public Product(int product_ID, String product_name,
                 double price, String description,
                 String category, String cname, double discount) {
    this.product_ID = product_ID;
    this.product_name = product_name;
    this.price = price;
    this.description = description;
    this.category = category;
    this.cname = cname;
    this.discount = discount;
  }

  @Override
  public String toString() { return "\tPRODUCT: " + product_name + " COSTS $" + price; }
}