package usda.weru.weps.reports.query;

import de.schlichtherle.truezip.file.TFile;
import de.schlichtherle.truezip.file.TFileReader;
import java.io.BufferedReader;
import java.io.IOException;
import java.sql.SQLException;
import java.io.FileNotFoundException;
import java.sql.Types;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger; 
import usda.weru.util.ConfigData;
import usda.weru.util.ConversionCalculator;
import usda.weru.util.ConversionCalculator.ConversionNotFoundException;
import usda.weru.util.ConversionCalculator.UnitNotFoundException;
import usda.weru.util.Util;
import static usda.weru.weps.wepsDate.WepsDateUtils.getSqlDateWeps;

/**
 * 
 * @author joelevin
 */
public class StirEnergyResultSet extends WepsResultSet {

    private static final Logger LOGGER = LogManager.getLogger(StirEnergyResultSet.class);
    public static final String NAME = "stir_energy";
    public static final String OUTPUT_FILE = "stir_energy.out";
    public static final String COLUMN_LINE = "line";
    public static final String COLUMN_STIR = "stir";
    public static final String COLUMN_ENERGY = "energy";
    public static final String COLUMN_DATE = "date";
    public static final String COLUMN_ROTATION = "rotation";
    public static final String COLUMN_OPERATION = "operation";
    public static final String COLUMN_CROP = "crop";
    public static final String COLUMN_FUEL = "fuel";
    public static final String COLUMN_FUELDEFAULT = "fueldefault";
    public static final String COLUMN_CROPNUMBER = "cropnumber";
    public static final String COLUMN_LASTHARVEST = "lastharvest";
    public static final String COLUMN_VOLUMENAME = "volumename";

    //These are for specifying the actual date of the last harvest as well as the duration of the NRCS Crop Interval
    //The official NRCS Crop Interval always goes from "last Harvest" to "last Harvest",
    //even if the crop continues to grow past the "Last Harvest".
    //It also means any crop that is not harvested, e.g. most cover crops by definition are considered part of
    //a "harvested" crop's NRCS Crop Interval and do not have an NRCS Crop Interval for the cover crops themselves.
//    public static final String COLUMN_LASTHARVESTDATE = "lastharvestdate";
    public static final String COLUMN_NRCS_CI_START = "nrcs_ci_start";
    public static final String COLUMN_NRCS_CI_END = "nrcs_ci_end";
    public static final String COLUMN_NRCS_CI_DAYS = "nrcs_ci_days";
    public static final String COLUMN_NRCSCROPINTERVALDURATION = "NRCScropintervalduration";
    
    //These are used for coverstandingstirRunSummary (sub-report of the stir energy)
    public static final String COLUMN_CROPPLANTED = "planted";
    public static final String COLUMN_RANDOMROUGHNESS = "rr";
    public static final String COLUMN_SURFACECOVER = "surfacecover";
    public static final String COLUMN_EFFSTANDINGSIL = "silhouette";
    
    private final WepsConnection c_con;
    private boolean c_filled;
    
    private final DateFormat stirDateFormat = new SimpleDateFormat("dd/MM/yyyy");
    
//    public class NrcsCropIntv {
//        public java.sql.Date start;
//        public java.sql.Date end;
//        public NrcsCropIntv () {
//            this (null, null);
//        }
//        public NrcsCropIntv (java.sql.Date start, java.sql.Date end) {
//            this.start = start;
//            this.end = end;
//        }
//    }
    

