package net.astercrono;

import java.awt.Color;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.Timer;
import java.util.TimerTask;
import java.util.Date;

import javax.swing.JFrame;
import javax.swing.JScrollPane;
import javax.swing.JTable;

public class App {
    private static final String SQL = " " + "select " + "   case "
            + "       when minutes > 60 then round((minutes / 60)::numeric, 1) || ' hours' "
            + "       else round(minutes::numeric, 1) || ' minutes'  " + "       end as dur, "
            + "       pid, usr, app, addr::varchar, start, wait_event_type, wait_event, state, query "
            + "       from ( "
            + "           select extract(epoch from now() - query_start) / 60 as minutes, pid, usename as usr, application_name as app, client_addr as addr, wait_event_type, wait_event, to_char(query_start, 'MM/DD/YY HH24:MI:SS') as start, state, query "
            + "           from pg_stat_activity ps " + "           where state != 'idle' " + "       ) stats "
            + "       where minutes > 0 " + "       and lower(query) !~ 'autovacuum' " + " order by minutes desc ";

    private static final String[] COLUMN_NAMES = new String[] { "dur", "pid", "usr", "app", "addr", "start",
            "wait-event-type", "wait-event", "state", "query" };

    private static JFrame window = new JFrame();
    private static JTable currentTable;
    private static JScrollPane scrollPane;

    public static void main(String[] args) throws Exception {
        window.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        window.setSize(1000, 800);

        Timer timer = new Timer();
        timer.scheduleAtFixedRate(new TimerTask() {
            @Override
            public void run() {
                populateTable();
            }
        }, 0, 1000 * 30);
    }

    private static void populateTable() {
        System.out.println("populating table");

        try {
            if (currentTable != null) {
                window.remove(scrollPane);
            }

            String[][] data = query();
            currentTable = new JTable(data, COLUMN_NAMES);
            currentTable.setShowHorizontalLines(false);
            currentTable.setShowVerticalLines(false);

            currentTable.getColumn("dur").setPreferredWidth(100);
            currentTable.getColumn("pid").setPreferredWidth(100);
            currentTable.getColumn("usr").setPreferredWidth(100);
            currentTable.getColumn("app").setPreferredWidth(100);
            currentTable.getColumn("addr").setPreferredWidth(100);
            currentTable.getColumn("start").setPreferredWidth(100);
            currentTable.getColumn("wait-event-type").setPreferredWidth(100);
            currentTable.getColumn("wait-event").setPreferredWidth(100);
            currentTable.getColumn("state").setPreferredWidth(100);
            currentTable.getColumn("query").setPreferredWidth(500);

            currentTable.getTableHeader().setBackground(new Color(70, 70, 70));
            currentTable.getTableHeader().setForeground(Color.WHITE);

            scrollPane = new JScrollPane(currentTable, JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED,
                    JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED);
            currentTable.setAutoResizeMode(JTable.AUTO_RESIZE_LAST_COLUMN);

            window.add(scrollPane);
            window.setVisible(true);
            window.repaint();
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        System.out.println("table populated");
    }

    private static String[][] query() throws Exception {
        System.out.println("querying");

        Class.forName("org.postgresql.Driver");
        Connection connection = null;
        connection = DriverManager.getConnection("jdbc:postgresql://pgprimary.saucontds.com:5432/tds", "postgres",
                "postgres");

        ResultSet rs = connection.createStatement().executeQuery(SQL);
        System.out.println("query returned");
        List<String[]> allRows = new ArrayList<String[]>();

        while (rs.next()) {
            String dur = Objects.toString(rs.getString("dur"), "--");
            String pid = Objects.toString(rs.getInt("pid"), "--");
            String usr = Objects.toString(rs.getString("usr"), "--");
            String app = Objects.toString(rs.getString("app"), "--");
            String addr = Objects.toString(rs.getString("addr"), "--");
            String start = Objects.toString(rs.getString("start"), "--");
            String waitEventType = Objects.toString(rs.getString("wait_event_type"), "--");
            String waitEvent = Objects.toString(rs.getString("wait_event"), "--");
            String state = Objects.toString(rs.getString("state"), "--");

            String query = Objects.toString(rs.getString("query"), "--");
            if (query.length() > 110) {
                query = query.substring(0, 110);
            }

            String[] row = new String[] { dur, pid, usr, app, addr, start, waitEventType, waitEvent, state, query };

            allRows.add(row);
        }

        connection.close();

        System.out.println(allRows.size() + " rows");
        return allRows.toArray(new String[allRows.size()][10]);
    }
}