###########################################
# OP 1
###########################################
# Broken United States
# Select broken countries.
SELECT
  field_ro_country_value
FROM
  d8_node__field_ro_country
WHERE
  field_ro_country_value = 'United States'
;

# Location adjustments, so data is consistent.
# Done (3 records updated)
UPDATE
  d8_node__field_ro_country
SET
  field_ro_country_value = 'United States of America'
WHERE
  field_ro_country_value = 'United States'
;
###########################################
# END OF OP 1
###########################################

###########################################
# OP 2
###########################################
# Select Single entity
# https://takeielts.britishcouncil.org/recognising-organisation/wwwmhe-krgorg
# Iraq with website url instead of univercity name in title - country should be Iraq, city should be Erbil
SELECT
  d8_node_field_data.nid as id,
  d8_node_field_data.title,
  field_ro_country_value as country
FROM
  d8_node_field_data
  INNER JOIN d8_node__field_ro_country
  ON d8_node_field_data.nid = d8_node__field_ro_country.entity_id
WHERE d8_node_field_data.nid = 4388
;

# Location and title adjustments, so data is consistent.
# Done (1 record updated)
UPDATE
  d8_node_field_data
SET
  title = 'Ministry of Higher Education and Scientific Research'
WHERE
  d8_node_field_data.nid = 4388
;

UPDATE
  d8_node__field_ro_country
SET
  field_ro_country_value = 'Iraq'
WHERE
  d8_node__field_ro_country.entity_id = 4388
;

# Insert Ebril City into cities table for 4388
INSERT INTO
  d8_node__field_ro_city
VALUES
('ro', 0, 4388, 4684, 'en', 0, 'Erbil')
;

###########################################
# END OF OP 2
###########################################

###########################################
# OP 3
###########################################
# Select Single entity
# https://takeielts.britishcouncil.org/recognising-organisation/portalufesbr
# Iraq with website url instead of univercity name in title - country should be Brazil, city should be Vitoria
SELECT
  d8_node_field_data.nid as id,
  d8_node_field_data.title,
  field_ro_country_value as country,
  d8_node__field_ro_city.field_ro_city_value as city,
  d8_node__field_ro_city.revision_id
FROM
  d8_node_field_data
  INNER JOIN d8_node__field_ro_country
  ON d8_node_field_data.nid = d8_node__field_ro_country.entity_id
  INNER JOIN d8_node__field_ro_city
  ON d8_node__field_ro_city.entity_id = d8_node_field_data.nid
WHERE d8_node_field_data.nid = 7319
;

# Location and title adjustments, so data is consistent.
# Done (1 record updated)
UPDATE
  d8_node_field_data
SET
  title = 'Universidade Federal do Espirito Santo'
WHERE
  d8_node_field_data.nid = 7319
;

UPDATE
  d8_node__field_ro_country
SET
  field_ro_country_value = 'Brasil'
WHERE
  d8_node__field_ro_country.entity_id = 7319
;

# Insert Vitoria city into cities table for 7319
INSERT INTO
  d8_node__field_ro_city
VALUES
('ro', 0, 7319, 7722, 'en', 0, 'Vitoria')
;

###########################################
# END OF OP 3
###########################################

###########################################
# OP 4
###########################################
# Select Single entity
# https://takeielts.britishcouncil.org/recognising-organisation/institute-philosophy-and-sociology-pan
# State is set to Warsaw. To change: remove state, add Warsaw as city and Poland as country
SELECT
  d8_node_field_data.nid as id,
  d8_node_field_data.title,
  field_ro_country_value as country,
  d8_node__field_ro_city.field_ro_city_value as city,
  d8_node__field_state.field_state_value as state,
  d8_node_field_data.vid as revision_id
FROM
  d8_node_field_data
  LEFT JOIN d8_node__field_ro_country
  ON d8_node_field_data.nid = d8_node__field_ro_country.entity_id
  LEFT JOIN d8_node__field_ro_city
  ON d8_node__field_ro_city.entity_id = d8_node_field_data.nid
  LEFT JOIN d8_node__field_state
  ON d8_node_field_data.nid = d8_node__field_state.entity_id
WHERE d8_node_field_data.nid = 10564
;

# Location and title adjustments, so data is consistent.
# Done (1 record removed, 2 added)
DELETE FROM
  d8_node__field_state
WHERE
  entity_id = 10564
AND
  revision_id = 10973
;

# Insert Warsaw city into cities table for 10564
INSERT INTO
  d8_node__field_ro_city
VALUES
('ro', 0, 10564, 10973, 'en', 0, 'Warsaw')
;

# Insert Poland country into countries table for 10564
INSERT INTO
  d8_node__field_ro_country
VALUES
('ro', 0, 10564, 10973, 'en', 0, 'Poland')
;

