/**
 * MySqlJoinGenerator traverses information_schema data to yield join specifications for the 
 * registered foreign keys in the passed connection's schema.  Only works for engines that
 * use DRI like InnoDb.  No copyright claimed - feel free to use this as you wish.
 * 
use getJoins method to retrieve strings like:

gclaim c inner join gcarrier ca on c.Carrier=ca.Row
gclaim c inner join gclaimassign cb on c.Row=cb.Claim
gclaim c inner join gclaimnote cn on c.Row=cn.Claim
gclaim c inner join gclaimshare cs on c.Row=cs.ClaimRow
tcustomer cu inner join torder o on cu.SiteNum=o.SiteNum and cu.cid=o.oCustomer
tlocation l inner join torderlocation ol on l.SiteNum=ol.SiteNum and l.loid=ol.LocRow
torder o inner join tcustomer cu on o.SiteNum=cu.SiteNum and o.oCustomer=cu.cid
torder o inner join torderarticle oa on o.oid=oa.oaOrder and o.SiteNum=oa.SiteNum
 
*/
public class MySqlJoinGenerator 
{    
	private final static Comparator<String>IGNORE_CASE=(a,b) -> a.compareToIgnoreCase(b);
	private final Map<String,String>aliasMap = new TreeMap<String,String>(IGNORE_CASE);
	private final List<ForeignKeyRow>fkRows=new ArrayList<>();
	private final Map<String,String>groupPrefixes = new TreeMap<String,String>(IGNORE_CASE);
	private final Set<String>ignorePrefixes = new TreeSet<String>(IGNORE_CASE);
	/**
	 * Provided as a sample of usage: 
	 * @return a list of strings like "fromTable fromAlias inner join toTable toAlias on fromAlias.fromCol1=toAlias.toCol1" 
	 * */ 
	public static List<String>dumpAll(Connection mysqlConn) throws SQLException
	{
		var ignorePrefixes = Set.of("t","g");
		var groupPrefixes = Map.of(
				"gCarrier","ca",
				"gClaim","c",
				"gUser","gu",
				"tArticle","a",
				"tCompany","co",
				"tCustomer","cu",
				"tOrder","o",
				"tOrderRoom","orm",
				"tUser","u");
		var fd = new MySqlJoinGenerator(mysqlConn,ignorePrefixes,groupPrefixes);
		for (var spec : fd.getJoins())
		{
			System.out.println(spec);
		}
	}
	/**
	 * @param mysqlConn using schema we care about
	 * @param ignorePrefixes if you use t as table name prefix you may want to include t as an ignorePrefix
	 * @param groupPrefixes if key is full table name, pre-registers alias, like tCustomer→c, or if you have 
	 *	a tree of related tables like tOrder*, you can force them all to have the same first character, like o	 
	 * @throws SqlException
	 */
	public MySqlJoinGenerator(Connection mysqlConn,Set<String>ignorePrefixes,Map<String,String>groupPrefixes) throws SQLException
    {
    	this.ignorePrefixes.addAll(ignorePrefixes);
    	this.groupPrefixes.putAll(groupPrefixes);
    	this.fkRows.addAll(ForeignKeyRow.selectForSchema(mysqlConn));
    	for (var r : fkRows)
    	{
    		assignPreferred(r);
    	}
    	for (var r : fkRows)
    	{
			assignArbitrary(r.fromTable);
			assignArbitrary(r.toTable);
    	}    
    	//
    	var valueCheck = new TreeSet<String>(IGNORE_CASE);
    	for (var alias : aliasMap.values())
    	{
    		if (! valueCheck.add(alias))
    		{
    			StringKit.println(getClass().getSimpleName() + " FAIL: alias " + alias + " assigned multiple times.");
    		}
    	}
    }
	/** assigns alias to table passed as next available single character, or
     * if we've exhausted the alphabet, as two characters where second character
     * could be a-z or 1-9
     */
    private void assignArbitrary(String tableName)
    {
		if (aliasMap.containsKey(tableName))
		{
			return;
		}
		var nameFrom = tableName;
		for (var ignorePrefix : ignorePrefixes)
		{
			if (tableName.toLowerCase().startsWith(ignorePrefix.toLowerCase()))
			{
				nameFrom = tableName.substring(ignorePrefix.length());
				break;
			}
		}
		var charAlias = nameFrom.toLowerCase().charAt(0);
		while (charAlias!=0 && aliasMap.containsValue(charAlias + ""))
		{
			charAlias = nextFirst(charAlias);
		}
		if (charAlias != 0)
		{
			aliasMap.put(tableName, charAlias+"");
			return;
		}
		//try 2 chars
		var aliasPrefix = nameFrom.toLowerCase().substring(0,1);
		char aliasSuffix = nameFrom.length() > 1 ? nameFrom.toLowerCase().charAt(1) : 'a';
		String strAlias = aliasPrefix + aliasSuffix;
		while (aliasMap.containsValue(strAlias) || badAlias(strAlias))
		{
			aliasSuffix = nextSecond(aliasSuffix);
			if (aliasSuffix==0)
			{
				break;
			}
			strAlias = aliasPrefix + aliasSuffix;
		}
		if (! badAlias(strAlias))
		{
			aliasMap.put(tableName, strAlias);
		}
    }
	/** assigns alias to table passed by group prefix */
    private void assignByGroup(String tableName)
    {
		var prefixFullMatch = groupPrefixes.get(tableName);
		if (prefixFullMatch!=null)
		{
			setAlias(tableName,prefixFullMatch);
			return;
		}
		String groupMatch = null;
		for (var prefixKey : groupPrefixes.keySet())
		{
			if (tableName.toLowerCase().startsWith(prefixKey.toLowerCase()))
			{
				var aliasPrefix = groupPrefixes.get(prefixKey).toLowerCase();
				var tableNameSuffix = tableName.substring(prefixKey.length());
				var aliasSuffix = tableNameSuffix.toLowerCase().charAt(0);
				var alias = aliasPrefix + aliasSuffix;
				while (aliasMap.containsValue(alias) || badAlias(alias))
				{
					aliasSuffix = nextFirst(aliasSuffix);
					if (aliasSuffix==0)
					{
						break;
					}
					alias = aliasPrefix + aliasSuffix;
				}
				if (aliasSuffix!=0)
				{
					groupMatch = alias;
				}
				break;
			}
		}   
		if (groupMatch != null)
		{
			setAlias(tableName,groupMatch);
		}
    }
	/**assigns from and to aliases based on group prefixes */
    private void assignPreferred(ForeignKeyRow r) 
    {
    	for (var from : new boolean[] {true,false})
    	{
    		var tableName = from ? r.fromTable : r.toTable;
    		if (aliasMap.containsKey(tableName))
    		{
    			continue;
    		}
    		assignByGroup(tableName);
    	}
    }
    /** indicates whether alias is ok for use in MySQL */
    private boolean badAlias(String v)
    {
    	if (v.charAt(0)==0)
    	{
    		return true;
    	}
    	if (v.length()==1)
    	{
    		return false;
    	}
    	return v.charAt(1)==0 || v.equalsIgnoreCase("if") || v.equalsIgnoreCase("or");
    }
    /** @return all joins for schema */
	public List<String> getJoins() 
	{
		return getJoins(null,null);
	}
    /** 
	 * @param String fromTableOrNullForAll a full table name or null for any from table 
	 * @param String toTableOrNullForAll a full table name or null for any to table
	 * @return all joins meeting criteria from params */
	public List<String> getJoins(String fromTableOrNullForAll,String toTableOrNullForAll) 
	{
		var result = new ArrayList<String>();
    	for (var r : fkRows)
    	{
			if (fromTableOrNullForAll!=null && ! fromTableOrNullForAll.equalsIgnoreCase(r.fromTable))
			{
				continue;
			}
			if (toTableOrNullForAll!=null && ! toTableOrNullForAll.equalsIgnoreCase(r.toTable))
			{
				continue;
			}
			result.add(r.getFromTo(aliasMap));
			result.add(r.getToFrom(aliasMap));
    	}    
    	result.sort(IGNORE_CASE);
		return result;
	}
    /** yields the next character allowed as first of an alias, or 0 (not '0') on failure */
    private char nextFirst(char what)
	{
    	final String abc = "abcdefghijklmnopqrstuvwxyz";
		var ix = abc.indexOf(what);
		if (ix < abc.length() - 1)
		{
			return abc.charAt(ix + 1);
		}
		return 0;
	}
    /** yields the next character allowed as second of an alias, or 0 (not '0') on failure */
    private char nextSecond(char what)
	{
    	final String abc2 = "abcdefghijklmnopqrstuvwxyz123456789";
		var ix = abc2.indexOf(what);
		if (ix < abc2.length() - 1)
		{
			return abc2.charAt(ix + 1);
		}
		return 0;
	}
	private void setAlias(String table,String alias)
    {
		aliasMap.put(table,alias.toLowerCase());
    }
    static class ForeignKeyRow 
    {
    	public final String[] fromColumns;
    	public final String fromTable;
    	public final String[] toColumns;
    	public final String toTable;
        public static List selectForSchema(Connection mysqlConn) throws SQLException
    	{
    		final var sql="""
    		select tc.table_name as ft
    		    ,replace(f.ref_name,concat(schema(),'/'),'') as tt    
    		    ,group_concat(fc.for_col_name) as fcs
    		    ,group_concat(fc.ref_col_name) as tcs
    		from information_schema.Table_CONSTRAINTS tc
    		inner join information_schema.INNODB_SYS_FOREIGN f on f.id=concat(schema(),'/',tc.constraint_name)
    		inner join information_schema.INNODB_SYS_FOREIGN_COLS fc on f.id=fc.id
    		inner join information_schema.columns c on c.table_name=tc.table_name and c.column_name=fc.for_col_name
    		where tc.constraint_schema = schema() and tc.constraint_type like 'FOREIGN_KEY%'
    		group by ft,tt,tc.constraint_name
    		order by ft,tt,tc.constraint_name
    		""";
    		var result = new ArrayList();
    		try (var rs = mysqlConn.createStatement().executeQuery(sql))
    		{
    			while (rs.next())
    			{
    				result.add(new ForeignKeyRow(rs));
    			}
    			rs.close();
    		}
    		return result;
    	}
        ForeignKeyRow(ResultSet rs) throws SQLException
        {
            this.fromTable = rs.getString(1);
            this.toTable = rs.getString(2);
            this.fromColumns = rs.getString(3).split(",");
            this.toColumns = rs.getString(4).split(",");
        }
        public String getFromTo(Map<String,String>aliasMap)
        {
        	return getSpec(aliasMap,fromTable,fromColumns,toTable,toColumns);
        }
        private String getSpec(Map<String,String>aliasMap,String ft,String[] fca,String tt,String[]tca)
        {
        	var fromAlias = aliasMap.get(ft);
        	if (fromAlias == null)
        	{
        		fromAlias = ft;
        	}
        	var toAlias = aliasMap.get(tt);
        	if (toAlias == null)
        	{
        		toAlias = tt;
        	}
        	var b = new StringBuilder("")
        			.append(ft).append(" ").append(fromAlias)
        			.append(" inner join ")
        			.append(tt).append(" ").append(toAlias);
        	var conjunction = " on ";
        	for (int i=0;i<fca.length;i++)
        	{
        		b.append(conjunction).append(fromAlias).append(".").append(fca[i]).append("=")
        		.append(toAlias).append(".").append(tca[i]);
        		conjunction = " and ";
        	}
        	return b.toString();
        }		
        public String getToFrom(Map<String,String>aliasMap)
        {
        	return getSpec(aliasMap,toTable,toColumns,fromTable,fromColumns);
        }
    }
}