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.types.WebColor
import de.uapcore.lightpit.util.*
import de.uapcore.lightpit.viewmodel.ComponentSummary
import de.uapcore.lightpit.viewmodel.IssueSummary
import de.uapcore.lightpit.viewmodel.VersionSummary
import org.intellij.lang.annotations.Language
import java.sql.Connection
import java.sql.PreparedStatement
import java.sql.ResultSet

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

    /**
     * Prepares the given [sql] statement and executes the [block] function with the prepared statement as receiver.
     * The statement is then closed properly.
     */
    private fun <R> withStatement(@Language("SQL") sql: String, block: PreparedStatement.() -> R) =
        connection.prepareStatement(sql).use(block)

    /**
     * Prepares the given [sql] statement and executes the [block] function on that statement.
     * The statement is then closed properly.
     */
    private fun <R> useStatement(@Language("SQL") sql: String, block: (PreparedStatement) -> R) =
        connection.prepareStatement(sql).use(block)

    /**
     * Executes the statement and iterates the whole result set extracting the rows with the given [extractor] function.
     */
    private fun <T> PreparedStatement.queryAll(extractor: (ResultSet) -> T): List<T> = executeQuery().use {
        sequence {
            while (it.next()) {
                yield(extractor(it))
            }
        }.toList()
    }

    /**
     * Executes the statement and extracts a single row with the given [extractor] function.
     * If the result set is empty, null is returned.
     */
    private fun <T> PreparedStatement.querySingle(extractor: (ResultSet) -> T): T? = executeQuery().use {
        return if (it.next()) extractor(it) else null
    }

    //<editor-fold desc="User">
    //language=SQL
    private val userQuery = "select userid, username, lastname, givenname, mail from lpit_user"

    private fun ResultSet.extractUser() = User(getInt("userid")).apply {
        username = getString("username")
        givenname = getString("givenname")
        lastname = getString("lastname")
        mail = getString("mail")
    }

    private fun ResultSet.containsUserInfo(): Boolean {
        getInt("userid")
        return !wasNull()
    }

    private fun ResultSet.extractOptionalUser() = if (containsUserInfo()) extractUser() else null

    override fun listUsers() =
        withStatement("$userQuery where userid > 0 order by username") {
            queryAll { it.extractUser() }
        }

    override fun findUser(id: Int): User? =
        withStatement("$userQuery where userid = ?") {
            setInt(1, id)
            querySingle { it.extractUser() }
        }

    override fun findUserByName(username: String): User? =
        withStatement("$userQuery where lower(username) = lower(?)") {
            setString(1, username)
            querySingle { it.extractUser() }
        }

    override fun insertUser(user: User) {
        withStatement("insert into lpit_user (username, lastname, givenname, mail) values (?, ?, ?, ?)") {
            with(user) {
                setStringSafe(1, username)
                setStringOrNull(2, lastname)
                setStringOrNull(3, givenname)
                setStringOrNull(4, mail)
            }
            executeUpdate()
        }
    }

    override fun updateUser(user: User) {
        withStatement("update lpit_user set lastname = ?, givenname = ?, mail = ? where userid = ?") {
            with(user) {
                setStringOrNull(1, lastname)
                setStringOrNull(2, givenname)
                setStringOrNull(3, mail)
                setInt(4, id)
            }
            executeUpdate()
        }
    }
