package dmi_demo;

import java.io.*;
import java.sql.*;
import java.util.*;

public class ProblemGenerator {
	private Connection dbconn;
	public static final int SELL = 1;
	public static final int BUY = 2;

	public ProblemGenerator () throws SQLException, ClassNotFoundException, IOException 
	{
		dbconn = DmiDB.connect();
		dbconn.setAutoCommit(false);
	}
	
	public void AssignCompanyRequests () {
		Random r = new Random();
		try {
			// Clear out the existing rows from the Request table
			String sql = "DELETE FROM DMI_Request";
			PreparedStatement delPs = dbconn.prepareStatement(sql);
			delPs.execute();
			delPs.close();
			
			// prepare to update company roles and requests
			sql = "UPDATE DMI_Company SET Role=? WHERE Id=?";
			PreparedStatement updCoPs = dbconn.prepareStatement(sql);
			sql = "INSERT INTO DMI_Request (Company_Id,Part_Id,Type) VALUES (?,?,?)";
			PreparedStatement insReqPs = dbconn.prepareStatement(sql);

			// retrieve companies
			sql = "SELECT Id, Name FROM DMI_Company ORDER BY Name";
			PreparedStatement queryCoPs = dbconn.prepareStatement(sql);
			ResultSet companyRs = queryCoPs.executeQuery();
			// ...and random parts
			sql = "SELECT Id,Description,Price FROM DMI_Part ORDER BY RAND() LIMIT ?";
			PreparedStatement queryPartPs = dbconn.prepareStatement(sql);

			while (companyRs.next()) {
				int coId = companyRs.getInt("Id");
				String name = companyRs.getString("Name");
				//random role: 1="seller", 2="buyer"
				int role = r.nextInt(2)+1;
				String action = (role == SELL ? "sell" : "buy");  
				updCoPs.setInt(1, role);
				updCoPs.setInt(2, coId);
				updCoPs.execute();
				
				// Pick a random number from 5-20: this is how many items the
				// company wishes to buy or sell.
				int numitems = r.nextInt(16)+5;
				System.out.println (name+" wishes to "+action+" "+numitems
						+" items: ");
				// Pick some random items
				queryPartPs.setInt(1, numitems);
				ResultSet partRs = queryPartPs.executeQuery();
				while (partRs.next()) {
					int partId = partRs.getInt("Id");
					System.out.println("  "+partRs.getString("Description"));
					insReqPs.setInt(1, coId);
					insReqPs.setInt(2, partId);
					insReqPs.setInt(3, role);
					insReqPs.execute();
				}
				partRs.close();
			}
			dbconn.commit();
			// all done here
			companyRs.close();
			queryCoPs.close();
			updCoPs.close();
			insReqPs.close();
			queryPartPs.close();
		} catch (SQLException e) {
			System.out.println("SQL Exception while trying to set up company requests: "+e.getMessage());
		}
	}
}