package pzlab4;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.table.AbstractTableModel;
import java.awt.Dimension;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.io.FileNotFoundException;
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.Scanner;

public class App extends JPanel implements ActionListener {
	JTable table = new JTable(new MyTableModel());
	public JButton b1 = new JButton("Refresh");
	private static ArrayList<DaneTabeli> datax = new ArrayList<DaneTabeli>();
	public static JFrame frame = new JFrame("TABELA");
	public static App test = new App();
	public static String adres, port, user, baza, password, url;
	// polacz z baza
	public Connection connect(String url, String user, String password) {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(url, user, password);
			System.out.println("Connected to the PostgreSQL server successfully.");
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}

		return conn;
	}

	public App() {
		super(new GridLayout(2, 0));
		b1.addActionListener(this);

		table.setPreferredScrollableViewportSize(new Dimension(500, 70));
		table.setFillsViewportHeight(true);

		// Create the scroll pane and add the table to it.
		JScrollPane scrollPane = new JScrollPane(table);
		// Add the scroll pane to this panel.
		add(scrollPane);
		add(b1);

	}

	// model tablicy
	class MyTableModel extends AbstractTableModel {

		public String[] columnNames = { "kraj", "przychod" };
		public ArrayList<DaneTabeli> data = datax;

		public int getColumnCount() {
			return columnNames.length;
		}

		public int getRowCount() {
			return data.size();
		}

		public String getColumnName(int col) {
			return columnNames[col];
		}

		public void SetDatax(int i, String x, int y) {
			DaneTabeli element = new DaneTabeli(x, y);
			data.set(i, element);
		}

		public Object getValueAt(int row, int col) {
			if (col == 0) {
				return data.get(row).kraj;
			} else {
				return data.get(row).przych;
			}
		}

	}

	// stworz gui
	private static void createAndShowGUI() {
		// Create and set up the window.

		frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		frame.setSize(800, 800);

		// Create and set up the content pane.
		App newContentPane = new App();
		newContentPane.setOpaque(true); // content panes must be opaque
		frame.setContentPane(newContentPane);

		// Display the window.
		frame.pack();
		frame.setVisible(true);
	}

	// pobiera dane z bazy za pomoca zapytania
	public void pobierzdane(String url, String user, String password) {

		String SQL = "SELECT customers.country kraj, SUM(orders.netamount) przychody "
				+ "FROM customers INNER JOIN orders ON customers.customerid=orders.customerid "
				+ "WHERE orders.orderdate BETWEEN '2004-01-01' AND '2004-06-01' "
				+ "GROUP BY kraj ORDER BY przychody  DESC ";

		try (Connection conn = connect(url, user, password);
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(SQL)) {
			zaladujdane(rs);
		} catch (SQLException ex) {
			System.out.println(ex.getMessage());
		}
	}

	// dodaje dane do listy
	private void zaladujdane(ResultSet rs) throws SQLException {
		while (rs.next()) {
			DaneTabeli x = new DaneTabeli(rs.getString("kraj"), rs.getInt("przychody"));
			datax.add(x);

		}
	}

	public static void main(String[] args) throws FileNotFoundException {
		Scanner odczyt = new Scanner(new File("dane.txt"));
		adres = odczyt.nextLine();
		port = odczyt.nextLine();
		baza = odczyt.nextLine();
		user = odczyt.nextLine();
		password = odczyt.nextLine();
		url = "jdbc:postgresql://" + adres + ":" + port + "/" + baza;

		test.pobierzdane(url, user, password);
		javax.swing.SwingUtilities.invokeLater(new Runnable() {
			public void run() {
				createAndShowGUI();
			}
		});
	}

	// ponownie laczy z baza i odswierza dane w tablicy
	@Override
	public void actionPerformed(ActionEvent e) {
		datax.clear();
		test.pobierzdane(url, user, password);
		for (int i = 0; i < datax.size(); i++) {
			table.getModel().setValueAt(datax.get(i).kraj, i, 0);
			table.getModel().setValueAt(datax.get(i).przych, i, 1);
		}
		frame.setVisible(false);
		frame.setVisible(true);
	}

}