//</editor-fold>

    //<editor-fold desc="Version">
    //language=SQL
    private val versionQuery = "select versionid, project, name, node, ordinal, status from lpit_version"

    private fun ResultSet.extractVersion() =
        Version(getInt("versionid"), getInt("project")).apply {
            name = getString("name")
            node = getString("node")
            ordinal = getInt("ordinal")
            status = getEnum("status")
        }

    override fun listVersions(project: Project): List<Version> =
        withStatement("$versionQuery where project = ? order by ordinal desc, lower(name) desc") {
            setInt(1, project.id)
            queryAll { it.extractVersion() }
        }

    override fun listVersionSummaries(project: Project): List<VersionSummary> =
        withStatement(
            """
            with version_map(issueid, versionid, isresolved) as (
                select issueid, versionid, true
                from lpit_issue_resolved_version
                union
                select issueid, versionid, false
                from lpit_issue_affected_version
            ),
            issues as (
                select versionid, phase, isresolved, count(issueid) as total
                from lpit_issue
                join version_map using (issueid)
                join lpit_issue_phases using (status)
                group by versionid, phase, isresolved
            ),
            summary as (
                select versionid, phase, isresolved, total
                from lpit_version v
                left join issues using (versionid)
            )
            select v.versionid, project, name, node, ordinal, status,
                ro.total as resolved_open, ra.total as resolved_active, rd.total as resolved_done,
                ao.total as affected_open, aa.total as affected_active, ad.total as affected_done
            from lpit_version v
            left join summary ro on ro.versionid = v.versionid and ro.phase = 0 and ro.isresolved
            left join summary ra on ra.versionid = v.versionid and ra.phase = 1 and ra.isresolved
            left join summary rd on rd.versionid = v.versionid and rd.phase = 2 and rd.isresolved
            left join summary ao on ao.versionid = v.versionid and ao.phase = 0 and not ao.isresolved
            left join summary aa on aa.versionid = v.versionid and aa.phase = 1 and not aa.isresolved
            left join summary ad on ad.versionid = v.versionid and ad.phase = 2 and not ad.isresolved
            where v.project = ?
            order by ordinal desc, lower(name) desc
            """.trimIndent()
        ) {
            setInt(1, project.id)
            queryAll { rs ->
                VersionSummary(rs.extractVersion()).apply {
                    reportedTotal.open = rs.getInt("affected_open")
                    reportedTotal.active = rs.getInt("affected_active")
                    reportedTotal.done = rs.getInt("affected_done")
                    resolvedTotal.open = rs.getInt("resolved_open")
                    resolvedTotal.active = rs.getInt("resolved_active")
                    resolvedTotal.done = rs.getInt("resolved_done")
                }
            }
        }

    override fun findVersion(id: Int): Version? =
        withStatement("$versionQuery where versionid = ?") {
            setInt(1, id)
            querySingle { it.extractVersion() }
        }

    override fun findVersionByNode(project: Project, node: String): Version? =
        withStatement("$versionQuery where project = ? and node = ?") {
            setInt(1, project.id)
            setString(2, node)
            querySingle { it.extractVersion() }
        }

    override fun insertVersion(version: Version) {
        withStatement("insert into lpit_version (name, node, ordinal, status, project) values (?, ?, ?, ?::version_status, ?)") {
            with(version) {
                setStringSafe(1, name)
                setStringSafe(2, node)
                setInt(3, ordinal)
                setEnum(4, status)
                setInt(5, version.projectid)
            }
            executeUpdate()
        }

    }

    override fun updateVersion(version: Version) {
        withStatement("update lpit_version set name = ?, node = ?, ordinal = ?, status = ?::version_status where versionid = ?") {
            with(version) {
                setStringSafe(1, name)
                setStringSafe(2, node)
                setInt(3, ordinal)
                setEnum(4, status)
                setInt(5, id)
            }
            executeUpdate()
        }
    }
