/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.javafxdata.datasources.protocol;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.scene.control.TableColumn;
import org.javafxdata.control.cell.MapValueFactory;

/**
 *
 * @author johan
 */
public class JdbcDataSource extends DataSource<Map<String, String>> {

  private String dbURL;
  private static Connection conn;
  private List<String> selectedColumnNames = new LinkedList<String>();
  private ObservableList<TableColumn<Map, String>> tableColumns;
  private Map<String, TableColumn<Map, String>> tableMap = new HashMap();

  public JdbcDataSource(String url, String table, String... cols) {
    this.dbURL = url;
    selectedColumnNames.addAll(Arrays.asList(cols));
    createConnection();
    int totcols = selectedColumnNames.size();
    if (totcols > 0) {

      try {
        String queryString = "SELECT ";
        int i = 0;
        while (i < totcols) {
          queryString = queryString + selectedColumnNames.get(i);
          i++;
          if (i < totcols) {
            queryString = queryString + ", ";
          }
        }
        queryString = queryString + " FROM " + table;
        System.out.println("querystring = " + queryString);
        ResultSet resultSet = conn.createStatement().executeQuery(queryString);
        System.out.println("RESULTSET = " + resultSet);
        while (resultSet.next()) {
          Map<String, String> map = new HashMap<String, String>();
          add(map);
          for (String c : selectedColumnNames) {
            map.put(c, resultSet.getString(c));
          }
          System.out.println("NEXT ROW = " + resultSet.getString("FIRSTNAME"));

          System.out.println("map = " + map);
        }
      } catch (SQLException ex) {
        Logger.getLogger(JdbcDataSource.class.getName()).log(Level.SEVERE, null, ex);
      }
    }
  }

  private void createConnection() {
    try {
      Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
      conn = DriverManager.getConnection(dbURL);
    } catch (Exception except) {
      except.printStackTrace();
    }
  }

  public ObservableList<TableColumn<Map, String>> getColumns() {
    if (selectedColumnNames == null) {
      return FXCollections.emptyObservableList();
    }

    if (tableColumns != null) {
      return tableColumns;
    }

    // build columns
    tableColumns = FXCollections.observableArrayList();
    for (final String columnName : selectedColumnNames) {
      TableColumn<Map, String> tc = new TableColumn<Map, String>(columnName);
      tc.setCellValueFactory(new MapValueFactory<String>(columnName));

      tableColumns.add(tc);
      tableMap.put(columnName, tc);


    }
    return tableColumns;
  }

  public TableColumn<Map, String> getNamedColumn(String firstName) {
    return tableMap.get(firstName);
  }
}
