import java.awt.Container;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

public class Testing extends JFrame {
    private static final long serialVersionUID = 1L;

    Connection connection;
    Statement state;
    ResultSet result;
    ResultSetMetaData metaData;

    private String url = "jdbc:mysql://localhost:3306/tsz";
    private String user = "root";
    private String password = "";
    private String timezone = "?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Europe/Berlin";

    private List<String> list;
    private Container container;
    private JPanel panel;
    private JTable table;
    private DefaultTableModel model;

    public Testing(){
        setSize(800, 600);
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        setLocationRelativeTo(null);
        gui();
    }

    public void gui() {
        container = this.getContentPane();
        panel = new JPanel();
        table = new JTable();
    }

    // connect to database
    public Connection getConnection() throws ClassNotFoundException, SQLException{
        Class.forName("com.mysql.cj.jdbc.Driver");
        connection = DriverManager.getConnection(url+timezone, user, password);
        return connection;
    }

    // try to build some tablemodel :(
    public void run() throws ClassNotFoundException, SQLException {
        model = new DefaultTableModel() {
            public boolean isCelleditable(int row, int column) {
                return !(column == 0);
            }
            public Class<?> getColumnClass (int column) {
                return Boolean.class;
            }
        };

        // to get a metadata from database for column count
        String select = "SELECT * FROM sortierung";
        list = new ArrayList<String>(); // to save dates from result.next();
        state = getConnection().createStatement();
        result = state.executeQuery(select);
        metaData = result.getMetaData();

        for(int j=2; j<=metaData.getColumnCount(); j++) {
            if(j==2) {
                model.addColumn("");
            }
            model.addColumn(metaData.getColumnName(j));
        }

        int prioritaet = 1; // priority beginns with 1, then 1++
        int row = 1;
        for(int i=3; i<=metaData.getColumnCount(); i++) { // begin with name of lessons i=3 (i=1 is ID, i=2 is names of users)
            while(prioritaet <= 9) {
            String sql = "SELECT * FROM sortierung WHERE "+metaData.getCatalogName(i)+" = "+prioritaet+";";
            state = getConnection().createStatement();
            result = state.executeQuery(sql);

            // if priority is among this numbers, the name of user should be put on first row (=morning)
            if(prioritaet==1 || prioritaet==3 || prioritaet==5 || prioritaet==7) {
                while(result.next()) {
                    list.add(result.getString("name"));
                    if(list.size()>=5) {  // i can put only a certain number of names into cell
                        model.setValueAt(list.toString(), row, i);
                        list.clear();// arter putting dates into cell i remove all dates in list, because i will use it for next loop
                    }
                }

                if(!list.isEmpty()) {
                    model.setValueAt(list.toString(), row, i);
                    list.clear();
                    prioritaet++;
                    break;
                }
                prioritaet++;
                break;
            }

            // if priority is among this numbers, the name of user should be put on second row (=afternoon)
            if(prioritaet==2 || prioritaet==4 || prioritaet==6 || prioritaet==8) {
                while(result.next()) {
                    list.add(result.getString("name"));
                    if(list.size()>=5) {
//                      model.setValueAt(list, 1, column);
                        list.clear();
                        break;
                    }
                }
                if(!list.isEmpty()) {
//                  model.setValueAt(list, row, column);
                    list.clear();
                    prioritaet++;
                    break;
                }
                prioritaet++;
                break;
            }

            if(prioritaet==9) { // if priority is 9, the programm should to put it into one cell where the user doesn't have any lesson
                while(result.next()) {
                    list.add(result.getString("name"));
                    if(list.size()>=5) {
//                      model.setValueAt(list, row, column);
                        list.clear();
                        break;
                    }
                }
                if(!list.isEmpty()) {
//                  model.setValueAt(list, row, column);
                    list.clear();
                    prioritaet++;
                    break;
                }
                prioritaet++;
                break;
            }
        }
    }
        table.setModel(model);
}

    public static void main (String[] args) {
        Testing test = new Testing();
        test.setVisible(true);
        }
    }