//</editor-fold>

    //<editor-fold desc="Component">
    //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
        """.trimIndent()

    private fun ResultSet.extractComponent(): Component =
        Component(getInt("id"), getInt("project")).apply {
            name = getString("name")
            node = getString("node")
            color = try {
                WebColor(getString("color"))
            } catch (ex: IllegalArgumentException) {
                WebColor("000000")
            }
            ordinal = getInt("ordinal")
            description = getString("description")
            lead = extractOptionalUser()
        }

    private fun PreparedStatement.setComponent(index: Int, component: Component): Int {
        with(component) {
            var i = index
            setStringSafe(i++, name)
            setStringSafe(i++, node)
            setStringSafe(i++, color.hex)
            setInt(i++, ordinal)
            setStringOrNull(i++, description)
            setIntOrNull(i++, lead?.id)
            return i
        }
    }

    override fun listComponents(project: Project): List<Component> =
        withStatement("$componentQuery where project = ? order by ordinal, lower(name)") {
            setInt(1, project.id)
            queryAll { it.extractComponent() }
        }

    override fun listComponentSummaries(project: Project): List<ComponentSummary> =
        withStatement(
            """
            with issues as (
                select component, phase, count(issueid) as total
                from lpit_issue
                join lpit_issue_phases using (status)
                group by component, phase
            ),
            summary as (
                select c.id, phase, total
                from lpit_component c
                left join issues i on c.id = i.component 
            )
            select c.id, project, name, node, color, ordinal, description,
                userid, username, givenname, lastname, mail,
                open.total as open, active.total as active, done.total as done
            from lpit_component c
            left join lpit_user on lead = userid
            left join summary open on c.id = open.id and open.phase = 0
            left join summary active on c.id = active.id and active.phase = 1
            left join summary done on c.id = done.id and done.phase = 2
            where c.project = ?
            order by ordinal, name
            """.trimIndent()
        ) {
            setInt(1, project.id)
            queryAll { rs ->
                ComponentSummary(rs.extractComponent()).apply {
                    issueSummary.open = rs.getInt("open")
                    issueSummary.active = rs.getInt("active")
                    issueSummary.done = rs.getInt("done")
                }
            }
        }

    override fun findComponent(id: Int): Component? =
        withStatement("$componentQuery where id = ?") {
            setInt(1, id)
            querySingle { it.extractComponent() }
        }

    override fun findComponentByNode(project: Project, node: String): Component? =
        withStatement("$componentQuery where project = ? and node = ?") {
            setInt(1, project.id)
            setString(2, node)
            querySingle { it.extractComponent() }
        }

    override fun insertComponent(component: Component) {
        withStatement("insert into lpit_component (name, node, color, ordinal, description, lead, project) values (?, ?, ?, ?, ?, ?, ?)") {
            val col = setComponent(1, component)
            setInt(col, component.projectid)
            executeUpdate()
        }
    }

    override fun updateComponent(component: Component) {
        withStatement("update lpit_component set name = ?, node = ?, color = ?, ordinal = ?, description = ?, lead = ? where id = ?") {
            val col = setComponent(1, component)
            setInt(col, component.id)
            executeUpdate()
        }
    }

//</editor-fold>

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

    //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
        """.trimIndent()

    private fun ResultSet.extractProject() =
        Project(getInt("projectid")).apply {
            name = getString("name")
            node = getString("node")
            ordinal = getInt("ordinal")
            description = getString("description")
            repoUrl = getString("repourl")
            owner = extractOptionalUser()
        }

    private fun PreparedStatement.setProject(index: Int, project: Project): Int {
        var i = index
        with(project) {
            setStringSafe(i++, name)
            setStringSafe(i++, node)
            setInt(i++, ordinal)
            setStringOrNull(i++, description)
            setStringOrNull(i++, repoUrl)
            setIntOrNull(i++, owner?.id)
        }
        return i
    }

    override fun listProjects(): List<Project> =
        withStatement("$projectQuery order by ordinal, lower(name)") {
            queryAll { it.extractProject() }
        }

    override fun findProject(id: Int): Project? =
        withStatement("$projectQuery where projectid = ?") {
            setInt(1, id)
            querySingle { it.extractProject() }
        }

    override fun findProjectByNode(node: String): Project? =
        withStatement("$projectQuery where node = ?") {
            setString(1, node)
            querySingle { it.extractProject() }
        }

    override fun insertProject(project: Project) {
        withStatement("insert into lpit_project (name, node, ordinal, description, repourl, owner) values (?, ?, ?, ?, ?, ?)") {
            setProject(1, project)
            executeUpdate()
        }
    }

    override fun updateProject(project: Project) {
        withStatement("update lpit_project set name = ?, node = ?, ordinal = ?, description = ?, repourl = ?, owner = ? where projectid = ?") {
            val col = setProject(1, project)
            setInt(col, project.id)
            executeUpdate()
        }
    }

    override fun collectIssueSummary(project: Project): IssueSummary =
        withStatement(
            """
            select phase, count(*) as total
            from lpit_issue
            join lpit_issue_phases using(status)
            where project = ?
            group by phase  
            """.trimIndent()
        ) {
            setInt(1, project.id)
            executeQuery().use {
                val summary = IssueSummary()
                while (it.next()) {
                    val phase = it.getInt("phase")
                    val total = it.getInt("total")
                    when (phase) {
                        0 -> summary.open = total
                        1 -> summary.active = total
                        2 -> summary.done = total
                    }
                }
                summary
            }
        }