    public StirEnergyResultSet(WepsConnection con) throws SQLException {
        c_con = con;
        addColumn(RunsResultSet.COLUMN_RUNID, Types.INTEGER, 10, 0);

        //stir file specific 
        addColumn(COLUMN_LINE, Types.INTEGER, 10, 0);

        //Columns expected in the file
        addColumn(COLUMN_DATE, Types.DATE, 0, 0);
        addColumn(COLUMN_ROTATION, Types.INTEGER, 10, 0);
        addColumn(COLUMN_OPERATION, Types.VARCHAR, 50, 0);
        addColumn(COLUMN_CROP, Types.VARCHAR, 50, 0);
        addColumn(COLUMN_CROPPLANTED, Types.VARCHAR, 50, 0);
        addColumn(COLUMN_FUEL, Types.VARCHAR, 50, 0);
        addColumn(COLUMN_STIR, Types.DOUBLE, 10, 5);
        addColumn(COLUMN_ENERGY, Types.DOUBLE, 10, 5);
        addColumn(COLUMN_RANDOMROUGHNESS, Types.DOUBLE, 10, 5);
        addColumn(COLUMN_SURFACECOVER, Types.DOUBLE, 10, 5);
        addColumn(COLUMN_EFFSTANDINGSIL, Types.DOUBLE, 10, 5);
        addColumn(COLUMN_CROPNUMBER, Types.INTEGER, 10, 0);
        addColumn(COLUMN_LASTHARVEST, Types.BOOLEAN, 0, 0);
	// Additional (data) columns from tabular detail report
        addColumn(COLUMN_FUELDEFAULT, Types.BOOLEAN, 0, 0);
        addColumn(COLUMN_VOLUMENAME, Types.VARCHAR, 50, 0);
	// Additional and/or computed (data) columns
        addColumn(COLUMN_NRCS_CI_START, Types.DATE, 0, 0);
        addColumn(COLUMN_NRCS_CI_END, Types.DATE, 0, 0);
        addColumn(COLUMN_NRCS_CI_DAYS, Types.INTEGER, 0, 0);
        addColumn(COLUMN_NRCSCROPINTERVALDURATION, Types.INTEGER, 10, 0);
    }

    @Override
    public String getName() {
        return NAME;
    }

    protected boolean isUSUnits() {
        return Util.USUnits.equals(c_con.getUnits());
    }

