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

import org.litesoft.commonfoundation.base.*;
import org.litesoft.commonfoundation.typeutils.Objects;
import org.litesoft.db.*;
import org.litesoft.logger.*;
import org.litesoft.orsup.base.*;
import org.litesoft.orsup.nonpublic.*;
import org.litesoft.orsup.selection.*;
import org.litesoft.sql.statements.*;

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

public class SQLPersistenceHelper extends AbstractPersistenceHelper
{
    public static final Logger LOGGER = LoggerFactory.getLogger( SQLPersistenceHelper.class );

    private SQLDBconnectionProvider mSQLDBconnectionProvider;

    public SQLPersistenceHelper( SQLDBconnectionProvider pSQLDBconnectionProvider )
    {
        Objects.assertNotNull( "SQLDBconnectionProvider", mSQLDBconnectionProvider = pSQLDBconnectionProvider );
    }

    public SQLDBconnectionProvider getSQLDBconnectionProvider()
    {
        return mSQLDBconnectionProvider;
    }

    private SQLDBconnection createSQLDBconnection()
            throws DBException
    {
        return mSQLDBconnectionProvider.createSQLDBconnection();
    }

    private SQLProductSpecificHelper mHelper;

    public SQLProductSpecificHelper getHelper()
    {
        return mHelper;
    }

    // PersistenceHelper vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv

    @Override
    public synchronized void initializeDB()
    {
        SQLDBconnection zDBC = null;
        try
        {
            zDBC = createSQLDBconnection();
            DBinfo info = zDBC.getDBinfo();
            mHelper = SQLProductSpecificHelperManager.getVendorSpecificHelperFor( info );
            mHelper.initializeDB( zDBC.getConnection(), info, new MyIndexColumnsLocator() );
        }
        catch ( Exception e )
        {
            throw new Error( "Unable to initializeDB", e );
        }
        finally
        {
            if ( zDBC != null )
            {
                zDBC.dispose();
            }
        }
    }

    @Override
    public DBconnection createConnection()
            throws DBException
    {
        return createSQLDBconnection();
    }

    @Override
    protected String LLnormalizeIdentifierNotNullOrEmpty( String pIdentifier )
    {
        return getHelper().normalizeIdentifier( pIdentifier );
    }

    @Override
    protected String[] LLgetTablesEquals( String pName )
    {
        return LLgetTablesFor( SQLHelper.escapePercent( pName ) );
    }

    @Override
    protected String[] LLgetTablesStartsWith( String pStart )
    {
        return LLgetTablesFor( SQLHelper.escapePercent( pStart ) + "%" );
    }

    @Override
    protected String[] LLgetTablesEndsWith( String pEnd )
    {
        return LLgetTablesFor( "%" + SQLHelper.escapePercent( pEnd ) );
    }

    @Override
    protected String[] LLgetTablesStartsAndEndsWith( String pStart, String pEnd )
    {
        return LLgetTablesFor( SQLHelper.escapePercent( pStart ) + "%" + SQLHelper.escapePercent( pEnd ) );
    }

    @Override
    protected String[] LLgetTablesForParts( String[] pParts )
    {
        StringBuilder sb = new StringBuilder();
        sb.append( SQLHelper.escapePercent( pParts[0] ) );
        for ( int i = 1; i < pParts.length; i++ )
        {
            sb.append( "%" ).append( SQLHelper.escapePercent( pParts[i] ) );
        }
        return LLgetTablesFor( sb.toString() );
    }

    protected String[] LLgetTablesFor( String pPattern )
    {
        String zPattern = SQLHelper.escapeUnderscore( pPattern );
        SQLDBconnection zDBC = createSQLDBconnection();
        try
        {
            return getHelper().getTablesFor( zDBC.getConnection(), zDBC.getDBinfo(), zPattern );
        }
        catch ( SQLException e )
        {
            throw new WrappedSQLException( e );
        }
        finally
        {
            zDBC.dispose();
        }
    }

