package com.tandbergtv.watchpoint.pmm.action.schedule.ingest.converter;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.TransformerFactoryConfigurationError;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import org.apache.log4j.Logger;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;

import com.tandbergtv.watchpoint.pmm.util.TemplateProperties;
import com.tandbergtv.watchpoint.pmm.util.schedule.ingest.ConversionResult;
import com.tandbergtv.watchpoint.pmm.util.schedule.ingest.FileType;
import com.tandbergtv.watchpoint.pmm.util.schedule.ingest.PlannerConstants;
import com.tandbergtv.watchpoint.pmm.util.schedule.ingest.TVNConstants;

/**
 * Class that converts TVN format planner xls to PMM planner. The converted file is written to a
 * temp directory. Any node after this conversion is responsible to move the file in an appropriate
 * location. The conversion only includes those sections which have items specified. e.g. If the box
 * cover id is not specified in a particular row, the box cover element will not be created for this
 * title in the output file.
 * 
 * @author spuranik
 */
public class TVNToPMMScheduleConverter implements IScheduleConverter {

	private static final Logger logger = Logger.getLogger(TVNToPMMScheduleConverter.class);

	private static final String VALID_FILE_EXTN = "xls";
	private static String FILE_NAME_EXTN_SEPARATOR = ".";
	private static String PROVIDER_TIME_FORMAT = "MM/dd/yy";
	private static String PMM_DATETIME_FORMAT = TemplateProperties
			.getString("PMM.date.time.format");
	private static String ADI_DATE_FORMAT = TemplateProperties.getString("ADI.date.format");
	private static String LISENCE_DATE_FORMAT = "yyyy/MM/dd";
	private static String CREATION_DATE_FORMAT = "mm/dd/yy";

	public TVNToPMMScheduleConverter() {
	}

	/**
	 * converts the given xls file to PMM planner/program list Planner without a date is a program
	 * list. This assumes that the xls file has the complete metadata information.
	 * 
	 * @param xlsFilepath
	 *            path to xsl file
	 * @param outputPath
	 *            path where PMM planner will be written out.
	 */
	public ConversionResult convert(String xlsFilepath, String outputPath) {
		ConversionResult result = new ConversionResult();
		try {
			// if its here that means this converter can translate the incoming file
			// to the internal format.
			Workbook workbook = Workbook.getWorkbook(new File(xlsFilepath));
			Sheet sheet = workbook.getSheet(TVNConstants.METADATA_WORKSHEET_NAME);
			if (sheet == null) {
				result.setErrorMsg("No 'Metadata' work sheet found in input file: " + xlsFilepath);
				return result;
			}
			int rowCount = sheet.getRows();
			int dataRowStart = findActiveDataRow(sheet);
			logger.debug("Active data row: " + dataRowStart);
			if (dataRowStart == TVNConstants.INVALID_ROW) {
				String errorMsg = "No row with marker: 'the active line' found in xls";
				result.setErrorMsg(errorMsg);
				return result;
			}

			Document doc = createDocument();
			if (doc != null) {
				// this converter only converts planners
				result.setFileType(FileType.PLANNER);

				// process the rows now.
				for (int rowIndex = dataRowStart; rowIndex < rowCount; rowIndex++) {
					try {
						processRow(sheet, rowIndex, doc, result);
					} catch (RuntimeException e) {
						// If any row has a problem, report the error. Do not proceed to the next
						// row.
						result.setErrorMsg("Error while processing row " + (rowIndex + 1) + " - "
								+ e.getMessage());
						// Hopefully this one doesn't throw an error!
						String titleName = getContent(sheet, rowIndex,
								TVNConstants.TITLE_ASSET_NAME);
						result.setFailedTitle(titleName);
						return result;
					}
				}
				removeEmptyPlanners(doc);

				print(doc, outputPath);
				return result;
			}
		} catch (BiffException e) {
			String errorMsg = "Error while converting file: " + xlsFilepath + " : "
					+ e.getMessage();
			result.setErrorMsg(errorMsg);
			return result;
		} catch (IOException e) {
			String errorMsg = "Error while converting file: " + xlsFilepath + " : "
					+ e.getMessage();
			result.setErrorMsg(errorMsg);
			return result;
		}
		return result;
	}

