# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3 (ipykernel)
#     language: python
#     name: python3
# ---

# be really clear about what's going into repaired tables: 
# - repaired
# - already OK
# - unrepairable
#
# Only ~ 215k repaired `env_package`s ?!
# - yeah, there just aren't that many annotated in the biosample metadata
#
#
# - some of this code should probably be moved back into the package
#     - splitting and repiping 
#
# - read in previous SSSOMs and don't re-map? or at least double check?
#
# - don't forget to consult "model" field in biosample table too
#     - for scoping, in addition to en_package and taxonomy_id?
#
# - what to do about hits against obsolete classes... does OLS provide a mechanism to avoid?
# - be careful with GAZ matches
# - avoid IDless, OMIT, NCIT, MICRO, BTO
#
# - is it worth trimming and concatenating "x1", "x2", "x3", where x is a mappable term? numbers may be low.
#     - example = XXX
#
# - reconcile "x biome" and "x" mappings
# - take note of non-local salvage hits
#     - like XXX
#     - small/large (freshwater) lake?
#     - include native labels in the review frame, in addition to local?
#     - should use rdftab, not additional OLS calls
#     
#
# - switch from hard-coded `env_package_overrides` to something like SSSOM?
# - mappings can come from merging strategies, first pass OLS or salvage OLS
# - reflect those methods in SSSOM
# - add string distance/confidence
#     - multiple columns for multiple methods?
#
# review these SSSOM columns
#
# ```
#     "subject_category": [biosample_col_to_map] * results_rows,
#     "predicate_id": ["skos:relatedMatch"] * results_rows,
#     "match_type": ["Lexical"] * results_rows,
#     "creator_id": ["https://github.com/turbomam/scoped-mapping"] * results_rows,
#     #     "mapping_tool": ["https://www.ebi.ac.uk/ols/docs/api"] * results_rows,
#     #     "confidence": 1 - frame_for_sssom["string_dist"],
# ```
#
# - are there samples with a host_taxid and a (sample) taxonomy_id from "unclassified"?
# - start at uncultured or metagenome taxonomy_id?
#
# - what scoping on or mapping of  "gold" or "path"? WON'T FIND THAT IN NCBI BIOSAMPLE
#
#
# What to save where? 
# - new columns in biosample sqlite? 
# - delimited file dumps
# - move content from original column and insert the new values into that original column?
# - pandas clipboard integration handy for review step
#

# +
import pandas as pd
import re
import requests as requests
import sqlite3
import string
import urllib
import yaml

import scoped_mapping

from datetime import datetime
from pkg_resources import get_distribution, DistributionNotFound
from strsimpy.cosine import Cosine
from xml.etree import ElementTree
from tdda import rexpy
# -

# # User-provided data
# See repo README for notes on setting up SQLite databases of OBO ontologies with semantic-sql, relation-graph and rdftab

# +
# from https://www.ncbi.nlm.nih.gov/biosample/docs/packages/?format=xml
# see also https://www.ncbi.nlm.nih.gov/biosample/docs/packages/
biosample_packages_file = "../target/biosample_packages.xml"

# from ftp://ftp.ncbi.nlm.nih.gov//biosample/biosample_set.xml.gz
# via harmonized_table.db.gz
# in https://drive.google.com/drive/u/0/folders/1eL0v0stoduahjDpoDJIk3z2pJBAU4b2Y
biosample_sqlite_file = "../target/harmonized_table.db"
ncbitaxon_sqlite_file = "../semantic-sql/db/ncbitaxon.db"
# -

biosample_cnx = sqlite3.connect(biosample_sqlite_file)
ncbitaxon_cnx = sqlite3.connect(ncbitaxon_sqlite_file)

# # Utilizing taxonomy for broad subsetting
#

# **This uses an SQLite database in which the transitive closure over subClassOf has already been materialized. See the README and Makefile.**
#
# Specifically, flag the Biosamples whose `taxon_id` indicates they are an unclassified entity. Ignoring the others will throw out samples of multicellular organisms, like fruit flies.
#
#

# ## Get a listing of all taxa that are transitive subclasses of `NCBITaxon:2787823`
#
# I.e. 'unclassified entities'

# +
q = """
select
    distinct s.subject
from
    entailed_edge ee
join statements s on
    ee.subject = s.subject
where
    ee.predicate = 'rdfs:subClassOf'
    and ee.object = 'NCBITaxon:2787823'
    and s.predicate = 'rdfs:label'
"""
[unclassified_taxa, query_duration] = scoped_mapping.timed_query(q, ncbitaxon_cnx)
unclassified_taxa["unclassified"] = True

print(query_duration)

unclassified_taxa
# -

# ## Get taxon counts from the Biosample metadata

# +
q = """
select
    taxonomy_id biosample_taxid,
    count(*) as count
from
    biosample b
group by
    taxonomy_id
order by
    count(*) desc
"""
[biosample_sample_taxon_summary, query_duration] = scoped_mapping.timed_query(
    q, biosample_cnx
)
biosample_sample_taxon_summary["curie"] = "NCBITaxon:" + biosample_sample_taxon_summary[
    "biosample_taxid"
].astype(str)

print(query_duration)
# -

# ## Merge the two taxonomy dataframes
#
# I.e. flag the the Biosample records whose `taxonomy_id` field belongs to a subclass of 'unclassified entries'.

# +
biosample_sample_taxon_summary = biosample_sample_taxon_summary.merge(
    unclassified_taxa, left_on="curie", right_on="subject", how="left"
)
biosample_sample_taxon_summary.unclassified.fillna(False, inplace=True)

biosample_sample_taxon_summary
# -

# ## Add labels to all taxa

# +
q = """
select
    subject ,
    value
from statements
where
    predicate = 'rdfs:label' and subject = stanza
"""
[all_tax_labels, query_duration] = scoped_mapping.timed_query(q, ncbitaxon_cnx)

biosample_sample_taxon_summary = biosample_sample_taxon_summary.merge(
    all_tax_labels, left_on="curie", right_on="subject", how="left"
)

biosample_sample_taxon_summary = biosample_sample_taxon_summary[
    ["curie", "biosample_taxid", "count", "unclassified", "value"]
]
biosample_sample_taxon_summary.columns = [
    "curie",
    "biosample_taxid",
    "count",
    "unclassified",
    "label",
]

print(query_duration)
biosample_sample_taxon_summary.to_sql(
    "biosample_sample_taxon_summary", biosample_cnx, if_exists="replace", index=False
)

all_tax_labels.to_sql("all_tax_labels", biosample_cnx, if_exists="replace", index=False)

biosample_sample_taxon_summary
# -

# **Almost all of the taxa that are common in the biosample collection are either unclassified/metagenomes or easily recognized cellular organisms. Cellular organism samples are de-prioritized in this exercise**
#
# Exceptions include:
# - 32630 = synthetic construct (other entries; other sequences; artificial sequences)
#     - 'other entries' would add 16k rows on top of the 1k 'unclassified entities'
#     - metagenomes account for 331 of the 'unclassified entities'
#     - there are also a small number of uncultured/unclassified microorganisms in the biosample dataset
# - 77133 = uncultured bacterium (cellular organisms; Bacteria; environmental samples)
#     - 'cellular organisms' would add 2M rows on top of the 1k 'unclassified entities'
#     - 'cellular organisms; Bacteria; environmental samples' adds 26k
#     
# ----

StarcoderdataJupyterScriptsDedupFiltered