/*
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * published by the Free Software Foundation; either version 2.1 of
 * the license, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *  
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite
 * 330, Boston, MA  02111-1307, USA.
 * 
 */
package com.k_int.xrtree;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


/**
 * Title:		GenericXRDbManager
 * @author: 	rob
 * @version:	$Id: v Exp $ 
 * @since			
 * Copyright:	1999-2007 Knowledge Integration Ltd
 * Company:		Knowledge Integration Ltd
 * Description:
 *
 *
 *
 * Created:		27 Feb 2008
 *
 *
 * History:
 *				$Log: v $
 *
 * 
 */

public class GenericXRDbManager
{
  private Connection conn;
  
  private PreparedStatement add_data_stmt;
  private PreparedStatement add_link_stmt;
  private PreparedStatement add_xr_stmt;
  private PreparedStatement add_rel_stmt;
  private PreparedStatement get_data_count_stmt;
  private PreparedStatement set_next_index_stmt;
  private PreparedStatement add_cat_stmt;
  private PreparedStatement get_cat_stmt;
  private PreparedStatement get_link_stmt;
  private PreparedStatement get_data_stmt;
  private PreparedStatement add_revision_stmt;
  
  /**
   * 
   */
  public GenericXRDbManager(Connection conn) throws SQLException
  {
      this.conn=conn;
      add_xr_stmt             = conn.prepareStatement("INSERT INTO xr_tree  VALUES(?,?,?)");
      add_data_stmt           = conn.prepareStatement("INSERT INTO data  VALUES(?,?,?,?,?)");           
      get_data_count_stmt           = conn.prepareStatement("SELECT COUNT(*) FROM data WHERE id=? and source=?"); 
      get_data_stmt           = conn.prepareStatement("SELECT * FROM data WHERE id=?"); 
      
      add_rel_stmt            = conn.prepareStatement("INSERT INTO relationships  VALUES(?,?,?)");
      add_cat_stmt            = conn.prepareStatement("INSERT INTO categories  VALUES(?,?)");
      add_link_stmt           = conn.prepareStatement("INSERT INTO assist_links  VALUES(?,?)");
      get_cat_stmt           = conn.prepareStatement("SELECT cat_id FROM categories  WHERE data_id=?");
      get_link_stmt           = conn.prepareStatement("SELECT link_id FROM assist_links  WHERE data_id=?");
   
      add_revision_stmt        = conn.prepareStatement("INSERT INTO vocab_revision VALUES(?,?,?,?)");
      
      set_next_index_stmt     = conn.prepareStatement("INSERT INTO next_xr VALUES(?)");
     // set_next_index_stmt.setInt(1, 1);
     // set_next_index_stmt.execute();
      
      // only first time round
      if(getData("ROOT")==null)
      {
        DataNode data = new DataNode();
        
        data.setId("ROOT");
        data.setName("ROOT");
        data.setSource("ROOT");
        addData(data);
        
        XRNode root = new XRNode();
        root.setDataId("ROOT");
        root.setXRIndex("1");
       
        addXRNode(root);
      }
      
  }
  
  
  public void addRevision(String guid,String data_id, String revision, String auth)throws SQLException
  {
    add_revision_stmt.setString(1,guid);
    add_revision_stmt.setString(2,data_id);
    add_revision_stmt.setString(3,revision);
    add_revision_stmt.setString(4,auth);
    add_revision_stmt.execute();
  }
  
  
  public void addCategory(String data_id, String category_id) throws SQLException
  {
    List<String> cats = getCategories(data_id);
    if(cats.contains(category_id))
      return;
    add_cat_stmt.setString(1,data_id);
    add_cat_stmt.setString(2,category_id);
    add_cat_stmt.execute();
  }
  
  public void addLink(String data_id, String link_id) throws SQLException
  {
    List<String> links = getLinks(data_id);
    if(links.contains(link_id))
      return;
    add_link_stmt.setString(1,data_id);
    add_link_stmt.setString(2,link_id);
    add_link_stmt.execute();
  }
  
  
  
