package species.formatReader;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.codehaus.groovy.grails.web.json.JSONArray;
import org.codehaus.groovy.grails.web.json.JSONElement;
import org.codehaus.groovy.grails.web.json.JSONObject;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class SpreadsheetWriter {

    static final String KEYVALUE_SEP = "#11#";
    static final String COLUMN_SEP = "#12#";
    static final String FIELD_SEP = "#13#";
    
    public static void writeSpreadsheet(File f, InputStream inp, JSONElement headerMarkers, String writeContributor, String contEmail, JSONArray orderedArray) {
        //System.out.println ("params in write SPREADSHEET " + gridData + " ----- " + headerMarkers);
        try {
//            Workbook wb = WorkbookFactory.create(inp);
//            TESTING following line
            Workbook wb = new XSSFWorkbook(inp);
            int sheetNo = 0;
            //writeDataInSheet(wb, gridData, sheetNo, writeContributor, contEmail, orderedArray);
            writeHeadersInFormat(wb, headerMarkers, orderedArray);
            Sheet sheet = wb.getSheet("headerMetadata");
            if(sheet != null) {
                wb.setSheetOrder("headerMetadata", 2);
            }
            
            FileOutputStream out = new FileOutputStream(f);
            wb.write(out);
            out.close();
        }
        catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        //catch (InvalidFormatException e) {
       //     e.printStackTrace();
       // }

    }

    public static void writeDataInSheet(Workbook wb, JSONArray gridData, int sheetNo, String writeContributor, String contEmail, JSONArray orderedArray) {
        Sheet sheet = wb.getSheetAt(sheetNo);
        Iterator<Row> rowIterator = sheet.iterator();
        int index = 0;
        int i = 0;
        boolean headerRow = true;
        int gDataSize = gridData.length();
        JSONObject rowData = gridData.getJSONObject(index);
        Iterator<String> keys = rowData.keys();
        int numKeys = 0;
        while(keys.hasNext()){
            String kk = keys.next();
            numKeys++;
        }
        String[] keysArray = new String[numKeys];
        //String[] keysArray = orderedArray;
        for (int k = 0; k< numKeys; k++){
            keysArray[k] = orderedArray.getString(k); 
        }
        Row row = rowIterator.next();
        for(int a = 0; a < numKeys; a++){
            Cell cell = row.getCell(i, Row.CREATE_NULL_AS_BLANK);
            //System.out.println("======PRINTING THIS TO HEADER CELL===== " + keysArray[a]);
            cell.setCellValue(keysArray[a]);
            i++;
        }
        int lastHeaderCellNum = row.getLastCellNum();
        for(int j = i; j <= lastHeaderCellNum; j++) {
            Cell cell = row.getCell(j, Row.CREATE_NULL_AS_BLANK);
            cell.setCellValue("");
        }

        for (int k = 0; k < gDataSize; k++) {
            //System.out.println("REACHED FOR LOOP");
            rowData = gridData.getJSONObject(index);
            //mapRow.put(gridData.get(count));
            //rowIterator.hasNext();
            //
            if(rowIterator.hasNext()){
                row = rowIterator.next();
            }
            else{
                row = sheet.createRow(k+1);
                for(int a = 0; a < numKeys; a++){
                    Cell cell = row.createCell(a);
                }
            }
            i = 0;
            //System.out.println("============ "); 
            //System.out.println(gridData);

            //for ( Map.Entry<String, String> entry : mapRow.entrySet()) {
            //while( keys.hasNext() ){
            for(int a = 0; a < numKeys; a++){
                //String key = (String)keys.next();
                Cell cell = row.getCell(i, Row.CREATE_NULL_AS_BLANK);
                //System.out.println ("=====PRINTING THIS TO NORMAL CELL====== " + rowData.getString(keysArray[a]));
                cell.setCellValue(rowData.getString(keysArray[a]));
                i++;
            }  
            index++;
            headerRow = false;
            // rest cells in that row overwritten with empty string
            int lastCellNum = row.getLastCellNum();
            for(int j = i; j <= lastCellNum; j++) {
                Cell cell = row.getCell(j, Row.CREATE_NULL_AS_BLANK);
                cell.setCellValue("");
            }
        }
        //overwrite rest row data in sheet
        while(rowIterator.hasNext()) {
            row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            while(cellIterator.hasNext()) { 
                Cell cell = cellIterator.next();
                cell.setCellValue("");
            }
        }
        return;
    }
    
    public static void writeHeadersInFormat(Workbook wb, JSONElement headerMarkers1, JSONArray orderedArray) {
    	JSONObject headerMarkers = (JSONObject) headerMarkers1;
        //System.out.println("=CLASS===="+headerMarkers.getClass());
        Object o = headerMarkers.remove("undefined");
        Sheet sheet = wb.getSheet("headerMetadata");
        if(sheet != null) {
            int sindex = wb.getSheetIndex(sheet);
            wb.removeSheetAt(sindex);
        }
        sheet = wb.createSheet("headerMetadata");
        Map <String, Map<String,String>> reverseMarkers = new HashMap<String, Map<String, String>>();
        int rownum = 0;

        for(Object entry1 : headerMarkers.entrySet()) {
        	Entry<String, Map<String, String>> entry = (Entry<String, Map<String, String>>) entry1;
            String headerName = entry.getKey();
            headerName = headerName.trim().toLowerCase();
            Map<String,String> headerValues = new HashMap<String, String>();
            for(Map.Entry<String, String> en : entry.getValue().entrySet()) {
                //System.out.println("=======HERE======" + en.getKey());
                if(en.getKey() != "undefined"){
                	String val = (en.getValue() instanceof String ? en.getValue() : null);
                    headerValues.put(en.getKey(), val);
                }
            }
            //System.out.println("==========NEW FUNC==============");
            //System.out.println(headerName);
            //System.out.println("---------" + entry.getValue() + entry.getValue().getClass());
            //System.out.println("-------==============" + headerValues + headerValues.getClass());
            String dataColumns = "";
            if(headerValues.get("dataColumns") != null){
                dataColumns = headerValues.get("dataColumns");
                dataColumns = dataColumns.trim();
            }
            String group = "";
            if(headerValues.get("group") != null){
                //System.out.println(headerValues.get("group"));
                group = headerValues.get("group");
                group = group.trim();
            }

            //System.out.println("====ERROR FINIDING==" + headerValues.get("group"));
            String includeHeadings = "";
            if(headerValues.get("header") != null){
                includeHeadings = headerValues.get("header");
                includeHeadings = includeHeadings.trim();
            }
            
            String append = "";
            if(headerValues.get("append") != null){
                append = headerValues.get("append");
                append = append.trim();
            }

            String delimiter = "";
            if(headerValues.get("delimiter") != null){
                delimiter = headerValues.get("delimiter");
                delimiter = delimiter.trim();
            }
            
            String images = "";
            if(headerValues.get("images") != null){
                images = headerValues.get("images");
                images = images.trim();
            }

            String contributor = "";
            if(headerValues.get("contributor") != null){
                contributor = headerValues.get("contributor");
                contributor = contributor.trim();
            }

            String attributions = "";
            if(headerValues.get("attributions") != null){
                attributions = headerValues.get("attributions");
                attributions = attributions.trim();
            }

            String references = "";
            if(headerValues.get("references") != null){
                references = headerValues.get("references");
                references = references.trim();
            }
            
            String license = "";
            if(headerValues.get("license") != null){
                license = headerValues.get("license");
                license = license.trim();
            }
            
            String audience = "";
            if(headerValues.get("audience") != null){
                audience = headerValues.get("audience");
                audience = audience.trim();
            }

            String language = "";
            if(headerValues.get("language") != null){
                language = headerValues.get("language");
                language = language.trim();
            }

            //System.out.println("=======" + dataColumns);
            List<String> dcList = Arrays.asList(dataColumns.split(","));
            Iterator<String> dcIterator = dcList.iterator();
            while (dcIterator.hasNext()) {
                String nextVal = dcIterator.next();
                nextVal = nextVal.trim();
                //System.out.println("==THIS IS THE VALUE=== " + nextVal);
                if(!nextVal.equals("") && !nextVal.equals(null)){
                    if(reverseMarkers.containsKey(nextVal)){
                        Map<String, String> m = reverseMarkers.get(nextVal);
                        String fieldNames = m.get("fieldNames");
                        if(fieldNames != "") {
                            fieldNames += FIELD_SEP + headerName;
                            m.put("fieldNames", fieldNames);
                        }
                        else {
                            m.put("fieldNames", headerName);
                        }
                        String contentDelimiter = m.get("contentDelimiter");
                        if(contentDelimiter != "") {
                            contentDelimiter += COLUMN_SEP + headerName + KEYVALUE_SEP + delimiter;
                            m.put("contentDelimiter", contentDelimiter);
                        }
                        else {
                            m.put("contentDelimiter", headerName + KEYVALUE_SEP + delimiter);
                        }
                        String contentFormat = m.get("contentFormat");
                        if(contentFormat != "") {
                            contentFormat += COLUMN_SEP + headerName + KEYVALUE_SEP + "Group=" + group +";" + "includeheadings=" + includeHeadings +";" + "append=" + append + ";";
                            m.put("contentFormat", contentFormat);
                        }
                        else {
                            m.put("contentFormat",  headerName + KEYVALUE_SEP + "Group=" + group +";" + "includeheadings=" + includeHeadings +";" + "append=" + append + ";");
                        }
                        String imagesCol = m.get("images");
                        if(imagesCol != "") {
                            imagesCol += COLUMN_SEP + headerName + KEYVALUE_SEP  + images;
                            m.put("images", imagesCol);
                        }
                        else {
                            m.put("images",  headerName + KEYVALUE_SEP + images);
                        }
                        String contributorCol = m.get("contributor");
                        if(contributorCol != "") {
                            contributorCol += COLUMN_SEP + headerName + KEYVALUE_SEP  + contributor;
                            m.put("contributor", contributorCol);
                        }
                        else {
                            m.put("contributor",  headerName + KEYVALUE_SEP + contributor);
                        }
                        String attributionsCol = m.get("attributions");
                        if(attributionsCol != "") {
                            attributionsCol += COLUMN_SEP + headerName + KEYVALUE_SEP  + attributions;
                            m.put("attributions", attributionsCol);
                        }
                        else {
                            m.put("attributions",  headerName + KEYVALUE_SEP + attributions);
                        }
                        String referencesCol = m.get("references");
                        if(referencesCol != "") {
                            referencesCol += COLUMN_SEP + headerName + KEYVALUE_SEP  + references;
                            m.put("references", referencesCol);
                        }
                        else {
                            m.put("references",  headerName + KEYVALUE_SEP + references);
                        }

                        String licenseCol = m.get("license");
                        if(licenseCol != "") {
                            licenseCol += COLUMN_SEP + headerName + KEYVALUE_SEP  + license;
                            m.put("license", licenseCol);
                        }
                        else {
                            m.put("license",  headerName + KEYVALUE_SEP + license);
                        }
                        String audienceCol = m.get("audience");
                        if(audienceCol != "") {
                            audienceCol += COLUMN_SEP + headerName + KEYVALUE_SEP  + audience;
                            m.put("audience", audienceCol);
                        }
                        else {
                            m.put("audience",  headerName + KEYVALUE_SEP + audience);
                        }
            
                        String languageCol = m.get("language");
                        if(languageCol != "") {
                            languageCol = language;
                            m.put("language", languageCol);
                        }
                        else {
                            m.put("language",  language);
                        }


                    }else {
                        Map<String, String> m1 = new HashMap();
                        m1.put("fieldNames", headerName);
                        m1.put("contentDelimiter", headerName + KEYVALUE_SEP + delimiter);
                        m1.put("contentFormat",  headerName + KEYVALUE_SEP + "Group=" + group +";" + "includeheadings=" + includeHeadings +";" + "append=" + append + ";");
                        m1.put("images",  headerName + KEYVALUE_SEP + images);
                        m1.put("contributor",  headerName + KEYVALUE_SEP + contributor);
                        m1.put("attributions",  headerName + KEYVALUE_SEP + attributions);
                        m1.put("references",  headerName + KEYVALUE_SEP + references);
                        m1.put("license",  headerName + KEYVALUE_SEP + license);
                        m1.put("audience",  headerName + KEYVALUE_SEP + audience);
                        m1.put("language",  language);


                        reverseMarkers.put(nextVal, m1);
                    }
                }
            }

        }

        Row row = sheet.createRow(rownum++);
        
        String[] headerRowValues = {"CONCEPT", "CATEGORY", "SUBCATEGORY", "FIELD NAME(S)", "CONTENT DELIMITER", "CONTENT FORMAT", "IMAGES", "CONTRIBUTOR", "ATTRIBUTIONS", "REFERENCES", "LICENSE","AUDIENCE", "LANGUAGE"};
        int numOfColumns = headerRowValues.length;
        for (int cellNum = 0; cellNum < numOfColumns; cellNum++ ){
            Cell cell = row.createCell(cellNum);
            cell.setCellValue(headerRowValues[cellNum]);
        }
        //System.out.println("====REVERSE MARKERS=====" + reverseMarkers);
        for(Map.Entry<String , Map<String,String> > entry : reverseMarkers.entrySet()) {
            String[] arr = new String[numOfColumns];
            String headerName = entry.getKey();
            List<String> pipedNameList = Arrays.asList(headerName.split("\\|"));
            Iterator<String> pnlIterator = pipedNameList.iterator();
            for(int i = 0; i < 3; i++){
                if(pnlIterator.hasNext()){
                    arr[i] = pnlIterator.next();
                }
                else{
                    arr[i] = "";
                }
            }
            Map<String,String> m2 = new HashMap(entry.getValue());
            arr[3] = m2.get("fieldNames");
            arr[4] = m2.get("contentDelimiter");
            arr[5] = m2.get("contentFormat");
            arr[6] = m2.get("images");
            arr[7] = m2.get("contributor");
            arr[8] = m2.get("attributions");
            arr[9] = m2.get("references");
            arr[10] = m2.get("license");
            arr[11] = m2.get("audience");
            arr[12] = m2.get("language");
            

            //Rewriting fieldnames according to columns in sheet 1

            String[] tokens = arr[3].split(FIELD_SEP);
            String finalKeysOrdered = "";
            int orderedArraySize = orderedArray.length();
            for (int k = 0; k< orderedArraySize; k++){
                String kkk = orderedArray.getString(k);
                for (String token : tokens)
                {
                    if(token.equals(kkk)) {
                        if(finalKeysOrdered == "") {
                            finalKeysOrdered = token;
                        } else {
                            finalKeysOrdered = finalKeysOrdered + FIELD_SEP + token;
                        }
                    }
                }
            }
            arr[3] = finalKeysOrdered;
            /////////

            row = sheet.createRow(rownum++);
            for (int cellNum = 0; cellNum < numOfColumns; cellNum++ ){
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(arr[cellNum]);
            }

        }

    }

    public static void writeHeaderMarkersInSheet(Workbook wb, Map<String, Map<String,String> > headerMarkers) {
        Sheet sheet = wb.getSheet("headerMetadata");
        if(sheet != null) {
            int sindex = wb.getSheetIndex(sheet);
            wb.removeSheetAt(sindex);
        }
        sheet = wb.createSheet("headerMetadata");
        int rownum = 0;
        String headerName;
        //int headerMarkersSize = 0;
        /*
           for(int i = 0; i<headerMarkersSize; i++){
           JSONObject marker = headerMarkers.get(i);

           }*/

        Row row = sheet.createRow(rownum++);
        for (int cellNum = 0; cellNum <= 2; cellNum++ ){
            Cell cell = row.createCell(cellNum);
            if(cellNum == 0) {
                //System.out.println("===HEADER NAME IN MARKER SHEET==== " );

                cell.setCellValue("column_name");
            }
            else if (cellNum == 1) {
                //System.out.println("===TAGS IN MARKER SHEET==== " );
                cell.setCellValue("type");
            }
            else if (cellNum == 2) {
                //System.out.println("===VALUES IN MARKER SHEET==== " );
                cell.setCellValue("value");
            }
        }
        for(Map.Entry<String , Map<String,String> > entry : headerMarkers.entrySet()) {
            headerName = entry.getKey();
            //System.out.println("===HEADER NAME IN MARKER SHEET==== " + headerName );
            for(Map.Entry<String, String> en : entry.getValue().entrySet()) {
                row = sheet.createRow(rownum++);
                for (int cellNum = 0; cellNum <= 2; cellNum++ ){
                    Cell cell = row.createCell(cellNum);
                    if(cellNum == 0) {
                        //System.out.println("===HEADER NAME IN MARKER SHEET==== " + headerName );

                        cell.setCellValue(headerName);
                    }
                    else if (cellNum == 1) {
                        //System.out.println("===TAGS IN MARKER SHEET==== " + en.getKey());
                        cell.setCellValue(en.getKey());
                    }
                    else if (cellNum == 2) {
                        //System.out.println("===VALUES IN MARKER SHEET==== " + en.getValue());
                        cell.setCellValue(en.getValue());
                    }
                }

            }
        }

        return;
    }

        
}
