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

public class Exercise2 extends JFrame implements ActionListener
{
    JPanel panMain, panTopic, panName, panMatrix, panClass;
    JPanel panSpace, panSpace2, panSpace3;
    JPanel panAction, panAction1, panAction2;
    JPanel panView;
    JLabel lblName, lblMatrix, lblTopic, lblClass;
    JLabel lblSpace, lblSpace2, lblSpace3;
    JTextField txtName, txtMatrix;
    JTextArea txtView;
    JComboBox cbClass;
    JButton btnSubmit, btnUpdate, btnView, btnDelete;

    Connection con;
    Statement stmt;
    ResultSet rs;
    String output;

    public void connectDB() throws Exception
    {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String path = "jdbc:mysql://localhost/student2?useTimezone=true&serverTimezone=UTC";
        con = DriverManager.getConnection(path, "root", "abcDEF123");
        System.out.println("Database connected");

        stmt = con.createStatement();
        String sql = "CREATE TABLE STUDENT" + "(name VARCHAR(50), matrix VARCHAR(50), class VARCHAR(50))";
        stmt.executeUpdate(sql);
        System.out.println("Table created");
    }

    public Exercise2()
    {
        super("Exercise 2");

        try{
            connectDB();
        }
        catch(Exception e){
            JOptionPane.showMessageDialog(null,"Database not connected");
        }

        panTopic = new JPanel();
        panTopic.setLayout(new FlowLayout(FlowLayout.CENTER));

        lblTopic = new JLabel("STUDENT REGISTRATION");
        lblTopic.setFont(new Font("Arial",Font.BOLD, 24));
        panTopic.add(lblTopic);

        panName = new JPanel();
        panName.setLayout(new FlowLayout(FlowLayout.LEFT));

        lblName = new JLabel("Name");
        lblName.setFont(new Font("Arial",Font.BOLD,12));
        panName.add(lblName);

        txtName = new JTextField(20);
        txtName.setFont(new Font("Arial",Font.BOLD,12));
        panName.add(txtName);

        panMatrix = new JPanel();
        panMatrix.setLayout(new FlowLayout(FlowLayout.LEFT));

        lblMatrix = new JLabel("Matrix");
        lblMatrix.setFont(new Font("Arial",Font.BOLD,12));
        panMatrix.add(lblMatrix);

        txtMatrix = new JTextField(20);
        txtMatrix.setFont(new Font("Arial",Font.BOLD,12));
        panMatrix.add(txtMatrix);

        panClass = new JPanel();
        panClass.setLayout(new FlowLayout(FlowLayout.LEFT));

        lblClass = new JLabel("Class");
        lblClass.setFont(new Font("Arial",Font.BOLD,12));
        panClass.add(lblClass);

        String classList[] = {"DDTP5", "DDTS5", "DDTN5"};
        cbClass = new JComboBox(classList);
        cbClass.setFont(new Font("Arial", Font.BOLD, 12));
        panClass.add(cbClass);

        panAction = new JPanel();
        panAction.setLayout(new BoxLayout(panAction, BoxLayout.Y_AXIS));

        panAction1 = new JPanel();
        panAction1.setLayout(new FlowLayout(FlowLayout.CENTER));

        panAction2 = new JPanel();
        panAction2.setLayout(new FlowLayout(FlowLayout.CENTER));


        btnSubmit = new JButton("SUBMIT");
        btnSubmit.setFont(new Font("Arial",Font.BOLD,12));
        panAction1.add(btnSubmit);

        btnUpdate = new JButton("UPDATE");
        btnUpdate.setFont(new Font("Arial",Font.BOLD,12));
        panAction1.add(btnUpdate);

        btnView = new JButton("VIEW");
        btnView.setFont(new Font("Arial",Font.BOLD,12));
        panAction2.add(btnView);

        btnDelete = new JButton("DELETE");
        btnDelete.setFont(new Font("Arial",Font.BOLD,12));
        panAction2.add(btnDelete);

        btnSubmit.addActionListener(this);
        btnUpdate.addActionListener(this);
        btnView.addActionListener(this);
        btnDelete.addActionListener(this);

        panAction.add(panAction1);
        panAction.add(panAction2);

        txtView = new JTextArea(10,50);
        txtView.setFont(new Font("Arial",Font.BOLD,12));

        panView = new JPanel();
        panView.setLayout(new FlowLayout(FlowLayout.CENTER));
        panView.add(txtView);


        panSpace = new JPanel();
        panSpace.setLayout(new FlowLayout(FlowLayout.CENTER));

        lblSpace = new JLabel("\t");
        lblSpace.setFont(new Font("Arial",Font.BOLD, 20));
        panSpace.add(lblSpace);

        panSpace2 = new JPanel();
        panSpace2.setLayout(new FlowLayout(FlowLayout.CENTER));

        lblSpace2 = new JLabel("\t");
        lblSpace2.setFont(new Font("Arial",Font.BOLD, 20));
        panSpace2.add(lblSpace2);


        panMain = new JPanel();
        panMain.setLayout(new BoxLayout(panMain, BoxLayout.Y_AXIS));
        panMain.add(panTopic);
        panMain.add(panSpace);
        panMain.add(panName);
        panMain.add(panMatrix);
        panMain.add(panClass);
        panMain.add(panSpace2);
        panMain.add(panAction);
        panMain.add(panView);
        panTopic.setBackground(Color.YELLOW);
        panSpace.setBackground(Color.YELLOW);
        panName.setBackground(Color.YELLOW);
        panMatrix.setBackground(Color.YELLOW);
        panClass.setBackground(Color.YELLOW);
        panSpace2.setBackground(Color.YELLOW);
        panAction.setBackground(Color.YELLOW);
        panAction1.setBackground(Color.YELLOW);
        panAction2.setBackground(Color.YELLOW);
        panView.setBackground(Color.YELLOW);
        panMain.setBackground(Color.YELLOW);
        add(panMain);

        getContentPane().setBackground(Color.YELLOW);
        setLayout(new FlowLayout());
        setSize(700,700);
        setVisible(true);
        setLocationRelativeTo(null);
    }

