--complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION pgwaffles" to load this file. \quit

-- Create the schema
create schema pgwaffles;

-- Create the tables for the extension
-- Data model:
-- INGREDIENT: name
-- NEEDS, 0N INGREDIENT, 0N RECIPE: quantity, unit, yield
-- RECIPE: title, picture, description, keywords, ranking
-- HAS INSTRUCTIONS, 0N STEP, _11 RECIPE
-- STEP: ordinality, description
--
-- http://mocodo.wingi.net/
create table pgwaffles.ingredient (
  id integer generated always as identity primary key,
  name text unique not null
);

create table pgwaffles.recipe (
  id integer generated always as identity primary key,
  title text unique not null,
  picture text,
  description text,
  keywords text[] not null,
  rating float
);

create table pgwaffles.step (
  recipeId integer not null references pgwaffles.recipe (id),
  ordinality integer not null,
  description text not null,
  primary key (recipeId, ordinality)
);

create table pgwaffles.ingredientInRecipe (
  recipeId integer not null references pgwaffles.recipe (id),
  yield integer not null,
  ingredientId integer not null references pgwaffles.ingredient (id),
  quantity decimal(5,3) not null,
  unit text not null
);

-- Read the search results from allrecipes.com
-- sort=re means we will sort by descending rating
create temporary table allRecipes (html text);
copy allRecipes from program 'curl -s "https://www.allrecipes.com/search/results/?wt=belgium%20waffles&sort=re" | hxnormalize -x | hxselect -i  "section.recipe-section > article.fixed-recipe-card > div.fixed-recipe-card__info > a" | tr "\n" " "';

-- Create the temporary table to store the best recipe
create temporary table processedRecipe(
  data text,
  recipe jsonb,
  title text generated always as ((recipe -> 'name')::text) stored,
  image text generated always as (
      ((recipe -> 'image')::jsonb -> 'url')::text
    ) stored,
  description text generated always as (
      (recipe -> 'description')::text
	) stored,
  yield integer generated always as (
      regexp_replace((
	    regexp_split_to_array((recipe -> 'recipeYield')::text,' '))[1],
		'"',
		'')::integer
	) stored,
  ingredients jsonb generated always as (recipe -> 'recipeIngredient') stored,
  steps jsonb generated always as (recipe -> 'recipeInstructions') stored,
  keywords text[],
  rating double precision  generated always as (
      ((recipe -> 'aggregateRating')::jsonb -> 'ratingValue')::float
    ) stored
);

do
  $$declare
   mylink text;
   _stmt text;
  begin
    -- Forge the copy query to get the html code source of the recipe
    with rawHtml(parts) as (
      -- Let's get the url links at the begining
      select regexp_split_to_table(html, 'href="')
      from allRecipes
    ),
    myRecipes(url) as ( 
      -- Let's stop at teh end of the link's url
      select split_part(parts, '/">', 1)
      from rawHtml
      -- The first part does not contain a real link
      where rawHtml.parts !~ '^<a data-content-provider-id'
    )
    select url into mylink
    from myRecipes
    limit 1;

    _stmt := $query$copy processedRecipe (data) from program 'curl -s "$query$;
    _stmt := _stmt || mylink;
    _stmt := _stmt || $query$/" | hxnormalize -x |  hxselect -ci "script[type=application\/ld\+json]" | tr -d "\n" | sed "s=\\\\=\\\\\\\\=g"';$query$;
    execute _stmt;
  end;
$$ language plpgsql;

-- Get the json we want
update processedRecipe set recipe = (regexp_match(
    data,
    '{ *"@context": "http://schema.org", *"@type": "Recipe",.*}')
	)[1]::jsonb,
  keywords = array['belgium', 'waffle'];

