package com.aws.rest

import kotlinx.coroutines.runBlocking
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.boot.autoconfigure.SpringBootApplication
import org.springframework.boot.runApplication
import org.springframework.http.HttpStatus
import org.springframework.web.bind.annotation.CrossOrigin
import org.springframework.web.bind.annotation.GetMapping
import org.springframework.web.bind.annotation.PathVariable
import org.springframework.web.bind.annotation.PostMapping
import org.springframework.web.bind.annotation.PutMapping
import org.springframework.web.bind.annotation.RequestBody
import org.springframework.web.bind.annotation.RequestParam
import org.springframework.web.bind.annotation.ResponseStatus
import org.springframework.web.bind.annotation.RestController
import java.io.IOException

@SpringBootApplication
open class App

fun main(args: Array<String>) {
    runApplication<App>(*args)
}

@CrossOrigin(origins = ["*"])
@RestController
class MessageResource {

    @Autowired
    private lateinit var redshiftService: RedshiftService

    @Autowired
    private lateinit var sendMsg: SendMessage

    // Add a new item.
    @PostMapping("api/items")
    fun addItems(@RequestBody payLoad: Map<String, Any>): String = runBlocking {
        val nameVal = "user"
        val guideVal = payLoad.get("guide").toString()
        val descriptionVal = payLoad.get("description").toString()
        val statusVal = payLoad.get("status").toString()

        // Create a Work Item object.
        val myWork = WorkItem()
        myWork.guide = guideVal
        myWork.description = descriptionVal
        myWork.status = statusVal
        myWork.name = nameVal
        val id = redshiftService.injestNewSubmission(myWork)
        return@runBlocking "Item $id added successfully!"
    }

    // Retrieve items.
    @GetMapping("api/items")
    fun getItems(@RequestParam(required = false) archived: String?): MutableList<WorkItem> = runBlocking {
        val list: MutableList<WorkItem>
        if (archived != null) {
            list = redshiftService.getData(archived)
        } else {
            list = redshiftService.getData("")
        }
        return@runBlocking list
    }

    // Flip an item from Active to Archive.
    @PutMapping("api/items/{id}:archive")
    @ResponseStatus(value = HttpStatus.NO_CONTENT)
    fun modUser(@PathVariable id: String) = runBlocking {
        redshiftService.flipItemArchive(id)
        return@runBlocking
    }

    // Send a report through Amazon SES.
    @PostMapping("api/items:report")
    @ResponseStatus(value = HttpStatus.NO_CONTENT)
    fun sendReport(@RequestBody body: Map<String, String>) = runBlocking {
        val email = body.get("email")
        val xml = redshiftService.getDataXML()
        try {
            if (email != null && xml != null) {
                sendMsg.send(email, xml)
            }
        } catch (e: IOException) {
            e.stackTrace
        }
        return@runBlocking
    }
}

// Copyright Amazon.com, Inc. or its affiliates. All Rights Reserved.
// SPDX-License-Identifier: Apache-2.0

package com.aws.rest

import aws.sdk.kotlin.services.redshiftdata.RedshiftDataClient
import aws.sdk.kotlin.services.redshiftdata.model.DescribeStatementRequest
import aws.sdk.kotlin.services.redshiftdata.model.ExecuteStatementRequest
import aws.sdk.kotlin.services.redshiftdata.model.Field
import aws.sdk.kotlin.services.redshiftdata.model.GetStatementResultRequest
import aws.sdk.kotlin.services.redshiftdata.model.SqlParameter
import kotlinx.coroutines.delay
import org.springframework.stereotype.Component
import org.w3c.dom.Document
import java.io.StringWriter
import java.sql.Date
import java.text.SimpleDateFormat
import java.time.LocalDateTime
import java.time.format.DateTimeFormatter
import java.util.UUID
import javax.xml.parsers.DocumentBuilderFactory
import javax.xml.parsers.ParserConfigurationException
import javax.xml.transform.TransformerException
import javax.xml.transform.TransformerFactory
import javax.xml.transform.dom.DOMSource
import javax.xml.transform.stream.StreamResult
import kotlin.collections.ArrayList

