package de.intevation.flys.utils;

import java.io.BufferedInputStream;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.Reader;
import java.util.ArrayList;
import java.util.List;

import au.com.bytecode.opencsv.CSVReader;


/**
 * A converter for CSV files with DGM information. The result of a conversion
 * is an SQL file with "INSERT INTO dem ..." statements.
 * <br>
 * To start the converter, at least the following three system properties are
 * required:
 * <br>
 * <ul>
 * <ol><b>gew.dir</b>: This property must point to the directory where all
 * rivers are stored.</ol>
 * <ol><b>csv</b>: This property must point to the CSV file that contains the
 * DGM information.</ol>
 * <ol><b>sql</b>: This property must point to a (not yet existing) file that
 * will be generated by this converter.</ol>
 * </ul>
 * <br>
 * In addiation, the following properties are accepted to modify log messages,
 * etc.
 * <ul>
 * <ol><b>verbose</b>: Accepts integer values (0, 1, 2, 3) to modify the log
 * messages. The higher the value the more log messages are printed to STDOUT.
 * </ol>
 * <ol><b>full</b>: Accepts true|false values. If true is set, all rivers
 * included in the CSV file are taken into account while parsing. Otherwise,
 * the converter reads information for 'Saar', 'Mosel' and 'Eble' only.</ol>
 * </ul>
 *
 * @author Ingo Weinzierl <a href="mailto:ingo.weinzierl@intevation.de">
 * ingo.weinzierl@intevation.de</a>
 *
 */
public class DgmSqlConverter {

    public static final String SQL_INSERT = "INSERT INTO dem (river_id, name, lower, upper, year_from, year_to,"
        + "projection, elevation_state, format, border_break, resolution, description, path) VALUES ("
        + "%s, '%s', %s, %s, %s, %s, '%s', '%s', '%s', %s, '%s', '%s', '%s');";

    public static final String SQL_SELECT_RIVER = "(SELECT id from rivers WHERE name = '%s')";

    public static final char DEFAULT_SEPERATOR = ',';
    public static final char DEFAULT_QUOTE = '"';
    public static final int DEFAULT_LOG_LEVEL = 2;

    public static final boolean FULL_MODE = Boolean.getBoolean("full");
    public static final String GEW_DIR = System.getProperty("gew.dir", null);
    public static final String CSV_FILE = System.getProperty("csv");
    public static final String SQL_FILE = System.getProperty("sql");
    public static final int LOG_LEVEL = Integer.getInteger("verbose",
        DEFAULT_LOG_LEVEL);

    public static final int MIN_COLUMN_COUNT = 15;

    public static final int IDX_RIVERNAME = 0;
    public static final int IDX_NAME = 12;
    public static final int IDX_LOWER = 1;
    public static final int IDX_UPPER = 2;
    public static final int IDX_YEAR_FROM = 3;
    public static final int IDX_YEAR_TO = 4;
    public static final int IDX_PROJECTION = 7;
    public static final int IDX_ELEVATION_STATE = 8;
    public static final int IDX_FORMAT = 9;
    public static final int IDX_BORDER_BREAK = 10;
    public static final int IDX_RESOLUTION = 11;
    public static final int IDX_DESCRIPTION = 14;
    public static final int IDX_FILE_NAME = 5;
    public static final int IDX_FILE_PATH = 6;

    private class DGM {

        public String river;
        public String name;
        public String projection;
        public String elevationState;
        public String format;
        public String resolution;
        public String description;
        public String path;

        public double lower;
        public double upper;
        public Integer yearFrom;
        public Integer yearTo;

        public boolean borderBreak;

        public DGM() {
            borderBreak = false;
        }

        public String toSQL() {
            String riverId = String.format(SQL_SELECT_RIVER, river);
            String lower = String.valueOf(this.lower);
            String upper = String.valueOf(this.upper);
            String yearFrom = this.yearFrom != null ? String
                .valueOf(this.yearFrom) : "";
            String yearTo = this.yearTo != null ? String.valueOf(this.yearTo)
                : "";

            return String.format(SQL_INSERT, riverId, name, lower, upper,
                yearFrom, yearTo, projection, elevationState, format,
                borderBreak, resolution, description, path);
        }
    }

    private File riverDir;
    private File csv;
    private File sql;

    private List<DGM> dgms;

    public static void debug(String msg) {
        if (LOG_LEVEL >= 3) {
            System.out.println("DEBUG: " + msg);
        }
    }

    public static void info(String msg) {
        if (LOG_LEVEL >= 2) {
            System.out.println("INFO: " + msg);
        }
    }

