package usda.weru.weps.reports.query;

import de.schlichtherle.truezip.file.TFile;
import de.schlichtherle.truezip.file.TFileReader;
import java.awt.Color;
import java.awt.image.BufferedImage;
import java.awt.image.RenderedImage;
import java.io.BufferedReader;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import javax.imageio.ImageIO;
import org.apache.log4j.Logger;
import usda.weru.util.ConfigData;
import usda.weru.util.ConversionCalculator;
import usda.weru.util.Util;
import usda.weru.weps.DrawField;
import usda.weru.weps.RunFileData;
import usda.weru.weps.location.Site;
import usda.weru.weps.location.Station;
import usda.weru.weps.reports.RepBarPanel;

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

    private static final Logger LOGGER = Logger.getLogger(RunsResultSet.class);

    /**
     *
     */
    public static final String NAME = "runs";

    /**
     *
     */
    public static final String COLUMN_RUNID = "runid";

    /**
     *
     */
    public static final String COLUMN_NAME = "name";

    /**
     *
     */
    public static final String COLUMN_NAMEEXT = "nameext";

    /**
     *
     */
    public static final String COLUMN_LOCATION = "location";

    /**
     *
     */
    public static final String COLUMN_CLIENT = "client";

    /**
     *
     */
    public static final String COLUMN_FARM = "farm";

    /**
     *
     */
    public static final String COLUMN_TRACT = "tract";

    /**
     *
     */
    public static final String COLUMN_FIELD = "field";

    /**
     *
     */
    public static final String COLUMN_MANAGE = "manage";

    /**
     *
     */
    public static final String COLUMN_SOIL = "soil";

    /**
     *
     */
    public static final String COLUMN_FIELDIMAGE = "fieldimage";

    /**
     *
     */
    public static final String COLUMN_RUNDATE = "rundate";

    /**
     *
     */
    public static final String COLUMN_MODE = "mode";

    /**
     *
     */
    public static final String COLUMN_CYCLESCOUNT = "cyclescount";

    /**
     *
     */
    public static final String COLUMN_STATE = "state";

    /**
     *
     */
    public static final String COLUMN_COUNTY = "county";

    /**
     *
     */
    public static final String COLUMN_SITE = "site";

    /**
     *
     */
    public static final String COLUMN_LATITUDE = "latitude";

    /**
     *
     */
    public static final String COLUMN_LONGITUDE = "longitude";

    /**
     *
     */
    public static final String COLUMN_ELEVATION = "elevation";

    /**
     *
     */
    public static final String COLUMN_FIELDSHAPE = "fieldshape";

    /**
     *
     */
    public static final String COLUMN_FIELDXLENGTH = "fieldxlength";

    /**
     *
     */
    public static final String COLUMN_FIELDYLENGTH = "fieldylength";

    /**
     *
     */
    public static final String COLUMN_FIELDRADIUS = "fieldradius";

    /**
     *
     */
    public static final String COLUMN_FIELDORIENTATION = "fieldorientation";

    /**
     *
     */
    public static final String COLUMN_CLIGEN = "cligen";

    /**
     *
     */
    public static final String COLUMN_WINDGEN = "windgen";

    private final WepsConnection c_con;
    private boolean c_filled;

    /**
     *
     * @param con
     * @throws SQLException
     */
    public RunsResultSet(WepsConnection con) throws SQLException {
        c_con = con;
        addColumn(COLUMN_RUNID, Types.INTEGER, 10, 0);
        addColumn(COLUMN_NAME, Types.VARCHAR, 50, 0);
        addColumn(COLUMN_NAMEEXT, Types.VARCHAR, 50, 0);
        addColumn(COLUMN_LOCATION, Types.VARCHAR, 255, 0);
        addColumn(COLUMN_CLIENT, Types.VARCHAR, 100, 0);
        addColumn(COLUMN_FARM, Types.VARCHAR, 100, 0);
        addColumn(COLUMN_TRACT, Types.VARCHAR, 50, 0);
        addColumn(COLUMN_FIELD, Types.VARCHAR, 50, 0);
        addColumn(COLUMN_MANAGE, Types.VARCHAR, 255, 0);
        addColumn(COLUMN_SOIL, Types.VARCHAR, 255, 0);
        addColumn(COLUMN_CLIGEN, Types.VARCHAR, 255, 0);
        addColumn(COLUMN_WINDGEN, Types.VARCHAR, 255, 0);
        addColumn(COLUMN_FIELDIMAGE, Types.BINARY, 0, 0);
        addColumn(COLUMN_RUNDATE, Types.TIMESTAMP, 0, 0);
        addColumn(COLUMN_MODE, Types.VARCHAR, 25, 0);
        addColumn(COLUMN_CYCLESCOUNT, Types.INTEGER, 10, 0);
        addColumn(COLUMN_STATE, Types.VARCHAR, 50, 0);
        addColumn(COLUMN_COUNTY, Types.VARCHAR, 50, 0);
        addColumn(COLUMN_SITE, Types.VARCHAR, 50, 0);
        addColumn(COLUMN_LATITUDE, Types.DOUBLE, 10, 3);
        addColumn(COLUMN_LONGITUDE, Types.DOUBLE, 10, 3);
        addColumn(COLUMN_ELEVATION, Types.DOUBLE, 10, 3);
        addColumn(COLUMN_FIELDSHAPE, Types.VARCHAR, 50, 0);
        addColumn(COLUMN_FIELDXLENGTH, Types.DOUBLE, 10, 3);
        addColumn(COLUMN_FIELDYLENGTH, Types.DOUBLE, 10, 3);
        addColumn(COLUMN_FIELDRADIUS, Types.DOUBLE, 10, 3);
        addColumn(COLUMN_FIELDORIENTATION, Types.DOUBLE, 10, 3);

    }

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

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

    /**
     *
     * @throws SQLException
     */
    @Override
    public synchronized void fill() throws SQLException {
        if (c_filled) {
            return;
        }
        TFile[] files = c_con.getRunFiles();
        RunFileData[] RFD = c_con.getRFD();

        DrawField fieldPainter = null;
        RepBarPanel rbp = null;

        for (int i = 0; i < files.length; i++) {
            TFile runDir = files[i];
            Object[] row = createNewRow(true);
            setRowValue(row, COLUMN_RUNID, i);

            //name with extension
            String name = runDir.getName();
            setRowValue(row, COLUMN_NAMEEXT, name);
            //name with out extension
            name = name.substring(0, name.length() - RunFileData.RunSuffix.length());
            setRowValue(row, COLUMN_NAME, name);

            RunFileData data = RFD[i];
            setRowValue(row, COLUMN_CLIENT, data.getData(RunFileData.UserName));

            setRowValue(row, COLUMN_FARM, data.getData(RunFileData.FarmId));
            setRowValue(row, COLUMN_TRACT, data.getData(RunFileData.TractId));
            setRowValue(row, COLUMN_FIELD, data.getData(RunFileData.FieldId));

            setRowValue(row, COLUMN_MANAGE, data.getData(RunFileData.ManageFile));
            setRowValue(row, COLUMN_SOIL, data.getData(RunFileData.SoilFile));

            Station cligen = data.getBean().getCligenStation();
            setRowValue(row, COLUMN_CLIGEN, cligen != null ? cligen.getDisplayName() : null);

            Station windgen = data.getBean().getWindgenStation();
            setRowValue(row, COLUMN_WINDGEN, windgen != null ? windgen.getDisplayName() : null);

            //run mode
            setRowValue(row, COLUMN_MODE, data.getData(RunFileData.RunTypeDisp));

            try {
                Integer cyclescount = Integer.valueOf(data.getData(RunFileData.CycleCount));
                setRowValue(row, COLUMN_CYCLESCOUNT, cyclescount);

            } catch (NumberFormatException nfe) {
                LOGGER.error("Error parsing cylescount value.");
            }

            //site
            try {
                String buffer = null;

                for (Site<?> site = data.getBean().getSite(); site != null; site = site.getParent()) {

                    if (buffer == null) {
                        buffer = site.getDisplayName();

                    } else {
                        buffer = site.getDisplayName() + ", " + buffer;
                    }
                }

                setRowValue(row, COLUMN_SITE, buffer != null ? buffer : "");
            } catch (SQLException e) {
                LOGGER.error("Error parsing site in file: " + runDir.getAbsolutePath());
            }

            DecimalFormat twoDecimalFormat = new DecimalFormat("#.#####");
            //lat/lon
            String latlong = data.getData(RunFileData.StrLatLong);
            String[] latlongParts = latlong.split(";", 2);
            try {
                Double lat = Double.valueOf(twoDecimalFormat.format(Double.valueOf(latlongParts[0].trim())));

                setRowValue(row, COLUMN_LATITUDE, lat);
            } catch (NumberFormatException nfe) {
                LOGGER.warn("Unable able to parse latitude for file: " + runDir.getAbsolutePath());
            }

            try {
                Double lon = Double.valueOf(twoDecimalFormat.format(Double.valueOf(latlongParts[1].trim())));
                setRowValue(row, COLUMN_LONGITUDE, lon);
            } catch (NumberFormatException nfe) {
                LOGGER.warn("Unable able to parse latitude for file: " + runDir.getAbsolutePath());
            }

            setRowValue(row, COLUMN_FIELDSHAPE, data.getData(RunFileData.Shape));
            //radius
            try {
                Double radius = Double.valueOf(data.getData(RunFileData.Radius));
                if (isUSUnits()) {
                    radius = ConversionCalculator.convert(radius, "m", "ft");
                }
                setRowValue(row, COLUMN_FIELDRADIUS, radius);

            } catch (NumberFormatException nfe) {
                LOGGER.error("Error parsing radius.", nfe);
            } catch (ConversionCalculator.ConversionNotFoundException | ConversionCalculator.UnitNotFoundException cnfe) {
                LOGGER.error("Error converting radius value.", cnfe);
            }

            //x length
            try {
                Double xLength = Double.valueOf(data.getData(RunFileData.XLength));
                if (isUSUnits()) {
                    xLength = ConversionCalculator.convert(xLength, "m", "ft");
                }
                setRowValue(row, COLUMN_FIELDXLENGTH, xLength);

            } catch (NumberFormatException nfe) {
                LOGGER.error("Error parsing xlength.", nfe);
            } catch (ConversionCalculator.ConversionNotFoundException | ConversionCalculator.UnitNotFoundException cnfe) {
                LOGGER.error("Error converting xlength value.", cnfe);
            }

            //y length
            try {
                Double yLength = Double.valueOf(data.getData(RunFileData.YLength));
                if (isUSUnits()) {
                    yLength = ConversionCalculator.convert(yLength, "m", "ft");
                }
                setRowValue(row, COLUMN_FIELDYLENGTH, yLength);

            } catch (NumberFormatException nfe) {
                LOGGER.error("Error parsing ylength.", nfe);
            } catch (ConversionCalculator.ConversionNotFoundException | ConversionCalculator.UnitNotFoundException cnfe) {
                LOGGER.error("Error converting ylength value.", cnfe);
            }

            //elevation
            try {
                Double elevation = Double.valueOf(data.getData(RunFileData.Elevation));
                if (isUSUnits()) {
                    elevation = ConversionCalculator.convert(elevation, "m", "ft");
                }
                setRowValue(row, COLUMN_ELEVATION, elevation);

            } catch (NumberFormatException nfe) {
                LOGGER.error("Error parsing elevation.", nfe);
            } catch (ConversionCalculator.ConversionNotFoundException | ConversionCalculator.UnitNotFoundException cnfe) {
                LOGGER.error("Error converting elevation value.", cnfe);
            }

            //orientation
            try {
                Double orientation = Double.valueOf(data.getData(RunFileData.RegionAngle));
                setRowValue(row, COLUMN_FIELDORIENTATION, orientation);

            } catch (NumberFormatException nfe) {
                LOGGER.error("Error parsing orientation.", nfe);
            }

            //add special fields        
            //location of run
            String location = getRunLocation(ConfigData.getDefault().getData(ConfigData.DefaultRunsLocation), runDir);
            setRowValue(row, COLUMN_LOCATION, location);

            //field image
            if (fieldPainter == null) {
                fieldPainter = new DrawField();
                fieldPainter.setSize(200, 200);
                fieldPainter.setBackground(Color.WHITE);

            }
            rbp = new RepBarPanel();
            rbp.addPropertyChangeListener(fieldPainter);
            fieldPainter.reset();
            data.fireAll(rbp, fieldPainter);

            BufferedImage image = new BufferedImage(200, 200, BufferedImage.TYPE_INT_RGB);
            fieldPainter.paintComponent(image.getGraphics());
            byte[] bytes = getBytes(image);
            setRowValue(row, COLUMN_FIELDIMAGE, bytes);
            //end image

            //date of weps run, have to read the stdout.txt
            TFile stdout = new TFile(runDir, RunFileData.StdOut);
            if (stdout.exists()) {
                BufferedReader reader = null;
                try {
                    reader = new BufferedReader(new TFileReader(stdout));
                    String line = getLine(reader);  //First line
                    line = line.trim();
                    while (line != null) {
                        if (line.startsWith("Date of WEPS Run:")) {
                            //found the line
                            String dateText = line;
                            dateText = line.substring(18, line.length());
                            try {
                                //Each thread creates its own because formats are not thread safe.
                                DateFormat format = new SimpleDateFormat("MMM dd, yyyy HH:mm:ss");
                                Date date = format.parse(dateText);
                                setRowValue(row, COLUMN_RUNDATE, new Timestamp(date.getTime()));

                            } catch (NumberFormatException | ParseException nfe) {
                                LOGGER.warn("Unable to parse date line: \"" + line + "\" in file: " + stdout.getAbsolutePath());
                            }

                            break;
                        }
                        line = getLine(reader);  //next line
                    }
                } catch (IOException ioe) {
                    LOGGER.error("Error reading file: " + stdout.getAbsolutePath(), ioe);
                } finally {
                    if (reader != null) {
                        try {
                            reader.close();
                        } catch (IOException e) {
                            LOGGER.error("Error closing file: " + stdout.getAbsolutePath(), e);
                        }
                    }
                }
            }
        }

        c_filled = true;
    }

    /**
     *
     * @param image
     * @return
     */
    public byte[] getBytes(RenderedImage image) {
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        try {
            ImageIO.write(image, "JPEG", baos);
        } catch (IOException ex) {
            throw new RuntimeException(ex);
        }
        return baos.toByteArray();
    }

    private String getRunLocation(String defaultLocation, TFile run) {
        if (defaultLocation == null) {
            return run.getParentFile().getPath();
        }

        TFile defaultLocationFile = new TFile(defaultLocation);

        TFile parent = new TFile(run.getParentFile());

        //Is the direct parent a project file?
        if (parent.getName().endsWith(RunFileData.ProjectSuffix)) {
            return parent.getName().replace(RunFileData.ProjectSuffix, "");
        }

        while (parent != null) {
            if (parent.equals(defaultLocationFile)) {
                String pathToIgnore = defaultLocationFile.getParentFile().getAbsolutePath();
                String fullPath = run.getParentFile().getAbsolutePath();
                fullPath = fullPath.replace(pathToIgnore, "");
                if (fullPath.startsWith(TFile.separator)) {
                    fullPath = fullPath.substring(1);
                }
                return fullPath;
            } else {
                try {
                    parent = new TFile(parent.getParentFile());
                } catch (NullPointerException ex) {
                    return run.getParentFile().getPath();
                }
            }
        }
        return run.getParentFile().getPath();
    }

    //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;
    }

}