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

import org.litesoft.bo.*;
import org.litesoft.bo.attributes.*;
import org.litesoft.commonfoundation.typeutils.*;
import org.litesoft.db.*;
import org.litesoft.orsup.base.*;
import org.litesoft.orsup.lazyeval.*;

import java.sql.*;
import org.litesoft.commonfoundation.typeutils.Objects;
import java.util.*;

public abstract class SQLProductSpecificAbstractHelper extends SQL_OR_Helper implements SQLProductSpecificHelper
{
    // boolean  storesLowerCaseIdentifiers()
    //          Retrieves whether this database treats mixed case unquoted SQL identifiers as case insensitive and stores them in lower case.
    // boolean  storesLowerCaseQuotedIdentifiers()
    //          Retrieves whether this database treats mixed case quoted SQL identifiers as case insensitive and stores them in lower case.
    // boolean  storesMixedCaseIdentifiers()
    //          Retrieves whether this database treats mixed case unquoted SQL identifiers as case insensitive and stores them in mixed case.
    // boolean  storesMixedCaseQuotedIdentifiers()
    //          Retrieves whether this database treats mixed case quoted SQL identifiers as case insensitive and stores them in mixed case.
    // boolean  storesUpperCaseIdentifiers()
    //          Retrieves whether this database treats mixed case unquoted SQL identifiers as case insensitive and stores them in upper case.
    // boolean  storesUpperCaseQuotedIdentifiers()
    //          Retrieves whether this database treats mixed case quoted SQL identifiers as case insensitive and stores them in upper case.
    // boolean  supportsMixedCaseIdentifiers()
    //          Does the database treat mixed case unquoted SQL identifiers as case sensitive and as a result store them in mixed case? A JDBC CompliantTM driver will always return false.
    // boolean  supportsMixedCaseQuotedIdentifiers()
    //          Does the database treat mixed case quoted SQL identifiers as case sensitive and as a result store them in mixed case? A JDBC CompliantTM driver will always return true.

    protected CaseNormalizer mCaseNormalizer = LeaveCaseUnchanged.INSTANCE;
    protected SQLIndexColumnsLocator mIndexColumnsLocator = null;

    protected SQLProductSpecificAbstractHelper()
    {
        buildTypeMaps();
    }

    @Override
    public void initializeDB( Connection pConnection, DBinfo pDBinfo, SQLIndexColumnsLocator pIndexColumnsLocator )
            throws SQLException
    {
        mIndexColumnsLocator = pIndexColumnsLocator;
        DatabaseMetaData zDBMD = pConnection.getMetaData();

        if ( zDBMD.storesLowerCaseIdentifiers() )
        {
            mCaseNormalizer = ToLowercase.INSTANCE;
        }
        else if ( zDBMD.storesUpperCaseIdentifiers() )
        {
            mCaseNormalizer = ToUppercase.INSTANCE;
        }
    }

    @Override
    public void addColumnType( StringBuilder pCmd, SimpleColumnDefinitionExtended pSCD )
    {
        ColumnType zColumnType = getColumnType( pSCD );

        String zSqlType = zColumnType.getSqlType();
        String zDim = "";

        Integer zMaxLength = MaxLength.get( pSCD.getAMD() );
        if ( zColumnType.needsDim() && (zMaxLength == null) )
        {
            if ( null == (zMaxLength = zColumnType.defaultDim()) )
            {
                throw new IllegalStateException( "No AttributeMetaData 'MaxLength' for " + pSCD );
            }
        }
        if ( (zMaxLength != null) && (zColumnType.canUseDim() || zColumnType.needsDim()) )
        {
            zDim = "(" + zMaxLength + ")";
        }

        addColumnType( pCmd, zSqlType, zDim );
    }

