package releaseManager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ReleaseManager
{
	private Connection dbConnection;
	
	private static final String RELEASE_TAG_NAME_COLUMN = "rtag_name";
	private static final String PACKAGE_NAME_COLUMN = "pkg_name";
	private static final String PACKAGE_VERSION_COLUMN = "pkg_version";
	
	// If this query changes then the column names above likely change too
	private static final String queryString =
		  "Select " +
            "RELEASE_TAGS.RTAG_NAME, " +
            "PACKAGES.PKG_NAME, " +
            "PACKAGE_VERSIONS.PKG_VERSION, " +
            "PACKAGE_VERSIONS.PV_ID " +
          "from " +
            "UNIT_TESTS, " +
            "PACKAGE_VERSIONS, " +
            "PACKAGES, " +
            "RELEASE_CONTENT, " +
            "RELEASE_TAGS, " +
            "PROJECTS " +
          "where PACKAGE_VERSIONS.PKG_ID = PACKAGES.PKG_ID " +
            "and RELEASE_CONTENT.PV_ID = UNIT_TESTS.PV_ID " + 
            "and RELEASE_CONTENT.PV_ID = PACKAGE_VERSIONS.PV_ID " +
            "and RELEASE_CONTENT.RTAG_ID = RELEASE_TAGS.RTAG_ID " +
            "and PROJECTS.PROJ_ID = RELEASE_TAGS.PROJ_ID " +
            "and UNIT_TESTS.TEST_TYPES_FK = 7 " +
            "and RELEASE_TAGS.RTAG_ID = ? " + // This parameter needs to be substituted
          "order by RELEASE_TAGS.RTAG_NAME asc";
	
	/**
	 * Constructor
	 * @param pDbConnection - An open connection to the Release Manager
	 *                        database
	 */
	public ReleaseManager(Connection pDbConnection)
	{
		dbConnection = pDbConnection;
	}
	
	/**
	 * Queries the Release Manager database for any packages that have UTF
	 * tests, and returns the result in a list.
	 * @param rtag_id - The rtag_id as displayed by Release Manager in the URL
	 *                  for the desired project.
	 *                  E.g.: if the URL is:http://bms:8002/ManagerSuite/Release_Manager/dependencies.asp?envtab=3&rtag_id=30684
	 *                  then the rtagId is 30684 (this happens to be Stockholm
	 *                  Rel 10.1.1.0)
	 * @return - A list of Packages, having UTF tests, that are associated with
	 *           the given rtagId. Never null.
	 * @throws SQLException - If there was an error in the query.
	 */
	public List<VixPackage> getTestedPackages(int rtagId) throws SQLException
	{
		List<VixPackage> result = new ArrayList<VixPackage>();
		ResultSet queryResult = runQuery(rtagId);
		while (queryResult.next())
		{
			VixPackage pkg = parsePackage(rtagId, queryResult);
			result.add(pkg);
		}
		return result;		
	}

	/**
	 * Create a new VixPackage and fill it with data
	 * 
	 * @param rtagId - The release tag ID. This is gleaned from the Release
	 *                 Manager URL
	 * @param queryResult - The results of running the query against the
	 *                      Release Manager database
	 * @return - A list, possibly empty, of any packages that had UTF tests
	 *           in the specified release.
	 * @throws SQLException - If the column names change in the database.
	 */
	private VixPackage parsePackage(int rtagId, ResultSet queryResult) throws SQLException
	{
		VixPackage pkg = new VixPackage();
		pkg.releaseTagId = rtagId;
		pkg.releaseTagName = queryResult.getNString(RELEASE_TAG_NAME_COLUMN);
		pkg.packageName = queryResult.getNString(PACKAGE_NAME_COLUMN);
		pkg.packageVersion = queryResult.getNString(PACKAGE_VERSION_COLUMN);
		return pkg;
	}

	/**
	 * Create the SQL query to run against the Release Manager database
	 * ensuring all parameters are substituted
	 * 
	 * @param rtagId - The release tag ID. This is gleaned from the Release
	 *                 Manager URL.
	 * @return - The results from the query. Never null.
	 * @throws SQLException - If there was an error in the query.
	 */
	private ResultSet runQuery(int rtagId) throws SQLException
	{
		PreparedStatement query;
		query = dbConnection.prepareStatement(queryString);
		query.setInt(1, rtagId);
		ResultSet queryResult = query.executeQuery();
		return queryResult;
	}
}