    public static void main(String[]args)
    {
        new Exercise2().setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    }

    public void insertData() throws Exception
    {
        String name = txtName.getText();
        String matrix = txtMatrix.getText();
        Object classList = cbClass.getSelectedItem();

        stmt = con.createStatement();
        String sql = "INSERT INTO STUDENT VALUES" + "('"+name+"', '"+matrix+"', '"+classList+"')";
        stmt.executeUpdate(sql);
        JOptionPane.showMessageDialog(null,"Data successfully inserted!");

    }

    public void viewData() throws Exception
    {
        stmt = con.createStatement();
        String sql = "SELECT * FROM STUDENT";
        rs = stmt.executeQuery(sql);
        output = "Name\t Matrix\t Class\n";
        while(rs.next())
        {
            output = output + rs.getString("name") + "\t" + rs.getString("matrix") + "\t" + rs.getString("class") + "\n";
        }

    }


    public void actionPerformed(ActionEvent ae)
    {
        if(ae.getSource()==btnSubmit)
        {
            String name = txtName.getText();
            String matrix = txtMatrix.getText();
            Object classList = cbClass.getSelectedItem();

            if(name=="" || matrix=="" || classList=="")
            {
                JOptionPane.showMessageDialog(null,"Please complete the details!");
            }
            else
            {
                try{
                    insertData();
                }
                catch(Exception e){
                    JOptionPane.showMessageDialog(null,"Error");
                }

            }
        }

        else if(ae.getSource()==btnUpdate)
        {
            JOptionPane.showMessageDialog(null,"Update");
        }

        else if(ae.getSource()==btnView)
        {
            try{
                viewData();
                txtView.setText(output);
            }
            catch(Exception e){
                JOptionPane.showMessageDialog(null,"No data!");
            }
        }

        else if(ae.getSource()==btnDelete)
        {
            JOptionPane.showMessageDialog(null,"Delete");
        }
    }
}