src/main/kotlin/de/uapcore/lightpit/dao/postgres/PGIssueDao.kt

/*
 * Copyright 2020 Mike Becker. All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * 1. Redistributions of source code must retain the above copyright
 * notice, this list of conditions and the following disclaimer.
 *
 * 2. Redistributions in binary form must reproduce the above copyright
 * notice, this list of conditions and the following disclaimer in the
 * documentation and/or other materials provided with the distribution.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
 * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE
 * FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
 * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
 * SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
 * CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
 * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 */

package de.uapcore.lightpit.dao.postgres

import de.uapcore.lightpit.dao.AbstractIssueDao
import de.uapcore.lightpit.dao.Functions
import de.uapcore.lightpit.entities.*
import java.sql.Connection
import java.sql.PreparedStatement
import java.sql.ResultSet
import java.sql.Types

class PGIssueDao(connection: Connection) : AbstractIssueDao() {

    private val query = "select issueid, i.project, p.name as projectname, p.node as projectnode, " +
            "component, c.name as componentname, c.node as componentnode, " +
            "status, category, subject, i.description, " +
            "userid, username, givenname, lastname, mail, " +
            "created, updated, eta " +
            "from lpit_issue i " +
            "join lpit_project p on i.project = projectid " +
            "left join lpit_component c on component = c.id " +
            "left join lpit_user on userid = assignee "
    private val list = connection.prepareStatement(query +
            "where i.project = ? and coalesce(component, -1) = coalesce(?, component, -1)")
    private val listForVersion = connection.prepareStatement(
            "with issue_version as ( " +
                    "select issueid, versionid from lpit_issue_affected_version union " +
                    "select issueid, versionid from lpit_issue_resolved_version) " +
                    query +
                    "left join issue_version using (issueid) " +
                    "where i.project = ? " +
                    "and coalesce(versionid,-1) = ? and coalesce(component, -1) = coalesce(?, component, -1)"
    )
    private val find = connection.prepareStatement(query + "where issueid = ? ")
    private val insert = connection.prepareStatement(
            "insert into lpit_issue (project, component, status, category, subject, description, assignee, eta) " +
                    "values (?, ?, ?::issue_status, ?::issue_category, ?, ?, ?, ?) returning issueid"
    )
    private val update = connection.prepareStatement(
            "update lpit_issue set " +
                    "updated = now(), component = ?, status = ?::issue_status, category = ?::issue_category, " +
                    "subject = ?, description = ?, assignee = ?, eta = ? where issueid = ?"
    )
    private val affectedVersions = connection.prepareStatement(
            "select versionid, name, status, ordinal, node " +
                    "from lpit_version join lpit_issue_affected_version using (versionid) " +
                    "where issueid = ? " +
                    "order by ordinal, name"
    )
    private val clearAffected = connection.prepareStatement("delete from lpit_issue_affected_version where issueid = ?")
    private val insertAffected = connection.prepareStatement("insert into lpit_issue_affected_version (issueid, versionid) values (?,?)")

    private val resolvedVersions = connection.prepareStatement(
            "select versionid, name, status, ordinal, node " +
                    "from lpit_version v join lpit_issue_resolved_version using (versionid) " +
                    "where issueid = ? " +
                    "order by ordinal, name"
    )
    private val clearResolved = connection.prepareStatement("delete from lpit_issue_resolved_version where issueid = ?")
    private val insertResolved = connection.prepareStatement("insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)")
    private val insertComment = connection.prepareStatement(
            "insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?)"
    )
    private val updateComment = connection.prepareStatement(
            "update lpit_issue_comment set comment = ?, updated = now(), updatecount = updatecount+1 where commentid = ?"
    )
    private val listComments = connection.prepareStatement(
            "select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created"
    )

    override fun mapResult(rs: ResultSet): Issue {
        val project = Project(rs.getInt("project"))
        project.name = rs.getString("projectname")
        project.node = rs.getString("projectnode")
        val issue = Issue(rs.getInt("issueid"))
        issue.project = project
        issue.component = rs.getInt("component").let { id ->
            if (rs.wasNull()) {
                null
            } else {
                val component = Component(id)
                component.name = rs.getString("componentname")
                component.node = rs.getString("componentnode")
                component
            }
        }
        issue.status = IssueStatus.valueOf(rs.getString("status"))
        issue.category = IssueCategory.valueOf(rs.getString("category"))
        issue.subject = rs.getString("subject")
        issue.description = rs.getString("description")
        issue.assignee = PGUserDao.mapResult(rs).takeUnless { rs.wasNull() }
        issue.created = rs.getTimestamp("created")
        issue.updated = rs.getTimestamp("updated")
        issue.eta = rs.getDate("eta")
        return issue
    }