    @Override
    protected Map<String, DBtableColumn[]> LLgetColumns( String pTableName )
            throws DBException
    {
        String zTableName = SQLHelper.escapeTableNamePattern( pTableName );
        SQLDBconnection zDBC = createSQLDBconnection();
        try
        {
            return getHelper().getColumns( zDBC.getConnection(), zDBC.getDBinfo(), zTableName );
        }
        catch ( SQLException e )
        {
            throw new WrappedSQLException( e );
        }
        finally
        {
            zDBC.dispose();
        }
    }

    @Override
    protected Map<String, DBtableIndex[]> LLgetIndexes( String pTableName )
            throws DBException
    {
        SQLDBconnection zDBC = createSQLDBconnection();
        try
        {
            return getHelper().getIndexes( zDBC.getConnection(), zDBC.getDBinfo(), pTableName );
        }
        catch ( SQLException e )
        {
            throw new WrappedSQLException( e );
        }
        finally
        {
            zDBC.dispose();
        }
    }

    /**
     * @return TRUE if table exists,
     *         FALSE if table appears to not exist
     */
    @Override
    public boolean tableExists( SimpleFromIdentifier pSimpleFromIdentifier )
            throws DBException, MultipleTablesException
    {
        String zTableName = getHelper().normalizeIdentifier( pSimpleFromIdentifier.getTableName() );

        try
        {
            return LLtableExists( zTableName );
        }
        catch ( SQLException e )
        {
            throw new WrappedSQLException( e );
        }
    }

    /**
     * @return TRUE if table exists,
     *         FALSE if table appears to not exist
     */
    @Override
    public boolean columnExists( SimpleFromIdentifier pSimpleFromIdentifier, String pColumnName )
            throws DBException, MultipleTablesException
    {
        String zTableName = getHelper().normalizeIdentifier( pSimpleFromIdentifier.getTableName() );

        SQLDBconnection dbc = createSQLDBconnection();
        try
        {
            return getHelper().columnExists( dbc.getConnection(), dbc.getDBinfo(), zTableName, pColumnName );
        }
        catch ( SQLException e )
        {
            throw new WrappedSQLException( e );
        }
        finally
        {
            dbc.dispose();
        }
    }

    @Override
    public long rawCount( SimpleFromIdentifier pSimpleFromIdentifier, WhereClause pSelectionWhereClause )
            throws DBException
    {
        String zTableName = getHelper().normalizeIdentifier( pSimpleFromIdentifier.getTableName() );

        return rawCount( zTableName, SQL_OR_Helper.toSQL( pSelectionWhereClause ) );
    }

    @Override
    public void deleteRows( SimpleFromIdentifier pSimpleFromIdentifier, WhereClause pSelectionWhereClause )
            throws DBException
    {
        deleteRows( null, pSimpleFromIdentifier, pSelectionWhereClause );
    }

    @Override
    public void deleteRows( DBconnection pDBconnection, SimpleFromIdentifier pSimpleFromIdentifier, WhereClause pSelectionWhereClause )
            throws DBException
    {

        LLdeleteRows( pDBconnection, createDeleteRowsCmd( pSimpleFromIdentifier, pSelectionWhereClause ) );
    }