	/**
	 * prepares the package, title, movie , poster, preview, poster and box cover titles for this
	 * row in the xls
	 * 
	 * @param sheet
	 * @param rowIndex
	 *            row to be processed
	 * @param doc
	 * @param result
	 *            result object which will be finally returned back to the caller. This contains the
	 *            list of failed titles if any.
	 */
	private void processRow(Sheet sheet, int rowIndex, Document doc, ConversionResult result) {

		String actionDate = getContent(sheet, rowIndex, TVNConstants.ACTION_DATE);
		if (actionDate == null || actionDate.trim().length() == 0) {
			// If action date is not specified and there is any data in this
			// row, report an error for missing action date.
			if (rowHasData(sheet, rowIndex)) {
				String errorMsg = "Action date not specified.";
				throw new RuntimeException(errorMsg);
			} else {
				// if there is no action date and any other content in a row,
				// that means there is no title info in that row.
				return;
			}
		}

		String currRowDate = getContent(sheet, rowIndex, TVNConstants.ACTION_DATE);
		String providerID = getContent(sheet, rowIndex, TVNConstants.PROVIDER_ID);
		// If the date is provided, convert the date to 'yyyy-mm-ddT00:00:00' format. This
		// format is required for unmarshalling the planner.
		Element plannerElement = getPlannerElement(doc, formatDate(currRowDate,
				PROVIDER_TIME_FORMAT, PMM_DATETIME_FORMAT), providerID);

		// package title
		Element packageTitle = preparePackage(sheet, rowIndex, doc);

		// title level title
		Element titleElement = prepareTitle(sheet, rowIndex, doc);
		NodeList childrenElements = packageTitle
				.getElementsByTagName(PlannerConstants.TITLE_CHILDREN_ELEMENT);
		childrenElements.item(0).appendChild(titleElement);

		// movie title
		Element movieTitle = prepareMovie(sheet, rowIndex, doc);
		if (movieTitle != null) {
			childrenElements = titleElement
					.getElementsByTagName(PlannerConstants.TITLE_CHILDREN_ELEMENT);
			childrenElements.item(0).appendChild(movieTitle);
		}

		// poster title
		Element posterTitle = preparePoster(sheet, rowIndex, doc);
		if (posterTitle != null) {
			childrenElements = titleElement
					.getElementsByTagName(PlannerConstants.TITLE_CHILDREN_ELEMENT);
			childrenElements.item(0).appendChild(posterTitle);
		}

		// preview/trailer title
		Element previewTitle = preparePreview(sheet, rowIndex, doc);
		if (previewTitle != null) {
			childrenElements = titleElement
					.getElementsByTagName(PlannerConstants.TITLE_CHILDREN_ELEMENT);
			childrenElements.item(0).appendChild(previewTitle);
		}

		// box cover title
		Element boxCoverTitle = prepareBoxCover(sheet, rowIndex, doc);
		if (boxCoverTitle != null) {
			childrenElements = titleElement
					.getElementsByTagName(PlannerConstants.TITLE_CHILDREN_ELEMENT);
			childrenElements.item(0).appendChild(boxCoverTitle);
		}

		// attach the package title to the planner
		plannerElement.appendChild(packageTitle);
	}

	/**
	 * Returns true if there is any content in this row else returns false.
	 * 
	 * @param sheet
	 * @param rowIndex
	 * @return
	 */
	private boolean rowHasData(Sheet sheet, int rowIndex) {
		Cell[] cells = sheet.getRow(rowIndex);
		for (Cell c : cells) {
			if (c.getContents() != null && c.getContents().trim().length() > 0) {
				return true;
			}
		}
		return false;
	}

