package pl.kti.pk2.dbdemo.gui;

import java.awt.BorderLayout;
import java.awt.Dimension;
import java.awt.Font;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.swing.BorderFactory;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextArea;
import javax.swing.JTextField;

import pl.kti.pk2.dbdemo.data.CachingResultSetTableModel;
import pl.kti.pk2.dbdemo.data.DBManager;

public class DBDemoFrame extends JFrame {

    private static final long serialVersionUID = -6846315193410091203L;

    public static void main(String[] args) {
        JFrame app_frame = new DBDemoFrame();
        app_frame.setVisible(true);
    }

    private JTextArea _editor;
    private JTable _table;
    private CachingResultSetTableModel _tableModel;
    private JFrame okienko;
    private JTextField _id;
    private JTextField _name;
    private JTextField _desc;
    private JTextField _lastname;
    private JComboBox _idd_fk;
    private String[] deps;

    public DBDemoFrame() {
        super("Employees and departments");
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        setSize(550, 650);
        setLocationRelativeTo(null);

        initGUI();
    }

    private void initGUI() {

        getContentPane().setLayout(new BorderLayout());

        JButton _showAll        = new JButton("Show All");
        JButton _showLatest     = new JButton("Show Latest");
        JButton _showDepartments = new JButton("Show departments");
        JButton _showEmpl       = new JButton("Employees from department");
        JButton _addDep         = new JButton("Add new department");
        JButton _addEmpl        = new JButton("Add new employee");

        JPanel actionPanel = new JPanel();
        actionPanel.setLayout(new GridLayout(3, 0));
        getContentPane().add(actionPanel,BorderLayout.NORTH);

        actionPanel.add(_showAll);
        actionPanel.add(_showLatest);
        actionPanel.add(_showDepartments );
        actionPanel.add(_showEmpl );
        actionPanel.add(_addDep );
        actionPanel.add(_addEmpl );


        _tableModel = new CachingResultSetTableModel(null);
        _table = new JTable(_tableModel);
        JScrollPane tableScroll = new JScrollPane(_table);
        add(tableScroll, BorderLayout.CENTER);

        _showAll.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent arg0) {
                sendQuery("SELECT * FROM pracownicy");
            }
        });

        _showLatest.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent arg0) {
                sendQuery("SELECT * FROM pracownicy ORDER BY id DESC LIMIT 1");
            }
        });

        _showDepartments.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent arg0) {
                sendQuery("SELECT * FROM dzialy");
            }
        });

        _showEmpl.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent arg0) {
                String id = String.valueOf( _tableModel.getValueAt(_table.getSelectedRow() , 0) );
                sendQuery("SELECT * FROM pracownicy LEFT JOIN dzialy ON(idd_fk=idd) WHERE idd_fk = "+id+";");
            }
        });

        _addDep.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent arg0) {
                //String name = JOptionPane.showInputDialog(getContentPane(), "Podaj nazwę działu:");
                JButton _ok     = new JButton("OK");
                JButton _cancel = new JButton("Cancel");
                _id = new JTextField();
                _name = new JTextField();
                _desc = new JTextField();

                okienko = new JFrame();
                okienko.add(new JLabel("ID:") );
                okienko.add(_id);
                okienko.add(new JLabel("Name:"));
                okienko.add(_name);
                okienko.add(new JLabel("Description:"));
                okienko.add(_desc);
                okienko.add(_cancel);
                okienko.add(_ok);

                okienko.setSize(500, 200);
                okienko.setLocationRelativeTo(null);
                GridLayout layout = new GridLayout(0, 2,6,6);

                okienko.setLayout(layout);
                okienko.setTitle("Add department");
                okienko.setVisible(true);

                _ok.addActionListener(new ActionListener() {
                    public void actionPerformed(ActionEvent arg0) {
                        sendQuery("INSERT INTO dzialy VALUES('"+_id.getText()+"','"+_name.getText()+"','"+_desc.getText()+"');");
                        sendQuery("SELECT * FROM dzialy");
                        okienko.setVisible(false);
                    }
                });

                _cancel.addActionListener(new ActionListener() {
                    public void actionPerformed(ActionEvent arg0) {
                        okienko.setVisible(false);
                    }
                });
            }
        });

        _addEmpl.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent arg0) {
                JButton _ok     = new JButton("OK");
                JButton _cancel = new JButton("Cancel");
                sendQuery("SELECT * FROM dzialy ORDER BY idd ASC");
                _id = new JTextField();
                _name = new JTextField();
                _lastname = new JTextField();
                _desc = new JTextField();
                _idd_fk = new JComboBox();
                deps = new String[_tableModel.getRowCount()];

                for( int x= 0; x<_tableModel.getRowCount(); x++)
                {
                    _idd_fk.insertItemAt(_tableModel.getValueAt(x, 1), x);
                    deps[x] = String.valueOf(_tableModel.getValueAt(x, 0));
                }

                okienko = new JFrame();
                okienko.add(new JLabel("ID:") );
                okienko.add(_id);
                okienko.add(new JLabel("Name:"));
                okienko.add(_name);
                okienko.add(new JLabel("Last Name:"));
                okienko.add(_lastname);
                okienko.add(new JLabel("Description:"));
                okienko.add(_desc);
                okienko.add(new JLabel("Department:"));
                okienko.add(_idd_fk);
                okienko.add(_cancel);
                okienko.add(_ok);

                okienko.setSize(500, 200);
                okienko.setLocationRelativeTo(null);
                GridLayout layout = new GridLayout(0, 2,6,6);

                okienko.setLayout(layout);
                okienko.setTitle("Add employee");
                okienko.setVisible(true);

                _ok.addActionListener(new ActionListener() {
                    public void actionPerformed(ActionEvent arg0) {
                        sendQuery("INSERT INTO pracownicy VALUES('"+_id.getText()+"','"+_name.getText()+"','"+_lastname.getText()+"','"+_desc.getText()+"','"+deps[_idd_fk.getSelectedIndex()]+"');");
                        sendQuery("SELECT * FROM pracownicy");
                        okienko.setVisible(false);
                    }
                });

                _cancel.addActionListener(new ActionListener() {
                    public void actionPerformed(ActionEvent arg0) {
                        okienko.setVisible(false);
                    }
                });
            }
        });
    }

    protected void sendQuery() {
        try {
            // read query from the editor
            String query = _editor.getText();

            if (query.length() > 6 &&
                (
                    query.substring(0, 6).equalsIgnoreCase("insert") ||
                    query.substring(0, 6).equalsIgnoreCase("update") ||
                    query.substring(0, 6).equalsIgnoreCase("delete")
                )
            ) {
                Statement stmt = DBManager.getConnection().createStatement();
                stmt.executeUpdate(query);
                _tableModel.setResultSet(null);
                stmt.close();
            } else if ((query.length() > 6) && (query.substring(0,6).equalsIgnoreCase("select"))){
                // create SQL statement and execute query read from the _editor
                Statement stmt = DBManager.getConnection().createStatement();
                ResultSet queryResult = stmt.executeQuery(query);
                // pass the resultSet to the table model (use setResultSet method from the model)
                _tableModel.setResultSet(queryResult);
                // close the resultSet and statement
                queryResult.close();
                stmt.close();
            }
            else
            {
                Statement stmt = DBManager.getConnection().createStatement();
                stmt.execute(query);
                stmt.close();
            }
        } catch (Exception e) {
            _editor.setText(e.getMessage());
        }
    }
    protected void sendQuery(String query) {
        try {

            if (query.length() > 6 &&
                (
                    query.substring(0, 6).equalsIgnoreCase("insert") ||
                    query.substring(0, 6).equalsIgnoreCase("update") ||
                    query.substring(0, 6).equalsIgnoreCase("delete")
                )
            ) {
                Statement stmt = DBManager.getConnection().createStatement();
                stmt.executeUpdate(query);
                _tableModel.setResultSet(null);
                stmt.close();
            } else if ((query.length() > 6) && (query.substring(0,6).equalsIgnoreCase("select"))){
                // create SQL statement and execute query read from the _editor
                Statement stmt = DBManager.getConnection().createStatement();
                ResultSet queryResult = stmt.executeQuery(query);
                // pass the resultSet to the table model (use setResultSet method from the model)
                _tableModel.setResultSet(queryResult);
                // close the resultSet and statement
                queryResult.close();
                stmt.close();
            }
            else
            {
                Statement stmt = DBManager.getConnection().createStatement();
                stmt.execute(query);
                stmt.close();
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }
}