--
-- Change ID of any revision having start_time at a later time than
-- any other revision with higher ID
--
-- New IDs for revisions to be moved will be assigned in
-- start_time order and start after the highest existing
-- revision ID.
--
-- The number of moved revisions is returned.
--
-- {
CREATE OR REPLACE FUNCTION ver_fix_revision_disorder()
RETURNS bigint AS
$FIX$
DECLARE

  v_rec RECORD;
  v_newid bigint;
  v_numversionedtables int;
  v_totdisordered bigint;
  v_numdisordered bigint;

BEGIN

-- 1. Make sure sequence is set to stop filling gaps

  PERFORM setval('@extschema@.revision_id_seq',
    greatest(
      (select max(id) FROM @extschema@.revision),
      (select last_value from @extschema@.revision_id_seq)
    ), true);

-- 2. Prepare update queries
  v_numversionedtables := 0;
  FOR v_rec IN SELECT schema_name, table_name
               FROM table_version.versioned_tables
  LOOP
    BEGIN
      EXECUTE format('PREPARE "p_uc%s" AS '
          'UPDATE table_version.%s_%s_revision '
          'SET _revision_created = $1 WHERE _revision_created = $2',
          v_numversionedtables, v_rec.schema_name, v_rec.table_name);
      EXECUTE format('PREPARE "p_ue%s" AS '
          'UPDATE table_version.%s_%s_revision '
          'SET _revision_expired = $1 WHERE _revision_expired = $2',
          v_numversionedtables, v_rec.schema_name, v_rec.table_name);
      v_numversionedtables := v_numversionedtables + 1;
    EXCEPTION WHEN UNDEFINED_TABLE THEN

      RAISE WARNING 'Spurious record in table_version.versioned_tables '
                    'for table % in schema %: % does not exist)',
                    v_rec.table_name, v_rec.schema_name,
                    format('table_version.%s_%s_revision',
                      v_rec.schema_name, v_rec.table_name);
    END;
  END LOOP;

-- 3. For each misplaced revision, move to correct place

  v_totdisordered := 0;

  LOOP

    v_numdisordered := 0;

    FOR v_rec IN
      WITH revs_by_id AS (
        SELECT
          row_number() OVER (ORDER BY id) seq,
          id,
          start_time
        FROM
          table_version.revision
      ),
      revs_by_time AS (
        SELECT
          row_number() OVER (ORDER BY start_time) seq,
          id,
          start_time
        FROM
          table_version.revision
      )
      SELECT
          a.id,
          a.start_time atm,
          b.id as bi,
          b.start_time btm
        FROM revs_by_id a, revs_by_time b
       WHERE a.seq = b.seq
         AND a.id < b.id
         AND a.start_time > b.start_time
       ORDER by a.start_time

    LOOP

      -- Revision v_rec.id has to be moved to nextval('@extschema@.revision_id_seq');

      v_numdisordered := v_numdisordered + 1;

      -- Create new revision v_record

      INSERT INTO @extschema@.revision
        (id, revision_time, start_time, user_name, schema_change, comment)
      SELECT
        nextval('@extschema@.revision_id_seq'::regclass),
        revision_time, start_time, user_name, schema_change,
        comment
      FROM @extschema@.revision
        WHERE id = v_rec.id
      RETURNING id
      INTO v_newid;

      RAISE NOTICE 'Revision % has start_time %, while % has start_time %: renamed % to %',
        v_rec.id, v_rec.atm, v_rec.bi, v_rec.btm, v_rec.id, v_newid;

      -- Update @extschema@.tables_changed

      UPDATE @extschema@.tables_changed
        SET revision = v_newid
        WHERE revision = v_rec.id;
        
      -- Update all revisions of all revisioned tables


      FOR v_i IN 0..v_numversionedtables-1
      LOOP
        EXECUTE format('EXECUTE "p_uc%s"(%s, %s)', v_i, v_newid, v_rec.id);
        EXECUTE format('EXECUTE "p_ue%s"(%s, %s)', v_i, v_newid, v_rec.id);
      END LOOP;

      -- Delete now hopefully unreferenced old revision

      DELETE FROM @extschema@.revision WHERE id = v_rec.id;

    END LOOP;

    v_totdisordered := v_totdisordered + v_numdisordered;

    IF v_numdisordered = 0 THEN
      EXIT;
    END IF;

  END LOOP;

-- 4. Deallocate prepared update queries
  FOR i IN 0..v_numversionedtables-1
  LOOP
    EXECUTE format('DEALLOCATE "p_uc%s"', i);
    EXECUTE format('DEALLOCATE "p_ue%s"', i);
  END LOOP;

  RETURN v_totdisordered;

END;
$FIX$
LANGUAGE 'plpgsql' VOLATILE;
--}