package com.saugat;

import java.sql.*;
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;

public class Main implements ActionListener{

    // Database
    String url="jdbc:mysql://localhost/?useSSL=false";
    Connection _connection;
    Statement _statement;

    // Form Elements
    JFrame _frame;
    JPanel panel1, panel2, panel3;
    JLabel l1, l2, l3, l4, l5, l6, l7, l8, status;
    JTextField txtDBName, txtTableName, txtCloumnName1, txtCloumnName2, txtCloumnName3, txtColumnType1, txtColumnType2, txtColumnType3,InsertData1, InsertData2, InsertData3;
    JComboBox selectDB, selectTable;
    JButton createDBButton, createTableButton, InsertDataButton;
    MenuBar menubar;
    Menu exitMenu;

    public static void main(String[] args) {
        new Main();
    }

    public Main() {
        if(makeDBConnection()) {
            System.out.println("Connection Successful");
            createLayout();
        }else {
            System.out.println("Connection Unsucessful");
        }
    }

    public void createLayout() {
        _frame = new JFrame("Saugat Database Project ");
        _frame.setSize(450, 330);
        _frame.setVisible(true);
        _frame.setLayout(new GridBagLayout());

        _frame.setMenuBar(menubar);

            panel2 = new JPanel();
            GridBagConstraints gbc = new GridBagConstraints();
            gbc.fill = GridBagConstraints.VERTICAL;


            panel2.setLayout(new GridBagLayout());
            l2 = new JLabel("Select DB");


            gbc.gridx = 0;
            gbc.gridy = 0;
            l1 = new JLabel("DB Name");
            panel2.add(l1,gbc);
            panel2.setBackground(Color.magenta);
            gbc.gridx = 1;
            gbc.gridy = 0;
            txtDBName = new JTextField(35);
            panel2.add(txtDBName,gbc);

            gbc.gridx = 2;
            gbc.gridy = 0;
            createDBButton = new JButton("Create Database");
            panel2.add(createDBButton,gbc);

            gbc.gridx = 0;
            gbc.gridy = 1;
            panel2.add(l2,gbc);

            gbc.gridx = 1;
            gbc.gridy = 1;
            selectDB = new JComboBox();
            selectDB.setSelectedIndex(-1);
            panel2.add(selectDB,gbc);
            _frame.add(panel2);
            addDBdata();


            gbc.gridx = 0;
            gbc.gridy = 2;
            l3 = new JLabel("Table Name:");
            txtTableName = new JTextField(35);
            panel2.add(l3,gbc);
            gbc.gridx = 1;
            gbc.gridy = 2;
            panel2.add(txtTableName,gbc);


            l4 = new JLabel("Columns");
            gbc.gridx = 0;
            gbc.gridy = 3;
            panel2.add(l4,gbc);

            l5 = new JLabel("Name");
            gbc.gridx = 1;
            gbc.gridy = 3;
            panel2.add(l5,gbc);

            l6 = new JLabel("Type");
            gbc.gridx = 2;
            gbc.gridy = 3;
            panel2.add(l6,gbc);


            txtCloumnName1 = new JTextField(20);
            gbc.gridx = 1;
            gbc.gridy = 4;
            panel2.add(txtCloumnName1,gbc);
            txtCloumnName2 = new JTextField(20);
            gbc.gridx = 1;
            gbc.gridy = 5;
            panel2.add(txtCloumnName2,gbc);
            txtCloumnName3 = new JTextField(20);
            gbc.gridx = 1;
            gbc.gridy = 6;
            panel2.add(txtCloumnName3,gbc);


            txtColumnType1 = new JTextField(20);
            gbc.gridx = 2;
            gbc.gridy = 4;
            panel2.add(txtColumnType1,gbc);
            txtColumnType2 = new JTextField(20);
            gbc.gridx = 2;
            gbc.gridy = 5;
            panel2.add(txtColumnType2,gbc);
            txtColumnType3 = new JTextField(20);
            gbc.gridx = 2;
            gbc.gridy = 6;
            panel2.add(txtColumnType3,gbc);
            createTableButton = new JButton("Create Table");
            gbc.gridx = 1;
            gbc.gridy = 7;
            panel2.add(createTableButton,gbc);

            gbc.gridx = 0;
            gbc.gridy = 8;
            l7 = new JLabel("Select Table");
            panel2.add(l7,gbc);

            gbc.gridx = 1;
            gbc.gridy = 8;
            selectTable = new JComboBox();
            panel2.add(selectTable,gbc);

            selectDB.addItemListener(new ItemListener() {

                @Override
                public void itemStateChanged(ItemEvent e) {
                    getTable();

                }
            });
            gbc.gridx = 0;
            gbc.gridy = 9;
            l8 = new JLabel("Insert Data: ");
            panel2.add(l8,gbc);

            gbc.gridx = 1;
            gbc.gridy = 9;
            InsertData1 = new JTextField(20);
            panel2.add(InsertData1,gbc);

            gbc.gridx = 1;
            gbc.gridy = 10;
            InsertData2 = new JTextField(20);
            panel2.add(InsertData2,gbc);

            gbc.gridx = 1;
            gbc.gridy = 11;
            InsertData3 = new JTextField(20);
            panel2.add(InsertData3,gbc);
            getTable();


            gbc.gridx = 1;
            gbc.gridy = 12;
            InsertDataButton = new JButton("Add Data");
            panel2.add(InsertDataButton,gbc);

        createTableButton.addActionListener(this);
        createDBButton.addActionListener(this);
        InsertDataButton.addActionListener(this);

    }
    public boolean makeDBConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            _connection = DriverManager.getConnection(url,"root","");
            return true;
        }catch(Exception e) {
            System.out.println("ERROR "+ e);
            return false;
        }
    }
    public boolean getTable() {
        try {
            String url="jdbc:mysql://localhost/"+selectDB.getSelectedItem()+"?useSSL=false";
            _connection =DriverManager.getConnection(url,"root","");

            _statement=_connection.createStatement();


            DatabaseMetaData meta = _connection.getMetaData();
            ResultSet res = meta.getTables(null, null, null, null);
            selectTable.removeAllItems();
            while (res.next())
            {

                selectTable.addItem(res.getString("TABLE_NAME"));
            }
             res.close();
            return true;
        }catch(Exception e) {
            return false;
        }
    }

    public boolean makeMyTable() {
        try {
            System.out.println("Creating table");
        _statement=_connection.createStatement();
        _statement.executeUpdate("use "+selectDB.getSelectedItem()+";");
        String sql1="CREATE TABLE "+txtTableName.getText()+ " ("
                    +txtCloumnName1.getText()+
                    " "
                    +txtColumnType1.getText()+
                    "(30),"
                    +txtCloumnName2.getText()+
                    " "
                    +txtColumnType2.getText()+
                    "(30),"
                    +txtCloumnName3.getText()+
                    " "
                    +txtColumnType3.getText()+
                    "(30));";

        _statement.executeUpdate(sql1);
        _connection.close();
        getTable();
        return true;
        }catch(Exception e) {
            return false;
        }
    }

    @Override
    public void actionPerformed(ActionEvent event) {
        if(event.getSource()==createDBButton) {
            try{
                _statement = _connection.createStatement();
                String sql = "CREATE DATABASE "+txtDBName.getText()+" ;";
                _statement.executeUpdate(sql);
                 addDBdata();
            }
            catch(Exception e){
                e.printStackTrace();
            }
        }

        if(event.getSource()==createTableButton)
        {
                System.out.println("LOL table");

                if(makeMyTable()) {
                    System.out.println("Sucessfull Added To "+selectDB.getSelectedItem()+" Table ");
                }else {
                    System.out.println("Failed Adding To Table ");
                }

                if(getTable()) {
                    System.out.println("Got Table");
                }else {
                    System.out.println("Failed Adding To Table ");
                }



        }if(event.getSource()==InsertDataButton) {
            addDataToTable();
        }

    }

    public void addDataToTable() {
        try
        {
            _statement=_connection.createStatement();
            _statement.executeUpdate("use "+selectDB.getSelectedItem()+";");
            System.out.println("Selected Table: "+selectTable.getSelectedItem());

            String sql2="INSERT INTO "+selectTable.getSelectedItem()+ " VALUES('"
                        +InsertData1.getText()+
                        "','"
                        +InsertData2.getText()+
                        "','"
                        +InsertData3.getText()+
                        "');";

            _statement.executeUpdate(sql2);

        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }
    public void addDBdata() {
        try {


        DatabaseMetaData meta = _connection.getMetaData();
        ResultSet res = meta.getCatalogs();// gets all the database names

        while (res.next()){
           selectDB.addItem(res.getString("TABLE_CAT"));

        }
        res.close();
        }catch(Exception e) {
            System.out.println("Exception Occured");
        }
    }
}