    @Override
    public synchronized void fill() throws SQLException {

        if (c_filled) {
            return;
        }
        TFile[] files = c_con.getRunFiles();
        ArrayList<String> sub_report_data = getDetailReportData();
        
        for (int i = 0; i < files.length; i++) {
            TFile runDir = files[i];
            TFile output = new TFile(runDir, OUTPUT_FILE);
            if (!output.exists()) {
                //no stir_energy file, might be an old run
                continue;
            }

            // A little brute force to simply read through the file twice...
            // It is a small file, and doing this allows the rest
            // of the fill logic to match all other result sets.
            // MEH
            
//            ArrayList<NrcsCropIntv> nrcsCropIntervals2 = new ArrayList<NrcsCropIntv>();
//            nrcsCropIntervals2.add(new NrcsCropIntv());
//            int cropIntervalIdx = 0;
//            
//            //Stir energy file specific
//            BufferedReader reader = null;
//            try {
////                Date nrcsCiStartSave = getSqlDateWepsInitial();
//                Date nrcsCiStartSave = parseDateIntervalBaseDateSql;
//                
//                String line;
//                reader = new BufferedReader(new TFileReader(output));
// 
//                //first line, flexible header
//                String header = reader.readLine();
//                header = header.replaceFirst("#", "");
//                String[] columns = header.split("\\|", -1);     //-1 so we grab blank columns, just in case
//
//                // skip
//                String rotationLine = getLine(reader);
//                
//                int lineIndex = -1;
//                
//                while ((line = getLine(reader)) != null) {
//                    lineIndex++;
//                    // Use -1 as the limit to allow String.split to return all 5 values regardless of whether they are
//                    // blank or not.
//                    String[] values = line.split("\\|", -1);
//                    
//                    try {
//                        String dateText = values[column(columns, "dd/mm/yyyy")].trim();
//                        Date date = getSqlDateWeps(dateText);
//
//                        String lastharvestFlagStr = values[column(columns, "1 if last")].trim();
//                        boolean lastHarvestFlag = lastharvestFlagStr.startsWith("1");
//
//                        if (lastHarvestFlag) {
//                            String lastharvestdateStr = values[column(columns, "dd/mm/yyyy")].trim();                            
//                            nrcsCiStartSave = getSqlDateWeps(lastharvestdateStr);
//                            
//                            NrcsCropIntv ci = nrcsCropIntervals2.get(cropIntervalIdx);
//                            ci.end = nrcsCiStartSave;
//                            nrcsCropIntervals2.set(cropIntervalIdx, ci);
//                            nrcsCropIntervals2.add(cropIntervalIdx+1, new NrcsCropIntv(nrcsCiStartSave, null));
//                                                       
//                            cropIntervalIdx++;
//                        }
//                        
//                    } catch (Exception ex) {
//                    }
//                }
//                NrcsCropIntv tArr2 = nrcsCropIntervals2.get(0);
//                tArr2.start = nrcsCiStartSave;
//                nrcsCropIntervals2.set(0, tArr2);
//           } catch (IOException ioe) {
//                LOGGER.error("Error reading file: " + output.getAbsolutePath(), ioe);
//                throw new SQLException("Error reading file: " + output.getAbsolutePath(), ioe);
//            } finally {
//                if (reader != null) {
//                    try {
//                        reader.close();
//                    } catch (IOException e) {
//                        LOGGER.error("Error closing file: " + output.getAbsolutePath(), e);
//                    }
//                }
//            }

            BufferedReader reader = null;
            try {
                String line;
                
                reader = new BufferedReader(new TFileReader(output));

                //first line, flexible header
                String header = reader.readLine();
                header = header.replaceFirst("#", "");
                String[] columns = header.split("\\|", -1);     //-1 so we grab blank columns, just in case

                int rotation = 0;
                String rotationLine = getLine(reader); 
                String[] rotationArray = rotationLine.split(" ");
                for(String s : rotationArray) {
                    try {
                        rotation = Integer.parseInt(s);
                        //System.out.println("Number of years in rotation is " + rotation);
                        break;
                    } catch(NumberFormatException e) {
                        e = new NumberFormatException("Not the year.");
                        System.err.println(e.getMessage());
                    }
                }
                
                int lineIndex = -1;
                String dateText = "";
                Integer cropSequence = -1;
                
                 Object[] row;
        
                while ((line = getLine(reader)) != null) {
                    //increment the line index for the next use.
                    lineIndex++;
                    row = createNewRow(true);
                    
                    //Always add the run id so that joins will work
                    setRowValue(row, RunsResultSet.COLUMN_RUNID, i);

                    //Add the lineIndex so things can be sorted if need be specific to this file
                    setRowValue(row, COLUMN_LINE, lineIndex);

                    // Use -1 as the limit to allow String.split to return all 5 values regardless of whether they are
                    // blank or not.
                    String[] values = line.split("\\|", -1);
                    if (values.length != columns.length) {
                        //something changed in the file output format that is unexpected
                        LOGGER.warn("Unexpected number of columns on line " + lineIndex + "; found "
                                + values.length + ", expected " + columns.length + ". File: " + output.getAbsolutePath());
                        //go to the next line
                        continue;
                    }

                    try {
                        dateText = values[column(columns, "dd/mm/yyyy")].trim();
                        setRowValue(row, COLUMN_DATE, getSqlDateWeps(dateText));
                    } catch (NumberFormatException|ParseException nfe) {
                        LOGGER.warn("Unable to parse date in file: " + output.getAbsolutePath() + " on line: " + lineIndex);
                    }
                    
                    //number of years in rotation
                    setRowValue(row, COLUMN_ROTATION, rotation);
                            
                    String operationText = values[column(columns, "operation")].trim();
                    setRowValue(row, COLUMN_OPERATION, operationText);
                    String cropText = values[column(columns, "crop name")].trim();
                    setRowValue(row, COLUMN_CROP, cropText);
                    
                    //obtain only planted crop names for planted crops
                    if((operationText.contains("Plant") || operationText.contains("Drill")) && !cropText.isEmpty()) {
                        setRowValue(row, COLUMN_CROPPLANTED, cropText);
                    }
                    
                    //only used in coverstandingstirRunSummary.jrxml file
                    if(lineIndex < sub_report_data.size()) {
                        try {
                            String[] data_line = sub_report_data.get(lineIndex).split(",");
                            String rrText = data_line[0];
                            Double rr = Double.valueOf(rrText);
                            
                            if(isUSUnits())
                                rr = ConversionCalculator.convert(rr, "mm", "in");
                            

                            String stdCoverText = data_line[1];
                            Double standingCover = Double.valueOf(stdCoverText);

                            String effStdSilText = data_line[2];
                            Double silhouette = Double.valueOf(effStdSilText);

                            setRowValue(row, COLUMN_RANDOMROUGHNESS, rr);
                            setRowValue(row, COLUMN_SURFACECOVER, standingCover);
                            setRowValue(row, COLUMN_EFFSTANDINGSIL, silhouette);
                        } catch(ConversionNotFoundException | UnitNotFoundException ce) {
                            LOGGER.warn("ConversionNotFoundException: Unable to convert random roughness/standing cover/standing sil/ value from gui1_data.out file");
                        } catch(NumberFormatException nfe) {
                            LOGGER.warn("NumberFormatException: Unable to parse random roughness/standing cover/standing sil/ value from gui1_data.out file");
                            /* setRowValue(row, COLUMN_RANDOMROUGHNESS, "-"); */
                        }
                    }
                    
                    if (column(columns, "fuel") >= 0) {
                        String fuelText = values[column(columns, "fuel")].trim();

                        //pull in default fuel if required
                        if (fuelText == null || fuelText.trim().length() == 0) {
                            fuelText = ConfigData.getDefault().getData(ConfigData.FuelDefault);
                            setRowValue(row, COLUMN_FUELDEFAULT, true);
                        } else {
                            setRowValue(row, COLUMN_FUELDEFAULT, false);
                        }

                        setRowValue(row, COLUMN_FUEL, fuelText);
                    } else {
                        String fuelText = ConfigData.getDefault().getData(ConfigData.FuelDefault);
                        setRowValue(row, COLUMN_FUEL, fuelText);
                        setRowValue(row, COLUMN_FUELDEFAULT, true);
                    }

                    try {
                        String stirText = values[column(columns, "stir")].trim();
                        Double stir = Double.valueOf(stirText);
                        setRowValue(row, COLUMN_STIR, stir);
                    } catch (NumberFormatException nfe) {
                        LOGGER.warn("Unable to parse stir value in file: " + output.getAbsolutePath()
                                + " on line: " + lineIndex);
                    }

                    try {
                        String energyText = values[column(columns, "energy")].trim();
                        Double energy = Double.valueOf(energyText);
                        if (isUSUnits()) {
                            energy = ConversionCalculator.convert(energy, "L/ha", "gal/ac");
                        }
                        setRowValue(row, COLUMN_ENERGY, energy);
                    } catch (UnitNotFoundException | ConversionNotFoundException unf) {
                        LOGGER.warn("Unable to convert energy value in file: " + output.getAbsolutePath()
                                + " on line: " + lineIndex);
                    } catch (NumberFormatException nfe) {
                        LOGGER.warn("Unable to parse energy value in file: " + output.getAbsolutePath()
                                + " on line: " + lineIndex);
                    }

                    try {
                        String cropSequenceText = values[column(columns, "crop sequence")].trim();
                        cropSequence = Integer.valueOf(cropSequenceText);
                        setRowValue(row, COLUMN_CROPNUMBER, cropSequence - 1);
                    } catch (NumberFormatException nfe) {
                        LOGGER.warn("Unable to parse crop sequence value in file: " + output.getAbsolutePath()
                                + " on line: " + lineIndex);
                    }

                    try {
                        String lastharvestFlagStr = values[column(columns, "1 if last")].trim();
                        boolean lastHarvestFlag = lastharvestFlagStr.startsWith("1");
                        setRowValue(row, COLUMN_LASTHARVEST, lastHarvestFlag);
                        
                        if (lastHarvestFlag) {
//                            NrcsCropIntv ci = nrcsCropIntervals2.get(cropSequence-1);
//                            setRowValue(row, COLUMN_NRCS_CI_START, ci.start);
//                            setRowValue(row, COLUMN_NRCS_CI_END, ci.end);
//                            setRowValue(row, COLUMN_NRCS_CI_DAYS, getIntervalLengthDays(ci.start, ci.end, rotation));
                        }
                    } catch (Exception ex) {
                        LOGGER.warn("Unable to parse last harvest flag in file: " + output.getAbsolutePath()
                                + " on line: " + lineIndex);
                    }
                    
                    String volumeName;
                    if(isUSUnits()) {
                        volumeName = "gal";
                    }
                    else {
                        volumeName = "L";
                    }
                    setRowValue(row, COLUMN_VOLUMENAME, volumeName);
                }
            } catch (IOException ioe) {
                LOGGER.error("Error reading file: " + output.getAbsolutePath(), ioe);
                throw new SQLException("Error reading file: " + output.getAbsolutePath(), ioe);
            } finally {
                if (reader != null) {
                    try {
                        reader.close();
                    } catch (IOException e) {
                        LOGGER.error("Error closing file: " + output.getAbsolutePath(), e);
                    }
                }
            }

        }

        c_filled = true;
    }
    
