# HG changeset patch
# User Mike Becker <universe@uap-core.de>
# Date 1738498407 -3600
# Node ID 41cf84e10aeb251add585ea909da7508c06cf581
# Parent  1dc9c405e9e2b6c378e5abf834c8e26c06ac154c
implement query for variant status

relates to #491

diff -r 1dc9c405e9e2 -r 41cf84e10aeb src/main/kotlin/de/uapcore/lightpit/dao/DataAccessObject.kt
--- a/src/main/kotlin/de/uapcore/lightpit/dao/DataAccessObject.kt	Sat Feb 01 18:52:08 2025 +0100
+++ b/src/main/kotlin/de/uapcore/lightpit/dao/DataAccessObject.kt	Sun Feb 02 13:13:27 2025 +0100
@@ -89,9 +89,9 @@
 
     /**
      * Lists issues for the specified [project].
-     * The result will only [includeDone] issues, if requested.
+     * This list will NOT include variant data and is intended for simple lookups.
      */
-    fun listIssues(project: Project, includeDone: Boolean): List<Issue>
+    fun listIssues(project: Project): List<Issue>
 
     /**
      * Lists all issues for the specified [project].
diff -r 1dc9c405e9e2 -r 41cf84e10aeb src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt
--- a/src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt	Sat Feb 01 18:52:08 2025 +0100
+++ b/src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt	Sun Feb 02 13:13:27 2025 +0100
@@ -26,10 +26,7 @@
 package de.uapcore.lightpit.dao
 
 import de.uapcore.lightpit.entities.*
-import de.uapcore.lightpit.types.CommitRef
-import de.uapcore.lightpit.types.IssueHistoryType
-import de.uapcore.lightpit.types.RelationType
-import de.uapcore.lightpit.types.WebColor
+import de.uapcore.lightpit.types.*
 import de.uapcore.lightpit.viewmodel.ComponentSummary
 import de.uapcore.lightpit.viewmodel.IssueSummary
 import de.uapcore.lightpit.viewmodel.VariantSummary
@@ -685,17 +682,69 @@
         return i
     }
 
-    override fun listIssues(includeDone: Boolean): List<Issue> =
-        withStatement("$issueQuery where (? or phase < 2)") {
+    override fun listIssues(includeDone: Boolean): List<Issue> {
+        val issues = withStatement("$issueQuery where (? or phase < 2) order by issueid") {
             setBoolean(1, includeDone)
             queryAll { it.extractIssue() }
         }
+        addVariantData(issues, includeDone)
+        return issues
+    }
 
-    override fun listIssues(project: Project, includeDone: Boolean): List<Issue> =
-        withStatement("$issueQuery where i.project = ? and (? or phase < 2)") {
+    /**
+     * Queries variant status data for all [issues].
+     * For performance reasons issues can be filtered to not [includeDone] issues or to include
+     * only issues for a certain [project].
+     * Attention: this method is optimized and requires that [issues] is ordered by issue id!
+     */
+    private fun addVariantData(issues: List<Issue>, includeDone: Boolean, project: Project? = null) {
+        if (issues.isEmpty()) return
+        // it is probably faster to simply query all variant status linked
+        // to issues of a certain project instead of applying possible component/version filters
+        // in any case, it is simpler...
+        withStatement(
+            """
+            select issueid, s.status,
+                v.id, v.project, v.name, v.node, v.color, v.ordinal, v.description, v.active
+            from lpit_issue_variant_status s
+            join lpit_variant v on v.id = s.variant
+            join lpit_issue i using (issueid)
+            join lpit_issue_phases ph on ph.status = i.status
+            where (? or phase < 2) and (? or i.project = ?)
+            order by issueid
+            """.trimIndent()
+        ) {
+            setBoolean(1, includeDone)
+            setBoolean(2, project == null)
+            setInt(3, project?.id ?: 0)
+            val variantCache = mutableMapOf<Int, Variant>()
+            val issueIter = issues.listIterator()
+            var issue = issueIter.next()
+            executeQuery().let { rs ->
+                while (rs.next()) {
+                    val issueid = rs.getInt("issueid")
+                    val variantid = rs.getInt("id")
+                    val variant = variantCache.getOrPut(variantid) { rs.extractVariant() }
+                    val status: IssueStatus = rs.getEnum("status")
+                    // this loop uses that both queries ordered their results by issue id
+                    while (true) {
+                        if (issue.id == issueid) {
+                            issue.setVariantStatus(variant, status)
+                            break
+                        }
+                        if (!issueIter.hasNext()) break
+                        issue = issueIter.next()
+                    }
+                }
+            }
+        }
+    }
+
+    override fun listIssues(project: Project): List<Issue> =
+        withStatement("$issueQuery where i.project = ?") {
             setInt(1, project.id)
-            setBoolean(2, includeDone)
             queryAll { it.extractIssue() }
+            // do not add variant data here - not needed in this use case!
         }
 
     override fun listIssues(
@@ -743,7 +792,13 @@
             sql ="$sql and variants = 0"
         }
 
