2 files changed, 272 insertions, 3 deletions
diff --git a/gn3/auth/authorisation/groups/data.py b/gn3/auth/authorisation/groups/data.py
new file mode 100644
index 0000000..93b8e1d
--- /dev/null
+++ b/gn3/auth/authorisation/groups/data.py
@@ -0,0 +1,182 @@
+"""Handles the resource objects' data."""
+from typing import Any, Sequence
+
+from MySQLdb.cursors import DictCursor
+
+from gn3 import db_utils as gn3db
+from gn3.auth import db as authdb
+from gn3.auth.authorisation.groups import Group
+from gn3.auth.authorisation.checks import authorised_p
+from gn3.auth.authorisation.errors import InvalidData, NotFoundError
+
+def __fetch_grouped_data__(
+        conn: authdb.DbConnection, dataset_type: str) -> Sequence[dict[str, Any]]:
+    """Retrieve ids for all data that are linked to groups in the auth db."""
+    with authdb.cursor(conn) as cursor:
+        cursor.execute(
+            "SELECT dataset_type, dataset_or_trait_id FROM linked_group_data "
+            "WHERE LOWER(dataset_type)=?",
+            (dataset_type,))
+        return tuple(dict(row) for row in cursor.fetchall())
+
+def __fetch_ungrouped_mrna_data__(
+        conn: gn3db.Connection, grouped_data,
+        offset: int = 0) -> Sequence[dict]:
+    """Fetch ungrouped mRNA Assay data."""
+    query = ("SELECT psf.Id, psf.Name, psf.FullName, "
+             "ifiles.GN_AccesionId AS accession_id FROM ProbeSetFreeze AS psf "
+             "INNER JOIN InfoFiles AS ifiles ON psf.Name=ifiles.InfoPageName")
+    params: tuple[Any, ...] = tuple()
+    if bool(grouped_data):
+        clause = ", ".join(["%s"] * len(grouped_data))
+        query = f"{query} WHERE psf.Id NOT IN ({clause})"
+        params = tuple(item["dataset_or_trait_id"] for item in grouped_data)
+
+    query = f"{query} LIMIT 100 OFFSET %s"
+    with conn.cursor(DictCursor) as cursor:
+        cursor.execute(query, (params + (offset,)))
+        return tuple(dict(row) for row in cursor.fetchall())
+
+def __fetch_ungrouped_geno_data__(
+        conn: gn3db.Connection, grouped_data,
+        offset: int = 0) -> Sequence[dict]:
+    """Fetch ungrouped Genotype data."""
+    query = ("SELECT gf.Id, gf.Name, gf.FullName, "
+             "ifiles.GN_AccesionId AS accession_id FROM GenoFreeze AS gf "
+             "INNER JOIN InfoFiles AS ifiles ON gf.Name=ifiles.InfoPageName")
+    params: tuple[Any, ...] = tuple()
+    if bool(grouped_data):
+        clause = ", ".join(["%s"] * len(grouped_data))
+        query = f"{query} WHERE gf.Id NOT IN ({clause})"
+        params = tuple(item["dataset_or_trait_id"] for item in grouped_data)
+
+    query = f"{query} LIMIT 100 OFFSET %s"
+    with conn.cursor(DictCursor) as cursor:
+        cursor.execute(query, (params + (offset,)))
+        return tuple(dict(row) for row in cursor.fetchall())
+
+def __fetch_ungrouped_pheno_data__(
+        conn: gn3db.Connection, grouped_data,
+        offset: int = 0) -> Sequence[dict]:
+    """Fetch ungrouped Phenotype data."""
+    query = ("SELECT pf.Id, pf.Name, pf.FullName, "
+             "ifiles.GN_AccesionId AS accession_id FROM PublishFreeze AS pf "
+             "INNER JOIN InfoFiles AS ifiles ON pf.Name=ifiles.InfoPageName")
+    params: tuple[Any, ...] = tuple()
+    if bool(grouped_data):
+        clause = ", ".join(["%s"] * len(grouped_data))
+        query = f"{query} WHERE pf.Id NOT IN ({clause})"
+        params = tuple(item["dataset_or_trait_id"] for item in grouped_data)
+
+    query = f"{query} LIMIT 100 OFFSET %s"
+    with conn.cursor(DictCursor) as cursor:
+        cursor.execute(query, (params + (offset,)))
+        return tuple(dict(row) for row in cursor.fetchall())
+
+def __fetch_ungrouped_data__(
+        conn: gn3db.Connection, dataset_type: str,
+        ungrouped: Sequence[dict[str, Any]]) -> Sequence[dict[str, Any]]:
+    """Fetch any ungrouped data."""
+    fetch_fns = {
+        "mrna": __fetch_ungrouped_mrna_data__,
+        "genotype": __fetch_ungrouped_geno_data__,
+        "phenotype": __fetch_ungrouped_pheno_data__
+    }
+    return fetch_fns[dataset_type](conn, ungrouped)
+
+@authorised_p(("system:data:link-to-group",),
+              error_description=(
+                  "You do not have sufficient privileges to link data to (a) "
+                  "group(s)."),
+              oauth2_scope="profile group resource")
+def retrieve_ungrouped_data(
+        authconn: authdb.DbConnection,
+        gn3conn: gn3db.Connection,
+        dataset_type: str) -> Sequence[dict]:
+    """Retrieve any data not linked to any group."""
+    if dataset_type not in ("mrna", "genotype", "phenotype"):
+        raise InvalidData(
+            "Requested dataset type is invalid. Expected one of "
+            "'mrna', 'genotype' or 'phenotype'.")
+    grouped_data = __fetch_grouped_data__(authconn, dataset_type)
+    return __fetch_ungrouped_data__(gn3conn, dataset_type, grouped_data)
+
+def __fetch_mrna_data_by_id__(conn: gn3db.Connection, dataset_id: str) -> dict:
+    """Fetch mRNA Assay data by ID."""
+    with conn.cursor(DictCursor) as cursor:
+        cursor.execute(
+            "SELECT psf.Id, psf.Name, psf.FullName, "
+            "ifiles.GN_AccesionId AS accession_id FROM ProbeSetFreeze AS psf "
+            "INNER JOIN InfoFiles AS ifiles ON psf.Name=ifiles.InfoPageName "
+            "WHERE psf.Id=%s",
+            (dataset_id,))
+        res = cursor.fetchone()
+        if res:
+            return dict(res)
+        raise NotFoundError("Could not find mRNA Assay data with the given ID.")
+
+def __fetch_geno_data_by_id__(conn: gn3db.Connection, dataset_id: str) -> dict:
+    """Fetch genotype data by ID."""
+    with conn.cursor(DictCursor) as cursor:
+        cursor.execute(
+            "SELECT gf.Id, gf.Name, gf.FullName, "
+            "ifiles.GN_AccesionId AS accession_id FROM GenoFreeze AS gf "
+            "INNER JOIN InfoFiles AS ifiles ON gf.Name=ifiles.InfoPageName "
+            "WHERE gf.Id=%s",
+            (dataset_id,))
+        res = cursor.fetchone()
+        if res:
+            return dict(res)
+        raise NotFoundError("Could not find Genotype data with the given ID.")
+
+def __fetch_pheno_data_by_id__(conn: gn3db.Connection, dataset_id: str) -> dict:
+    """Fetch phenotype data by ID."""
+    with conn.cursor(DictCursor) as cursor:
+        cursor.execute(
+            "SELECT pf.Id, pf.Name, pf.FullName, "
+            "ifiles.GN_AccesionId AS accession_id FROM PublishFreeze AS pf "
+            "INNER JOIN InfoFiles AS ifiles ON pf.Name=ifiles.InfoPageName "
+            "WHERE pf.Id=%s",
+            (dataset_id,))
+        res = cursor.fetchone()
+        if res:
+            return dict(res)
+        raise NotFoundError(
+            "Could not find Phenotype/Publish data with the given ID.")
+
+def __fetch_data_by_id(
+        conn: gn3db.Connection, dataset_type: str, dataset_id: str) -> dict:
+    """Fetch data from MySQL by ID."""
+    fetch_fns = {
+        "mrna": __fetch_mrna_data_by_id__,
+        "genotype": __fetch_geno_data_by_id__,
+        "phenotype": __fetch_pheno_data_by_id__
+    }
+    return fetch_fns[dataset_type](conn, dataset_id)
+
+@authorised_p(("system:data:link-to-group",),
+              error_description=(
+                  "You do not have sufficient privileges to link data to (a) "
+                  "group(s)."),
+              oauth2_scope="profile group resource")
+def link_data_to_group(
+        authconn: authdb.DbConnection, gn3conn: gn3db.Connection,
+        dataset_type: str, dataset_id: str, group: Group) -> dict:
+    """Link the given data to the specified group."""
+    the_data = __fetch_data_by_id(gn3conn, dataset_type, dataset_id)
+    with authdb.cursor(authconn) as cursor:
+        params = {
+            "group_id": str(group.group_id), "dataset_type": {
+                "mrna": "mRNA", "genotype": "Genotype",
+                "phenotype": "Phenotype"
+            }[dataset_type],
+            "dataset_or_trait_id": dataset_id, "dataset_name": the_data["Name"],
+            "dataset_fullname": the_data["FullName"],
+            "accession_id": the_data["accession_id"]
+        }
+        cursor.execute(
+            "INSERT INTO linked_group_data VALUES"
+            "(:group_id, :dataset_type, :dataset_or_trait_id, :dataset_name, "
+            ":dataset_fullname, :accession_id)",
+            params)
+        return params
diff --git a/gn3/auth/authorisation/groups/views.py b/gn3/auth/authorisation/groups/views.py
index d3710f6..d21466c 100644
--- a/gn3/auth/authorisation/groups/views.py
+++ b/gn3/auth/authorisation/groups/views.py
@@ -6,15 +6,18 @@ from functools import partial
 from flask import request, jsonify, Response, Blueprint, current_app
 
 from gn3.auth import db