    private ColumnType getColumnType( SimpleColumnDefinitionExtended pSCD )
    {
        AttributeMetaData zAMD = pSCD.getAMD();
        if ( zAMD == null )
        {
            throw new IllegalStateException( "No AttributeMetaData for " + pSCD );
        }
        AttributeMetaData.Type zType = zAMD.getType();
        if ( zType == null )
        {
            throw new IllegalStateException( "No AttributeMetaData.Type for " + pSCD );
        }
        BoAttribute.AttributeType zBoAttributeType = zType.getBoAttributeType();
        if ( zBoAttributeType == null )
        {
            throw new IllegalStateException( "No AttributeMetaData.Type.BoAttributeType for " + pSCD );
        }
        Class<?> zPOclass = zBoAttributeType.getType();
        if ( zPOclass == null )
        {
            throw new IllegalStateException( "No AttributeMetaData.Type.BoAttributeType.Type for " + pSCD );
        }
        Class<?> zJDBCclass = zPOclass;
        LiteSoftToJDBCtypeSupport zSupport = LiteSoftToJDBCtypeSupport.get( zPOclass );
        if ( zSupport != null )
        {
            zJDBCclass = zSupport.getJDBC();
            if ( (zJDBCclass == null) || NonPersistable.class.equals( zJDBCclass ) )
            {
                throw new IllegalStateException( "Not Persistable " + pSCD );
            }
        }
        ColumnType zColumnType = mColumnTypeBySQLTypeMap.get( zJDBCclass );
        if ( zColumnType == null )
        {
            throw new IllegalStateException( "Persistence of type (" + zJDBCclass + ") not supported for " + pSCD );
        }
        return zColumnType;
    }

    protected void addColumnType( StringBuilder pCmd, String pSqlType, String pDim )
    {
        pCmd.append( pSqlType );
        pCmd.append( pDim );
    }

    @Override
    public String normalizeIdentifier( String pIdentifier )
    {
        return mCaseNormalizer.normalize( pIdentifier );
    }

    @Override
    public String[] getTablesFor( Connection pConnection, DBinfo pDBinfo, String pTableNamePattern )
            throws SQLException
    {
        List<String> zMatches = SQLHelper.getAllTableNames( pConnection, //
                                                            pTableNamePattern );
        String zTableNamePattern = normalizeIdentifier( pTableNamePattern );
        if ( !zTableNamePattern.equals( pTableNamePattern ) )
        {
            List<String> zMatches2 = SQLHelper.getAllTableNames( pConnection, //
                                                                 zTableNamePattern );
            if ( !zMatches2.isEmpty() )
            {
                if ( zMatches.isEmpty() )
                {
                    zMatches = zMatches2;
                }
                else
                {
                    zMatches.addAll( zMatches2 );
                }
            }
        }
        return zMatches.isEmpty() ? Strings.EMPTY_ARRAY : zMatches.toArray( new String[zMatches.size()] );
    }

    @Override
    public Map<String, DBtableColumn[]> getColumns( Connection pConnection, DBinfo pDBinfo, String pTableNamePattern )
            throws SQLException
    {
        Map<String, DBtableColumn[]> zMatches = SQLHelper.getAllTableColumns( pConnection, //
                                                                              pTableNamePattern );
        String zTableNamePattern = normalizeIdentifier( pTableNamePattern );
        if ( !zTableNamePattern.equals( pTableNamePattern ) )
        {
            Map<String, DBtableColumn[]> zMatches2 = SQLHelper.getAllTableColumns( pConnection, //
                                                                                   zTableNamePattern );
            if ( !zMatches2.isEmpty() )
            {
                if ( zMatches.isEmpty() )
                {
                    zMatches = zMatches2;
                }
                else
                {
                    zMatches.putAll( zMatches2 );
                }
            }
        }
        return zMatches;
    }

    @Override
    public Map<String, DBtableIndex[]> getIndexes( Connection pConnection, DBinfo pDBinfo, String pTableName )
            throws SQLException
    {
        Map<String, DBtableIndex[]> zFounds = new HashMap<String, DBtableIndex[]>();

        String[] zTableNames = getTablesFor( pConnection, pDBinfo, SQLHelper.escapeTableNamePattern( pTableName ) );
        for ( String zTableName : zTableNames )
        {
            List<DBtableIndex> zMatches = SQLHelper.getAllTableIndexes( pConnection, zTableName );
            zFounds.put( zTableName, zMatches.toArray( new DBtableIndex[zMatches.size()] ) );
        }
        return zFounds;
    }