-        return withStatement(sql) {
+        // ordering is required when variant data must be added
+        if (!specificVariant) {
+            // language=SQL
+            sql = "$sql order by issueid"
+        }
+
+        val issues = withStatement(sql) {
             setInt(1, project.id)
             setBoolean(2, includeDone)
 
@@ -761,6 +816,10 @@
 
             queryAll { it.extractIssue() }
         }
+        if (!specificVariant) {
+            addVariantData(issues, includeDone)
+        }
+        return issues
     }
 
     override fun findIssue(id: Int): Issue? =
diff -r 1dc9c405e9e2 -r 41cf84e10aeb src/main/kotlin/de/uapcore/lightpit/entities/Issue.kt
--- a/src/main/kotlin/de/uapcore/lightpit/entities/Issue.kt	Sat Feb 01 18:52:08 2025 +0100
+++ b/src/main/kotlin/de/uapcore/lightpit/entities/Issue.kt	Sun Feb 02 13:13:27 2025 +0100
@@ -48,6 +48,18 @@
     var affected: Version? = null
     var resolved: Version? = null
 
+    val isTrackingVariantStatus get() = variantStatus.isNotEmpty()
+    fun setVariantStatus(variant: Variant, status: IssueStatus) {
+        variantStatus[variant] = status
+    }
+    fun removeVariant(variant: Variant) {
+        variantStatus.remove(variant)
+    }
+    fun getVariantStatus(variant: Variant): IssueStatus? {
+        return variantStatus[variant]
+    }
+    private val variantStatus = mutableMapOf<Variant, IssueStatus>()
+
     /**
      * An issue is overdue, if it is not done and the ETA is before the current time.
      */
diff -r 1dc9c405e9e2 -r 41cf84e10aeb src/main/kotlin/de/uapcore/lightpit/logic/IssueLogic.kt
--- a/src/main/kotlin/de/uapcore/lightpit/logic/IssueLogic.kt	Sat Feb 01 18:52:08 2025 +0100
+++ b/src/main/kotlin/de/uapcore/lightpit/logic/IssueLogic.kt	Sun Feb 02 13:13:27 2025 +0100
@@ -128,7 +128,7 @@
         view = IssueDetailView(
             issue,
             comments,
-            dao.listIssues(issue.project, true),
+            dao.listIssues(issue.project),
             dao.listIssueRelations(issue),
             dao.listCommitRefs(issue),
             relationError,
diff -r 1dc9c405e9e2 -r 41cf84e10aeb src/main/kotlin/de/uapcore/lightpit/servlet/ProjectServlet.kt
--- a/src/main/kotlin/de/uapcore/lightpit/servlet/ProjectServlet.kt	Sat Feb 01 18:52:08 2025 +0100
+++ b/src/main/kotlin/de/uapcore/lightpit/servlet/ProjectServlet.kt	Sun Feb 02 13:13:27 2025 +0100
@@ -188,7 +188,7 @@
             }
 
             // obtain the list of issues for this project to filter cross-project references
-            val knownIds = dao.listIssues(path.project, true).map { it.id }
+            val knownIds = dao.listIssues(path.project).map { it.id }
 
             // read the provided commit log and merge only the refs that relate issues from the current project
             dao.mergeCommitRefs(parseCommitRefs(http.body).filter { knownIds.contains(it.issueId) })