	/**
	 * @param sheet
	 * @param rowIndex
	 * @param doc
	 * @return
	 */
	private Element prepareBoxCover(Sheet sheet, int rowIndex, Document doc) {
		// do this only if the box cover is present
		String boxCoverAssetId = getContent(sheet, rowIndex, TVNConstants.BOX_COVER_ASSET_ID);
		if (boxCoverAssetId == null || boxCoverAssetId.trim().length() <= 0)
			return null;

		Element boxCoverElement = doc.createElement(PlannerConstants.PLANNER_TITLE_ELEMENT);
		Element metadata = doc.createElement(PlannerConstants.METADATA_ELEMENT);

		// Asset_ID
		String assetId = getContent(sheet, rowIndex, TVNConstants.BOX_COVER_ASSET_ID);
		Element assetIdField = createField(doc, PlannerConstants.ASSET_ID, assetId);
		metadata.appendChild(assetIdField);

		// Asset_Name
		String assetName = getContent(sheet, rowIndex, TVNConstants.BOX_COVER_ASSET_NAME);
		Element assetNameField = createField(doc, PlannerConstants.ASSET_NAME, assetName);
		metadata.appendChild(assetNameField);

		// Content_FileSize
		String fileSize = getContent(sheet, rowIndex, TVNConstants.BOX_COVER_FILE_SIZE);
		Element fileSizeField = createField(doc, PlannerConstants.FILE_SIZE, fileSize);
		metadata.appendChild(fileSizeField);

		// Content_CheckSum
		String checksum = getContent(sheet, rowIndex, TVNConstants.BOX_COVER_CONTENT_CHECKSUM);
		Element checksumField = createField(doc, PlannerConstants.CONTENT_CHECKSUM, checksum);
		metadata.appendChild(checksumField);

		// content file name
		String fileName = getContent(sheet, rowIndex, TVNConstants.BOX_COVER_FILE_NAME);
		Element fileNameField = createField(doc, PlannerConstants.FILENAME, fileName);
		metadata.appendChild(fileNameField);

		metadata = addAMSProperties(sheet, rowIndex, doc, metadata);
		boxCoverElement = createRequiredElements(boxCoverElement, doc,
				PlannerConstants.BOXCOVER_TITLE_NAME);
		boxCoverElement.appendChild(metadata);

		return boxCoverElement;
	}

	/**
	 * removes planner elements with no titles from the doc.
	 * 
	 * @param doc
	 */
	private void removeEmptyPlanners(Document doc) {
		// if there are no titles for this planner, remove this planner element
		// this is done because there could be such empty elements for rows with no data
		// in planners. Can't restrict creation based on date available becuase thats a program list
		// format.

		Element planners = (Element) doc.getElementsByTagName(PlannerConstants.PLANNERS_ELEMENT)
				.item(0);
		NodeList plannerList = doc.getElementsByTagName(PlannerConstants.PLANNER_ELEMENT);

		List<Element> plannersToDelete = new ArrayList<Element>();
		for (int i = 0; i < plannerList.getLength(); i++) {
			Element planner = (Element) plannerList.item(i);
			if (planner.getElementsByTagName(PlannerConstants.PLANNER_TITLE_ELEMENT).getLength() == 0) {
				plannersToDelete.add(planner);
			}
		}
		for (Element planner : plannersToDelete) {
			planners.removeChild(planner);
		}
	}

	/**
	 * Checks if there is a planner with this date already and returns that else creates a new
	 * planner element. It also inserts the date and providerId if a new element is created.
	 * 
	 * @param doc
	 * @param currRowDate
	 * @param providerID
	 * @return
	 */
	private Element getPlannerElement(Document doc, String currRowDate, String providerID) {
		NodeList plannerList = doc.getElementsByTagName(PlannerConstants.PLANNER_ELEMENT);
		for (int i = 0; i < plannerList.getLength(); i++) {
			Element currPlanner = ((Element) plannerList.item(i));
			NodeList dateElements = currPlanner
					.getElementsByTagName(PlannerConstants.PLANNER_DATE_ELEMENT);
			if (dateElements.item(0).getTextContent().equals(currRowDate)) {
				return currPlanner;
			}
		}

		// if not found create a new element and set the date and providerId with the provided one.
		Element plannerElement = createPlannerElement(doc);
		NodeList dateElements = plannerElement
				.getElementsByTagName(PlannerConstants.PLANNER_DATE_ELEMENT);

		dateElements.item(0).setTextContent(currRowDate);

		NodeList providerIDElements = plannerElement
				.getElementsByTagName(PlannerConstants.PROVIDERID_ELEMENT);
		providerIDElements.item(0).setTextContent(providerID);

		doc.getElementsByTagName(PlannerConstants.PLANNERS_ELEMENT).item(0).appendChild(
				plannerElement);
		return plannerElement;
	}

	/**
	 * converts a date from the specified format to another.
	 * 
	 * @param dateToFormat
	 *            date to be converted
	 * @return string representation of the converted date or throws an error if unsuccessful.
	 */
	private String formatDate(String dateToFormat, String fromFormat, String toFormat) {
		if (dateToFormat.trim().length() > 0) {
			try {
				SimpleDateFormat from = new SimpleDateFormat(fromFormat);
				SimpleDateFormat to = new SimpleDateFormat(toFormat);
				return to.format(from.parse(dateToFormat));
			} catch (ParseException e) {
				String errorMsg = "Error parsing date: " + dateToFormat;
				throw new RuntimeException(errorMsg, e);
			}
		} else {
			return dateToFormat;
		}

	}

