import java.sql.Connection
import java.sql.ResultSet;
import java.sql.SQLException
import java.sql.Statement;

import javax.servlet.ServletContext

import org.apache.commons.logging.Log;
import org.codehaus.groovy.grails.commons.ApplicationAttributes
import org.codehaus.groovy.grails.commons.ApplicationHolder
import org.codehaus.groovy.grails.commons.GrailsDomainClass
import org.hibernate.Session
import org.hibernate.SessionFactory
import org.hibernate.jdbc.Work
import org.springframework.context.ApplicationContext

/**
 * @author Reiner Saddey - this code donated to public domain
 * @see <a href="http://blog.saddey.net/2011/12/15/grails-and-microsoft-sql-server-not-so-painless-when-using-unique-constraints-a-pragmatic-work-around/">Grails and Microsoft SQL Server - Not so painless when using Unique Constraints - A Pragmatic Work-Around</a>
 */
class BootStrap {

    def init = { ServletContext servletContext ->

        // BootStrap.groovy is NOT auto-wired by Grails...
        final ApplicationContext ctx = servletContext.getAttribute(ApplicationAttributes.APPLICATION_CONTEXT)
        final SessionFactory sessionFactory = ctx.sessionFactory

        // Build Grails table names [table_one][table_two]... to avoid touching non-Grails table constraints
        final String grailsTables = ApplicationHolder.application.domainClasses.inject(new StringBuilder()) { StringBuilder sb, GrailsDomainClass item ->
            sb << "[" << sessionFactory.getClassMetadata(item.clazz.name).tableName << "]" }.toString()

        // The hard work is done within Transact-SQL
        final String sql = """\
BEGIN
    -- Avoid changing any of those as it might choke Hibernate
    -- SET ANSI_NULLS ON
    -- SET QUOTED_IDENTIFIER ON
    -- SET NOCOUNT ON

    DECLARE @grailsTables AS NVARCHAR(MAX)
    SET @grailsTables = N'""" + grailsTables + """'

    DECLARE @constraintName AS sysname, @tableName AS sysname, @columnName AS sysname, @isNullable AS NVARCHAR(255)
    DECLARE @recentConstraintName AS sysname, @recentTableName AS sysname, @columns AS NVARCHAR(MAX), @whereFragment AS NVARCHAR(MAX)
    DECLARE @cmdString AS NVARCHAR(MAX)

    DECLARE constraintInfo CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY -- TYPE_WARNING 
        FOR
            SELECT TC.CONSTRAINT_NAME, TC.TABLE_NAME, KC.COLUMN_NAME, CS.IS_NULLABLE 
            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC 
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KC ON KC.TABLE_SCHEMA = TC.TABLE_SCHEMA AND KC.TABLE_CATALOG = TC.TABLE_CATALOG AND KC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
            INNER JOIN INFORMATION_SCHEMA.COLUMNS CS ON CS.TABLE_CATALOG = KC.TABLE_CATALOG AND CS.TABLE_SCHEMA = KC.TABLE_SCHEMA AND CS.TABLE_NAME = KC.TABLE_NAME AND CS.COLUMN_NAME = KC.COLUMN_NAME 
            WHERE TC.CONSTRAINT_TYPE = N'UNIQUE'
            ORDER BY TC.TABLE_NAME, TC.CONSTRAINT_NAME, KC.ORDINAL_POSITION

    OPEN constraintInfo 
    FETCH NEXT FROM constraintInfo
    INTO @constraintName, @tableName, @columnName, @isNullable

    SET @recentConstraintName = N''

    WHILE @@FETCH_STATUS <> -1 BEGIN
    
        -- PRINT @constraintName + N', ' + @tableName + N', ' + @columnName + N', ' + @isNullable

        IF @recentConstraintName <> @constraintName BEGIN
            IF @recentConstraintName <> N'' BEGIN
                IF @whereFragment = N''
                    SELECT N'Preserving constraint ' + @recentConstraintName + N' on table ' + @recentTableName + N' as it does not contain any nullable columns.'
                ELSE BEGIN
                    IF CHARINDEX( (N'[' + @recentTableName + N']') COLLATE Latin1_General_CI_AI, @grailsTables COLLATE Latin1_General_CI_AI) = 0
                        SELECT N'Preserving constraint ' + @recentConstraintName + N' on table ' + @recentTableName + N' as it does not appear to refer to a Grails table.'
                    ELSE BEGIN
                        SET @cmdString = N'ALTER TABLE [' + @recentTableName + '] DROP CONSTRAINT [' + @recentConstraintName + N']'
                        SELECT @cmdString
                        EXECUTE (@cmdString)
                        SET @cmdString = N'CREATE UNIQUE NONCLUSTERED INDEX [' + @recentConstraintName + N'] on [' + @recentTableName + N'] (' + @columns + N') ' + @whereFragment
                        SELECT @cmdString
                        EXECUTE (@cmdString)
                        END
                    END
                END         
            
            SET @recentConstraintName = @constraintName
            SET @recentTableName = @tableName
            SET @columns = N''
            SET @whereFragment = N''
            END
        
        IF @columns <> N''
            SET @columns = @columns + N','          
        SET @columns = @columns + N'[' + @columnName + N']'
        
        IF @isNullable = N'YES' BEGIN
            IF @whereFragment = N''
                SET @whereFragment = N'WHERE '
            ELSE
                SET @whereFragment = N'AND ' 
            SET @whereFragment = @whereFragment + N'[' + @columnName + N'] IS NOT NULL'
            END

        FETCH NEXT FROM constraintInfo
        INTO @constraintName, @tableName, @columnName, @isNullable

        END

    IF @recentConstraintName <> N'' BEGIN
        IF @whereFragment = N''
            PRINT N'Preserving constraint ' + @recentConstraintName + N' on table ' + @recentTableName + N' as it does not contain any nullable columns.'
        ELSE BEGIN
            IF CHARINDEX( (N'[' + @recentTableName + N']') COLLATE Latin1_General_CI_AI, @grailsTables COLLATE Latin1_General_CI_AI) = 0
                PRINT N'Preserving constraint ' + @recentConstraintName + N' on table ' + @recentTableName + N' as it does not appear to refer to a Grails table.'
            ELSE BEGIN
                SET @cmdString = N'ALTER TABLE [' + @recentTableName + '] DROP CONSTRAINT [' + @recentConstraintName + N']'
                PRINT @cmdString
                EXECUTE (@cmdString)
                SET @cmdString = N'CREATE UNIQUE NONCLUSTERED INDEX [' + @recentConstraintName + N'] on [' + @recentTableName + N'] (' + @columns + N') ' + @whereFragment
                PRINT @cmdString
                EXECUTE (@cmdString)
                END
            END
        END         

    CLOSE constraintInfo
    DEALLOCATE constraintInfo

END
"""

        final Session session = sessionFactory.openSession()
        try {
            session.doWork new Work() {
                        @Override
                        public void execute(Connection conn) throws SQLException {
                            final Statement statement = conn.createStatement();
                            try {
                                boolean isResultSet = statement.execute(sql)
                                while(isResultSet) {
                                    final ResultSet resultSet = statement.getResultSet()
                                    while (resultSet.next()) {
                                        getLog().info(resultSet.getString(1i))
                                    }
                                    while (!(isResultSet = statement.getMoreResults()) && statement.getUpdateCount() != -1) {
                                        // Rien
                                    }
                                }
                            } finally { statement.close() }
                        }
                    }
        } catch (Throwable t) {
            getLog().warn("Error executing $sql: $t", t)
        } finally { session.close() }

    }

    def destroy = {
    }
}