From c33cfc004e3b7f3e1dc52d3e89c785a175edd0d6 Mon Sep 17 00:00:00 2001
From: John Nduli
Date: Mon, 26 Aug 2024 20:40:49 +0300
Subject: feat: add api calls to get categories and last comment

---
 gn3/api/metadata_api/wiki.py | 25 +++++++++++++++++++++++++
 gn3/db/wiki.py               | 44 ++++++++++++++++++++++++++++++++++++++++----
 2 files changed, 65 insertions(+), 4 deletions(-)

diff --git a/gn3/api/metadata_api/wiki.py b/gn3/api/metadata_api/wiki.py
index 1e566cd..32872f3 100644
--- a/gn3/api/metadata_api/wiki.py
+++ b/gn3/api/metadata_api/wiki.py
@@ -85,3 +85,28 @@ def get_wiki_entries(symbol: str):
         payload.headers["Content-Type"] = "application/ld+json"
         return payload, status_code
     return jsonify(data), status_code
+
+
+@wiki_blueprint.route("/<int:comment_id>", methods=["GET"])
+def get_wiki(comment_id: int):
+    with db_utils.database_connection(current_app.config["SQL_URI"]) as conn:
+        return jsonify(wiki.get_latest_comment(conn, comment_id))
+    return jsonify(error="Error editting wiki entry, most likely due to DB error!"), 500
+
+
+@wiki_blueprint.route("/categories", methods=["GET"])
+def get_categories():
+    with db_utils.database_connection(current_app.config["SQL_URI"]) as conn:
+        cursor = conn.cursor()
+        categories_dict = wiki.get_categories(cursor)
+        return jsonify(categories_dict)
+    return jsonify(error="Error editting wiki entry, most likely due to DB error!"), 500
+
+
+@wiki_blueprint.route("/species", methods=["GET"])
+def get_species():
+    with db_utils.database_connection(current_app.config["SQL_URI"]) as conn:
+        cursor = conn.cursor()
+        species_dict = wiki.get_species(cursor)
+        return jsonify(species_dict)
+    return jsonify(error="Error editting wiki entry, most likely due to DB error!"), 500
diff --git a/gn3/db/wiki.py b/gn3/db/wiki.py
index c3cfee0..f2984af 100644
--- a/gn3/db/wiki.py
+++ b/gn3/db/wiki.py
@@ -1,12 +1,38 @@
 """Helper functions to access wiki entries"""
 
-from typing import List
+from typing import Dict, List
+
+from MySQLdb.cursors import DictCursor
 
 
 class MissingDBDataException(Exception):
     """Error due to DB missing some data"""
 
 
+def get_latest_comment(connection, comment_id: str) -> int:
+    cursor = connection.cursor(DictCursor)
+    query = """ SELECT versionId AS version, symbol, PubMed_ID AS pubmed_ids, sp.Name AS species,
+        comment, email, weburl, initial, reason
+        FROM `GeneRIF` gr
+		INNER JOIN Species sp USING(SpeciesId)
+		WHERE gr.Id = %s
+		ORDER BY versionId DESC LIMIT 1;
+    """
+    cursor.execute(query, (comment_id,))
+    result = cursor.fetchone()
+    result["pubmed_ids"] = [x.strip() for x in result["pubmed_ids"].split()]
+    categories_query = """
+        SELECT grx.GeneRIFId, grx.versionId, gc.Name FROM GeneRIFXRef grx
+                INNER JOIN GeneCategory gc ON grx.GeneCategoryId=gc.Id
+                WHERE GeneRIFId = %s AND versionId=%s;
+    """
+
+    cursor.execute(categories_query, (comment_id, result["version"]))
+    categories = cursor.fetchall()
+    result["categories"] = [x["Name"] for x in categories]
+    return result
+
+
 def get_species_id(cursor, species_name: str) -> int:
     """Find species id given species `Name`"""
     cursor.execute("SELECT SpeciesID from Species  WHERE Name = %s", (species_name,))
@@ -31,9 +57,7 @@ def get_next_comment_version(cursor, comment_id: int) -> int:
 
 def get_categories_ids(cursor, categories: List[str]) -> List[int]:
     """Get the categories_ids from a list of category strings"""
-    cursor.execute("SELECT Name, Id from GeneCategory")
-    raw_categories = cursor.fetchall()
-    dict_cats = dict(raw_categories)
+    dict_cats = get_categories(cursor)
     category_ids = []
     for category in set(categories):
         cat_id = dict_cats.get(category.strip())
@@ -41,3 +65,15 @@ def get_categories_ids(cursor, categories: List[str]) -> List[int]:
             raise MissingDBDataException(f"Category with Name={category} not found")
         category_ids.append(cat_id)
     return category_ids
+
+def get_categories(cursor) -> Dict[str, int]:
+    cursor.execute("SELECT Name, Id from GeneCategory")
+    raw_categories = cursor.fetchall()
+    dict_cats = dict(raw_categories)
+    return dict_cats
+
+def get_species(cursor) -> Dict[str, str]:
+    cursor.execute("SELECT Name, SpeciesName from Species")
+    raw_species = cursor.fetchall()
+    dict_cats = dict(raw_species)
+    return dict_cats
-- 
cgit v1.2.3