Add function for adding a change.

Signed-off-by: Munyoki Kilyungi <me@bonfacemunyoki.com>

2 files changed, 218 insertions, 1 deletions

diff --git a/gn3/db/case_attributes.py b/gn3/db/case_attributes.py
index 4bdb654..0c3d115 100644
--- a/gn3/db/case_attributes.py
+++ b/gn3/db/case_attributes.py
@@ -123,7 +123,7 @@ def update_case_attribute(cursor, directory: Path,
                     review_ids = pickle.loads(reviews)
                 if approvals := txn.get(b"approved"):
                     approved_ids = pickle.loads(approvals)
-                review_ids.remove(change_id)
+                review_ids.discard(change_id)
                 approved_ids.add(change_id)
                 txn.put(b"review", pickle.dumps(review_ids))
                 txn.put(b"approved", pickle.dumps(approved_ids))
@@ -186,3 +186,100 @@ def get_changes(cursor, inbredset_id: int, directory: Path) -> dict:
         "approvals": approvals,
         "rejections": rejections
     }
+
+
+def apply_change(cursor, change_type: EditStatus, change_id: int, directory: Path) -> bool:
+    review_ids, approved_ids, rejected_ids = set(), set(), set()
+    env = lmdb.open(directory, map_size=8_000_000)  # 1 MB
+    with env.begin(write=True) as txn:
+        if reviews := txn.get(b"review"):
+            review_ids = pickle.loads(reviews)
+        if change_id not in review_ids:
+            return False
+        match change_type:
+            case EditStatus.rejected:
+                cursor.execute(
+                    "UPDATE caseattributes_audit "
+                    "SET status = %s "
+                    "WHERE id = %s",
+                    (str(change_type), change_id))
+                review_ids.discard(change_id)
+                rejected_ids.add(change_id)
+                txn.put(b"review", pickle.dumps(review_ids))
+                txn.put(b"rejected", pickle.dumps(rejected_ids))
+                return True
+            case EditStatus.approved:
+                cursor.execute(
+                    "SELECT json_diff_data "
+                    "FROM caseattributes_audit WHERE "
+                    "id = %s",
+                    (change_id,)
+                )
+                json_diff_data, _ = cursor.fetchone()
+                json_diff_data = json.loads(json_diff_data)
+                inbredset_id = json_diff_data.get("inbredset_id")
+                modifications = json_diff_data.get(
+                    "Modifications", {}).get("Current", {})
+                strains = tuple(modifications.keys())
+                case_attrs = set()
+                for data in modifications.values():
+                    case_attrs.update(data.keys())
+
+                # Bulk fetch strain ids
+                strain_id_map = {}
+                if strains:
+                    cursor.execute(
+                        "SELECT Name, Id FROM Strain WHERE Name IN "
+                        f"({', '.join(['%s'] * len(strains))})",
+                        strains
+                    )
+                    for name, strain_id in cursor.fetchall():
+                        strain_id_map[name] = strain_id
+
+                # Bulk fetch case attr ids
+                caseattr_id_map = {}
+                if case_attrs:
+                    cursor.execute(
+                        "SELECT Name, CaseAttributeId FROM CaseAttribute "
+                        "WHERE InbredSetId = %s AND Name IN "
+                        f"({', '.join(['%s'] * len(case_attrs))})",
+                        (inbredset_id, *case_attrs)
+                    )
+                    for name, caseattr_id in cursor.fetchall():
+                        caseattr_id_map[name] = caseattr_id
+
+                # Bulk insert data
+                insert_data = []
+                for strain, data in modifications.items():
+                    strain_id = strain_id_map.get(strain)
+                    for case_attr, value in data.items():
+                        insert_data.append({
+                            "inbredset_id": inbredset_id,
+                            "strain_id": strain_id,
+                            "caseattr_id": caseattr_id_map.get(case_attr),
+                            "value": value,
+                        })
+                if insert_data:
+                    cursor.executemany(
+                        "INSERT INTO CaseAttributeXRefNew "
+                        "(InbredSetId, StrainId, CaseAttributeId, Value) "
+                        "VALUES (%(inbredset_id)s, %(strain_id)s, %(caseattr_id)s, %(value)s) "
+                        "ON DUPLICATE KEY UPDATE Value = VALUES(Value)",
+                        insert_data
+                    )
+
+                # Update LMDB and audit table
+                cursor.execute(
+                    "UPDATE caseattributes_audit "
+                    "SET status = %s "
+                    "WHERE id = %s",
+                    (str(change_type), change_id))
+                if approvals := txn.get(b"approved"):
+                    approved_ids = pickle.loads(approvals)
+                review_ids.discard(change_id)
+                approved_ids.add(change_id)
+                txn.put(b"review", pickle.dumps(review_ids))
+                txn.put(b"approvals", pickle.dumps(approved_ids))
+                return True
+            case _:
+                raise ValueError
diff --git a/tests/unit/db/test_case_attributes.py b/tests/unit/db/test_case_attributes.py
index 3580f93..494461a 100644
--- a/tests/unit/db/test_case_attributes.py
+++ b/tests/unit/db/test_case_attributes.py
@@ -11,6 +11,7 @@ from gn3.db.case_attributes import queue_edit
 from gn3.db.case_attributes import (
     CaseAttributeEdit,
     EditStatus,
+    apply_change,
     view_change,
     update_case_attribute
 )
