// This Source Code is in the Public Domain per: http://litesoft.org/License.txt
package org.litesoft.sql;

import java.sql.*;

import org.litesoft.commonfoundation.typeutils.*;

import java.util.*;

import org.litesoft.db.*;

public class SQLHelper
{
    public static final String ALL_WHERECLAUSE = "WHERE 1=1";
    protected static final String[] TYPES_TABLE = new String[]{"TABLE"};
    protected static final String[] TYPES_TABLE_AND_VIEW = new String[]{"TABLE", "VIEW"};

    public static String createAlterColumnRenameSQL( String table, String oldColumnName, String newColumnName )
    {
        return String.format( "ALTER TABLE %s RENAME COLUMN %s TO %s", table, oldColumnName, newColumnName );
    }

    public static String createRenameTableSQL( String pOldTableName, String pNewTableName )
    {
        return String.format( "ALTER TABLE %s RENAME TO %s", pOldTableName, pNewTableName );
    }

    public static String createDropColumnSQL( String pOldTableName, String pDropColumnName )
    {
        return String.format( "ALTER TABLE %s DROP COLUMN %s", pOldTableName, pDropColumnName );
    }

    public static String createAlterColumnTypeSQL( String pTableName, String pColumnName, String pNewTypeName, String pLength, String pPrecision )
    {
        String scaleClause = createScaleClause( pLength, pPrecision );
        return String.format( "ALTER TABLE %s ALTER COLUMN %s TYPE %s%s", pTableName, pColumnName, pNewTypeName, scaleClause );
    }

    private static String createScaleClause( String pLength, String pPrecision )
    {
        String scaleClause = "";
        if ( pLength.length() > 0 )
        {
            String precisionClause = pPrecision.length() > 0 ? ("," + pPrecision) : "";
            scaleClause = String.format( "(%s%s)", pLength, precisionClause );
        }
        return scaleClause;
    }

    public static String createAddRemoveNotNullSQL( String pTableName, String pColumnName, boolean pRequired )
    {
        if ( pRequired )
        {
            return createAddNotNullSQL( pTableName, pColumnName );
        }
        return createDropNotNullSQL( pTableName, pColumnName );
    }

    public static String createDropNotNullSQL( String pTableName, String pColumnName )
    {
        return String.format( "ALTER TABLE %s ALTER COLUMN %s DROP NOT NULL", pTableName, pColumnName );
    }

    public static String createAddNotNullSQL( String pTableName, String pColumnName )
    {
        return String.format( "ALTER TABLE %s ALTER COLUMN %s SET NOT NULL", pTableName, pColumnName );
    }

    public static String createAddDefaultSQL( String pTableName, String pColumnName, String pNewDefaultValue )
    {
        return String.format( "ALTER TABLE %s ALTER COLUMN %s SET DEFAULT '%s'", pTableName, pColumnName, pNewDefaultValue );
    }

    public static String createDropDefaultSQL( String pTableName, String pColumnName )
    {
        return String.format( "ALTER TABLE %s ALTER COLUMN %s DROP DEFAULT", pTableName, pColumnName );
    }

    public static String createDropTableSQL( String pOldTableName )
    {
        return String.format( "DROP TABLE %s ", pOldTableName );
    }

    public static String createAddTableSQL( String pTableName )
    {
        return String.format( "CREATE TABLE %s", pTableName );
    }

    /**
     * creates only a partial clause  for use both in an ALTER TABLE statemtent or in a CREATE TABLE statement
     * <br>Of particular note is that there are no tokens 'CREATE' 'ALTER' or 'COLUMN'
     */
    static String createColumnDefClause( String pName, String pType, String pLength, String pPrecision, String pDefaultValue, boolean notNull )
    {
        String zScaleClause = createScaleClause( pLength, pPrecision );
        String zNotNullClause = notNull ? "NOT NULL" : "NULL";
        String zDefaultValue = (pDefaultValue != null) ? pDefaultValue : "";
        return String.format( "%s %s%s %s %s", pName, pType, zScaleClause, zDefaultValue, zNotNullClause );
    }

