// 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.*;

public class SQLProductSpecificHelperPostgreSQL extends SQLProductSpecificAbstractHelper
{
    private static final String NOT_NULL_VIOLATION = "23502";
    private static final String UNIQUE_VIOLATION = "23505";
    private static final String NULL_VALUE_IN_COLUMN_PATTERN = "null value in column \"";
    private static final String DUP_KEY_INDEX_PATTERN = "duplicate key violates unique constraint \"";

    /*
    private static final String INTEGRITY_CONSTRAINT_VIOLATION  = "23000";
    private static final String RESTRICT_VIOLATION  = "23001";
    private static final String FOREIGN_KEY_VIOLATION = "23503";
    private static final String CHECK_VIOLATION = "23514";
    */

    /**
     * @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" + //
               " " + pSelectColumns + //
               " FROM " + pTableName + //
               " " + pSelectionWhereClause + //
               " " + pOrderBy + //
               " LIMIT " + pLimit + //
               " OFFSET " + pOffset;
    }

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

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

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

    @SuppressWarnings({"ThrowableInstanceNeverThrown"}) @Override
    public WrappedSQLException wrapSQLException( SQLException pException )
    {
        //org.postgresql.util.PSQLException: ERROR: null value in column "c_1" violates not-null constraint
        String message = pException.getMessage();
        if ( isConstraintViolation( pException ) )
        {
            if ( isMissingPKException( pException ) )
            {
                return new DuplicatePKException( pException );
            }
            //org.postgresql.util.PSQLException: ERROR: duplicate key violates unique constraint "systemuseridx0"
            String[] zDuplicatedFieldNames = null;
            if ( mIndexColumnsLocator != null )
            {
                String zIndexName = getIndexNameFrom( message );
                if ( zIndexName != null )
                {
                    zDuplicatedFieldNames = mIndexColumnsLocator.getColumnsForIndex( zIndexName );
                }
            }
            return new DuplicateValueFieldException( pException, zDuplicatedFieldNames );
        }

        List<String> columnNames = Collections.emptyList();
        if ( isNotNullConstraintViolation( pException ) )
        {
            int index = message.indexOf( NULL_VALUE_IN_COLUMN_PATTERN );
            int endIndex;
            if ( index > 0 )
            {
                index += NULL_VALUE_IN_COLUMN_PATTERN.length();
                endIndex = message.indexOf( '"', index );
                if ( endIndex > index )
                {
                    String column = message.substring( index, endIndex );
                    columnNames = Collections.singletonList( column );
                }
            }
            return new MissingRequiredValueFieldException( pException, columnNames );
        }

        return new WrappedSQLException( pException );
    }

    private String getIndexNameFrom( String pMessage )
    {
        int zDupKeyAt = pMessage.indexOf( DUP_KEY_INDEX_PATTERN );
        if ( zDupKeyAt != -1 )
        {
            String zIndexNamePlus = pMessage.substring( zDupKeyAt + DUP_KEY_INDEX_PATTERN.length() );
            int zQuoteAt = zIndexNamePlus.indexOf( '\"' );
            if ( zQuoteAt > 0 )
            {
                return zIndexNamePlus.substring( 0, zQuoteAt );
            }
        }
        return null;
    }

    @Override
    protected boolean isConstraintViolation( SQLException pException )
    {
        return UNIQUE_VIOLATION.equals( pException.getSQLState() );
    }

    public boolean isNotNullConstraintViolation( SQLException pException )
    {
        return NOT_NULL_VIOLATION.equals( pException.getSQLState() );
    }

    @Override
    protected boolean isMissingPKException( SQLException pException )
    {
        return pException.getMessage().endsWith( "_pkey\"" );
    }
}