@@ -186,3 +187,122 @@ def test_view_change_no_data(mocker: MockFixture) -> None:
     mock_cursor.execute.assert_called_once_with(
         "SELECT json_diff_data FROM caseattributes_audit WHERE id = %s",
         (CHANGE_ID,))
+
+
+@pytest.mark.unit_test
+def test_apply_change_approved(mocker: MockFixture) -> None:
+    """Test approving a change"""
+    mock_cursor, mock_conn = mocker.MagicMock(), mocker.MagicMock()
+    mock_conn.cursor.return_value = mock_cursor
+    mock_lmdb = mocker.patch("gn3.db.case_attributes.lmdb")
+    mock_env, mock_txn = mocker.MagicMock(), mocker.MagicMock()
+    mock_lmdb.open.return_value = mock_env
+    mock_env.begin.return_value.__enter__.return_value = mock_txn
+    CHANGE_ID, review_ids = 1, {1, 2, 3}
+    mock_txn.get.side_effect = (
+        pickle.dumps(review_ids),  # b"review" key
+        None,                      # b"approved" key
+    )
+    TMPDIR = Path(os.environ.get("TMPDIR", tempfile.gettempdir()))
+    mock_cursor.fetchone.return_value = (json.dumps({
+        "inbredset_id": 1,
+        "Modifications": {
+            "Current": {
+                "B6D2F1": {"Epoch": "10"},
+                "BXD100": {"Epoch": "3"},
+                "BXD101": {"SeqCvge": "2"},
+                "BXD102": {"Epoch": "3"},
+                "BXD108": {"SeqCvge": "oo"}
+            }
+        }
+    }), None)
+    mock_cursor.fetchall.side_effect = [
+        [  # Strain query
+            ("B6D2F1", 1), ("BXD100", 2),
+            ("BXD101", 3), ("BXD102", 4),
+            ("BXD108", 5)],
+        [  # CaseAttribute query
+            ("Epoch", 101), ("SeqCvge", 102)]
+    ]
+    assert apply_change(mock_cursor, EditStatus.approved,
+                        CHANGE_ID, TMPDIR) is True
+    assert mock_cursor.execute.call_count == 4
+    mock_cursor.execute.assert_has_calls([
+        mocker.call(
+            "SELECT json_diff_data FROM caseattributes_audit WHERE id = %s",
+            (CHANGE_ID,)),
+        mocker.call(
+            "SELECT Name, Id FROM Strain WHERE Name IN (%s, %s, %s, %s, %s)",
+            ("B6D2F1", "BXD100", "BXD101", "BXD102", "BXD108")),
+        mocker.call(
+            "SELECT Name, CaseAttributeId FROM CaseAttribute WHERE InbredSetId = %s AND Name IN (%s, %s)",
+            (1, "SeqCvge", "Epoch")),
+        mocker.call(
+            "UPDATE caseattributes_audit SET status = %s WHERE id = %s",
+            ("approved", CHANGE_ID))
+    ])
+    mock_cursor.executemany.assert_called_once_with(
+        "INSERT INTO CaseAttributeXRefNew (InbredSetId, StrainId, CaseAttributeId, Value) "
+        "VALUES (%(inbredset_id)s, %(strain_id)s, %(caseattr_id)s, %(value)s) "
+        "ON DUPLICATE KEY UPDATE Value = VALUES(Value)",
+        [
+            {"inbredset_id": 1, "strain_id": 1, "caseattr_id": 101, "value": "10"},
+            {"inbredset_id": 1, "strain_id": 2, "caseattr_id": 101, "value": "3"},
+            {"inbredset_id": 1, "strain_id": 3, "caseattr_id": 102, "value": "2"},
+            {"inbredset_id": 1, "strain_id": 4, "caseattr_id": 101, "value": "3"},
+            {"inbredset_id": 1, "strain_id": 5, "caseattr_id": 102, "value": "oo"}
+        ]
+    )
+
+
+@pytest.mark.unit_test
+def test_apply_change_rejected(mocker: MockFixture) -> None:
+    """Test rejecting a change"""
+    mock_cursor, mock_conn = mocker.MagicMock(), mocker.MagicMock()
+    mock_conn.cursor.return_value = mock_cursor
+    mock_lmdb = mocker.patch("gn3.db.case_attributes.lmdb")
+    mock_env, mock_txn = mocker.MagicMock(), mocker.MagicMock()
+    mock_lmdb.open.return_value = mock_env
+    mock_env.begin.return_value.__enter__.return_value = mock_txn
+    TMPDIR = Path(os.environ.get("TMPDIR", tempfile.gettempdir()))
+    CHANGE_ID, review_ids = 3, {1, 2, 3}
+    mock_txn.get.side_effect = [
+        pickle.dumps(review_ids),  # review_ids
+        None  # rejected_ids (initially empty)
+    ]
+
+    assert apply_change(mock_cursor, EditStatus.rejected,
+                        CHANGE_ID, TMPDIR) is True
+
+    # Verify SQL query call sequence
+    mock_cursor.execute.assert_called_once_with(
+        "UPDATE caseattributes_audit SET status = %s WHERE id = %s",
+        (str(EditStatus.rejected), CHANGE_ID))
+    mock_cursor.executemany.assert_not_called()
+
+    # Verify LMDB operations
+    mock_env.begin.assert_called_once_with(write=True)
+    expected_txn_calls = [
+        mocker.call(b"review", pickle.dumps({1, 2})),
+        mocker.call(b"rejected", pickle.dumps({3}))
+    ]
+    mock_txn.put.assert_has_calls(expected_txn_calls, any_order=False)
+
+
+@pytest.mark.unit_test
+def test_apply_change_non_existent_change_id(mocker: MockFixture) -> None:
+    """Test that there's a missing change_id from the returned LMDB rejected set."""
+    mock_env, mock_txn = mocker.MagicMock(), mocker.MagicMock()
+    mock_cursor, mock_conn = mocker.MagicMock(), mocker.MagicMock()
+    mock_lmdb = mocker.patch("gn3.db.case_attributes.lmdb")
+    mock_lmdb.open.return_value = mock_env
+    mock_conn.cursor.return_value = mock_cursor
+    mock_env.begin.return_value.__enter__.return_value = mock_txn
+    CHANGE_ID, review_ids = 28, {1, 2, 3}
+    mock_txn.get.side_effect = [
+        pickle.dumps(review_ids),  # b"review" key
+        None,                      # b"approved" key
+    ]
+    TMPDIR = Path(os.environ.get("TMPDIR", tempfile.gettempdir()))
+    assert apply_change(mock_cursor, EditStatus.approved,
+                        CHANGE_ID, TMPDIR) is False