/*
 * @author Timothy White
 * Program: dbManipulatorGUI
 * Purpose: Allows one to interact with a connected database
 */

// imports for project
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import javax.swing.*;
import javax.swing.event.*;

public class dbManipulatorGUI extends JFrame implements ActionListener
{

    /* Class Wide Variables
     * Relating to GUI & The DB Object
     */
    public static DBAccessObject objDBAccess;

    private static boolean
        blnLeftWindowPopulated = false;

    private static JButton
        jbtnExecute = new JButton("Execute"),
        jbtnClear = new JButton("Clear"),
        jbtnExit = new JButton("Exit");

    private static DefaultListModel<String>
        dlmSQLPanelLeft = new DefaultListModel<String>(),
        dlmSQLPanelRight = new DefaultListModel<String>();

    private static JList<String>
        jlstSQLPanelLeft = new JList<String>( dlmSQLPanelLeft ),
        jlstSQLPanelRight = new JList<String>( dlmSQLPanelRight );

    private static JTextArea
        jtaInput = new JTextArea(15, 1),
        jtaOutput = new JTextArea(15, 1);

    /* Constructor dbManipulatorGUI
     * Sets the functionality of the GUI
     */
    public dbManipulatorGUI()
    {
        super("Timothy White's DB Tool");
        setSize(560, 640);
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

        JPanel jpnlMain = new JPanel();
        JPanel jpnlNorth = new JPanel();
        JPanel jpnlCenter = new JPanel();
        JPanel jpnlSouth = new JPanel();

        jpnlMain.setLayout(new BorderLayout());
        jpnlNorth.setLayout(new GridLayout(1, 2));
        jpnlCenter.setLayout(new GridLayout(2, 1));
        jpnlSouth.setLayout(new GridLayout(1, 3));

        jpnlMain.add(jpnlNorth, BorderLayout.NORTH);
        jpnlMain.add(jpnlCenter, BorderLayout.CENTER);
        jpnlMain.add(jpnlSouth, BorderLayout.SOUTH);

        jpnlNorth.add(new JScrollPane(jlstSQLPanelLeft));
        jpnlNorth.add(new JScrollPane(jlstSQLPanelRight));

        jpnlCenter.add(new JScrollPane(jtaInput));
        jpnlCenter.add(new JScrollPane(jtaOutput));

        jlstSQLPanelLeft.addListSelectionListener(new ListBoxListener());

        jbtnExecute.addActionListener(this);
        jbtnClear.addActionListener(this);
        jbtnExit.addActionListener(this);

        jpnlSouth.add(jbtnExecute);
        jpnlSouth.add(jbtnClear);
        jpnlSouth.add(jbtnExit);

        Container ctnObjDBTool = getContentPane();
        ctnObjDBTool.setBackground(Color.lightGray);
        ctnObjDBTool.add(jpnlMain);

        setContentPane(ctnObjDBTool);
        setVisible(true);
    }

    /* ListBoxListener
     * This handles the upper left list boxes click events.
     * Depending on which DB table is clicked, corresponds to
     * what columns are added to the right list box.
     */
    class ListBoxListener implements ListSelectionListener
    {
        public void valueChanged(ListSelectionEvent e)
        {
            if (!e.getValueIsAdjusting())
            {
                dlmSQLPanelRight.clear();
                try
                {
                    ResultSetMetaData rsmdColumns = objDBAccess.getResultMetaData(jlstSQLPanelLeft.getSelectedValue());
                    for (int iCount = 1; iCount <= rsmdColumns.getColumnCount(); iCount++)  {
                        dlmSQLPanelRight.addElement(rsmdColumns.getColumnLabel(iCount) + " (" + rsmdColumns.getColumnTypeName(iCount) + ")");
                    }
                }
                catch (SQLException err)
                {
                    setOutput(err.getMessage());
                }
            }
        }
    }

    /* clearScreen()
     * Clears all the writable GUI components
     */
    public void clearScreen()
    {
        jtaInput.setText("");
        jtaOutput.setText("");
    }