@Component
class RedshiftService {
    // Update these values to reflect your environment.
    private val databaseVal = "dev"
    private val dbUserVal = "awsuser"
    private val clusterId = "redshift-cluster-1"

    fun param(nameVal: String, valueVal: Boolean): SqlParameter {
        val myPar = SqlParameter {
            name = nameVal
            value = valueVal.toString()
        }
        return myPar
    }

    fun param2(nameVal: String, valueVal: String): SqlParameter {
        val myPar = SqlParameter {
            name = nameVal
            value = valueVal
        }
        return myPar
    }

    // Inject a new submission.
    suspend fun injestNewSubmission(item: WorkItem): String {
        val name = item.name
        val guide = item.guide
        val description = item.description
        val status = item.status
        val arc = "0"

        // Generate the work item ID.
        val uuid = UUID.randomUUID()
        val workId = uuid.toString()

        // Date conversion.
        SimpleDateFormat("yyyy-MM-dd")
        val dtf = DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss")
        val now = LocalDateTime.now()
        val sDate1 = dtf.format(now)
        val date1 = SimpleDateFormat("yyyy/MM/dd").parse(sDate1)
        val sqlDate = Date(date1.time)

        // Inject an item into the database.
        val sqlStatement =
            "INSERT INTO work (idwork, username, date, description, guide, status, archive) VALUES" +
                "(:idwork, :username, :date, :description, :guide, :status, :arch);"

        val parametersVal = listOf(
            param2("arch", arc),
            param2("username", name.toString()),
            param2("status", status.toString()),
            param2("date", sqlDate.toString()),
            param2("description", description.toString()),
            param2("guide", guide.toString()),
            param2("idwork", workId)
        )

        val statementRequest = ExecuteStatementRequest {
            clusterIdentifier = clusterId
            database = databaseVal
            dbUser = dbUserVal
            sql = sqlStatement
            parameters = parametersVal
        }

        RedshiftDataClient { region = "us-west-2" }.use { redshiftDataClient ->
            redshiftDataClient.executeStatement(statementRequest)
            return workId
        }
    }

    // Return items from the work table.
    suspend fun getData(status: String): MutableList<WorkItem> {
        val isArc: Boolean
        val statementRequest: ExecuteStatementRequest
        val sqlStatement: String
        if (status.compareTo("true") == 0) {
            sqlStatement = "SELECT idwork, date, description, guide, status, username, archive FROM work WHERE archive = :arch ;"
            isArc = true
            val parametersVal = listOf(param("arch", isArc))
            statementRequest = ExecuteStatementRequest {
                this.clusterIdentifier = clusterId
                this.database = databaseVal
                this.dbUser = dbUserVal
                this.parameters = parametersVal
                sql = sqlStatement
            }
        } else if (status.compareTo("false") == 0) {
            sqlStatement = "SELECT idwork, date, description, guide, status, username, archive FROM work WHERE archive = :arch ;"
            isArc = false
            val parametersVal = listOf(param("arch", isArc))
            statementRequest = ExecuteStatementRequest {
                this.clusterIdentifier = clusterId
                this.database = databaseVal
                this.dbUser = dbUserVal
                this.parameters = parametersVal
                sql = sqlStatement
            }
        } else {
            sqlStatement = "SELECT idwork, date, description, guide, status, username, archive FROM work ;"
            statementRequest = ExecuteStatementRequest {
                this.clusterIdentifier = clusterId
                this.database = databaseVal
                this.dbUser = dbUserVal
                sql = sqlStatement
            }
        }

        val id = performSQLStatement(statementRequest)
        println("The identifier of the statement is $id")
        checkStatement(id)
        return getResults(id)
    }