	private Element preparePreview(Sheet sheet, int rowIndex, Document doc) {
		// do this only if the poster is presents
		String previewAssetId = getContent(sheet, rowIndex, TVNConstants.PREVIEW_ASSET_ID);
		if (previewAssetId == null || previewAssetId.trim().length() <= 0)
			return null;

		Element previewElement = doc.createElement(PlannerConstants.PLANNER_TITLE_ELEMENT);
		Element metadata = doc.createElement(PlannerConstants.METADATA_ELEMENT);

		// Asset_ID
		String assetId = getContent(sheet, rowIndex, TVNConstants.PREVIEW_ASSET_ID);
		Element assetIdField = createField(doc, PlannerConstants.ASSET_ID, assetId);
		metadata.appendChild(assetIdField);

		// Asset_Name
		String assetName = getContent(sheet, rowIndex, TVNConstants.PREVIEW_ASSET_NAME);
		Element assetNameField = createField(doc, PlannerConstants.ASSET_NAME, assetName);
		metadata.appendChild(assetNameField);

		// Audio_Type
		String audioType = getContent(sheet, rowIndex, TVNConstants.AUDIO_TYPE);
		Element audioTypeField = createField(doc, PlannerConstants.AUDIO_TYPE, audioType);
		metadata.appendChild(audioTypeField);

		// HDContent
		String hdContent = getContent(sheet, rowIndex, TVNConstants.HDCONTENT);
		Element hdContentField = createField(doc, PlannerConstants.HDCONTENT, hdContent);
		metadata.appendChild(hdContentField);

		// Rating
		String rating = getContent(sheet, rowIndex, TVNConstants.RATING);
		Element ratingField = createField(doc, PlannerConstants.RATING, rating);
		metadata.appendChild(ratingField);

		// Run_Time
		String runTime = getContent(sheet, rowIndex, TVNConstants.PREVIEW_RUNTIME);
		Element runTimeField = createField(doc, PlannerConstants.PREVIEW_RUN_TIME, runTime);
		metadata.appendChild(runTimeField);

		// Content_FileSize
		String fileSize = getContent(sheet, rowIndex, TVNConstants.PREVIEW_FILE_SIZE);
		Element fileSizeField = createField(doc, PlannerConstants.FILE_SIZE, fileSize);
		metadata.appendChild(fileSizeField);

		// Content_CheckSum
		String checksum = getContent(sheet, rowIndex, TVNConstants.PREVIEW_CONTENT_CHECKSUM);
		Element checksumField = createField(doc, PlannerConstants.CONTENT_CHECKSUM, checksum);
		metadata.appendChild(checksumField);

		// content file name
		String fileName = getContent(sheet, rowIndex, TVNConstants.PREVIEW_FILE_NAME);
		Element fileNameField = createField(doc, PlannerConstants.FILENAME, fileName);
		metadata.appendChild(fileNameField);

		metadata = addAMSProperties(sheet, rowIndex, doc, metadata);
		previewElement = createRequiredElements(previewElement, doc,
				PlannerConstants.PREVIEW_TITLE_NAME);
		previewElement.appendChild(metadata);

		return previewElement;
	}

	private Element preparePoster(Sheet sheet, int rowIndex, Document doc) {
		// do this only if the poster is presents
		String posterAssetId = getContent(sheet, rowIndex, TVNConstants.POSTER_ASSET_ID);
		if (posterAssetId == null || posterAssetId.trim().length() <= 0)
			return null;

		Element posterElement = doc.createElement(PlannerConstants.PLANNER_TITLE_ELEMENT);
		Element metadata = doc.createElement(PlannerConstants.METADATA_ELEMENT);

		// Asset_ID
		Element assetIdField = createField(doc, PlannerConstants.ASSET_ID, posterAssetId);
		metadata.appendChild(assetIdField);

		// Asset_Name
		String assetName = getContent(sheet, rowIndex, TVNConstants.POSTER_ASSET_NAME);
		Element assetNameField = createField(doc, PlannerConstants.ASSET_NAME, assetName);
		metadata.appendChild(assetNameField);

		metadata = addAMSProperties(sheet, rowIndex, doc, metadata);

		// Content_CheckSum
		String checksum = getContent(sheet, rowIndex, TVNConstants.POSTER_CHECKSUM);
		Element checksumField = createField(doc, PlannerConstants.CHECKSUM, checksum);
		metadata.appendChild(checksumField);

		// Content_FileSize
		String fileSize = getContent(sheet, rowIndex, TVNConstants.POSTER_FILE_SIZE);
		Element fileSizeField = createField(doc, PlannerConstants.FILE_SIZE, fileSize);
		metadata.appendChild(fileSizeField);

		// content
		String fileName = getContent(sheet, rowIndex, TVNConstants.POSTER_FILENAME);
		Element fileNameField = createField(doc, PlannerConstants.FILENAME, fileName);
		metadata.appendChild(fileNameField);

		posterElement = createRequiredElements(posterElement, doc,
				PlannerConstants.POSTER_TITLE_NAME);
		posterElement.appendChild(metadata);

		return posterElement;
	}

