src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt

/*
 * Copyright 2021 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

import de.uapcore.lightpit.entities.*
import de.uapcore.lightpit.filter.*
import de.uapcore.lightpit.types.WebColor
import java.sql.Connection
import java.sql.PreparedStatement
import java.sql.ResultSet

class PostgresDataAccessObject(private val connection: Connection) : DataAccessObject {

    //<editor-fold desc="User">
    private fun selectUserInfo(
        rs: ResultSet,
        idColumn: String = "userid",
        usernameColumn: String = "username",
        givennameColumn: String = "givenname",
        lastnameColumn: String = "lastname",
        mailColumn: String = "mail"
    ): User? {
        val idval = rs.getInt(idColumn)
        return if (rs.wasNull()) null else {
            User(idval).apply {
                username = rs.getString(usernameColumn)
                givenname = rs.getString(givennameColumn)
                lastname = rs.getString(lastnameColumn)
                mail = rs.getString(mailColumn)
            }
        }
    }

    private fun selectUsers(stmt: PreparedStatement) = sequence {
        stmt.executeQuery().use { rs ->
            while (rs.next()) selectUserInfo(rs)?.let { yield(it) }
        }
    }

    //language=SQL
    private val userQuery = "select userid, username, lastname, givenname, mail from lpit_user"

    private val stmtUsers by lazy {
        connection.prepareStatement(
            """${userQuery}
            where userid > 0
            order by username
            """
        )
    }
    private val stmtUserByID by lazy {
        connection.prepareStatement(
            """${userQuery}
            where userid = ?
            """
        )
    }
    private val stmtUserByName by lazy {
        connection.prepareStatement(
            """${userQuery}
            where lower(username) = lower(?)
            """
        )
    }
    private val stmtInsertUser by lazy {
        connection.prepareStatement(
            "insert into lpit_user (username, lastname, givenname, mail) values (?, ?, ?, ?)"
        )
    }
    private val stmtUpdateUser by lazy {
        connection.prepareStatement(
            "update lpit_user set lastname = ?, givenname = ?, mail = ? where userid = ?"
        )
    }

    override fun listUsers() = selectUsers(stmtUsers).toList()
    override fun findUser(id: Int): User? {
        stmtUserByID.setInt(1, id)
        return selectUsers(stmtUserByID).firstOrNull()
    }

    override fun findUserByName(username: String): User? {
        stmtUserByName.setString(1, username)
        return selectUsers(stmtUserByName).firstOrNull()
    }

    override fun insertUser(user: User) {
        with(user) {
            stmtInsertUser.setStringSafe(1, username)
            stmtInsertUser.setStringOrNull(2, lastname)
            stmtInsertUser.setStringOrNull(3, givenname)
            stmtInsertUser.setStringOrNull(4, mail)
        }
        stmtInsertUser.execute()
    }

    override fun updateUser(user: User) {
        with(user) {
            stmtUpdateUser.setStringOrNull(1, lastname)
            stmtUpdateUser.setStringOrNull(2, givenname)
            stmtUpdateUser.setStringOrNull(3, mail)
            stmtUpdateUser.setInt(4, id)
        }
        stmtUpdateUser.execute()
    }
    //</editor-fold>

    //<editor-fold desc="Version">
    private fun selectVersions(stmt: PreparedStatement) = sequence {
        stmt.executeQuery().use { rs ->
            while (rs.next()) {
                yield(Version(rs.getInt("versionid"), rs.getInt("project")).apply {
                    name = rs.getString("name")
                    node = rs.getString("node")
                    ordinal = rs.getInt("ordinal")
                    status = rs.getEnum("status")
                })
            }
        }
    }

    private fun setVersionFields(stmt: PreparedStatement, obj: Version): Int {
        with(obj) {
            stmt.setStringSafe(1, name)
            stmt.setStringSafe(2, node)
            stmt.setInt(3, ordinal)
            stmt.setEnum(4, status)
        }
        return 5
    }

    //language=SQL
    private val versionQuery = "select versionid, project, name, node, ordinal, status from lpit_version"

    private val stmtVersions by lazy {
        connection.prepareStatement(
            """${versionQuery}
            where project = ?
            order by ordinal desc, lower(name) desc
            """
        )
    }
    private val stmtVersionByID by lazy {
        connection.prepareStatement(
            """${versionQuery}
            where versionid = ?
            """
        )
    }
    private val stmtVersionByNode by lazy {
        connection.prepareStatement(
            """${versionQuery}
            where project = ? and node = ?
            """
        )
    }
    private val stmtInsertVersion by lazy {
        connection.prepareStatement(
            """
            insert into lpit_version (name, node, ordinal, status, project)
            values (?, ?, ?, ?::version_status, ?)
            """
        )
    }
    private val stmtUpdateVersion by lazy {
        connection.prepareStatement(
            """
            update lpit_version set name = ?, node = ?, ordinal = ?, status = ?::version_status
            where versionid = ?
            """
        )
    }

    override fun listVersions(project: Project): List<Version> {
        stmtVersions.setInt(1, project.id)
        return selectVersions(stmtVersions).toList()
    }

    override fun findVersion(id: Int): Version? {
        stmtVersionByID.setInt(1, id)
        return selectVersions(stmtVersionByID).firstOrNull()
    }

    override fun findVersionByNode(project: Project, node: String): Version? {
        stmtVersionByNode.setInt(1, project.id)
        stmtVersionByNode.setString(2, node)
        return selectVersions(stmtVersionByNode).firstOrNull()
    }

    override fun insertVersion(version: Version) {
        val col = setVersionFields(stmtInsertVersion, version)
        stmtInsertVersion.setInt(col, version.projectid)
        stmtInsertVersion.execute()
    }

    override fun updateVersion(version: Version) {
        val col = setVersionFields(stmtUpdateVersion, version)
        stmtUpdateVersion.setInt(col, version.id)
        stmtUpdateVersion.execute()
    }
    //</editor-fold>

    //<editor-fold desc="Component">
    private fun selectComponents(stmt: PreparedStatement) = sequence {
        stmt.executeQuery().use { rs ->
            while (rs.next()) {
                yield(Component(rs.getInt("id"), rs.getInt("project")).apply {
                    name = rs.getString("name")
                    node = rs.getString("node")
                    color = try {
                        WebColor(rs.getString("color"))
                    } catch (ex: IllegalArgumentException) {
                        WebColor("000000")
                    }
                    ordinal = rs.getInt("ordinal")
                    description = rs.getString("description")
                    lead = selectUserInfo(rs)
                })
            }
        }
    }

    private fun setComponentFields(stmt: PreparedStatement, obj: Component): Int {
        with(obj) {
            stmt.setStringSafe(1, name)
            stmt.setStringSafe(2, node)
            stmt.setStringSafe(3, color.hex)
            stmt.setInt(4, ordinal)
            stmt.setStringOrNull(5, description)
            stmt.setIntOrNull(6, obj.lead?.id)
        }
        return 7
    }

    //language=SQL
    private val componentQuery =
        """
        select id, project, name, node, color, ordinal, description,
            userid, username, givenname, lastname, mail
        from lpit_component
        left join lpit_user on lead = userid
        """

    private val stmtComponents by lazy {
        connection.prepareStatement(
            """${componentQuery}
            where project = ?
            order by ordinal, lower(name)
            """
        )
    }
    private val stmtComponentById by lazy {
        connection.prepareStatement(
            """${componentQuery}
            where id = ?
            """
        )
    }
    private val stmtComponentByNode by lazy {
        connection.prepareStatement(
            """${componentQuery}
            where project = ? and node = ?
            """
        )
    }
    private val stmtInsertComponent by lazy {
        connection.prepareStatement(
            """
            insert into lpit_component (name, node, color, ordinal, description, lead, project)
            values (?, ?, ?, ?, ?, ?, ?)
            """
        )
    }
    private val stmtUpdateComponent by lazy {
        connection.prepareStatement(
            "update lpit_component set name = ?, node = ?, color = ?, ordinal = ?, description = ?, lead = ? where id = ?"
        )
    }

    override fun listComponents(project: Project): List<Component> {
        stmtComponents.setInt(1, project.id)
        return selectComponents(stmtComponents).toList()
    }

    override fun findComponent(id: Int): Component? {
        stmtComponentById.setInt(1, id)
        return selectComponents(stmtComponentById).firstOrNull()
    }

    override fun findComponentByNode(project: Project, node: String): Component? {
        stmtComponentByNode.setInt(1, project.id)
        stmtComponentByNode.setString(2, node)
        return selectComponents(stmtComponentByNode).firstOrNull()
    }

    override fun insertComponent(component: Component) {
        val col = setComponentFields(stmtInsertComponent, component)
        stmtInsertComponent.setInt(col, component.projectid)
        stmtInsertComponent.execute()
    }

    override fun updateComponent(component: Component) {
        val col = setComponentFields(stmtUpdateComponent, component)
        stmtUpdateComponent.setInt(col, component.id)
        stmtUpdateComponent.execute()
    }

    //</editor-fold>

    //<editor-fold desc="Project">

    private fun selectProjects(stmt: PreparedStatement) = sequence {
        stmt.executeQuery().use { rs ->
            while (rs.next()) {
                yield(Project(rs.getInt("projectid")).apply {
                    name = rs.getString("name")
                    node = rs.getString("node")
                    ordinal = rs.getInt("ordinal")
                    description = rs.getString("description")
                    repoUrl = rs.getString("repourl")
                    owner = selectUserInfo(rs)
                })
            }
        }
    }

    private fun setProjectFields(stmt: PreparedStatement, obj: Project): Int {
        with(obj) {
            stmt.setStringSafe(1, name)
            stmt.setStringSafe(2, node)
            stmt.setInt(3, ordinal)
            stmt.setStringOrNull(4, description)
            stmt.setStringOrNull(5, repoUrl)
            stmt.setIntOrNull(6, owner?.id)
        }
        return 7
    }

    //language=SQL
    private val projectQuery =
        """
        select projectid, name, node, ordinal, description, repourl,
            userid, username, lastname, givenname, mail
        from lpit_project
        left join lpit_user owner on lpit_project.owner = owner.userid
        """

    private val stmtProjects by lazy {
        connection.prepareStatement(
            """${projectQuery}
            order by ordinal, lower(name)
            """
        )
    }
    private val stmtProjectByID by lazy {
        connection.prepareStatement(
            """${projectQuery}
            where projectid = ?
            """
        )
    }
    private val stmtProjectByNode by lazy {
        connection.prepareStatement(
            """${projectQuery}
            where node = ?
            """
        )
    }
    private val stmtInsertProject by lazy {
        connection.prepareStatement(
            "insert into lpit_project (name, node, ordinal, description, repourl, owner) values (?, ?, ?, ?, ?, ?)"
        )
    }
    private val stmtUpdateProject by lazy {
        connection.prepareStatement(
            "update lpit_project set name = ?, node = ?, ordinal = ?, description = ?, repourl = ?, owner = ? where projectid = ?"
        )
    }
    private val stmtIssueSummary by lazy {
        connection.prepareStatement(
            """
            select phase, count(*) as total
            from lpit_issue
            join lpit_issue_phases using(status)
            where project = ?
            group by phase  
            """
        )
    }

    override fun listProjects(): List<Project> {
        return selectProjects(stmtProjects).toList()
    }

    override fun findProject(id: Int): Project? {
        stmtProjectByID.setInt(1, id)
        return selectProjects(stmtProjectByID).firstOrNull()
    }

    override fun findProjectByNode(node: String): Project? {
        stmtProjectByNode.setString(1, node)
        return selectProjects(stmtProjectByNode).firstOrNull()
    }

    override fun insertProject(project: Project) {
        setProjectFields(stmtInsertProject, project)
        stmtInsertProject.execute()
    }

    override fun updateProject(project: Project) {
        val col = setProjectFields(stmtUpdateProject, project)
        stmtUpdateProject.setInt(col, project.id)
        stmtUpdateProject.execute()
    }

    override fun collectIssueSummary(project: Project): IssueSummary {
        stmtIssueSummary.setInt(1, project.id)
        return stmtIssueSummary.executeQuery().use { rs ->
            val summary = IssueSummary()
            while (rs.next()) {
                val phase = rs.getInt("phase")
                val total = rs.getInt("total")
                when (phase) {
                    0 -> summary.open = total
                    1 -> summary.active = total
                    2 -> summary.done = total
                }
            }
            summary
        }
    }

    //</editor-fold>

    //<editor-fold desc="Issue">

    private fun selectIssues(stmt: PreparedStatement) = sequence {
        stmt.executeQuery().use { rs ->
            while (rs.next()) {
                val proj = Project(rs.getInt("project")).apply {
                    name = rs.getString("projectname")
                    node = rs.getString("projectnode")
                }
                val comp = rs.getInt("component").let {
                    if (rs.wasNull()) null else
                        Component(it, proj.id).apply {
                            name = rs.getString("componentname")
                            node = rs.getString("componentnode")
                        }
                }
                val issue = Issue(rs.getInt("issueid"), proj, comp).apply {
                    component = comp
                    status = rs.getEnum("status")
                    category = rs.getEnum("category")
                    subject = rs.getString("subject")
                    description = rs.getString("description")
                    assignee = selectUserInfo(rs)
                    created = rs.getTimestamp("created")
                    updated = rs.getTimestamp("updated")
                    eta = rs.getDate("eta")
                }
                queryAffectedVersions.setInt(1, issue.id)
                issue.affectedVersions = selectVersions(queryAffectedVersions).toList()
                queryResolvedVersions.setInt(1, issue.id)
                issue.resolvedVersions = selectVersions(queryResolvedVersions).toList()
                yield(issue)
            }
        }
    }

    private fun setIssueFields(stmt: PreparedStatement, obj: Issue): Int {
        with(obj) {
            stmt.setIntOrNull(1, component?.id)
            stmt.setEnum(2, status)
            stmt.setEnum(3, category)
            stmt.setStringSafe(4, subject)
            stmt.setStringOrNull(5, description)
            stmt.setIntOrNull(6, assignee?.id)
            stmt.setDateOrNull(7, eta)
        }
        return 8
    }

    //language=SQL
    private val issueQuery =
        """
        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 queryResolvedVersions by lazy {
        connection.prepareStatement(
            """
            select versionid, project, name, status, ordinal, node
            from lpit_version v join lpit_issue_resolved_version using (versionid)
            where issueid = ?
            order by ordinal, name
            """
        )
    }

    private val queryAffectedVersions by lazy {
        connection.prepareStatement(
            """
            select versionid, project, name, status, ordinal, node
            from lpit_version join lpit_issue_affected_version using (versionid)
            where issueid = ?
            order by ordinal, name
            """
        )
    }

    private val stmtIssues by lazy {
        connection.prepareStatement(
            """
            with issue_version as (
                select issueid, versionid from lpit_issue_affected_version
                union select issueid, versionid from lpit_issue_resolved_version
            ) ${issueQuery} left join issue_version using (issueid)
            where
            (not ? or projectid = ?) and 
            (not ? or versionid = ?) and (not ? or versionid is null) and
            (not ? or component = ?) and (not ? or component is null)
            """
        )
    }

    private val fproj = 1
    private val projectid = 2
    private val fversion = 3
    private val versionid = 4
    private val nversion = 5
    private val fcomp = 6
    private val component = 7
    private val ncomp = 8

    private fun <T : Entity> applyFilter(filter: Filter<T>, fflag: Int, nflag: Int, idcol: Int) {
        when (filter) {
            is AllFilter -> {
                stmtIssues.setBoolean(fflag, false)
                stmtIssues.setBoolean(nflag, false)
                stmtIssues.setInt(idcol, 0)
            }
            is NoneFilter -> {
                stmtIssues.setBoolean(fflag, false)
                stmtIssues.setBoolean(nflag, true)
                stmtIssues.setInt(idcol, 0)
            }
            is SpecificFilter -> {
                stmtIssues.setBoolean(fflag, true)
                stmtIssues.setBoolean(nflag, false)
                stmtIssues.setInt(idcol, filter.obj.id)
            }
            else -> {
                TODO("Implement range filter.")
            }
        }
    }

    override fun listIssues(filter: IssueFilter): List<Issue> {
        when (filter.project) {
            is AllFilter -> {
                stmtIssues.setBoolean(fproj, false)
                stmtIssues.setInt(projectid, 0)
            }
            is SpecificFilter -> {
                stmtIssues.setBoolean(fproj, true)
                stmtIssues.setInt(projectid, filter.project.obj.id)
            }
            else -> throw IllegalArgumentException()
        }
        applyFilter(filter.version, fversion, nversion, versionid)
        applyFilter(filter.component, fcomp, ncomp, component)

        return selectIssues(stmtIssues).toList()
    }

    private val stmtFindIssueByID by lazy {
        connection.prepareStatement(
            """${issueQuery}
            where issueid = ?
            """
        )
    }
    private val stmtInsertIssue by lazy {
        connection.prepareStatement(
            """
            insert into lpit_issue (component, status, category, subject, description, assignee, eta, project)
            values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?)
            returning issueid
            """
        )
    }
    private val stmtUpdateIssue by lazy {
        connection.prepareStatement(
            """
            update lpit_issue set updated = now(),
                component = ?, status = ?::issue_status, category = ?::issue_category, subject = ?,
                description = ?, assignee = ?, eta = ?
            where issueid = ?
            """
        )
    }
    private val stmtInsertAffectedVersion by lazy {
        connection.prepareStatement(
            "insert into lpit_issue_affected_version (issueid, versionid) values (?,?)"
        )
    }
    private val stmtInsertResolvedVersion by lazy {
        connection.prepareStatement(
            "insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)"
        )
    }
    private val stmtClearAffectedVersions by lazy {
        connection.prepareStatement("delete from lpit_issue_affected_version where issueid = ?")
    }
    private val stmtClearResolvedVersions by lazy {
        connection.prepareStatement("delete from lpit_issue_resolved_version where issueid = ?")
    }

    override fun findIssue(id: Int): Issue? {
        stmtFindIssueByID.setInt(1, id)
        return selectIssues(stmtFindIssueByID).firstOrNull()
    }

    private fun insertVersionInfo(issue: Issue) {
        stmtInsertAffectedVersion.setInt(1, issue.id)
        stmtInsertResolvedVersion.setInt(1, issue.id)
        issue.affectedVersions.forEach {
            stmtInsertAffectedVersion.setInt(2, it.id)
            stmtInsertAffectedVersion.execute()
        }
        issue.resolvedVersions.forEach {
            stmtInsertResolvedVersion.setInt(2, it.id)
            stmtInsertResolvedVersion.execute()
        }
    }

    override fun insertIssue(issue: Issue) {
        val col = setIssueFields(stmtInsertIssue, issue)
        stmtInsertIssue.setInt(col, issue.project.id)
        stmtInsertIssue.executeQuery().use { rs ->
            rs.next()
            issue.id = rs.getInt(1)
        }
        insertVersionInfo(issue)
    }

    override fun updateIssue(issue: Issue) {
        val col = setIssueFields(stmtUpdateIssue, issue)
        stmtUpdateIssue.setInt(col, issue.id)
        stmtUpdateIssue.execute()
        // TODO: improve by only inserting / deleting changed version information
        stmtClearAffectedVersions.setInt(1, issue.id)
        stmtClearResolvedVersions.setInt(1, issue.id)
        stmtClearAffectedVersions.execute()
        stmtClearResolvedVersions.execute()
        insertVersionInfo(issue)
    }

    //</editor-fold>

    //<editor-fold desc="IssueComment">

    private fun selectComments(stmt: PreparedStatement) = sequence {
        stmt.executeQuery().use { rs ->
            while (rs.next()) {
                yield(IssueComment(rs.getInt("commentid"), rs.getInt("issueid")).apply {
                    created = rs.getTimestamp("created")
                    updated = rs.getTimestamp("updated")
                    updateCount = rs.getInt("updatecount")
                    comment = rs.getString("comment")
                    author = selectUserInfo(rs)
                })
            }
        }
    }

    private val stmtComments by lazy {
        connection.prepareStatement(
            "select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created"
        )
    }
    private val stmtInsertComment by lazy {
        connection.prepareStatement(
            "insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?)"
        )
    }
    private val stmtUpdateIssueDate by lazy {
        connection.prepareStatement(
            "update lpit_issue set updated = now() where issueid = ?"
        )
    }

    override fun listComments(issue: Issue): List<IssueComment> {
        stmtComments.setInt(1, issue.id)
        return selectComments(stmtComments).toList()
    }

    override fun insertComment(issueComment: IssueComment) {
        with(issueComment) {
            stmtUpdateIssueDate.setInt(1, issueid)
            stmtInsertComment.setInt(1, issueid)
            stmtInsertComment.setStringSafe(2, comment)
            stmtInsertComment.setIntOrNull(3, author?.id)
        }
        stmtInsertComment.execute()
        stmtUpdateIssueDate.execute()
    }
    //</editor-fold>
}