package com.emforium.tools;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import java.util.TreeSet;

// call this class to generate the db sizes of all the char/varchar strings.
// this size map is used inside html model forms to default to a maximum size for text fields
public class GenDataDictionary {
	public static void main(String[] args) throws Exception {
		GenDataDictionary gen = new GenDataDictionary();
		try {
			gen.open();
			gen.collectEntitiesAndFields();
		} finally {
			gen.done();
		}
		gen.writeConfig();
	}

	private Connection connection;
	private DatabaseMetaData dbmeta;
	// these are stored in primary location
	private Map<String,Integer> fieldSize = new TreeMap<String,Integer>();
	private Map<String,Integer> entityFieldSize = new TreeMap<String,Integer>();
	// these are stored as details
	private Map<String,Set<String>> fieldEntitySizeSet = new TreeMap<String,Set<String>>();
	private Map<String,Set<Integer>> fieldSizeSet = new TreeMap<String,Set<Integer>>();
	
	void writeConfig() throws Exception {
		PrintWriter conf = new PrintWriter("./config/FieldSize.properties");
		conf.println("# field and maximum size for the field");
		writeMap(conf,"",fieldSize);
		conf.flush();
		conf.close();

		PrintWriter entityFieldConf = new PrintWriter("./config/EntityFieldSize.properties");
		entityFieldConf.println("# entity.field to maximum size for the field");
		writeMap(entityFieldConf,"",entityFieldSize);
		entityFieldConf.flush();
		entityFieldConf.close();

		if ( fieldSizeSet.size() > 0 || fieldEntitySizeSet.size() > 0 ) {
			PrintWriter detailsConf = new PrintWriter("./config/FieldSizeAnomaly.properties");
			detailsConf.println("# field to list of sizes available for field");
			writeMap(detailsConf,"size.",fieldSizeSet);
			detailsConf.println("# field to list of 'entity.size', if size varies per entity");
			writeMap(detailsConf,"entitysize.",fieldEntitySizeSet);
			detailsConf.flush();
			detailsConf.close();
		}
	}

	private void writeMap(PrintWriter prt, String prefix, Map<String,?> map) {
		for ( Map.Entry<String, ?> entry : map.entrySet() ) {
			String key = entry.getKey();
			Object value = entry.getValue();
			if ( value instanceof Iterable ) {
				StringBuffer buf = null;
				@SuppressWarnings("unchecked")
				Iterator<Object> iter = ((Iterable)value).iterator();
				while ( iter.hasNext() ) {
					if ( buf == null )
						buf = new StringBuffer();
					else
						buf.append(",");
					buf.append(String.valueOf(iter.next()));
				}
				prt.println(prefix+key+"="+buf.toString());
			} else {
				prt.println(prefix+key+"="+String.valueOf(value));
			}
		}
	}
	
	void open() throws Exception {
        //connection = getConnection("org.postgresql.Driver","jdbc:postgresql://server1/ofbiz_hb_081122","postgres","postgres");
	    // TODO dmay: The server connection details shouldnt be hardcoded here
        connection = getConnection("org.postgresql.Driver","jdbc:postgresql://127.0.0.1/ofbiz_local","ofbiz","ofbiz");
		dbmeta = connection.getMetaData();
	}
	
	void done() throws Exception {
		if ( connection != null ) {
			connection.close();
			connection = null;
		}
	}
	
    Connection getConnection(String driver,String url, String userid, String password) throws Exception {
    	Class.forName(driver);
        return DriverManager.getConnection(url,userid,password);
    }
    
    // convert from db to ofbiz application names
    private String dbNameToOfbizName(String table,boolean tableType) {
        // convert to entity
        StringBuffer buf = new StringBuffer();
        boolean first = true;
        for ( String part : table.split("_") ) {
        	if ( tableType || !first )
            	buf.append(Character.toUpperCase(part.charAt(0)));
        	else
            	buf.append(Character.toLowerCase(part.charAt(0)));
            buf.append(part.substring(1).toLowerCase());
            first = false;
        }
        return buf.toString();
    }

    // create map that holds a set of values
    private <K,V>void addToMultiMap(Map<K,Set<V>> map,K key, V value) {
    	Set<V> set = map.get(key);
    	if ( set == null ) {
    		set = new TreeSet<V>();
    		map.put(key, set);
    	}
    	set.add(value);
    }
    // remove if map has a set with only single entry
    private <K>void removeSingleEntry(Map<K,? extends Collection<?>> map,Map<K,? extends Collection<?>> associatedMap) { 
    	List<K> list = new ArrayList<K>();
    	for ( Map.Entry<K,? extends Collection<?>> entry : map.entrySet() ) {
    		if ( entry.getValue().size() <= 1 )
    			list.add(entry.getKey());
    	}
    	for ( K key : list ) {
    		map.remove(key);
    		associatedMap.remove(key);
    	}
    }
    
    void collectEntitiesAndFields() throws Exception {
    	List<String> tables = new ArrayList<String>();
        ResultSet rs = dbmeta.getTables(null,null,null,new String[] { "TABLE" });
        while(rs.next()) {
        	tables.add(rs.getString("TABLE_NAME"));
        }
        rs.close();
        for ( String table : tables ) {
            String entity = dbNameToOfbizName(table,true);
            rs = dbmeta.getColumns(null,null,table,null);
            while (rs.next()) {
	            String colName = rs.getString("COLUMN_NAME");
	            int colType = rs.getInt("DATA_TYPE");
	            int colSize = rs.getInt("COLUMN_SIZE");
	            
	            // care only about strings
	            if ( colType != Types.CHAR 
	            		&& colType != Types.VARCHAR 
	            		//&& colType != Types.LONGVARCHAR 
	            		)
	            {
	            	continue;
	            }
	            
	            String field = dbNameToOfbizName(colName,false);
	            entityFieldSize.put(entity+"."+field, colSize);
	            addToMultiMap(fieldEntitySizeSet,field,entity+":"+colSize);
	            addToMultiMap(fieldSizeSet,field,colSize);
            }
            rs.close();
        }
        // create max size map
        for ( Map.Entry<String,Set<Integer>> entry : fieldSizeSet.entrySet() ) {
        	TreeSet<Integer> sizeSet = (TreeSet<Integer>)entry.getValue();
        	fieldSize.put(entry.getKey(), sizeSet.first());
        }
        // remove from 'fieldEntitySizeSet' and 'fieldSizeSet' if is only one size per entity
        removeSingleEntry(fieldSizeSet,fieldEntitySizeSet);
    }
}