    public static String createPrimaryKeyClause( String pColumnName, String pType )
    {
        return String.format( "%s %s not null primary key", pColumnName, pType );
    }

    public static String createAddColumnToExistingTableSQL( String tableName, String pColumnName, String pType, String pDefaultValue, boolean pNotNull, String pLength, String pPrecision )
    {

        String zDefClause = createColumnDefClause( pColumnName, pType, pLength, pPrecision, pDefaultValue, pNotNull );
        return String.format( "ALTER TABLE %s ADD COLUMN %s", tableName, zDefClause );
    }

    public static String createDropIndexSQL( String pIndexName )
    {
        return String.format( "DROP INDEX IF EXISTS %s ", pIndexName );
    }

    public static String createAddIndexSQL( String pTableName, String pIndexName, boolean pUnique, String pColumns )
    {
        String zUnique = pUnique ? "UNIQUE" : "";
        return String.format( "CREATE %s INDEX %s ON %s ( %s )", zUnique, pIndexName, pTableName, pColumns );
    }

    public static String createAddForeignKeySQL( String pTableName, String pColumnName, String pType, String pReferenceTable, String pReferenceColumn, boolean pRequired )
    {
        String zColumnRef = null == pReferenceColumn ? "" : "(" + pReferenceColumn + ")";
        String zRequired = pRequired ? "NOT NULL" : "NULL";
        return String.format( "ALTER TABLE %s ADD COLUMN %s %s REFERENCES %s %s %s", pTableName, pColumnName, pType, pReferenceTable, zColumnRef, zRequired );
    }

    public static String escapePercent( String pString )
    {
        return Strings.replace( pString, '%', "\\%" );
    }

    public static String escapeUnderscore( String pString )
    {
        return Strings.replace( pString, '_', "\\_" );
    }

    public static String escapeTableNamePattern( String pTableName )
    {
        return escapePercent( escapeUnderscore( pTableName ) );
    }

    /**
     * @return !null list of "Tables" for the Connection's Catalog, which will be "character cased" per the DB Vendor's "Table Character Case Stretegy"
     */
    public static List<String> getAllTableNames( Connection pConnection )
            throws SQLException
    {
        return getAllTableNamesOfTypes( pConnection, TYPES_TABLE );
    }

    /**
     * @return !null list of "Tables" for the Connection's Catalog, which will be "character cased" per the DB Vendor's "Table Character Case Stretegy"
     */
    public static List<String> getAllTableNames( Connection pConnection, String pTableNamePattern )
            throws SQLException
    {
        return getAllTableNamesOfTypes( pConnection, pTableNamePattern, TYPES_TABLE );
    }

    /**
     * @return !null list of "Tables" for the Connection's Catalog, which will be "character cased" per the DB Vendor's "Table Character Case Stretegy"
     */
    public static List<String> getAllTableNamesWithViews( Connection pConnection )
            throws SQLException
    {
        return getAllTableNamesOfTypes( pConnection, TYPES_TABLE_AND_VIEW );
    }

    /**
     * @return !null list of "Tables" for the Connection's Catalog, which will be "character cased" per the DB Vendor's "Table Character Case Stretegy"
     */
    public static List<String> getAllTableNamesWithViews( Connection pConnection, String pTableNamePattern )
            throws SQLException
    {
        return getAllTableNamesOfTypes( pConnection, pTableNamePattern, TYPES_TABLE_AND_VIEW );
    }

    /**
     * @return !null list of "Tables" for the Connection's Catalog, which will be "character cased" per the DB Vendor's "Table Character Case Stretegy"
     */
    public static List<String> getAllTableNamesOfTypes( Connection pConnection, String[] pTypes )
            throws SQLException
    {
        return getAllTableNamesOfTypes( pConnection, null, pTypes );
    }

