package pac;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;

public class DataBase {
    private Connection c;
    private Statement stmt;
    public DataBase() {

    }
    public Object[][] getTableData(String tableName) throws SQLException {
        c = null;
        stmt = null;
          try {
             Class.forName("org.postgresql.Driver");
             c = DriverManager.getConnection("jdbc:postgresql://localhost:5432/kond", "postgres", "POI098poi");
          } catch (Exception e) {
             e.printStackTrace();
             System.err.println(e.getClass().getName()+": "+e.getMessage());
             System.exit(0);
          }
          //System.out.println("Opened database successfully");

        stmt = c.createStatement();
        ResultSet rs1 = stmt.executeQuery("SELECT COUNT(*) FROM kond." + tableName);
        rs1.next();
        int m = rs1.getInt(1);
        ResultSet rs = stmt.executeQuery("SELECT * FROM kond." + tableName);
        int n = rs.getMetaData().getColumnCount();

        Object[][] ans = new Object[m + 1][n];
        int i = 0;
        int max = 0;
        while (rs.next()) {
            for (int j = 0; j < n; j++) {
                ans[i][j] = rs.getString(j + 1);
                if (j == 0 && Integer.parseInt((String) ans[i][j]) > max) {
                    max = Integer.parseInt((String) ans[i][j]);
                }
            }
            i++;
        }
        for (int j = 1; j < n; j++) {
            ans[m][j] = "";
        }
        ans[m][0] = max + 1;
        rs1.close();
        rs.close();
        stmt.close();
        c.close();
        return ans;
    }
    public void updateTable(String tableName, int posRow, int posCol, String newVal) throws SQLException {

        c = null;
        stmt = null;
          try {
             Class.forName("org.postgresql.Driver");
             c = DriverManager.getConnection("jdbc:postgresql://localhost:5432/kond", "postgres", "POI098poi");
          } catch (Exception e) {
             e.printStackTrace();
             System.err.println(e.getClass().getName()+": "+e.getMessage());
             System.exit(0);
          }
          //System.out.println("Opened for update database successfully");


        stmt = c.createStatement();

        ResultSet rs = stmt.executeQuery("SELECT * FROM kond." + tableName);

        int i = 0;
        while (i < posRow + 1) {
            rs.next();
            i++;
        }

        String idName = rs.getMetaData().getColumnName(1);
        String thatUpdate = rs.getMetaData().getColumnName(posCol + 1);
        String id = rs.getString(1);
        String query = "UPDATE kond." + tableName + " SET " + thatUpdate + " = " + "'" + newVal + "'" + " WHERE " + idName + " = " + id;
        rs.close();

        stmt.executeUpdate(query);
        //c.commit();
        stmt.close();
        c.close();

    }
    public void deleteFromTable(String tableName, int posRow, int posCol) throws SQLException {

        c = null;
        stmt = null;
          try {
             Class.forName("org.postgresql.Driver");
             c = DriverManager.getConnection("jdbc:postgresql://localhost:5432/kond", "postgres", "POI098poi");
          } catch (Exception e) {
             e.printStackTrace();
             System.err.println(e.getClass().getName()+": "+e.getMessage());
             System.exit(0);
          }
          //System.out.println("Opened for update database successfully");


        stmt = c.createStatement();

        ResultSet rs = stmt.executeQuery("SELECT * FROM kond." + tableName);

        int i = 0;
        while (i < posRow + 1) {
            rs.next();
            i++;
        }

        String idName = rs.getMetaData().getColumnName(1);
        String id = rs.getString(1);
        String query = "DELETE FROM kond." + tableName + " WHERE " + idName + " = " + id;
        rs.close();

        stmt.executeUpdate(query);
        //c.commit();
        stmt.close();
        c.close();
    }
    public void insertData(String tableName, Object args[]) throws SQLException {
        String vals = "";
        for (int i = 0; i < args.length; i++) {
            vals += "'" + args[i] + "'";
            if (i + 1 < args.length) {
                vals += ", ";
            }
        }
        c = null;
        stmt = null;
          try {
             Class.forName("org.postgresql.Driver");
             c = DriverManager.getConnection("jdbc:postgresql://localhost:5432/kond", "postgres", "POI098poi");
          } catch (Exception e) {
             e.printStackTrace();
             System.err.println(e.getClass().getName()+": "+e.getMessage());
             System.exit(0);
          }
          //System.out.println("Opened for update database successfully");


        stmt = c.createStatement();

        String query = "INSERT INTO kond." + tableName + " VALUES(" + vals + ")";

        stmt.executeUpdate(query);
        //c.commit();
        stmt.close();
        c.close();
    }
}