+from gn3 import db_utils as gn3dbutils
+
 from gn3.auth.dictify import dictify
 from gn3.auth.db_utils import with_db_connection
 
+from .data import link_data_to_group, retrieve_ungrouped_data
 from .models import (
-    user_group, all_groups, join_requests, GroupCreationError,
-    accept_reject_join_request, group_users as _group_users,
+    user_group, all_groups, DUMMY_GROUP, group_by_id, join_requests,
+    GroupCreationError, accept_reject_join_request, group_users as _group_users,
     create_group as _create_group)
 
-from ..errors import AuthorisationError
+from ..errors import InvalidData, AuthorisationError
 
 from ...authentication.users import User
 from ...authentication.oauth2.resource_server import require_oauth
@@ -128,3 +131,87 @@ def reject_join_requests() -> Response:
         return jsonify(with_db_connection(partial(
             accept_reject_join_request, request_id=request_id,
             user=the_token.user, status="REJECTED")))
+
+@groups.route("/<string:resource_type>/unlinked-data")
+@require_oauth("profile group resource")
+def unlinked_data(resource_type: str) -> Response:
+    """View data linked to the group but not linked to any resource."""
+    if resource_type not in ("all", "mrna", "genotype", "phenotype"):
+        raise AuthorisationError(f"Invalid resource type {resource_type}")
+
+    with require_oauth.acquire("profile group resource") as the_token:
+        db_uri = current_app.config["AUTH_DB"]
+        with db.connection(db_uri) as conn, db.cursor(conn) as cursor:
+            ugroup = user_group(cursor, the_token.user).maybe(# type: ignore[misc]
+                DUMMY_GROUP, lambda grp: grp)
+            if ugroup == DUMMY_GROUP:
+                return jsonify(tuple())
+            type_filter = {
+                "all": "",
+                "mrna": 'WHERE dataset_type="mRNA"',
+                "genotype": 'WHERE dataset_type="Genotype"',
+                "phenotype": 'WHERE dataset_type="Phenotype"'
+            }[resource_type]
+
+            except_filter = (
+                "SELECT group_id, dataset_type, "
+                "dataset_id AS dataset_or_trait_id FROM mrna_resources "
+                "UNION "
+                "SELECT group_id, dataset_type, "
+                "trait_id AS dataset_or_trait_id FROM genotype_resources "
+                "UNION "
+                "SELECT group_id, dataset_type, "
+                "trait_id AS dataset_or_trait_id FROM phenotype_resources")
+
+            ids_query = ("SELECT group_id, dataset_type, dataset_or_trait_id "
+                         "FROM linked_group_data "
+                         f"{type_filter} "
+                         f"EXCEPT {except_filter} ")
+            cursor.execute(ids_query)
+            ids = cursor.fetchall()
+
+            if ids:
+                clause = ", ".join(["(?, ?, ?)"] * len(ids))
+                data_query = (
+                    "SELECT * FROM linked_group_data "
+                    "WHERE (group_id, dataset_type, dataset_or_trait_id) "
+                    f"IN (VALUES {clause})")
+                params = tuple(item for sublist in
+                               ((row[0], row[1], row[2]) for row in ids)
+                               for item in sublist)
+                cursor.execute(data_query, params)
+                return jsonify(tuple(dict(row) for row in cursor.fetchall()))
+
+    return jsonify(tuple())
+
+@groups.route("/<string:dataset_type>/ungrouped-data", methods=["GET"])
+@require_oauth("profile group resource")
+def ungrouped_data(dataset_type: str) -> Response:
+    """View data not linked to any group."""
+    if dataset_type not in ("all", "mrna", "genotype", "phenotype"):
+        raise AuthorisationError(f"Invalid dataset type {dataset_type}")
+
+    with require_oauth.acquire("profile group resource") as _the_token:
+        with gn3dbutils.database_connection() as gn3conn:
+            return jsonify(with_db_connection(partial(
+                retrieve_ungrouped_data, gn3conn=gn3conn,
+                dataset_type=dataset_type)))
+
+@groups.route("/data/link", methods=["POST"])
+@require_oauth("profile group resource")
+def link_data() -> Response:
+    """Link selected data to specified group."""
+    with require_oauth.acquire("profile group resource") as _the_token:
+        form = request.form
+        group_id = uuid.UUID(form["group_id"])
+        dataset_id = form["dataset_id"]
+        dataset_type = form.get("dataset_type")
+        if dataset_type not in ("mrna", "genotype", "phenotype"):
+            raise InvalidData("Unexpected dataset type requested!")
+        def __link__(conn: db.DbConnection):
+            group = group_by_id(conn, group_id)
+            with gn3dbutils.database_connection() as gn3conn:
+                return link_data_to_group(
+                    conn, gn3conn, dataset_type, dataset_id, group)
+
+        return jsonify(with_db_connection(__link__))