 /** public List<String> getLinksToCat(String id)throws SQLException
  {
    List<String> retval = new ArrayList<String>();
    
    try
    {
        get_links_to_cat_stmt.setString(1, id);
        ResultSet result = get_links_to_cat_stmt.executeQuery();
       
        while(result.next())
        {              
          String cat = result.getString(1);
          retval.add(cat);
        }
        return retval;    
    }
    catch(SQLException e)
    {        
        throw e;
    } 
  }**/
  
  public  List<String> getCategories(String id) throws SQLException
  {
    List<String> retval = new ArrayList<String>();
   
    try
    {
        get_cat_stmt.setString(1, id);
        ResultSet result = get_cat_stmt.executeQuery();
       
        while(result.next())
        {              
          String cat = result.getString(1);
          retval.add(cat);
        }
        return retval;    
    }
    catch(SQLException e)
    {        
        throw e;
    } 
  }
  
  public  List<String> getLinks(String id) throws SQLException
  {
    List<String> retval = new ArrayList<String>();
   
    try
    {
        get_link_stmt.setString(1, id);
        ResultSet result = get_link_stmt.executeQuery();
       
        while(result.next())
        {              
          String cat = result.getString(1);
          retval.add(cat);
        }
        return retval;    
    }
    catch(SQLException e)
    {        
        throw e;
    } 
  }
  
  public DataNode getData(String id) throws SQLException
  {
    get_data_stmt.setString(1,id);
   
    ResultSet results = get_data_stmt.executeQuery();
    DataNode result=null;
    while(results.next())
    {            
      result=new DataNode();
      result.setId(results.getString(1));
      result.setName(results.getString(2));
      result.setSource(results.getString(3));
      result.setType(results.getInt(4));
    }
    return result;
  }
  
  public void addData(DataNode node) throws SQLException
  {
      get_data_count_stmt.setString(1, node.getId());
      get_data_count_stmt.setString(2, node.getSource());
      ResultSet result = get_data_count_stmt.executeQuery();
      
      while(result.next())
      {              
          int count = result.getInt(1);
         
          if(count>0)
          {
              //System.out.println("-------- node "+node.getId()+" "+node.getName()+" exists already");
              return;
          }
      }
    
      try
      {
          add_data_stmt.setString(1,node.getId());
          add_data_stmt.setString(2,node.getName());
          add_data_stmt.setString(3,node.getSource());
          add_data_stmt.setInt(4,node.getType());
          add_data_stmt.setString(5,node.getDescription());
          add_data_stmt.execute();
      }
      catch(SQLException e)
      {
          System.out.println("Exception Adding data "+node.getName()+": "+node.getId());
          throw e;
      }
  }
  
  
  public void addXRNode(XRNode node) throws SQLException
  {
    //System.out.println("Adding xr node "+node.getXRIndex());
      add_xr_stmt.setString(1,node.getXRIndex());
      add_xr_stmt.setString(2,node.getDataId());
      add_xr_stmt.setString(3,node.getDataSource());
      add_xr_stmt.execute();
  }
  
  public void addXRNodeRelationships(XRNodeRelationship node_rel) throws SQLException
  {
      add_rel_stmt.setString(1,node_rel.getSourceId());
      add_rel_stmt.setString(2,node_rel.getDestinationId());
      add_rel_stmt.setInt(3,node_rel.getType());
      add_rel_stmt.execute();
  }
  
  
 
  
  public String getNextXrRoot() throws SQLException
  {
    String retval=null;
    try
    {
       
        ResultSet result = conn.createStatement().executeQuery("SELECT * FROM next_xr");
        int res=0;
        while(result.next())
        {              
          res = result.getInt(1);
        }
        
        retval=Integer.toString(res);
        set_next_index_stmt.setInt(1, res+1);
        set_next_index_stmt.execute();
        return retval;    
    }
    catch(SQLException e)
    {        
        throw e;
    } 
  }

}