	/**
	 * Adds metadata fields corresponding to the required attributes for the AMS element.
	 * 
	 * @param sheet
	 * @param rowIndex
	 * @param doc
	 * @param metadata
	 * @return
	 */
	private Element addAMSProperties(Sheet sheet, int rowIndex, Document doc, Element metadata) {
		// Creation_Date
		String creationDate = getContent(sheet, rowIndex, TVNConstants.CREATION_DATE);
		creationDate = formatDate(creationDate, CREATION_DATE_FORMAT, ADI_DATE_FORMAT);
		Element creationDateField = createField(doc, PlannerConstants.CREATION_DATE, creationDate);
		metadata.appendChild(creationDateField);

		// Provider
		String provider = getContent(sheet, rowIndex, TVNConstants.PROVIDER);
		Element providerField = createField(doc, PlannerConstants.PROVIDER, provider);
		metadata.appendChild(providerField);

		// Provider_ID
		String providerId = getContent(sheet, rowIndex, TVNConstants.PROVIDER_ID);
		Element providerIdField = createField(doc, PlannerConstants.PROVIDER_ID, providerId);
		metadata.appendChild(providerIdField);

		// Description
		String description = getContent(sheet, rowIndex, TVNConstants.DESCRIPTION);
		Element descriptionField = createField(doc, PlannerConstants.DESCRIPTION, description);
		metadata.appendChild(descriptionField);

		// Version_Major
		String majorVersion = getContent(sheet, rowIndex, TVNConstants.VERSION_MAJOR);
		Element majorVersionField = createField(doc, PlannerConstants.VERSION_MAJOR, majorVersion);
		metadata.appendChild(majorVersionField);

		// Version_Minor
		String minorVersion = getContent(sheet, rowIndex, TVNConstants.VERSION_MINOR);
		Element minorVersionField = createField(doc, PlannerConstants.VERSION_MINOR, minorVersion);
		metadata.appendChild(minorVersionField);

		// Product
		String product = getContent(sheet, rowIndex, TVNConstants.PRODUCT);
		Element productField = createField(doc, PlannerConstants.PRODUCT, product);
		metadata.appendChild(productField);

		return metadata;
	}

	/**
	 * Prepares ADI movie level title element
	 * 
	 * @param sheet
	 * @param rowIndex
	 * @param doc
	 * @param rootTitle
	 * @return
	 */
	private Element prepareMovie(Sheet sheet, int rowIndex, Document doc) {
		// do this only if the movie asset is present.
		String movieAssetId = getContent(sheet, rowIndex, TVNConstants.MOVIE_ASSET_ID);
		if (movieAssetId == null || movieAssetId.trim().length() <= 0)
			return null;

		Element movieElement = doc.createElement(PlannerConstants.PLANNER_TITLE_ELEMENT);
		Element metadata = doc.createElement(PlannerConstants.METADATA_ELEMENT);

		// Asset_ID
		Element assetIdField = createField(doc, PlannerConstants.ASSET_ID, movieAssetId);
		metadata.appendChild(assetIdField);

		// Asset_Name
		String assetName = getContent(sheet, rowIndex, TVNConstants.MOVIE_ASSET_NAME);
		Element assetNameField = createField(doc, PlannerConstants.ASSET_NAME, assetName);
		metadata.appendChild(assetNameField);

		metadata = addAMSProperties(sheet, rowIndex, doc, metadata);

		// Audio_Type
		String audioType = getContent(sheet, rowIndex, TVNConstants.AUDIO_TYPE);
		Element audioTypeField = createField(doc, PlannerConstants.AUDIO_TYPE, audioType);
		metadata.appendChild(audioTypeField);

		// HDContent
		String hdContent = getContent(sheet, rowIndex, TVNConstants.HDCONTENT);
		Element hdContentField = createField(doc, PlannerConstants.HDCONTENT, hdContent);
		metadata.appendChild(hdContentField);

		// Content_FileSize
		String fileSize = getContent(sheet, rowIndex, TVNConstants.MOVIE_FILE_SIZE);
		Element fileSizeField = createField(doc, PlannerConstants.FILE_SIZE, fileSize);
		metadata.appendChild(fileSizeField);

		// Content_CheckSum
		String checksum = getContent(sheet, rowIndex, TVNConstants.MOVIE_CONTENT_CHECKSUM);
		Element checksumField = createField(doc, PlannerConstants.CONTENT_CHECKSUM, checksum);
		metadata.appendChild(checksumField);

		// content
		String content = getContent(sheet, rowIndex, TVNConstants.MOVIE_CONTENT_FILE_NAME);
		Element contentField = createField(doc, PlannerConstants.CONTENT_FILE_NAME, content);
		metadata.appendChild(contentField);

		movieElement = createRequiredElements(movieElement, doc, PlannerConstants.MOVIE_TITLE_NAME);
		movieElement.appendChild(metadata);

		return movieElement;
	}

