package scott.barleydb.build.specgen.fromdb;

import static java.util.Arrays.asList;

import java.sql.SQLException;

/*-
 * #%L
 * BarleyDB
 * $Id:$
 * $HeadURL:$
 * %%
 * Copyright (C) 2014 - 2017 Scott Sinclair
 *       <[email protected]>
 * %%
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Lesser 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 Lesser Public License for more details.
 *
 * You should have received a copy of the GNU General Lesser Public
 * License along with this program.  If not, see
 * <http://www.gnu.org/licenses/lgpl-3.0.html>.
 * #L%
 */

import java.sql.Types;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.Map;
import java.util.Set;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import schemacrawler.schema.Catalog;
import schemacrawler.schema.Column;
import schemacrawler.schema.ForeignKey;
import schemacrawler.schema.ForeignKeyColumnReference;
import schemacrawler.schema.IndexColumn;
import schemacrawler.schema.Schema;
import schemacrawler.schema.Table;
import schemacrawler.schemacrawler.SchemaCrawlerException;
import schemacrawler.schemacrawler.SchemaCrawlerOptions;
import schemacrawler.schemacrawler.SchemaInfoLevelBuilder;
import schemacrawler.utility.SchemaCrawlerUtility;
import scott.barleydb.api.config.NodeType;
import scott.barleydb.api.config.RelationType;
import scott.barleydb.api.core.types.JavaType;
import scott.barleydb.api.core.types.JdbcType;
import scott.barleydb.api.core.types.Nullable;
import scott.barleydb.api.specification.DefinitionsSpec;
import scott.barleydb.api.specification.EntitySpec;
import scott.barleydb.api.specification.JoinTypeSpec;
import scott.barleydb.api.specification.NodeSpec;
import scott.barleydb.api.specification.RelationSpec;
import scott.barleydb.api.specification.SpecRegistry;
import scott.barleydb.api.specification.constraint.ForeignKeyConstraintSpec;
import scott.barleydb.api.specification.constraint.PrimaryKeyConstraintSpec;

/**
 * Generates a schema specification from database meta-data
 * @author scott
 *
 */
public class FromDatabaseSchemaToSpecification {

    private static Logger LOG = LoggerFactory.getLogger(FromDatabaseSchemaToSpecification.class);

  public interface ExclusionRules {
    boolean excludeTable(Table tableName);

    boolean excludeColumn(Column column);
  }

    private final String namespace;
    private final SpecRegistry registry = new SpecRegistry();
    private final DefinitionsSpec spec;
    private final Set<String> prefixesToRemove = new HashSet<>();
    private final Map<Table, EntitySpec> entitySpecs = new HashMap<>();
    private final Map<Column, NodeSpec> nodeSpecs = new HashMap<>();
    private final Set<ProcessedFk> processedFks = new HashSet<>();
    private final ExclusionRules exclusionRules;

    public FromDatabaseSchemaToSpecification(String namespace) {
      this(namespace, new ExclusionRules() {
        @Override
        public boolean excludeTable(Table tableName) {
          return false;
        }

        @Override
        public boolean excludeColumn(Column column) {
          return false;
        }
      });
    }
    public FromDatabaseSchemaToSpecification(String namespace, ExclusionRules exclusionRules) {
        this.namespace = namespace;
        this.exclusionRules = exclusionRules;
        spec = new DefinitionsSpec();
        spec.setNamespace( namespace );
        registry.add(spec);
    }

    public void removePrefix(String ...prefixes) {
        for (String prefix: prefixes) {
            prefixesToRemove.add( prefix.toLowerCase() );
        }
    }

    public SpecRegistry generateSpecification(DataSource dataSource, String schemaName) throws Exception {
        SchemaCrawlerOptions options = new SchemaCrawlerOptions();
        // Set what details are required in the schema - this affects the
        // time taken to crawl the schema
        options.setSchemaInfoLevel(SchemaInfoLevelBuilder.detailed());
        if (schemaName != null) {
          options.setSchemaInclusionRule(s -> s.equals( schemaName ));
        }

        Catalog catalog = loadCatalog(dataSource, options);

        firstPass(catalog);
        secondPass(catalog);
        postProcess(catalog);
        return registry;
    }

    protected Catalog loadCatalog(DataSource dataSource, SchemaCrawlerOptions options) throws SchemaCrawlerException, SQLException {
        return SchemaCrawlerUtility.getCatalog( dataSource.getConnection(),
                options);
    }

    /**
     * provides the oppertunity for further processing.
     * @param catalog
     */
    protected void postProcess(Catalog catalog ) {
    }


