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

import java.sql.*;
import java.util.*;

import org.litesoft.db.*;
import org.litesoft.util.*;

public class SQLProductSpecificHelperHSQL_Database_Engine extends SQLProductSpecificAbstractHelper
{
    @Override
    public void initializeDB( Connection pConnection, DBinfo pDBinfo, SQLIndexColumnsLocator pIndexColumnsLocator )
            throws SQLException
    {
        super.initializeDB( pConnection, pDBinfo, pIndexColumnsLocator );
        pConnection.setAutoCommit( true );
        Statement zStatement = pConnection.createStatement();
        zStatement.execute( "SET PROPERTY \"sql.enforce_strict_size\" TRUE" );
    }

    @Override
    protected void addColumnType( StringBuilder pCmd, String pSqlType, String pDim )
    {
        super.addColumnType( pCmd, pSqlType, pDim );
        if ( pDim.length() == 0 )
        {
            if ( "VARCHAR".equalsIgnoreCase( pSqlType ) )
            {
                pCmd.append( "(999999)" ); // should probably be: Integer.MAXVALUE
            }
        }
    }

    private static final String DUPLICATE_FIELD_VALUE_PATTERN = "duplicate value(s) for column(s)";
    private static final String NON_NULLABLE_COLUMN_PATTERN = "non-nullable column: column: ";
    private static final String NOT_NULLABLE_COLUMN_PATTERN_2 = " table:";

    /*
    private static final String UNIQUE_CONSTRAINT_PATTERN = "Violation of unique constraint";
    private static final String PKEY_VIOLATION_PATTERN = "$$: SYS_PK_";
    private static final String MISSING_COLUMNS_PATTERN = "Attempt to insert null into a non-nullable column: column: ";
    private static final String MISSING_COLUMN_PATTERN_2 = " table: ";
    */

    /**
     * @param pProductVersion       - Of The DataBase
     * @param pTableName            - Of the Schema's Table Name
     * @param pSelectColumns        - Of the Table (comma separated)
     * @param pSelectionWhereClause - null means all
     * @param pOrderBy              - null means order immaterial
     * @param pOffset               - Rows to Skip (>= 0 verified)
     * @param pLimit                - Max Rows to return (>= 1 verified) @return !Null
     */
    @Override
    public String buildQuery( String pProductVersion, String pTableName, //
                              String pSelectColumns, String pSelectionWhereClause, String pOrderBy, //
                              long pOffset, int pLimit )
    {
        return "SELECT" + //
               " LIMIT " + pOffset + " " + pLimit + //
               " " + pSelectColumns + //
               " FROM " + pTableName + //
               " " + pSelectionWhereClause + //
               " " + pOrderBy;
    }

    @Override
    public boolean validateConnection( Connection pConnection, DBinfo pDBinfo )
            throws SQLException
    {
        return LLvalidateConWith( pConnection, //
                                  "SELECT 1 AS Yup FROM INFORMATION_SCHEMA.SYSTEM_USERS" );
    }

    @Override
    public boolean tableExists( Connection pConnection, DBinfo pDBinfo, String pTableName )
            throws SQLException, MultipleTablesException
    {
        return LLtableExists( pConnection, pDBinfo, pTableName, //
                              "SELECT LIMIT 0 1 1 AS Yup FROM " + pTableName );
    }

    @Override
    public boolean columnExists( Connection pConnection, DBinfo pDBinfo, String pTableName, String pColumnName )
            throws SQLException, MultipleTablesException
    {
        return LLcolumnExists( pConnection, pDBinfo, pTableName, pColumnName,  //
                               "SELECT LIMIT 0 1 " + pColumnName + " FROM " + pTableName );
    }

    @SuppressWarnings({"ThrowableInstanceNeverThrown"}) @Override
    public WrappedSQLException createMissingSomethingException( SQLException pException )
    {
        List<String> pColumnNames = Collections.emptyList();
        String message = pException.getMessage();
        int index = message.indexOf( NON_NULLABLE_COLUMN_PATTERN );
        index += NON_NULLABLE_COLUMN_PATTERN.length();
        message = message.substring( index );
        index = message.indexOf( NOT_NULLABLE_COLUMN_PATTERN_2 );
        if ( index > 0 )
        {
            String columnName = message.substring( 0, index );
            pColumnNames = Collections.singletonList( columnName );
        }
        return new MissingRequiredValueFieldException( pException, pColumnNames );
    }

    @SuppressWarnings({"ThrowableInstanceNeverThrown"}) @Override
    protected WrappedSQLException createDuplicateFieldValueException( SQLException pException )
    {
        String message = pException.getMessage();
        int index = message.lastIndexOf( DUPLICATE_FIELD_VALUE_PATTERN );
        String columnList = message.substring( index + DUPLICATE_FIELD_VALUE_PATTERN.length() ).trim();
        String[] zFields = Utils.parseChar( columnList, ',' );
        return new DuplicateValueFieldException( pException, zFields );
    }

    @Override
    protected boolean isMissingSomethingException( SQLException pException )
    {
        return isConstraintViolation( pException ) && pException.getErrorCode() == -10;
    }

    @Override
    protected boolean isDuplicateFieldValueException( SQLException pException )
    {
        return isConstraintViolation( pException ) && pException.getErrorCode() == -9;
    }

    @Override
    protected boolean isDuplicatePKException( SQLException pException )
    {
        return isConstraintViolation( pException ) && pException.getErrorCode() == -104;
    }
}