package dmi_demo;

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

public class Solution {
	private static final int maxTransactions = 50;
	private Transaction[] transactions;
	private int[] sellers, buyers;
	// "busy" property is a lookup that indicates which companies are already
	// involved in transactions with the current solution
	private HashMap<Integer, Boolean> busy;
	private float value;
	
	public Solution () {
		sellers = new int[maxTransactions];
		buyers = new int[maxTransactions];
		transactions = new Transaction[maxTransactions];
		busy = new HashMap<Integer,Boolean>();
		value = 0;
		
		for (int i = 0; i < maxTransactions; i ++) {
			sellers[i] = 0;
			buyers[i] = 0;
			transactions[i] = null;
		}
	}

	public boolean canSetTransaction (int i, int s, int b)
	{
		Integer S = new Integer(s), B = new Integer(b);
		if (i < 0 || i >= maxTransactions) {
			//System.out.println("Error: transaction out of bounds");
			return false;
		} else if (s == b) {
			//System.out.println("Buyer and seller can't be the same, silly!");
			return false;
		} else if (busy.get(S) == Boolean.TRUE && 
				!(transactions[i] != null && (
						transactions[i].getSeller() != s
						|| transactions[i].getBuyer() != s)
						)
		) {
			//System.out.println("Error: Cannot update transaction "+i+", seller already busy");
			return false;
		} else if (busy.get(B) == Boolean.TRUE && 
				!(transactions[i] != null && (
				transactions[i].getSeller() != b
				|| transactions[i].getBuyer() != b)
				)
		) {
			//System.out.println("Error: Cannot update transaction "+i+", buyer already busy");
			return false;
		}
		return true;
	}
	
	public void setTransaction(int i, int s, int b, int p, float c)
	{
		Integer S = new Integer(s), B = new Integer(b);
		if (!canSetTransaction(i, s, b)) {
			System.out.println ("Error: Cannot set transaction");
		} else {
			if (transactions[i] != null) {
				// clear the busy table of the old members
				value -= transactions[i].getPrice();
				Integer oldS = new Integer(transactions[i].getSeller());
				Integer oldB = new Integer(transactions[i].getBuyer());
				busy.put(oldS, Boolean.FALSE);
				busy.put(oldB, Boolean.FALSE);
			}
			Transaction t = new Transaction (s, b, p, c);
			transactions[i] = t;
			busy.put(S, Boolean.TRUE);
			busy.put(B, Boolean.TRUE);
			value += c;
			//System.out.println("adding "+c+", value is now "+value);
		}
	}
	
	public void populateRandom(Connection conn)
	{
		// We want a query to find two random requests, SUCH THAT:
		// * The first request is a sell (1)
		// * The second request is a buy (2)
		// * The first item matches the second item
		// * Each buyer and seller are used once at most
		try {
			String sql = "SELECT * FROM " +
					"(" +
					"  SELECT * FROM" +
					"  (" +
					"    SELECT Price, sreq.Company_Id AS seller, breq.Company_Id AS buyer, DMI_Part.Id AS part" +
					"    FROM DMI_Request AS sreq, DMI_Request AS breq, DMI_Part" +
					"    WHERE sreq.Type=1 AND breq.Type=2" +
					"    AND sreq.Part_Id=DMI_Part.Id" +
					"    AND breq.Part_Id=DMI_Part.Id" +
					"    ORDER BY RAND()" +
					"    limit 5000" +
					"  ) AS alltrans" +
					"  GROUP BY seller" +
					"  ORDER BY RAND()" +
					") AS uniqseller " +
					"GROUP BY buyer " +
					"ORDER BY RAND() LIMIT "+maxTransactions;

			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			int i = 0;
			while (rs.next()) {
				int seller = rs.getInt("seller");
				int buyer = rs.getInt("buyer");
				int part = rs.getInt("part");
				float price = rs.getFloat("price");
				setTransaction(i++, seller, buyer, part, price);
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			System.out.println("SQL Exception while trying to generate random solution: "+e.getMessage());
		}
	}
	
	// improve method: attempts to make better choices for all buyers if
	// possible.  Returns true if any improvements were found.
	public boolean improve (Connection conn) {
		boolean result = false;
		try {
			String sql = "SELECT Price, seller.Id, DMI_Part.Id AS part " +
					"FROM DMI_Company AS seller, " +
					"DMI_Request AS sreq, DMI_Request AS breq, DMI_Part " +
					"WHERE DMI_Part.Price > ? " +
					"AND breq.Company_Id = ? " +
					"AND sreq.Type=1 " +
					"AND breq.Part_Id = sreq.Part_Id " +
					"AND sreq.Part_Id=DMI_Part.Id " +
					"AND seller.Id=sreq.Company_Id";
			PreparedStatement findBetterPs = conn.prepareStatement(sql); 
			for (int i = 0; i < maxTransactions; i ++) {
				// Find a better transaction with this buyer
				Transaction oldt = transactions[i];
				if (oldt != null) {
					int buyer = oldt.getBuyer();
					findBetterPs.setFloat(1, oldt.getPrice());
					findBetterPs.setInt(2, buyer);
					ResultSet rs = findBetterPs.executeQuery();
					boolean improved = false;
					while (rs.next() && !improved) {
						int seller = rs.getInt("seller.Id");
						int part = rs.getInt("part");
						float price = rs.getFloat("Price");
						if (canSetTransaction(i, seller, buyer)) {
							setTransaction(i, seller, buyer, part, price);
//							System.out.println("Improved transaction "+i+" by going from "+oldt.getPrice()+" to "+price);
							improved = true;
							result = true;
						}
					}
					rs.close();
				}
			}
			findBetterPs.close();
		} catch (SQLException e) {
			System.out.println("SQL Exception while trying to improve on solution: "+e.getMessage());
		}
		return result;
	}

	// Save a solution to the Transaction table
	public void record (Connection conn) {
		try {
			String sql = "DELETE FROM DMI_Transaction";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.execute();
			ps.close();
			
			sql = "INSERT INTO DMI_Transaction (Seller_Id, Buyer_Id, Part_Id) " +
					"VALUES (?, ?, ?)";
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < maxTransactions; i ++) {
				Transaction t = transactions[i];
				if (t != null) {
					ps.setInt(1, t.getSeller());
					ps.setInt(2, t.getBuyer());
					ps.setInt(3, t.getPart());
					ps.execute();
				}
			}
			
			//String sql = "UPDATE DMI_TransactionSELECT Price, seller.Id, DMI_Part.Id AS part " +
			conn.commit();
			ps.close();
		} catch (SQLException e) {
			System.out.println("SQL Exception while trying to record solution to database: "+e.getMessage());
		}
	}
	public boolean isBusy(int company)
	{
		return busy.get(new Integer(company)).booleanValue();
	}
	
	public float getValue()
	{
		return value;
	}
	
	public void dump()
	{
		for (int i = 0; i < maxTransactions; i ++) {
			if (transactions[i] != null) {
				transactions[i].dump();
			}
		}
		System.out.println("Value of all transactions: $"+value);
	}
}