    private void createForeignKeyConstraint(EntitySpec entitySpec, NodeSpec nodeSpec, RelationSpec relationSpec) {
        String keySpecName = createForeignKeyConstraintName( entitySpec, nodeSpec, relationSpec );
        EntitySpec toEntitySpec = relationSpec.getEntitySpec();

        Collection<NodeSpec> toPrimaryKey = toEntitySpec.getPrimaryKeyNodes(true);
        if (toPrimaryKey == null) {
            throw new IllegalStateException("Cannot create foreign key reference to entity " + toEntitySpec.getClassName() + " which  has no primary key");
        }
        ForeignKeyConstraintSpec spec = new ForeignKeyConstraintSpec(keySpecName, asList(nodeSpec), toEntitySpec, toPrimaryKey);
        entitySpec.add(spec);
    }

    protected String createForeignKeyConstraintName(EntitySpec entitySpec, NodeSpec nodeSpec, RelationSpec relationSpec) {
        return "fk_" + entitySpec.getTableName() + "_" + nodeSpec.getColumnName();
    }

    private String incrementNodeName(String nodeName) {
        char c = nodeName.charAt( nodeName.length() - 1);
        if (Character.isDigit(c)) {
            int i = Integer.parseInt("" + c);
            i++;
            return nodeName.substring(0, nodeName.length() -  1) + i;
        }
        else {
            return nodeName + "1";
        }
    }

    /**
     * the name of the node if it is a FK pointing to another table
     * @param fkNode
     * @return
     */
    private String genRealtionNodeName(NodeSpec fkNode) {
        String columnName = fkNode.getColumnName();
        if (columnName.toLowerCase().endsWith("_id")) {
            columnName = columnName.substring(0, columnName.length() -  3);
        }
        return toCamelCase(columnName);
    }

    private String genRealtionNodeName(EntitySpec pkEspec, boolean plural) {
        int i = pkEspec.getClassName().lastIndexOf('.');
        String name = pkEspec.getClassName().substring(i+1, pkEspec.getClassName().length());
        char c = Character.toLowerCase( name.charAt(0) );
        String result = c + name.substring(1, name.length());
        result = ensureFirstLetterIsLower( removePrefixes(result) );
        return plural ? result + "s" : result;
    }

    private String ensureFirstLetterIsLower(String name) {
        char c = name.charAt(0);
        if (Character.isLowerCase(c)) {
            return name;
        }
        return Character.toLowerCase(c) + name.substring(1, name.length());
    }

    private String removePrefixes(String name) {
        String lcName = name.toLowerCase();
        for (String prefix: prefixesToRemove) {
            if (lcName.startsWith(prefix)) {
                return name.substring(prefix.length());
            }
        }
        return name;
    }

    /**
     * create the entityspecs and nodespecs.
     * @param catalog
     */
    private void firstPass(Catalog catalog) {
        LOG.debug("First pass...");
        for (Schema schema: catalog.getSchemas()) {
          LOG.debug("Processing schema...");
          for (Table table: catalog.getTables(schema)) {
             if (exclusionRules.excludeTable(table)) {
              continue;
             }
            LOG.debug("Processing table {}", table.getName());
            EntitySpec espec = toEntitySpec(table);
            entitySpecs.put(table, espec);
            spec.add( espec );
          }
        }

    }