    public static void warn(String msg) {
        if (LOG_LEVEL >= 1) {
            System.out.println("WARN: " + msg);
        }
    }

    public static void error(String msg) {
        System.out.println("ERROR: " + msg);
    }

    public static File getRiverDir(String[] args) {
        if (GEW_DIR != null && GEW_DIR.length() > 0) {
            return new File(GEW_DIR);
        }
        else if (args != null && args.length > 0) {
            return new File(args[0]);
        }

        return null;
    }

    public static File getCSVFile(String[] args) {
        if (CSV_FILE != null && CSV_FILE.length() > 0) {
            return new File(CSV_FILE);
        }
        else if (args != null && args.length > 1) {
            return new File(args[1]);
        }

        return null;
    }

    public static File getSQLFile(String[] args) {
        if (SQL_FILE != null && SQL_FILE.length() > 0) {
            return new File(SQL_FILE);
        }
        else if (args != null && args.length > 2) {
            return new File(args[2]);
        }

        return null;
    }

    public static void main(String[] args) {
        info("Start convering CSV -> SQL statements");

        if (!FULL_MODE) {
            info("You are running in DEMO mode; other rivers than 'Saar', 'Mosel' and 'Elbe' are ignored.");
        }

        File riverDir = getRiverDir(args);

        if (riverDir == null) {
            warn("No rivers directory specified!");
            return;
        }
        else if (!riverDir.isDirectory()) {
            warn("Specified rivers directory is not a directory!");
            return;
        }
        else if (!riverDir.canRead()) {
            warn("Unable to read '" + riverDir.toString() + "'");
            return;
        }

        File csv = getCSVFile(args);

        if (csv == null) {
            warn("No CSV file specified!");
            return;
        }
        else if (csv.isDirectory()) {
            warn("Specified CSV file is a directory!");
            return;
        }
        else if (!csv.canRead()) {
            warn("Unable to read '" + csv.toString() + "'");
            return;
        }

        File sql = getSQLFile(args);

        if (sql == null) {
            warn("No destination file specified!");
            return;
        }
        else if (sql.isDirectory()) {
            warn("Specified destination file is a directory!");
            return;
        }
        else if (sql.exists() && !sql.canWrite()) {
            warn("Unable to write to '" + sql.toString() + "'");
            return;
        }
        else if (!sql.exists()) {
            try {
                sql.createNewFile();
            }
            catch (IOException ioe) {
                warn("Unable to write to '" + sql.toString() + "'");
                return;
            }
        }

        info("Start parsing CSV file '" + csv.toString() + "'");

        try {
            DgmSqlConverter parser = new DgmSqlConverter(riverDir, csv, sql);
            parser.read();
            parser.write();
        }
        catch (Exception e) {
            error("Unexpected error: " + e.getMessage());
            e.printStackTrace();
        }

        info("Finished converting CSV -> SQL regularly.");
    }

    public DgmSqlConverter(File riverDir, File csv, File sql) {
        this.riverDir = riverDir;
        this.csv = csv;
        this.sql = sql;
        this.dgms = new ArrayList<DGM>();
    }

    public void read() {
        info("Read DGM information from CSV file: " + csv.getAbsolutePath());

        InputStream in = null;

        try {
            in = new BufferedInputStream(new FileInputStream(csv));
        }
        catch (FileNotFoundException e) {
            error("File not found: " + e.getMessage());
            return;
        }

        Reader reader = new InputStreamReader(in);
        CSVReader csvReader = new CSVReader(reader, DEFAULT_SEPERATOR,
            DEFAULT_QUOTE);

        List<String[]> rows = new ArrayList<String[]>();

        int success = 0;

        try {
            rows = csvReader.readAll();

            for (int idx = 0; idx < rows.size(); idx++) {
                String[] row = rows.get(idx);
                if (readRow(row)) {
                    success++;
                }
                else {
                    warn("Unable to parse row " + (idx + 1));
                }
            }
        }
        catch (IOException e) {
            error("Error while parsing CSV: " + e.getMessage());
            return;
        }

        info("Parsed CSV file: " + rows.size() + " lines.");
        info("Parsed " + success + " line successful");
    }

