datamodel

package event.handling;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
//import event.handling.utils.TextFileReader;

//=====================================================================
public class DataModel
{
    // --- Public Members -----------------
    public static enum Source
    {
	SRC_NULL, SRC_DBASE
    };

    // --- Private Members ----------------
    private ObservableList<Track> the_tracklst = null;
    private ObservableList<String> the_statelst = null;
    private Source the_src_type = Source.SRC_NULL;

    // --- Constructors -------------------
    public DataModel() // Default
    {
	the_tracklst = FXCollections.observableArrayList();
	the_statelst = FXCollections.observableArrayList();
    }

    public DataModel(Source src_type)
    {
	this();
	this.setSourceType(src_type);
    }

    public DataModel(String source, Source src_type)
    {
	this();
	this.setData(source, src_type);
    }

    // --- Methods ---
    private void setSourceType(Source src_type)
    {
	this.the_src_type = src_type;
    }

    public Source getSourceType()
    {
	return the_src_type;
    }

    public ObservableList<Track> getCityData()
    {
	return the_tracklst;
    }

    public ObservableList<String> getStateData()
    {
	return the_statelst;
    }

    public void setData(String source, DataModel.Source src_type)
    {
	this.setSourceType(src_type);
	this.setData(source);
    }

    public void setData(String source)
    {
	switch (getSourceType())
	{
	    case SRC_DBASE:
		this.loadDbTable(source);
		break;
	    case SRC_NULL:
		break;
	    default:
		break;
	}
    }



    private final void loadDbTable(String source)
    {
	final String sql = "SELECT * FROM " + source+ " order by ArtistId ASC;";
	try (Connection conn = this.connectDatabase();
		Statement stmt = conn.createStatement();
		ResultSet rset = stmt.executeQuery(sql))
	{
	Track record = null;
			while (rset.next())
			{
		record = new Track(rset.getInt("AlbumId"),
						   rset.getString("Title"),
						   rset.getInt("ArtistId")
		);
		the_tracklst.add(record); // Add City object to the list
	    }
	}
	 catch (SQLException e)
	{
	    // e.printStackTrace();
	    System.out.println(e.getMessage());
	}
  }

	public static void deleteDbEntry(int albumId, String title, int artistId) {
		final String sql = "DELETE from albums where albumId = "+albumId;
    	try {
    		String db = "jdbc:sqlite:./data/dbs/chinook.db";
    			Statement st = DriverManager.getConnection(db).createStatement();
    			st.execute(sql);
    	} catch (SQLException e)
    	{
    	    // e.printStackTrace();
    	    System.out.println(e.getMessage());
    	}
    }

	public static void addDbEntry(int albumId, String title, int artistId) {
		final String sql = "insert into albums(Title, ArtistId) values (' "  + title + " ' , " + artistId + " ) ";
    	try {
    		String db = "jdbc:sqlite:./data/dbs/chinook.db";
    			Statement st = DriverManager.getConnection(db).createStatement();
    			st.execute(sql);
    	} catch (SQLException e)
    	{
    	    // e.printStackTrace();
    	    System.out.println(e.getMessage());
    	}
	}

    public void getNewDb() {
    	loadDbTable("albums");
    }

    public static void editDbEntry(int albumId, String title, int artistId )  {
    	final String sql = "update albums set Title =  " + title + "  , ArtistId = " + artistId + " where albumId = "+albumId;
    	try {
    		String db = "jdbc:sqlite:./data/dbs/chinook.db";
    			Statement st = DriverManager.getConnection(db).createStatement();
    			st.execute(sql);
    	} catch (SQLException e)
    	{
    	    // e.printStackTrace();
    	    System.out.println(e.getMessage());
    	}
    }

    private final Connection connectDatabase()
    {
	// SQLite connection string
	String url = "jdbc:sqlite:./data/dbs/chinook.db";
	Connection conn = null;
	try
	{
	    conn = DriverManager.getConnection(url);
	} catch (SQLException e)
	{
	    System.out.println(e.getMessage());
	}
	return conn;
    }

    public void close()
    {
	switch (getSourceType())
	{
	    case SRC_DBASE:
		// TODO: Do something with the database
		break;
	    case SRC_NULL:
		// TODO: Do nothing
		break;
	    default:
		// TODO: Do nothing
		break;
	}

    }

}