	/**
	 * Prepares the ADI title level title element
	 * 
	 * @param sheet
	 * @param rowIndex
	 * @param doc
	 * @param rootTitle
	 * @return
	 */
	private Element prepareTitle(Sheet sheet, int rowIndex, Document doc) {
		Element titleElement = doc.createElement(PlannerConstants.PLANNER_TITLE_ELEMENT);
		Element metadata = doc.createElement(PlannerConstants.METADATA_ELEMENT);

		// title
		String title = getContent(sheet, rowIndex, TVNConstants.TITLE);
		Element titleField = createField(doc, PlannerConstants.TITLE, title);
		metadata.appendChild(titleField);

		// asset id
		String assetId = getContent(sheet, rowIndex, TVNConstants.TITLE_ASSET_ID);
		Element assetIdField = createField(doc, PlannerConstants.ASSET_ID, assetId);
		metadata.appendChild(assetIdField);

		// asset name
		Element assetNameField = createField(doc, PlannerConstants.ASSET_NAME,
				PlannerConstants.TITLE_ASSET_NAME);
		metadata.appendChild(assetNameField);

		metadata = addAMSProperties(sheet, rowIndex, doc, metadata);

		// summary_short
		String summary_short = getContent(sheet, rowIndex, TVNConstants.SUMMARY_SHORT);
		Element shortSummaryField = createField(doc, PlannerConstants.TITLE_SUMMARY_SHORT,
				summary_short);
		metadata.appendChild(shortSummaryField);

		// summary_medium
		String summary_medium = getContent(sheet, rowIndex, TVNConstants.SUMMARY_MEDIUM);
		Element mediumSummaryField = createField(doc, PlannerConstants.TITLE_SUMMARY_MEDIUM,
				summary_medium);
		metadata.appendChild(mediumSummaryField);

		// display runtime
		String displayRuntime = getContent(sheet, rowIndex, TVNConstants.DISPLAY_RUNTIME);
		Element displayRuntimeField = createField(doc, PlannerConstants.TITLE_DISPLAY_RUNTIME,
				displayRuntime);
		metadata.appendChild(displayRuntimeField);

		// runtime
		String runtime = getContent(sheet, rowIndex, TVNConstants.MOVIE_RUNTIME);
		Element runtimeField = createField(doc, PlannerConstants.TITLE_RUNTIME, runtime);
		metadata.appendChild(runtimeField);

		// Rating
		String rating = getContent(sheet, rowIndex, TVNConstants.RATING);
		Element ratingField = createField(doc, PlannerConstants.RATING, rating);
		metadata.appendChild(ratingField);

		// Billing_ID
		String billingId = getContent(sheet, rowIndex, TVNConstants.BILLING_ID);
		Element billingIdField = createField(doc, PlannerConstants.TITLE_BILLING_ID, billingId);
		metadata.appendChild(billingIdField);

		// Licensing_Window_Start
		String liscStart = getContent(sheet, rowIndex, TVNConstants.LISC_START_WINDOW);
		liscStart = formatDate(liscStart, LISENCE_DATE_FORMAT, ADI_DATE_FORMAT);
		Element liscStartField = createField(doc, PlannerConstants.TITLE_LISC_START_WINDOW,
				liscStart);
		metadata.appendChild(liscStartField);

		// Licensing_Window_End
		String liscEnd = getContent(sheet, rowIndex, TVNConstants.LISC_END_WINDOW);
		liscEnd = formatDate(liscEnd, LISENCE_DATE_FORMAT, ADI_DATE_FORMAT);
		Element liscEndField = createField(doc, PlannerConstants.TITLE_LISC_END_WINDOW, liscEnd);
		metadata.appendChild(liscEndField);

		// Title_Brief
		String titleBrief = getContent(sheet, rowIndex, TVNConstants.TITLE_BRIEF);
		Element titleBriefField = createField(doc, PlannerConstants.TITLE_BRIEF, titleBrief);
		metadata.appendChild(titleBriefField);

		// Preview_Period
		String titlePreviewPeriod = getContent(sheet, rowIndex, TVNConstants.TITLE_PREVIEW_PERIOD);
		Element titlePreviewPeriodField = createField(doc, PlannerConstants.TITLE_PREVIEW_PERIOD,
				titlePreviewPeriod);
		metadata.appendChild(titlePreviewPeriodField);

		// Provider_QA_Contact
		String qaContact = getContent(sheet, rowIndex, TVNConstants.PROVIDER_QA_CONTACT);
		Element qaContactField = createField(doc, PlannerConstants.TITLE_PROVIDER_QA_CONTACT,
				qaContact);
		metadata.appendChild(qaContactField);

		// Category is a required field as per CableLabs VOD 1.1 spec but works fine without it too!

		titleElement = createRequiredElements(titleElement, doc, PlannerConstants.TITLE_NAME);
		titleElement.appendChild(metadata);

		return titleElement;
	}

