-- mirri.vw_strains_202206 source

CREATE OR REPLACE VIEW `mirri`.`vw_strains_202206` AS 
with `taxonomy` as (
select
    `zmsmt`.`_id` AS `_id`,
    `zmsmt`.`x` AS `x`,
    `zmsmt`.`y` AS `y`,
    `zmsmt`.`idx` AS `idx`,
    `zmsmt`.`idy` AS `idy`
from
    `mirri`.`zy_mirri_strains_mirri_taxonomy_737` `zmsmt`)
select
    `s`.`name` AS `MIRRI Accession number`,
    `s`.`mirri_accession_number` AS `Accession number`,
    `s`.`restriction_on_use` AS `Restrictions on use`,
    `s`.`nagoya` AS `Nagoya protocol restrictions and compliance conditions`,
    `s`.`abs_url` AS `ABS related files`,
    `s`.`mta_url` AS `MTA url`,
    `s`.`mta_text` AS `MTA text`,
    `s`.`strains_from_reg_collection` AS `Strain from a Registered Collection`,
    `s`.`risk_group` AS `Risk Group`,
    `s`.`dual_use` AS `Dual use`,
    `s`.`quarantine_europe` AS `Quarantine in Europe`,
    trim(leading ';' from concat((case when (substring_index(substring_index(`s`.`type_of_organim`, '¶', 2), '¶',-(1)) = '2') then ';Algae' else '' end),(case when (substring_index(substring_index(`s`.`type_of_organim`, '¶', 3), '¶',-(1)) = '2') then ';Archaea' else '' end),(case when (substring_index(substring_index(`s`.`type_of_organim`, '¶', 4), '¶',-(1)) = '2') then ';Bacteria' else '' end),(case when (substring_index(substring_index(`s`.`type_of_organim`, '¶', 5), '¶',-(1)) = '2') then ';Cyanobacteria' else '' end),(case when (substring_index(substring_index(`s`.`type_of_organim`, '¶', 6), '¶',-(1)) = '2') then ';Filamentous Fungi' else '' end),(case when (substring_index(substring_index(`s`.`type_of_organim`, '¶', 7), '¶',-(1)) = '2') then ';Phage' else '' end),(case when (substring_index(substring_index(`s`.`type_of_organim`, '¶', 8), '¶',-(1)) = '2') then ';Plasmid' else '' end),(case when (substring_index(substring_index(`s`.`type_of_organim`, '¶', 9), '¶',-(1)) = '2') then ';Virus' else '' end),(case when (substring_index(substring_index(`s`.`type_of_organim`, '¶', 10), '¶',-(1)) = '2') then ';Yeast' else '' end),(case when (substring_index(substring_index(`s`.`type_of_organim`, '¶', 11), '¶',-(1)) = '2') then ';Microalgae' else '' end))) AS `Organism type`,
    `t`.`name` AS `Taxon name`,
    `s`.`infra_subsp_name` AS `Infrasubspecific names`,
    `s`.`comment_taxonomy` AS `Comment on taxonomy`,
    `s`.`type_description` AS `Type`,
    `s`.`status` AS `Status`,
    `s`.`history` AS `History of deposit`,
    `s`.`depositor` AS `Depositor`,
    trim(leading '//' from trim(leading '//0' from concat(substr(`s`.`deposited_date`, 7, 2), '/', substr(`s`.`deposited_date`, 5, 2), '/', substr(`s`.`deposited_date`, 1, 4)))) AS `Date of deposit`,
    `s`.`collector` AS `Collected by`,
    trim(leading '//' from trim(leading '//0' from concat(substr(`s`.`collection_date`, 7, 2), '/', substr(`s`.`collection_date`, 5, 2), '/', substr(`s`.`collection_date`, 1, 4)))) AS `Date of collection`,
    `s`.`isolator` AS `Isolated by`,
    trim(leading '//' from trim(leading '//0' from concat(substr(`s`.`isolation_date`, 7, 2), '/', substr(`s`.`isolation_date`, 5, 2), '/', substr(`s`.`isolation_date`, 1, 4)))) AS `Date of isolation`,
    trim(leading '//' from trim(leading '//0' from concat(substr(`s`.`catalogue_date`, 7, 2), '/', substr(`s`.`catalogue_date`, 5, 2), '/', substr(`s`.`catalogue_date`, 1, 4)))) AS `Date of inclusion in the catalogue`,
    `s`.`tested_growth_temp` AS `Tested temperature growth range`,
    `s`.`growth_temp` AS `Recommended growth temperature`,
    NULL AS `Recommended medium for growth`,
    trim(leading ';' from concat((case when (substring_index(substring_index(`s`.`form_supply`, '¶', 2), '¶',-(1)) = '2') then 'Agar' else '' end),(case when (substring_index(substring_index(`s`.`form_supply`, '¶', 3), '¶',-(1)) = '2') then ';Cryo' else '' end),(case when (substring_index(substring_index(`s`.`form_supply`, '¶', 4), '¶',-(1)) = '2') then ';Dry Ice' else '' end),(case when (substring_index(substring_index(`s`.`form_supply`, '¶', 5), '¶',-(1)) = '2') then ';Liquid Culture Medium' else '' end),(case when (substring_index(substring_index(`s`.`form_supply`, '¶', 6), '¶',-(1)) = '2') then ';Lyo' else '' end),(case when (substring_index(substring_index(`s`.`form_supply`, '¶', 7), '¶',-(1)) = '2') then ';Oil' else '' end),(case when (substring_index(substring_index(`s`.`form_supply`, '¶', 8), '¶',-(1)) = '2') then ';Water' else '' end))) AS `Form of supply`,
    `s`.`other_denomination` AS `Other denomination`,
    `s`.`coordinates` AS `Coordinates of geographic origin`,
    `s`.`origin_id` AS `Country`,
    `s`.`locality_of_origin` AS `Geographic origin`,
    `s`.`gmo` AS `GMO`,
    NULL AS `GMO construction information`,
    `s`.`mutant` AS `Mutant information`,
    `s`.`genotype` AS `Genotype`,
    `s`.`literature_doi` AS `Literature`,
    `s`.`sexual_state` AS `Sexual state`,
    `s`.`ploidy` AS `Ploidy`,
    `s`.`interspecific_hybrid` AS `Interspecific hybrid`,
    `s`.`pathogenicity` AS `Pathogenicity`,
    `s`.`enzyme_production` AS `Enzyme production`,
    `s`.`metabolites_production` AS `Production of metabolites`,
    `s`.`applications` AS `Applications`,
    `s`.`remarks` AS `Remarks`,
    `s`.`plasmids` AS `Plasmids`,
    `s`.`plasmids_collections` AS `Plasmids collections fields`,
    `s`.`substrate_of_isolation` AS `Substrate/host of isolation`,
    `s`.`isolation_habitat` AS `Isolation habitat`,
    `s`.`ontobiotope` AS `Ontobiotope term for the isolation habitat`,
    NULL AS `Genomic sequences and accession numbers`,
    NULL AS `Literature linked to the sequence/genome`,
    NULL AS `Link to other sites`,
    NULL AS `QPS`,
    NULL AS `Axenic culture`
from
    ((`mirri`.`mirri_strains` `s`
left join `taxonomy` `tu` on
    ((`tu`.`x` = `s`.`_id`)))
left join `mirri`.`mirri_taxonomy` `t` on
    ((`t`.`_id` = `tu`.`y`)));