package us.deans.parrot.dataprovider;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

// import us.deans.parrot.dataprovider.WpTitleRecord;

import java.util.ArrayList;


public class DataManager {

	private DataSource ds_cms;
	private DataSource ds_ref;
	
	private Connection dc1 = null;
	private Statement st1;
	private ResultSet rs1; 
	
	private ArrayList<WpTitleRecord> dset = null;
	
	private Log log = LogFactory.getLog(ExperienceDataSet.class);
	
	// singleton:
	
	private static final DataManager instance = new DataManager();
	private DataManager(){
		super();
	}
	public static DataManager getInstance() {
		return instance;
	}
	
	// configuration:
	
	public void setProps() {
		// called by ParrotLoader 
		// prefer to pass SQL to functions per request rather than loading it all up front.
	}
	
	public void setDataSource_CMS(DataSource ds) {
		// called by ParrotLoader
		ds_cms = ds;
	}
	
	public void setDataSource_REF(DataSource ds) {
		// called by ParrotLoader	
		ds_ref = ds;
	}
	
	// services:
	
	public String checkdb() {
		
		int x = 0;
		
		try {
			dc1 = ds_cms.getConnection();
			st1 = dc1.createStatement();
			log.debug(">> got connection.");
			String sql = "SELECT COUNT(*) AS COUNT FROM wp_posts";
			rs1 = st1.executeQuery(sql);

			while(rs1.next()) {
				x = rs1.getInt("Count");
			}

			st1.close();
			dc1.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return Integer.toString(x) + " records in database...";
	}
	
	public ArrayList<WpTitleRecord> getStoryList(String SQL){

		dset = new ArrayList<WpTitleRecord>();
		
		try {
			
			dc1 = ds_cms.getConnection();
			st1 = dc1.createStatement();
			
			log.debug("sql check >> " + SQL);
			rs1 = st1.executeQuery(SQL);
			
			while (rs1.next()) {
				WpTitleRecord drec = new WpTitleRecord(); 
				drec.setPostTitle(rs1.getString("post_title"));
				drec.setPostID(rs1.getInt("ID")); 
				dset.add(drec);
			}
			
			rs1.close();
			st1.close();
			dc1.close();
			
		}
		catch(SQLException ex) {
			ex.printStackTrace();
		}
		return dset;
		
	}
	
	public WpPostRecord getStory(String SQL, int storyId) {
		
		WpPostRecord drec = new WpPostRecord();
		
		try {

			dc1 = ds_cms.getConnection();
			PreparedStatement ps1 = dc1.prepareStatement(SQL);
			
			ps1.setInt(1, storyId); 
			rs1 = ps1.executeQuery();

			while (rs1.next()) {
				drec.setPostTitle(rs1.getString("post_title"));
				drec.setPostContent(rs1.getString("post_content"));
			}
			
			rs1.close();
			st1.close();
			dc1.close();	
			
		}
		catch (SQLException ex) {
			ex.printStackTrace();
		}
		
		return drec;
		
	}
	
}