package us.deans.zinc;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.Properties;
import java.sql.*;

import org.apache.log4j.Logger;

import org.firebirdsql.pool.FBWrappingDataSource;

/** 
 *  Contains operations for storing and retrieving data to and from external database.
 */

public class ZnDbAdapter {

	private Properties props;
	private Connection dbc;
	private Logger logger;
	private ArrayList<ZnJobRecord> lstJobEntries;
	private Iterator<ZnJobRecord> itrJobEntries;
	private ZnJobRecord recJobEntry;															// job record	
	
	public ZnDbAdapter(Properties props, Logger logger){
		
		this.props = props;
		this.logger = logger;
		
		dbConnect01();											// simple connection using a driver manager
	//	dbConnect0x();
		
	}
	
	private void dbConnect01(){
		
		// URL format for Pure Java (Type 4) Driver = jdbc:firebirdsql:host[/port]:/path/to/db.fdb
		String dbUrl = props.getProperty("dbUrl");
		String dbUsr = props.getProperty("dbUsr");
		String dbPwd = props.getProperty("dbPwd");
		
		try{
			//trigger static class initializer so the driver registers itself in java.sql.DriverManager
			Class.forName("org.firebirdsql.jdbc.FBDriver");		
			
			//get connection object
			dbc = DriverManager.getConnection(dbUrl,dbUsr,dbPwd);
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			logger.error("class loader exception: " + e.getMessage(),e);

		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	private void dbConnect0x(){

		FBWrappingDataSource dataSource = new FBWrappingDataSource();
		dataSource.setDatabase("c:/data/databases/zinc-001.gdb");
		dataSource.setDescription("test connection to zinc database");

		try{
			dataSource.setLoginTimeout(10);
			dbc = dataSource.getConnection("ndeans","!code");
			Statement stmt = dbc.createStatement();
			ResultSet rs = stmt.executeQuery("select * from joblist");
			while (rs.next())
				System.out.println("a1 = " + rs.getString(1) + ", a2 = " + rs.getString(2));
			stmt.close();
			
		}
		catch(java.sql.SQLException e){
			e.printStackTrace();
			System.out.println("sql exception: " + e.getMessage());
		}
		
	}
	
	public void exportJobData(ArrayList<ZnJobRecord> rssData) {
		
		String jobTitle;
		String jobLink;
		String jobLocation;
		String jobCompany;
		String jobDescription;
		String jobKeywords;
		String sqlInsert;
		
		this.lstJobEntries = rssData;
		itrJobEntries = lstJobEntries.iterator();											//create subject iterator 
		int count = 0;
		while (itrJobEntries.hasNext()){													//cycle through the subject list
			recJobEntry = itrJobEntries.next();												//get subject record			
			jobTitle = recJobEntry.getJobTitle();
			jobLink = recJobEntry.getJobLink();
			jobLocation = recJobEntry.getJobLocation();
			jobCompany = recJobEntry.getJobCompany();
			jobDescription = recJobEntry.getJobDescription();
			jobKeywords = recJobEntry.getJobKeywords();
				
			sqlInsert = "insert into job_list (JOB_TITLE, JOB_LINK, JOB_LOCATION, JOB_COMPANY, JOB_DESCRIPTION, JOB_KEYWORDS)";
			sqlInsert = sqlInsert + " values ('";
			sqlInsert = sqlInsert + jobTitle + "','" + jobLink + "','" + jobLocation + "','" + jobCompany + "','"; 
			sqlInsert = sqlInsert + jobDescription + "','" + jobKeywords;
			sqlInsert = sqlInsert + "')";
		
			try {
				Statement stmt = dbc.createStatement();
				int result = stmt.executeUpdate(sqlInsert);
				stmt.close(); count++;
			}
			catch (SQLException e) {
				logger.error("sql exception: " + e.getMessage(),e);
			//	e.printStackTrace();
			}
		}
		logger.info(count + " records inserted...");
		
		
		
	}

	public void importJobData() {
	}

	public void exportTermData() {
	}

	public void importTermData() {
	}

}