drop extension if exists pg_liquid cascade;

create extension pg_liquid;

select count(*) as seeded_food_schema_count
from liquid.query($$
% ============================================================
% FOOD / GROCERY DOMAIN METAGRAPH
% schema only — no food instances
% ============================================================

DefPred("meta/domain_class",           "0", "liquid/node", "0", "liquid/node").
DefPred("meta/range_class",            "0", "liquid/node", "0", "liquid/node").
DefPred("meta/compound_subject_class", "0", "liquid/node", "0", "liquid/node").

DefPred("food/preferred_label", "1", "liquid/node", "0", "liquid/string").
DefPred("food/alt_label",       "0", "liquid/node", "0", "liquid/string").
DefPred("food/description",     "1", "liquid/node", "0", "liquid/string").
DefPred("food/status",          "1", "liquid/node", "0", "liquid/node").

DefPred("food/instance_of",     "0", "liquid/node", "0", "liquid/node").
DefPred("food/subclass_of",     "0", "liquid/node", "0", "liquid/node").

DefPred("food/broader_concept",   "0", "liquid/node", "0", "liquid/node").
DefPred("food/in_group",          "0", "liquid/node", "0", "liquid/node").
DefPred("food/derived_from",      "0", "liquid/node", "0", "liquid/node").
DefPred("food/has_state",         "0", "liquid/node", "0", "liquid/node").
DefPred("food/processed_by",      "0", "liquid/node", "0", "liquid/node").
DefPred("food/substitutable_for", "0", "liquid/node", "0", "liquid/node").

DefPred("food/denotes_concept", "1", "liquid/node", "0", "liquid/node").
DefPred("food/brand",           "1", "liquid/node", "0", "liquid/node").
DefPred("food/gtin",            "1", "liquid/node", "0", "liquid/string").
DefPred("food/package_form",    "1", "liquid/node", "0", "liquid/node").

DefPred("food/makes_concept",   "1", "liquid/node", "0", "liquid/node").

Edge("class/Thing",            "food/preferred_label", "Thing").

Edge("class/FoodConcept",      "food/preferred_label", "Food concept").
Edge("class/FoodConcept",      "food/subclass_of",     "class/Thing").

Edge("class/GroceryProduct",   "food/preferred_label", "Grocery product").
Edge("class/GroceryProduct",   "food/subclass_of",     "class/Thing").

Edge("class/Recipe",           "food/preferred_label", "Recipe").
Edge("class/Recipe",           "food/subclass_of",     "class/Thing").

Edge("class/FoodGroup",        "food/preferred_label", "Food group").
Edge("class/FoodGroup",        "food/subclass_of",     "class/Thing").

Edge("class/Nutrient",         "food/preferred_label", "Nutrient").
Edge("class/Nutrient",         "food/subclass_of",     "class/Thing").

Edge("class/DietRule",         "food/preferred_label", "Diet rule").
Edge("class/DietRule",         "food/subclass_of",     "class/Thing").

Edge("class/Allergen",         "food/preferred_label", "Allergen").
Edge("class/Allergen",         "food/subclass_of",     "class/Thing").

Edge("class/Unit",             "food/preferred_label", "Unit").
Edge("class/Unit",             "food/subclass_of",     "class/Thing").

Edge("class/Brand",            "food/preferred_label", "Brand").
Edge("class/Brand",            "food/subclass_of",     "class/Thing").

Edge("class/PackageForm",      "food/preferred_label", "Package form").
Edge("class/PackageForm",      "food/subclass_of",     "class/Thing").

Edge("class/FoodState",        "food/preferred_label", "Food state").
Edge("class/FoodState",        "food/subclass_of",     "class/Thing").

Edge("class/ProcessingMethod", "food/preferred_label", "Processing method").
Edge("class/ProcessingMethod", "food/subclass_of",     "class/Thing").

Edge("class/Vocabulary",       "food/preferred_label", "External vocabulary").
Edge("class/Vocabulary",       "food/subclass_of",     "class/Thing").

Edge("class/LifecycleStatus",    "food/preferred_label", "Lifecycle status").
Edge("class/LifecycleStatus",    "food/subclass_of",     "class/Thing").

Edge("class/IngredientRole",     "food/preferred_label", "Ingredient role").
Edge("class/IngredientRole",     "food/subclass_of",     "class/Thing").

Edge("class/SuitabilityStatus",  "food/preferred_label", "Suitability status").
Edge("class/SuitabilityStatus",  "food/subclass_of",     "class/Thing").

Edge("class/AllergenDisposition", "food/preferred_label", "Allergen disposition").
Edge("class/AllergenDisposition", "food/subclass_of",     "class/Thing").

Edge("class/MappingType",      "food/preferred_label", "Mapping type").
Edge("class/MappingType",      "food/subclass_of",     "class/Thing").

Edge("class/SourceKind",       "food/preferred_label", "Source kind").
Edge("class/SourceKind",       "food/subclass_of",     "class/Thing").

Edge("class/EvidenceKind",     "food/preferred_label", "Evidence kind").
Edge("class/EvidenceKind",     "food/subclass_of",     "class/Thing").

Edge("status/proposed",   "food/instance_of", "class/LifecycleStatus").
Edge("status/proposed",   "food/preferred_label", "proposed").
Edge("status/approved",   "food/instance_of", "class/LifecycleStatus").
Edge("status/approved",   "food/preferred_label", "approved").
Edge("status/deprecated", "food/instance_of", "class/LifecycleStatus").
Edge("status/deprecated", "food/preferred_label", "deprecated").

Edge("role/ingredient",    "food/instance_of", "class/IngredientRole").
Edge("role/ingredient",    "food/preferred_label", "ingredient").
Edge("role/subingredient", "food/instance_of", "class/IngredientRole").
Edge("role/subingredient", "food/preferred_label", "subingredient").
Edge("role/component",     "food/instance_of", "class/IngredientRole").
Edge("role/component",     "food/preferred_label", "component").

Edge("suit/allowed",    "food/instance_of", "class/SuitabilityStatus").
Edge("suit/allowed",    "food/preferred_label", "allowed").
Edge("suit/disallowed", "food/instance_of", "class/SuitabilityStatus").
Edge("suit/disallowed", "food/preferred_label", "disallowed").
Edge("suit/limit",      "food/instance_of", "class/SuitabilityStatus").
Edge("suit/limit",      "food/preferred_label", "limit").

Edge("adisposition/contains",    "food/instance_of", "class/AllergenDisposition").
Edge("adisposition/contains",    "food/preferred_label", "contains").
Edge("adisposition/may_contain", "food/instance_of", "class/AllergenDisposition").
Edge("adisposition/may_contain", "food/preferred_label", "may contain").
Edge("adisposition/free_from",   "food/instance_of", "class/AllergenDisposition").
Edge("adisposition/free_from",   "food/preferred_label", "free from").

Edge("map/exact",   "food/instance_of", "class/MappingType").
Edge("map/exact",   "food/preferred_label", "exact mapping").
Edge("map/broad",   "food/instance_of", "class/MappingType").
Edge("map/broad",   "food/preferred_label", "broad mapping").
Edge("map/narrow",  "food/instance_of", "class/MappingType").
Edge("map/narrow",  "food/preferred_label", "narrow mapping").
Edge("map/related", "food/instance_of", "class/MappingType").
Edge("map/related", "food/preferred_label", "related mapping").

Edge("source/import",   "food/instance_of", "class/SourceKind").
Edge("source/import",   "food/preferred_label", "import").
Edge("source/curation", "food/instance_of", "class/SourceKind").
Edge("source/curation", "food/preferred_label", "curation").
Edge("source/label",    "food/instance_of", "class/SourceKind").
Edge("source/label",    "food/preferred_label", "package label").

Edge("evidence/declared",   "food/instance_of", "class/EvidenceKind").
Edge("evidence/declared",   "food/preferred_label", "declared").
Edge("evidence/ingredient", "food/instance_of", "class/EvidenceKind").
Edge("evidence/ingredient", "food/preferred_label", "ingredient based").
Edge("evidence/nutrient",   "food/instance_of", "class/EvidenceKind").
Edge("evidence/nutrient",   "food/preferred_label", "nutrient based").
Edge("evidence/inferred",   "food/instance_of", "class/EvidenceKind").
Edge("evidence/inferred",   "food/preferred_label", "inferred").

Edge("food/preferred_label", "meta/domain_class", "class/Thing").
Edge("food/alt_label",       "meta/domain_class", "class/Thing").
Edge("food/description",     "meta/domain_class", "class/Thing").
Edge("food/status",          "meta/domain_class", "class/Thing").
Edge("food/status",          "meta/range_class",  "class/LifecycleStatus").

Edge("food/broader_concept", "meta/domain_class", "class/FoodConcept").
Edge("food/broader_concept", "meta/range_class",  "class/FoodConcept").

Edge("food/in_group",        "meta/domain_class", "class/FoodConcept").
Edge("food/in_group",        "meta/range_class",  "class/FoodGroup").

Edge("food/derived_from",    "meta/domain_class", "class/FoodConcept").
Edge("food/derived_from",    "meta/range_class",  "class/FoodConcept").

Edge("food/has_state",       "meta/domain_class", "class/FoodConcept").
Edge("food/has_state",       "meta/domain_class", "class/GroceryProduct").
Edge("food/has_state",       "meta/domain_class", "class/Recipe").
Edge("food/has_state",       "meta/range_class",  "class/FoodState").

Edge("food/processed_by",    "meta/domain_class", "class/FoodConcept").
Edge("food/processed_by",    "meta/range_class",  "class/ProcessingMethod").

Edge("food/substitutable_for", "meta/domain_class", "class/FoodConcept").
Edge("food/substitutable_for", "meta/range_class",  "class/FoodConcept").

Edge("food/denotes_concept", "meta/domain_class", "class/GroceryProduct").
Edge("food/denotes_concept", "meta/range_class",  "class/FoodConcept").

Edge("food/brand",           "meta/domain_class", "class/GroceryProduct").
Edge("food/brand",           "meta/range_class",  "class/Brand").

Edge("food/package_form",    "meta/domain_class", "class/GroceryProduct").
Edge("food/package_form",    "meta/range_class",  "class/PackageForm").

Edge("food/makes_concept",   "meta/domain_class", "class/Recipe").
Edge("food/makes_concept",   "meta/range_class",  "class/FoodConcept").

DefCompound("Serving", "serv/subject", "0", "liquid/node").
DefCompound("Serving", "serv/amount",  "0", "liquid/string").
DefCompound("Serving", "serv/unit",    "0", "liquid/node").
DefCompound("Serving", "serv/name",    "0", "liquid/string").
Edge("Serving", "liquid/mutable", "false").

Edge("Serving", "meta/compound_subject_class", "class/FoodConcept").
Edge("Serving", "meta/compound_subject_class", "class/GroceryProduct").
Edge("Serving", "meta/compound_subject_class", "class/Recipe").

DefCompound("Nutrition",  "nut/subject",      "0", "liquid/node").
DefCompound("Nutrition",  "nut/nutrient",     "0", "liquid/node").
DefCompound("Nutrition",  "nut/amount",       "0", "liquid/string").
DefCompound("Nutrition",  "nut/unit",         "0", "liquid/node").
DefCompound("Nutrition",  "nut/basis_amount", "0", "liquid/string").
DefCompound("Nutrition",  "nut/basis_unit",   "0", "liquid/node").
DefCompound("Nutrition",  "nut/source_kind",  "0", "liquid/node").
Edge("Nutrition", "liquid/mutable", "false").

Edge("Nutrition", "meta/compound_subject_class", "class/FoodConcept").
Edge("Nutrition", "meta/compound_subject_class", "class/GroceryProduct").
Edge("Nutrition", "meta/compound_subject_class", "class/Recipe").

DefCompound("Ingredient", "ing/subject",     "0", "liquid/node").
DefCompound("Ingredient", "ing/component",   "0", "liquid/node").
DefCompound("Ingredient", "ing/role",        "0", "liquid/node").
DefCompound("Ingredient", "ing/sequence",    "0", "liquid/int").
DefCompound("Ingredient", "ing/amount",      "0", "liquid/string").
DefCompound("Ingredient", "ing/unit",        "0", "liquid/node").
DefCompound("Ingredient", "ing/qualifier",   "0", "liquid/string").
DefCompound("Ingredient", "ing/source_kind", "0", "liquid/node").
Edge("Ingredient", "liquid/mutable", "false").

Edge("Ingredient", "meta/compound_subject_class", "class/FoodConcept").
Edge("Ingredient", "meta/compound_subject_class", "class/GroceryProduct").
Edge("Ingredient", "meta/compound_subject_class", "class/Recipe").

DefCompound("Suitability", "suit_subject",     "0", "liquid/node").
DefCompound("Suitability", "suit_rule",        "0", "liquid/node").
DefCompound("Suitability", "suit_status",      "0", "liquid/node").
DefCompound("Suitability", "suit_evidence",    "0", "liquid/node").
DefCompound("Suitability", "suit_source_kind", "0", "liquid/node").
Edge("Suitability", "liquid/mutable", "false").

Edge("Suitability", "meta/compound_subject_class", "class/FoodConcept").
Edge("Suitability", "meta/compound_subject_class", "class/GroceryProduct").
Edge("Suitability", "meta/compound_subject_class", "class/Recipe").

DefCompound("AllergenStatus", "alg/subject",     "0", "liquid/node").
DefCompound("AllergenStatus", "alg/allergen",    "0", "liquid/node").
DefCompound("AllergenStatus", "alg/status",      "0", "liquid/node").
DefCompound("AllergenStatus", "alg/evidence",    "0", "liquid/node").
DefCompound("AllergenStatus", "alg/source_kind", "0", "liquid/node").
Edge("AllergenStatus", "liquid/mutable", "false").

Edge("AllergenStatus", "meta/compound_subject_class", "class/FoodConcept").
Edge("AllergenStatus", "meta/compound_subject_class", "class/GroceryProduct").
Edge("AllergenStatus", "meta/compound_subject_class", "class/Recipe").

DefCompound("Package", "pkg/product",    "0", "liquid/node").
DefCompound("Package", "pkg/net_amount", "0", "liquid/string").
DefCompound("Package", "pkg/net_unit",   "0", "liquid/node").
DefCompound("Package", "pkg/unit_count", "0", "liquid/string").
DefCompound("Package", "pkg/unit_kind",  "0", "liquid/node").
Edge("Package", "liquid/mutable", "false").

Edge("Package", "meta/compound_subject_class", "class/GroceryProduct").

DefCompound("Mapping", "map/local_term",   "0", "liquid/node").
DefCompound("Mapping", "map/vocabulary",   "0", "liquid/node").
DefCompound("Mapping", "map/external_id",  "0", "liquid/string").
DefCompound("Mapping", "map/mapping_type", "0", "liquid/node").
DefCompound("Mapping", "map/source_kind",  "0", "liquid/node").
Edge("Mapping", "liquid/mutable", "false").

Edge("Mapping", "meta/compound_subject_class", "class/Thing").

Edge("class/FoodConcept", "food/subclass_of", parent)?
$$) as t(parent text);

select subject_class
from liquid.query($$
Edge("Nutrition", "meta/compound_subject_class", subject_class)?
$$) as t(subject_class text)
order by 1;

select role_name,
       cardinality,
       value_type
from liquid.query($$
DefCompound("AllergenStatus", role_name, cardinality, value_type)?
$$) as t(role_name text, cardinality text, value_type text)
order by 1, 2, 3;

select status_id,
       status_label
from liquid.query($$
Edge(status_id, "food/instance_of", "class/AllergenDisposition"),
Edge(status_id, "food/preferred_label", status_label)?
$$) as t(status_id text, status_label text)
order by 1, 2;
