-- clean up database from previous test
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

-- create EAV tables
CREATE TABLE entity (
  id           SERIAL PRIMARY KEY,
  name         TEXT,
  description  TEXT
);
CREATE TABLE entity_attribute (
  id          SERIAL PRIMARY KEY,
  name        TEXT,
  description TEXT
);
CREATE TABLE entity_attribute_value (
  id                  SERIAL PRIMARY KEY,
  entity_id           INT    REFERENCES entity(id),
  entity_attribute_id INT    REFERENCES entity_attribute(id),
  value               TEXT
);

-- create entities
INSERT INTO entity(name, description)
SELECT 'entity_' || i, 'Test entity no. ' || i
FROM generate_series(1, 1000000) AS i;

-- create attributes
INSERT INTO entity_attribute(name, description)
VALUES
  ('color', 'The color of the entity')                              -- id = 1
, ('lenght', 'The lenght of the entity')                            -- id = 2
, ('width', 'The width of the entity')                              -- id = 3
, ('hassomething', 'A bool expressing if the entity has something') -- id = 4
, ('country', 'The home country of the entity');                    -- id = 5

-- insert default values for entities and attributes
INSERT INTO entity_attribute_value(entity_id, entity_attribute_id, value)
SELECT i, 1, 'red' FROM generate_series(1, 1000000) AS i;
INSERT INTO entity_attribute_value(entity_id, entity_attribute_id, value)
SELECT i, 2, '120' FROM generate_series(1, 1000000) AS i;
INSERT INTO entity_attribute_value(entity_id, entity_attribute_id, value)
SELECT i, 3, '3.1882420' FROM generate_series(1, 1000000) AS i;
INSERT INTO entity_attribute_value(entity_id, entity_attribute_id, value)
SELECT i, 4, 'true' FROM generate_series(1, 1000000) AS i;
INSERT INTO entity_attribute_value(entity_id, entity_attribute_id, value)
SELECT i, 5, 'Belgium' FROM generate_series(1, 1000000) AS i;

-- create JSONB table
CREATE TABLE entity_jsonb (
      id          SERIAL PRIMARY KEY,
      name        TEXT,
      description TEXT,
      properties  JSONB
    );

-- insert the same data
INSERT INTO entity_jsonb (name, description, properties)
SELECT 'entity_' || i, 'Test entity no. ' || i,
  json_build_object( 'color','red',
                     'lenght', 120,
                     'width', 3.1882420,
                     'hassomething', true,
                     'country', 'Belgium'
                   )
FROM generate_series(1, 1000000) AS i;

-- VACUUM ANALYZE;
VACUUM ANALYZE entity;
VACUUM ANALYZE entity_attribute;
VACUUM ANALYZE entity_attribute_value;
VACUUM ANALYZE entity_jsonb;

SELECT 'Update some records' AS NextTest;

EXPLAIN ANALYSE UPDATE entity_jsonb SET properties = jsonb_set(properties, '{"color"}', '"blue"') WHERE id = 120;
EXPLAIN ANALYSE UPDATE entity_jsonb SET properties = jsonb_set(properties, '{"hassomething"}', 'false') WHERE id = 1200;
EXPLAIN ANALYSE UPDATE entity_jsonb SET properties = jsonb_set(properties, '{"lenght"}', '256') WHERE id = 20;
EXPLAIN ANALYSE UPDATE entity_jsonb SET properties = jsonb_set(properties, '{"width"}', '1.20') WHERE id = 5012;


EXPLAIN ANALYSE UPDATE entity_attribute_value SET value = 'blue' WHERE entity_attribute_id = 1 AND entity_id = 120;
EXPLAIN ANALYSE UPDATE entity_attribute_value SET value = 'false' WHERE entity_attribute_id = 4 AND entity_id = 1200;
EXPLAIN ANALYSE UPDATE entity_attribute_value SET value = '256' WHERE entity_attribute_id = 2 AND entity_id = 20;
EXPLAIN ANALYSE UPDATE entity_attribute_value SET value = '1.20' WHERE entity_attribute_id = 3 AND entity_id = 5012;

SELECT 'Select all entity_names that have a certain property' AS NextTest;

EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties ->> 'color' = 'blue';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties @> '{"color": "blue"}';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties ->> 'hassomething' = 'false';

EXPLAIN ANALYSE SELECT e.name FROM entity e
                INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
                INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
                WHERE ea.name = 'color' AND eav.value = 'blue';
EXPLAIN ANALYSE SELECT e.name FROM entity e
                INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
                INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
                WHERE ea.name = 'hassomething' AND eav.value = 'false';

-- REPEAT WITH INDEXES
CREATE INDEX ON entity_attribute_value (entity_id);
CREATE INDEX ON entity_attribute_value (entity_attribute_id);

CREATE INDEX ON entity_jsonb USING GIN (properties);

SELECT 'Update some records (with indexes)' AS NextTest;

EXPLAIN ANALYSE UPDATE entity_jsonb SET properties = jsonb_set(properties, '{"color"}', '"blue"') WHERE id = 121;
EXPLAIN ANALYSE UPDATE entity_jsonb SET properties = jsonb_set(properties, '{"hassomething"}', 'false') WHERE id = 1201;
EXPLAIN ANALYSE UPDATE entity_jsonb SET properties = jsonb_set(properties, '{"lenght"}', '256') WHERE id = 21;
EXPLAIN ANALYSE UPDATE entity_jsonb SET properties = jsonb_set(properties, '{"width"}', '1.20') WHERE id = 5013;


EXPLAIN ANALYSE UPDATE entity_attribute_value SET value = 'blue' WHERE entity_attribute_id = 1 AND entity_id = 121;
EXPLAIN ANALYSE UPDATE entity_attribute_value SET value = 'false' WHERE entity_attribute_id = 4 AND entity_id = 1201;
EXPLAIN ANALYSE UPDATE entity_attribute_value SET value = '256' WHERE entity_attribute_id = 2 AND entity_id = 21;
EXPLAIN ANALYSE UPDATE entity_attribute_value SET value = '1.20' WHERE entity_attribute_id = 3 AND entity_id = 5013;

SELECT 'Select all entity_names that have a certain property (with indexes)' AS NextTest;

EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties ->> 'color' = 'blue';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties @> '{"color": "blue"}';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties ->> 'hassomething' = 'false';

EXPLAIN ANALYSE SELECT e.name FROM entity e
                INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
                INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
                WHERE ea.name = 'color' AND eav.value = 'blue';
EXPLAIN ANALYSE SELECT e.name FROM entity e
                INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
                INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
                WHERE ea.name = 'hassomething' AND eav.value = 'false';