dropProcedureIfExists

import net.futureset.kontroldb.dsl.addColumnsTo
import net.futureset.kontroldb.dsl.applyDsvToTable
import net.futureset.kontroldb.dsl.createProcedure
import net.futureset.kontroldb.dsl.createRole
import net.futureset.kontroldb.dsl.createSequence
import net.futureset.kontroldb.dsl.createTable
import net.futureset.kontroldb.dsl.dropColumnsFrom
import net.futureset.kontroldb.dsl.dropIndexIfExists
import net.futureset.kontroldb.dsl.dropProcedureIfExists
import net.futureset.kontroldb.dsl.dropRole
import net.futureset.kontroldb.dsl.dropSequenceIfExists
import net.futureset.kontroldb.dsl.dropTable
import net.futureset.kontroldb.dsl.dropTableIfExists
import net.futureset.kontroldb.dsl.grantPermissions
import net.futureset.kontroldb.model.DbObjectType
import net.futureset.kontroldb.model.StandardColumnTypes
import net.futureset.kontroldb.model.StandardColumnTypes.BOOLEAN
import net.futureset.kontroldb.model.StandardColumnTypes.DATE
import net.futureset.kontroldb.model.StandardColumnTypes.Decimal
import net.futureset.kontroldb.model.StandardColumnTypes.INT16
import net.futureset.kontroldb.model.StandardColumnTypes.INT32
import net.futureset.kontroldb.model.StandardColumnTypes.INT64
import net.futureset.kontroldb.model.StandardColumnTypes.LOCALDATETIME
import net.futureset.kontroldb.model.StandardColumnTypes.Varchar
import net.futureset.kontroldb.refactoring.Refactoring

fun main() { 
   //sampleStart 
   class CreateAProcedure :
    Refactoring(
        executionOrder {
            ymd(2023, 11, 30)
            author("ben")
        },
        forward =
        changes {
            createProcedure("NEW_CUSTOMER") {
                body(
                    """
                        CREATE PROCEDURE NEW_CUSTOMER
                            @firstname VARCHAR(50),
                            @lastname VARCHAR(50),
                            @address VARCHAR(100) AS
                        BEGIN
                        INSERT INTO CUSTOMER(CUST_ID,FIRSTNAME,LASTNAME,ADDRESS,CITY,STATE,ZIP)
                                        VALUES (1, @firstname, @lastname, @address, 'LDN', 'NY', '123');
                        END
                    """.trimIndent(),
                )
                wholeDefinition(true)
            }.onlyIfDatabase { it == "sqlserver" }
            createProcedure("NEW_CUSTOMER") {
                body(
                    """
                        CREATE PROCEDURE NEW_CUSTOMER(IN firstname VARCHAR (50), IN lastname VARCHAR (50), IN address VARCHAR (100))
                        MODIFIES SQL DATA
                        BEGIN ATOMIC
                        INSERT INTO CUSTOMER(CUST_ID,FIRSTNAME,LASTNAME,ADDRESS,CITY,STATE,ZIP)
                                VALUES (1, firstname, lastname, address, 'LDN', 'NY', '123');
                        END
                    """.trimIndent(),
                )
                wholeDefinition(true)
            }.onlyIfDatabase { it == "hsqldb" }
            createProcedure("NEW_CUSTOMER") {
                body(
                    """
                        CREATE PROCEDURE "NEW_CUSTOMER"(
                            firstname VARCHAR (50),
                            lastname VARCHAR (50),
                            address VARCHAR (100))
                            LANGUAGE SQL AS $$
                        INSERT INTO "CUSTOMER"("CUST_ID", "FIRSTNAME", "LASTNAME", "ADDRESS", "CITY", "STATE", "ZIP")
                        VALUES (1, firstname, lastname, address, 'LDN', 'NY', '123');
                        $$
                    """.trimIndent(),
                )
                wholeDefinition(true)
            }.onlyIfDatabase { it == "postgres" }
        },
        rollback =
        changes {
            dropProcedureIfExists("NEW_CUSTOMER")
        },
    ) 
   //sampleEnd
}