package de.codecentric.util;

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;

import de.codecentric.domain.Session;
import de.codecentric.domain.SessionType;

public class XlsSessionReader {

    public static int ID_OFFSET = 1000000;
    private static final short COL_DATE = 0;
    private static final short COL_START = 1;
    private static final short COL_END = 2;
    private static final short COL_TOPIC = 3;
    private static final short COL_AUTHOR = 4;
    private static final short COL_AUTHOR2 = 5;
    private static final short COL_DESCRIPTION = 6;
    private static final short COL_LOCATION = 7;
    private static final short COL_TYPE = 8;
    private static final short COL_AUTHORINFO = 9;
    private static final short COL_AUTHOR2INFO = 10;
    private static final short COL_AUTHORIMAGEURL = 11;
    private static final short COL_AUHTOR2IMAGEURL = 12;

    public List<Session> readAllSessions() {
        try {
            final InputStream is = this.getClass().getClassLoader().getResourceAsStream("program.xls");
            return readAllSessions(is);
        } catch (Exception e) {
            throw new RuntimeException("Error while reading sessions from file", e);
        }
    }

    public List<Session> readAllSessions(String filename) {
        try {
            final InputStream is = new BufferedInputStream(new FileInputStream(filename));
            return readAllSessions(is);
        } catch (Exception e) {
            throw new RuntimeException("Error while reading sessions from file " + filename, e);
        }
    }

    private List<Session> readAllSessions(InputStream is) {
        final List<Session> result = new ArrayList<Session>();

        try {
            final POIFSFileSystem fileSystem = new POIFSFileSystem(is);
            final HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
            final HSSFSheet sheet = workBook.getSheet("Alle Tage");

            int rows = sheet.getPhysicalNumberOfRows();
            // as the row is a header we start with the second one
            for (int r = 1; r < rows; r++) {
                final HSSFRow row = sheet.getRow(r);
                if (row == null) {
                    continue;
                }
                final Session session = getSessionFromRow(row, r);
                if (session != null) {
                    result.add(session);
                }
            }
        } catch (Exception e) {
            throw new RuntimeException("Error while reading sessions from file", e);
        }
        return result;
    }

    private Session getSessionFromRow(HSSFRow row, int id) {
        Session result = null;
        final String date = getCellValue(row.getCell(COL_DATE, Row.RETURN_BLANK_AS_NULL));
        final String start = getCellValue(row.getCell(COL_START, Row.RETURN_BLANK_AS_NULL));
        final String end = getCellValue(row.getCell(COL_END, Row.RETURN_BLANK_AS_NULL));
        final String title = getCellValue(row.getCell(COL_TOPIC, Row.RETURN_BLANK_AS_NULL));
        final String author = getCellValue(row.getCell(COL_AUTHOR, Row.RETURN_BLANK_AS_NULL));
        final String author2 = getCellValue(row.getCell(COL_AUTHOR2, Row.RETURN_BLANK_AS_NULL));
        final String description = getCellValue(row.getCell(COL_DESCRIPTION, Row.RETURN_BLANK_AS_NULL));
        final String location = getCellValue(row.getCell(COL_LOCATION, Row.RETURN_BLANK_AS_NULL));
        final SessionType type = SessionType.getTypeForString(getCellValue(row.getCell(COL_TYPE, Row.RETURN_BLANK_AS_NULL)));
        final String authorInfo = getCellValue(row.getCell(COL_AUTHORINFO, Row.RETURN_BLANK_AS_NULL));
        final String author2Info = getCellValue(row.getCell(COL_AUTHOR2INFO, Row.RETURN_BLANK_AS_NULL));
        final String authorImgUrl = getCellValue(row.getCell(COL_AUTHORIMAGEURL, Row.RETURN_BLANK_AS_NULL));
        final String author2ImgUrl = getCellValue(row.getCell(COL_AUHTOR2IMAGEURL, Row.RETURN_BLANK_AS_NULL));

        if (title != null || author != null || description != null) {
            result = new Session(date, start, end, title, author, author2, description, location, type, authorInfo, author2Info, authorImgUrl, author2ImgUrl, id + ID_OFFSET);
        }

        try {
            SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy");
            Date startDate = sdf.parse(date);
            result.setStartDate(startDate);
        } catch (Exception e) {
        }

        return result;
    }

    private String getCellValue(HSSFCell cell) {
        String result = null;
        if (cell != null) {
            switch (cell.getCellType()) {

            case HSSFCell.CELL_TYPE_FORMULA:
                result = cell.getCellFormula().toString();
                break;

            case HSSFCell.CELL_TYPE_NUMERIC:
                result = "" + cell.getNumericCellValue();
                break;

            case HSSFCell.CELL_TYPE_STRING:
                result = cell.getStringCellValue();
                break;

            default:
            }
        }
        return result;
    }

}