    private static final String SQL_STATE_CONSTRAINT_VIOLATION = "23000";
    private static final String SQL_STATE_CONSTRAINT_VIOLATION_STARTSWITH = SQL_STATE_CONSTRAINT_VIOLATION.substring( 0, 3 );

    @SuppressWarnings({"ThrowableInstanceNeverThrown"}) @Override
    public WrappedSQLException wrapSQLException( SQLException pException )
    {
        if ( isConstraintViolation( pException ) )
        {
            if ( isMissingSomethingException( pException ) )
            {
                if ( isMissingPKException( pException ) )
                {
                    return createMissingPKException( pException );
                }

                if ( isMissingFieldValueException( pException ) )
                {
                    return createMissingValueFieldException( pException );
                }
                return createMissingSomethingException( pException );
            }
            if ( isDuplicatePKException( pException ) )
            {
                return createDuplicatePKException( pException );
            }

            if ( isDuplicateFieldValueException( pException ) )
            {
                return createDuplicateFieldValueException( pException );
            }
            return createConstraintViolationException( pException );
        }

        //else
        return new WrappedSQLException( pException );
    }

    protected boolean isConstraintViolation( SQLException pException )
    {
        return pException.getSQLState().startsWith( SQL_STATE_CONSTRAINT_VIOLATION_STARTSWITH );
    }

    protected boolean isMissingSomethingException( SQLException pException )
    {
        return false;
    }

    protected boolean isMissingFieldValueException( SQLException pException )
    {
        return false;
    }

    protected boolean isMissingPKException( SQLException pException )
    {
        return false;
    }

    protected boolean isDuplicateFieldValueException( SQLException pException )
    {
        return false;
    }

    protected boolean isDuplicatePKException( SQLException pException )
    {
        return false;
    }

    @SuppressWarnings({"ThrowableInstanceNeverThrown"})
    protected WrappedSQLException createMissingSomethingException( SQLException pException )
    {
        return new MissingPkOrValueFieldException( pException );
    }

    @SuppressWarnings({"ThrowableInstanceNeverThrown"})
    protected WrappedSQLException createDuplicatePKException( SQLException pException )
    {
        return new DuplicatePKException( pException );
    }

    @SuppressWarnings({"ThrowableInstanceNeverThrown"})
    protected WrappedSQLException createConstraintViolationException( SQLException pException )
    {
        return new ConstraintViolationException( pException );
    }

    @SuppressWarnings({"ThrowableInstanceNeverThrown"})
    protected WrappedSQLException createMissingValueFieldException( SQLException pException )
    {
        List<String> pColumnNames = Collections.emptyList();
        return new MissingRequiredValueFieldException( pException, pColumnNames );
    }

    @SuppressWarnings({"ThrowableInstanceNeverThrown"})
    protected MissingPKException createMissingPKException( SQLException pException )
    {
        return new MissingPKException( pException );
    }

    @SuppressWarnings({"ThrowableInstanceNeverThrown"})
    protected WrappedSQLException createDuplicateFieldValueException( SQLException pException )
    {
        return new DuplicateValueFieldException( pException );
    }

    protected static boolean LLvalidateConWith( Connection pConnection, String pSQL )
    {
        return execNoResultSQL( pConnection, pSQL );
    }

    private interface CaseNormalizer
    {
        public String normalize( String pIdentifier );
    }

    public static class ToUppercase implements CaseNormalizer
    {
        public static final CaseNormalizer INSTANCE = new ToUppercase();

        @Override
        public String normalize( String pIdentifier )
        {
            return pIdentifier.toUpperCase();
        }
    }

    public static class ToLowercase implements CaseNormalizer
    {
        public static final CaseNormalizer INSTANCE = new ToLowercase();

        @Override
        public String normalize( String pIdentifier )
        {
            return pIdentifier.toLowerCase();
        }
    }

    public static class LeaveCaseUnchanged implements CaseNormalizer
    {
        public static final CaseNormalizer INSTANCE = new LeaveCaseUnchanged();

        @Override
        public String normalize( String pIdentifier )
        {
            return pIdentifier;
        }
    }

