package zadatak22;

import java.awt.CardLayout;
import java.awt.Dimension;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.swing.*;


public class Zadatak22 {

	static {
		try {
			Class.forName("com.ibm.db2.jcc.DB2Driver");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// Stampanje greske
	public static void printError (SQLException e){
		if (e.getErrorCode() == -911 || e.getErrorCode() == -913)
			System.out.println("Objekat je zakljucan od strane druge transakcije. Sacekajte.\n");
		else
			System.out.println("SQLCODE: " + e.getErrorCode() + "\nSQLSTATE: " + e.getSQLState() +
					"\nMessage: " + e.getMessage());
	}

	public static void main(String[] args) {

		final JFrame frame = new JFrame();
		frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		frame.setTitle("zadatak-22");

		final JPanel cards = new JPanel(new CardLayout());

		final JPanel card1 = new JPanel();
		final JPanel c1panel1 = new JPanel();
		final JPanel c1panel2 = new JPanel();
		final JPanel c1panel3 = new JPanel();
		card1.setLayout(new BoxLayout(card1, BoxLayout.Y_AXIS));

		final JPanel card2 = new JPanel();
		final JPanel c2panel1 = new JPanel();
		final JPanel c2panel2 = new JPanel();
		c2panel2.setLayout(new BoxLayout(c2panel2, BoxLayout.Y_AXIS));
		final JPanel c2panel21 = new JPanel();
		final JPanel c2panel22 = new JPanel();
		final JPanel c2panel23 = new JPanel();
		final JPanel c2panel24 = new JPanel();
		final JPanel prijave = new JPanel();
		prijave.setLayout(new BoxLayout(prijave, BoxLayout.Y_AXIS));

		final JTextField c2bodoviPis = new JTextField();
		final JTextField c2bodoviUs = new JTextField();
		final JTextField c2ocena = new JTextField();
		final JLabel polje2 = new JLabel();
		final JLabel poljeIndeks = new JLabel();
		final JLabel labelBodoviPis = new JLabel("Bodovi sa pismenog dela");
		final JLabel labelBodoviUs = new JLabel("Bodovi sa usmenog dela");
		final JLabel labelOcena = new JLabel("Ocena");
		card2.setLayout(new BoxLayout(card2, BoxLayout.Y_AXIS));

		final JButton dugmePrijave = new JButton("Prikazi prijave");
		final JButton dugmeIzadji = new JButton("Izadji");
		final JButton dugmePrekid = new JButton("Prekid");
		final JButton dugmeUnos = new JButton("Unos");

		List<String> smerovi = new ArrayList<>();
		List<String> predmeti = new ArrayList<>();
		List<String> rokovi = new ArrayList<>();

		final String url = "jdbc:db2://localhost:50001/VSTUD";
		final String username = "student";
		final String password = "abcdef";
		try{
			Connection con = DriverManager.getConnection(url, username, password);

			// Izvlacenje smerova, predmeta i rokova iz baze
			Statement st = con.createStatement();
			String sql1 = "select naziv from smer";
			String sql2 = "select naziv from predmet";
			String sql3 = "select naziv from ispitni_rok";

			ResultSet rs = st.executeQuery(sql1);
			while(rs.next()){
				smerovi.add(rs.getString(1).trim());
			}
			rs = st.executeQuery(sql2);
			while(rs.next()){
				predmeti.add(rs.getString(1).trim());
			}
			rs = st.executeQuery(sql3);
			while(rs.next()){
				rokovi.add(rs.getString(1).trim());
			}

			rs.close();
			st.close();
			con.close();
		}
		catch (SQLException e){
			printError(e);
		}
		catch (Exception e) {
			e.printStackTrace();
		}

		// Popunjavanje dropdown-a
		final JComboBox<Object> cbSmer = new JComboBox<Object>(smerovi.toArray());
		final JComboBox<Object> cbPredmet = new JComboBox<Object>(predmeti.toArray());
		final JComboBox<Object> cbIspitniRok = new JComboBox<Object>(rokovi.toArray());

		//Postavljnje velicina komponenti
		Dimension dugmeVelicina = new Dimension(200, 25);
		cbSmer.setPreferredSize(dugmeVelicina);
		cbPredmet.setPreferredSize(dugmeVelicina);
		cbIspitniRok.setPreferredSize(dugmeVelicina);
		dugmePrijave.setPreferredSize(dugmeVelicina);
		dugmeIzadji.setPreferredSize(dugmeVelicina);

		Dimension poljeVelicina = new Dimension(25, 25);
		c2bodoviPis.setPreferredSize(poljeVelicina);
		c2bodoviUs.setPreferredSize(poljeVelicina);
		c2ocena.setPreferredSize(poljeVelicina);

		// Postavljanje dropdown-a na nedefinisano
		cbSmer.setSelectedIndex(-1);
		cbPredmet.setSelectedIndex(-1);
		cbIspitniRok.setSelectedIndex(-1);

		//Dodajemo komponente na panel
		c1panel1.add(cbSmer);
		c1panel1.add(cbPredmet);
		c1panel1.add(cbIspitniRok);
		c1panel2.add(dugmePrijave);
		c1panel2.add(dugmeIzadji);
		c1panel3.add(prijave);

		card1.add(c1panel1);
		card1.add(c1panel2);
		card1.add(c1panel3);

		c2panel1.add(dugmePrekid);
		c2panel1.add(dugmeUnos);
		c2panel21.add(polje2);
		c2panel22.add(labelBodoviPis);
		c2panel22.add(c2bodoviPis);
		c2panel23.add(labelBodoviUs);
		c2panel23.add(c2bodoviUs);
		c2panel24.add(labelOcena);
		c2panel24.add(c2ocena);

		c2panel2.add(c2panel21);
		c2panel2.add(c2panel22);
		c2panel2.add(c2panel23);
		c2panel2.add(c2panel24);

		card2.add(c2panel1);
		card2.add(c2panel2);

		cards.add(card1, "card-1");
		cards.add(card2, "card-2");
		final CardLayout cardLayout = (CardLayout) cards.getLayout();

		// Event za dugme za listanje prijava
		dugmePrijave.addActionListener(new ActionListener() {

			@Override
			public void actionPerformed(ActionEvent event) {
				try {
					Connection con = DriverManager.getConnection(url, username, password);
					int uspesno = 0;

					// Izvlacenje vrednosti iz dropdown-a
					String smer = (String)cbSmer.getSelectedItem();
					String predmet = (String)cbPredmet.getSelectedItem();
					String irok = (String)cbIspitniRok.getSelectedItem();

					String sql = null;
					PreparedStatement prep;
					ResultSet rs = null;

					//Provera da li su izabrane pojedinacne stavkee
					if(cbSmer.getSelectedIndex() == -1){
						JOptionPane.showMessageDialog(cards, "Nije izabran smer!");
						cbSmer.requestFocus();
					}
					else if(cbPredmet.getSelectedIndex() == -1){
						JOptionPane.showMessageDialog(cards, "Nije izabran predmet!");
						cbPredmet.requestFocus();
					}
					else if(cbIspitniRok.getSelectedIndex() == -1){
						JOptionPane.showMessageDialog(cards, "Nije izabran ispitni rok!");
						cbIspitniRok.requestFocus();
					}
					else{ // Izabrane su sve stavke
						sql = "select d.ime, d.prezime, d.indeks, i.bodovi_pismenog, i.bodovi_usmenog, i.ocena " +
							"from ispit i join dosije d on d.indeks = i.indeks " +
								"join predmet p on p.id_predmeta = i.id_predmeta " +
								"join smer sm on sm.id_smera = d.id_smera " +
								"join ispitni_rok ir on ir.oznaka = i.oznaka_roka " +
									"and ir.godina = i.godina_roka " +
							"where sm.naziv = ? and p.naziv = ? and ir.naziv = ? " +
								"and i.status_prijave in ('o', 'p')";


						// Pripremanje upita
						prep = con.prepareStatement(sql);
						prep.setString(1, smer);
						prep.setString(2, predmet);
						prep.setString(3, irok);

						// Hvatanje exceptiona u slucaju visekorisnickog okruzenja
						try {
							rs = prep.executeQuery();
							uspesno = 1;
						} catch (SQLException e) {
							if (e.getErrorCode() == -911 || e.getErrorCode() == -913) {
								JOptionPane.showMessageDialog(cards, "Objekat je zakljucan od strane druge" +
									" transakcije. Sacekajte.");
								con.rollback();
								System.out.println("Rollback");
							}
							else {
								System.out.println("SQLCODE: " + e.getErrorCode() + "\nSQLSTATE: " + e.getSQLState() +
										"\nMessage: " + e.getMessage());
							}
						}
						catch (Exception e) {
							e.printStackTrace();
						}

						// Brisanje prethodnog sadrzaja liste prijava
						frame.setResizable(true);
						prijave.removeAll();
						prijave.revalidate();
						prijave.repaint();

						if(uspesno == 1){
							int i = 1; // broj studenata u panelu zbog podesavanja velicine prozora
							while(rs.next()){
								final String ime = rs.getString(1).trim();
								final String prezime = rs.getString(2).trim();
								final String indeks = rs.getString(3);
								final String bodoviPis = rs.getString(4);
								final String bodoviUs = rs.getString(5);
								final String ocena = rs.getString(6);
								JLabel polje = new JLabel(ime + " " + prezime + " " + indeks);
								polje.addMouseListener(new MouseAdapter(){
						            @Override
						            public void mouseClicked(MouseEvent e){
						            	polje2.setText(ime + " " + prezime + "\n");
						            	poljeIndeks.setText(indeks);
						            	c2bodoviPis.setText(bodoviPis);
						            	c2bodoviUs.setText(bodoviUs);
						            	c2ocena.setText(ocena);

						            	cardLayout.show(cards, "card-2");
						            }
						        });
								prijave.add(polje);

								c1panel3.setSize(170, 15*i);
								frame.setSize(620, 15*i+150);
								i++;
							}
						}

						rs.close();
						prep.close();
						con.close();
					}
				} catch (SQLException e) {
					printError(e);
				}
				catch (Exception e) {
					e.printStackTrace();
				}
			}
		});

		dugmeIzadji.addActionListener(new ActionListener() {
			@Override
			public void actionPerformed(ActionEvent event) {
				System.exit(0);
			}
		});
		dugmePrekid.addActionListener(new ActionListener() {
			@Override
			public void actionPerformed(ActionEvent event) {
				cardLayout.show(cards, "card-1");
			}
		});
		dugmeUnos.addActionListener(new ActionListener() {
			@Override
			public void actionPerformed(ActionEvent event) {
				//Provera da li su popunjena pojedinacna polja
				if(c2bodoviPis.getText().equals("")){
					JOptionPane.showMessageDialog(cards, "Polje za bodove sa pismenog je prazno!");
					c2bodoviPis.requestFocus();
				}
				else if(c2bodoviUs.getText().equals("")){
					JOptionPane.showMessageDialog(cards, "Polje za bodove sa usmenog je prazno!");
					c2bodoviUs.requestFocus();
				}
				else if(c2ocena.getText().equals("")){
					JOptionPane.showMessageDialog(cards, "Polje za ocenu je prazno!");
					c2ocena.requestFocus();
				}
				else { // Sva tri polja su popounjena
					String sql = null;
					PreparedStatement prep;
					int uspesno = 0;

					try {
						Connection con = DriverManager.getConnection(url, username, password);
						con.setAutoCommit(false);

						sql = 	"update ispit i1 " +
								"set i1.bodovi_pismenog = ?, i1.bodovi_usmenog = ?, i1.bodovi = ?, i1.ocena = ? " +
								"where exists (select i2.indeks " +
												"from ispit i2 " +
													"join dosije d on d.indeks = i2.indeks " +
													"join predmet p on p.id_predmeta = i2.id_predmeta " +
													"join smer sm on sm.id_smera = d.id_smera " +
													"join ispitni_rok ir on ir.oznaka = i2.oznaka_roka " +
														"and ir.godina = i2.godina_roka " +
												"where sm.naziv = ? and p.naziv = ? and ir.naziv = ? and i2.indeks = ? " +
													"and i1.indeks = i2.indeks " +
													"and i1.godina_roka = i2.godina_roka " +
													"and i1.oznaka_roka = i2.oznaka_roka " +
													"and i1.id_predmeta = i2.id_predmeta)";

						// Pripremanje upita
						prep = con.prepareStatement(sql);
						int bodPis = Integer.parseInt(c2bodoviPis.getText());
						int bodUs = Integer.parseInt(c2bodoviUs.getText());
						prep.setInt(1, bodPis);
						prep.setInt(2, bodUs);
						prep.setInt(3, bodPis + bodUs);
						prep.setInt(4, Integer.parseInt(c2ocena.getText()));
						prep.setString(5, (String)cbSmer.getSelectedItem());
						prep.setString(6, (String)cbPredmet.getSelectedItem());
						prep.setString(7, (String)cbIspitniRok.getSelectedItem());
						prep.setInt(8, Integer.parseInt(poljeIndeks.getText()));

						// Hvatanje exceptiona u slucaju visekorisnickog okruzenja
						try {
							prep.executeUpdate();
							JOptionPane.showMessageDialog(cards, "Podaci obradjeni!");
							uspesno = 1;
						} catch (SQLException e) {
							if (e.getErrorCode() == -911 || e.getErrorCode() == -913) {
								JOptionPane.showMessageDialog(cards, "Objekat je zakljucan od strane druge" +
									" transakcije. Sacekajte.");
								con.rollback();
								System.out.println("Rollback");
							}
							else {
								System.out.println("SQLCODE: " + e.getErrorCode() + "\nSQLSTATE: " + e.getSQLState() +
										"\nMessage: " + e.getMessage());
							}
						}
						catch (Exception e) {
							e.printStackTrace();
						}

						// Commit samo ako je uspeo upit
						if (uspesno == 1) con.commit();
						prep.close();
						con.close();

					} catch (SQLException e) {
						printError(e);
					}
					catch (Exception e) {
						e.printStackTrace();
					}
				}
			}
		});

		frame.add(cards);
		frame.pack();
		frame.setVisible(true);
		frame.setLocationRelativeTo(null);
	}

}