	/**
	 * Gets the string content of the cell in the given sheet. 'dataRequired' indicates if this cell
	 * must have some value. An exception is thrown in case there is no value for a cell that must
	 * have some data.
	 * 
	 * @param sheet
	 * @param row
	 * @param searchContent
	 * @return null or string representation of the content.
	 */
	private String getContent(Sheet sheet, int row, String searchContent) {
		int col = TVNConstants.INVALID_COLUMN;
		Cell contentCell;
		try {
			contentCell = sheet.findCell(searchContent);
			col = contentCell.getColumn();
		} catch (RuntimeException e) {
			throw new RuntimeException("Could not find column '" + searchContent + "'");
		}

		// the column text should be found only in the header row.
		if (contentCell.getRow() == TVNConstants.TVN_HEADER_ROW_INDEX) {
			try {
				return sheet.getCell(col, row).getContents();
			} catch (RuntimeException e) {
				String errorMsg = "Could not get content for column '" + searchContent
						+ "' in row " + row;
				throw new RuntimeException(errorMsg);
			}
		} else {
			throw new RuntimeException("Could not find column '" + searchContent + "'");
		}
	}

	/**
	 * Prepares the root title for this row in the work sheet
	 * 
	 * @param sheet
	 * @param rowIndex
	 * @param doc
	 * @return
	 */
	private Element preparePackage(Sheet sheet, int rowIndex, Document doc) {
		Element rootTitle = doc.createElement(PlannerConstants.TITLE_ELEMENT);
		Element metadata = doc.createElement(PlannerConstants.METADATA_ELEMENT);

		// asset id
		String assetId = getContent(sheet, rowIndex, TVNConstants.PACKAGE_ASSET_ID);
		Element assetIdField = createField(doc, PlannerConstants.ASSET_ID, assetId);
		metadata.appendChild(assetIdField);

		// asset name
		String assetName = getContent(sheet, rowIndex, TVNConstants.PACKAGE_ASSET_NAME);
		Element assetNameField = createField(doc, PlannerConstants.ASSET_NAME, assetName);
		metadata.appendChild(assetNameField);

		metadata = addAMSProperties(sheet, rowIndex, doc, metadata);

		rootTitle = createRequiredElements(rootTitle, doc, PlannerConstants.PACKAGE_TITLE_NAME);
		rootTitle.appendChild(metadata);

		return rootTitle;
	}

	/**
	 * creates the id, spec, name, asset location and metadata location elements for the title
	 * passed
	 * 
	 * @param title
	 * @param doc
	 * @param assetName
	 * @return title with new elements.
	 */
	private Element createRequiredElements(Element title, Document doc, String titleName) {

		// specification
		Element spec = doc.createElement(PlannerConstants.SPEC_ELEMENT);
		spec.setTextContent(PlannerConstants.CABLELABS_VOD);
		title.appendChild(spec);

		// name
		Element name = doc.createElement(PlannerConstants.NAME_ELEMENT);
		name.setTextContent(titleName);
		title.appendChild(name);

		// assetlocation
		Element assetLocation = doc.createElement(PlannerConstants.ASSET_LOCATION_ELEMENT);
		title.appendChild(assetLocation);

		// metadatalocation
		Element metadataLocation = doc.createElement(PlannerConstants.METADATA_LOCATION_ELEMENT);
		title.appendChild(metadataLocation);

		// children
		Element children = doc.createElement(PlannerConstants.TITLE_CHILDREN_ELEMENT);
		title.appendChild(children);

		return title;
	}

