package com.savecsvtopostgres;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class SaveCsvDataToPostgres {

    List<OauthResponse> responseList = new ArrayList<OauthResponse>();

    public static void main(String[] args){

        BufferedReader csvBuffer = null;
        List<OauthResponse> csvlist = new ArrayList<OauthResponse>();

        try {
            String csvLine;
            csvBuffer = new BufferedReader(new FileReader("C:\Users\DVSabareesh\testresults.csv"));

            while ((csvLine = csvBuffer.readLine()) != null) {
                System.out.println("Raw CSV data: " + csvLine);
                OauthResponse oauthResponse = csvtoArrayList(csvLine);
                csvlist.add(oauthResponse);
            }
            insert(csvlist);
            System.out.println("Converted ArrayList data: " + csvlist.size() + "n");
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (csvBuffer != null) csvBuffer.close();
            } catch (IOException exception) {
                exception.printStackTrace();
            }
        }
    }

    // Utility which converts CSV to ArrayList using Split Operation
    public static OauthResponse csvtoArrayList(String csvLine) {
        OauthResponse oauthResponse = new OauthResponse();

        if (csvLine != null) {
            String[] splitData = csvLine.split(",");
            int datalength = splitData.length;
            System.out.println(datalength);

            oauthResponse.setId(splitData[0]);
            oauthResponse.setValue2(splitData[1]);
            oauthResponse.setTicketName(splitData[2]);
            oauthResponse.setTicketResponse(splitData[3]);
            oauthResponse.setResponseId(splitData[4]);
            oauthResponse.setResponseMessage(splitData[5]);
            oauthResponse.setOperations(splitData[6]);
            oauthResponse.setFileType(splitData[7]);
            oauthResponse.setBooleanValue(splitData[8]);
            oauthResponse.setId2(splitData[9]);
            oauthResponse.setId3(splitData[10]);
            oauthResponse.setOauthUrl(splitData[11]);
            if(datalength == 13){
                oauthResponse.setValue13(splitData[12]);
            }else{
                oauthResponse.setValue13("");
            }
        }
        return oauthResponse;
    }

    // Utility which used to insert data into postgres
    public static void insert(List<OauthResponse> responseList) throws SQLException {
        Connection dbConnection = null;
        PreparedStatement ps = null;
        try {
            dbConnection = getDBConnection();
            dbConnection.setAutoCommit(false);
            String query = "INSERT INTO csvvalues("
                    + "testgeneratorid, "
                    + "ticketvalues1, "
                    + "ticketname, "
                    + "ticketresponsecode, "
                    + "ticketresponsemessage, "
                    + "testcaseresults, "
                    + "resultformat, "
                    + "booleanresult, "
                    + "ticketvalues2, "
                    + "ticketvalues3, "
                    + "oauthurl, "
                    + "ticketvalues4, "
                    + "ticketvalues5) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            ps = dbConnection.prepareStatement(query);

            System.out.println("Records are getting inserted into TestSuiteResults.TestCase table!");

            for(OauthResponse res : responseList){
                ps.setString(1,res.getId());
                ps.setString(2,res.getValue2());
                ps.setString(3,res.getTicketName());
                ps.setString(4,res.getTicketResponse());
                ps.setString(5,res.getResponseId());
                ps.setString(6,res.getResponseMessage());
                ps.setString(7,res.getOperations());
                ps.setString(8,res.getFileType());
                ps.setString(9,res.getBooleanValue());
                ps.setString(10,res.getId2());
                ps.setString(11,res.getId3());
                ps.setString(12,res.getOauthUrl());
                ps.setString(13,res.getValue13());
                ps.addBatch();
            }
            ps.executeBatch();
            dbConnection.commit();
            System.out.println("Record is inserted into DBUSER table!");
        } catch (SQLException e) {
            e.getNextException().printStackTrace();
            dbConnection.rollback();
        } finally {
            if (ps != null) {
                ps.close();
            }
            if (dbConnection != null) {
                dbConnection.close();
            }
        }
    }

    // Utility which gets connection to postgres
    public static Connection getDBConnection() {
        Connection dbConnection = null;
        try {
            //Class.forName("org.postgresql.Driver");
            //dbConnection = DriverManager.getConnection(
                    //"jdbc:postgresql://localhost:5432/Database4TesSuitetResults", "postgres", "santhosh");
            Class.forName("com.mysql.jdbc.Driver");
            dbConnection = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3309/csv","root","system");

            System.out.println("DB connected");
            return dbConnection;
        } catch (SQLException | ClassNotFoundException e) {
            System.out.println(e.getMessage());
        }
        return dbConnection;
    }
}