    /**
     * @return !null list of "Tables" for the Connection's Catalog, which will be "character cased" per the DB Vendor's "Table Character Case Stretegy"
     */
    public static List<String> getAllTableNamesOfTypes( Connection pConnection, String pTableNamePattern, String[] pTypes )
            throws SQLException
    {
        DatabaseMetaData zMetaData = pConnection.getMetaData();
        ResultSet zRS = zMetaData.getTables( pConnection.getCatalog(), null, pTableNamePattern, pTypes );

        List<String> names = new ArrayList<String>();

        while ( zRS.next() )
        {
            names.add( zRS.getString( "TABLE_NAME" ) ); // See DatabaseMetaData.getTables
        }
        return names;
    }

    public static Map<String, DBtableColumn[]> getAllTableColumns( Connection pConnection, String pTableNamePattern )
            throws SQLException
    {
        DatabaseMetaData zMetaData = pConnection.getMetaData();
        ResultSet rs = zMetaData.getColumns( pConnection.getCatalog(), null, pTableNamePattern, null );
        // Note Rows returned order by: TABLE_NAME, and ORDINAL_POSITION
        // TABLE_NAME       String  =>  table name
        // COLUMN_NAME      String  =>  column name
        // DATA_TYPE        int     =>  SQL type from java.sql.Types
        // COLUMN_SIZE      int     =>  column size.  For char or date types this is the maximum number of
        //                              characters, for numeric or decimal types this is precision.
        // DECIMAL_DIGITS   int     =>  the number of fractional digits
        // NULLABLE         int     =>  is NULL allowed:
        //                                  columnNoNulls - might not allow NULL values
        //                                  columnNullable - definitely allows NULL values
        //                                  columnNullableUnknown - nullability unknown
        DBTCCollector zCollector = new DBTCCollector();
        while ( rs.next() )
        {
            String zName = rs.getString( "TABLE_NAME" );
            if ( zName != null )
            {
                zCollector.apply( zName, //
                                  rs.getString( "COLUMN_NAME" ), //
                                  rs.getInt( "DATA_TYPE" ), //
                                  rs.getInt( "COLUMN_SIZE" ), //
                                  rs.getInt( "DECIMAL_DIGITS" ), //
                                  rs.getInt( "NULLABLE" ) //
                );
            }
        }
        return zCollector.getReturnValue();
    }

    public static List<DBtableIndex> getAllTableIndexes( Connection pConnection, String pTableName )
            throws SQLException
    {
        DatabaseMetaData zDBMD = pConnection.getMetaData();
        ResultSet rs = zDBMD.getIndexInfo( pConnection.getCatalog(), null, pTableName, false, false );
        // Note Rows returned order by: NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION
        // INDEX_NAME   String  =>  index name; null when TYPE is tableIndexStatistic
        // NON_UNIQUE   boolean =>  Can index values be non-unique. false when TYPE is tableIndexStatistic
        // COLUMN_NAME  String  =>  column name; null when TYPE is tableIndexStatistic
        DBICollector zCollector = new DBICollector();
        while ( rs.next() )
        {
            String zName = rs.getString( "INDEX_NAME" );
            if ( zName != null )
            {
                zCollector.apply( zName, //
                                  rs.getBoolean( "NON_UNIQUE" ), //
                                  rs.getString( "COLUMN_NAME" ) );
            }
        }
        return zCollector.getReturnValue();
    }

    public static DBinfo createDBinfo( Connection pConnection )
            throws SQLException
    {
        DatabaseMetaData dbMD = pConnection.getMetaData();
        String zProductName = dbMD.getDatabaseProductName();
        String zProductVersion = getDatabaseProductVersion( dbMD );
        String zUser = getConnectionUserName( dbMD );
        return new DBinfo( zProductName, zProductVersion, zUser );
    }

    private static String getDatabaseProductVersion( DatabaseMetaData pDbMD )
    {
        try
        {
            return pDbMD.getDatabaseProductVersion();
        }
        catch ( SQLException e ) // Note: Some DBs will NOT provide this!
        {
            return null;
        }
    }

    private static String getConnectionUserName( DatabaseMetaData pDbMD )
    {
        try
        {
            return pDbMD.getUserName();
        }
        catch ( SQLException e ) // Note: Some DBs will NOT provide this!
        {
            return null;
        }
    }