	/**
	 * Creates a field element which has name and value child elements for the given name and value.
	 * 
	 * @param doc
	 * @param name
	 * @param value
	 * @return
	 */
	private Element createField(Document doc, String name, String value) {

		Element field = doc.createElement(PlannerConstants.FIELD_ELEMENT);

		Element fieldName = doc.createElement(PlannerConstants.FIELD_NAME_ELEMENT);
		fieldName.setTextContent(name);
		field.appendChild(fieldName);

		Element fieldValue = doc.createElement(PlannerConstants.FIELD_VALUE_ELEMENT);
		fieldValue.setTextContent(value);
		field.appendChild(fieldValue);

		return field;
	}

	/**
	 * gets the row with the content 'the active line'. This line marks the start of the data row.
	 * Before this row, there could be many rows for some examples, headers etc.
	 * 
	 * @param sheet
	 *            sheet from which the data row start needs to be determined.
	 * @return zero based row index which has contents = 'the active line'
	 */
	private int findActiveDataRow(Sheet sheet) {

		int rowCount = sheet.getRows();
		for (int i = 0; i < rowCount; i++) {
			Cell c = sheet.getCell(TVNConstants.ACTIVE_LINE_COLUMN, i);
			if (c.getContents().equalsIgnoreCase(TVNConstants.ACTIVE_LINE))
				return i;
		}

		return TVNConstants.INVALID_ROW;
	}

	/**
	 * @return The xml document with the planners element
	 */
	private Document createDocument() {
		Document doc;
		try {
			DocumentBuilderFactory domFactory = DocumentBuilderFactory.newInstance();
			DocumentBuilder domBuilder = domFactory.newDocumentBuilder();
			doc = domBuilder.newDocument();

			Element plannersElement = doc.createElement(PlannerConstants.PLANNERS_ELEMENT);
			doc.appendChild(plannersElement);

			return doc;
		} catch (ParserConfigurationException e) {
			logger.error("Error" + e.toString());
		}

		return null;
	}

	/**
	 * @param doc
	 *            doc for which the planner element needs to be created.
	 * @return the planner element created
	 */
	private Element createPlannerElement(Document doc) {
		Element plannerElement = doc.createElement(PlannerConstants.PLANNER_ELEMENT);
		Element partnerElement = doc.createElement(PlannerConstants.PROVIDERID_ELEMENT);
		plannerElement.appendChild(partnerElement);
		Element dateElement = doc.createElement(PlannerConstants.PLANNER_DATE_ELEMENT);
		plannerElement.appendChild(dateElement);

		return plannerElement;
	}

	private void print(Document doc, String outputFile) {
		try {
			TransformerFactory tFactory = TransformerFactory.newInstance();
			Transformer transformer = tFactory.newTransformer();

			DOMSource source = new DOMSource(doc);

			FileOutputStream fs = new FileOutputStream(outputFile);
			StreamResult result = new StreamResult(fs);
			transformer.transform(source, result);

		} catch (TransformerConfigurationException e) {
			String errorMsg = "Error while printing output doc: " + e.toString();
			logger.error(errorMsg);
			throw new RuntimeException(errorMsg);
		} catch (TransformerFactoryConfigurationError e) {
			String errorMsg = "Error while printing output doc: " + e.toString();
			logger.error(errorMsg);
			throw new RuntimeException(errorMsg);
		} catch (TransformerException e) {
			String errorMsg = "Error while printing output doc: " + e.toString();
			logger.error(errorMsg);
			throw new RuntimeException(errorMsg);
		} catch (FileNotFoundException e) {
			String errorMsg = "Error while printing output doc: " + e.toString();
			logger.error(errorMsg);
			throw new RuntimeException(errorMsg);
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.tandbergtv.watchpoint.pmm.util.schedule.ingest.IScheduleConverter#canConvert(java.lang.String)
	 * 
	 * This check is purely based on the file extension
	 */
	public boolean canConvert(String inputFilename) {
		int index = inputFilename.lastIndexOf(FILE_NAME_EXTN_SEPARATOR);
		String xtn = inputFilename.substring(index + 1);
		return xtn.equals(VALID_FILE_EXTN);
	}
}