    /* setOutput
     * used for easily appending output to the jtaOutput JTextArea
     */
    public void setOutput(String sMessage)
    {
        jtaOutput.append(sMessage);
    }

    /* doExecution()
     * Handles the execution button.
     * Will push the user inputted query
     * and show results in the bottom JTextArea
     * accordingly.
     */
    public void doExecution()
    {
        String sPrepOutput = "";
        try
        {
            // This if is to check if the statement issued is a SELECT
            if(jtaInput.getText().length() >= 6 && jtaInput.getText().substring(0, 6).equalsIgnoreCase("SELECT"))
            {
                // Attempt to get a result set from the inputted query
                ResultSet rsSQLselect = objDBAccess.getResultSet(jtaInput.getText());
                // get the result sets meta data so I can see the column names
                ResultSetMetaData rsmdSelect = rsSQLselect.getMetaData();
                // get total column names
                int iTotalColumns = rsmdSelect.getColumnCount();

                // loop through them and tab them nicely
                for(int i = 1; i < iTotalColumns + 1; i++)
                {
                    sPrepOutput += rsmdSelect.getColumnName(i) + "\t";
                }

                sPrepOutput += "\n";

                // loop through the results and tab them
                while(rsSQLselect.next())
                {
                    for(int i = 1; i < iTotalColumns + 1; i++)
                    {
                        sPrepOutput += rsSQLselect.getString(i) + "\t";
                    }
                    sPrepOutput += "\n";
                }
                // write all the prepped output to the JTextArea
                setOutput(sPrepOutput);

            }
            // If It's not a select statement, this is executed.
            else
            {
                // Holds the affected rows from the executed query
                int iSQLResults = objDBAccess.executeSQL(jtaInput.getText());

                // Populate Left Window in case Table Query is made
                dlmSQLPanelLeft.clear();
                populateLeftWindow();

                // Appends output to the JTextArea regarding the results from query made above.
                setOutput(iSQLResults + " results produced from query " + jtaInput.getText() + "\n");
            }
        }
        catch (SQLException e)
        {
            setOutput("SQL ERROR: " + e.getMessage() + "\n");
        }
        catch (Exception e)
        {
            setOutput("General Error: " + e.getMessage() + "\n");
        }
    }

    /* actionPerformed
     * Handles the button clicks associated with the GUI
     */
    public void actionPerformed(java.awt.event.ActionEvent e)
    {
        try
        {
            // cast the source to a jbutton
            JButton jbtnClicked = (JButton) e.getSource();

            // depending on the jbutton text decides what happens from here
            if(jbtnClicked.getText().equals("Execute"))
                doExecution();

            else if(jbtnClicked.getText().equals("Clear"))
                clearScreen();

            else
                System.exit(0);

        }
        catch (Exception err)
        {
            setOutput(err.getMessage() + "\n");
        }
    }

    /* populateLeftWindow()
     * Populates the upper left list window of the GUI with the database tables
     */
    public static void populateLeftWindow()
    {
        // Try to get the tables from DB
        try
        {
            // try to get a result set
            ResultSet rsDBTables = objDBAccess.getDatabaseTableNames();

            // loop through the results and add them to the right window
            while(rsDBTables.next())
            {
                dlmSQLPanelLeft.addElement(rsDBTables.getString("TABLE_NAME"));
            }
        }
        catch (SQLException e)
        {
            jtaOutput.append(e.getMessage() + "\n");
        }
    }

    /* Main for dbManipulatorGUI
     * Instantiates GUI and Attempts to connect the DB
     * Then Populates the Upper Left List with the corresponding tables from the selected DB
     * Then sets the selected Index to 0 to show the tables inside the DB by default.
     */
    public static void main(String[] args)
    {
        try
        {
            objDBAccess = new DBAccessObject("jdbc:mysql://localhost:3306/empdb");
        }
        catch (Exception e)
        {
            System.out.println("Failed to instantiate Database");
        }

        dbManipulatorGUI objDbMan = new dbManipulatorGUI();

        dbManipulatorGUI.populateLeftWindow();
        jlstSQLPanelLeft.setSelectedIndex(0);

    }
}