    protected static boolean hasRow( ResultSet pResultSet )
            throws SQLException
    {
        boolean result = false;
        if ( pResultSet != null )
        {
            result = pResultSet.next();
            pResultSet.close();
        }
        return result;
    }

    private static class DBICollector
    {
        private List<DBtableIndex> mCollector = new ArrayList<DBtableIndex>();
        private String mName = null;
        private boolean mUnique = true;
        List<String> zColumnNames = new ArrayList<String>();

        public void apply( String pName, boolean pNonUnique, String pColumnName )
        {
            if ( !pName.equals( mName ) )
            {
                append();
                mName = pName;
                mUnique = true;
                zColumnNames.clear();
            }
            mUnique &= !pNonUnique;
            zColumnNames.add( pColumnName );
        }

        public List<DBtableIndex> getReturnValue()
        {
            append();
            return mCollector;
        }

        private void append()
        {
            if ( (mName != null) && !zColumnNames.isEmpty() )
            {
                mCollector.add( new DBtableIndex( mName, mUnique, zColumnNames.toArray( new String[zColumnNames.size()] ) ) );
            }
        }
    }

    private static class DBTCCollector
    {
        HashMap<String, DBtableColumn[]> mCollector = new HashMap<String, DBtableColumn[]>();
        private String mTableName = null;
        List<DBtableColumn> zColumns = new ArrayList<DBtableColumn>();

        // TABLE_NAME       String  =>  table name
        // COLUMN_NAME      String  =>  column name
        // DATA_TYPE        int     =>  SQL type from java.sql.Types
        // COLUMN_SIZE      int     =>  column size.  For char or date types this is the maximum number of
        //                              characters, for numeric or decimal types this is precision.
        // DECIMAL_DIGITS   int     =>  the number of fractional digits
        // NULLABLE         int     =>  is NULL allowed:
        //                                  columnNoNulls - might not allow NULL values
        //                                  columnNullable - definitely allows NULL values
        //                                  columnNullableUnknown - nullability unknown
        public void apply( String pTableName, String pColumnName, int pSqlTypesType, int pColumnSize, int pDecimalDigits, int pNullable )
        {
            if ( !pTableName.equals( mTableName ) )
            {
                append();
                mTableName = pTableName;
                zColumns.clear();
            }
            Integer zColumnSize = null;
            Integer zDecimalDigits = null;
            switch ( SQLTypeGroup.getGroup( pSqlTypesType ) )
            {
                case DECIMAL:
                    if ( (pDecimalDigits > 0) && (pDecimalDigits < pColumnSize) && (pColumnSize > 0) )
                    {
                        zDecimalDigits = pDecimalDigits;
                    }
                    // Fall Thru
                case INTEGER:
                case NUMERIC:
                case TEMPORAL:
                case CHAR:
                    if ( pColumnSize > 0 )
                    {
                        zColumnSize = pColumnSize;
                    }
                    // Fall Thru
                default:
                    break;
            }
            Boolean zNullable = null;
            switch ( pNullable )
            {
                case DatabaseMetaData.columnNoNulls:
                    zNullable = Boolean.FALSE;
                    break;
                case DatabaseMetaData.columnNullable:
                    zNullable = Boolean.TRUE;
                    break;
                default:
                    break;
            }

            zColumns.add( new DBtableColumn( pColumnName, pSqlTypesType, zColumnSize, zDecimalDigits, zNullable ) );
        }

        public Map<String, DBtableColumn[]> getReturnValue()
        {
            append();
            return mCollector;
        }

        private void append()
        {
            if ( (mTableName != null) && !zColumns.isEmpty() )
            {
                mCollector.put( mTableName, zColumns.toArray( new DBtableColumn[zColumns.size()] ) );
            }
        }
    }

    protected static boolean execNoResultSQL( Connection pConnection, String pSQL )
    {
        try
        {
            Statement statement = null;
            try
            {
                statement = pConnection.createStatement();
                statement.execute( pSQL );
                pConnection.commit();
            }
            finally
            {
                if ( statement != null )
                {
                    statement.close();
                }
            }
            return true;
        }
        catch ( SQLException e )
        {
            return false;
        }
    }
}