--Insert into my datamodel
with insertIntoRecipe(id) as (
  insert into pgwaffles.recipe(title,
  picture,
  description,
  keywords,
  rating)
  (select title,
    image,
	description,
	keywords,
	rating
  from processedRecipe)
  on conflict (title) do update set title = excluded.title
  returning id
),
ingredients (data) as (
  select 
    /* we'll remove parentethis and what's inside them as it can cause 
	 * problems and those details shouldn't be necessary */ 
     regexp_replace(jsonb_array_elements_text(ingredients),
	   ' *\([^\)]+\) *',
	   ' '
	 )
from processedRecipe
),
processedIngredients(data) as (
  select
    regexp_split_to_array (
	  /* we'll change those horrible fractions into real numbers */
      case 
        when data ~ ' ½' then regexp_replace(data, ' ½','.5') 
	    when data ~ '½' then regexp_replace(data, '½','0.5')
	    when data ~ ' ⅓' then regexp_replace(data, ' ⅓','.3') 
	    when data ~ '⅓' then regexp_replace(data, '⅓','0.3')
        when data ~ ' ⅕' then regexp_replace(data, ' ⅕','.2') 
	    when data ~ '⅕' then regexp_replace(data, '⅕','0.2')
	    when data ~ ' ⅙' then regexp_replace(data, ' ⅙','.17') 
	    when data ~ '⅙' then regexp_replace(data, '⅙','0.17')
        when data ~ ' ⅛' then regexp_replace(data, ' ⅛','.125') 
	    when data ~ '⅛' then regexp_replace(data, '⅛','0.125')
        when data ~ ' ⅔' then regexp_replace(data, ' ⅔','.7') 
	    when data ~ '⅔' then regexp_replace(data, '⅔','0.7')
        when data ~ ' ⅖' then regexp_replace(data, ' ⅖','.4') 
	    when data ~ '⅖' then regexp_replace(data, '⅖','0.4')
	    when data ~ ' ⅚' then regexp_replace(data, ' ⅚','.8') 
	    when data ~ '⅚' then regexp_replace(data, '⅚','0.8')
	    when data ~ ' ⅜' then regexp_replace(data, ' ⅜','.375') 
	    when data ~ '⅜' then regexp_replace(data, '⅜','0.375')
	    when data ~ ' ¾' then regexp_replace(data, ' ¾','.75') 
	    when data ~ '¾' then regexp_replace(data, '¾','0.75')
	    when data ~ ' ⅗' then regexp_replace(data, ' ⅗','.6') 
	    when data ~ '⅗' then regexp_replace(data, '⅗','0.6')
        when data ~ ' ⅝' then regexp_replace(data, ' ⅝','.625') 
	    when data ~ '⅝' then regexp_replace(data, '⅝','0.625')
	    when data ~ ' ⅞' then regexp_replace(data, ' ⅞','.875')
	    when data ~ ' ⅞' then regexp_replace(data, '⅞','.875') 
	    when data ~ '⅘' then regexp_replace(data, ' ⅘','.8')
        when data ~ ' ⅘' then regexp_replace(data, '⅘','0.8') 
	    when data ~ '¼' then regexp_replace(data, ' ¼','.25')
	    when data ~ ' ¼' then regexp_replace(data, '¼','0.25') 
	    when data ~ '⅐' then regexp_replace(data, ' ⅐','.14')
        when data ~ ' ⅐' then regexp_replace(data, '⅐','0.14') 
	    when data ~ '⅑' then regexp_replace(data, ' ⅑','.11')
	    when data ~ ' ⅑' then regexp_replace(data, '⅑','0.11') 
	    when data ~ '⅒' then regexp_replace(data, ' ⅒','.1')
	    when data ~ ' ⅒' then regexp_replace(data, '⅒','0.1') 
	    else data
      end,
	  ' ')
  from ingredients
),
insertIntoIngredient(id) as (
  insert into pgwaffles.ingredient (name)
  (select lower(array_to_string(array_remove(array_remove(data,data[1]),data[2]),' '))
  from processedIngredients
  )
  on conflict (name) do update set name = excluded.name
  returning id, name
),
insertIntoIngredientInRecipe(id) as (
  insert into pgwaffles.ingredientInRecipe(
    ingredientId,
    recipeId,
    quantity,
	 yield,
    unit
  )
  (select insertIntoIngredient.id,
    insertIntoRecipe.id,
    processedIngredients.data[1]::decimal(5,3) as quantity,
	processedRecipe.yield,
	processedIngredients.data[2] as unit
    from insertIntoIngredient
	  inner join processedIngredients
	    on insertIntoIngredient.name = lower(array_to_string(array_remove(array_remove(processedIngredients.data,processedIngredients.data[1]),data[2]),' ')),
	  insertIntoRecipe,
	  processedRecipe
  )
  returning recipeId
)
insert into pgwaffles.step(recipeId,
  ordinality,
  description)
 (select distinct insertIntoIngredientInRecipe.id, 
   steps_with_ordinality.ordinality,
   steps_with_ordinality."text"
 from processedRecipe,
  insertIntoIngredientInRecipe,
  rows from (jsonb_to_recordset(steps) as ("text" text,"@type" text)) 
    with ordinality steps_with_ordinality)
;

create function pgwaffles.displayIngredients(out yield integer,
  out quantity decimal(5,3),
  out unit text,
  out name text)
returns setof record as
$body$
  -- Display the ingredients
  with bestRecipe(recipeId) as (
    select id
    from pgwaffles.recipe
    order by rating
    limit 1
  )
  select
    ingredientInRecipe.yield,
    ingredientInRecipe.quantity,
    ingredientInRecipe.unit,
    ingredient.name
  from pgwaffles.ingredientInRecipe
    inner join bestRecipe
      on ingredientInRecipe.recipeId = bestRecipe.recipeId
    inner join pgwaffles.ingredient
      on ingredientInRecipe.ingredientId = ingredient.id;
$body$ language sql;

create function pgwaffles.displayRecipe()
returns setof text as
$body$
  --Display the steps
  with bestRecipe(recipeId) as (
    select id
    from pgwaffles.recipe
    order by rating
    limit 1
  )
  select description
  from pgwaffles.step
    inner join bestRecipe
      on step.recipeId = bestRecipe.recipeId
  order by ordinality;
$body$ language sql;

-- Clean up
drop table allRecipes;
drop table processedRecipe;