    /**
     * process the foreign key relations and constraints.
     */
    private void secondPass(Catalog catalog) {
        for (Schema schema: catalog.getSchemas()) {
            LOG.debug(schema.toString());
            for (Table table: catalog.getTables(schema)) {
                for (ForeignKey fk: table.getForeignKeys()) {
                    for (ForeignKeyColumnReference fkRef: fk.getColumnReferences()) {
                        Column fkCol = fkRef.getForeignKeyColumn();
                        Column pkCol = fkRef.getPrimaryKeyColumn();
                        Table pkTable = pkCol.getParent();

                        EntitySpec pkEspec = entitySpecs.get(pkTable);
                        EntitySpec fkEspec = entitySpecs.get(fkCol.getParent());
                        NodeSpec fkNSpec = nodeSpecs.get(fkCol);
                        if (pkEspec == null || fkEspec == null || fkNSpec == null) {
                          continue;
                        }

                        if (!processedFks.add(new ProcessedFk(fkNSpec, pkEspec))) {
                            continue;
                        }

                        /*
                         *  Do the N:1 natuarl foreign key relation *
                         */
                        fkNSpec.setName( genRealtionNodeName(fkNSpec) );
                        //java type is null, as the relation defines the type.
                        fkNSpec.setJavaType(null);
                        RelationSpec rspec = new RelationSpec();
                        rspec.setEntitySpec(pkEspec);
                        rspec.setJoinType(JoinTypeSpec.LEFT_OUTER_JOIN);
                        rspec.setType(RelationType.REFERS);
                        fkNSpec.setRelation(rspec);

                        LOG.debug("Added FK relation from {}.{} to {}", fkEspec.getClassName(), fkNSpec.getName(), pkEspec.getClassName());

                        createForeignKeyConstraint(fkEspec, fkNSpec, rspec);

                        /*
                         * do the opposite 1:N relation
                         */
                        //create the nodespec as there is no dbcolumn whch created a node for us
                        LOG.debug("Creating tomany node for N relation from {} to {}", pkEspec.getClassName(), fkEspec.getClassName());
                        NodeSpec toManyNodeSpec = new NodeSpec();
                        String nodeName = genRealtionNodeName(fkEspec, true);
                        while (pkEspec.getNodeSpec(nodeName) != null) {
                            nodeName = incrementNodeName(nodeName);
                        }
                        toManyNodeSpec.setName( nodeName );
                        toManyNodeSpec.setEntity(pkEspec);

                        rspec = new RelationSpec();
                        rspec.setBackReference(fkNSpec);
                        rspec.setEntitySpec(fkEspec);
                        rspec.setJoinType(JoinTypeSpec.LEFT_OUTER_JOIN);
                        rspec.setType(RelationType.REFERS);
                        toManyNodeSpec.setRelation(rspec);
                        pkEspec.add(toManyNodeSpec);
                    }
                }
            }
        }
    }


    protected EntitySpec toEntitySpec(Table table) {
        EntitySpec entitySpec = new EntitySpec();
        entitySpec.setTableName( table.getName());
        entitySpec.setClassName( generateClassName(table) );
        entitySpec.setAbstractEntity(false);
        entitySpec.setQueryClassName( generateQueryClassName(table) );
        entitySpec.setDtoClassName( generateDtoClassName(table) );

        for (Column column: table.getColumns()) {
            if (exclusionRules.excludeColumn( column )) {
              continue;
            }
            LOG.debug("Processing column {}", column.getName());
            NodeSpec nodeSpec = toNodeSpec(entitySpec, table, column);
            entitySpec.add( nodeSpec );

            //set PK contraints
            Collection<NodeSpec> key = new LinkedList<>();
            for (NodeSpec ns: entitySpec.getNodeSpecs()) {
                if (ns.isPrimaryKey()) {
                    key.add(ns);
                }
            }
            if (!key.isEmpty()) {
                createPrimaryKeyConstraint(entitySpec, key);
            }

        }
        return entitySpec;
    }

    protected NodeSpec toNodeSpec(EntitySpec entitySpec, Table table, Column column) {
        NodeSpec nodeSpec = new NodeSpec();
        nodeSpec.setName( getNodeName( column ));
        if (isPrimaryKey(table, column)) {
            nodeSpec.setPrimaryKey(true);
        }
        nodeSpec.setColumnName( column.getName());
        nodeSpec.setJdbcType( getNodeJdbcType( column ));
        nodeSpec.setJavaType( getJavaType( nodeSpec.getJdbcType() ));
        nodeSpec.setNullable( column.isNullable() ? Nullable.NULL : Nullable.NOT_NULL);
        if (nodeSpec.getJavaType() == JavaType.STRING) {
            nodeSpec.setLength( column.getSize() );
        }
        else if (nodeSpec.getJavaType() == JavaType.BIGDECIMAL) {
            nodeSpec.setPrecision( column.getSize() );
            nodeSpec.setScale( column.getDecimalDigits());
        }
        nodeSpec.setEntity(entitySpec);
        nodeSpecs.put(column, nodeSpec);
        return nodeSpec;
    }

  protected boolean isPrimaryKey(Table table, Column column) {
    if (table.getPrimaryKey() != null) {
      for (IndexColumn col : table.getPrimaryKey().getColumns()) {
        if (col.getName().equalsIgnoreCase(column.getName())) {
          return true;
        }
      }
    } else {
      LOG.warn("No primary key constraints for {}", table.getName());
    }
    return false;
  }

  protected static String getNodeName(Column column) {
    return toCamelCase(stripBadChars( column.getName() ));
  }

  protected static String stripBadChars(String name) {
    return name.replaceAll("\"", "");
  }

    private static String toCamelCase(String nameString) {
        String name = nameString.toLowerCase();
        int i = name.indexOf('_');
        while(i != -1) {
            String start = name.substring(0,  i);
            String end = "";
            if (i+2 < name.length()) {
                end = Character.toUpperCase( name.charAt(i+1) ) + name.substring(i+2, name.length());
            }
            name = start + end;
            i = name.indexOf('_');
        }
        return name;
    }