//</editor-fold>

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

    //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 
        """.trimIndent()

    private fun ResultSet.extractIssue(): Issue {
        val proj = Project(getInt("project")).apply {
            name = getString("projectname")
            node = getString("projectnode")
        }
        val comp = getInt("component").let {
            if (wasNull()) null else
                Component(it, proj.id).apply {
                    name = getString("componentname")
                    node = getString("componentnode")
                }
        }
        val issue = Issue(getInt("issueid"), proj).apply {
            component = comp
            status = getEnum("status")
            category = getEnum("category")
            subject = getString("subject")
            description = getString("description")
            assignee = extractOptionalUser()
            created = getTimestamp("created")
            updated = getTimestamp("updated")
            eta = getDate("eta")
        }

        //language=SQL
        fun versionQuery(table: String) =
            """
            select versionid, project, name, status, ordinal, node
            from lpit_version join $table using (versionid)
            where issueid = ?
            order by ordinal, name
            """.trimIndent()

        issue.affectedVersions = withStatement(versionQuery("lpit_issue_affected_version")) {
            setInt(1, issue.id)
            queryAll { it.extractVersion() }
        }
        issue.resolvedVersions = withStatement(versionQuery("lpit_issue_resolved_version")) {
            setInt(1, issue.id)
            queryAll { it.extractVersion() }
        }
        return issue
    }

    private fun PreparedStatement.setIssue(index: Int, issue: Issue): Int {
        var i = index
        with(issue) {
            setIntOrNull(i++, component?.id)
            setEnum(i++, status)
            setEnum(i++, category)
            setStringSafe(i++, subject)
            setStringOrNull(i++, description)
            setIntOrNull(i++, assignee?.id)
            setDateOrNull(i++, eta)
        }
        return i
    }

    override fun listIssues(filter: IssueFilter): List<Issue> =
        withStatement(
            """
            with issue_version as (
                select issueid, versionid from lpit_issue_affected_version
                union select issueid, versionid from lpit_issue_resolved_version
            ),
            filtered_issues as (
                select distinct issueid from lpit_issue
                left join issue_version using (issueid)
                where
                (not ? or project = ?) and 
                (not ? or versionid = ?) and (not ? or versionid is null) and
                (not ? or component = ?) and (not ? or component is null)
            )
            $issueQuery join filtered_issues using (issueid)
            """.trimIndent()
        ) {
            fun <T : Entity> applyFilter(filter: Filter<T>, fflag: Int, nflag: Int, idcol: Int) {
                when (filter) {
                    is AllFilter -> {
                        setBoolean(fflag, false)
                        setBoolean(nflag, false)
                        setInt(idcol, 0)
                    }
                    is NoneFilter -> {
                        setBoolean(fflag, false)
                        setBoolean(nflag, true)
                        setInt(idcol, 0)
                    }
                    is SpecificFilter -> {
                        setBoolean(fflag, true)
                        setBoolean(nflag, false)
                        setInt(idcol, filter.obj.id)
                    }
                    else -> {
                        TODO("Implement range filter.")
                    }
                }
            }
            when (filter.project) {
                is AllFilter -> {
                    setBoolean(1, false)
                    setInt(2, 0)
                }
                is SpecificFilter -> {
                    setBoolean(1, true)
                    setInt(2, filter.project.obj.id)
                }
                else -> throw IllegalArgumentException()
            }
            applyFilter(filter.version, 3, 5, 4)
            applyFilter(filter.component, 6, 8, 7)

            queryAll { it.extractIssue() }
        }

    override fun findIssue(id: Int): Issue? =
        withStatement("$issueQuery where issueid = ?") {
            setInt(1, id)
            querySingle { it.extractIssue() }
        }

    private fun insertVersionInfo(id: Int, issue: Issue) {
        withStatement("insert into lpit_issue_affected_version (issueid, versionid) values (?,?)") {
            setInt(1, id)
            issue.affectedVersions.forEach {
                setInt(2, it.id)
                executeUpdate()
            }
        }
        withStatement("insert into lpit_issue_resolved_version (issueid, versionid) values (?,?)") {
            setInt(1, id)
            issue.resolvedVersions.forEach {
                setInt(2, it.id)
                executeUpdate()
            }
        }
    }

    override fun insertIssue(issue: Issue): Int {
        val id = withStatement(
            """
            insert into lpit_issue (component, status, category, subject, description, assignee, eta, project)
            values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?)
            returning issueid
            """.trimIndent()
        ) {
            val col = setIssue(1, issue)
            setInt(col, issue.project.id)
            querySingle { it.getInt(1) }!!
        }
        insertVersionInfo(id, issue)
        return id
    }

    override fun updateIssue(issue: Issue) {
        withStatement(
            """
            update lpit_issue set updated = now(),
                component = ?, status = ?::issue_status, category = ?::issue_category, subject = ?,
                description = ?, assignee = ?, eta = ?
            where issueid = ?
            """.trimIndent()
        ) {
            val col = setIssue(1, issue)
            setInt(col, issue.id)
            executeUpdate()
        }

        // TODO: improve by only inserting / deleting changed version information
        withStatement("delete from lpit_issue_affected_version where issueid = ?") {
            setInt(1, issue.id)
            executeUpdate()
        }
        withStatement("delete from lpit_issue_resolved_version where issueid = ?") {
            setInt(1, issue.id)
            executeUpdate()
        }
        insertVersionInfo(issue.id, issue)
    }

//</editor-fold>

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

    private fun ResultSet.extractIssueComment() =
        IssueComment(getInt("commentid"), getInt("issueid")).apply {
            created = getTimestamp("created")
            updated = getTimestamp("updated")
            updateCount = getInt("updatecount")
            comment = getString("comment")
            author = extractOptionalUser()
        }

    override fun listComments(issue: Issue): List<IssueComment> =
        withStatement("select * from lpit_issue_comment left join lpit_user using (userid) where issueid = ? order by created") {
            setInt(1, issue.id)
            queryAll { it.extractIssueComment() }
        }

    override fun findComment(id: Int): IssueComment? =
        withStatement("select * from lpit_issue_comment left join lpit_user using (userid) where commentid = ?") {
            setInt(1, id)
            querySingle { it.extractIssueComment() }
        }

    override fun insertComment(issueComment: IssueComment) {
        useStatement("update lpit_issue set updated = now() where issueid = ?") { updateIssueDate ->
            withStatement("insert into lpit_issue_comment (issueid, comment, userid) values (?, ? ,?)") {
                with(issueComment) {
                    updateIssueDate.setInt(1, issueid)
                    setInt(1, issueid)
                    setStringSafe(2, comment)
                    setIntOrNull(3, author?.id)
                }
                executeUpdate()
                updateIssueDate.executeUpdate()
            }
        }
    }

    override fun updateComment(issueComment: IssueComment) {
        useStatement("update lpit_issue set updated = now() where issueid = ?") { updateIssueDate ->
            withStatement("update lpit_issue_comment set comment = ?, updatecount = updatecount + 1, updated = now() where commentid = ?") {
                with(issueComment) {
                    updateIssueDate.setInt(1, issueid)
                    setStringSafe(1, comment)
                    setInt(2, id)
                }
                executeUpdate()
                updateIssueDate.executeUpdate()
            }
        }
    }
//</editor-fold>
}