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, release, eol from lpit_version"

    private fun ResultSet.extractVersion() =
        Version(getInt("versionid"), getInt("project")).apply {
            name = getString("name")
            node = getString("node")
            ordinal = getInt("ordinal")
            release = getDate("release")
            eol = getDate("eol")
            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 as (
                select issueid, status, resolved as versionid, true as isresolved from lpit_issue
                union all
                select issueid, status, affected as versionid, false as isresolved from lpit_issue
            ), issues as (
                select versionid, phase, isresolved, count(issueid) as total from version_map
                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, release, eol,
                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, release, eol) values (?, ?, ?, ?::version_status, ?, ?, ?)") {
            with(version) {
                setStringSafe(1, name)
                setStringSafe(2, node)
                setInt(3, ordinal)
                setEnum(4, status)
                setInt(5, version.projectid)
                setDateOrNull(6, version.release)
                setDateOrNull(7, version.eol)
            }
            executeUpdate()
        }

    }

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

    //<editor-fold desc="Component">
    //language=SQL
    private val componentQuery =
        """
        select id, project, name, node, color, ordinal, description, active,
            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")
            active = getBoolean("active")
            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)
            setBoolean(i++, active)
            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, active,
                userid, username, givenname, lastname, mail,
                open.total as open, wip.total as wip, 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 wip on c.id = wip.id and wip.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("wip")
                    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, active, 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 = ?, active = ?, 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, affected, resolved
        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")
            affected = getInt("affected").takeIf { it > 0 }?.let { findVersion(it) }
            resolved = getInt("resolved").takeIf { it > 0 }?.let { findVersion(it) }
        }

        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)
            setIntOrNull(i++, affected?.id)
            setIntOrNull(i++, resolved?.id)
        }
        return i
    }

    override fun listIssues(filter: IssueFilter): List<Issue> =
        withStatement(
            """$issueQuery where
                (not ? or i.project = ?) and 
                (not ? or ? in (resolved, affected)) and (not ? or (resolved is null and affected is null)) and
                (not ? or component = ?) and (not ? or component is null)
            """.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() }
        }

    override fun insertIssue(issue: Issue): Int {
        val id = withStatement(
            """
            insert into lpit_issue (component, status, category, subject, description, assignee, eta, affected, resolved, project)
            values (?, ?::issue_status, ?::issue_category, ?, ?, ?, ?, ?)
            returning issueid
            """.trimIndent()
        ) {
            val col = setIssue(1, issue)
            setInt(col, issue.project.id)
            querySingle { it.getInt(1) }!!
        }
        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 = ?, affected = ?, resolved = ?
            where issueid = ?
            """.trimIndent()
        ) {
            val col = setIssue(1, issue)
            setInt(col, issue.id)
            executeUpdate()
        }
    }

    //</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>
}