###########################################
# END OF OP 4
###########################################

###########################################
# OP 5
###########################################
# Select Single entity
# https://takeielts.britishcouncil.org/recognising-organisation/university-wisconsin-flexible-option
# Country is set to Wisconsin. To change: change country, add state, add city of Madison
SELECT
  d8_node_field_data.nid as id,
  d8_node_field_data.title,
  field_ro_country_value as country,
  d8_node__field_ro_city.field_ro_city_value as city,
  d8_node__field_state.field_state_value as state,
  d8_node_field_data.vid as revision_id
FROM
  d8_node_field_data
  LEFT JOIN d8_node__field_ro_country
  ON d8_node_field_data.nid = d8_node__field_ro_country.entity_id
  LEFT JOIN d8_node__field_ro_city
  ON d8_node__field_ro_city.entity_id = d8_node_field_data.nid
  LEFT JOIN d8_node__field_state
  ON d8_node_field_data.nid = d8_node__field_state.entity_id
WHERE d8_node_field_data.nid = 10222
;

# Location and title adjustments, so data is consistent.
# Done (2 records updated, 1 added)

# Insert Madison city into cities table
INSERT INTO
  d8_node__field_ro_city
VALUES
('ro', 0, 10222, 18424, 'en', 0, 'Madison')
;

# Insert Wisconsin state into states table
UPDATE
  d8_node__field_state
SET
  field_state_value = 'Wisconsin'
WHERE
  entity_id = 10222 and revision_id = 18424
;


# Update Wisconsin to United States of America for 10564
UPDATE
  d8_node__field_ro_country
SET
  field_ro_country_value = 'United States of America'
WHERE
  entity_id = 10222 and revision_id = 18424
;

###########################################
# END OF OP 5
###########################################

###########################################
# OP 6
###########################################
# Select Single entity
# https://takeielts.britishcouncil.org/recognising-organisation/oak-valley-college
# Country is set to California, state to Oak Glen. To change: update country, update state, add city of Oak Glen
SELECT
  d8_node_field_data.nid as id,
  d8_node_field_data.title,
  field_ro_country_value as country,
  d8_node__field_ro_city.field_ro_city_value as city,
  d8_node__field_state.field_state_value as state,
  d8_node_field_data.vid as revision_id
FROM
  d8_node_field_data
  LEFT JOIN d8_node__field_ro_country
  ON d8_node_field_data.nid = d8_node__field_ro_country.entity_id
  LEFT JOIN d8_node__field_ro_city
  ON d8_node__field_ro_city.entity_id = d8_node_field_data.nid
  LEFT JOIN d8_node__field_state
  ON d8_node_field_data.nid = d8_node__field_state.entity_id
WHERE d8_node_field_data.nid = 10867
;

# Location and title adjustments, so data is consistent.
# Done (2 records updated, 1 added)

# Insert Madison city into cities table
INSERT INTO
  d8_node__field_ro_city
VALUES
('ro', 0, 10867, 11276, 'en', 0, 'Oak Glen')
;

# Insert Oak Glen to California state into states table
UPDATE
  d8_node__field_state
SET
  field_state_value = 'California'
WHERE
  entity_id = 10867 and revision_id = 11276
;


# Update California to United States of America for 10564
UPDATE
  d8_node__field_ro_country
SET
  field_ro_country_value = 'United States of America'
WHERE
  entity_id = 10867 and revision_id = 11276
;

###########################################
# END OF OP 6
###########################################

# Location select.
(
SELECT DISTINCT
  field_ro_country_value as location,
  NULL as parent_country,
  NULL as parent_state
FROM
d8_node__field_ro_country
WHERE
bundle = 'ro'
)

UNION

(
SELECT DISTINCT
  d8_node__field_state.field_state_value as location,
  d8_node__field_ro_country.field_ro_country_value as parent_country,
  NULL as parent_state
FROM
  d8_node__field_state LEFT JOIN d8_node__field_ro_country
  ON
  d8_node__field_state.entity_id = d8_node__field_ro_country.entity_id
WHERE
d8_node__field_state.bundle = 'ro'
)

UNION

(
SELECT DISTINCT
  d8_node__field_ro_city.field_ro_city_value as location,
  d8_node__field_ro_country.field_ro_country_value as parent_country,
  d8_node__field_state.field_state_value as parent_state

FROM
  d8_node__field_ro_city
  LEFT JOIN d8_node__field_ro_country ON d8_node__field_ro_city.entity_id = d8_node__field_ro_country.entity_id
  LEFT JOIN d8_node__field_state ON d8_node__field_ro_city.entity_id = d8_node__field_state.entity_id

WHERE
  d8_node__field_ro_city.bundle = 'ro'
)
;