    /**
     * @param pSimpleFromIdentifier - Normally the Table Identifier
     * @param pKeyCD                - The Unique Key Column of the table
     * @param pSnagCD               - The Snag Column of the table
     * @param pUniqueSnagValue      - The Unique Value for the Snag Column, that if set will be unique in the table
     * @param pSelectionWhereClause - null means all, which will only succeed if the table only has one entry
     * @param pOrderBy              - null means order immaterial
     * @param pUpdateValuePairs     - additional columns to set
     *
     * @return true if row 'snaged'
     *
     * @throws DBException - Exception regarding the OR layer
     */
    @Override
    public boolean snagOne( SimpleFromIdentifier pSimpleFromIdentifier, SimpleColumnDefinition pKeyCD, SimpleColumnDefinition pSnagCD, Long pUniqueSnagValue, WhereClause pSelectionWhereClause, OrderBy pOrderBy, ColumnUpdateValuePair[] pUpdateValuePairs )
            throws DBException
    {
        String zTableName = getHelper().normalizeIdentifier( pSimpleFromIdentifier.getTableName() );

        String zSelectColumns = pKeyCD.getColumnName();

        WhereClause zNullSnagColumn = WhereClauseFactory.INSTANCE.isNull( pSnagCD );

        DBconnection zDBconnection = createConnection();
        try
        {
            String wc = (pSelectionWhereClause == null) ? zNullSnagColumn.toSQL() : WhereClauseFactory.INSTANCE.and( zNullSnagColumn, pSelectionWhereClause ).toSQL();
            String orderBy = (pOrderBy != null) ? pOrderBy.toSQL() : new OrderBy( pKeyCD ).toSQL();
            String zSubSelect = getHelper().buildQuery( zDBconnection.getDBinfo().getProductVersion(), //
                                                        zTableName, zSelectColumns, wc, orderBy, 0, 1 );

            StringBuilder zUpdateClause = new StringBuilder();
            zUpdateClause.append( pSnagCD.getColumnName() ).append( '=' );
            WhereClauseColumnSupport.makeSqlValue( zUpdateClause, pSnagCD, false, pUniqueSnagValue );
            for ( ColumnUpdateValuePair zPair : pUpdateValuePairs )
            {
                if ( zPair != null )
                {
                    SimpleColumnDefinition zSCD = zPair.getColumnDefinition();
                    zUpdateClause.append( ", " ).append( zSCD.getColumnName() ).append( '=' );
                    WhereClauseColumnSupport.makeSqlValue( zUpdateClause, zSCD, false, zPair.getValue() );
                }
            }

            StringBuilder zWC = new StringBuilder( "WHERE " );
            zWC.append( pKeyCD.getColumnName() ).append( " in (" ).append( zSubSelect ).append( ')' );

            String cmd = createUpdateCmd( zTableName, zUpdateClause.toString(), zWC.toString() );
            int rowsEffected = update( zDBconnection, cmd, true );
            if ( rowsEffected > 1 )
            {
                throw new MultiRowUpdateException( cmd, rowsEffected );
            }
            return rowsEffected == 1;
        }
        finally
        {
            zDBconnection.dispose();
        }
        //    create table SnagTest
        //    (
        //      id bigint NOT NULL,
        //      recordversion bigint NOT NULL,
        //      claimedby bigint,
        //      zsnaguniquecolumn bigint,
        //      description character varying,
        //      started boolean NOT NULL,
        //      CONSTRAINT SnagTest_pk PRIMARY KEY (id)
        //    );
        //
        //    UPDATE SnagTest SET zsnaguniquecolumn=22 where id in (
        //    select id from SnagTest where zsnaguniquecolumn is null order by id limit 1 );
    }

    @Override
    public void deleteAllRows( SimpleFromIdentifier pSimpleFromIdentifier )
            throws DBException
    {
        deleteAllRows( null, pSimpleFromIdentifier );
    }

    @Override
    public void deleteAllRows( DBconnection pDBconnection, SimpleFromIdentifier pSimpleFromIdentifier )
            throws DBException
    {
        String zTableName = getHelper().normalizeIdentifier( pSimpleFromIdentifier.getTableName() );

        LLdeleteRows( pDBconnection, "DELETE FROM " + zTableName + " WHERE 1=1" );
    }

    @Override
    public void dropTable( TableManager pTableManager, SimpleFromIdentifier pSimpleFromIdentifier )
            throws DBException
    {
        String zTableName = getHelper().normalizeIdentifier( pSimpleFromIdentifier.getTableName() );

        String cmd = "DROP TABLE " + zTableName;
        try
        {
            executeNoResultsNoCommitSQL( cmd );
            pTableManager.removeTable( zTableName );
            return;
        }
        catch ( DBException e )
        {
            try
            {
                if ( LLtableExists( zTableName ) )
                {
                    LOGGER.error.log( e, "Failed to drop table (HSQLDB has no 'IF EXISTS' clause in 'DROP TABLE': ", zTableName );
                    throw e; // table exists, so original 'DROP' problem should be reported
                }
            }
            catch ( SQLException e1 )
            {
                throw e; // unable to ask if table exists, so original 'DROP' problem should be reported
            }
        }
        // Here means that the table 'probably' does NOT exist
        pTableManager.removeTable( zTableName );
    }