    private fun updateVersionLists(instance: Issue) {
        clearAffected.setInt(1, instance.id)
        clearResolved.setInt(1, instance.id)
        insertAffected.setInt(1, instance.id)
        insertResolved.setInt(1, instance.id)
        clearAffected.executeUpdate()
        clearResolved.executeUpdate()
        for (v: Version in instance.affectedVersions) {
            insertAffected.setInt(2, v.id)
            insertAffected.executeUpdate()
        }
        for (v: Version in instance.resolvedVersions) {
            insertResolved.setInt(2, v.id)
            insertResolved.executeUpdate()
        }
    }

    private fun setData(stmt: PreparedStatement, column: Int, instance: Issue): Int {
        var col = column
        setForeignKeyOrNull(stmt, ++col, instance.component, Component::id)
        stmt.setString(++col, instance.status.name)
        stmt.setString(++col, instance.category.name)
        stmt.setString(++col, instance.subject)
        Functions.setStringOrNull(stmt, ++col, instance.description)
        setForeignKeyOrNull(stmt, ++col, instance.assignee, User::id)
        Functions.setDateOrNull(stmt, ++col, instance.eta)
        return col
    }

    override fun save(instance: Issue, parent: Project) {
        instance.project = parent
        var column = 0
        insert.setInt(++column, parent.id)
        setData(insert, column, instance)
        // insert and retrieve the ID
        val rs = insert.executeQuery()
        rs.next()
        instance.id = rs.getInt(1)
        updateVersionLists(instance)
    }

    override fun update(instance: Issue): Boolean {
        var column = setData(update, 0, instance)
        update.setInt(++column, instance.id)
        return if (update.executeUpdate() > 0) {
            updateVersionLists(instance)
            true
        } else {
            false
        }
    }

    override fun list(parent: Project): List<Issue> {
        list.setInt(1, parent.id)
        list.setNull(2, Types.INTEGER)
        return super.list(list)
    }

    override fun list(project: Project, component: Component?, version: Version?): List<Issue> {
        listForVersion.setInt(1, project.id)
        listForVersion.setInt(2, version?.id ?: -1)
        listForVersion.setInt(3, component?.id ?: -1)
        return super.list(listForVersion)
    }

    override fun list(project: Project, version: Version?): List<Issue> {
        listForVersion.setInt(1, project.id)
        listForVersion.setInt(2, version?.id ?: -1)
        listForVersion.setNull(3, Types.INTEGER)
        return super.list(listForVersion)
    }

    override fun list(project: Project, component: Component?): List<Issue> {
        list.setInt(1, project.id)
        list.setInt(2, component?.id ?: -1)
        return super.list(list)
    }

    override fun find(id: Int): Issue? {
        find.setInt(1, id)
        return super.find(find)
    }

    private fun listVersions(stmt: PreparedStatement, issue: Issue): List<Version> {
        stmt.setInt(1, issue.id)
        return sequence {
            stmt.executeQuery().use { result ->
                while (result.next()) yield(PGVersionDao.mapResult(result))
            }
        }.toList()
    }

    override fun joinVersionInformation(issue: Issue) {
        issue.affectedVersions = listVersions(affectedVersions, issue)
        issue.resolvedVersions = listVersions(resolvedVersions, issue)
    }

    override fun listComments(issue: Issue): List<IssueComment> {
        listComments.setInt(1, issue.id)
        return sequence {
            listComments.executeQuery().use { rs ->
                while (rs.next()) {
                    val comment = IssueComment(rs.getInt("commentid"))
                    comment.created = rs.getTimestamp("created")
                    comment.updated = rs.getTimestamp("updated")
                    comment.updateCount = rs.getInt("updatecount")
                    comment.comment = rs.getString("comment")
                    comment.author = PGUserDao.mapResult(rs).takeUnless { rs.wasNull() }
                    yield(comment)
                }
            }
        }.toList()
    }

    override fun saveComment(issue: Issue, comment: IssueComment) {
        if (comment.id >= 0) {
            updateComment.setString(1, comment.comment)
            updateComment.setInt(2, comment.id)
            updateComment.execute()
        } else {
            insertComment.setInt(1, issue.id)
            insertComment.setString(2, comment.comment)
            setForeignKeyOrNull(insertComment, 3, comment.author, User::id)
            insertComment.execute()
        }
    }
}