import com.mysql.cj.jdbc.util.BaseBugReport;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.Types;
import java.util.Properties;
import java.util.ArrayList;
import java.util.List;

public class CallableStatementBugReport extends BaseBugReport {
  private static final String DB_URL = "jdbc:mysql://localhost:3306/tmp?useSSL=false";
  private static final String DB_USERNAME = "user";
  private static final String DB_PASSWORD = "password";
  private static final String ROUTINE_NAME = "get_setting";
  private static final String TABLE_NAME = "TMP_SYSTEM_SETTINGS";
  private static final String CHARSET = "utf8mb4";
  private static final String COLLATE = "utf8mb4_unicode_ci";
  private Connection connection = null;

  public Connection getCon() throws Exception {
    Properties props = new Properties();
    props.setProperty("user", DB_USERNAME);
    props.setProperty("password", DB_PASSWORD);
    Connection connection = getConnection(DB_URL, props);
    return connection;
  }

  @Override
  public void setUp() throws Exception {
    String sql = "";
    try (Statement statement = getCon().createStatement()) {

      statement.execute("DROP TABLE IF EXISTS " + TABLE_NAME);
      sql =
        "CREATE TABLE " + TABLE_NAME + "(" +
        "  system_setting_uid int NOT NULL AUTO_INCREMENT," +
        "  setting varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL," +
        "  value varchar(2000) COLLATE utf8mb4_unicode_ci NOT NULL," +
        "  created_date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP," +
        "  updated_date datetime DEFAULT NULL," +
        "  PRIMARY KEY (system_setting_uid)," +
        "  UNIQUE KEY setting (setting)" +
        ") ENGINE=InnoDB DEFAULT CHARSET=" + CHARSET + " COLLATE=" + COLLATE;
      statement.execute(sql);
      
      sql = "INSERT INTO " + TABLE_NAME + "(setting, value) VALUES('SETTING_1', '100'),('SETTING_2', '200'),('SETTING_3', '300')";
      statement.execute(sql);

      statement.execute("DROP FUNCTION IF EXISTS " + ROUTINE_NAME);
      sql =
        "CREATE FUNCTION " + ROUTINE_NAME + "(p_setting VARCHAR(32)) RETURNS VARCHAR(2000) CHARSET " + CHARSET + " COLLATE " + COLLATE + " " +
        "BEGIN"+
        "  DECLARE v_value VARCHAR(2000);" +
        "  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_value = 'N/A';" +
        "  SELECT value INTO v_value FROM " + TABLE_NAME + " WHERE setting = p_setting;" +
        "  RETURN v_value;" +
        "END";
      statement.execute(sql);

      statement.execute("DROP PROCEDURE IF EXISTS " + ROUTINE_NAME);
      sql =
        "CREATE PROCEDURE " + ROUTINE_NAME + "(IN p_setting VARCHAR(200), OUT message_out VARCHAR(200)) " +
        "BEGIN" +
        "  SELECT setting, value, message_out FROM " + TABLE_NAME + " WHERE setting = p_setting;" +
        "END";
      statement.execute(sql);
    }
  }

  @Override
  public void tearDown() throws Exception {
    try (Statement statement = getCon().createStatement()) {
      statement.execute("DROP FUNCTION IF EXISTS " + ROUTINE_NAME);
      statement.execute("DROP PROCEDURE IF EXISTS " + ROUTINE_NAME);
      statement.execute("DROP TABLE IF EXISTS " + TABLE_NAME);
      getCon().close();
    }
  }

  @Override
  public void runTest() throws Exception {
    CallableStatement cs = null;
    try {
      List<String> values = new ArrayList<String>() {
        private static final long serialVersionUID = 1L;
      {
        add("SETTING_1");
        add("SETTING_2");
        add("SETTING_3");
        add("NON_EXISTENT_SETTING");
      }};
      
      for(String val : values) {
        cs = getCon().prepareCall("{call " + ROUTINE_NAME +  "(?, ?)}");
        cs.setString(1, val);
        cs.registerOutParameter(2, Types.VARCHAR);
        
        cs.execute();
        String message = cs.getString(2);
        if(message != null) {
          System.out.printf("out parameter value: %s\n",  message);
        } else {
          ResultSet rs = cs.getResultSet();
          boolean rowsReturned = ((rs != null) && rs.next());
          if(rowsReturned) { 
            String setting = rs.getString("setting");
            String value = rs.getString("value");
            System.out.printf("setting: %s, value: %s\n", setting, value);
          } else {
            System.out.printf("setting: %s does not exist.\n", val);
          }
        }
     }
   } catch(Exception e) {
     e.printStackTrace();
   } finally {
     try {
       cs.close();
     } catch (SQLException e) {
       e.printStackTrace();
     }
   }
  }

  public static void main(String[] args) throws Exception {
    new CallableStatementBugReport().run();
  }
}