    protected JavaType getJavaType(JdbcType jdbcType) {
        switch(jdbcType) {
        case INT: return JavaType.INTEGER;
        case DECIMAL: return JavaType.BIGDECIMAL;
        case BIGINT: return JavaType.LONG;
        case CHAR: return JavaType.STRING;
        case NVARCHAR: return JavaType.STRING;
        case VARCHAR: return JavaType.STRING;
        case DATE: return JavaType.UTIL_DATE;
        case DATETIME: return JavaType.UTIL_DATE;
        case TIMESTAMP: return JavaType.UTIL_DATE;
        case BLOB: return JavaType.BYTE_ARRAY;
        case CLOB: return JavaType.STRING;
        case SMALLINT: return JavaType.INTEGER;
        default: throw new IllegalArgumentException("Unsupported JDBC type " + jdbcType);
        }
    }

    protected JdbcType getNodeJdbcType(Column column) {
        switch(column.getColumnDataType().getJavaSqlType().getJavaSqlType()) {
        case Types.VARCHAR: return JdbcType.VARCHAR;
        case Types.INTEGER: return JdbcType.INT;
        case Types.BIGINT: return JdbcType.BIGINT;
        case Types.CHAR: return JdbcType.CHAR;
        case Types.DATE: return JdbcType.DATE;
        case Types.TIMESTAMP: return JdbcType.TIMESTAMP;
        case Types.DECIMAL: return JdbcType.DECIMAL;
        case Types.NUMERIC: return JdbcType.DECIMAL;
        case Types.SMALLINT: return JdbcType.SMALLINT;
        case Types.BIT: return JdbcType.SMALLINT;
        case Types.BLOB: return JdbcType.BLOB;
        case Types.CLOB: return JdbcType.CLOB;
        case 2147483647: {
          JdbcType jt = getJdbcTypeForUnknownJavaSqlType(column);
          if (jt != null) {
            return jt;
          }
        }
        }
       throw new IllegalArgumentException("Unsupported column type " + column.getColumnDataType().getJavaSqlType() + " (" + column.getColumnDataType().getJavaSqlType().getJavaSqlType() + ")");
    }

    protected JdbcType getJdbcTypeForUnknownJavaSqlType(Column column) {
      /*
       * unknown type
       */
      if (column.getColumnDataType().getName().contains("TIMESTAMP")) {
          return JdbcType.TIMESTAMP;
      }
      return null;
    }

    protected String generateQueryClassName(Table table) {
        String ccName = "Q" + removePrefixes( toCamelCase(table.getName()) );
        return  namespace + ".query." + ccName;
    }

    protected String generateDtoClassName(Table table) {
      String ccName = toCamelCase(table.getName());
      ccName = Character.toUpperCase( ccName.charAt(0) ) + ccName.substring(1, ccName.length());
      return namespace + ".dto." + removePrefixes( ccName )+ "Dto";
  }

    protected String generateClassName(Table table) {
        String ccName = toCamelCase(table.getName());
        ccName = Character.toUpperCase( ccName.charAt(0) ) + ccName.substring(1, ccName.length());
        return namespace + ".model." + removePrefixes( ccName );
    }

    private void createPrimaryKeyConstraint(EntitySpec entitySpec, Collection<NodeSpec> key) {
        String keySpecName = createPrimaryKeyConstraintName( entitySpec, key );
        PrimaryKeyConstraintSpec pkSpec = new PrimaryKeyConstraintSpec(keySpecName, key);
        entitySpec.setPrimaryKeyConstraint( pkSpec );
    }

    protected String createPrimaryKeyConstraintName(EntitySpec entitySpec, Collection<NodeSpec> key) {
        return "pk_" + entitySpec.getTableName();
    }

    private static class ProcessedFk {
        private final NodeSpec from;
        private final EntitySpec to;
        public ProcessedFk(NodeSpec from, EntitySpec to) {
            this.from = from;
            this.to = to;
        }
        @Override
        public int hashCode() {
            final int prime = 31;
            int result = 1;
            result = prime * result + ((from == null) ? 0 : from.hashCode());
            result = prime * result + ((to == null) ? 0 : to.hashCode());
            return result;
        }
        @Override
        public boolean equals(Object obj) {
            if (this == obj)
                return true;
            if (obj == null)
                return false;
            if (getClass() != obj.getClass())
                return false;
            ProcessedFk other = (ProcessedFk) obj;
            if (from == null) {
                if (other.from != null)
                    return false;
            } else if (!from.equals(other.from))
                return false;
            if (to == null) {
                if (other.to != null)
                    return false;
            } else if (!to.equals(other.to))
                return false;
            return true;
        }
    }

}