import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.ArrayList;

public class StarApp {
    private static ArrayList<String> sessionid;
    private static ArrayList<String> ixnid;
    private static ArrayList<String> customerid;
    private static final String dburl = "jdbc:oracle:thin:@spb-voxdb-scan.scartel.dc:1521/chatdbvox.scartel.dc";
    private static final String user_virtual_agent = "";
    private static final String password_virtual_agent = "";

    private static final String user_ucs = "";
    private static final String password_ucs = "";

    private static Connection con;
    private static Statement stmt;
    private static ResultSet rs;
    private static PreparedStatement stmt_in;


    public static void main(String[] args) {
        StarApp starApp = new StarApp();
        SOAPRequest soapRequest = new SOAPRequest();
        /*sessionid = starApp.getSessionID();
        ixnid = starApp.getIxnID(sessionid);
        customerid = starApp.getCustomerIdByIxnID(ixnid);
        for (int i = 0; i < customerid.size(); i++) {
            System.out.println(customerid.get(i));
        }
        */
        soapRequest.getCustomerIDFromCIS("133223d501b04cb591ddffefda1886e5");




    }

    public ArrayList<String> getSessionID() {
        ArrayList<String> sessionid = new ArrayList<String>();
        try (BufferedReader reader = new BufferedReader(
                new InputStreamReader(
                        new FileInputStream("C:\\Users\\mavramenko\\Desktop\\ID.txt"), StandardCharsets.UTF_8))) {
            String line;
            while ((line = reader.readLine()) != null) {
                sessionid.add(line);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

        return sessionid;
    }

    public ArrayList<String> getIxnID(ArrayList<String> sessionid) {
        ArrayList<String> ixid = new ArrayList<>();
        String query = "";
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            con = DriverManager.getConnection(dburl, user_virtual_agent, password_virtual_agent);
            stmt = con.createStatement();
            for (int i = 0; i < sessionid.size(); i++) {
                query = "select sessionid from chat " +
                        "where bot_sessionid = '" + sessionid.get(i) +
                        "' and rownum = 1";
                System.out.println("get ixn id SQL: " + query);
                rs = stmt.executeQuery(query);

                while (rs.next()) {
                    ixid.add(rs.getString(1));
                }
                //Thread.sleep(2000);

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                System.out.println(e);
            }
            try {
                stmt.close();
            } catch (SQLException e) {
                System.out.println(e);
            }
            try {
                rs.close();
            } catch (SQLException e) {
                System.out.println(e);
            }
        }


        return ixid;
    }

    public ArrayList<String> getCustomerIdByIxnID(ArrayList<String> ixnid) {
        ArrayList<String> customerid = new ArrayList<>();
        String query = "";
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            con = DriverManager.getConnection(dburl, user_ucs, password_ucs);
            stmt = con.createStatement();
            for (int i = 0; i < sessionid.size(); i++) {
                query = "select STRATTRIBUTE1 from CONTACT " +
                        "where id = (SELECT CONTACTID " +
                        "FROM interaction i" +
                        " where i.id = '" + ixnid.get(i) + "') ";
                System.out.println("get customer SQL: " + query);
                rs = stmt.executeQuery(query);

                while (rs.next()) {
                    customerid.add(rs.getString(1));
                }
               // Thread.sleep(2000);

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                System.out.println(e);
            }
            try {
                stmt.close();
            } catch (SQLException e) {
                System.out.println(e);
            }
            try {
                rs.close();
            } catch (SQLException e) {
                System.out.println(e);
            }
        }


        return customerid;
    }


}