-- script for diagnostic and cleanup for studio vocabularies
-- see https://jira.nuxeo.com/browse/NXP-23018
-- v1.0

DO $$
DECLARE nvoc INTEGER := 0;
DECLARE studiotbl text;
DECLARE tbl text;
DECLARE i INTEGER;
DECLARE conflictid text;
DECLARE cleanup text[] := '{}';
DECLARE c text;
BEGIN
  FOR studiotbl IN
    SELECT table_name FROM information_schema.tables t1
     WHERE t1.table_name LIKE 'studio_vocabulary_%'
       AND EXISTS (SELECT * FROM information_schema.tables t2 WHERE t1.table_name = 'studio_vocabulary_' || t2.table_name)
  LOOP
    nvoc := nvoc + 1;
    tbl := substring(studiotbl from 19);

    -- re-add rows to studio vocabulary any missing rows added in bad vocabulary
    FOR i IN EXECUTE format('SELECT COUNT(*) FROM %I T WHERE NOT EXISTS (SELECT * FROM %I ST WHERE ST.id = T.id)', tbl, studiotbl)
    LOOP
      IF i = 0 THEN
        RAISE INFO 'No missing rows for %', studiotbl;
      ELSE
        RAISE INFO 'Copying % missing rows to %', i, studiotbl;
      END IF;
    END LOOP;
    EXECUTE format('INSERT INTO %I SELECT * FROM %I T WHERE NOT EXISTS (SELECT * FROM %I ST WHERE ST.id = T.id)', studiotbl, tbl, studiotbl);

    -- find conflicts
    FOR conflictid IN EXECUTE format('SELECT T.id FROM %I ST JOIN %I T ON ST.id = T.id WHERE (ST.label, ST.ordering, ST.obsolete) IS DISTINCT FROM (T.label, T.ordering, T.obsolete)', studiotbl, tbl)
    LOOP
      RAISE INFO 'In %, conflict for id: %', studiotbl, conflictid;
    END LOOP;

    -- generate cleanup SQL
    cleanup := array_append(cleanup, format('DROP TABLE %I;', tbl));

  END LOOP;

  RAISE INFO 'Done merging % Studio vocabularies', nvoc;
  RAISE INFO 'To finish cleanup, after resolving conflicts, delete the associated unprefixed tables by running the SQL queries below:';
  FOREACH c IN ARRAY cleanup
  LOOP
    RAISE INFO '  %', c;
  END LOOP;
END
$$;
