CREATE TABLE IF NOT EXISTS wcc_contentful_schema_version (
  version integer PRIMARY KEY,
  updated_at timestamp DEFAULT now()
);

START TRANSACTION;

CREATE TABLE IF NOT EXISTS contentful_raw (
  -- The Contentful 'sys'->'id'
  id varchar PRIMARY KEY,
  -- The contentful entry
  data jsonb,
  -- Every ID that this entry links to in 'fields'->*->[each locale]->'sys'->'id'
  links text[]
);
CREATE INDEX IF NOT EXISTS contentful_raw_value_type ON contentful_raw ((data->'sys'->>'type'));
CREATE INDEX IF NOT EXISTS contentful_raw_value_content_type ON contentful_raw ((data->'sys'->'contentType'->'sys'->>'id'));

-- Insert or update a Contentful entry by it's ID
CREATE or replace FUNCTION "fn_contentful_upsert_entry"(_id varchar, _data jsonb, _links text[]) RETURNS jsonb AS $$
DECLARE
  prev jsonb;
BEGIN
  SELECT data, links FROM contentful_raw WHERE id = _id INTO prev;
  INSERT INTO contentful_raw (id, data, links) values (_id, _data, _links)
    ON CONFLICT (id) DO
      UPDATE
      SET data = _data,
        links = _links;
  RETURN prev;
END;
$$ LANGUAGE 'plpgsql';

-- Joins the entries table to itself by all the linked entries down to depth 5.
-- Each entry has a row for each downstream entry in it's tree.
-- Example:
--  | id    | included_id | depth |
--  | page1 | page2       | 1     |
--  | page1 | subpage2    | 2     | -- through page2
--  | page1 | asset1      | 1     |
--  | page2 | subpage2    | 1     |
--  ...
CREATE MATERIALIZED VIEW IF NOT EXISTS contentful_raw_includes_ids_jointable AS
  WITH RECURSIVE includes (root_id, depth) AS (
    SELECT t.id as root_id, 0, t.id, t.links FROM contentful_raw t
    UNION ALL
      SELECT l.root_id, l.depth + 1, r.id, r.links
      FROM includes l, contentful_raw r
      WHERE r.id = ANY(l.links) AND l.depth < 5
  )
  SELECT root_id as id, id as included_id, min(depth)
    FROM includes
    GROUP BY root_id, id;

CREATE INDEX IF NOT EXISTS contentful_raw_includes_ids_jointable_id ON contentful_raw_includes_ids_jointable (id);
CREATE UNIQUE INDEX IF NOT EXISTS contentful_raw_includes_ids_jointable_id_included_id ON contentful_raw_includes_ids_jointable (id, included_id);

-- Uses the contentful_raw_includes_ids_jointable to join the entries table to itself,
-- aggregating the included entries into an array.
-- Example:
--  | id    | data  | includes                                    |
--  | page1 | jsonb | {page2 jsonb, subpage2 jsonb, asset1 jsonb} |
CREATE OR REPLACE VIEW contentful_raw_includes AS
  SELECT t.id, t.data, array_remove(array_agg(r_incl.data), NULL) as includes
    FROM contentful_raw t
    LEFT JOIN contentful_raw_includes_ids_jointable incl ON t.id = incl.id
    LEFT JOIN contentful_raw r_incl ON r_incl.id = incl.included_id
    GROUP BY t.id, t.data;

INSERT INTO wcc_contentful_schema_version
  VALUES (1);

COMMIT;