    protected boolean LLtableExists( Connection pConnection, DBinfo pDBinfo, String pTableName, String pExistsOnSuccessSQL )
            throws SQLException, MultipleTablesException
    {
        String[] zTableNames = getTablesFor( pConnection, pDBinfo, SQLHelper.escapeTableNamePattern( pTableName ) );
        if ( Objects.isNullOrEmpty( zTableNames ) )
        {
            return false;
        }
        if ( zTableNames.length > 1 )
        {
            throw MultipleTablesException.createFrom( pTableName, zTableNames );
        }
        return execNoResultSQL( pConnection, pExistsOnSuccessSQL );
    }

    protected boolean LLcolumnExists( Connection pConnection, DBinfo pDBinfo, String pTableName, String pColumnName, String pExistsOnSuccessSQL )
            throws SQLException, MultipleTablesException
    {
        Map<String, DBtableColumn[]> zFound = getColumns( pConnection, pDBinfo, SQLHelper.escapeTableNamePattern( pTableName ) );
        if ( zFound.isEmpty() )
        {
            return false;
        }
        if ( zFound.size() > 1 )
        {
            throw MultipleTablesException.createFrom( pTableName, zFound.keySet() );
        }
        return columnIn( pColumnName, zFound.values().iterator().next() ) && execNoResultSQL( pConnection, pExistsOnSuccessSQL );
    }

    private boolean columnIn( String pColumnName, DBtableColumn[] pColumns )
    {
        for ( DBtableColumn zColumn : pColumns )
        {
            if ( zColumn.getColumnName().equalsIgnoreCase( pColumnName ) )
            {
                return true;
            }
        }
        return false;
    }

    private void buildTypeMaps()
    {

        add( ColumnType.of( java.sql.Date.class, "DATE" ) );
        add( ColumnType.of( java.sql.Time.class, "TIME" ) );
        add( ColumnType.of( java.sql.Timestamp.class, "TIMESTAMP" ) );
        add( ColumnType.of( Boolean.class, "BOOLEAN" ) );
        add( ColumnType.dimable( String.class, "VARCHAR" ) );
        add( ColumnType.of( Integer.class, "INTEGER" ) );
        add( ColumnType.of( Long.class, "BIGINT" ) );
        add( ColumnType.of( Float.class, "FLOAT" ) );
        add( ColumnType.of( Double.class, "FLOAT" ) );
    }

    protected static class ColumnType
    {
        private Class mJDBCclass;
        private String mSqlType;
        private boolean mCanUseDim;
        private boolean mNeedsDim;
        private Integer mDefaultDim;

        private ColumnType( Class pJDBCclass, String pSqlType, boolean pCanUseDim, boolean pNeedsDim, Integer pDefaultDim )
        {
            mJDBCclass = pJDBCclass;
            mSqlType = pSqlType;
            mCanUseDim = pCanUseDim;
            mNeedsDim = pNeedsDim;
            mDefaultDim = pDefaultDim;
        }

        public static ColumnType of( Class pJDBCclass, String pSqlType )
        {
            return new ColumnType( pJDBCclass, pSqlType, false, false, null );
        }

        public static ColumnType dimable( Class pJDBCclass, String pSqlType )
        {
            return new ColumnType( pJDBCclass, pSqlType, true, false, null );
        }

        public static ColumnType dimmed( Class pJDBCclass, String pSqlType, Integer pDefaultDim )
        {
            return new ColumnType( pJDBCclass, pSqlType, false, true, pDefaultDim );
        }

        public static ColumnType dimmed( Class pJDBCclass, String pSqlType )
        {
            return dimmed( pJDBCclass, pSqlType, null );
        }

        public Class getJDBCclass()
        {
            return mJDBCclass;
        }

        public String getSqlType()
        {
            return mSqlType;
        }

        public boolean canUseDim()
        {
            return mCanUseDim;
        }

        public boolean needsDim()
        {
            return mNeedsDim;
        }

        public Integer defaultDim()
        {
            return mDefaultDim;
        }
    }

    protected void add( ColumnType pColumnType )
    {
        mColumnTypeBySQLTypeMap.put( pColumnType.getJDBCclass(), pColumnType );
    }

    protected final HashMap<Class, ColumnType> mColumnTypeBySQLTypeMap = new HashMap<Class, ColumnType>();
}