    private boolean readRow(String[] row) {
        if (row == null) {
            warn("Row is null!");
            return false;
        }

        if (row.length < MIN_COLUMN_COUNT) {
            warn("invalid column count: " + row.length);
            return false;
        }

        StringBuffer rowBuffer = new StringBuffer();
        for (String col : row) {
            rowBuffer.append(col);
            rowBuffer.append(" | ");
        }
        debug(rowBuffer.toString());

        try {
            DGM dgm = new DGM();
            dgm.river = readRiver(row[IDX_RIVERNAME]);
            dgm.name = row[IDX_NAME];
            dgm.projection = row[IDX_PROJECTION];
            dgm.elevationState = row[IDX_ELEVATION_STATE];
            dgm.format = row[IDX_FORMAT];
            dgm.resolution = row[IDX_RESOLUTION];
            dgm.description = row[IDX_DESCRIPTION];
            dgm.lower = readLower(row[IDX_LOWER]);
            dgm.upper = readUpper(row[IDX_UPPER]);
            dgm.yearFrom = readFromYear(row[IDX_YEAR_FROM]);
            dgm.yearTo = readToYear(row[IDX_YEAR_TO]);
            dgm.borderBreak = readBorderBreak(row[IDX_BORDER_BREAK]);
            dgm.path = readPath(dgm.river, row[IDX_FILE_PATH],
                row[IDX_FILE_NAME]);

            dgms.add(dgm);

            return true;
        }
        catch (IllegalArgumentException iae) {
            warn(iae.getMessage());
        }

        return false;
    }

    private String readRiver(String rivername) throws IllegalArgumentException {
        if (rivername == null || rivername.length() == 0) {
            throw new IllegalAccessError("Invalid rivername: " + rivername);
        }

        if (!FULL_MODE
            && !(rivername.equals("Saar") || rivername.equals("Mosel") || rivername
                .equals("Elbe"))) {
            throw new IllegalArgumentException("In DEMO mode; skip river: "
                + rivername);
        }

        return rivername;
    }

    private Double readLower(String lower) throws IllegalArgumentException {
        try {
            return Double.valueOf(lower);
        }
        catch (NumberFormatException nfe) {
        }

        throw new IllegalArgumentException("Attribute 'lower' invalid: "
            + lower);
    }

    private Double readUpper(String upper) throws IllegalArgumentException {
        try {
            return Double.valueOf(upper);
        }
        catch (NumberFormatException nfe) {
        }

        throw new IllegalArgumentException("Attribute 'upper' invalid: "
            + upper);
    }

    private Integer readFromYear(String from) throws IllegalArgumentException {
        try {
            return Integer.valueOf(from);
        }
        catch (NumberFormatException nfe) {
        }

        return null;
    }

    private Integer readToYear(String to) throws IllegalArgumentException {
        try {
            return Integer.valueOf(to);
        }
        catch (NumberFormatException nfe) {
        }

        return null;
    }

    private String readPath(String rivername, String dir, String filename)
        throws IllegalArgumentException {
        File riverDir = new File(this.riverDir, rivername);
        File dgmDir = new File(riverDir, dir);
        File dgmFile = new File(dgmDir, filename);

        try {
            debug("Path of DGM = " + dgmFile.getAbsolutePath());

            if (dgmFile == null || !dgmFile.exists()) {
                throw new IllegalAccessError(
                    "Specified DGM file does not exist: "
                        + dgmFile.getAbsolutePath());
            }

            if (!dgmFile.isFile()) {
                throw new IllegalArgumentException(
                    "Specified DGM file is no file: "
                        + dgmFile.getAbsolutePath());
            }
        }
        catch (IllegalAccessError iae) {
            throw new IllegalArgumentException("Cannot find DGM file (river="
                + rivername + " | directory=" + dir + " | filename=" + filename
                + ")");
        }

        return dgmFile.getAbsolutePath();
    }

    private boolean readBorderBreak(String borderBreak) {
        if (borderBreak == null || borderBreak.length() == 0) {
            return true;
        }
        else if (borderBreak.toLowerCase().equals("ja")) {
            return true;
        }
        else if (borderBreak.toLowerCase().equals("nein")) {
            return false;
        }
        else {
            return true;
        }
    }

    public void write() {
        info("Write DEM information to SQL file: " + sql.getAbsolutePath());

        BufferedWriter bufferedWriter = null;
        try {
            bufferedWriter = new BufferedWriter(new FileWriter(sql));

            for (DGM dgm : dgms) {
                bufferedWriter.write(dgm.toSQL());
                bufferedWriter.newLine();
            }
        }
        catch (IOException ioe) {
            error(ioe.getMessage());
        }
        finally {
            if (bufferedWriter != null) {
                try {
                    bufferedWriter.close();
                }
                catch (IOException ioe) {
                }
            }
        }
    }
}