package com.albums;

import javax.swing.*;
import javax.swing.event.TableModelEvent;
import javax.swing.event.TableModelListener;
import javax.swing.tree.ExpandVetoException;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.io.IOException;
import java.sql.*;

/**
 * Created by fakepotato on 10/19/15.
 */

public class MainForm extends JFrame implements ActionListener,
        TableModelListener {
    /**
     * Name of main table.
     */
    private static final String TABLE_NAME = "albums";

    /**
     * For connection to db.
     */
    private static final String
            URL = "jdbc:sqlite:identifier.sqlite",
            URL_PG = "jdbc:postgresql://localhost:5432/lab4",
            NAME = "postgres",
            PASS = "root";


    /**
     * Default size of list.
     */
    private static final int DEFAULT_SIZE = 5;
    /**
     * Default year.
     */
    private static final int DEFAULT_YEAR = 1980;
    /**
     * list of albums.
     */
    private final AlbumList albumList;
    /**
     * button, button and 3 buttons.
     */
    private final JButton loadButton, saveButton,
            exitButton, addButton, deleteButton;
    /**
     * Combo box.
     */
    private final JComboBox<String> comboBox;
    /**
     * Main table.
     */
    private final JTable table;
    /**
     * Constructor of form.
     */

    public MainForm() {
        super("Albums");
        setDefaultCloseOperation(WindowConstants.DO_NOTHING_ON_CLOSE);
        addWindowListener(new WindowAdapter() {
            @Override
            public void windowClosing(final WindowEvent e) {
                super.windowClosing(e);
                if (albumList.isDataSaved()) {
                    Runtime.getRuntime().exit(0);
                } else {
                    askSaving();
                }
            }
        });

        albumList = new AlbumList();
        for (int i = 0; i < DEFAULT_SIZE; i++) {
            albumList.add(new Album("Title" + i, "Band " + i,
                    DEFAULT_YEAR + 1));
        }

        albumList.addTableModelListener(this);
        table = new JTable(albumList);
        table.getColumnModel().getColumn(2).setCellEditor(
                new YearCellEditor(new JTextField()));
        getContentPane().add(new JScrollPane(table));
        JPanel panel = new JPanel();
        panel.setLayout(new FlowLayout());
        comboBox = new JComboBox<>(new String[]{"SQLite", "PostgreSQL"});
        saveButton = new JButton("Save");
        saveButton.addActionListener(this);
        loadButton = new JButton("Load");
        loadButton.addActionListener(this);
        exitButton = new JButton("Exit");
        exitButton.addActionListener(this);
        addButton = new JButton("Add");
        addButton.addActionListener(this);
        deleteButton = new JButton("Remove");
        deleteButton.addActionListener(this);
        panel.add(comboBox);
        panel.add(exitButton);
        panel.add(addButton);
        panel.add(deleteButton);
        panel.add(loadButton);
        panel.add(saveButton);
        getContentPane().add(panel, BorderLayout.SOUTH);
        pack();
        setLocationRelativeTo(null);
        setVisible(true);
    }

    @Override
    public final void actionPerformed(final ActionEvent e) {
        if (e.getSource().equals(exitButton)) {
            if (albumList.isDataSaved()) {
                Runtime.getRuntime().exit(0);
            } else {
                askSaving();
            }
        } else if (e.getSource().equals(addButton)) {
            albumList.add(new Album("Title", "Band", DEFAULT_YEAR));
        } else if (e.getSource().equals(deleteButton)) {
            if (table.getSelectedRowCount() == 1) {
                albumList.remove(albumList.get(table.getSelectedRow()));
            }
        } else {
            if (e.getSource().equals(loadButton)) {
                if(comboBox.getSelectedIndex() == 0) {
                    loadFromSQLite();
                } else {
                    loadFromPostgres();
                }
            } else if (e.getSource().equals(saveButton)) {
                if (comboBox.getSelectedIndex() == 0) {
                    saveToSQLite();
                } else {
                    saveToPostgres();
                }
            }
        }
    }

    @Override
    public final void tableChanged(final TableModelEvent e) {
        saveButton.setEnabled(!albumList.isDataSaved());
        deleteButton.setEnabled(albumList.count() > 0);
    }

    /**
     * Ask user for saving changes.
     */
    private void askSaving() {
        switch (JOptionPane.showConfirmDialog(this,
                "Data has changed. Save it?",
                "Confirmation", JOptionPane.YES_NO_CANCEL_OPTION)) {
            case JOptionPane.YES_OPTION:
                saveButton.doClick();
            case JOptionPane.NO_OPTION:
                Runtime.getRuntime().exit(0);
                break;
            default:
                //
        }
    }

    /**
     * Load data from SQLite.
     */
    private void loadFromSQLite() {
        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection(URL);
            statement = connection.createStatement();
            // Load from base
            ResultSet resultSet = statement.executeQuery("select * from " + TABLE_NAME);

            //albumList.clear();
            while (resultSet.next()) {
                albumList.add(new Album(
                        resultSet.getString("Title"),
                        resultSet.getString("Band"),
                        resultSet.getInt("Year")));
            }
            table.updateUI();
            JOptionPane.showMessageDialog(this, "Data loaded from SQLite");
        } catch (Exception e) {
            JOptionPane.showMessageDialog(this, e.getMessage());
        }
    }

    /**
     * Save data to SQLite.
     */
    private void saveToSQLite() {
        // create driver & connection
        Connection connection = null;
        // 2 statements for save & load
        Statement statement = null;
        PreparedStatement preStatement = null;
        try {
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection(URL);
            statement = connection.createStatement();
            preStatement = connection.prepareStatement("DROP TABLE IF EXISTS " + TABLE_NAME + ";");
            preStatement.executeUpdate();

            String queryCreateTable = "CREATE TABLE " + TABLE_NAME +
                    "(Title text," +
                    "Band text," +
                    "Year int);";

            preStatement = connection.prepareStatement(queryCreateTable);
            preStatement.executeUpdate();

            String queryInsertDataFromAlbums =
                    "INSERT INTO " + TABLE_NAME +
                            " (Title, Band, Year) VALUES (?,?,?);";

            preStatement = connection.prepareStatement(queryInsertDataFromAlbums);

            for (Album a : albumList) {
                preStatement.setString(1, a.getTitle());
                preStatement.setString(2, a.getBand());
                preStatement.setInt(3, a.getReleaseYear());

                preStatement.executeUpdate();
            }
            albumList.onStateChanged();
            JOptionPane.showMessageDialog(this, "Data saved to SQLite db.");

        } catch (Exception e) {
            JOptionPane.showMessageDialog(this, e.getMessage());
        }
    }

    /**
     * Load data from PostgreSQL
     */
    private void loadFromPostgres(){
        // create driver & connection
        Connection connection = null;
        // 2 statements for save & load
        Statement statement = null;
        try {
            Class.forName("org.postgresql.Driver");
            connection = DriverManager.getConnection(URL_PG, NAME, PASS);
            statement = connection.createStatement();
            // load from base
            ResultSet resultSet = statement.executeQuery("select * from " + TABLE_NAME);

            //albumList.clear();
            while (resultSet.next()) {
                albumList.add(new Album(
                        resultSet.getString("Title"),
                        resultSet.getString("Band"),
                        resultSet.getInt("Year")));
            }
            table.updateUI();
            JOptionPane.showMessageDialog(this, "Data loaded from PostgreSQL db.");
        } catch (Exception e) {
            JOptionPane.showMessageDialog(this, e.getMessage());
        }
    }
    /**
     * Save data to PostgreSQL
     */
    private void saveToPostgres() {
        // create driver & connection
        Connection connection = null;
        // 2 statements for save & load
        Statement statement = null;
        PreparedStatement preStatement = null;
        try {
            Class.forName("org.postgresql.Driver");
            connection = DriverManager.getConnection(URL_PG, NAME, PASS);
            statement = connection.createStatement();
            preStatement = connection.prepareStatement("DROP TABLE IF EXISTS " + TABLE_NAME + ";");
            preStatement.executeUpdate();

            String queryCreateTable = "CREATE TABLE " + TABLE_NAME +
                    "(Title text," +
                    "Band text," +
                    "Year int);";

            preStatement = connection.prepareStatement(queryCreateTable);
            preStatement.executeUpdate();

            String queryInsertDataFromAlbums =
                    "INSERT INTO " + TABLE_NAME +
                            " (Title, Band, Year) VALUES (?,?,?);";

            preStatement = connection.prepareStatement(queryInsertDataFromAlbums);

            for (Album a : albumList) {
                preStatement.setString(1, a.getTitle());
                preStatement.setString(2, a.getBand());
                preStatement.setInt(3, a.getReleaseYear());

                preStatement.executeUpdate();
            }
            albumList.onStateChanged();
            JOptionPane.showMessageDialog(this, "Data saved to PostgreSQL db.");
        } catch (Exception e) {
            JOptionPane.showMessageDialog(this, e.getMessage());
        }
    }
}