package pt.evolute.dbtransfer.db.helper;

import java.sql.Blob;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.Map;

import pt.evolute.dbtransfer.db.DBConnection;
import pt.evolute.utils.arrays.Virtual2DArray;
import pt.evolute.utils.string.UnicodeChecker;

public class PostgreSQLServerHelper extends NullHelper
{
	private static final Map<String,String> OUTPUT = new HashMap<String,String>();
	private static final Map<String,String> RESERVED = new HashMap<String,String>();
	private static final Map<String,String> NORMALIZE = new HashMap<String,String>();
	private static final Map<String,String> DEFAULTS = new HashMap<String,String>();
	private static final Map<String,String> NORMALIZE_DEFAULTS = new HashMap<String,String>();
	
	static
	{
		OUTPUT.put( "boolean", "bool" );
		OUTPUT.put( "datetime", "timestamp" );
		OUTPUT.put( "datetimetz", "timestamptz" );
		OUTPUT.put( "blob", "bytea" );
		OUTPUT.put( "longblob", "bytea" );
		OUTPUT.put( "double", "double precision" );
		OUTPUT.put( "longvarchar", "text" );
		OUTPUT.put( "tinyint", "smallint" );
		
		RESERVED.put( "order", "order" );
		RESERVED.put( "user", "user" );
		
		NORMALIZE.put( "int4", "int" );
		NORMALIZE.put( "timestamp", "datetime" );
		NORMALIZE.put( "timestamptz", "datetimetz" );
		NORMALIZE.put( "bool", "boolean" );
		NORMALIZE.put( "bpchar", "char" );
		NORMALIZE.put( "float8", "float" );
		NORMALIZE.put( "bytea", "blob" );
		
		DEFAULTS.put( "'(newsequentialid())'", "" );
		DEFAULTS.put( "'(' ')'", "' '" );
		DEFAULTS.put( "'('S')'", "'S'" );
		DEFAULTS.put( "'('N')'", "'N'" );
		DEFAULTS.put( "(getdate())", "CURRENT_DATE" );
		DEFAULTS.put( "'(CONVERT([varchar](5),getdate(),(108)))'", "CURRENT_TIME" );
		
		NORMALIZE_DEFAULTS.put( "(now())", "CURRENT_TIMESTAMP" );
	}
	
	private static PostgreSQLServerHelper translator = null;
	
	private PostgreSQLServerHelper()
	{
		System.out.println( "PostgreSQL helper - setting double slash on UnicodeChecker" );
		UnicodeChecker.setUseDoubleSlash( true );
	}
	
	public static PostgreSQLServerHelper getTranslator()
	{
		if( translator == null )
		{
			translator = new PostgreSQLServerHelper();
		}
		return translator;
	}
	
	@Override
	public String outputType( String type, Integer size )
	{
		String output = OUTPUT.get( type.toLowerCase() );
		if( output == null )
		{
			output = type;
		}
		if( "text".equalsIgnoreCase( output ) )
		{
			size = null;
		}
		if( size != null && !"uuid".equalsIgnoreCase( output ) )
		{
			output = output + "( " + size + ")";
		}
		
		if( type.contains( "numeric" ) )
		{
			System.out.println( "NUMERIC <" + type + ">" );
			System.exit(0);
		}
		
		return output;
	}
	
	@Override
	public String outputName( String name )
	{
		if( name.contains( " " ) )
		{
			name = name.replace( ' ', '_' );
		}
		if( name.contains( "." ) )
		{
			name = name.replace( '.', '_' );
		}
//		name = StringPlainer.convertString( name );
		if( RESERVED.containsKey( name ) )
		{
			name = "\"" + name + "\"";
		}
		return name;
	}
	
	@Override
	public String normalizedType( String type )
	{
		String normalize = NORMALIZE.get( type );
		if( normalize == null )
		{
			normalize = type;
		}
		return normalize;
	}
	
