package vso.models;

import java.sql.Connection; // a connection to the DB
import java.sql.DriverManager; // handles communication with the DB
import java.sql.ResultSet; // a table of rows generated from an SQL query
import java.sql.Statement; // an SQL statement for the DB to execute
import java.util.ArrayList;
import java.util.Date;

public class MySQL {
    Connection connection = null;

    public MySQL() {
        dataBaseSetUp();
        createTable();
        insertData();
    }

    private void dataBaseSetUp() {
        try {
            String connectionURL = "jdbc:mysql://localhost:3306/happyDB?createDatabaseIfNotExist=true";
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            connection = DriverManager.getConnection(connectionURL, "root", "1234");
            if (!connection.isClosed())
                System.out.println("Successfully connected to " + "MySQL server using TCP/IP...");
        } catch (Exception ex) {
            System.out.println("Unable to connect to database" + ex);
        }
    }

    private void createTable() {
        try {
            Statement stmt = connection.createStatement();
            String sql = "CREATE TABLE books (" + "id SERIAL PRIMARY KEY NOT NULL," + "name TEXT NOT NULL,"
                    + "author TEXT NOT NULL," + "date_of_publishing DATE NOT NULL," + "isbn TEXT NOT NULL)";
            stmt.executeUpdate(sql);
            stmt.close();
            System.out.print("Table created");
        } catch (Exception e) {
            System.out.println("Table was not created");
        }
    }

    public void insertData() {
        for (int i = 0; i < 10; i++) {
            insertRow("Kniga" + i, "Avtor" + i, i + "");
        }
    }

    public void insertRow(String name, String author, String isbn) {
        try {
            Statement stmt = connection.createStatement();
            String sql = "INSERT INTO books (name, author, date_of_publishing, isbn) VALUES('" + name + "', '" + author
                    + "', '" + new java.sql.Date(new Date().getTime()) + "', '" + isbn + "');";
            stmt.executeUpdate(sql);
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public ArrayList<String> readData() {

        ArrayList<String> names = new ArrayList<String>();
        try {
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT name FROM books;");

            while (rs.next()) {
                //int id = rs.getInt("id");
                names.add(rs.getString("name"));
                //String bookAuthor = rs.getString("author");
                //String bookIsbn = rs.getString("isbn");
                //System.out.format("%d %s %s %s\n", id, bookName, bookAuthor, bookIsbn);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return names;
    }
}