    // Return items from the work table used for an email report.
    suspend fun getDataXML(): String? {
        val sqlStatement = "SELECT idwork, date, description, guide, status, username, archive " +
            "FROM work WHERE archive = :arch ;"
        val isArc = false
        val parametersVal = listOf(param("arch", isArc))
        val statementRequest = ExecuteStatementRequest {
            this.clusterIdentifier = clusterId
            this.database = databaseVal
            this.dbUser = dbUserVal
            this.parameters = parametersVal
            sql = sqlStatement
        }
        val id = performSQLStatement(statementRequest)
        println("The identifier of the statement is $id")
        checkStatement(id)
        return getResultsXML(id)
    }

    // Returns items from the Amazon Redshift table using the redshiftDataClient.
    suspend fun getResults(statementId: String?): MutableList<WorkItem> {
        val resultRequest = GetStatementResultRequest {
            id = statementId
        }

        return RedshiftDataClient { region = "us-west-2" }.use { redshiftDataClient ->
            val response = redshiftDataClient.getStatementResult(resultRequest)
            response.records.map { fields ->
                WorkItem().apply {
                    fields.forEachIndexed { index, field ->
                        val value = parseValue(field)
                        when (index) {
                            0 -> id = value
                            1 -> date = value
                            2 -> description = value
                            3 -> guide = value
                            4 -> status = value
                            5 -> name = value
                            6 -> archived = parseBooleanValue(field) != "false"
                        }
                    }
                }
            }.toMutableList() ?: mutableListOf()
        }
    }

    // Returns open items within XML.
    suspend fun getResultsXML(statementId: String?): String? {
        val resultRequest = GetStatementResultRequest {
            id = statementId
        }

        return RedshiftDataClient { region = "us-west-2" }.use { redshiftDataClient ->
            val response = redshiftDataClient.getStatementResult(resultRequest)
            response.records?.map { list ->
                WorkItem().apply {
                    list.forEachIndexed { index, field ->
                        when (index) {
                            0 -> id = parseValue(field)
                            1 -> date = parseValue(field)
                            2 -> description = parseValue(field)
                            3 -> guide = parseValue(field)
                            4 -> status = parseValue(field)
                            5 -> name = parseValue(field)
                        }
                    }
                }
            }?.let { toXml(it) }?.let { convertToString(it) }
        }
    }
    // Update the work table.
    suspend fun flipItemArchive(id: String) {
        val arc = "1"
        // Specify the SQL statement to query data.
        val sqlStatement = "update work set archive = (:arch) where idwork =(:id);"
        val parametersVal = listOf(
            param2("arch", arc),
            param2("id", id)
        )
        val statementRequest = ExecuteStatementRequest {
            clusterIdentifier = clusterId
            database = databaseVal
            dbUser = dbUserVal
            sql = sqlStatement
            parameters = parametersVal
        }

        RedshiftDataClient { region = "us-west-2" }.use { redshiftDataClient ->
            redshiftDataClient.executeStatement(statementRequest)
        }
    }

    // Convert Work item data into XML.
    private fun toXml(itemList: List<WorkItem>): Document? {
        try {
            val factory = DocumentBuilderFactory.newInstance()
            val builder = factory.newDocumentBuilder()
            val doc = builder.newDocument()

            // Start building the XML.
            val root = doc.createElement("Items")
            doc.appendChild(root)

            // Get the elements from the collection.
            val custCount = itemList.size

            // Iterate through the collection.
            for (index in 0 until custCount) {
                // Get the WorkItem object from the collection.
                val myItem = itemList[index]
                val item = doc.createElement("Item")
                root.appendChild(item)

                // Set Id.
                val id = doc.createElement("Id")
                id.appendChild(doc.createTextNode(myItem.id))
                item.appendChild(id)

                // Set Name.
                val name = doc.createElement("Name")
                name.appendChild(doc.createTextNode(myItem.name))
                item.appendChild(name)

                // Set Date.
                val date = doc.createElement("Date")
                date.appendChild(doc.createTextNode(myItem.date))
                item.appendChild(date)

                // Set Description.
                val desc = doc.createElement("Description")
                desc.appendChild(doc.createTextNode(myItem.description))
                item.appendChild(desc)

                // Set Guide.
                val guide = doc.createElement("Guide")
                guide.appendChild(doc.createTextNode(myItem.guide))
                item.appendChild(guide)

                // Set Status.
                val status = doc.createElement("Status")
                status.appendChild(doc.createTextNode(myItem.status))
                item.appendChild(status)
            }
            return doc
        } catch (e: ParserConfigurationException) {
            e.printStackTrace()
        }
        return null
    }

