/*
 * The initial disgusting DO block checks for existence of qna_table before running the update.
 * See https://dba.stackexchange.com/questions/180318/postgres-update-a-table-only-if-it-exists
 */
DO $$
  BEGIN
    IF EXISTS
        ( SELECT 1
          FROM   information_schema.tables
          WHERE  table_schema = 'public'
          AND    table_name = 'qna_table'
        )
    THEN
      UPDATE qna_table
      SET state_id = state_none.id
      FROM
        (SELECT id FROM qna_state_table WHERE name = 'STATE_NONE') AS state_none,
        (SELECT id FROM qna_state_table WHERE name = 'STATE_PENDING_VERIFICATION') AS state_pending_verification
      WHERE state_id = state_pending_verification.id;
    END IF;
  END
$$;

DO $$
  BEGIN
    IF EXISTS
        ( SELECT 1
          FROM   information_schema.tables
          WHERE  table_schema = 'public'
          AND    table_name = 'qna_version_table'
        )
    THEN
      UPDATE qna_version_table
      SET state_id = state_none.id
      FROM
        (SELECT id FROM qna_state_table WHERE name = 'STATE_NONE') AS state_none,
        (SELECT id FROM qna_state_table WHERE name = 'STATE_PENDING_VERIFICATION') AS state_pending_verification
      WHERE state_id = state_pending_verification.id;
    END IF;
  END
$$;

DO $$
  BEGIN
    IF EXISTS
        ( SELECT 1
          FROM   information_schema.tables
          WHERE  table_schema = 'public'
          AND    table_name = 'qna_state_change_table'
        )
    THEN
      UPDATE qna_state_change_table
      SET from_state_id = state_none.id
      FROM
        (SELECT id FROM qna_state_table WHERE name = 'STATE_NONE') AS state_none,
        (SELECT id FROM qna_state_table WHERE name = 'STATE_PENDING_VERIFICATION') AS state_pending_verification
      WHERE from_state_id = state_pending_verification.id;
    END IF;
  END
$$;

DO $$
  BEGIN
    IF EXISTS
        ( SELECT 1
          FROM   information_schema.tables
          WHERE  table_schema = 'public'
          AND    table_name = 'qna_state_change_table'
        )
    THEN
      UPDATE qna_state_change_table
      SET to_state_id = state_none.id
      FROM
        (SELECT id FROM qna_state_table WHERE name = 'STATE_NONE') AS state_none,
        (SELECT id FROM qna_state_table WHERE name = 'STATE_PENDING_VERIFICATION') AS state_pending_verification
      WHERE to_state_id = state_pending_verification.id;
    END IF;
  END
$$;


/*
 * The initial disgusting DO block checks for existence of qna_state_table before trying to delete.
 * See https://dba.stackexchange.com/questions/180318/postgres-update-a-table-only-if-it-exists
 */
DO $$
  BEGIN
    IF EXISTS
        ( SELECT 1
          FROM   information_schema.tables
          WHERE  table_schema = 'public'
          AND    table_name = 'qna_state_table'
        )
    THEN
      DELETE FROM qna_state_table
      WHERE name = 'STATE_PENDING_VERIFICATION';
    END IF;
  END
$$;
