/*

    Copyright (C) 2015 North Carolina State University

    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
package org.kuali.ole.contrib.persistor.common

import groovy.sql.Sql
import groovy.util.logging.Slf4j
import org.apache.commons.lang3.time.FastDateFormat

import java.sql.ResultSet

/**
 * Analyzes tables to get basic column info (name, Java type, length, nullability), and column order.  This allows
 * consuming programs to do some basic validity checks and ensure that the output is correct.   it also lows for the
 * automatic generation of (correct) loader files.
 */
@Slf4j
class TableAnalyzer {

    Sql sql

    // A format for timestamps that seems to be accepted by MariaDB 10.1
    def timestampFormatter = FastDateFormat.getInstance("yyyy-MM-dd HH:mm:ss.SS")

    // ISO 8601 for date fields.
    private def dateFormatter = FastDateFormat.getInstance("yyyy-MM-dd")


    private static boolean isQuotable(String className) {
        // this will do for a start
        return ! Number.isAssignableFrom(Class.forName(className))
    }

    /**
     * Gets the LOAD DATA INFILE statement for a table from a specified filename, assuming the file was generated
     * with a "CSV" format using a mapper generated by this class.
     * @param the name of the output file
     * @param tableName the name of the table
     * @return
     */
    public static String getDataLoadTemplate(String fileName, String tableName ) {

        return """LOAD DATA INFILE '${fileName}'
                    INTO TABLE ${tableName}
                    CHARACTER SET utf8mb4
                    FIELDS TERMINATED BY ','
                    OPTIONALLY ENCLOSED BY '"'
                    LINES TERMINATED BY '\\n';
                """.toString()
    }


    // more data is available about columns (e.g. default values) using this method than
    // the much simpler resultSetMetadata technique used below.
    def getDBMetadata(String tableName) {
        // make sure our bases are covered with respect to table names
        def tableNames = [ tableName.toUpperCase(), tableName, tableName.toLowerCase() ]
        def conn = sql.dataSource.connection
        def dmd = conn.metaData

        def tblDef = tableNames.findResult {
            String tbl ->
                def tbldef = [:]
                ResultSet rs = dmd.getColumns(null,null,tbl, '%')
                while( rs.next() ) {
                    tbldef[ rs.getString("COLUMN_NAME")] = [ defaultValue: rs.getString("COLUMN_DEF"),
                                                             generated: rs.getString("IS_GENERATEDCOLUMN"),
                                                             colSize: rs.getString("COLUMN_SIZE"),
                                                             autoIncrement : rs.getString("IS_AUTOINCREMENT")
                                                           ]

                }
                rs.close()
                tbldef
        }

        conn.close()
        return tblDef
    }

    /**
     * Gets metadata about columns (type, length, javaType, nullability) for a named table.
     * @param tableName
     * @return
     */
    public Map<String, Map<String,Object>> getColumnMetadata(String tableName) {
        def tableData = [:]
        def dbmd = getDBMetadata(tableName)
        log.info "Reading metadata for ${tableName}"
        sql.query "select * from " + tableName + " LIMIT 1", {
            ResultSet rs ->
                def md = rs.metaData
                for (int i=1;i<= md.columnCount;i++) {
                    def cn = md.getColumnName(i)
                    tableData[cn] = [javaType: md.getColumnClassName(i), nullable: md.isNullable(i) == 1, length: md.getColumnDisplaySize(i) ]
                }
        }
        tableData
    }

    /**
     * Gets a validator for input data.  This can be used as a first step before trying to write or save the data,
     * or during a "dry run" to identify problematic records.
     * @param tableName  the name of the table
     * @param warningsAreFatal whether to have warnings be reported as errors.
     * @return a closure that itself returns a map with keys <code>valid</code> (boolean) and <code>warnings</code> (list of warnings)
     * for valid records, or throws an IllegalArgumentException for invalid records.
     */
    public Closure getValidator(String tableName, boolean warningsAreFatal = false) {
        def metadata = getColumnMetadata(tableName)

        def validator = {
            Map rowData ->
                def errors = []
                def warnings = warningsAreFatal ? errors : []
                metadata.each {
                    String colname, Map props ->
                        def value = rowData[colname.toLowerCase()]
                        if (value == null && !props.nullable && !props.defaultValue) {
                            warnings << "Column '${colname} is unset and column has no default and may not be null."
                            return
                        } else {
                            if (props.javaType == 'java.lang.String' && String.valueOf(value).size() > props.length) {
                                errors << "Column '${colname} has size ${props.length} but value '${value}' exceeds that"
                            }
                        }
                }
                rowData.keySet().each { String key ->
                    if (key.toUpperCase() in metadata) {
                        warnings << "Map key ${key} is not found in table"
                    }
                }
                if ( errors ) {
                    throw new IllegalArgumentException(errors.join("\n"))
                }
                [ valid: true, warnings: warnings ]
        }
        validator
    }


    /**
     * Creates a closure for this record type that outputs column *values* in the form that would be
     * expected by a MySQL/MariaDB LOAD DATA INFILE command (quoting string values, replacing <code>null</code>
     * with <code>\N</code>, and formatting date and timestamp fields correctly.
     **/
    public Closure<Map> getLoaderColumnMapper(String tableName) {
        def metaData = getColumnMetadata(tableName)
        // since we are writing out files, we need to know which fields are quotable
        metaData.each { k, v ->
            v.quotable = isQuotable(v.javaType)
        }

        def mapper = {
                Map rowData ->
                    def result = [:]
                    metaData.each {
                        String colName, Map props ->
                            def val = rowData[colName.toLowerCase()]
                            if ( val != null && props.javaType == 'java.lang.String' ) {
                                if ( String.valueOf(val).size() > props.length ) {
                                    log.warn "For row ${rowData}, column '${colName} is too long ${ val.size() } vs. ${ props.length }; truncating"
                                    val = val.substring(0,props.length)
                                }
                            }

                            if ( val instanceof Date ) {
                                if ( props.javaType == 'java.loader.Timestamp' ) {
                                    val = timestampFormatter.format(val)
                                } else if ( props.type == 'java.loader.Date' ) {
                                    val = dateFormatter.format(val)
                                }
                            }

                            if ( props.quotable && val != null ) {
                                val = '"' + String.valueOf(val).replaceAll('"', '\\\\"') + '"'
                            }

                            if ( val == null ) {
                                if (!props.nullable) {
                                    log.warn "Null value in column ${colName}"
                                    throw new Error("Temporarily dying on null value for stacktrace!")
                                }
                                val = '\\N' // MySQL's symbol for 'null' in dataLoader
                            }

                            result[colName] = val
                    }
                    result
        }
        mapper
    }
}