	@Override
	public void fixSequences( DBConnection con, String table, String typeName, String column )
		throws Exception
	{
		if( "serial".equals(typeName))
		{
			StringBuilder buff = new StringBuilder("SELECT MAX( ");
			buff.append(column);
			buff.append(" ) FROM ");
			buff.append( table );
			Object value = null;
			try
			{
				//				System.out.println( "C: " + buff );
				con.executeQuery( "BEGIN;" );
				Virtual2DArray rs = con.executeQuery(buff.toString());
				value = rs.getObjects()[0][0];
				con.executeQuery( "COMMIT;" );
			}
			catch(SQLException ex)
			{
				if(ex.getMessage().indexOf("ultiple") == -1)
				{
					throw ex;
				}
			}
			if(value != null)
			{
				buff = new StringBuilder("SELECT setval( '");
				buff.append( table );
				buff.append("_");
				buff.append(column);
				buff.append("_seq', ");
				buff.append(value);
				buff.append(" )");
				try
				{
					System.out.println("C: " + buff);
					con.executeQuery( "BEGIN;" );
					con.executeQuery(buff.toString());
					con.executeQuery( "COMMIT;" );
				}
				catch(Exception ex)
				{
					if(ex.getMessage().indexOf("ultiple") == -1)
					{
						throw ex;
					}
				}
			}
		}
	}
	
	@Override
	public String normalizeValue( String value )
	{
		if( value != null )
		{
			if( value.endsWith( "::bpchar" ) )
			{
				value = value.substring( 1, value.length() - "::bpchar".length() - 1 );
			}
			else if( value.endsWith( "::character varying" ) )
			{
				value = value.substring( 1, value.length() - "::character varying".length() - 1 );
			}
			else if( "now()".equals( value ) )
			{
				value = "CURRENT_TIMESTAMP";
			}
			else if( "('now'::text)::date".equals( value ) )
			{
				value = "CURRENT_DATE";
			}
		}
		return value;
	}
	
	@Override
	public void setDefaultValue( DBConnection con, String table, String typeName, String column, String value )
			throws Exception
	{
		if( DEFAULTS.containsKey( value ) )
		{
			value = DEFAULTS.get( value );
		}
		if( value != null && !value.isEmpty() )
		{
			if( "bit".equalsIgnoreCase( typeName ) )
			{
				value = "CAST( " + value + " AS bit )";
			}
			else if( "boolean".equalsIgnoreCase( typeName )
					|| "bool".equalsIgnoreCase( typeName ) )
			{
				value = "CAST( " + value + " AS boolean )";
			}
			try
			{
				super.setDefaultValue( con, table, typeName, column, value );
			}
			catch( Exception ex )
			{
				System.out.println( "type: <" + typeName + "> value: <" + value + ">" ); 
				throw ex;
			}
		}
	}
	
	@Override
	public int translateType( int type ) 
	{
		int pType = type;
		switch ( type ) 
		{
			case Types.NCHAR:
				pType = Types.CHAR;
				break;	
			case Types.NVARCHAR:
				pType = Types.VARCHAR;
				break;
			case Types.LONGNVARCHAR:
				pType = Types.LONGVARCHAR;
			case Types.BOOLEAN:
				pType = Types.BIT;
			default:
				break;
		}
		return pType;
	}
	
        @Override
	public String normalizeDefault( String str )
	{
		String norm = NORMALIZE_DEFAULTS.get( str );
		if( norm == null )
		{
			norm = str;
		}
		return norm;
	}
    
    @Override
    public void setPreparedValue(PreparedStatement pStm, int col, Object o, int type ) 
            throws SQLException
    {
        if( type == Types.BLOB 
        		|| type == Types.LONGVARBINARY 
        		|| type == Types.VARBINARY
        		|| type == Types.BINARY )
        {
        	if( o != null )
        	{
	            o = outputValue( o );
	            
//	            System.out.println( "BLOB class: " + o.getClass() + " Type: " + type + " Col: " + col );
	            if( o instanceof byte[] )
	            {
	                pStm.setBytes( col + 1, ( byte[] )o );
	            }
	            else
	            {
	            	Blob b = ( Blob )o;
	                pStm.setBytes( col + 1, b.getBytes( 1, ( int )b.length() ) );
	            }
        	}
        	else
        	{
        		pStm.setBytes( col + 1, null );
        	}
        }
        else
        {
        	super.setPreparedValue( pStm, col, o, type );
        }
    }
}