    @Override
    public void addColumnToExistingTable( SimpleFromIdentifier pSimpleFromIdentifier, SimpleColumnDefinitionExtended pSCD )
            throws DBException
    {
        String zTableName = getHelper().normalizeIdentifier( pSimpleFromIdentifier.getTableName() );

        StringBuilder cmd = new StringBuilder();
        cmd.append( "ALTER TABLE " ).append( zTableName ).append( " ADD ( " );

        createTableColumn( cmd, pSCD );
        String zCmd = cmd.append( " )" ).toString();
        executeNoResultsNoCommitSQL( zCmd );
    }

    // PersistenceHelper ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

    protected void createTableColumn( StringBuilder pCmd, SimpleColumnDefinitionExtended pSCD )
    {
        createTableColumn( pCmd, pSCD.getColumnName(), pSCD );

        if ( pSCD.hasSearchColumn() )
        {
            pCmd.append( ", " );
            createTableColumn( pCmd, pSCD.getSearchColumnName(), pSCD );
        }
    }

    private void createTableColumn( StringBuilder pCmd, String pColumnName, SimpleColumnDefinitionExtended pSCD )
    {
        pCmd.append( pColumnName );
        pCmd.append( ' ' );
        getHelper().addColumnType( pCmd, pSCD );
        if ( pSCD.isRequired() )
        {
            pCmd.append( " NOT NULL" );
        }
    }

    private void LLdeleteRows( DBconnection pDBconnection, String pCmd )
    {
        if ( pDBconnection == null )
        {
            new JDBCexecuteNoResults( this, pCmd, this ).executeSQLnoResults( true );
        }
        else
        {
            JDBCexecuteUpdate updater = new JDBCexecuteUpdate( this, pDBconnection, pCmd, this );
            try
            {
                updater.executeUpdate( false );
            }
            finally
            {
                updater.dispose();
            }
        }
    }

    protected int update( DBconnection pDBconnection, String pCmd, boolean pCommit )
            throws DBException
    {

        JDBCexecuteUpdate updater = new JDBCexecuteUpdate( this, pDBconnection, pCmd, this );
        try
        {
            return updater.executeUpdate( pCommit );
        }
        finally
        {
            updater.dispose();
        }
    }

    protected String createUpdateCmd( String pTableName, String pUpdateClause, String pWhereClause )
    {
        return "UPDATE " + pTableName + " SET " + pUpdateClause + " " + pWhereClause;
    }

    private String createDeleteRowsCmd( SimpleFromIdentifier pSimpleFromIdentifier, WhereClause pSelectionWhereClause )
    {
        String zTableName = getHelper().normalizeIdentifier( pSimpleFromIdentifier.getTableName() );

        String zCmd = "DELETE FROM " + zTableName;
        if ( pSelectionWhereClause != null )
        {
            zCmd += " " + pSelectionWhereClause.toSQL();
        }
        return zCmd;
    }

    protected long rawCount( String pTableName, String pWhereClause )
            throws DBException
    {
        // todo: switch the '*' to the primary key!
        JDBCexecuteQuery query = new JDBCexecuteQuery( this, "SELECT COUNT(*) FROM " + pTableName + " " + pWhereClause, this ).executeQuery();
        try
        {
            if ( !query.hasNext() )
            {
                throw new SQLException( "No Results returned" );
            }
            long count = query.next().getLong( 1 );
            if ( query.hasNext() )
            {
                throw new SQLException( "Too many result rows, expected only 1" );
            }
            return count;
        }
        catch ( SQLException e )
        {
            throw query.error( e );
        }
        finally
        {
            query.dispose();
        }
    }

