package esac.archive.npsa.dl.ingestion.util;

import org.junit.Ignore;
import org.junit.Test;

import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;

import java.sql.*;
import java.util.Base64;

public class JdbcTest {

    //private final String url = "jdbc:postgresql://localhost:8300/psa?currentSchema=test_product_ui_partition";
    private final String url = "jdbc:postgresql://localhost:1082/psa?currentSchema=test_product_ui_partition";
    private final String user = "ingestion";
    private final String password = "********";

    private final String[] QUERIES = new String[] {
            "select * from test_product_ui_partition.observational_product where logical_identifier = 'urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t000000-20220729t235959'",
            "select * from test_product_ui_partition.product_ui pui where pui.mission_id='urn:esa:psa:context:investigation:mission.em16' and pui.logical_identifier='urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t000000-20220729t235959'",
            "select * from test_product_ui_partition.product_ui_em16 pui where pui.mission_id='urn:esa:psa:context:investigation:mission.em16' and pui.logical_identifier='urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t000000-20220729t235959'",
            "select logical_identifier, version_id, lastproduct from test_product_ui_partition.product_ui pui where pui.mission_id='urn:esa:psa:context:investigation:mission.em16' and pui.logical_identifier='urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t000000-20220729t235959'",
            "select logical_identifier, version_id, lastproduct from test_product_ui_partition.product_ui_em16 pui where pui.mission_id='urn:esa:psa:context:investigation:mission.em16' and pui.logical_identifier='urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t000000-20220729t235959'"
    };

    /**
     * Connect to the PostgreSQL database
     *
     * @return a Connection object
     */
    private Connection connect() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, user, password);
            conn.setAutoCommit(false);
            System.out.println("Connected to the PostgreSQL server successfully.");
            System.out.println(conn.getClass());
            DatabaseMetaData meta = conn.getMetaData();
            System.out.println(meta.getDatabaseProductName());
            System.out.println("version: " + meta.getDatabaseProductVersion());
            System.out.println(meta.getDriverName());
            System.out.println("version: " + meta.getDriverVersion());
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return conn;
    }

    @Test
    @Ignore
    public void testConnection() throws SQLException {

        try (Connection conn = connect()) {

            Statement st = conn.createStatement();
            // Turn use of the cursor on.
            st.setFetchSize(50);

            boolean first = true;
            for (String query : QUERIES) {

                System.out.println("Query: " + query);

                long ts = System.currentTimeMillis();
                ResultSet rs = st.executeQuery(query);
                int count = 0;
                while (rs.next()) {
                    if (first) {
                        System.out.println(
                                String.format("  Row %d: %s, %s ... ", ++count,
                                              rs.getString(rs.findColumn("logical_identifier")),
                                              rs.getString(rs.findColumn("version_id"))));
                        first = false;
                    } else {
                        System.out.println(
                                String.format("  Row %d: %s, %s, %b...", ++count,
                                              rs.getString(rs.findColumn("logical_identifier")),
                                              rs.getString(rs.findColumn("version_id")),
                                              rs.getBoolean(rs.findColumn("lastProduct"))));
                    }
                }
                rs.close();
                long elapsed = System.currentTimeMillis() - ts;
                System.out.println("Elapsed: " + elapsed + " ms\n");
            }
            st.close();
        }
    }

    @Test
    public void testQuery_Explain_Analize() throws SQLException {

        try (Connection conn = connect()) {

            Statement st = conn.createStatement();
            // Turn use of the cursor on.
            st.setFetchSize(50);

            //st.execute("SET ENABLE_SEQSCAN TO OFF");

            String query =
                    "explain analyze select logical_identifier, version_id, lastproduct from test_product_ui_partition.product_ui pui where pui.mission_id='urn:esa:psa:context:investigation:mission.em16' and pui.logical_identifier='urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t000000-20220729t235959'";
            System.out.println("Query: " + query);

            long ts = System.currentTimeMillis();
            ResultSet rs = st.executeQuery(query);
            int count = 0;
            while (rs.next()) {
                System.out.println(rs.getString(1));
            }
            rs.close();
            long elapsed = System.currentTimeMillis() - ts;
            System.out.println("Elapsed: " + elapsed + " ms\n");

            st.close();
        }
    }

    @Test
    public void testQuery_Explain_Analize_product_ui_em16() throws SQLException {

        try (Connection conn = connect()) {

            Statement st = conn.createStatement();
            // Turn use of the cursor on.
            st.setFetchSize(50);

            String query =
                    "explain analyze select logical_identifier, version_id, lastproduct from test_product_ui_partition.product_ui_em16 pui where pui.mission_id='urn:esa:psa:context:investigation:mission.em16' and pui.logical_identifier='urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t000000-20220729t235959'";
            System.out.println("Query: " + query);

            long ts = System.currentTimeMillis();
            ResultSet rs = st.executeQuery(query);
            int count = 0;
            while (rs.next()) {
                System.out.println(rs.getString(1));
            }
            rs.close();
            long elapsed = System.currentTimeMillis() - ts;
            System.out.println("Elapsed: " + elapsed + " ms\n");

            st.close();
        }
    }

    @Test
    public void testQuery_Explain_Analize_PrepareStatement() throws SQLException {

        try (Connection conn = connect()) {

            PreparedStatement pst = conn.prepareStatement(
                    "explain analyze select logical_identifier, version_id, lastproduct from test_product_ui_partition.product_ui pui where pui.mission_id=? and pui.logical_identifier=?");
            // Turn use of the cursor on.
            pst.setFetchSize(50);

            pst.setString(1, "urn:esa:psa:context:investigation:mission.em16");
            pst.setString(2, "urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t000000-20220729t235959");

            long ts = System.currentTimeMillis();
            ResultSet rs = pst.executeQuery();
            int count = 0;
            while (rs.next()) {
                System.out.println(rs.getString(1));
            }
            rs.close();
            long elapsed = System.currentTimeMillis() - ts;
            System.out.println("Elapsed: " + elapsed + " ms\n");

            pst.close();
        }
    }

}