    private fun convertToString(xml: Document): String? {
        try {
            val transformer = TransformerFactory.newInstance().newTransformer()
            val result = StreamResult(StringWriter())
            val source = DOMSource(xml)
            transformer.transform(source, result)
            return result.writer.toString()
        } catch (ex: TransformerException) {
            ex.printStackTrace()
        }
        return null
    }

    // Return the String value of the field.
    fun parseValue(myField: Field): String {
        val ss = myField.toString()
        if ("StringValue" in ss) {
            var str = ss.substringAfterLast("=")
            str = str.substring(0, str.length - 1)
            return str
        }
        return ""
    }

    fun parseBooleanValue(myField: Field): String {
        val ss = myField.toString()
        if ("BooleanValue" in ss) {
            var str = ss.substringAfterLast("=")
            str = str.substring(0, str.length - 1)
            return str
        }
        return ""
    }

    suspend fun performSQLStatement(statementRequest: ExecuteStatementRequest): String? {
        RedshiftDataClient { region = "us-west-2" }.use { redshiftDataClient ->
            val response = redshiftDataClient.executeStatement(statementRequest)
            return response.id
        }
    }

    suspend fun checkStatement(sqlId: String?) {
        val statementRequest = DescribeStatementRequest {
            id = sqlId
        }

        // Wait until the sql statement processing is finished.
        var finished = false
        var status: String
        RedshiftDataClient { region = "us-west-2" }.use { redshiftDataClient ->
            while (!finished) {
                val response = redshiftDataClient.describeStatement(statementRequest)
                status = response.status.toString()
                println("...$status")

                if (status.compareTo("Finished") == 0) {
                    finished = true
                } else {
                    delay(500)
                }
            }
        }
        println("The statement is finished!")
    }
}

package com.example.demo

import kotlin.system.exitProcess
import aws.sdk.kotlin.services.ses.SesClient
import aws.sdk.kotlin.services.ses.model.SesException
import aws.sdk.kotlin.services.ses.model.Destination
import aws.sdk.kotlin.services.ses.model.Content
import aws.sdk.kotlin.services.ses.model.Body
import aws.sdk.kotlin.services.ses.model.Message
import aws.sdk.kotlin.services.ses.model.SendEmailRequest

class SendMessage {

    suspend fun send(
        recipient: String,
        strValue: String?
    ) {
        val sesClient = SesClient { region = "us-east-1" }
        // The HTML body of the email.
        val bodyHTML = ("<html>" + "<head></head>" + "<body>" + "<h1>Amazon RDS Items!</h1>"
                + "<textarea>$strValue</textarea>" + "</body>" + "</html>")

        val destinationOb = Destination {
            toAddresses = listOf(recipient)
        }

        val contentOb = Content {
            data = bodyHTML
        }

        val subOb = Content {
            data = "Item Report"
        }

        val bodyOb= Body {
            html = contentOb
        }

        val msgOb = Message {
            subject = subOb
            body = bodyOb
        }

        val emailRequest = SendEmailRequest {
            destination = destinationOb
            message = msgOb
            source = "<Enter email>"
        }

        try {
            println("Attempting to send an email through Amazon SES using the AWS SDK for Kotlin...")
            sesClient.sendEmail(emailRequest)

        } catch (e: SesException) {
            println(e.message)
            sesClient.close()
            exitProcess(0)
        }
    }
}

   package com.example.demo

class WorkItem {
    var id: String? = null
    var name: String? = null
    var guide: String? = null
    var date: String? = null
    var description: String? = null
    var status: String? = null
    var archived: Boolean? = null
}

   curl -X GET http://localhost:8080/api/items

   curl -X POST -H "Content-Type: application/json" -d "{\"email\":\"<email address>\"}" http://localhost:8080/api/items:report