package mongoJava;

import java.sql.*;
import java.util.List;
import java.util.Arrays;
import java.util.Iterator;
import com.mongodb.Mongo;
import com.mongodb.DB;
import com.mongodb.DBCollection;
import com.mongodb.BasicDBObject;
import com.mongodb.DBObject;
import com.mongodb.DBCursor;
import com.mongodb.WriteConcern;

import oracle.jdbc.pool.OracleDataSource;

public class mongoSalesForCustomer {

	/**
	 *Load some Oracle sample schema data into MongoDb 
	 */
	public static void main(String[] args) {
		try {
			if (args.length != 7) {
				System.err
						.println("Usage: mongoHost mongoPort mongoDb mongoCollection oracleThinConnection "
								+ "oracleUser oraclePass");
				System.exit(1);
			}
			String mongoHost = args[0];
			Integer mongoPort = Integer.parseInt(args[1]);
			String mongoDb = args[2];
			String mongoCollection = args[3];
			String oracleThinConnection = args[4];
			String oracleUser = args[5];
			String oraclePass = args[6];

			Connection oracleConn = connectOracle(oracleThinConnection,
					oracleUser, oraclePass);

			Mongo m = new Mongo(mongoHost, mongoPort);

			DB db = m.getDB(mongoDb);

			insertSales(oracleConn, db, mongoCollection);
			System.out.println("Done");

		} catch (Exception x) {
			x.printStackTrace();
			System.exit(2);
		}

	}

	static protected Connection connectOracle(String oracleThinConnection,
			String oracleUser, String oraclePass) throws SQLException {
		OracleDataSource ods = new OracleDataSource();
		ods.setUser(oracleUser);
		ods.setPassword(oraclePass);
		String dbUrl = "jdbc:oracle:thin:@" + oracleThinConnection;
		// System.out.println("Connecting to "+dbUrl);
		ods.setURL(dbUrl);
		Connection oracleConn = ods.getConnection();
		return (oracleConn);
	}

	private static void insertSales(Connection oracleConn, DB mongoDb,
			String mongoCollection) throws SQLException {

		String custSQL = "SELECT cust_id, cust_first_name,  cust_last_name FROM sh.customers " +
				" WHERE cust_id IN (SELECT distinct cust_id from sh.sales) AND rownum <10";
		String salesSQL = "SELECT  time_id,prod_id, prod_name,  "
				+ "    amount_sold , quantity_sold  "
				+ "  FROM sh.sales     "
				+ "  JOIN sh.products  USING (prod_id)   "
				+ " WHERE cust_id=:custId "   
				+ "  ORDER BY 4 DESC";
		Statement query = oracleConn.createStatement();

		PreparedStatement custSalesQry = oracleConn.prepareStatement(salesSQL);

		ResultSet custRs = query.executeQuery(custSQL);

		DBCollection custColl = mongoDb.getCollection(mongoCollection);

		Integer custCount = 0;

		while (custRs.next()) { // For each customer
			String custId = custRs.getString("CUST_ID");
			String custFirstName = custRs.getString("CUST_FIRST_NAME");
			String custLastName = custRs.getString("CUST_LAST_NAME");
			// System.out.printf("%s %s\n", custFirstName, custLastName);

			BasicDBObject custDoc = new BasicDBObject();
			custDoc.put("_id", custId);
			custDoc.put("CustomerFirstName", custFirstName);
			custDoc.put("CustomerLastName", custLastName);
			// Add the products
			BasicDBObject customerProducts = new BasicDBObject();
			custSalesQry.setString(1, custId);
			ResultSet prodRs = custSalesQry.executeQuery();
			Integer prodCount = 0;
			while (prodRs.next()) {
				String  timeId=prodRs.getString("TIME_ID"); 
				Integer prodId = prodRs.getInt("PROD_ID");
				String prodName = prodRs.getString("PROD_NAME");
				Float Amount = prodRs.getFloat("AMOUNT_SOLD");
				Float Quantity = prodRs.getFloat("QUANTITY_SOLD");
				// System.out.println(prodId+prodName);
				BasicDBObject productItem = new BasicDBObject();			
				productItem.put("prodId", prodId);
				productItem.put("prodName", prodName);
				productItem.put("Amount", Amount);
				productItem.put("Quantity", Quantity);
				customerProducts.

				put(timeId, productItem);
				if (prodCount++ > 4) { // Just 5 for this demo
					prodCount = 0;
					break;
				}
			}
			custDoc.put("SalesForCustomer", customerProducts);

			System.out.println(custDoc);
			custColl.insert(custDoc);
			custCount++;

		}

		System.out.println(custCount + " customers loaded into "
				+ mongoCollection);
	}

}