    private boolean LLtableExists( String pTableName )
            throws SQLException, MultipleTablesException
    {
        SQLDBconnection zDBC = createSQLDBconnection();
        try
        {
            return getHelper().tableExists( zDBC.getConnection(), zDBC.getDBinfo(), pTableName );
        }
        finally
        {
            zDBC.dispose();
        }
    }

    protected void executeNoResultsNoCommitSQL( String pCmd )
    {
        new JDBCexecuteNoResults( this, pCmd, this ).executeSQLnoResults( false );
    }

    protected static class MyRowSource implements PersistenceHelperRowSource
    {
        private JDBCexecuteQuery mQuery;
        private int mResultSetColumnCount = -1;

        public MyRowSource( SQLPersistenceHelper pPersistenceHelper, String pCmd, DBconnection pDBconnection )
        {
            mQuery = new JDBCexecuteQuery( pPersistenceHelper, pCmd, this );
            try
            {
                mQuery.assumeOwnershipOf( pDBconnection );
            }
            catch ( RuntimeException e )
            {
                dispose();
                throw e;
            }
        }

        @Override
        public PersistenceHelperRowSource prepareForGetNextRow()
        {
            if ( mResultSetColumnCount != -1 )
            {
                throw new IllegalStateException( "Multiple calls to prepareForGetNextRow()" );
            }
            try
            {
                mQuery.executeQuery();
                mResultSetColumnCount = mQuery.getResultSetColumnCount();
                return this;
            }
            catch ( RuntimeException e )
            {
                dispose();
                throw e;
            }
        }

        @Override
        public Object[] getNextRow()
        {
            if ( mQuery == null )
            {
                return null;
            }
            try
            {
                if ( !mQuery.hasNext() )
                {
                    dispose();
                    return null;
                }
                Object[] row = new Object[mResultSetColumnCount];
                try
                {
                    ResultSet rs = mQuery.next();
                    for ( int i = 0; i < row.length; i++ )
                    {
                        row[i] = rs.getObject( i + 1 );
                    }
                }
                catch ( SQLException e )
                {
                    dispose();
                    throw new WrappedSQLException( e );
                }
                return row;
            }
            catch ( RuntimeException e )
            {
                dispose();
                throw e;
            }
        }

        @Override
        public void dispose()
        {
            if ( mQuery != null )
            {
                mQuery.dispose();
                mQuery = null;
            }
        }

        @Override
        protected void finalize()
                throws Throwable
        {
            dispose();
            super.finalize();
        }
    }

    //private boolean indexDefinitionIsUpToDate( MetaDataForPO pMetaDataForPO, MetaDataForPO.Index pIndex )
    //        throws ORMapperException
    //{
    //    List<String> metadataColumns = Arrays.asList( pIndex.getColumns() );
    //    Collections.sort( metadataColumns );
    //    List<String> existingColumns = getIndexColumns( pMetaDataForPO.getTableName(), pIndex.getIndexName() );
    //    Collections.sort( existingColumns );
    //    return existingColumns.equals( metadataColumns );
    //}

    private static class MyIndexColumnsLocator implements SQLIndexColumnsLocator
    {
        @Override
        public String[] getColumnsForIndex( String pIndexName )
        {
            for ( MetaDataForPO zMD : DataStoreLocator.get().getMetaDataStore().getAllMetaDataForPOs() )
            {
                for ( MetaDataForPO.Index zIndex : zMD.getIndexes() )
                {
                    if ( pIndexName.equalsIgnoreCase( zIndex.getIndexName() ) )
                    {
                        List<String> rv = new ArrayList<String>();
                        for ( String zColumn : zIndex.getColumns() )
                        {
                            if ( zColumn.endsWith( "_id" ) )
                            {
                                zColumn = zColumn.substring( 0, zColumn.length() - 3 );
                            }
                            rv.add( DeCamelizer.resolve( zColumn ) );
                        }
                        return rv.toArray( new String[rv.size()] );
                    }
                }
            }
            return null;
        }
    }
}