    /**
     * Retrieves surface cover/ effective standing silhouette/ random roughness
     * from the gui1_data.out file and used by _coverstandingstirRunSummary.jrxml
     * 
     * @return an ArrayList of strings with each value separated by commas
     */
    private ArrayList<String> getDetailReportData() {
        TFile gui1_datafile = new TFile(c_con.getRunFiles()[0], "gui1_data.out");
        ArrayList<String> sub_report_data = new ArrayList<>();
        String line;
        
        try (BufferedReader read = new BufferedReader(new TFileReader(gui1_datafile))) {
            while((line = read.readLine()) != null) {
                String[] line_elements = line.split("\\|", -1);
                String operation = line_elements[2].trim();
                if(!operation.isEmpty()) {
                    String surface_cover = line_elements[62].trim();
                    String standing_sil = line_elements[63].trim();
                    String rr = line_elements[71].trim();                   
                    String data_collected = rr + "," + surface_cover + "," + standing_sil;

                   /* Need to deal with multiple (>1) operations per date - LEW */
                   for (Integer i = 0; i < operation.length(); i++) {
                        if (operation.charAt(i) == '~') { /* separation char for multiple operations listed per date */
                           /* output "-1.0" values to signal JasperReports to substitute "-" */
                           /* for all previous operations listed on this date in the Report section */
                           sub_report_data.add("-1.0,-1.0,-1.0");                           
                        }
                   }
                   /* output the actual values for the last operation listed for each date here */
                   sub_report_data.add(data_collected);
                }
            }
        } catch (FileNotFoundException fe) {
            System.out.println("Couldn't find file for Run Summary - Rotation Cover/Standing Silhouette/STIR. EXCEPTION: " + fe);
        } catch (IOException ex) {
            System.out.println("Couldn't Read file for Run Summary - Rotation Cover/Standing Silhouette/STIR. EXCEPTION: " + ex);
        }
        
        sub_report_data.remove(0);
        
        return sub_report_data;
    }

    private int column(String[] columns, String startsWith) {
        for (int i = 0; i < columns.length; i++) {
            if (columns[i].toLowerCase().trim().startsWith(startsWith.toLowerCase().trim())) {
                return i;
            }
        }

        return -1;
    }

    //Skip comments and blank lines
    private String getLine(BufferedReader in) throws IOException {
        String temp;
        while ((temp = in.readLine()) != null) {
            temp = temp.trim();
            if (temp.length() == 0) {
                //blank line
                continue;
            }
            if (temp.charAt(0) != '#') {
                //not a comment
                return temp;
            }
        }
        return null;
    }

    public static String getDisplayUnits(String columnName, Boolean us) {
        if (COLUMN_ENERGY.equals(columnName)) {
            if (us) {
                return "gal diesel/ac";
            } else {
                return "L diesel/ha";
            }
        }
        return null;
    }
}