package start1;

import java.awt.BorderLayout;
import java.awt.Dimension;
import java.awt.Font;
import java.awt.Insets;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import javax.swing.BorderFactory;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JPanel;
import javax.swing.JTextField;

@SuppressWarnings("serial")
public class InputPanel extends JPanel {

    CommanderPanel superPanel;

    JComboBox dropDownList;
    JTextField theTextField;
    JButton executeButton;

    Connection theConnection = null;
    Statement theStatement;

    public InputPanel(CommanderPanel thePanel) {

        superPanel = thePanel;

        String[] listItems = {
                "Create database <database name>",
                "List all databases",
                "Switch to database <database name>",
                "Show all tables in the current database",
                "Show fields in table <table name>",
                "Delete database <database name>",
                "Delete table <table name>",
                "Show all data in a table <table name>",
                "Show columns and column information of <table name>",
                "String Command",
                "String Query"
                }
        ;

        dropDownList = new JComboBox(listItems);
        dropDownList.setPreferredSize(new Dimension(100, 25));
        dropDownList.setToolTipText("Select a command");
        dropDownList.setSelectedIndex(10);

        theTextField = new JTextField();
        theTextField.setMargin(new Insets(2, 2, 2, 2));
        theTextField.setFont(superPanel.customFont.deriveFont(Font.BOLD, 14));
        theTextField.setBorder(BorderFactory.createLoweredBevelBorder());
        theTextField.setPreferredSize(new Dimension(100, 25));
        theTextField.setText("SELECT * FROM jonruna_mystartpage;");
        theTextField.setToolTipText("Command argument(s)");

        executeButton = new JButton("Execute");
        executeButton.setPreferredSize(new Dimension(100, 25));
        executeButton.setToolTipText("Execute the command");
        ActionListener executeListener = new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                executeCommand();
            }
        };
        executeButton.addActionListener(executeListener);

        BorderLayout theLayout = new BorderLayout();

        this.setLayout(theLayout);
        this.add(dropDownList, BorderLayout.NORTH);
        this.add(theTextField, BorderLayout.CENTER);
        this.add(executeButton, BorderLayout.SOUTH);

        superPanel.theStatusPanel.outputServerErrorMsg("åäö ÅÄÖ");

    }

    public void TESTconnectToMySql() {
        try {

            String url = "jdbc:mysql://localhost:3306/canvas?useUnicode=true&characterEncoding=iso-8859-1"; //?useUnicode=true&characterEncoding=UTF-8&charSet=UTF-8

            Class.forName("com.mysql.jdbc.Driver").newInstance();

            Properties prop = new Properties();
            //prop.put("socketFactory", "com.mysql.jdbc.NamedPipeSocketFactory");
            //prop.put("charSet", "UTF-8");
            //prop.put("characterEncoding", "UTF-8");
            //prop.put("useUnicode", "true");
            //prop.put("useUnicode", "true");
            //prop.put("charSet", "UTF-8");
            //prop.put("characterEncoding", "UTF-8");

            prop.put("user", "root");
            prop.put("password", "propan11");

            theConnection = DriverManager.getConnection(url, prop);

            if(!theConnection.isClosed()) {
                outputServerMsg("Successfully connected to MySQL server...");
            }

            theStatement = theConnection.createStatement();


            //stmt.executeUpdate(STRING) d�r STRING �r ett kommando att executa. den returnar en info string ocks�.
            //System.out.println(stmt.executeUpdate("CREATE DATABASE JunkDB2"));    //creata en databas
            //System.out.println(theStatement.executeUpdate("DROP DATABASE JunkDB2"));      //deleta en databas

        } catch(Exception e) {
            outputServerErrorMsg("Exception: " + e.getMessage());
        }
    }

    public void connectToMySql() {
        try {

            String url = "jdbc:mysql://localhost:3306/canvas?useUnicode=true&characterEncoding=UTF-8"; //?useUnicode=true&characterEncoding=UTF-8&charSet=UTF-8

            Class.forName("com.mysql.jdbc.Driver").newInstance();

            Properties prop = new Properties();
            //prop.put("socketFactory", "com.mysql.jdbc.NamedPipeSocketFactory");
            //prop.put("charSet", "UTF-8");
            //prop.put("characterEncoding", "UTF-8");
            //prop.put("useUnicode", "true");
            prop.put("user", "root");
            prop.put("password", "propan11");

            theConnection = DriverManager.getConnection(url, prop);

            if(!theConnection.isClosed()) {
                outputServerMsg("Successfully connected to MySQL server...");
            }

            theStatement = theConnection.createStatement();

            //stmt.executeUpdate(STRING) där STRING är ett kommando att executa. den returnar en info string också.
            //System.out.println(stmt.executeUpdate("CREATE DATABASE JunkDB2"));    //creata en databas
            //System.out.println(theStatement.executeUpdate("DROP DATABASE JunkDB2"));      //deleta en databas

        } catch(Exception e) {
            outputServerErrorMsg("Exception: " + e.getMessage());
        }
    }

    public void outputCommandMsg(String theString) {
        superPanel.theStatusPanel.outputCommandMsg(theString);
    }

    public void outputServerMsg(String theString) {
        superPanel.theStatusPanel.outputServerMsg(theString);
    }

    public void outputServerErrorMsg(String theString) {
        superPanel.theStatusPanel.outputServerErrorMsg(theString);
    }

    public void executeCommand() {

        int commandIndex = dropDownList.getSelectedIndex();

        //outputCommandMsg("Selected item has index: " + commandIndex);
        //outputServerMsg("Server response");
        //outputServerErrorMsg("Server error response");

        /*
                "Create database <database name>",
                "List all databases",
                "Switch to database <database name>",
                "Show all tables in the current database",
                "Show fields in table <table name>",
                "Delete database <database name>",
                "Delete table <table name>",
                "Show all data in a table <table name>",
                "Show columns and column information of <table name>"
        */
        switch (commandIndex) {

        case 0: //"Create database <database name>"
            createDataBase(theTextField.getText());
            break;

        case 1: //"List all databases"
            listDataBases();
            break;

        case 2: //"Switch to database <database name>"

            break;

        case 3: //"Show all tables in the current database"

            break;

        case 4: //"Show fields in table <table name>"

            break;

        case 5: //"Delete database <database name>"
            deleteDataBase(theTextField.getText());
            break;

        case 6: //"Delete table <table name>"

            break;

        case 7: //"Show all data in a table <table name>"

            break;

        case 8: //"Show columns and column information of <table name>"

            break;

        case 9: // String command
            mySqlCommand(theTextField.getText());
            break;

        case 10: // String command
            mySqlQuery(theTextField.getText());
            break;

        }

        //TODO outputStatMsg med commandot som skickas till MySQL servern, outputServerMsg med det server ger som response.

    }

    public void mySqlQuery(String commandString) { //returna ett v�rde fr�n servern.

        outputCommandMsg(commandString);

        try {
            ResultSet rs = theStatement.executeQuery(commandString);

            ResultSetMetaData rsMetaData = rs.getMetaData();

            int numberOfColumns = rsMetaData.getColumnCount();


            while (rs.next()){

                for (int i = 1; i <= numberOfColumns; i++) {

                    String outputString = "";
                    InputStream stream = rs.getBinaryStream(i);
                    int theChar;

                    try {
                        theChar = stream.read();
                        while (theChar != -1) {
                            outputString += (char)theChar;
                            theChar = stream.read();
                        }

                    } catch (IOException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }

                    outputServerMsg(outputString);
                    System.out.println(outputString);

                }

            }

        } catch (SQLException e) {
            outputServerErrorMsg(e.getMessage());
        }

        //outputServerMsg("If there is a succesful server response this method should output it");
        //outputServerErrorMsg("If there is a server error this method should output it");

    }

    public void mySqlCommand(String commandString) { //skicka commandot till server.

        outputCommandMsg(commandString);

        try {
            theStatement.executeUpdate(commandString);

            //outputServerMsg("" + theStatement.executeUpdate(commandString));


        } catch (SQLException e) {
            outputServerErrorMsg(e.getMessage());
        }

    }

    public void createDataBase(String databaseName) {

        mySqlCommand("create database " + databaseName + ";");

    }

    public void listDataBases() {

        mySqlQuery("show databases;");

    }

    public void deleteDataBase(String databaseName) {

        mySqlCommand("drop database " + databaseName + ";");

    }

}